Scan Oracle-DB for excessive execution of SQL with literals instead of bind variables

Using literals instead of bind variables in SQLs may have several drawbacks:
  • Performance slows down because of:
    • worse cache hit ratio
    • higher parse effort
  • Growing SQL-area in shared pool pushes other major memory objects outside, especially DB-cache
  • Each executed SQL has to be hard parsed separately
  • Risk of SQL-injection may exist (depending from source of literal)

Methods to find heavy executed SQLs with missing bind variables

1. Scan active session history for different SQL-ID but same execution plan

WITH Ret AS (SELECT :Consider_Days_Back Days FROM DUAL)
SELECT x.SQL_Plan_Hash_Value, x.Different_SQL_IDs, x.Last_Used_SQL_ID, u.UserName, x.First_Occurrence, x.Last_Occurrence, x.Elapsed_Secs
FROM   (
      SELECT h.SQL_Plan_Hash_Value,
             COUNT(DISTINCT h.SQL_ID) Different_SQL_IDs,
             MAX(h.SQL_ID) KEEP (DENSE_RANK LAST ORDER BY h.Sample_Time) Last_Used_SQL_ID,
             User_ID,
             MIN(h.Sample_Time) First_Occurrence,
             MAX(h.Sample_Time) Last_Occurrence,
             SUM(CASE WHEN h.Sample=1 OR d.Min_Sample_Time IS NULL OR h.Sample_Time < d.Min_Sample_Time THEN h.Sample END) Elapsed_Secs   /* dont count twice in SGA and AWR */
      FROM   (SELECT Inst_ID Instance_Number, Sample_Time, SQL_Plan_Hash_Value, SQL_ID, 1 Sample, User_ID,
                     MIN(Sample_Time) OVER (PARTITION BY SQL_Plan_Hash_Value) Delimiter
              FROM   gv$Active_Session_History
              CROSS JOIN Ret
              WHERE  Sample_Time > SYSDATE-Ret.Days
              UNION ALL
              SELECT Instance_Number, Sample_Time, SQL_Plan_Hash_Value, SQL_ID, 10 Sample, User_ID,NULL Delimiter
              FROM   DBA_Hist_Active_Sess_History
              CROSS JOIN Ret
              WHERE  Sample_Time > SYSDATE-Ret.Days
             ) h
      LEFT OUTER JOIN (SELECT Inst_ID Instance_Number, SQL_Plan_Hash_Value, MIN(Sample_Time) Min_Sample_Time   /* limit Values in AWR-table from SGA*/
                       FROM   gv$Active_Session_History
                       GROUP BY  Inst_ID, SQL_Plan_Hash_Value
                      ) d ON d.Instance_Number = h.Instance_Number AND d.SQL_Plan_Hash_Value = h.SQL_Plan_Hash_Value
      WHERE  h.SQL_Plan_Hash_Value != 0
      GROUP BY h.SQL_Plan_Hash_Value, h.User_ID
     ) x
JOIN   DBA_Users u ON u.User_ID = x.User_ID
WHERE Different_SQL_IDs > :Min_Different_SQLs
ORDER BY Different_SQL_IDs DESC
;

2. Scan SGA for different SQL-ID but same execution plan

SELECT a.Inst_ID                                                         "Instance",
       a.Plan_Hash_Value                                                 "Plan hash value",
       a.Parsing_Schema_Name                                             "Parsing schema",
       COUNT(*)                                                          "No. of entries in gv$SQLArea",
       COUNT(DISTINCT a.SQL_ID)                                          "No. of different SQL-IDs",
       MIN(a.Last_Active_Time)                                           "Oldest active time",
       MAX(a.Last_Active_Time)                                           "Youngest active time",
       MAX(a.SQL_ID) KEEP (DENSE_RANK LAST ORDER BY Last_Active_Time)    "SQL_ID",
       ROUND(SUM(a.Sharable_Mem)  /(1024*1024), 2)                       "Sharable memory (MB)",
       ROUND(SUM(a.Persistent_Mem)/(1024*1024), 2)                       "Persistent memory (MB)",
       ROUND(SUM(a.Runtime_Mem)   /(1024*1024), 2)                       "Runtime memory (MB)",
       SUBSTR(MAX(a.SQL_Text) KEEP (DENSE_RANK LAST ORDER BY Last_Active_Time), 1, 400)  "SQL text"
FROM   gv$SQLArea a
WHERE  a.Plan_Hash_Value != 0
GROUP BY a.Inst_ID, a.Plan_Hash_Value, a.Parsing_Schema_Name
HAVING COUNT(*) > :Min_Different_SQLs
ORDER BY 4 DESC
;

3. Scan the SGA for different SQLs with same leading characters in statement

