DBMS_XPLAN package

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

----------------- SAMPLES. -------------


Using a View to Display Last Explain Plan

EXPLAIN PLAN FOR
SELECT * FROM emp e, dept d
WHERE e.deptno = d.deptno
AND e.ename='benoit';

Display the plan using the DBMS_XPLAN.DISPLAY table function

SET LINESIZE 130
SET PAGESIZE 0
SELECT * FROM table(DBMS_XPLAN.DISPLAY);



Display the execution plan for the cursor:
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(('gwp663cqh5qbf',0));

Display the execution plan of all cursors matching the string 'TOTO':

SELECT t.*
FROM v$sql s, table(DBMS_XPLAN.DISPLAY_CURSOR(s.sql_id, s.child_number)) t WHERE sql_text LIKE '%TOTO%';

------------------------

DISPLAY Function

This table function displays the contents of the plan table.

Syntax

DBMS_XPLAN.DISPLAY(
table_name IN VARCHAR2 DEFAULT 'PLAN_TABLE',
statement_id IN VARCHAR2 DEFAULT NULL,
format IN VARCHAR2 DEFAULT 'TYPICAL',
filter_preds IN VARCHAR2 DEFAULT NULL);

To display the result of the last EXPLAIN PLAN command stored in the plan table:

SELECT * FROM table (DBMS_XPLAN.DISPLAY);
To display from other than the default plan table, "my_plan_table":

SELECT * FROM table (DBMS_XPLAN.DISPLAY('my_plan_table'));
To display the minimum plan information:

SELECT * FROM table (DBMS_XPLAN.DISPLAY('plan_table', null, 'basic'));


------------------------------------------

DISPLAY_AWR Function

This table function displays the contents of an execution plan stored in the AWR.

Syntax

DBMS_XPLAN.DISPLAY_AWR(
sql_id IN VARCHAR2,
plan_hash_value IN NUMBER DEFAULT NULL,
db_id IN NUMBER DEFAULT NULL,
format IN VARCHAR2 DEFAULT TYPICAL);

To display the different execution plans associated with the SQL ID 'atfwcg8anrykp':

SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR('atfwcg8anrykp'));

-----------------------------------------------------------------------------

DISPLAY_CURSOR Function

This table function displays the explain plan of any cursor loaded in the cursor cache. In addition to the explain plan, various plan statistics (such as. I/O, memory and timing) can be reported (based on the V$SQL_PLAN_STATISTICS_ALL VIEWS).

Syntax

DBMS_XPLAN.DISPLAY_CURSOR(
sql_id IN VARCHAR2 DEFAULT NULL,
child_number IN NUMBER DEFAULT NULL,
format IN VARCHAR2 DEFAULT 'TYPICAL');


child_number
Child number of the cursor to display. If not supplied, the execution plan of all cursors matching the supplied sql_id parameter are displayed. The child_number can be specified only if sql_id is specified.



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('atfwcg8anrykp'));
with children 1

SELECT * FROM table (
DBMS_XPLAN.DISPLAY_CURSOR('atfwcg8anrykp',1));

--------------------------------------------------

DISPLAY_SQL_PLAN_BASELINE Function

This table function displays one or more execution plans for the specified SQL handle of a SQL plan baseline.

Syntax

DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE (
sql_handle IN VARCHAR2 := NULL,
plan_name IN VARCHAR2 := NULL,
format IN VARCHAR2 := 'TYPICAL')
RETURN dbms_xplan_type_table;

plan_name
Plan name. It identifies a specific plan. Default NULL means all plans associated with identified SQL statement are explained and displayed.

Display all plans of a SQL statement identified by the SQL handle 'SYS_SQL_b1d49f6074ab95af' using TYPICAL format

SET LINESIZE 150
SET PAGESIZE 2000
SELECT t.*
FROM TABLE(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(
'SYS_SQL_b1d49f6074ab95af')) t;

-----------------------------------------------------

DISPLAY_SQLSET Function


DBMS_XPLAN.DISPLAY_SQLSET(
sqlset_name IN VARCHAR2,
sql_id IN VARCHAR2,
plan_hash_value IN NUMBER := NULL,
format IN VARCHAR2 := 'TYPICAL',
sqlset_owner IN VARCHAR2 := NULL)
RETURN DBMS_XPLAN_TYPE_TABLE PIPELINED;

This table function displays the execution plan of a given statement stored in a SQL tuning set.

plan_hash_value
Optional parameter. Identifies a specific stored execution plan for a SQL statement. If suppressed, all stored execution plans are shown.

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_0405','gwp663cqh5qbf'));

