SQL PROFILE DEMYSTIFIED : PART – I
In this post and the next one, I will discuss about SQL Profiles – what is their purpose, how to create and use them , how are they stored etc.
PURPOSE OF SQL PROFILE :
It is DBA’s responsibility to maintain SQL performance despite various changes which affect the optimizer. The changes could be change in optimizer statistics, schema changes (creation of index), change in the size of table etc. A DBA should be able to influence the optimizer to use the most efficient plan. To achieve this, Oracle 9i introduced outlines but outlines are more about fixing the plan for a query that optimizer will use. So if there is change in environment, the plan would still be the same. For example I created an outline when size of the table was small and Full table scan was the most efficient. Later as the size of the table grew and I still use the older outline, my execution plan would surely will be not efficient any more. This is just an example situation and things can change in your environment and so outline is not a very good approach for such situations.
HOW DO SQL PROFILES WORK?
To resolve this issue, Oracle 10g introduced SQL Profiles. Profiles store statement statistics in data dictionary in the form of hints as key inputs to the optimizer. The statistics could be used to :
– adjust the No. of rows returned by the query
e.g. 10 times as many as expected are returned from table test
OPT_ESTIMATE(@SEL$1, TABLE, TEST@SEL$1, SCALE_ROWS=10)
– adjust the No. of rows returned through an index scan
e.g. 10 times fewer rows as expected are returned from table test through index test_idx
OPT_ESTIMATE(@SEL$1, INDEX_SCAN, TEST@SEL$1, TEST_IDX, SCALE_ROWS=.1)
– adjust the No. of rows returned from a join
e.g. 4.2 times rows as expected are returned when tables test1 and test2 are joined
OPT_ESTIMATE(@SEL$1, JOIN, (TEST1@SEL$1,TEST2@SEL$1),SCALE_ROWS=4.2)
– provide missing/stale statistics for a
TABLE_STATS(“HR”, “EMPLOYEES”, scale, blocks=10, rows=107)
COLUMN_STATS(“HR”, “EMPLOYEES”, “EMPLOYEE_ID”, scale,length=3 DISTINCT=107 nulls=0 min=100 max=207)
INDEX_STATS(“HR”, “EMPLOYEES”, “EMP_IDX”, scale, blocks=5, rows=107)
– to set optimizer mode
– disable hints present in SQL statement
– to set OPTIMIZER_FEATURES_ENABLE
Whenever the statement is executed, the hints are automatically applied to the statement. Thus the execution plan of the statement is not fixed. Thus profile is more of an advisor. We can say that a SQL profile serves the samed purpose for a SQL statement as is served by optimizer statistics for a table or index. With change in the environment the plans can adapt to the change if the profile is used.
HOW ARE SQL PROFILES CREATED?
Profiles can be created on two occasions :
– When SQL Tuning Advisor is run manually on high resource SQL statements in comprehensive mode
– When SQL Tuning Advisor runs automatically in default maintenance window.
In both the cases, Query optimizer runs in tuning mode and performs additional analysis to check whether the execution plan produced can further be improved. When a statament is executed, optimizer makes estimates regarding the execution of the statement e.g. no. of rows returned by the query, no. of buffer gets, Cost of the query etc. In tuning mode, it additionally verifies whether its estimates are correct by employing various techniques :
– Sampling : A sample of data is taken and predicates are appllied to it. The new estimate is checked against earlier estimate. If difference is substantial, a correction factor is stored as part of the hint.
– Partial execution : A fragment of the SQL statement is executed. This method is preferred when respective predicates provide efficient access paths.
– Past execution history: Past execution history of the statement is used to determine correct settings. Ifthe execution hostory indicates that a SQL statement is executed partially most of the times, hint for OPTIMIZER_MODE= FIRST_ROWS is set instead of ALL_ROWS.
Automatic Tuning optimizer chooses the appropriate estimate validation method.
After the estimates have been verified, optimizer generates auxiliary information and generates a recommendation to the user to accept the profile. Once the user accepts the profile, the auxiliary info in the form of hints is stored persistently in the data dictionary . Whenever the statement is executed , that information is used to generate the most efficient execution plan.
Thus, SQL Profiles , without any modification to the application code, the referenced objects and their statistics, provide the cost based optimizer adjustments for
– Initialization parameters
– Estimated cardinalities
– Object statistucs
SQL profiles provide a solution at the statement level.
In my next post SQL PROFILE DEMYSTIFIED : PART – II , I will demonstrate the creation and use of SQL profile for a poorly tuned SQL statement and also explain the hints the that are stored as part of the profile.
SQL Profiles: Check what they do before accepting
Oracle SQL Profile: Why Multiple OPT_ESTIMATE
Oracle’s OPT_ESTIMATE hint: Usage Guide