ORACLE query optimization and gather_plan_statistics hint, oracledbmsgather
Query Optimization Methods and gather_plan_statistics hint:
After 10 Gb, we can use the gather_plan_statistics prompt to learn more about SQL Execution statistics. The usage is as follows:
If the statistics_level parameter is set to ALL, or the executed SQL uses gather_plan_statistics hint, the execution statistics of the SQL statement are found in the v $ SQL _plan_statistics_all view after the SQL statement is executed, such as logical read, physical read, and base. This data is very helpful for performance diagnosis. At the same time, the execution plan in v $ SQL _plan is the actual execution plan used by oracle to execute SQL compared with the execution plan obtained through explain Plan, while the latter may not be the real execution plan; at the same time, some executed SQL statements use variable binding, while oracle usually performs variable binding during SQL parsing. At this time, we cannot use the explain plan to obtain the SQL Execution PLAN, even though the actual execution plan of the SQL statement is different, sometimes we prefer to get the execution plan directly from v $ SQL _plan.
The following combined with tom's article examples to talk about the use of this hint: Original: http://www.oracle.com/technetwork/issue-archive/2014/14-nov/o64asktom-2298498.html
---------------------------------------------------------------------------------
An example of query optimization:
SQL> create table t
As
Select case when mod (rownum, 200000) = 0 then 5
Else mod (rownum, 4)
End X,
Rpad ('x', 100, 'x') data
From dual
Connect by level <= 1000000
/
-- This creates unevenly distributed data (skew data) to confuse the optimizer)
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 preceding query execution plan
SQL> select *
From table (
Dbms_xplan.display_cursor (format => 'allstats la ')
)
/
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-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)
20 rows selected.
-- We can see that the execution plan follows the full table scan and E-Rows, that is, the estimated base number is K and A-rows, that is, the actual return base number is 5, which is quite different (a huge difference ). The reason is that the optimizer is unaware of the unevenly distributed data on column X.
-- In this case, we need to provide a histogram (histogram) on the X column for The optimizer to take full control of the base 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
---- The NO_INVALIDATE parameter is used to invalidate the previous dependent cursor, so that hard parse is executed for the next run of the query)
-- Execute the preceding query operation again to view the running plan 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 | 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)
21 rows selected.
-- The execution plan follows the index range scan and E-Rows is very close to A-Rows, and the execution time is greatly reduced.
-- Here you may be confused: Why didn't a histogram be generated when table information is collected for the first time?
Let's take a look at tom's original words:
This change 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 that table to find out what columns shoshould have histograms collected automatically, based on past query workload. it looked at your predicates and said, "Hmm, these columns are candidates for histograms based on the queries the end users have been running."
It sounds amazing, right? This is the case!
△ Supplement 1: The Pipe function dbms_xplan.display_cursor (format => 'allstats la') must explain the values of the following parameters:
--- Function display_cursor (SQL _id varchar2 default null,
--- Cursor_child_no integer default 0,
--- Format varchar2 default 'typical ')
---
----SQL _id:
Specify the value of SQL _ID as V $ SQL. SQL _ID, V $ SESSION. SQL _ID,
Or V $ SESSION. PREV_ SQL _ID. If this parameter is not specified, the SQL _ID of the last statement is used by default.
----Cursor_child_no:
Specify the subaccount of the SQL cursor. The value is V $ SQL. CHILD_NUMBER or in V $ SESSION. SQL _CHILD_NUMBER, V $ SESSION. PREV_CHILD_NUMBER,
This parameter is valid only when SQL _id is specified. If this parameter is not specified, all child game tags under the specified SQL _id are displayed.
----Format:
--- Specify the output column. The value is taken from the View: v $ SQL _plan_statistics_all.
---
--- IOSTATS:
Assuming that the basic plan statistics have been collected during SQL Execution (use the gather_plan_statistics prompt or set the statistics_level parameter to ALL)
--- This format displays IO statistics for all cursor executions (or only the last cursor executed.
--- MEMSTATS:
--- Assume that PGA memory management is enabled (for example, the pga_aggregate_target parameter is set to a non-0 value). This format allows display of memory management statistics
(For example, Operation execution mode, memory usage, overflow to disk bytes)
--- ALLSTATS: Specifies the Quick Command for 'iostats memstats '.
---
--- LAST:
--- This format only displays statistics of the last executed SQL statement.
---
--- Also, the following two formats are still supported for backward
--- Compatibility: the other two parameters are reserved for backward 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:
You must have the select on v $ SQL _PLAN_STATISTICS_ALL, V $ SQL, and V $ SQL _PLAN permissions.
△ Supplement 2: You can also use another path to query the SQL Execution Plan during running:
HR @ orcl> select/* + gather_plan_statistics emp_plan */count (1) from employees;
COUNT (1)
----------
107
HR @ orcl> SELECT SQL _ID, CHILD_NUMBER from v $ SQL where SQL _TEXT LIKE '% emp_plan %' and SQL _TEXT not like '% V $ SQL % ';
SQL _ID CHILD_NUMBER
-------------------------
2c5q2d8489ybt 0
HR @ orcl> SELECT * from table (DBMS_XPLAN.DISPLAY_CURSOR ('2c5q2d8489ybt ', 0, 'all IOSTATS last '));
PLAN_TABLE_OUTPUT
Certificate -----------------------------------------------------------------------------------------------------------------------------------
SQL _ID 2c5q2d8489ybt, child number 0
-------------------------------------
Select/* + gather_plan_statistics emp_plan */count (1) from employees
Plan hash value: 3580537945
Bytes ----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (% CPU) | E-Time | A-Rows | A-Time | Buffers | Reads |
Bytes ----------------------------------------------------------------------------------------------------------------------------
| 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 |
Bytes ----------------------------------------------------------------------------------------------------------------------------
Query Block Name/Object Alias (identified by operation id ):
-------------------------------------------------------------
1-SEL $1
2-SEL $1/EMPLOYEES @ SEL $1
Column Projection Information (identified by operation id ):
PLAN_TABLE_OUTPUT
Certificate -----------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------
1-(# keys = 0) COUNT (*) [22]
Select 25 rows.
---------------------------------------
Dylan Presents.
