Join Methods : Nested Loop Join, Hash Join, Sort Merge Join
Nested Loops Joins :
Nested loops join an outer data set to an inner data set. For each row in the outer data set that matches the single-table predicates, the database retrieves all rows in the inner data set that satisfy the join predicate. If an index is available, then the database can use it to access the inner data set by rowid.
Hash Joins :
The database uses a hash join to join larger data sets. The optimizer uses the smaller of two data sets to build a hash table on the join key in memory, using a deterministic hash function to specify the location in the hash table in which to store each row. The database then scans the larger data set, probing the hash table to find the rows that meet the join condition.
Sort Merge Joins :
A sort merge join is a variation on a nested loops join. If the two data sets in the join are not already sorted, then the database sorts them. These are the SORT JOIN operations. For each row in the first data set, the database probes the second data set for matching rows and joins them, basing its start position on the match made in the previous iteration. This is the MERGE JOIN operation.
Join Types : Inner Join, Outer Join, Semi Join, Anti Join, Cartesian Join (A join type is determined by the type of join condition.)
Semi joins :
A semi join is a join between two data sets that returns a row from the first set when a matching row exists in the subquery data set. The database stops processing the second data set at the first match. Thus, optimization does not duplicate rows from the first data set when multiple rows in the second data set satisfy the subquery criteria.
Outer Joins :
An outer join returns all rows that satisfy the join condition and also rows from one table for which no rows from the other table satisfy the condition. Thus, the result set of an outer join is the superset of an inner join.
Inner Joins :
An inner join (sometimes called a simple join) is a join that returns only rows that satisfy the join condition. Inner joins are either equijoins or non equijoins.
Access Methods :
Optimizer statistics include the following:
Table statistics
Number of rows
Number of blocks
Average row length
Column statistics
Number of distinct values (NDV) in a column
Number of nulls in a column
Data distribution (histogram)
Extended statistics
Index statistics
Number of leaf blocks
Number of levels
Index clustering factor
System statistics
I/O performance and utilization
CPU performance and utilization
Table Statistics
Table statistics contain metadata that the optimizer uses when developing an execution plan.
In Oracle Database, table statistics include information about rows and blocks.
The optimizer uses these statistics to determine the cost of table scans and table joins. The database tracks all relevant statistics about permanent tables. For example, table statistics stored in DBA_TAB_STATISTICS track the following:
Number of rows
The database uses the row count stored in
DBA_TAB_STATISTICSwhen determining cardinality.- Average row length
Number of data blocks
The optimizer uses the number of data blocks with the
DB_FILE_MULTIBLOCK_READ_COUNTinitialization parameter to determine the base table access cost.Number of empty data blocks
DBMS_STATS.GATHER_TABLE_STATS commits before gathering statistics on permanent tables.
SELECT NUM_ROWS, AVG_ROW_LEN, BLOCKS,
EMPTY_BLOCKS, LAST_ANALYZED
FROM DBA_TAB_STATISTICS
WHERE OWNER='SH'
AND TABLE_NAME='CUSTOMERS';
EMPTY_BLOCKS, LAST_ANALYZED
FROM DBA_TAB_STATISTICS
WHERE OWNER='SH'
AND TABLE_NAME='CUSTOMERS';
NUM_ROWS AVG_ROW_LEN BLOCKS EMPTY_BLOCKS LAST_ANAL
---------- ----------- ---------- ------------ ---------
55500 189 1517 0 25-MAY-17
---------- ----------- ---------- ------------ ---------
55500 189 1517 0 25-MAY-17
Column Statistics
Column statistics track information about column values and data distribution.
The optimizer uses column statistics to generate accurate cardinality estimates and make better decisions about index usage, join orders, join methods, and so on. For example, statistics in DBA_TAB_COL_STATISTICS track the following:
Number of distinct values
Number of nulls
High and low values
Histogram-related information
The optimizer can use extended statistics, which are a special type of column statistics. These statistics are useful for informing the optimizer of logical relationships among columns.
Index Statistics
The index statistics include information about the number of index levels, the number of index blocks, and the relationship between the index and the data blocks. The optimizer uses these statistics to determine the cost of index scans.
The DBA_IND_STATISTICS view tracks index statistics.
Statistics include the following:
Levels
The
BLEVELcolumn shows the number of blocks required to go from the root block to a leaf block. A B-tree index has two types of blocks: branch blocks for searching and leaf blocks that store values. See Oracle Database Concepts for a conceptual overview of B-tree indexes.Distinct keys
This columns tracks the number of distinct indexed values. If a unique constraint is defined, and if no
NOT NULLconstraint is defined, then this value equals the number of non-null values.Average number of leaf blocks for each distinct indexed key
Average number of data blocks pointed to by each distinct indexed key
SELECT INDEX_NAME, BLEVEL, LEAF_BLOCKS AS "LEAFBLK", DISTINCT_KEYS AS "DIST_KEY",
AVG_LEAF_BLOCKS_PER_KEY AS "LEAFBLK_PER_KEY",
AVG_DATA_BLOCKS_PER_KEY AS "DATABLK_PER_KEY"
FROM DBA_IND_STATISTICS
WHERE OWNER = 'SH'
AND INDEX_NAME IN ('CUST_LNAME_IX','CUSTOMERS_PK');
INDEX_NAME BLEVEL LEAFBLK DIST_KEY LEAFBLK_PER_KEY DATABLK_PER_KEY
-------------- ------ ------- -------- --------------- ---------------
CUSTOMERS_PK 1 115 55500 1 1
CUST_LNAME_IX 1 141 908 1 10
For a B-tree index, the index clustering factor measures the physical grouping of rows in relation to an index value The index clustering factor helps the optimizer decide whether an index scan or full table scan is more efficient for certain queries). A low clustering factor indicates an efficient index scan. A clustering factor that is close to the number of blocks in a table indicates that the rows are physically ordered in the table blocks by the index key. If the database performs a full table scan, then the database tends to retrieve the rows as they are stored on disk sorted by the index key. A clustering factor that is close to the number of rows indicates that the rows are scattered randomly across the database blocks in relation to the index key. If the database performs a full table scan, then the database would not retrieve rows in any sorted order by this index key.
The clustering factor is a property of a specific index, not a table. If multiple indexes exist on a table, then the clustering factor for one index might be small while the factor for another index is large. An attempt to reorganize the table to improve the clustering factor for one index may degrade the clustering factor of the other index.
SELECT table_name, num_rows, blocks
FROM user_tables
WHERE table_name='CUSTOMERS';
TABLE_NAME NUM_ROWS BLOCKS
------------------------------ ---------- ----------
CUSTOMERS 55500 1486
CREATE INDEX CUSTOMERS_LAST_NAME_IDX ON customers(cust_last_name);
SELECT index_name, blevel, leaf_blocks, clustering_factor
FROM user_indexes
WHERE table_name='CUSTOMERS'
AND index_name= 'CUSTOMERS_LAST_NAME_IDX';
INDEX_NAME BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR
------------------------------ ---------- ----------- -----------------
CUSTOMERS_LAST_NAME_IDX 1 141 9859
Histograms
A histogram is a special type of column statistic that provides more detailed information about the data distribution in a table column. A histogram sorts values into "buckets," as you might sort coins into buckets.
A histogram is a special type of column statistic that provides more detailed information about the data distribution in a table column. A histogram sorts values into "buckets," as you might sort coins into buckets.
Based on the NDV and the distribution of the data, the database chooses the type of histogram to create. (In some cases, when creating a histogram, the database samples an internally predetermined number of rows.) The types of histograms are as follows:
Frequency histograms and top frequency histograms
Height-Balanced histograms (legacy)
Hybrid histograms
By default the optimizer assumes a uniform distribution of rows across the distinct values in a column.
For columns that contain data skew (a nonuniform distribution of data within the column), a histogram enables the optimizer to generate accurate cardinality estimates for filter and join predicates that involve these columns.
For columns that contain data skew (a nonuniform distribution of data within the column), a histogram enables the optimizer to generate accurate cardinality estimates for filter and join predicates that involve these columns.
For example, a California-based book store ships 95% of the books to California, 4% to Oregon, and 1% to Nevada. The book orders table has 300,000 rows. A table column stores the state to which orders are shipped. A user queries the number of books shipped to Oregon. Without a histogram, the optimizer assumes an even distribution of 300000/3 (the NDV is 3), estimating cardinality at 100,000 rows. With this estimate, the optimizer chooses a full table scan. With a histogram, the optimizer calculates that 4% of the books are shipped to Oregon, and chooses an index scan.
BEGIN
DBMS_STATS.GATHER_TABLE_STATS (ownname=> 'SH',tabname=> 'COUNTRIES',method_opt => 'FOR COLUMNS COUNTRY_SUBREGION_ID');
END;
SELECT TABLE_NAME, COLUMN_NAME, NUM_DISTINCT, HISTOGRAM
FROM USER_TAB_COL_STATISTICS
WHERE TABLE_NAME='COUNTRIES'
AND COLUMN_NAME='COUNTRY_SUBREGION_ID';
TABLE_NAME COLUMN_NAME NUM_DISTINCT HISTOGRAM
---------- -------------------- ------------ ---------------
COUNTRIES COUNTRY_SUBREGION_ID 8 FREQUENCY
COL OWNER FORMAT a5
COL TABLE_NAME FORMAT a15
COL PREFERENCE_NAME FORMAT a20
COL PREFERENCE_VALUE FORMAT a30
SELECT * FROM DBA_TAB_STAT_PREFS;
OWNER TABLE_NAME PREFERENCE_NAME PREFERENCE_VALUE
----- --------------- -------------------- -----------------------------
OE CUSTOMERS NO_INVALIDATE DBMS_STATS.AUTO_INVALIDATE
SH SALES STALE_PERCENT 13
The
DBMS_XPLAN package supplies five table functions:-
DISPLAY- to format and display the contents of a plan table. -
DISPLAY_AWR- to format and display the contents of the execution plan of a stored SQL statement in the AWR. -
DISPLAY_CURSOR- to format and display the contents of the execution plan of any loaded cursor. -
DISPLAY_SQL_PLAN_BASELINE- to display one or more execution plans for the SQL statement identified by SQL handle -
DISPLAY_SQLSET- to format and display the contents of the execution plan of statements stored in a SQL tuning set.
| Format Constants | |
| ALIAS | If relevant, shows the "Query Block Name / Object Alias" section |
| ALLSTATS | A shortcut for 'IOSTATS MEMSTATS' |
| BYTES | If relevant, shows the number of bytes estimated by the optimizer |
| COST | If relevant, shows optimizer cost information |
| IOSTATS | Assuming that basic plan statistics are collected when SQL statements are executed (either by using the gather_plan_statistics hint or by setting the parameter statistics_level to ALL), this format will show IO statistics for ALL (or only for the LAST as shown below) executions of the cursor |
| LAST | By default, plan statistics are shown for all executions of the cursor. The keyword LAST can be specified to see only the statistics for the last execution |
| MEMSTATS | Assuming that PGA memory management is enabled (that is, pga_aggregate_target parameter is set to a non 0 value), this format allows to display memory management statistics (for example, execution mode of the operator, how much memory was used, number of bytes spilled to disk, and so on). These statistics only apply to memory intensive operations like hash-joins, sort or some bitmap operators |
| NOTE | If relevant, shows the note section of the explain plan |
| PARALLEL | If relevant, shows PX information (distribution method and table queue information) |
| PARTITION | If relevant, shows partition pruning information |
| PREDICATE | If relevant, shows the predicate section |
| PROJECTION | If relevant, shows the projection section |
| REMOTE | If relevant, shows the information for distributed query (for example, remote from serial distribution and remote SQL) |
| ROWS | If relevant, shows the number of rows estimated by the optimizer |
| RUNSTATS_LAST | Same as IOSTATS LAST: displays the runtime stat for the last execution of the cursor |
| RUNSTATS_TOT | Same as IOSTATS: displays IO statistics for all executions of the specified cursor |
dbms_xplan.display function has four basic levels
BASIC TYPICAL (default) SERIAL ALL
SELECT * FROM table(dbms_xplan.display);
SELECT * FROM table(dbms_xplan.display(null,null,'ALL'));
SELECT * FROM table(dbms_xplan.display(null,null,'BASIC +COST'));
SELECT * FROM table(dbms_xplan.display(null,null,'TYPICAL -BYTES -ROWS'));
To display runtime statistics for the cursor included in the preceding statement:
SELECT * FROM table ( DBMS_XPLAN.DISPLAY_CURSOR('13adnwhahfkqf', NULL, 'ALLSTATS LAST');
To display the execution plan of the last SQL statement executed by the current session:
SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR);
To display the execution plan of all children associated with the SQL ID 'atfwcg8anrykp':
SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR('13adnwhahfkqf'));
SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR('6bv38j6jvmt7g', format => 'TYPICAL +PEEKED_BINDS'));
SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR('6BV38J6JVMT7G', FORMAT => 'TYPICAL +PEEKED_BINDS'));
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST'));
To display the execution plan for the SQL statement associated with SQL ID 'atfwcg8anrykp' and PLAN HASH 5593697099 in the SQL Tuning Set called 'OLTP_optimization_123":
SELECT * FROM table ( DBMS_XPLAN.DISPLAY_SQLSET('OLTP_optimization_123','atfwcg8anrykp', 5593697099));
To display all execution plans of the SQL ID 'atfwcg8anrykp' stored in the SQL tuning set:
SELECT * FROM table (DBMS_XPLAN.DISPLAY_SQLSET('OLTP_optimization_123','atfwcg8anrykp'));
To display runtime statistics for the SQL statement included in the preceding statement:
SELECT * FROM table (DBMS_XPLAN.DISPLAY_SQLSET('OLTP_optimization_123', 'atfwcg8anrykp', NULL, 'ALLSTATS LAST');
| Operation | Explanation | To Learn More |
|---|---|---|
|
Access paths
|
As for simple statements, the optimizer must choose an access path to retrieve data from each table in the join statement. For example, the optimizer might choose between a full table scan or an index scan..
|
|
|
Join methods
|
To join each pair of row sources, Oracle Database must decide how to do it. The "how" is the join method. The possible join methods are nested loop, sort merge, and hash joins. A Cartesian join requires one of the preceding join methods. Each join method has specific situations in which it is more suitable than the others.
|
|
|
Join types
|
The join condition determines the join type. For example, an inner join retrieves only rows that match the join condition. An outer join retrieves rows that do not match the join condition.
|
"Join Types" |
|
Join order
|
To execute a statement that joins more than two tables, Oracle Database joins two tables and then joins the resulting row source to the next table. This process continues until all tables are joined into the result. For example, the database joins two tables, and then joins the result to a third table, and then joins this result to a fourth table, and so on.
|
N/A |
STALE STATS FOR TABLE:
SELECT OWNER,TABLE_NAME,STALE_STATS FROM DBA_TAB_STATISTICS WHERE OWNER='&SCHEMA_NAME' AND TABLE_NAME='&TABLE_NAME';
STALE STATS FOR INDEX :
SELECT OWNER,INDEX_NAME,TABLE_NAME FROM DBA_IND_STATISTICS WHERE OWNER='&SCHEMA_NAME' AND INDEX_NAME='&INDEX_NAME';
FOR GETTING HISTORY OF TABLE STATISTICS :
SELECT OWNER,TABLE_NAME,STATS_UPDATE_TIME FROM DBA_TAB_STATS_HISTORY WHERE TABLE_NAME='&TABLE_NAME';
FIXED OBJECTS STATS:
1. CHECK FIXED OBJECT STATS
SELECT COUNT(1) FROM TAB_STATS$;
2. GATHER FIXED OBJECTS STATS
EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
3. CHECK FIXED OBJECT STATS
SELECT COUNT(1) FROM TAB_STATS$;
GATHER SYSTEM STAT:
EXECUTE DBMS_STATS.GATHER_SYSTEM_STATS('START');
-- FEW HOUR DELAY DURING HIGH WORKLOAD
EXECUTE DBMS_STATS.GATHER_SYSTEM_STATS('STOP');
FLUSH SHARED POOL
ALTER SYSTEM FLUSH SHARED_POOL;
ROLL BACK IF THE CHANGE IS NOT SUCCESSFUL:
1. EXEC DBMS_STATS.DELETE_FIXED_OBJECTS_STATS();
2. EXEC DBMS_STATS.DELETE_SYSTEM_STATS;
https://blogs.oracle.com/optimizer/how-to-gather-optimizer-statistics-fast
---GATHER STATS FOR A TABLE:
BEGIN
DBMS_STATS.GATHER_TABLE_STATS (
OWNNAME => ‘HR’,
TABNAME => ‘EMP’,
CASCADE => TRUE, —- FOR COLLECTING STATS FOR RESPECTIVE INDEXES
METHOD_OPT=>’FOR ALL INDEXED COLUMNS SIZE 1′,
GRANULARITY => ‘ALL’,
ESTIMATE_PERCENT =>DBMS_STATS.AUTO_SAMPLE_SIZE,
DEGREE => 4);
END;
/
— FOR A SINGLE TABLE PARTITION
BEGIN
DBMS_STATS.GATHER_TABLE_STATS (
OWNNAME => ‘HR’,
TABNAME => ‘EMP’, — TABLE NAME
PARTNAME => ‘EMP_P1’ — PARTITOIN NAME
METHOD_OPT=>’FOR ALL INDEXED COLUMNS SIZE 1′,
GRANULARITY => ‘APPROX_GLOBAL AND PARTITION’,
DEGREE => 8);
END;
/
---Statistics Gathering Procedures in the DBMS_STATS Package
Default Table and Index Values When Statistics are Missing
| Statistic | Default Value Used by Optimizer |
|---|---|
Tables | num_of_blocks * (block_size - cache_layer) / avg_row_len 100 bytes |
Indexes | 800 (8 * number of blocks) |
Verifying Table Statistics
SELECT TABLE_NAME, NUM_ROWS, BLOCKS, AVG_ROW_LEN,
TO_CHAR(LAST_ANALYZED, 'MM/DD/YYYY HH24:MI:SS')
FROM DBA_TABLES
WHERE TABLE_NAME IN ('SO_LINES_ALL','SO_HEADERS_ALL','SO_LAST_ALL');
This returns the following typical data:
TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANALYZED
------------------------ -------- ------- ----------- -------------
SO_HEADERS_ALL 1632264 207014 449 07/29/1999
00:59:51
SO_LINES_ALL 10493845 1922196 663 07/29/1999
01:16:09
SO_LAST_ALL--Verifying Index Statistics
SELECT INDEX_NAME "NAME", NUM_ROWS, DISTINCT_KEYS "DISTINCT",
LEAF_BLOCKS, CLUSTERING_FACTOR "CF", BLEVEL "LEVEL",
AVG_LEAF_BLOCKS_PER_KEY "ALFBPKEY"
FROM DBA_INDEXES
WHERE owner = 'SH'
ORDER BY INDEX_NAME;
NAME NUM_ROWS DISTINCT LEAF_BLOCKS CF LEVEL ALFBPKEY
-------------------------- -------- -------- ----------- ------- ------- ----------
CUSTOMERS_PK 50000 50000 454 4405 2 1
PRODUCTS_PK 10000 10000 90 1552 1 1
PRODUCTS_PROD_CAT_IX 10000 4 99 4422 1 24
PRODUCTS_PROD_SUBCAT_IX 10000 37 170 6148 2 4
SALES_PROD_BIX 6287 909 1480 6287 1 1
SALES_PROMO_BIX 4727 459 570 4727 1 1
--Verifying Column StatisticsSELECT COLUMN_NAME, NUM_DISTINCT, NUM_NULLS, NUM_BUCKETS, DENSITY FROM DBA_TAB_COL_STATISTICS WHERE TABLE_NAME ="PA_EXPENDITURE_ITEMS_ALL" ORDER BY COLUMN_NAME;This returns the following data:
COLUMN_NAME NUM_DISTINCT NUM_NULLS NUM_BUCKETS DENSITY ------------------------------ ------------ ---------- ----------- ---------- BURDEN_COST 4300 71957 1 .000232558 BURDEN_COST_RATE 675 7376401 1 .001481481 CONVERTED_FLAG 1 16793903 1 1 COST_BURDEN_DISTRIBUTED_FLAG 2 15796 1 .5 COST_DISTRIBUTED_FLAG 2 0 1 .5 COST_IND_COMPILED_SET_ID 87 6153143 1 .011494253 EXPENDITURE_ID 1171831 0 1 8.5337E-07 TASK_ID 8648 0 1 .000115634 TRANSFERRED_FROM_EXP_ITEM_ID 1233787 15568891 1 8.1051E-07
---Verifying Histogram Statistics
SQL> SELECT ENDPOINT_NUMBER, ENDPOINT_VALUE
FROM DBA_HISTOGRAMS
WHERE TABLE_NAME ="SO_LINES_ALL" AND COLUMN_NAME="S2"
ORDER BY ENDPOINT_NUMBER;
This query returns the following typical data:
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- ---------------
1365 4
1370 5
2124 8
2228 18--Table and Index Stats
Table statistics can be gathered for the database, schema, table or partition.
EXEC DBMS_STATS.gather_database_stats;
EXEC DBMS_STATS.gather_database_stats(estimate_percent => 15);
EXEC DBMS_STATS.gather_database_stats(estimate_percent => 15, cascade => TRUE);
EXEC DBMS_STATS.gather_schema_stats('SCOTT');
EXEC DBMS_STATS.gather_schema_stats('SCOTT', estimate_percent => 15);
EXEC DBMS_STATS.gather_schema_stats('SCOTT', estimate_percent => 15, cascade => TRUE);
EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMPLOYEES');
EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMPLOYEES', estimate_percent => 15);
EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMPLOYEES', estimate_percent => 15, cascade => TRUE);
EXEC DBMS_STATS.gather_dictionary_stats;
--Index statistics can be gathered explicitly using the
GATHER_INDEX_STATS procedure.EXEC DBMS_STATS.gather_index_stats('SCOTT', 'EMPLOYEES_PK');
EXEC DBMS_STATS.gather_index_stats('SCOTT', 'EMPLOYEES_PK', estimate_percent => 15);
The current statistics information is available from the data dictionary views for the specific objects (DBA, ALL and USER views). Some of these view were added in later releases.
- DBA_TABLES
- DBA_TAB_STATISTICS
- DBA_TAB_PARTITIONS
- DBA_TAB_SUB_PARTITIONS
- DBA_TAB_COLUMNS
- DBA_TAB_COL_STATISTICS
- DBA_PART_COL_STATISTICS
- DBA_SUBPART_COL_STATISTICS
- DBA_INDEXES
- DBA_IND_STATISTICS
- DBA_IND_PARTITIONS
- DBA_IND_SUBPARTIONS
Histogram information is available from the following views.
- DBA_TAB_HISTOGRAMS
- DBA_PART_HISTOGRAMS
- DBA_SUBPART_HISTOGRAMS
Table, column and index statistics can be deleted using the relevant delete procedures.
EXEC DBMS_STATS.delete_database_stats;
EXEC DBMS_STATS.delete_schema_stats('SCOTT');
EXEC DBMS_STATS.delete_table_stats('SCOTT', 'EMP');
EXEC DBMS_STATS.delete_column_stats('SCOTT', 'EMP', 'EMPNO');
EXEC DBMS_STATS.delete_index_stats('SCOTT', 'EMP_PK');
EXEC DBMS_STATS.delete_dictionary_stats;
--System Stats
EXEC DBMS_STATS.gather_system_stats;
-- Manually start and stop to sample a representative time (several hours) of system activity.
EXEC DBMS_STATS.gather_system_stats('start');
EXEC DBMS_STATS.gather_system_stats('stop');
-- Sample from now until a specific number of minutes.
DBMS_STATS.gather_system_stats('interval', interval => 180);
--Locking Stats
To prevent statistics being overwritten, you can lock the stats at schema, table or partition level.
EXEC DBMS_STATS.lock_schema_stats('SCOTT');
EXEC DBMS_STATS.lock_table_stats('SCOTT', 'EMP');
EXEC DBMS_STATS.lock_partition_stats('SCOTT', 'EMP', 'EMP_PART1');
If you need to replace the stats, they must be unlocked.
EXEC DBMS_STATS.unlock_schema_stats('SCOTT');
EXEC DBMS_STATS.unlock_table_stats('SCOTT', 'EMP');
EXEC DBMS_STATS.unlock_partition_stats('SCOTT', 'EMP', 'EMP_PART1');
--Transfering Stats
EXEC DBMS_STATS.create_stat_table('DBASCHEMA','STATS_TABLE');
EXEC DBMS_STATS.export_schema_stats('APPSCHEMA','STATS_TABLE',NULL,'DBASCHEMA');
EXEC DBMS_STATS.import_schema_stats('APPSCHEMA','STATS_TABLE',NULL,'DBASCHEMA');EXEC DBMS_STATS.drop_stat_table('DBASCHEMA','STATS_TABLE');
--Creating Histograms
You generate histograms by using the DBMS_STATS package. You can generate histograms for columns of a table or partition. For example, to create a 10-bucket histogram on the SAL column of the emp table, issue the following statement:
EXECUTE DBMS_STATS.GATHER_TABLE_STATS('scott','emp', METHOD_OPT => 'FOR COLUMNS SIZE 10 sal');Gather statistics during the day. Gathering ends after 720 minutes and is stored in the "mystats" table:
BEGIN
DBMS_STATS.GATHER_SYSTEM_STATS( gathering_mode => 'interval',interval => 720,stattab => 'mystats',statid => 'OLTP');
END;
/
Gather statistics during the night. Gathering ends after 720 minutes and is stored in the "mystats" table:
BEGIN
DBMS_STATS.GATHER_SYSTEM_STATS(gathering_mode => 'interval',interval => 720,stattab => 'mystats',statid => 'OLAP');
END;
/
Where estimates vary greatly from the actual number of rows returned, the cursor is marked IS_REOPTIMIZIBLE
and will not be used again. The IS_REOPTIMIZIBLE attribute indicates that this SQL statement should be hard
parsed on the next execution so the optimizer can use the execution statistics recorded on the initial execution to
determine a better execution plan
select sql_id,child_number,sql_text,is_reoptimizable from v$sql where sql_id=''
select sql_id,child_number,sql_text,is_reoptimizable from v$sql where sql_text like '% %'A SQL plan directive is also created, to ensure that the next time any SQL statement that uses similar predicates on
the customers table is executed, the optimizer will be aware of the correlation among these columns.
On the second execution the optimizer uses the statistics from the initial execution to determine a new plan that has
a different join order. The use of statistics feedback in the generation of execution plan is indicated in the note
section under the execution plan
The new plan is not marked IS_REOPTIMIZIBLE, so it will be used for all subsequent executions of this SQL statement.
New Reporting Subprograms in DBMS_STATS package
Knowing when and how to gather statistics in a timely manner is critical to maintain acceptable performance on any
system. Determining what statistics gathering operations are currently executing in an environment and how
changes to the statistics methodology will impact the system can be difficult and time consuming.
In Oracle Database 12c, new reporting subprograms have been added to the DBMS_STATS package to make it
easier to monitor what statistics gathering activities are currently going on and what impact changes to the
parameter settings of these operations will have. The DBMS_STATS subprograms are
REPORT_STATS_OPERATIONS, REPORT_SINGLE_STATS_OPERATION and REPORT_GATHER_*_STATS.
Figure 17 shows an example output from the REPORT_STATS_OPERATIONS function. The report shows detailed
information about what statistics gathering operations have occurred, during a specified time window. It gives details
on when each operation occurred, its status, and the number of objects covered and it can be displayed in either
text or HTML format.
2. Best Practices for Gathering Optimizer Statistics with Oracle Database 12c Release 2
http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-bp-for-stats-gather-12c-1967354.pdf
3. Analytical SQL in Database 12c Release 2
http://www.oracle.com/technetwork/database/bi-datawarehousing/wp-sqlnaturallanguageanalysis-2431343.pdf
4. SQL Plan Management with Oracle Database 12c Release 2
http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-sql-plan-mgmt-12c-1963237.pdf
5. Parallel Execution with Oracle Database 12c Fundamentals
http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-parallel-execution-fundamentals-133639.pdf
