Subscribe to Posts by Email

Subscriber Count

    696

Disclaimer

All information is offered in good faith and in the hope that it may be of use for educational purpose and for Database community purpose, but is not guaranteed to be correct, up to date or suitable for any particular purpose. db.geeksinsight.com accepts no liability in respect of this information or its use. This site is independent of and does not represent Oracle Corporation in any way. Oracle does not officially sponsor, approve, or endorse this site or its content and if notify any such I am happy to remove. Product and company names mentioned in this website may be the trademarks of their respective owners and published here for informational purpose only. This is my personal blog. The views expressed on these pages are mine and learnt from other blogs and bloggers and to enhance and support the DBA community and this web blog does not represent the thoughts, intentions, plans or strategies of my current employer nor the Oracle and its affiliates or any other companies. And this website does not offer or take profit for providing these content and this is purely non-profit and for educational purpose only. If you see any issues with Content and copy write issues, I am happy to remove if you notify me. Contact Geek DBA Team, via geeksinsights@gmail.com

Pages

Performance Troubleshooting Series: Query Parsing & Execution Times

As we all know, optimizer parse the statement to provide a plan, but to provide that Oracle Optimizer has to go through different phases called Parse, Execute, Fetch.

1. Parsing has three phases again,

Compiler: Compiles the statement to oracle readable format or oracle understandable state.

Estimator: Look out the statement and get the object names and check the object statistics and provide a estimate how much time and resources like cpu / mem / io required to execute this plan

Plan Generator: Based on the estimator inputs, plan generator will provide the plan in terms of execution plan.

Then plan execution will be done by the server process and get the data out from disk or buffer with two different phases called,

2. Execute: Executes the statement.

3. Fetch: Fetches the data in sequential way that how plan was.

Now for this post, How to find how much time spent for each phase.

Lets look at different views and what they can offer to us,

V$SQL From the shared pool, Provides sql execution details of all including cpu time, elapsed time, io time, wait time etc,

but elapsed time is for complete parse/execute/fetch

ELAPSED_TIME NUMBER Elapsed time (in microseconds) used by this cursor for parsing, executing, and fetching
dba_hist_sqlstat From the awr data, Provides sql execution details of all including cpu time, elapsed time, io time, wait time etc,but elapsed time is for complete parse/execute/fetch

ELAPSED_TIME_TOTAL NUMBER Cumulative value of elapsed time (in microseconds) used by this cursor for parsing/executing/fetching
ELAPSED_TIME_DELTA NUMBER Delta value of elapsed time (in microseconds) used by this cursor for parsing/executing/fetching
v$sesstat

&

v$sqlstat

select b.sid, a.name, b.value 

from v$sesstat b, v$statname a 

where a.name in ('parse count (hard)', 'execute count') 

and b.statistic# = a.statistic# 

order by sid;

v$sqlstats From the shared pool, more or like v$sql but more robust and good. but still elapsed time is for complete parse/execute/fetch

From Documentation:- 

ELAPSED_TIME NUMBER Elapsed time (in microseconds) used by this cursor for parsing, executing, and fetching
v$active_session_history ASH provides robust information of active sessions and what they are waiting or consuming, and can provide if a session is in parsing or execution during that sample time at least from 11gr2

IN_PARSE VARCHAR2(1) Indicates whether the session was parsing at the time of sampling (Y) or not (N)
IN_HARD_PARSE VARCHAR2(1) Indicates whether the session was hard parsing at the time of sampling (Y) or not (N)
IN_SQL_EXECUTION VARCHAR2(1) Indicates whether the session was executing SQL statements at the time of sampling (Y) or not (N)

or example:- 

if the sql_id is in_parse y , you have to sum the whole sample time or count it.

For example:- THe sid is 521 and sql_id is 93sdljkls0300

The following query gives you how much time the query spent on only hard parsing or executing.

select sid,sql_id,sum(delta_time) from v$active_session_history where sql_id='93sdljkls0300' and in_hard_parse='Y' group by sid,sql_id;

select sid,sql_id,sum(delta_time) from v$active_session_history where sql_id='93sdljkls0300' and in_sql_execution='Y' group by sid,sql_id;

Then, What is the straight method to know how much parsing,executing fetching time?

As far as my knowledge permits and what I know, The good gold old method, sql tracing. Yes, the 10046 or sql_trace provides the information you want.

How to trace the session/sql_id Read Here

Once you trace the session or sql statement, If you look raw trace file,

PARSING IN CURSOR #12 len=169 dep=2 uid=0 oct=3 lid=0 tim=11746424128512 hv=1173719687 ad='2e7c2c50'

select col#, grantee#, privilege#,max(mod(nvl(option$,0),2)) from objauth$ where obj#=:1 and col# is not null

group by privilege#, col#, grantee# order by col#, grantee#

END OF STMT

PARSE #12:c=0,e=1024,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=4,tim=11746424128512

EXEC #12:c=0,e=3072,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=4,tim=11746424131584

FETCH #12:c=0,e=0,p=0,cr=2,cu=0,mis=0,r=0,dep=2,og=4,tim=11746424131584

As you see above the e=1024 for PARSE and e-3072 for Exec and Fetch. This way you can identify where your statement is taking much time in parsing or executing.

-Regards

GeekDBA

Comments are closed.