[DOC] Kill hardparse

Descripcion

El hard parse es un performance killer. Hay que asesinarlo :-P

Pasos a seguir

No es fácil identificar qué genera el hard parse, los targets por este orden son:

  • Querys sin bind variables
  • Querys con muchas versiones
  • Uso de la SharedPool

La siguiente query no es definitiva ni excesivamente buena, pero puede servir. Hacer match sobre querys SIN bind variables no es precisamente fácil.

SET PAGES 0
SET LINESIZE 250
COL PERCENT_PARSES FORMAT 999.99
COL SCHEMA FORMAT A15
COL SQL_ID FORMAT A15
-- COL SQL_TEXT FORMAT A10000
COL TRIM_SQL FORMAT A190
SET SERVEROUT ON SIZE UNLIMITED
 
SELECT *
FROM (
    SELECT  VERSION_COUNT,
            EXECUTIONS,
            PARSING_SCHEMA_NAME SCHEMA,
            SQL_ID,                     
            SUBSTR(TRIM(REPLACE(REPLACE(REPLACE(SQL_FULLTEXT, CHR(10)), CHR(9)), CHR(13))),1,100) TRIM_SQL                                                                                                                                                                                                                                                   
--             SQL_FULLTEXT
    FROM V$SQLAREA
    WHERE PARSING_SCHEMA_NAME IN ( 'SCHEMA1', 'SCHEMA2', 'SCHEMAn' )
    AND REGEXP_LIKE(SQL_FULLTEXT,'[A-Z][[:alnum:]_]{1,33} {0,1}= {0,1}"{0,1}[[:alnum:]]{1,3}','inm')
    ORDER BY VERSION_COUNT DESC
    )
ORDER BY VERSION_COUNT DESC,EXECUTIONS DESC
;

La query busca en todos los SQL's de la SQLAREA con la REGEXP:

[A-Z][[:alnum:]_]{1,33} {0,1}= {0,1}"{0,1}[[:alnum:]]{1,3}

Es decir, intenta identificar “equals” (no likes, no debería haber :-P) que sean con bind.

COL PERCENT_PARSES FORMAT 999.99
COL TRIM_SQL FORMAT A120
SELECT * 
FROM (
    SELECT SQL_ID,
            VERSION_COUNT,
            EXECUTIONS,
            ((VERSION_COUNT*100)/EXECUTIONS) PERCENT_PARSES,
            PARSING_SCHEMA_NAME
    FROM V$SQLAREA
    WHERE VERSION_COUNT > 1
    AND EXECUTIONS > 1
    AND PARSING_SCHEMA_NAME IN ( 'SCHEMA1', 'SCHEMA2', 'SCHEMAn' )
    ORDER BY VERSION_COUNT DESC
    )
WHERE PERCENT_PARSES > 10
ORDER BY PERCENT_PARSES DESC
;
COL PARSE_CALLS FORMAT 999999
COL EXECUTIONS FORMAT 999999999
COL TRIM_SQLTEXT FORMAT A100
SELECT PARSE_CALLS, EXECUTIONS,  SQL_ID, SUBSTR(SQL_TEXT,1,100) TRIM_SQLTEXT
FROM V$SQLAREA
WHERE COMMAND_TYPE IN (2,3,6,7)
ORDER BY EXECUTIONS 
;

Lo mismo, limitando resultados:

COL PARSE_CALLS FORMAT 999999
COL EXECUTIONS FORMAT 9999999999
COL TRIM_SQLTEXT FORMAT A100
COL SQL_TEXT FORMAT A180
SELECT * FROM
  (
  SELECT PARSE_CALLS, EXECUTIONS,  SQL_ID, SQL_FULLTEXT
  FROM V$SQLAREA
  WHERE COMMAND_TYPE IN (2,3,6,7)
  ORDER BY EXECUTIONS DESC
  )
  WHERE ROWNUM < 30
;

Porcentaje de parses respecto al de ejecuciones:

COL PARSE_CALLS FORMAT 999999
COL EXECUTIONS FORMAT 9999999999
COL SQL_FULLTEXT FORMAT A180
SELECT * FROM
  (
  SELECT ((PARSE_CALLS*100)/EXECUTIONS) PERCENT_PARSES, PARSE_CALLS, EXECUTIONS,  SQL_ID, SQL_FULLTEXT
  FROM V$SQLAREA
  WHERE COMMAND_TYPE IN (2,3,6,7)
  AND EXECUTIONS>0
  AND EXECUTIONS>PARSE_CALLS
  ORDER BY ((PARSE_CALLS*100)/EXECUTIONS) DESC
  )
  WHERE ROWNUM < 30