To display the execution plan for the SQL statement associated with SQL ID 'gwp663cqh5qbf' and PLAN HASH 3693697075 in the SQL Tuning Set called 'OLTP_optimization_0405":

SELECT * FROM table (
DBMS_XPLAN.DISPLAY_SQLSET(
'OLTP_optimization_0405','gwp663cqh5qbf', 3693697075));

11g Upgrade check list

Upgrade-Öncesi
Binary kurulum için gerekli diski bul
Binary leri ve PSU yükle
Outline Topla
SQLArea Topla
SQLPLan bilgileri topla
SQL tuning Set yarat buffer Cache'de yüklemelerini yap.
Upgrade ve/veya downgrade testi yap
XDB yüklenmiş olmalı
utlu112i.sql çalıştır sonuçlarına bak
connect role privs için script hazırla
"select TZ_VERSION from registry$database;
"
DB linklerin backuplarını al
10g Tablo istatistiklerini bir tabloya export et.
EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
"ACL kullanan userlar

SELECT DISTINCT owner
FROM DBA_DEPENDENCIES
WHERE referenced_name
IN ('UTL_TCP','UTL_SMTP','UTL_MAIL','UTL_HTTP','UTL_INADDR')
AND owner NOT IN ('SYS','PUBLIC','ORDPLUGINS');


SELECT DISTINCT owner
FROM all_tab_columns
WHERE data_type
IN ('ORDIMAGE', 'ORDAUDIO', 'ORDVIDEO', 'ORDDOC','ORDSOURCE', 'ORDDICOM')
AND (data_type_owner = 'ORDSYS' OR data_type_owner = owner)
AND (owner != 'PM');
"
"The DIAGNOSTIC_DEST initialization parameter replaces the
USER_DUMP_DEST, BACKGROUND_DUMP_DEST."
Backup Al
DROP TABLE SYS.PLAN_TABLE;
TRUNCATE TABLE SYS.AUD$
SELECT * FROM v$recover_file;
Disk sistem type 'ına göre ufs veya vfs durumuna göre vfs ise ODM kullanılacaktır filesystemio_options='SETALL' parametresini belirle
Upgrade
copy password file & tnsnames.ora & listener.ora from 9i to 10g
clear dba_2pc_pending
invalid object list table
İş İstek'den invalid compile için snap al.
event='10298 trace name context forever, level 32','10946 trace name context forever,level 262144'
"init.oraya eklemeler yap ._first_spare_parameter=1
*._gby_hash_aggregation_enabled=FALSE
*._ksmg_granule_size=67108864
*._library_cache_advice=FALSE
*._log_archive_callout='LOCAL_FIRST=TRUE'
*._newsort_enabled=FALSE
*._optimizer_cost_based_transformation='OFF'
*._optimizer_rownum_pred_based_fkr=FALSE
*._rollback_segment_count=10000
*._undo_autotune=FALSE JAVA_JIT_ENABLED= FALSE "

STARTUP UPGRADE;
@catupgrd.sql
startup
@utlu112s.sql
@catuppst.sql
@utlrp.sql
Upgrade Sonrası
"Modify the listener.ora file
For the upgraded instance(s) modify the ORACLE_HOME parameter to
point to the new ORACLE_HOME"
"UPDATE Time zone
Updating the RDBMS DST version in 11gR2 (11.2.0.1 and up) using DBMS_DST (Doc ID 977512.1)
"
install XDB component eğer yoksa
Configure (ACL) Fine-Grained Access to External Network Services
invalid objelere bak
İş istekden invalid snapshot al ve karşılaştır
alert logu control etmeye başka
Database console hazırla
vfs varsa ODM kurulumu
Sistem performasını takip et
Standbylar varsa onların işini yap.
exec dbms_scheduler.disable('GATHER_STATS_JOB')
10g Database'in init.ora parametre dosyasını mv et
Backup al

"ACL yaratılır begin
dbms_network_acl_admin.create_acl (
acl => 'utlpkg.xml',
description => 'Normal Access',
principal => 'CONNECT',
is_grant => TRUE,
privilege => 'connect',
start_date => null,
end_date => null
);
commit;
end;
/"
"ACL nin tüm IP ve Portlara erişimi verilir
begin
dbms_network_acl_admin.assign_acl (
acl => 'utlpkg.xml',
host => '*');
commit;
end;
/
"
"
Her bir user için

