My oracle_performance github repository

After seeing Kyle Hailey’s ashmasters GitHub repository (with all kinds of interesting scripts), I thought I might give it a try and start my own repository on GitHub. I blogged about some of my scripts before, but storing these on my domain was kind of unfriendly, because if I stored them as a .sql file, it assumed the file was a SQLite database.

So now you can find my ever growing Oracle performance script repository on GitHub: https://github.com/Marcel-Jan/oracle_performance

I’ve already added several scripts. One of which, is sql_old_hash.sql. It’s a script to retrieve information about a SQL statement, based on the old hash value found in Statspack. It retrieves about the same information as sql_sql_id.sql does.

So now you can run a Statspack report, check the SQL statement with the most elapsed time in the “SQL ordered by Elapsed time for DB” section (or ordered by CPU, gets, reads, executions, etc.), check the Old Hash Value column. Then run this script, enter the old hash value, choose a child number that seems appropriate and out comes about everything you might want to know. I didn’t have an Oracle database with Statspack handy to test it. I think it works all right, since I copied a lot of working code from sql_sql_id.sql and replaced sql_id’s for hash_value’s in the where clauses, but let me know.

Advertisements

About Marcel-Jan Krijgsman

Ever since I started working with Oracle, I had an interest in Oracle database performance tuning. This led, eventually, to a four day training I made and gave for customers of Transfer Solutions. Since 2012 I work for Rabobank Nederland. A few years ago I also became interested in Oracle database security. All technology aside, it is my experience that security usually plays out on a political level. I'm a Oracle certified professional for the 8i, 9i, 10g and 11g databases and Oracle Database 11g Performance Tuning Certified Expert.
This entry was posted in Oracle performance tuning, Oracle scripts and tagged , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s