AWR and ASH Licensing – Letter to Larry Ellison

Saturday, June 30, 2007

Mark Brinsmead on the Pythian Group Blog presents an open letter to Larry Ellison regarding AWR (Automated Workload Repository) and ASH (Active Session History) licensing. This licensing topic has been previously discussed by Jonathan Lewis and Jared Still.

Mark has spearheaded a plea to allow access to the low-level tables and views where AWR and ASH retain performance information that we are unable to access unless licensed to use those features.

You can show your support by adding your name as a signatory to the Pythian Group Blog comments. The intention is to courier a letter to Oracle with the responses on July 9.

Update (22-Aug-2007)
Mark Brinsmead provides an update on his response from Oracle on the Pythian Group Blog.

Update (01-Jul-2007)
Howard Rogers provides his thoughts on this topic at Dizwell’s Blog.

Advertisements

What’s the purpose of blogging?

Monday, June 18, 2007

When I first started blogging back in September 2006, my wife was inquisitive and asked that exact question. My original intention was to learn about blogging and basically keep an digital notebook on RDBMS topics. I found that writing about problems I have encountered and solved, sometimes helps others which is rewarding in the sense I feel I’m giving back to the community. Some topics I write about receive informative comments or perspectives from others.

Since my blog expresses mostly “IT technical” topics, I believed someday it could possibly provide value should I ever require employment with another company. Kind of give the prospective employer additional detail on what I’ve done. At a minimum, hopefully show that I have some “written communication skills”.

Then Sunday morning, I see Chris Muir’s blog entry regarding technical blog “uses beyond documenting technical tips.” He was referring to Adam Darowski’s “The Blog is the New Resume“. Adam also provides a summary to his original post in “Roundup of “The Blog is the New Resume” Discussion“. There is additional input from Joshua Porter. The entire discussion offers some interesting perspectives.

Update (30-May-2008)
Read about “Blogging Safely” and “Blogging Safely Part II” from the ORACLENERD blog.

Update (06-Jul-2007)
Read about some ideas on how to enhance your blog from Fiona Brown’s “Blogging Tips for Oracle Bloggers“. And additional thoughts from Steven Chan’s “On Courtesy Amongst Bloggers“.


MS SQL Server Version and Edition

Tuesday, June 12, 2007

Found a need to determine the Microsoft SQL Server 2005 version I was running.  On a rare occasion, I need to determine the SQL Server versions being used with our Corporate Applications.  Most of those installations are SQL Server 2000 with various Service Pack or Hot Fix levels.

Microsoft Support provides a document called “How to identify your SQL Server version and edition” to help you decipher the SQL Server version and edition you have installed.

SQL Server 2005

Release

Sqlservr.exe

RTM

2005.90.1399

SQL Server 2005 Service Pack 1

2005.90.2047

SQL Server 2005 Service Pack 2

2005.90.3042

SQL Server 2000

Release

Sqlservr.exe

RTM

2000.80.194.0

SQL Server 2000 SP1

2000.80.384.0

SQL Server 2000 SP2

2000.80.534.0

SQL Server 2000 SP3

2000.80.760.0

SQL Server 2000 SP3a

2000.80.760.0

SQL Server 2000 SP4

2000.8.00.2039

Update (12-Jul-2007)
Some information on this blog for those who may not have moved to SQL Server 2000 Service Pack 4.  It appears support for Service Pack 3a expired on July 10, 2007.


SQL Server 2000 Support Lifecycle

Monday, June 11, 2007

A question arose in a Staff Meeting asking when SQL Server 2000 was expected to be de-supported.  Found this information from Microsoft regarding SQL Server 2000 Support Lifecycle.

Mainstream Support on SQL Server 2000 editions (excluding CE) will end April 8. 2008.  Extended Support expires April 9, 2013.

Below is an excerpt from the Microsoft Support Lifecycle Policy FAQ summarizing the difference between Mainstream and Extended support.

3. What is the difference between mainstream support, extended support, and online self-help support?

Support provided

Mainstream support phase

Extended support phase

Paid support (per-incident, per hour, and others)

X

X

Security update support

X

X

Non-security hotfix support

X

Requires extended hotfix agreement, purchased within 90 days of mainstream support ending.

No-charge incident support

X

 

Warranty claims

X

 

Design changes and feature requests

X

 

Product-specific information that is available by using the online Microsoft Knowledge Base

X

X

Product-specific information that is available by using the Support site at Microsoft Help and Support to find answers to technical questions

X

X

Note A hotfix is a modification to the commercially available Microsoft product software code to address specific critical problems.


Executions in v$sql

Monday, June 4, 2007

I have a third-party application called Clarity running on Solaris 9 (64-bit) using Oracle 9.2.0.8 (64-bit).  This is a development database, not production.  Archived redo log files have been generated every minute since midnight May 31st.  I was concerned at the frequency the archived redo log files were being generated as this is not a typical load for this database  Further investigation was warranted.

I started by checking v$sql looking for SQL with a high number of executions to see if I could find the SQL causing the high DML activity.  The output is below.

SYSTEM@nikudev> select executions, sql_text from v$sql where executions > 500000 order by executions desc;

EXECUTIONS SQL_TEXT
—————————– ————————————————————————————————————————
   1813867 INSERT INTO PRJ_BLB_SLICES_M_ALC (SLICE_
           REQUEST_ID, PRJ_OBJECT_ID, SLICE_DATE, S
           LICE, CREATED_DATE) VALUES (:REQUEST_ID,
           :OBJECT_ID,:SLICE_DATE,:SLICE,SYSDATE)    

   1420497 DELETE FROM PRJ_BLB_SLICES WHERE SLICE_R
           EQUEST_ID = :1 AND PRJ_OBJECT_ID = :2

    995791 INSERT INTO PRJ_BLB_SLICES_M_HARDALC (SL
           ICE_REQUEST_ID, PRJ_OBJECT_ID, SLICE_DAT
           E, SLICE, CREATED_DATE) VALUES (:REQUEST
           _ID,:OBJECT_ID,:SLICE_DATE,:SLICE,SYSDAT
           E)

    646375 INSERT INTO FCG_CQI_DATA_INTEGRITY (RUN_
           DATE, SEVERITY, OBJECT_TYPE, OBJECT_NAME
           , OBJECT_ID, CATEGORY, DESCRIPTION) SELE
           CT SYSDATE, 2, ‘PRJ’, :B2 , :B1 , ‘Tasks
           ‘, ‘Project has an EFFORT task.’ FROM DU
           AL

    543867 BEGIN NBI_EVENTS_INS_SP(:1,:2); END;

I believe this is one way to find the high activity but was wondering if there was some other view I should be querying in addition to this.  I re-ran the above SQL query about every 5 seconds to watch which statement’s execution count was growing at a rapid rate.

It was easy to look at the executions since the database was restarted about 48 hours earlier.  Was just wondering where else I could look to confirm the historical (since instance startup) executions and possibly match with existing redo/undo information (i.e. amount generated).  Unfortunately, I don’t believe I can determine which SQL statements are generating a lot of redo/undo information.

In the end, the Application Administrator was able to recognize what the application was doing based on the SQL statements I provided from my query of v$sql.  We just refreshed data from Production and now the application is going through the data slicing and summarizing it in many ways.  At least we know why.  Not sure it needs to be done for a development environment, but they are allowing to continue to completion.