Differences

This shows you the differences between two versions of the page.

--- dba:oracle:docs:killing_hardparse [2023/01/31 08:22] – removed - external edit (Unknown date) 127.0.0.1
+++ dba:oracle:docs:killing_hardparse [2023/01/31 08:22] (current) – ↷ Page moved from dba:oracle:killing_hardparse to dba:oracle:docs:killing_hardparse dodger
@@ Line 1: / Line 1: @@
+====== [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
+=====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.
+<code sql>
+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
+;
+</code>
+La query busca en todos los SQL's de la SQLAREA con la REGEXP:
+<code>[A-Z][[:alnum:]_]{1,33} {0,1}= {0,1}"{0,1}[[:alnum:]]{1,3}</code>
+Es decir, intenta identificar "equals" (no likes, no debería haber :-P) que sean con bind.
+=====Identificar SQL's con alto porcentaje de versiones=====
+<code sql>
+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
+;
+</code>
+=====Identificar SQL's generando hard parse=====
+<code sql>
+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
+;
+</code>
+Lo mismo, limitando resultados:
+<code sql>
+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
+;
+</code>
+Porcentaje de parses respecto al de ejecuciones:
+<code sql>
+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
+;
+</code>
+<code sql>
+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
+;
+</code>
+Los command types son que se buscan son:
+<code sql>
+SELECT * FROM V$SQLCOMMAND WHERE COMMAND_TYPE IN (2,3,6,7);
+</code>
+===== Versiones de un SQL =====
+<code 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 ;
+</code>
+===== Identificar SESIONES con mucho hard parse=====
+Localicar los SIDS con más hard parse:
+<code sql>
+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;
+</code>
+Y localizar quien es el que genera el hard parse:
+<code sql>
+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
+/
+</code>
+====== 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):
+<code sql>
+SELECT * FROM X$KSMLRU WHERE ksmlrsiz > 0;
+</code>
+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:
+<code sql>
+select '0 (<140)' BUCKET,
+        KSMCHCLS,
+        KSMCHIDX,
+*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,
+*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,
+*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,
+*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,
+*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);
+</code>
+Y por último la siguiente query muestra el uso general de la SGA:
+<code sql>
+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;
+</code>
+Thanks to "[[http://kundanjha-blogs.blogspot.com.es/2010/10/shared-pool-fragmentation.html|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:
+<code sql>
+ALTER SYSTEM SET EVENTS '10035 trace name context forever, level 1';
+</code>
+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 :-)
+<code sql>
+AUDIT SELECT TABLE BY <USERNAME> WHENEVER NOT SUCCESSFUL;
+</code>
+===== Queries for getting info =====
+  * Formatting:
+<code sql>
+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
+</code>
+  * Summary:
+<code sql>
+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
+;
+</code>
+  * Failed per day:
+<code sql>
+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
+;
+</code>