A free video tutorial from Database Masters Training | 250,000+ Students Worldwide
Oracle Architect & Best Selling Instructor
Rating: 4.5 out of 5Instructor rating
7 courses
250,801 students

Learn more from the full course

Oracle SQL Performance Tuning Masterclass (2024)

Become an Expert on Oracle SQL Tuning and Solve All The Performance Problems of Your SQL Queries and the Database!

20:07:21 of on-demand video • Updated April 2024

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!
Hi. In this lecture, you will see what is V$SQL_PLAN view, what is there in it, and how we can use it. As you know, V$SQL views are considered as dynamic performance views. Oracle has a lot of performance views. And some of them are very useful for some cases. Actually, it is not possible to explain them all in one course. It is mostly a DBA issue. Maybe most of the DBAs do not know most of them, too. Actually, it is not so possible to memorize every detail of the database. So when you need it, it is enough to know that, this view includes data about this issue. So you analyze that view and do what you need to do. So, except for the V$SQL_PLAN view, there are a couple more performance views that might be helpful for you. These views are about the execution of our queries and stored in the shared SQL area. I will not go so deep for these, but I will explain to you what they have basically. Actually, they have lots of similar data in them, but also have some specific data, too. The first one is, V$SQLAREA view. This view has the statistics of each SQL that run before. This view particularly has the data about memory usage of the query when it is executed. V$SQL_WORKAREA view shows more detailed work areas in the memory. If our query used a hash join, it shows the hash join area. If our query used a sort, it shows the sort area. Actually, V$SQLAREA has similar data, but it is an overview of the V$SQL_WORKAREA view. So this view has more detailed information about the work areas. The other one is V$SQL view. We may use this view so many times in tuning issues. Because it has a lot of data about our queries. When we run our query, we can simply check here for the costs, parse counts, execution counts, logical and physical reads, writes etc. It has a lot of statistics. This view is updated after the query execution. But, if it takes long, it is updated in every "5" seconds. So you can analyze a long-running query while it is still being executed. The other view is the V$SQL_PLAN view. This view has the execution plans of the queries including the estimated statistics for each row source. You will see this more detailed in this lecture. Another one is the V$SQL_PLAN_STATISTICS view. Normally, the statistics level is set to the typical. If you set it to all, this view will have some additional execution statistics. Or you can use the gather_plan_statistics hint to get an individual query statistics. If you query from V$PLAN_STATISTICS_ALL view, you will see a side by side comparisons of optimizer estimates with the actual execution statistics for the row sources. Alright. These views have a lot of information about our queries. But, they might be a bit complicated at first. So if you intend to check out these, you need to know what they have actually. Great! In this lecture, you will see the V$SQL_PLAN view more detailed since it has the execution plans which stored in the library cache. By the way, I think now you understand better why we explained the architecture at first. Because, since you know what the library cache, shared SQL area etc are, so you understand the terms better. So again, better knowledge will lead you to a better tuning strategy. In the previous lectures, we have mentioned that, once we execute a query, its actual execution plan is stored in the library cache inside the shared pool. Not only we can analyze it from there, but also the database itself uses it to eliminate hard parses. As you remember, when we run a query, the database server first checks, if this query is executed before. If yes, its execution plan must be in the library cache. So, the database server gets the execution plan from this view and uses it. So this increases the performance so much by eliminating the parse cost. Great! So let’s continue with the details of V$SQL_PLAN view. In the shared pool, actual execution plans are stored in the V$SQL_PLAN view. We can directly query it from this view, and analyze. But before analyzing, let me introduce this table a bit deeper. This view is very similar to plan_table. The difference is, plan table stores the estimated plan, but the v$sql_plan view has the actual plan used while the execution. If there is a difference between them, the one in V$SQL_PLAN is considered true. It is very connected with the V$SQL view. The V$SQL_PLAN view does not have the actual SQL text, but V$SQL view has. Besides, these two views have a lot of related columns. So we generally use them together. You can use table function and dbms_xplan package to get a good output for the execution plan in the V$SQL_PLAN view as we did in the previous lectures. However, as you can see, this time our structure is a bit different. As you can see, we used display_cursor function instead of display function. And as the parameter, we passed the id of that SQL. Great! Now, let’s open up the SQL Developer and see how we can analyze the actual execution plan stored in the library cache. Now, let’s first get the execution plan of that query by the SQL Developer tool and pin it to compare with the actual execution plan. Great! Now, let's pin it. Alright. As I said before, to be able to see the exact execution plan, we need to run our code first. So let’s run it. Perfect! Now, as you know, to get the exact execution plan, we need to know its SQL id first. So, how can we find our query and its SQL id? Of course, by querying from the V$SQL view. So let’s do it. As you can see, there are a lot of SQLs here. So how can we extract out query from here? Of course, by searching our SQL text from the SQL_TEXT or SQL_FULLTEXT columns here. So, let’s make a search with the first one. Now, let’s run this code and see if we can find. As you can see, we could not find. In the next query, I typed it without new lines. Only the spaces. So let’s run this one. As you can see, this time we could see our result. However, there might be several queries like this. So maybe it will be hard to distinguish which one is our query. To get it, we can write a comment into our code that will specify our query. As you can see in this code, we added a simple comment into our query. This will not affect the execution of our query, but it will be written in the SQL text. So we can easily select our query from the V$SQL view by using that comment. Let’s run it first. And query it from our view again. As you can see, we could find it much easier. Actually, there will be more than one results. Because when you run the V$SQL query to search, it will also be inserted into the V$SQL view, too. Besides, if you get the explain plan of it, or if you run that code again before, there will be similar codes here. But anyway, you will find yours easily. Ours is in here. Great! Now, let’s copy our SQL id and query from V$SQL_PLAN view again but with this id. So, let’s simply paste our id into that query. As you can see, we have 12 rows here. That means, 12 operations are done for this query. Besides, there are a lot of columns here like hash value, SQL_ID, operation type, costs of each step, optimizer level, etc. But, no need to go deeper for this. However, we can find out the explain plan with a better method as you know. So, let’s paste our SQL ID into here and run it. As you can see, this execution plan is the actual execution plan. This can be different than the explain plan stored in the plan table. However, this is a very small database and the changes are so less, so the statistics are true and the optimizer generates very accurate estimates. As you can see, the execution plans and the values are the same. Actually, if the estimates wouldn’t be so accurate, Oracle would not let us see the estimates and tune our codes with these explain plans. They are really accurate. However, when the database grows so much and so many changes occur in time, it might not generate accurate explain plans. At that time, you can use the SQL_PLAN view. Actually, if you are sure that your query is executed not long ago, and if you are sure that you did not change anything after the last execution, instead of taking a new explain plan, you can directly get from the V$SQL_PLAN view. Alright. In this lecture, you learned what is there in the v$sql_plan view and how to read from it. So this is the end of this lecture. See you in the next lectures.