RSS Feed

Method R Discussion Group

Can I run buffer load query, the explain plan, and execution trace in one session as long as the trace is last, and I exit the session?

Yes. 

You can request an explain plan without actually running the query.  Then, you can turn tracing on and execute your test SQL in your current session.  If that query requires blocks to be loaded into the buffer cache for the first time (I assume that's what you mean by "run buffer load"), then that would happen and the trace would capture this activity.  When the query completes, the STAT lines will be emitted to the trace file when you disconnect your session.

Here's an example (I've just clipped the steps down to only show you the parts you mentioned in your question).  Although the output isn't formatted well due to the limited page width, you can copy/paste it into an editor to see it a bit better.

-- Here's my test query

SQL> get emp
  1  select *
  2    from emp
  3*  where empno between 100 and 250
SQL>

-- Use DBMS_XPLAN to get the plan

Plan hash value: 275785152

--------------------------------------------------------------------------------------------
| Id  | Operation                  | Name        | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT            |              |    1 |    39 |    2  (0)| 00:00:01 |
|  1 |  TABLE ACCESS BY INDEX ROWID| EMP          |    1 |    39 |    2  (0)| 00:00:01 |
|*  2 |  INDEX RANGE SCAN          | EMP_EMPNO_PK |    1 |      |    1  (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  2 - access("EMPNO">=100 AND "EMPNO"<=250)
 
-- turn on trace here
-- run the test SQL statement
-- disconnect
-- get the trace file

PARSING IN CURSOR #15 len=52 dep=0 uid=64 oct=3 lid=64 tim=174947940020 hv=984598910 ad='2cd3408c'
select *
  from emp
 where empno between 100 and 250
END OF STMT
PARSE #15:c=46875,e=137056,p=8,cr=95,cu=0,mis=1,r=0,dep=0,og=1,tim=174947940012
BINDS #15:
EXEC #15:c=0,e=118,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=174947940652
WAIT #15: nam='SQL*Net message to client' ela= 5 driver id=1413697536 #bytes=1 p3=0 obj#=57387 tim=174947940773
WAIT #15: nam='db file sequential read' ela= 15850 file#=4 block#=19900 blocks=1 obj#=59163 tim=174947956779
FETCH #15:c=0,e=16084,p=1,cr=1,cu=0,mis=0,r=0,dep=0,og=1,tim=174947956971
WAIT #15: nam='SQL*Net message from client' ela= 626 driver id=1413697536 #bytes=1 p3=0 obj#=59163 tim=174947957821
STAT #15 id=1 cnt=0 pid=0 pos=1 obj=59162 op='TABLE ACCESS BY INDEX ROWID EMP (cr=1 pr=1 pw=0 time=16087 us)'
STAT #15 id=2 cnt=0 pid=1 pos=1 obj=59163 op='INDEX RANGE SCAN EMP_EMPNO_PK (cr=1 pr=1 pw=0 time=16070 us)'


The Hotsos Education team has developed a set of scripts, called the Hotsos Test Harness, that can be used to do all these steps in a consolidated, very easy way.  We currently give away these scripts to students of our Optimizing SQL course and have plans to make the scripts publicly available (without having to attend the class) very soon.