Table of Contents
[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: <code sql> COL ETIMESTAMP FORMAT A22 COL DBUSER FORMAT A10 COL OSUSER FORMAT A10 COL USERHOST FORMAT A25 COL OBJECTSCHEMA FORMAT A15 COL OBJECTNAME FORMAT A30 COL STATEMENTTYPE FORMAT A20 COL SORTSQLTEXT FORMAT A150 </code>
- Summary: <code sql> SELECT TOCHAR(EXTENDEDTIMESTAMP,('DD-MM-YYYY HH24:MI:SS')) ETIMESTAMP, DBUSER , – STATEMENTID, OBJECTSCHEMA , OBJECTNAME , – STATEMENTTYPE , RETURNCODE, SUBSTR(SQLTEXT,1,150) SORTSQLTEXT FROM DBACOMMONAUDITTRAIL WHERE STATEMENTTYPE NOT IN ('LOGON','LOGOFF','LOGOFF BY CLEANUP') AND DBUSER='<USERNAME>' AND NOT RETURNCODE=0 AND NOT (SUBSTR(SQLTEXT,1,30) LIKE '%INSERT%' or SUBSTR(SQLTEXT,1,30) LIKE '%MERGE%') ORDER BY EXTENDEDTIMESTAMP ; </code>
- Failed per day: <code sql> SELECT THEDAY, COUNT(*) FROM ( SELECT TOCHAR(EXTENDEDTIMESTAMP,('YYYY-MM-DD')) THEDAY FROM DBACOMMONAUDITTRAIL WHERE STATEMENTTYPE NOT IN ('LOGON','LOGOFF','LOGOFF BY CLEANUP') AND DBUSER='<USERNAME>' AND NOT RETURNCODE=0 AND NOT (SUBSTR(SQLTEXT,1,30) LIKE '%INSERT%' or SUBSTR(SQLTEXT,1,30) LIKE '%MERGE%') ORDER BY EXTENDEDTIMESTAMP ) GROUP BY THEDAY order by THEDAY ; </code>