Oracle Database Administrator

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_STATISTICS when determining cardinality.
Average row length
Number of data blocks
The optimizer uses the number of data blocks with the DB_FILE_MULTIBLOCK_READ_COUNT initialization 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';

NUM_ROWS AVG_ROW_LEN BLOCKS EMPTY_BLOCKS LAST_ANAL
---------- ----------- ---------- ------------ ---------
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 BLEVEL column 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 NULL constraint 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.

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 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

Autotrace Setting	Result
`SET AUTOTRACE OFF`	No `AUTOTRACE` report is generated. This is the default.
`SET AUTOTRACE ON EXPLAIN`	The `AUTOTRACE` report shows only the optimizer execution path.
`SET AUTOTRACE ON STATISTICS`	The `AUTOTRACE` report shows only the SQL statement execution statistics.
`SET AUTOTRACE ON`	The `AUTOTRACE` report includes both the optimizer execution path and the SQL statement execution statistics.
`SET AUTOTRACE TRACEONLY`	Like `SET` `AUTOTRACE` `ON`, but suppresses the printing of the user's query output, if any.

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..	"Optimizer Access Paths"
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 Methods"
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

Procedure	Collects
GATHER_INDEX_STATS	Index statistics
GATHER_TABLE_STATS	Table, column, and index statistics
GATHER_SCHEMA_STATS	Statistics for all objects in a schema
GATHER_DATABASE_STATS	Statistics for all objects in a database
GATHER_SYSTEM_STATS	CPU and I/O statistics for the system

Default Table and Index Values When Statistics are Missing

Statistic	Default Value Used by Optimizer
Tables Cardinality Average row length Number of blocks Remote cardinality Remote average row length	num_of_blocks * (block_size - cache_layer) / avg_row_len 100 bytes 1 2000 rows 100 bytes
Indexes Levels Leaf blocks Leaf blocks/key Data blocks/key Distinct keys Clustering factor	1 25 1 1 100 800 (8 * number of blocks)

Statistic

Default Value Used by Optimizer

Tables

Cardinality
Average row length
Number of blocks
Remote cardinality
Remote average row length

num_of_blocks * (block_size - cache_layer) / avg_row_len

100 bytes

2000 rows

100 bytes

Indexes

Levels
Leaf blocks
Leaf blocks/key
Data blocks/key
Distinct keys
Clustering factor

100

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 Statistics

SELECT 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.

1. Understanding Optimizer Statistics with Oracle Database 12c Release 2 http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-statistics-concepts-12c-1963871.pdf

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

Oracle Database Administrator

Saturday, February 22, 2020

Generating Explain Plan & Gather Statistics

Column Statistics

Index Statistics

---Statistics Gathering Procedures in the DBMS_STATS Package

Default Table and Index Values When Statistics are Missing

Verifying Table Statistics

---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

--Transfering Stats

--Creating Histograms

Saturday, February 22, 2020

Generating Explain Plan & Gather Statistics

Column Statistics

Index Statistics

---Statistics Gathering Procedures in the DBMS_STATS Package

Default Table and Index Values When Statistics are Missing

Verifying Table Statistics

---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

--Transfering Stats

--Creating Histograms

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