;
SELECT *
FROM (
    SELECT * FROM (
        SELECT PARSE_CALLS, 
            EXECUTIONS,
            ((PARSE_CALLS*100)/EXECUTIONS) PERCENT_PARSES,
            SQL_ID, 
            PARSING_SCHEMA_NAME
    --         , SUBSTR(SQL_TEXT,1,100) TRIM_SQLTEXT
        FROM V$SQLAREA
        WHERE COMMAND_TYPE IN (2,3,6,7)
        AND PARSING_SCHEMA_NAME NOT IN ('SYS','SYSTEM','DBSNMP')
        ORDER BY ((PARSE_CALLS*100)/EXECUTIONS) DESC
    )
    WHERE PERCENT_PARSES>50
    ORDER BY EXECUTIONS DESC , PARSE_CALLS DESC , PERCENT_PARSES DESC
    )    
WHERE ROWNUM<30
;

Los command types son que se buscan son:

SELECT * FROM V$SQLCOMMAND WHERE COMMAND_TYPE IN (2,3,6,7);
col SQL_TEXT FORMAT A200 WORD WRAPPED
SELECT SQL_ID, VERSION_COUNT, sql_text FROM v$sqlarea WHERE version_count > 1 ORDER BY VERSION_COUNT ;

Localicar los SIDS con más hard parse:

SELECT * FROM 
  (
  SELECT * FROM  V$SESSTAT WHERE STATISTIC# = 
    (
    SELECT STATISTIC# FROM V$STATNAME WHERE NAME LIKE 'parse count (hard)'
    )
  AND VALUE > 50 ORDER BY VALUE DESC
  ) WHERE ROWNUM <20;

Y localizar quien es el que genera el hard parse:

col "SID/SERIAL" format a10
col username format a25
col osuser format a25
COL USERHOST FORMAT A30
col program format a80
 
SELECT  s.sid || ',' || s.serial# "SID/SERIAL", 
        s.username, 
        s.osuser, 
        p.spid "OS PID", 
        s.program
FROM v$session s, v$process p
WHERE s.paddr = p.addr
AND S.SID IN 
        (
        SELECT * FROM 
          (
          SELECT SID FROM  V$SESSTAT WHERE STATISTIC# = 
            (
            SELECT STATISTIC# FROM V$STATNAME WHERE NAME LIKE 'parse count (hard)'
            )
          AND VALUE > 50 ORDER BY VALUE DESC
          )
        )
AND S.USERNAME IS NOT NULL
/

Uso de la shared pool

Existe la siguiente vista/tabla indocumentada que nos permite ver qué querys están causando que otras querys sean expulsadas de la shared pool (aged out):

SELECT * FROM X$KSMLRU WHERE ksmlrsiz > 0;

es EXTREMADAMENTE útil

La siguiente query da información sobre como está siendo usada la shared pool y la posible fragmentación de la misma. En concreto muestra la lista de chunks disponibles:

SELECT '0 (<140)' BUCKET,
        KSMCHCLS, 
        KSMCHIDX, 
        10*trunc(KSMCHSIZ/10) "From",
        COUNT(*) "Count" , 
        MAX(KSMCHSIZ) "Biggest",
        trunc(avg(KSMCHSIZ)) "AvgSize", 
        trunc(SUM(KSMCHSIZ)) "Total" 
FROM x$ksmsp 
WHERE KSMCHSIZ<140 
AND KSMCHCLS='free' 
GROUP BY KSMCHCLS, KSMCHIDX, 10*trunc(KSMCHSIZ/10) 
UNION ALL 
SELECT '1 (140-267)' BUCKET, 
        KSMCHCLS, 
        KSMCHIDX,
        20*trunc(KSMCHSIZ/20), 
        COUNT(*),
        MAX(KSMCHSIZ),
        trunc(avg(KSMCHSIZ)) "AvgSize",
        trunc(SUM(KSMCHSIZ)) "Total"
FROM x$ksmsp 
WHERE KSMCHSIZ BETWEEN 140 AND 267 
AND KSMCHCLS='free' 
GROUP BY KSMCHCLS, KSMCHIDX, 20*trunc(KSMCHSIZ/20) 
UNION ALL 
SELECT '2 (268-523)' BUCKET, 
        KSMCHCLS, 
        KSMCHIDX, 
        50*trunc(KSMCHSIZ/50) ,
        COUNT(*) ,
        MAX(KSMCHSIZ) ,
        trunc(avg(KSMCHSIZ)) "AvgSize", 
        trunc(SUM(KSMCHSIZ)) "Total" 
FROM x$ksmsp 
WHERE KSMCHSIZ BETWEEN 268 AND 523 
AND KSMCHCLS='free' 
GROUP BY KSMCHCLS, KSMCHIDX, 50*trunc(KSMCHSIZ/50) 
UNION ALL 
SELECT '3-5 (524-4107)' BUCKET, 
        KSMCHCLS, 
        KSMCHIDX, 
        500*trunc(KSMCHSIZ/500),
        COUNT(*) , 
        MAX(KSMCHSIZ) , 
        trunc(avg(KSMCHSIZ)) "AvgSize", 
        trunc(SUM(KSMCHSIZ)) "Total" 
