| Oracle SQL Performance Tuning and Optimization: Scripts [message #624976] |
Mon, 29 September 2014 20:26  |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
Oracle SQL Performance Tuning and Optimization: It's all about the Cardinalities. By Kevin Meade
Since the book is starting to sell on Amazon, I wanted to put the scripts online for download. Attached is a RAR file (will open with zip I believe). Remove the .txt extension and then open.
Just in case it is not clear, there is no requirement that you need to buy my book in order to download these scripts. They are FREE for anyone who wants them. This is after all OraFAQ and sharing is the name of the game so enjoy them please.
Have Fun. Kevin Meade
_____________________________________________________________________________________________________________________________________ __________________
_____________________________________________________________________________________________________________________________________ __________________
showtopcpu11g
My TOP-N query. This one is based on CPU usage, but you can edit it easily for any of the other metrics you might want. It has several sections describing your system to you.
It starts with a rundown of the BANNER telling you your version. This maps to available features etc. based on database version. Notice this report was produced on a database of version 11.1.0.7.0 which means it is missing some key features that are available in 11gR2 terminal release.
Then comes the list of active instances and their current uptime in days and hours. Use this to evalute the recent-ness of SQL in a later section. Note this database is a single instance database and the instance serving the database has been up for almost 94 days (2249 hours).
Then comes CPU count on each instance. This tells you actual total number of CPU hours that were available for processing. This can be compared to actuals used over all SQL in the cache along with time SQL was in the cache, in order to get a rough feel of overall activity of the system (rough feel!).
Then comes an interesting LOG based breakdown of the SQL. This is based on the Oracle tuning rule of thumb that 90% of the work on a database instance is normally done by only 1% of the SQL running on that instance. A logarithmic aggregation of SQL resource consumption allows us to zero in on the most expensive SQL and to see how it relates to all the other SQL, particularly in figuring work effort needed to obtain benefit. Notice for example that only 1 SQL statement is responsible for 30% of all CPU used by the instance, and that the next 4 SQL statements are responsible for the next 36% of CPU consumed. That means only 5 statements out of 2757 have consumed 66% of about 2/3 of the CPU used by the instance for SQL processing at least based on what is currently in the cursor cache. CPU_TIME is in seconds. Reading line 3, there are 4 SQL statements which have consumed >= 1000 seconds and < 10000 seconds.
Lastly comes the specific high cost SQL statements which if tuned, would eliminate the associated workload. This report shows their identifications along with their metrics. You would extract the SQL and their QEP and begin an analysis, assuming you are doing a system wide TOP-N tuning effort.
21:10:59 SQL> @SHOWTOPCPU11G
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE 11.1.0.7.0 Production
TNS for Solaris: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production
5 rows selected.
Elapsed: 00:00:00.13
INST_ID INSTANCE_NAME STARTUP_TIME UP_DAYS MAXIMUM_CACHE_HOURS RIGHT_NOW
---------- ---------------- -------------------- ---------- ------------------- --------------------
1 devdbabc 20-jul-2014 05:21:29 93.7 2249 21-oct-2014 21:11:03
1 row selected.
Elapsed: 00:00:00.14
INST_ID INSTANCE_NAME CPU_COUNT AVAILABLE_CPU_HOURS
---------- ---------------- ---------- -------------------
1 devdbabc 2 4498
1 row selected.
Elapsed: 00:00:00.15
INST_ID CPU_TIME_LOG10 SQL_STATEMENTS CPU_TIME_ROUNDED CPU_TIME INST_PCT_TOTAL DB_PCT_TOTAL RUNNING_CPU_TIME RUNNING_CONSUMED_CPU_HOURS
---------- -------------- -------------- ---------------- ---------- -------------- ------------ ---------------- --------------------------
1 -6 335 .000001 0 0 0 0 0
-2 236 .01 2 0 0 2 0
-1 430 .1 24 0 0 26 .01
0 1605 1 1749 5 5 1775 .49
1 122 10 3622 11 11 5398 1.5
2 24 100 6127 18 18 11524 3.2
3 4 1000 12353 36 36 23877 6.63
4 1 10000 10475 30 30 34351 9.54
-------------- ------------
sum 2757 100
INST_ID CPU_TIME_LOG10 SQL_STATEMENTS CPU_TIME_ROUNDED CPU_TIME INST_PCT_TOTAL DB_PCT_TOTAL RUNNING_CPU_TIME RUNNING_CONSUMED_CPU_HOURS
---------- -------------- -------------- ---------------- ---------- -------------- ------------ ---------------- --------------------------
-------------- ------------ --------------------------
sum 2757 100 21.37
8 rows selected.
Elapsed: 00:00:00.44
INST_ID SQL_ID CHILD_NUMBER CPU_SECONDS EPLAPSED_SECONDS EXECUTIONS SEC_PER_EXEC HOURS_IN_CACHE MODULE HASH_VALUE OPEN SQL_TEXT
---------- ------------- ------------ ----------- ---------------- ---------- ------------ -------------- ------------------------------ ---------- ---- ------------
1 db5d7kfgkaqkq 0 1339 1497 202298 0.0 2248 SQL*Plus 2670025302 Open select 'RECF
a3nb4uuym6ysw 0 1831 1893 606472 0.0 2248 SQL*Plus 3174267676 Open SELECT 'KTDA
bmw11wnmrc53r 0 2789 3922 32486 0.1 2248 SQL*Plus 662049911 Open SELECT 'DATA
09w67jh8gutt9 1 6393 16052 66434 0.2 249 ODBC Oracle 285042473 SELECT SP.SR
4ztz048yfq32s 0 10474 51949 4456 11.7 2248 Oracle Enterprise 1022037080 SELECT TO_CH
Manager.Metric Engine
5 rows selected.
Elapsed: 00:00:00.23
[Updated on: Thu, 23 April 2015 23:09] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
