Autotrace - Oracle SQL Tutorial
Autotrace is a free tutorial by Oracle Master Training � 60,000+ Students Worldwide from Oracle SQL course
Link to this course(Special Discount):
https://www.udemy.com/course/sql-perf...
This is the best Oracle SQL Course
Course summary:
Learn How to Solve Critical Performance Problems with Step by Step Approach!
Learn Advanced Indexing Techniques for Ultimate Database Performance!
Learn How to Use Various Performance Tuning Tools for In-Depth Assistance!
Maximize the Query Performance by Using Advanced Tuning Tecniques!
Learn Oracle Database Architecture by ""Tuning"" aspects.
Identify and Optimize the Performance of poorly performing (bad) SQLs.
Understand SQL Tuning Terminologies
Learn How to Create ""Better"" Queries
Learn How to Prioritize your SQL Tuning Efforts.
Learn the Details of How an SQL Code is Interpreted
Learn Useful Hints to improve Performance of your Queries
Learn & Improve How to Use Oracle Optimizer and Execution Plans Well
Various Tips and Tricks to make the Oracle SQL queries run faster.
Oracle Performance Tuning on both Oracle11g and Oracle12c Environment.
Learn Efficient Schema Design
Lifetime Support from the Authors.
Weekly Quizzes & Assesment Tests
Working materials and always up to date!
English
Hi. In this lecture, you will learn how to get the execution plan with using autotrace facility. In the previous lecture, you learned how to create the explain plan with the explain plan command and SQL developer. However, you know that, the explain plan is not the exact execution plan of a query. So it may change when you run the code by the optimizer. Because, explain plan is created with the statistics and based on the estimates. However, while executing the code, it may find out that, that plan is not so good at some point. So it changes the plan based on the new information. I don�t want to go deeper, not to confuse you. Just know that, the explain plan may change when you run the code. So, how can we find the exact execution plan? Of course, by running it. And after running a code, you can check it from the shared SQL area. However, we have another way to do it. It is by using ""autotrace"". When we enable the autotrace feature, Oracle server traces our query and produces the execution plan and the statistics. Autotrace can trace our query with different aspects. We have different usages of autotrace syntax. The first one is: SET AUTOTRACE ON; If we use it with ON, it will trace ""everything"". Rows, explain plan, statistics, etc. However, you can use the explain or statistics keywords to specify if it will return the explain plan or statistics. If you write ""set autotrace on explain, it will return the rows and the explain plan. If you write ""set outotrace on statistics, it will return the rows and the statistics. The next usage is, using with traceonly option. After the autotrace, you can write ""trace"" or ""traceonly"". They are the same. If you write ""set autotrace traceonly, it will get only the explain plan and the statistics. It will not show you the rows. If you are not interested in rows, you can write ""trace"" or ""traceonly"" option. Furthermore, trace option also has two more options. By writing ""explain"" or ""statistics"" keywords, you can specify if it will return only the statistics or only the explain plan. If you finished your job, you should close the autotrace. To close the autotrace, you simply write ""set autotrace off"". Great! We know that, to autotrace a code, we need to run a code. Besides, we need to have a ""plan_table"" for it. So, when we run a code by autotracing it, it writes the explain plan to the plan_table. However, there is one important thing here. Even if it runs the statement to trace it, sometimes, it will not have the exact execution plan if you used the bind variables in your code that needs to perform an implicit type conversion of dates. So, even if it is much more reliable than the explain plan command, if you used bind variables, it might not have the exact execution plan. I said might, because, even if you run the explain plan command, or autotrace, you may have the exact execution plan, too. If the estimates are true, it will mostly get the exact execution plan. But sometimes, it may not. Another cons for the autotrace against the explain plan command is, you need to run the code. So, if you need to check the explain plan of a DML code, you need to run it also. So you will insert, update or delete something. And sometimes, it might have some side effects like unnecessary logging, locking some rows, etc. So, if you could tune your code with the explain plan command, I highly suggest you to do like that. If you cannot succeed in this way, then you can use the autotrace method. But, if you