begin
dbms_network_acl_admin.add_privilege (
acl => 'utlpkg.xml',
principal => 'CEURO',
is_grant => TRUE,
privilege => 'connect',
start_date => null,
end_date => null);
commit;
end;
/

begin
dbms_network_acl_admin.add_privilege (
acl => 'utlpkg.xml',
principal => 'CEURO',
is_grant => TRUE,
privilege => 'resolve',
start_date => null,
end_date => null);
commit;
end;
/"

opt_param usage

After 10g you can use opt_param with only below parameter

- OPTIMIZER_DYNAMIC_SAMPLING,
- OPTIMIZER_INDEX_CACHING,
- OPTIMIZER_INDEX_COST_ADJ,
- OPTIMIZER_SECURE_VIEW_MERGING
- STAR_TRANSFORMATION_ENABLED

like this

SELECT /*+ OPT_PARAM('star_transformation_enabled' 'true') */ * FROM ... ;

opt_param

At 10g without alter session you can change optimizer parameter with opt_param

select /*+ opt_param('optimizer_mode','first_rows_10') */

select /*+ opt_param('_optimizer_cost_model','io') */

select /*+ opt_param('optimizer_index_cost_adj',20) */

select /*+ opt_param('optimizer_index_caching',20) */



The OPT_PARAM hint lets you set an initialization parameter for the duration of the current query only. This hint is valid only for the following parameters: OPTIMIZER_DYNAMIC_SAMPLING, OPTIMIZER_INDEX_CACHING, OPTIMIZER_INDEX_COST_ADJ, OPTIMIZER_SECURE_VIEW_MERGING, and STAR_TRANSFORMATION_ENABLED. For example, the following hint sets the parameter STAR_TRANSFORMATION_ENABLED to TRUE for the statement to which it is added:

SELECT /*+ OPT_PARAM('star_transformation_enabled' 'true') */ * FROM ... ;
Parameter values that are strings are enclosed in single quotation marks. Numeric parameter values are specified without quotation marks

Using Stored Outlines for Upgrade to 10g

First give grant to create stored outlines for users

CONN sys/password AS SYSDBA


-- Give grant to all app users

SELECT 'GRANT CREATE ANY OUTLINE TO ' || username || ';'
FROM dba_users
WHERE username NOT IN ('SYSTEM', 'SYS', 'OUTLN', 'DBSNMP', 'MASTER')
AND ( username not LIKE 'ZU%' )
AND ( username not LIKE 'U0%' );

GRANT CREATE ANY OUTLINE TO APPUSER;
GRANT EXECUTE_CATALOG_ROLE TO APPUSER;


-- Start to Collect outlines

alter system set CREATE_STORED_OUTLINES=OLDPLAN;

-- Check Outlines

-- Check Database performance

select name, sql_text, category from dba_outlines;

select count(*) from dba_outlines where category='OLDPLAN'

-- Stop Collecting Outlines

alter system set CREATE_STORED_OUTLINES=false;

--------------------------------------------------------------------------------

-- After collecting outlines upgrade the database
-- set USE_STORED_OUTLINES to correct execution plan


Alter system set USE_STORED_OUTLINES=FIXPLAN;

-- And create temporary tables for queries

Create table match_outlines (
owner varchar2(30),
name varchar2(30),
sql_text clob);

Insert into match_outlines
Select owner,name, to_lob(sql_text)
From dba_outlines ;

-- if there is any problem find the Query and change the execution plan with outlines.

-- Get the stored outline name

select name,sql_text from master.match_outlines where upper(sql_text) like '%Part of the SQL%';

alter outline OUTLINE_NAME change category to FIXPLAN;

-- Or fix the plan by Hash_value if you know it.

BEGIN
DBMS_OUTLN.create_outline(
hash_value => 3909283366,
child_number => 0,
category => 'FIXPLAN');
END;
/




----------- SOME INFORMATION ABOUT OUTLINES ------------------

-- views --
select * from dba_outlines;
select * from dba_outline_hints;
select * from OL$HINTS;

-- packages that you can use

DBMS_OUTLINE.*
DBMS_OUTLINE_EDIT.*


-- Create an outline for a specific SQL statement by CREATE OUTLINE command
-- white spaces are not important
-- But the name of the bind wariables are important
- Capital or small letters are not important


CREATE OUTLINE my_outline FOR CATEGORY oldplan
ON SELECT col1,col2,col4 FROM tablename WHERE col3='BB' and col4= :b1 ;


reading about the subject

http://www.oracle-base.com/articles/misc/Outlines.php