This post is continuation of the earlier one titled "Life Cycle of SQL Query in Oracle -I". In Previous post we discussed how the different phases of query execution by detailing each phase.
The second phase of query execution is parse phase. In this phase the query is parsed means the SQL engine check the query for following:
The parsing of a SQL statement is of two kinds a soft parse and a hard parse.
Soft Parse: A SQL is said to be soft parsed when the SQL engine founds the similar statement in the Library cache/Shared SQL area i.e. it will perform only first three steps and use the existing SQL plan in the Library cache/Shared SQL area.
Hard Parse: A SQL is said to be hard parsed when the SQL engine does not found any similar statement in the Library cache/Shared SQL area i.e.it will perform all the four steps in the parse phase.
Since the generation of execution plan is an expensive operation so a hard parse is comparatively expensive than the soft parse. A frequent hard parsing of SQL statement will effect the performance of system adversely. Excessive hard parse can occur when the shared pool area is small and the SQL statements being run are not using bind variable's or packages programs like functions/procedures. We should try and avoid SQL statements making hard parse very often.
We can achieve a better performance of system by monitoring hard parse ratio with respect to executions. This query gives the ratio of parse call made with respective number of query executions. I had some hard cutoff's like ratio of 50% and more than 20 parse calls made for a particular SQL.
There are few initialization parameters which can control what needs to be in shared pool, for how long and how to decide on parsing.
"cursor_space_for_time" by default FALSE i.e. it will tell oracle to flush out the aged SQL's to make room for new SQL's. When set to TRUE the oracle will not aged out the SQL as long as it has an open cursor referencing it.
When a user issues a SQL oracle evaluates the SQL statement whether the statement is identical or similar by comparing the text (hash value) with existing SQL statements (hash value) in the shared pool. The parameter cursor_sharing dictates oracle what needs to be done when evaluating SQL's.
"cursor_sharing" by default EXACT i.e. the SQL has to match the exact hash value for the previously run SQL, very useful when using bind variables or packaged programs which has bind values mismatch only considered in this category. It also has two other values SIMILAR and FORCE. When set to SIMILAR oracle forces it to use same cursor for SQL statement that are bind mismatch, literal mismatch without deteriorating/interfering with existing execution plan of the parent cursor. The SIMILAR option for cursor_sharing has been deprecated in 12c and oracle recommends not to use this in 11g R2 database for the problems its causing with multiple cursors kept open . And when set to FORCE oracle forces the statement to use same cursor or SQL statement that are bind mismatch, literal mismatch and even overwriting the existing execution plans. This is set to FORCE only as last resort of tuning when the pros outweighs cons i.e. if he performance improvement is substantial.
But with the enhancements in optimizer using the same plan for all statements irrespective of how data is skewed is not worth utilizing the power of cost based optimizer. The CBO decides which plan to use based on the data skewed using ACS (Advance Cursor Sharing). With ACS optimizer allows to use multiple plans for a single statement that uses bind value. This functionality is in-built in 11gR2 and does not require any additional configuration.
Some of the helpful SQL's to find which statements are switching plans...
I will post the remaining and final part in my next post title "Life Cycle of SQL Query in Oracle -III".
Any suggestions and correction are most welcomed. Thanks.
The second phase of query execution is parse phase. In this phase the query is parsed means the SQL engine check the query for following:
- The statement is check Syntactically (i.e. any typos, write order of clauses) and Schematically (i.e. existence of Objects used in SQL, correct column names, SQL functions) are valid.
- User privileges (i.e. select, update, insert, delete, execute) on objects involved in the SQL statement.
- Allocates private SQL area (i.e. a small memory area in UGA/PGA to hold the values of bind variables) for the SQL statement.
- Allocates Shared SQL area (Library Cache/SGA) depends on the availability of the similar SQL statement in the Library cache/Shared SQL area of SGA.
The parsing of a SQL statement is of two kinds a soft parse and a hard parse.
Soft Parse: A SQL is said to be soft parsed when the SQL engine founds the similar statement in the Library cache/Shared SQL area i.e. it will perform only first three steps and use the existing SQL plan in the Library cache/Shared SQL area.
Hard Parse: A SQL is said to be hard parsed when the SQL engine does not found any similar statement in the Library cache/Shared SQL area i.e.it will perform all the four steps in the parse phase.
Since the generation of execution plan is an expensive operation so a hard parse is comparatively expensive than the soft parse. A frequent hard parsing of SQL statement will effect the performance of system adversely. Excessive hard parse can occur when the shared pool area is small and the SQL statements being run are not using bind variable's or packages programs like functions/procedures. We should try and avoid SQL statements making hard parse very often.
We can achieve a better performance of system by monitoring hard parse ratio with respect to executions. This query gives the ratio of parse call made with respective number of query executions. I had some hard cutoff's like ratio of 50% and more than 20 parse calls made for a particular SQL.
SELECT SQL_ID,
PARSE_CALLS "PARSE CALLS",
EXECUTIONS "EXECUTIONS",
RATIO "RATIO%",
TO_CHAR(LAST_RUN, 'DD-MON-YY HH24:MI AM')
"LAST RUN",
TO_CHAR(LAST_LOAD, 'DD-MON-YY HH24:MI
AM') "LAST LOAD",
PARSING_SCHEMA_NAME "SCHEMA"
FROM (SELECT A.SQL_ID, A.PARSE_CALLS,
A.EXECUTIONS,
ROUND ((A.PARSE_CALLS/A.EXECUTIONS)*100)
RATIO,
A.LAST_ACTIVE_TIME LAST_RUN,
A.LAST_LOAD_TIME
LAST_LOAD,
A.PARSING_SCHEMA_NAME
FROM V$SQLAREA A, V$SQLAREA B
WHERE A.SQL_ID = B.SQL_ID
AND A.PARSE_CALLS >= 20
AND A.LAST_ACTIVE_TIME BETWEEN SYSDATE-1 AND
SYSDATE
ORDER BY PARSE_CALLS DESC )
WHERE
RATIO >= 50
ORDER BY 2 DESC,3
/
There are few initialization parameters which can control what needs to be in shared pool, for how long and how to decide on parsing.
SQL> show parameter cursor
NAME TYPE VALUE
------------------------------------ -----------
------------------------------
cursor_bind_capture_destination string
memory+disk
cursor_sharing string EXACT
cursor_space_for_time boolean
FALSE
open_cursors integer
2000
session_cached_cursors integer
50
"cursor_space_for_time" by default FALSE i.e. it will tell oracle to flush out the aged SQL's to make room for new SQL's. When set to TRUE the oracle will not aged out the SQL as long as it has an open cursor referencing it.
When a user issues a SQL oracle evaluates the SQL statement whether the statement is identical or similar by comparing the text (hash value) with existing SQL statements (hash value) in the shared pool. The parameter cursor_sharing dictates oracle what needs to be done when evaluating SQL's.
"cursor_sharing" by default EXACT i.e. the SQL has to match the exact hash value for the previously run SQL, very useful when using bind variables or packaged programs which has bind values mismatch only considered in this category. It also has two other values SIMILAR and FORCE. When set to SIMILAR oracle forces it to use same cursor for SQL statement that are bind mismatch, literal mismatch without deteriorating/interfering with existing execution plan of the parent cursor. The SIMILAR option for cursor_sharing has been deprecated in 12c and oracle recommends not to use this in 11g R2 database for the problems its causing with multiple cursors kept open . And when set to FORCE oracle forces the statement to use same cursor or SQL statement that are bind mismatch, literal mismatch and even overwriting the existing execution plans. This is set to FORCE only as last resort of tuning when the pros outweighs cons i.e. if he performance improvement is substantial.
But with the enhancements in optimizer using the same plan for all statements irrespective of how data is skewed is not worth utilizing the power of cost based optimizer. The CBO decides which plan to use based on the data skewed using ACS (Advance Cursor Sharing). With ACS optimizer allows to use multiple plans for a single statement that uses bind value. This functionality is in-built in 11gR2 and does not require any additional configuration.
Some of the helpful SQL's to find which statements are switching plans...
SQL> select * from
(select sql_id,
sum(execs) executions,
round(min(avg_eltime), 2) min_elapse_time,
round(max(avg_eltime), 2)
max_elapse_time
from
(select sql_id, plan_hash_value, execs,
avg_eltime
from (select
sql_id, plan_hash_value, sum(nvl(executions_delta,0))
execs, (sum(elapsed_time_delta)/decode(sum(nvl(executions_delta,0)),0,1,sum(executions_delta))/1000000)
avg_eltime
from DBA_HIST_SQLSTAT S,
DBA_HIST_SNAPSHOT SS
where ss.snap_id = S.snap_id
and ss.instance_number =
S.instance_number
and executions_delta > 0
group by sql_id, plan_hash_value
)
)
group by sql_id
)
where max_elapse_time > .1
and executions > 10
order by executions;
SQL_ID
EXECUTIONS MIN_ELAPSE_TIME MAX_ELAPSE_TIME
---------------------- -------------------
------------------------- --------------------------
aq8yqxyyb40nn 5,092
.25
.25
bhjxxwujr3ac3 2,508
.58
.58
7a5dp259kpw0a 2,502
.97
.97
bkbp21n9f0p4z 2,448 .41
.41
aqu52j8048h94 836 .01
.11
bd3cxdwx5xcnp 41 .05
1.05
ahsuxp21y43jb 41 0
9.67
0nt3bn5v8d1tp
23 .08
1.21
g5m0bnvyy37b1 17
1.01 2.77
SQL> select sql_id,
plan_hash_value, sum(nvl(executions_delta,0)) executions,
sum((elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000) Tot_Elapse_time,
sum((buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)))
"TOT_LOGICAL_IO's"
from
DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where sql_id = '&1'
and
ss.snap_id = S.snap_id
and
ss.instance_number = S.instance_number
and
executions_delta > 0
group by sql_id, plan_hash_value
order by 1, 2, 3;
SQL_ID
PLAN_HASH_VALUE EXECUTIONS
TOT_ELAPSE_TIME TOT_LOGICAL_IO's
--------------- --------------- ------------
----------------- ------------------
bd3cxdwx5xcnp 107901974 10 .052 994.4
bd3cxdwx5xcnp 873708484 1 1.053 1,147.0
bd3cxdwx5xcnp 1233429896 10 .085 999.4
bd3cxdwx5xcnp 2993587060 10 .084 995.0
bd3cxdwx5xcnp 4203913427 10 .064 994.4
--Use the awr to query the snapshot of all plan that’s
been associated with the sql statement.
SQL> select
* from TABLE(dbms_xplan.display_awr('bd3cxdwx5xcnp'));
I will post the remaining and final part in my next post title "Life Cycle of SQL Query in Oracle -III".
Any suggestions and correction are most welcomed. Thanks.
Avais , i found your article very informative , where is part III
ReplyDelete