oracle database expert performance tuning buffer pools keep recycle sqlplus query TOAD system trigger plsql programming
Edward Stoever is your
DATABASE-EXPERT
Computer solutions for businesses of all sizes!
(818) 528-4111
 Home  Résumé  Articles  Portfolio  Contact  YouTube 
Tuning SQL cursors that you cannot change.
By Edward Stoever
Typical of many databases, ours runs the same SQL statements every day. Regardless of when I run a statspack report, I frequently see the same anonymous SQL statements throughout the list. The question then becomes, "What can I do to improve the performance of a SQL statement that is run frequently but that I cannot edit?."

Actually, there are a few things that can be done. Let's take a look at an example from my statspack report:

                                                     CPU      Elapsd
  Buffer Gets    Executions  Gets per Exec  %Total Time (s)  Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
      9,110,989        1,032        8,828.5   12.8  4059.03   4473.86 4002696178
Module: GLBLSEL
SELECT SPRIDEN_ID FROM SPRIDEN,GURMAIL WHERE SPRIDEN_CHANGE_IND
IS NULL AND SPRIDEN_PIDM=GURMAIL_PIDM AND GURMAIL_SYSTEM_IND=:b1
 AND GURMAIL_LETR_CODE=:b2 AND((GURMAIL_TERM_CODE=:b3 AND:b1 IN(
'S','T'))OR(GURMAIL_AIDY_CODE=:b4 AND:b1='R')OR(:b1 NOT IN('S','
T','R')))AND GURMAIL_DATE_PRINTED IS NULL AND(TRUNC(NVL(GURMAIL_

First of all, its a good idea to run an explain plan on the SQL statement to see if it is making appropriate use of indexes. But, how do you get the entire SQL statement? You have the hash value, so all you need to do is run the following query:
  SELECT sql_text
    FROM v$sqltext
   WHERE hash_value = 4002696178
ORDER BY piece ASC;
As long as the query in question has not been aged out of the Shared pool, That will give you the entire SQL statement. Here is the results of my query:
----------------------------------------------------------------
SELECT SPRIDEN_ID FROM SPRIDEN,GURMAIL WHERE SPRIDEN_CHANGE_IND 
IS NULL AND SPRIDEN_PIDM=GURMAIL_PIDM AND GURMAIL_SYSTEM_IND=:b1
 AND GURMAIL_LETR_CODE=:b2 AND((GURMAIL_TERM_CODE=:b3 AND:b1 IN(
'S','T'))OR(GURMAIL_AIDY_CODE=:b4 AND:b1='R')OR(:b1 NOT IN('S','
T','R')))AND GURMAIL_DATE_PRINTED IS NULL AND(TRUNC(NVL(GURMAIL_
DATE_INIT,SYSDATE)+NVL(GURMAIL_WAIT_DAYS,0))<=TRUNC(SYSDATE))AND
 NOT EXISTS(SELECT'X' FROM GLRCOLR WHERE GLRCOLR_VARIABLE=:b5 AN
D GLRCOLR_PIDM=GURMAIL_PIDM AND GLRCOLR_LETR_CODE=:b2 AND GLRCOL
R_APPLICATION=:b6)                                              
9 rows selected
There is the text! Awesome... now all I need to do is piece it back together. I edit it a bit, format the SQL and I get this:
SELECT spriden_id
  FROM spriden, gurmail
 WHERE spriden_change_ind IS NULL
   AND spriden_pidm = gurmail_pidm
   AND gurmail_system_ind = :b1
   AND gurmail_letr_code = :b2
   AND (   (gurmail_term_code = :b3 AND :b1 IN ('S', 'T'))
        OR (gurmail_aidy_code = :b4 AND :b1 = 'R')
        OR (:b1 NOT IN ('S', 'T', 'R'))
       )
   AND gurmail_date_printed IS NULL
   AND (TRUNC (NVL (gurmail_date_init, SYSDATE) + NVL (gurmail_wait_days, 0)) <=
                                                               TRUNC (SYSDATE)
       )
   AND NOT EXISTS (
          SELECT 'X'
            FROM glrcolr
           WHERE glrcolr_variable = :b5
             AND glrcolr_pidm = gurmail_pidm
             AND glrcolr_letr_code = :b2
             AND glrcolr_application = :b6);
Regardless of the whitespace, the explain plan will be the same (perhaps not the case if the table is analyzed between the first execution and now). With TOAD, the explain plan is so easy to get, just open a new SQL Editor window, paste the SQL text in and click the Explain Plan tab. When I do so, I get the following results:
SELECT STATEMENT Optimizer Mode=RULE
  FILTER
    NESTED LOOPS
      TABLE ACCESS BY INDEX ROWID       GENERAL.GURMAIL
        INDEX RANGE SCAN                GENERAL.GURMAIL_SYS_IND_INDEX
      INDEX RANGE SCAN                  SATURN.SPRIDEN_KEY_INDEX
    INDEX RANGE SCAN                    GENERAL.GLRCOLR_KEY_INDEX
 	 
We can see that the Optimizer Mode is RULE. I know that will make many DBA's cringe, but our ERP was designed so that the entire database is optimized with RULE.
Even though the query is using indexes properly, there are some things we can still do to make it a bit faster:
  1. check each index to find out if it needs to be rebuilt. (to accomplish this, analyze index index_name validate structure then check the index_stats view. It should be rebuilt if del_lf_rows is 30% or more of lf_rows)
  2. move each index to a tablespace with a block size of 32 bytes (which will rebuild the index anyway). Indexes perform better with a 32 byte block size.
One final thing that I would suggest is to pin this SQL cursor into the shared pool. I already know that the module GLBLSEL runs once per day. It is likely that this SQL will be aged out by the next time the process is run. Pinning it in the shared pool will prevent it from being reparsed each day. To do so, I run the following query:
     SELECT address, hash_value
       FROM v$sqltext
      WHERE hash_value = 4002696178 AND ROWNUM = 1;

     ADDRESS          HASH_VALUE
     ---------------- ----------
     FFFFFEF91C3BC890 4002696178
     1 row selected
 
Then I pin the SQL cursor with the following:
     begin sys.dbms_shared_pool.keep('FFFFFEF91C3BC890, 4002696178','c'); end;

©2017 Edward Stoever