Query optimization methods and Gather_plan_statistics hint:
after 10g we can use gather_plan_statistics hints to learn more about SQL execution statistics, using the following methods:
If the Statistics_level parameter is set to all, or if the SQL executed uses gather_plan_statistics hint, after SQL executes, the V$sql_plan_statistics_ In the all view, SQL execution statistics are found, such as logical reads, physical reads, cardinality, and so on. These data are very helpful for performance diagnosis. At the same time, the execution plan in V$sql_plan, compared to the execution plan obtained through explain plan, is the execution plan that Oracle actually uses when executing SQL, while the latter may not be a true execution plan; Executed SQL uses bound variables, and Oracle usually does a binding variable snooping when parsing SQL, and we can't use the explain plan to get to that SQL execution plan, even if it's not the same as the actual execution plan for that SQL. So sometimes we prefer to get the execution plan directly from the V$sql_plan.
The following is an example of Tom's article about this hint usage: Original: http://www.oracle.com/technetwork/issue-archive/2014/14-nov/o64asktom-2298498.html
---------------------------------------------------------------------------------
An example of query tuning:
Sql> CREATE TABLE T
As
Select Case while mod (rownum,200000) = 0 Then 5
else MoD (rownum,4)
End X,
Rpad (' x ', +, ' x ') data
From dual
Connect by Level <= 1000000
/
--This is the creation of unevenly distributed data for the purpose of confusing the optimizer. (Skew data)
Sql> CREATE index T_idx on t (x);
Index created.
sql> exec dbms_stats.gather_table_stats (user, ' T ');
PL/SQL procedure successfully completed.
Sql> Select X, COUNT (*)
From T
GROUP BY X
ORDER by X
/
X COUNT (*)
——————————— ———————————
0 249995
1 250000
2 250000
3 250000
5 5
Sql> Select/*+ gather_plan_statistics */
COUNT (data)
From T
where x = 5;
/
--View the above query execution plan
Sql> SELECT *
From table (
Dbms_xplan.display_cursor (format=> ' Allstats last ')
)
/
Plan_table_output
——————————————————————————————————
sql_id CDWN5MQB0CPG1, child number 0
——————————————————————————————————
Select/*+ Gather_plan_statistics */
COUNT (data)
From T
where x = 5
Plan Hash value:2966233522
———————————————————————————————————————————————————————————————————————————
| Id | Operation | Name | Starts | E-rows | A-rows | A-time |
———————————————————————————————————————————————————————————————————————————
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.08 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.08 |
|* 2 | TABLE ACCESS full| T | 1 | 200k| 5 |00:00:00.08 |
———————————————————————————————————————————————————————————————————————————
predicate information (identified by Operation ID):
———————————————————————————————————————————————————————————————————————————
2-filter ("X" =5)
Rows selected.
--you can see that the execution plan goes through the full table scan and e-rows, that is, the estimated base is 200K and a-rows, that is, the actual return cardinality of 5 is quite large (a huge difference). The reason is that the optimizer is unaware of the uneven distribution of data on the X column.
--At this point, we need to provide the histogram (histogram) on the X column to let the optimizer take control of the overall cardinality information.
Sql> Select histogram
From User_tab_columns
WHERE table_name = ' T '
and column_name = ' X ';
Histogram
---------------
NONE
sql> exec dbms_stats.gather_table_stats (user, ' T ',No_invalidate=>false);
Sql> Select histogram
From User_tab_columns
WHERE table_name = ' T '
and column_name = ' X ';
Histogram
---------------
FREQUENCY
----Here is to invalidate the previous dependent cursor with the no_invalidate parameter, ensuring that the next run of the query performs a hard parse
--Perform the previous query operation again to view the run-time schedule information
Plan_table_output
——————————————————————————————————
sql_id CDWN5MQB0CPG1, child number 0
——————————————————————————————————
Select/*+ Gather_plan_statistics */
COUNT (data)
From T
where x = 5
Plan Hash value:1789076273
————————————————————————————————————————————————————————————————————————
| Id | Operation | Name | Starts | E-rows | A-rows |
————————————————————————————————————————————————————————————————————————
| 0 | SELECT STATEMENT | | 1 | | 1 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |
| 2 | TABLE ACCESS by INDEX rowid| T | 1 | 182 | 5 |
|* 3 | INDEX RANGE SCAN | T_idx | 1 | 182 | 5 |
————————————————————————————————————————————————————————————————————————
--------------
| A-time |
--------------
|00:00:00.01 |
|00:00:00.01 |
|00:00:00.01 |
|00:00:00.01 |
predicate information (identified by Operation ID):
————————————————————————————————————————————————————————————————————————
3-access ("X" =5)
Rows selected.
-At this point the execution plan goes through the index range scan and e-rows is already close to a-rows, and the execution time is greatly reduced.
--Here you may be whispered: I didn't generate a histogram when I first collected the table information.
Look at Tom's exact words:
This "is" likely due to the default value of the method_opt parameter used with Dbms_stats. In Oracle Database 10g, the method_opt parameter defaults to a SIZE AUTO. After you ran a query, the database remembered the predicates and updated a dictionary table, SYS. col_usage$. Then, the next time you ran dbms_stats to gather statistics on these tables, dbms_stats queried this table to find out WHA T columns should has histograms collected automatically, based on past query workload. It looked at your predicates and said, "Hmm, these columns is candidates for histograms based on the queries the end user S has been running. "
Sounds amazing, doesn't it? But that's the truth!
Supplement 1: About the pipe function dbms_xplan.display_cursor (format=> ' Allstats last ') to explain the following values of the parameters:
---function display_cursor (sql_id varchar2 default NULL,
---cursor_child_no integer default 0,
---format varchar2 default ' typical ')
---
----sql_id:
Specifies that the sql_id value is v$sql. sql_id, V$session. SQL_ID,
or v$session. PREV_SQL_ID, the default is the last executed statement if not specified sql_id
----cursor_child_no:
Specifies the child number of the SQL cursor, with a value of v$sql. Child_number or in V$session. Sql_child_number,v$session. Prev_child_number,
This parameter is valid only if the sql_id is specified. If not specified, all child cursors under the specified sql_id are displayed.
----format:
--- Specifies the output column, which is taken from the view: V$sql_plan_statistics_all.
---
---iostats:
Assume that basic plan statistics are collected when SQL is executed (using the GATHER_PLAN_STATISTICS hint or setting the Statistics_level parameter to all)
---This format will show IO statistics for all cursors executed (or only the last executed cursor).
---memstats:
--- Assuming that the PGA Memory management is on (for example: the Pga_aggregate_target parameter is set to a value other than 0), this format allows the display of memory management statistics
(Example: Operation execution mode, memory usage, overflow to disk bytes)
---allstats: Specify shortcut commands for ' iostats memstats '
---
---Last:
---This format is to specify that only the statistics of the last executed SQL be displayed
---
---Also, the following, formats is still supported for backward
---Compatibility: In addition, the following 2 parameters are reserved for backwards compatibility
---
---' runstats_tot ': Same as ' iostats ', i.e. displays IO statistics
---for all executions of the specified cursor.
---' runstats_last ': Same as ' iostats last ', i.e displays the runtime
---statistics for the last execution of the cursor.
---permission requirements:
Must have: SELECT on V$sql_plan_statistics_all,v$sql, and V$sql_plan permissions.
Supplement 2: You can also query the SQL Runtime execution plan using a different path:
[Email protected]> Select/*+ gather_plan_statistics emp_plan*/count (1) from employees;
COUNT (1)
----------
107
[e-mail protected]> SELECT sql_id,child_number from V$sql WHERE sql_text like '%emp_plan% ' and sql_text don't like '%v$sq l% ';
sql_id Child_number
------------- ------------
2C5Q2D8489YBT 0
[Email protected]> SELECT * from TABLE (Dbms_xplan. Display_cursor (' 2c5q2d8489ybt ', 0, ' all Iostats last '));
Plan_table_output
--------------------------------------------------------------------------------------------------------------- --------------------
sql_id 2C5Q2D8489YBT, child number 0
-------------------------------------
Select/*+ gather_plan_statistics emp_plan*/count (1) From Employees
Plan Hash value:3580537945
--------------------------------------------------------------------------------------------------------------- -------------
| Id | Operation | Name | Starts | E-rows | Cost (%CPU) | E-time | A-rows | A-time | Buffers | Reads |
--------------------------------------------------------------------------------------------------------------- -------------
| 0 | SELECT STATEMENT | | 1 | | 1 (100) | | 1 |00:00:00.02 | 1 | 1 |
| 1 | SORT AGGREGATE | | 1 | 1 | | | 1 |00:00:00.02 | 1 | 1 |
| 2 | INDEX Full scan| Emp_email_uk | 1 | 107 | 1 (0) | 00:00:01 | 107 |00:00:00.02 | 1 | 1 |
--------------------------------------------------------------------------------------------------------------- -------------
Query Block name/object Alias (identified by Operation ID):
-------------------------------------------------------------
1-sel$1
2-sel$1/[Email protected]$1
Column Projection Information (identified by Operation ID):
Plan_table_output
--------------------------------------------------------------------------------------------------------------- --------------------
-----------------------------------------------------------
1-(#keys =0) COUNT (*) [22]
25 rows have been selected.
---------------------------------------
Dylan presents.
Oracle query optimization and gather_plan_statistics hint
