Oracle 23c New Features for DBA'sAs 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
|
||||||||||
| 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
|
||||||||||
| 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:-
|
||||||||||
| 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
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
Follow Me!!!