dba:oracle:docs:killing_hardparse
Differences
This shows you the differences between two versions of the page.
Both sides previous revisionPrevious revision | Last revisionBoth sides next revision | ||
dba:oracle:killing_hardparse [2022/02/11 11:12] – ↷ Page moved from oracle:killing_hardparse to dba:oracle:killing_hardparse 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 | ||
- | EXECUTIONS, | ||
- | PARSING_SCHEMA_NAME SCHEMA, | ||
- | SQL_ID, | ||
- | SUBSTR(TRIM(REPLACE(REPLACE(REPLACE(SQL_FULLTEXT, | ||
- | -- | ||
- | FROM V$SQLAREA | ||
- | WHERE PARSING_SCHEMA_NAME IN ( ' | ||
- | AND REGEXP_LIKE(SQL_FULLTEXT,' | ||
- | ORDER BY VERSION_COUNT DESC | ||
- | ) | ||
- | ORDER BY VERSION_COUNT DESC, | ||
- | ; | ||
- | </ | ||
- | |||
- | La query busca en todos los SQL's de la SQLAREA con la REGEXP: | ||
- | < | ||
- | Es decir, intenta identificar " | ||
- | |||
- | |||
- | |||
- | |||
- | |||
- | |||
- | =====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)/ | ||
- | PARSING_SCHEMA_NAME | ||
- | FROM V$SQLAREA | ||
- | WHERE VERSION_COUNT > 1 | ||
- | AND EXECUTIONS > 1 | ||
- | AND PARSING_SCHEMA_NAME IN ( ' | ||
- | ORDER BY VERSION_COUNT DESC | ||
- | ) | ||
- | WHERE PERCENT_PARSES > 10 | ||
- | ORDER BY PERCENT_PARSES DESC | ||
- | ; | ||
- | </ | ||
- | |||
- | |||
- | =====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, | ||
- | FROM V$SQLAREA | ||
- | WHERE COMMAND_TYPE IN (2,3,6,7) | ||
- | ORDER BY EXECUTIONS | ||
- | ; | ||
- | </ | ||
- | |||
- | 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, | ||
- | 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: | ||
- | <code sql> | ||
- | COL PARSE_CALLS FORMAT 999999 | ||
- | COL EXECUTIONS FORMAT 9999999999 | ||
- | COL SQL_FULLTEXT FORMAT A180 | ||
- | SELECT * FROM | ||
- | ( | ||
- | SELECT ((PARSE_CALLS*100)/ | ||
- | FROM V$SQLAREA | ||
- | WHERE COMMAND_TYPE IN (2,3,6,7) | ||
- | AND EXECUTIONS> | ||
- | AND EXECUTIONS> | ||
- | ORDER BY ((PARSE_CALLS*100)/ | ||
- | ) | ||
- | WHERE ROWNUM < 30 | ||
- | ; | ||
- | </ | ||
- | <code sql> | ||
- | SELECT * | ||
- | FROM ( | ||
- | SELECT * FROM ( | ||
- | SELECT PARSE_CALLS, | ||
- | EXECUTIONS, | ||
- | ((PARSE_CALLS*100)/ | ||
- | SQL_ID, | ||
- | PARSING_SCHEMA_NAME | ||
- | -- , SUBSTR(SQL_TEXT, | ||
- | FROM V$SQLAREA | ||
- | WHERE COMMAND_TYPE IN (2,3,6,7) | ||
- | AND PARSING_SCHEMA_NAME NOT IN (' | ||
- | ORDER BY ((PARSE_CALLS*100)/ | ||
- | ) | ||
- | WHERE PERCENT_PARSES> | ||
- | ORDER BY EXECUTIONS DESC , PARSE_CALLS DESC , PERCENT_PARSES DESC | ||
- | ) | ||
- | WHERE ROWNUM< | ||
- | ; | ||
- | </ | ||
- | |||
- | |||
- | |||
- | Los command types son que se buscan son: | ||
- | <code sql> | ||
- | SELECT * FROM V$SQLCOMMAND WHERE COMMAND_TYPE IN (2,3,6,7); | ||
- | </ | ||
- | |||
- | |||
- | ===== Versiones de un SQL ===== | ||
- | <code sql> | ||
- | col SQL_TEXT FORMAT A200 WORD WRAPPED | ||
- | select SQL_ID, VERSION_COUNT, | ||
- | </ | ||
- | |||
- | |||
- | |||
- | |||
- | |||
- | ===== 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; | ||
- | </ | ||
- | |||
- | Y localizar quien es el que genera el hard parse: | ||
- | <code sql> | ||
- | col " | ||
- | col username format a25 | ||
- | col osuser format a25 | ||
- | COL USERHOST FORMAT A30 | ||
- | col program format a80 | ||
- | |||
- | SELECT | ||
- | 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): | ||
- | <code sql> | ||
- | 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: | ||
- | <code sql> | ||
- | select '0 (< | ||
- | KSMCHCLS, | ||
- | KSMCHIDX, | ||
- | 10*trunc(KSMCHSIZ/ | ||
- | count(*) " | ||
- | max(KSMCHSIZ) " | ||
- | trunc(avg(KSMCHSIZ)) " | ||
- | trunc(sum(KSMCHSIZ)) " | ||
- | from x$ksmsp | ||
- | where KSMCHSIZ< | ||
- | and KSMCHCLS=' | ||
- | group by KSMCHCLS, KSMCHIDX, 10*trunc(KSMCHSIZ/ | ||
- | UNION ALL | ||
- | select '1 (140-267)' | ||
- | KSMCHCLS, | ||
- | KSMCHIDX, | ||
- | 20*trunc(KSMCHSIZ/ | ||
- | count(*), | ||
- | max(KSMCHSIZ), | ||
- | trunc(avg(KSMCHSIZ)) " | ||
- | trunc(sum(KSMCHSIZ)) " | ||
- | from x$ksmsp | ||
- | where KSMCHSIZ between 140 and 267 | ||
- | and KSMCHCLS=' | ||
- | group by KSMCHCLS, KSMCHIDX, 20*trunc(KSMCHSIZ/ | ||
- | UNION ALL | ||
- | select '2 (268-523)' | ||
- | KSMCHCLS, | ||
- | KSMCHIDX, | ||
- | 50*trunc(KSMCHSIZ/ | ||
- | count(*) , | ||
- | max(KSMCHSIZ) , | ||
- | trunc(avg(KSMCHSIZ)) " | ||
- | trunc(sum(KSMCHSIZ)) " | ||
- | from x$ksmsp | ||
- | where KSMCHSIZ between 268 and 523 | ||
- | and KSMCHCLS=' | ||
- | group by KSMCHCLS, KSMCHIDX, 50*trunc(KSMCHSIZ/ | ||
- | UNION ALL | ||
- | select '3-5 (524-4107)' | ||
- | KSMCHCLS, | ||
- | KSMCHIDX, | ||
- | 500*trunc(KSMCHSIZ/ | ||
- | count(*) , | ||
- | max(KSMCHSIZ) , | ||
- | trunc(avg(KSMCHSIZ)) " | ||
- | trunc(sum(KSMCHSIZ)) " | ||
- | from x$ksmsp | ||
- | where KSMCHSIZ between 524 and 4107 | ||
- | and KSMCHCLS=' | ||
- | group by KSMCHCLS, KSMCHIDX, 500*trunc(KSMCHSIZ/ | ||
- | UNION ALL | ||
- | select '6+ (4108+)' | ||
- | KSMCHCLS, | ||
- | KSMCHIDX, | ||
- | 1000*trunc(KSMCHSIZ/ | ||
- | count(*) , | ||
- | max(KSMCHSIZ) , | ||
- | trunc(avg(KSMCHSIZ)) " | ||
- | trunc(sum(KSMCHSIZ)) " | ||
- | from x$ksmsp | ||
- | where KSMCHSIZ >= 4108 | ||
- | and KSMCHCLS=' | ||
- | group by KSMCHCLS, KSMCHIDX, 1000*trunc(KSMCHSIZ/ | ||
- | </ | ||
- | |||
- | Y por último la siguiente query muestra el uso general de la SGA: | ||
- | <code sql> | ||
- | SELECT | ||
- | COUNT(KSMCHCLS) NUM, | ||
- | SUM(KSMCHSIZ) SIZ, | ||
- | TO_CHAR( ((SUM(KSMCHSIZ)/ | ||
- | FROM X$KSMSP GROUP BY KSMCHCLS; | ||
- | </ | ||
- | |||
- | |||
- | Thanks to " | ||
- | |||
- | |||
- | ====== 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'; | ||
- | </ | ||
- | |||
- | 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' | ||
- | |||
- | So I tried enabling audit on failed select' | ||
- | 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 < | ||
- | </ | ||
- | |||
- | |||
- | |||
- | ===== Queries for getting info ===== | ||
- | |||
- | * Formatting: | ||
- | <code sql> | ||
- | COL ETIMESTAMP FORMAT A22 | ||
- | COL DB_USER FORMAT | ||
- | COL OS_USER FORMAT | ||
- | COL USERHOST FORMAT | ||
- | COL OBJECT_SCHEMA FORMAT | ||
- | COL OBJECT_NAME FORMAT | ||
- | COL STATEMENT_TYPE FORMAT | ||
- | COL SORTSQL_TEXT FORMAT | ||
- | </ | ||
- | * Summary: | ||
- | <code sql> | ||
- | SELECT TO_CHAR(EXTENDED_TIMESTAMP, | ||
- | DB_USER , | ||
- | -- STATEMENTID, | ||
- | OBJECT_SCHEMA , | ||
- | OBJECT_NAME , | ||
- | -- STATEMENT_TYPE , | ||
- | RETURNCODE, | ||
- | SUBSTR(SQL_TEXT, | ||
- | FROM DBA_COMMON_AUDIT_TRAIL | ||
- | WHERE STATEMENT_TYPE NOT IN (' | ||
- | AND DB_USER='< | ||
- | AND NOT RETURNCODE=0 | ||
- | AND NOT (SUBSTR(SQL_TEXT, | ||
- | ORDER BY EXTENDED_TIMESTAMP | ||
- | ; | ||
- | </ | ||
- | * Failed per day: | ||
- | <code sql> | ||
- | SELECT THEDAY, COUNT(*) | ||
- | FROM ( | ||
- | SELECT TO_CHAR(EXTENDED_TIMESTAMP, | ||
- | FROM DBA_COMMON_AUDIT_TRAIL | ||
- | WHERE STATEMENT_TYPE NOT IN (' | ||
- | AND DB_USER='< | ||
- | AND NOT RETURNCODE=0 | ||
- | AND NOT (SUBSTR(SQL_TEXT, | ||
- | ORDER BY EXTENDED_TIMESTAMP | ||
- | ) | ||
- | GROUP BY THEDAY | ||
- | order by THEDAY | ||
- | ; | ||
- | </ | ||
- | |||
- | |||
- | |||
- | |||
- | |||
- | |||
- |
dba/oracle/docs/killing_hardparse.txt · Last modified: 2023/01/31 08:22 by dodger