dbms_stats.display_cursor vs. sql trace
I’m an enthusiastic evangelist of dbms_xplan.display_cursor function(including other functions of dbms_xplan package) in local Oracle community.
Many thanks given for encouraging them to use dbms_xplan package. Considering the power of the dbms_xplan package, this appreciation is quite natural.
But there is a very frequent question on dbms_xplan.display_cursor fuction:
What’s the deal of dbms_xplan.display_cursor function when we have sql_trace(10046 trace) which seems more powerful?
My answer is:
These two are totally different tools!
Let me make a simple list for the features of each one.
What is so unique with dbms_xplan.display_cursor?
- Easier to use(we don’t need to lookup local folders and FTP download of trace files)
- With the gather_plan_statistics hint, we can gather very useful informations like e-rows, a-rows, buffer gets, physical reads, memory usage, and acutal elapsed time.
- Unless the sql has been flushed out from shared pool, we can extract the plan information with sql_id and child_number whenever we want.
- Since the result of dbms_xplan.display_cursor is pseudo table, we can use them in the query just like any other tables. This means that we can make customized query to meet our complex requirement.
Why do we still need sql_trace?
- The real tracing including all recursive SQLs.
- Tracking the entire steps of parses, executions and fetches.
- Many essential informations including wait events.
We use dbms_xplan.display_cursor to retrieve the execution plans of the cursors loaded onto shared pool. On the contrary, sql_trace is used to trace every inch of the query execution. With gather_plan_statisitcs hint enabled, dbms_xplan.display_cursor function can be used as an alternative to sql_trace, but only in limited way.
The point is that we have 2 totally different tools which help us to analyze the performance of query execution. It’s up to you to use them in the right situation.