FROM x$ksmsp 
WHERE KSMCHSIZ BETWEEN 524 AND 4107 
AND KSMCHCLS='free' 
GROUP BY KSMCHCLS, KSMCHIDX, 500*trunc(KSMCHSIZ/500) 
UNION ALL 
SELECT '6+ (4108+)' BUCKET, 
        KSMCHCLS, 
        KSMCHIDX, 
        1000*trunc(KSMCHSIZ/1000) , 
        COUNT(*) , 
        MAX(KSMCHSIZ) , 
        trunc(avg(KSMCHSIZ)) "AvgSize", 
        trunc(SUM(KSMCHSIZ)) "Total" 
FROM x$ksmsp 
WHERE KSMCHSIZ >= 4108 
AND KSMCHCLS='free' 
GROUP BY KSMCHCLS, KSMCHIDX, 1000*trunc(KSMCHSIZ/1000);

Y por último la siguiente query muestra el uso general de la SGA:

SELECT  KSMCHCLS CLASS, 
        COUNT(KSMCHCLS) NUM, 
        SUM(KSMCHSIZ) SIZ, 
        TO_CHAR( ((SUM(KSMCHSIZ)/COUNT(KSMCHCLS)/1024)),'999,999.00')||'k' "AVG SIZE" 
FROM X$KSMSP GROUP BY KSMCHCLS; 

Thanks to “kundan

External doc

  • How to Identify Hard Parse Failures (Doc ID 1353015.1)

Identifying Failed parsing

Failed parsing is the worst scenario, queries sent to Oracle that are not syntactically o semantically well done. Oracle does not provide any way to troubleshot it (normally). Here's a way to do it.

Oracle describe how to identify parse failures on the following document: How to Identify Hard Parse Failures (Doc ID 1353015.1)

So let's enable:

ALTER SYSTEM SET EVENTS '10035 trace name context forever, level 1';

After enabling this trace, I found this parameter extremely useful, since then till now I didn't notified any performance lack on our live instance…

So you have the query logged, that's fine, but if you have ~60k different queries coming from your app, and some of them are dynamic, try to ask for them to the developers, the less they'll do is have a good time laughing.

So I tried enabling audit on failed select's for users and.. bingo! You'll have on the audit trail all the info you need to find what is causing the failed parsing :-)

AUDIT SELECT TABLE BY <USERNAME> WHENEVER NOT SUCCESSFUL; 
  • Formatting:
COL ETIMESTAMP FORMAT A22
COL DB_USER FORMAT         A10
COL OS_USER FORMAT         A10
COL USERHOST FORMAT        A25
COL OBJECT_SCHEMA FORMAT   A15
COL OBJECT_NAME FORMAT     A30
COL STATEMENT_TYPE FORMAT  A20
COL SORTSQL_TEXT FORMAT        A150
  • Summary:
SELECT TO_CHAR(EXTENDED_TIMESTAMP,('DD-MM-YYYY HH24:MI:SS')) ETIMESTAMP,
	 DB_USER ,
--      STATEMENTID,
	 OBJECT_SCHEMA , 
	 OBJECT_NAME , 
-- 	 STATEMENT_TYPE ,
        RETURNCODE,
	 SUBSTR(SQL_TEXT,1,150) SORTSQL_TEXT
FROM DBA_COMMON_AUDIT_TRAIL 
WHERE STATEMENT_TYPE NOT IN ('LOGON','LOGOFF','LOGOFF BY CLEANUP')
AND DB_USER='<USERNAME>'
AND NOT RETURNCODE=0
AND NOT (SUBSTR(SQL_TEXT,1,30) LIKE '%INSERT%' OR SUBSTR(SQL_TEXT,1,30) LIKE '%MERGE%')
ORDER BY EXTENDED_TIMESTAMP
;
  • Failed per day:
SELECT THEDAY, COUNT(*)
FROM (
    SELECT TO_CHAR(EXTENDED_TIMESTAMP,('YYYY-MM-DD')) THEDAY
    FROM DBA_COMMON_AUDIT_TRAIL 
    WHERE STATEMENT_TYPE NOT IN ('LOGON','LOGOFF','LOGOFF BY CLEANUP')
    AND DB_USER='<USERNAME>'
    AND NOT RETURNCODE=0
    AND NOT (SUBSTR(SQL_TEXT,1,30) LIKE '%INSERT%' OR SUBSTR(SQL_TEXT,1,30) LIKE '%MERGE%')
    ORDER BY EXTENDED_TIMESTAMP
    )
GROUP BY THEDAY
ORDER BY THEDAY
;
  • oracle/killing_hardparse.txt
  • Last modified: 2018/04/27 11:02
  • by dodger