Dynamic Sampling

Prathap G
A free video tutorial from Prathap G
Technical Enterprise Architect
4.4 instructor rating • 3 courses • 11,088 students

Learn more from the full course

Oracle SQL : Mastering Oracle SQL Performance Tuning

Oracle SQL Query Tuning and Architecture | Solve critical Oracle SQL query performance issues | Advanced SQL Tuning

06:26:10 of on-demand video • Updated October 2019

  • Step by Step approach on troubleshooting badly performing SQL queries.
  • Identify how internal Oracle components affects the performance of the SQL query.
  • Identify potential performance bottlenecks by reading the Explain plan of the queries.
  • Tips and Tricks to make the Oracle SQL query run faster.
  • Compare BAD vs GOOD Queries with Examples.
  • Understand how STATISTICS and CARDINALITY affects the performance of the SQL Query.
  • Implications of JOINS and ACCESS Methods on the performance of the Query.
  • Oracle Performance Tuning on both Oracle11g and Oracle12c Environment.
  • Advanced SQL Query Tuning Steps.
English Hi Guys, Welcome back In this lesson, we will look at one of the powerful mechanisms through which on can tune the SQL query In many cases, there would be a necessity to change the execution plan or the Estimates generated by the Optimizer to improve the performance of the query. Generally, developer finds it very difficult to force the optimizer to change the execution plan of the query or improve the Cardinality estimates, without re doing the structure of the query. In those, cases one can use DYNAMIC SAMPLING. So, what is Dynamic Sampling and how it is used to improve the performance of the query. Before explaining what it is let me demonstrate how it improves the performance of the query and then we will look at what it is ? and how it improved the query performance. Let me first login to SH schema and create a a table called MYTABLE, by selecting the data from ALL_OBJECTS view. As part of this creation I am creating 2 additional columns namely Counter1 and Counter2, that will have data as "Y" or "N". If you carefully, look at the Logic, that I have put, I have made sure that the 2 columns are mutually exclusive. i.e. If Counter1 has the value Y, then counter2 column will have value as N and it the first column has value "N" , then 2nd column will have value "Y" Let us confirm this, by selecting the data from MYTABLE. Let me now create a composite index on Counter1 and Counter2 columns Let me now generate the STATISTICS and HISTOGRAMS all the columns in the MYTABLE. Now, let me run the main query, that selects the data for which Counter1 value is "Y" and counter2 value is "Y". Based, on the condition, we are sure that there isn't any data set that matches this condition and we expect 0 records. Let me run this query We see that it did not return any records. Let us check the execution plan of the query We see from the execution plan that the the optimizer estimated more than 14000 records, where as in reality we got 0 records So, this could prove costly in most of the cases and as a result Optimizer might pick an in efficient execution plan and causes Query slowness. Ok. Now let me execute same query, but this time with DYAMICS SAMPLING Hint. The Dynamic Sampling hint accepts the parameter from 0 to 10 in oracle 11g and 0 to 11 in oracle 12c. Let me run the query with DYNAMIMC sampling value as 0. We see that it returned 0 records Lets check the execution Plan. We see here that the Estimated number i still greater than 14000 Let me run the same query with different DYNAMICS sampling values from 1 to 4 and in all cases the estimated rows is > 14000 Let me run the query with DYNAMIMC sampling value as 5. We see that it returned 0 records Lets check the execution Plan. We see here that the Estimated number is 24. which is very close to actual number. Which is very Good and many times, it would cause the query to change the execution plan of the query and would improve the overall run time of the query Just for the sake of completion I manager to run the query with all the parameters and the results are provided here. You could see that with sampling level from 7 onwards the query estimation became even accurate. So, one trend you would have noticed is that the more the sampling level, the better the estimates it creates. So, now let us answer the question. What is DYNAMICS sampling and how it improved the performance of the query. Dynamic sampling (DS) was introduced to improve the optimizer's ability to generate good execution plans. This feature was enhanced and renamed as Dynamic Statistics in Oracle Database 12c. Goal of DS is to augment optimizer statistics; it is used when regular statistics are not sufficient to get good quality cardinality estimates. It is typically used to compensate for missing or insufficient statistics that would otherwise lead to a very bad plan. For the case where one or more of the tables in the query does not have statistics, DS is used by the optimizer to gather basic statistics on these tables before optimizing the statement. So, there would be an overhead in gathering the basic stats on the table, at the run time on applying the hint. But, in many cases, the benefits are immense when compared to small overhead. The Higher the value you provide as a parameter, then more blocks are read and more accurate stats are generated to improve the query, that is precisely the reason why the estimates improved as we went higher with the parameter value. So, you check the threshold, that works best for the query. In our case, parameter value 5 was Good enough. So, we looked in this course, how dynamic sampling improves the run time of the query. With that let me wind up this lesson and see you all in next lesson.