This panel allows you to add/change/hide PLAN_TABLE columns. Right mouse click on the Explain Plan and select Adjust Content from the drop down menu (see drop down menu illustration below). There are various useful Explain Plan options as well. The steps are numbered in the order in which they are executed. Notice the cost numbers and related objects automatically appear. This illustration shows the default graphical tree Explain Plan. I prefer to use one associated with the database.Ĭlick the Explain Plan button and you will get a nice graphical Explain Plan for your SQL statement. The Toad version works but is rather generic. There are subtle changes in this PLAN_TABLE between versions of Oracle.
Change the Explain Plan from TOAD_PLAN_TABLE to just PLAN_TABLE and you are good to go. To change this to the one Oracle now provides (Oracle10 made it a virtual table, prior to that, it was a script that needed to be run…), go to Toad Options (button or on the menu bar View à T oad Options and pick Oracle General.
Toad has its own, but I prefer not to use it and instead use the one associated with the Oracle database, and Toad lets me do this easily. This is a physical table that needs to exist for these tools to use. Most tools, including Toad, uses a PLAN_TABLE to present the steps of this Explain Plan. The Explain Plan is a visualization of this execution plan. The cost-based optimizer arrives at this (I spend a half a day on this in my SQL Performance Tuning class) via statistics and math. Getting an Explain PlanĮvery SQL statement has an execution plan. In the Related Information at the bottom of this blog, I have included links to better understand the Explain Plan process and the contents of the Explain Plans. This series of blog posts will focus on how Toad can display SQL execution plans and the usefulness of this information. SQL performance is affected by a number of factors that include data distribution, coding style, and available other resources such as indexing and partitioning. The faster this application SQL executes, the more quickly data is returned to your application. There is far more content on this topic than what I can share in this short blog post, but it goes to say that Toad for Oracle is the best SQL Performance Tuning tool available, in my humble opinion. Toad for Oracle exposes the execution plan for any given SQL statement. The Oracle database functions totally on SQL being submitted by your application process (application SQL) and by Oracle itself to process your submitted SQL (dictionary SQL or recursive SQL). Using Oracle Optimizer hints with Toad for Oracle.
First in a three-part series, we'll highlight features in our different editions of Toad for Oracle, starting with Base (Explain Plan), then Xpert and concluding with DBA edition. SQL performance tuning is the focus of this blog and video tutorial series.
0 kommentar(er)
