[DOC] Kill hardparse
Descripcion
Pasos a seguir
Uso de la shared pool
External doc
Identifying Failed parsing
- trace 10035
- Queries for getting info

[DOC] Kill hardparse

Descripcion

El hard parse es un performance killer. Hay que asesinarlo

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

Querys sin bind variables

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 ) que sean con bind.

Identificar SQL's con alto porcentaje de versiones

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
;

Identificar SQL's generando hard parse

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);

Versiones de un SQL

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 ;

Identificar SESIONES con mucho hard parse

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.

trace 10035

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;

Queries for getting info

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
;

Table of Contents