Home » Other » Marketplace » Oracle SQL Performance Tuning and Optimization: Scripts
Oracle SQL Performance Tuning and Optimization: Scripts [message #624976] Mon, 29 September 2014 20:26 Go to next message
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

Re: Oracle SQL Performance Tuning and Optimization: Scripts [message #625141 is a reply to message #624976] Wed, 01 October 2014 13:30 Go to previous messageGo to next message
Flyby
Messages: 188
Registered: March 2011
Location: Belgium
Senior Member
Thanks, going to check it out
Re: Oracle SQL Performance Tuning and Optimization: Scripts [message #625319 is a reply to message #624976] Sun, 05 October 2014 02:01 Go to previous messageGo to next message
LNossov
Messages: 318
Registered: July 2011
Location: Germany
Senior Member
Hello Kevin,

my congratulations on your book! I ordered it already. My book appeared in April this year (http:// www.amazon.de/Performance-Tuning-f%C3%BCr-Oracle-Datenbanken-X-systems-press/dp/3642330525/ref=sr_1_1?s=books&ie=UTF8&qid=139 9209716&sr=1-1).
Unfortunately it is in German because of my pour English. But you could download some test cases and scripts to this book from http://tutool.de/book.

I described there a method of SQL Tuning also based on cardinality in the chapter "Formal SQL Tuning". It would be interesting for me to compare the both methods.

Best regards
Leonid
Re: Oracle SQL Performance Tuning and Optimization: Scripts [message #627028 is a reply to message #625319] Thu, 06 November 2014 11:43 Go to previous messageGo to next message
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
Senior Member

I love reading your comments (very details and well versed)

Ordered a copy today. Smile

Best,
Manu
Re: Oracle SQL Performance Tuning and Optimization: Scripts [message #635619 is a reply to message #624976] Thu, 02 April 2015 13:43 Go to previous messageGo to next message
ismailklc84
Messages: 2
Registered: January 2009
Junior Member
Is the below coupon code valid now? I tried it but i couldn't use it.

K5G7AJFG Percentage Off 50%
Re: Oracle SQL Performance Tuning and Optimization: Scripts [message #636439 is a reply to message #624976] Thu, 23 April 2015 23:07 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Since the price of the book was dropped 40% on Amazon, the coupons no longer work. If you are on the fence about getting the book, I suggest two things:

1. download the attachments here so that you can evaluate the book before you buy it.

2. make sure to get your company to buy it for you by filling out whatever expense reimbursement forms they offer.  Most companies will give you your money back on books related to your continued job based education.


Good luck and enjoy it. Kevin
Re: Oracle SQL Performance Tuning and Optimization: Scripts [message #673919 is a reply to message #624976] Mon, 17 December 2018 15:56 Go to previous message
dan.m.dunlap@gmail.com
Messages: 1
Registered: December 2018
Location: Oracle Apps/FMW DBA
Junior Member
Kevin,

I purchased your book on Amazon on 12/15/2018. Just wanted to say thanks for such a detailed effort on a subject that I've always struggled to understand in a systematic manner. I'm still only in the first chapters, but the procedure/process you've developed makes a lot of sense.

Best Wishes,

Dan Dunlap
Previous Topic: Best online training for SQL
Next Topic: McTns: a lsnrctl-like program for Windows
Goto Forum:
  


Current Time: Thu Mar 28 18:45:49 CDT 2024