====== [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.
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 :-P) 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 "[[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:
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 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=''
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=''
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
;