Database Reference
In-Depth Information
was varied between 100 (default) and 10000, is responsible for the effect shown. The increase
of
OPTIMIZER_INDEX_COST_ADJ
caused the optimizer to consider index access as 100 times more
expensive. As a consequence, the optimizer chose a plan with full table scans and hash joins
instead of index accesses and nested loops.
There are two approaches for retrieving plans from an AWR repository:
The pipelined table function
DBMS_XPLAN.DISPLAY_AWR
The AWR SQL statement report script
$ORACLE_HOME/rdbms/admin/awrsqrpt.sql
Unless you wish to retrieve query block names for use in hints—these are not displayed by
the AWR report—the second approach is the preferred one, since it not only contains all plans
for a specific
SQL_ID
, but also includes execution statistics. The call to
DBMS_XPLAN.DISPLAY_AWR
requires the
SQL_ID
, plan hash value, and the database identifier (
V$DATABASE.DBID
) as input
parameters. Values for the first two parameters have already been retrieved from
DBA_HIST_
SQLSTAT
, so solely the database identifier must be queried before
DBMS_XPLAN
can be called.
SQL> SELECT dbid FROM v$database;
DBID
----------
2870266532
SQL> SELECT * FROM
TABLE (dbms_xplan.display_awr('1yw85nghurbkk', 4095786543, 2870266532, 'ALL'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
SQL_ID 1yw85nghurbkk
--------------------
SELECT emp.last_name, emp.first_name, j.job_title, d.department_name, l.city,
…
Plan hash value: 4095786543
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 9 (100)| |
| 1 | NESTED LOOPS | | 105 | 18060 | 9 (12)| 00:00:01 |
…
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
PLAN_TABLE_OUTPUT
-------------------------------
5 - SEL$1 / EMP@SEL$1
…
The AWR report script
awrsqrpt.sql
asks for the
SQL_ID
and the beginning and end snap-
shot identifiers. These values were previously retrieved from
DBA_HIST_SQLSTAT
. Figure 26-1
