 |
|
Monitoring Outlines
Oracle Database Tips by Donald Burleson |
Outlines provide a method for forcing the
cost-based optimizer to consistently use the same optimization for a
specific SQL statement. The DBA needs to be aware of which outlines
are currently stored in the database and if the outline has been used.
The views used to monitor the outlines are DBA_OUTLINES and
DBA_OUTLINE_HINTS. Note that to create an outline (covered in Chapter
7), the PLAN_TABLE must be located in either a publicly available
schema or in your own schema. In Oracle9i, the OEM tool has been
extended to allow for outline editing. There are also new DBMS
packages provided for this purpose. The views relationship is shown in
Figure 10.6. An example report using the DBA_OUTLINES and
DBA_OUTLINE_HINTS views is shown in Source 10.41, with example output
from the script following in Listing 10.39.
SOURCE 10.41 Example of database outline
report.
rem
rem NAME: outline.sql
rem FUNCTION: Generate a lit of all outlines in the
rem database for a specific user or all users
rem HISTORY: MRA 5/13/98 Created
rem
COLUMN owner FORMAT a8 HEADING 'Owner'
COLUMN name FORMAT a13 HEADING 'Outline|Name'
COLUMN category FORMAT a8 HEADING 'Category|Name'
COLUMN used FORMAT a7 HEADING 'Used?'
COLUMN timestamp FORMAT a16 HEADING 'Timestamp'
COLUMN version FORMAT a9 HEADING 'Version'
COLUMN sql_text FORMAT a40 HEADING 'SQL Outlined' WORD_WRAPPED
BREAK ON owner ON category
SET PAGES 58 LINES 130 FEEDBACK OFF VERIFY OFF
START title132 'Database OUTLINE Report'
SPOOL rep_out\&db\outline.lis
SELECT
owner,
name,
category,
used,
to_char(timestamp,'dd/mm/yyyy hh24:mi') timestamp ,
version,
sql_text
FROM
Dba_outlines
WHERE
Owner LIKE '%&owner%'
ORDER BY
owner,category;
SPOOL OFF
CLEAR BREAKS
TTITLE OFF
SET FEEDBACK ON VERIFY ON
LISTING 10.39 Example of output from database
outline report.
Date:
05/13/99
Page: 1
Time: 11:44 PM Database OUTLINE
Report SYSTEM
ORTEST1 database
Outline Category
Owner Name Name Used?
Timestamp Version SQL
Outlined
-------- ------------- -------- ------- ---------------- ---------
-----------------
TELE_DBA PROD_OUTLINE1 PROD UNUSED 13/05/1999 23:39 8.1.5.0.0
select owner,table_name from
dba_tables
PROD_OUTLINE2 UNUSED 13/05/1999 23:39 8.1.5.0.0
select * from dba_data_files
TEST_OUTLINE1 TEST UNUSED 13/05/1999 23:39 8.1.5.0.0
select a.table_name,
b.tablespace_name,
c.file_name from
dba_tables a,
dba_table
TEST_OUTLINE2 UNUSED 13/05/1999 23:39 8.1.5.0.0
select * from dba_data_files
In the outline report you should monitor for
outlines that either haven't been used or haven't been used recently,
and review whether they are still pertinent to your database
application. The OUTLN_UTL package is used to maintain outlines.
Monitoring Outline Hints
Outlines generate and use outline hints. The
outline hints are viewed in the DBA_OUTLINE_HINTS view. The report in
Source 10.42 demonstrates the monitoring of the outline hints. Listing
10.40 shows sample output from that report.
SOURCE 10.42 Example of database outline hints
report.
rem
rem NAME: outline_hint.sql
rem FUNCTION: Generate a lit of all outlines in the
rem database for a specific user and outline or all users
rem and outlines
rem HISTORY: MRA 5/13/98 Created
rem
COLUMN owner FORMAT a8 HEADING 'Owner'
COLUMN name FORMAT a13 HEADING 'Outline|Name'
COLUMN category FORMAT a10 HEADING 'Category|Name'
COLUMN node FORMAT 9999 HEADING 'Node'
COLUMN join_pos FORMAT 9999 HEADING 'Join|Pos'
COLUMN hint FORMAT A27 HEADING 'Hint Text' WORD_WRAPPED
BREAK ON owner ON category ON name
SET PAGES 58 LINES 78 FEEDBACK OFF VERIFY OFF
START title80 'Database OUTLINE Report'
SPOOL rep_out\&db\outline_hint.lis
SELECT
a.owner, a.name,
a.category, b.node,
b.join_pos, b.hint
FROM
Dba_outlines a, dba_outline_hints b
WHERE
a.Owner LIKE UPPER('%&owner%')
AND a.name LIKE UPPER('%&outline%')
AND a.owner=b.owner
AND a.name=b.name
ORDER BY
owner,category,name,b.node;
SPOOL OFF
CLEAR BREAKS
TTITLE OFF
SET FEEDBACK ON VERIFY ON
LISTING 10.40 Example of output from database
outline hints report.
Date:
05/14/99 Page:
1
Time: 12:08 AM Database OUTLINE Report
SYSTEM ORTEST1 databas
Outline Category Join
Owner Name Name Node Pos Hint Text
-------- ------------- ----------- ----- ---- ----
-----------------------
TELE_DBA TEST_OUTLINE2 TEST 1 0 NO_EXPAND
1 0
ORDERED
1 1 NO_ACCESS(DBA_DATA_FILES)
1 0 NOREWRITE
1 0 NO_FACT(DBA_DATA_FILES)
1 0 NOREWRITE
2 0 NO_EXPAND
2 0 ORDERED
2 0 NOREWRITE
2 0 NOREWRITE
3 0 NO_EXPAND
3 0 PQ_DISTRIBUTE(TS NONE
NONE) 3 0 PQ_DISTRIBUTE(HC
NONE NONE) 3 0
PQ_DISTRIBUTE(F NONE NONE) 3
0 ORDERED
3 0 NO_FACT(HC)
3 0 NO_FACT(X$KCCFN)
3 3 FULL(HC)
3 1 FULL(X$KCCFN)
3 0 NOREWRITE
3 0 NOREWRITE
3 2 INDEX(F
I_FILE1) 3 4
INDEX(TS)
3 0 NO_FACT(F)
3 0 NO_FACT(TS)
3 0 USE_NL(F)
3 0 USE_NL(HC)
3 0 USE_NL(TS)
4 0 NO_EXPAND
4 0 NOREWRITE
4 0 PQ_DISTRIBUTE(TS NONE
NONE) 4 0 PQ_DISTRIBUTE(F
NONE NONE) 4 0 USE_NL(TS)
4 0 USE_NL(F)
4 0 ORDERED
4 0 NO_FACT(TS)
4 0 NO_FACT(F)
4 0 NO_FACT(X$KCCFN)
4 3 INDEX(TS)
4 2 INDEX(F
I_FILE1) 4 1
FULL(X$KCCFN)
4 0 NOREWRITE
5 0 NOREWRITE
6 0 NOREWRITE
7 0 NOREWRITE
8 0 NOREWRITE
The output from the database outline hints
report should be reviewed to verify that the proper hints are being
used for the specific SQL outline.
Further DBA Reading
The DBA may find these references of interest
when planning to do monitoring activities:
Oracle9i Database Administrator's Guide,
Release 9.0.1, Part No. A90117-01, Oracle Corporation, June 2001.
Oracle9i Database Reference, Release 9.0.1,
Part No. A90190-01, Oracle Corporation, June 2001.
Oracle9i PL/SQL User's Guide and Reference,
Release 9.0.1, Part No. A89856-01, Oracle Corporation, June 2001.
Oracle9i SQL Reference, Release 9.0.1, Part
No. A90125-01, Oracle Corporation, June 2001.
Oracle9i Supplied PL/SQL Packages and Types
Reference, Release 9.0.1, Part No. A89852-02, June 2001, Oracle
Corporation.
This is an excerpt from Mike Ault, bestselling author of "Oracle
10g Grid and Real Application Clusters".
|
and include the URL for the page.