WITH Len AS (SELECT :Num_Chars_to_Compare Substr_Len FROM DUAL)
SELECT g.*, s.SQL_Text "Beispiel SQL-Text"
FROM   (
       SELECT COUNT(*) Variationen, Inst_ID, MIN(Parsing_Schema_Name) UserName, COUNT(DISTINCT Parsing_Schema_Name) Anzahl_User,
              SUBSTR(s.SQL_Text, 1, Len.Substr_Len) SubSQL_Text,
              ROUND(SUM(Sharable_Mem+Persistent_Mem+Runtime_Mem)/(1024*1024),3) "Memory (MB)",
              MIN(s.SQL_ID) SQL_ID,
              MIN(TO_DATE(s.First_Load_Time, 'YYYY-MM-DD/HH24:MI:SS')) Min_First_Load,
              MIN(Last_Load_Time) Min_Last_Load,
              MAX(Last_Load_Time) Max_Last_Load,
              MAX(Last_Active_Time) Max_Last_Active,
              MIN(Parsing_Schema_Name) Parsing_Schema_Name,
              COUNT(DISTINCT Parsing_Schema_Name) "Different pars. schema names"
       FROM   gv$SQLArea s, Len
       GROUP BY Inst_ID, SUBSTR(s.SQL_Text, 1, Len.Substr_Len)
       HAVING COUNT(*) > 10
      ) g
JOIN gv$SQLArea s ON s.Inst_ID = g.Inst_ID AND s.SQL_ID = g.SQL_ID
ORDER BY "Memory (MB)" DESC NULLS LAST
;

Panorama offers methods to quantify the impact of missing bind variables more comfortable and drill down into SQLs:

1. Check your system for SQL with missing bind variables sorted by number of occurrences

You can scan your system for SQLs with missing bind variables by two ways at menu „Spec. additions“ / „Dragnet investigation“, point 4.1 until 4.3

  • List SQLs with multiple SQL-ID but same execution plan from SGA and also from Active Session History
  • List SQLs with multiple SQL-ID but same fist n characters

2. Quantify memory consumption due to excessive SQLs flooding SQL-area

One obstacle is: If you don’t set limits for db_cache_size in initialization than excessive execution of SQLs with different literals instead of bind variables leads to growing SQL-area in shared pool, thereby reducing the buffer cache.
This may dramatically reduce your cache hit rate and enlarge execution times all over your database.

Common sizes for shared pool are between 0.5 and 2 GB for larger database systems, the greater part of SGA should be available for buffer cache.
If you consider a SQL-area that is much greater than the buffer cache, this is often a signal for massive problem with missing bind variables.

 Even if you drive your database with cursor_sharing=FORCE to fix the missing bind variables issue, than full SQL text before translation by cursor_sharing=FORCE is stored in KHLH0-area of shared pool and may also consume gigabytes.

You can recognize your memory dividing by Panorama at menu „SGA/PGA-Details“ / „SGA components“ like shown here:


You can find more about Panorama at http://rammpeter.github.io/panorama.html

Comments

Post a Comment

Popular posts from this blog

Common pitfalls using SQL*Net via Firewalls

It's very common that firewalls are terminating idle TCP sessions after a limited time, often after approximately one hour. Possible problem If you have a long running SQL or PL/SQL program and the firewall terminates your apparently idle TCP session this may lead to the following scenario: the database is not able to send result to client. After reaching the TCP-timeout the database server terminates the DB-Session the client program stays in socket read forever waiting for the database response Oracle's full solution: SQLNET.EXPIRE_TIME=x in sqlnet.ora Oracle's solution for this problem is setting parameter SQLNET.EXPIRE_TIME=x in sqlnet.ora where x are the minutes between keep alive packets on idle sessions that inform the firewall that this session is always alive. SQLNET.EXPIRE_TIME hast to be set in the sqlnet.ora of your RDBMS' ORACLE_HOME. Normally $ORACLE_HOME/network/admin. If you have a grid infrastructure setting SQLNET.EXPIRE_TIME in sqlnet
Read more

Oracle-DB: How to check for appropriate sequence caching

Image
A common pitfall while using sequences in Oracle-DB is forgetting to define sequence caching. The default for sequences is cache size = 0. That means, every sequence.nextval operation triggers a single write operation on dictionary table sys.SEQ$. Especially if frequently calling sequence.nextval uncached sequences may cause significant performance degradation. Frequently calling sequence.nextval in parallel sessions may lead to ramdom lock scenarios in library cache. Setting a cache size for a sequence may heavy increase performance of nextval-operations. There's a minimal memory overhead for caching a sequence because all that's needed is a counter to increase in SGA memory and a upper limit. If this limit is reached with the counter, the upper limit is increased by cache size and the new upper limit is written down once to SEQ$. The possible drawback for sequence caching is that you loose all your values between the current value and the highest cached value if yo
Read more

Oracle-DB: Link between audit trail and active session history

Unfortunately the audit trail of the Oracle-DB uses a different session identifier (AudSid) than the Active Session History (SID + Serial#). Both identifiers are available in v$Session (AudSid, SID, Serial#). So during lifetime of a session it is possible to link between the session info from v$Session an audit trail. But neither the AudSID is stored in ASH (v$Active_Session_History) nor the SID + Serial# is stored in audit trail. This prevents from combining session info of both sources after the session is closed. There is a possible way to link audit trail with ASH by establishing a logon trigger for that. The LOGOFF records in audit trail (if AUDIT SESSION is active) record also the Client_Identifier from v$Session. So supplying v$Session.Client_Identifier with the needed info allows to retrieve it from DBA_Audit_Trail.Client_ID.  This logon trigger does it: CREATE OR REPLACE TRIGGER Client_ID AFTER LOGON ON DATABASE -- Put unique session identifier into client-id to have it a
Read more