TDA 2.7 has a button on the editor toolbar to “include actual execution plan”. Where and how do I see the actual execution plan? I tried it against Oracle v9.2 and v11.1.
You will need to have privileges to sys.v_$sqlarea and sys.v_$sql_plan. If you don’t you will see the error in attached screenshot.
If you have the privileges, you need to execute the statement first and then the plan is retrieved right after execution as this is the actual explain plan used when executing. If you want an estimated plan use the other button and it will display in the explain tab as expected.
You can also execute or explain several statements and we calculate the cost of each plan relative to the batch.
Debbie
You will need to have privileges to sys.v_$sqlarea and sys.v_$sql_plan. If you don’t you will see the error in attached screenshot.
If you have the privileges, you need to execute the statement first and then the plan is retrieved right after execution as this is the actual explain plan used when executing. If you want an estimated plan use the other button and it will display in the explain tab as expected.
You can also execute or explain several statements and we calculate the cost of each plan relative to the batch.
Debbie
I don’t get any error message, and I tested that I have select privileges against the sys tables. When I look at the explain plan tab, I cannot tell whether I’m seeing an actual plan or estimated plan. Are you saying that both types of plans display in the same place?
Both types of plans are displayed in the explain plan tab. Which type is displayed is controlled by the sticky button on the tool bar. The default is estimated. Rarely do I see these plans different but how they are generated are very different.
Debbie
It makes sense that the sequence and hierarchy of the plan steps would be the same either way, but thought I’d be able to see a difference between actual and estimated costs. I’m still a novice SQL tuner and I wanted something in TOAD or tda to show actual execution costs. I know there are some Oracle tables and utilities that capture and format execution statistics. I started to explore the TOAD SGA Trace features and that will probably help. Thanks.
If you really want to get into SQL Tuning you should try our SQL Optimizer product.
I did try that out on some other queries, but one thing I learned on this most recent challenge is that the estimated cost can be very misleading in determining the best query path. I had a complex query driven by the results of a subquery that would return 50 rows. The CBO defaulted to some silly table joins and several full table scans of some very large DW partitioned tables, all of which had been analyzed. After extensive hinting, I got the query to run exactly as I want with indexed lookups and no FTS, but the estimated cost was driven into the trillions. Probably storing the interim results as a temporary table and analyzing would have changed the approach, but TOAD would not be able to tell me that (from what I’ve seen so far).
I should add that the original query never completed, after multiple hours of processing, and that a partially hinted version with an estimated cost of 1 million took at least a half hour to get through its full table scans. My optimized version with a 4 trillion cost takes 4 to 5 minutes.
Sounds like you got solution with hints. It is true that TDA does not get into SQL Tuning. Here is a link to a community of a product that does full SQL Tuning.
http://sqloptimizeroracle.inside.quest.com/index.jspa
Debbie
Wow. Shows you that it is worth spending time on tuning. Feels pretty good to be smarter than the optimizer:)
Debbie