Differences

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

--- oracle:killing_hardparse [2018/04/27 09:02] – dodger
+++ dba:oracle:docs:killing_hardparse [2023/01/31 08:22] – removed - external edit (Unknown date) 127.0.0.1
@@ 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>