Upgrade from 10.2.0.1.to 11.1.0.6

Upgradation from 10.2.0.1 to 11.1.0.6



We have a oracle 10g database, we have upgraded this to 11.1.0.6.

Oracle 10g home - /u01/app/ora10g/product/10.2.0/db_1
Oracle 11g home - /u01/app/ora10g/product/11.1.0/db_1



Synopsis Steps:-

1. Run the pre-upgradation sql script (utlu111i.sql) in the 10g database and
make the necessary changes as per the requirements it
throws.

2. Take a copy of the spfile as pfile and make the necessary
changes, (i.e) remove the obsolete parameter and
add the new parameters.

3. Shutdown the database and take the backup of datafiles,
redo logs, controlfiles, password file and pfiles.


4. Copy the modified pfile in the 11g home.

5. Startup the database in upgrade mode.

6. Run the catlag upgrade utility (catupgrd.sql), the database will be shutdown
once this script is completed.

7. Now Start the database in normal mode and run the catalog post upgrade
catuppst.sql and then run the utlu111s.sql and check whether all the components
are been upgraded.

8. Run the utlrp.sql to recompile all the objects.

9. Upgrade process completed.



Detailed Steps:-

Step 1:-


[oracle@linux11g ~]$ export ORACLE_HOME=/u01/app/ora10g/product/10.2.0/db_1
[oracle@linux11g ~]$ export ORACLE_SID=upg211g
[oracle@linux11g ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Mon May 30 22:30:07 2011

Copyright (c) 1982, 2007, Oracle. All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> set linesize 150;
SQL> @/u01/app/oracle/product/11.1.0/db_1/rdbms/admin/utlu111i.sql;
Oracle Database 11.1 Pre-Upgrade Information Tool 05-30-2011 22:30:41
.
**********************************************************************
Database:
**********************************************************************
--> name: UPG211G
--> version: 10.2.0.1.0
--> compatible: 10.2.0.1.0
--> blocksize: 8192
--> platform: Linux IA (32-bit)
--> timezone file: V2
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 723 MB
.... AUTOEXTEND additional space required: 243 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 474 MB
.... AUTOEXTEND additional space required: 444 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 411 MB
.... AUTOEXTEND additional space required: 181 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required size: 61 MB
.... AUTOEXTEND additional space required: 41 MB
--> EXAMPLE tablespace is adequate for the upgrade.
.... minimum required size: 69 MB
.
**********************************************************************
Update Parameters: [Update Oracle Database 11.1 init.ora or spfile]
**********************************************************************
WARNING: --> "sga_target" needs to be increased to at least 336 MB
.
**********************************************************************
Renamed Parameters: [Update Oracle Database 11.1 init.ora or spfile]
**********************************************************************
-- No renamed parameters found. No changes are required.
.
**********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 11.1 init.ora or spfile]
**********************************************************************
--> "background_dump_dest" replaced by "diagnostic_dest"
--> "user_dump_dest" replaced by "diagnostic_dest"
--> "core_dump_dest" replaced by "diagnostic_dest"
.
**********************************************************************
Components: [The following database components will be upgraded or installed]
**********************************************************************
--> Oracle Catalog Views [upgrade] VALID
--> Oracle Packages and Types [upgrade] VALID
--> JServer JAVA Virtual Machine [upgrade] VALID
--> Oracle XDK for Java [upgrade] VALID
--> Oracle Workspace Manager [upgrade] VALID
--> OLAP Analytic Workspace [upgrade] VALID
--> OLAP Catalog [upgrade] VALID
--> EM Repository [upgrade] VALID
--> Oracle Text [upgrade] VALID
--> Oracle XML Database [upgrade] VALID
--> Oracle Java Packages [upgrade] VALID
--> Oracle interMedia [upgrade] VALID
--> Spatial [upgrade] VALID
--> Data Mining [upgrade] VALID
--> Expression Filter [upgrade] VALID
--> Rule Manager [upgrade] VALID
--> Oracle OLAP API [upgrade] VALID
.
**********************************************************************
Miscellaneous Warnings
**********************************************************************
WARNING: --> Database is using an old timezone file version.
.... Patch the 10.2.0.1.0 database to timezone file version 4
.... BEFORE upgrading the database. Re-run utlu111i.sql after
.... patching the database to record the new timezone file version.
WARNING: --> Database contains stale optimizer statistics.
.... Refer to the 11g Upgrade Guide for instructions to update
.... statistics prior to upgrading the database.
.... Component Schemas with stale statistics:
.... SYS
.... OLAPSYS
.... SYSMAN
.... CTXSYS
.... XDB
WARNING: --> Database contains schemas with objects dependent on network packages.
.... Refer to the 11g Upgrade Guide for instructions to configure Network ACLs.
.... USER SYSMAN has dependent objects.
WARNING: --> EM Database Control Repository exists in the database.
.... Direct downgrade of EM Database Control is not supported. Refer to the
.... 11g Upgrade Guide for instructions to save the EM data prior to upgrade.
.

PL/SQL procedure successfully completed.


Here the first warning shows that the timezone file version is lower
and it has to be upgraded to timezone file version 4.

SQL> select * from V$timezone_file;

FILENAME VERSION
------------ ----------
timezlrg.dat 2

sql> Shutdown immediate;


We need to upgrade the timezonefile version to 4 before proceeding the
upgrade. Hence download the patch from the Metalink and apply the
patch.

Patchno for this is p5632264_10202_LINUX.zip (This patch is for 10.2.0.2 but
this patch can be applied for 10.2.0.1 also) and you cannot apply this patch
using opatch utility since it belong to 10.2.0.2 version. Hence we need to
patch it manually as below.


a) Download the patch from metalink p5632264_10202_LINUX.zip
$ unzip p5632264_10202_LINUX.zip

Take the backup of version 2 timezone files.

[oracle@linux11g zoneinfo]$ cp /u01/app/ora10g/product/10.2.0/db_1/oracore/zoneinfo/* /u01/bkpofoldtimezonefiles


copy the new timezone files to the zoneinfo directory.
$ cd 5632264
$ cd files/oracore/zoneinfo
[oracle@linux11g zoneinfo]$ cp * /u01/app/ora10g/product/10.2.0/db_1/oracore/zoneinfo/

Once the new files are copied start the datbase and check the timezone version;

[oracle@linux11g zoneinfo]$ sqlplus / as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Mon May 30 22:42:24 2011

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 83887696 bytes
Database Buffers 197132288 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
SQL> select * from V$timezone_file;

FILENAME VERSION
------------ ----------
timezlrg.dat 4


Gather the statistics as per the warning in the preupgrade script output.

SQL> exec dbms_stats.gather_dictionary_stats;

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_schema_stats('SYS');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_schema_stats('OLAPSYS');

PL/SQL procedure successfully completed.SQL> exec dbms_stats.gather_schema_stats('SYSMAN');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_schema_stats('CTXSYS');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_schema_stats('XDB');

PL/SQL procedure successfully completed.


Ignore the below warnings:-


WARNING: --> Database contains schemas with objects dependent on network packages.
.... Refer to the 11g Upgrade Guide for instructions to configure Network ACLs.
.... USER SYSMAN has dependent objects.
WARNING: --> EM Database Control Repository exists in the database.
.... Direct downgrade of EM Database Control is not supported. Refer to the
.... 11g Upgrade Guide for instructions to save the EM data prior to upgrade.
.

SQL> create pfile = '/u01/upg211g.ora' from spfile;

File created.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.


Now open the pfile which we created above and remove the obsolete parameter and
new the new parameters and also modified the sga setting accordingly.

sga_target=336m
diagnostic_dest='/u01/app/oracle/diag'

Remove the below parameters

"background_dump_dest"
"user_dump_dest"
"core_dump_dest"

Now paste the modified pfile in the 11g home and start the database in upgrade
mode.

[oracle@linux11g ~]$ export ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1
[oracle@linux11g ~]$ export ORACLE_SID=upg211g
[oracle@linux11g ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Mon May 30 22:52:21 2011

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Connected to an idle instance.

SQL> create spfile from pfile='/u01/upg211g.ora';

File created.

SQL> startup upgrade;
ORACLE instance started.

Total System Global Area 355717120 bytes
Fixed Size 1299876 bytes
Variable Size 79694428 bytes
Database Buffers 268435456 bytes
Redo Buffers 6287360 bytes
Database mounted.
Database opened.

Check whether the Oracle Database Vault Option is enabled, if it is enabled then
disable it before the upgrade and enable it once the db is upgrade.

You can check if Oracle Database Vault is enabled or disabled by querying the V$OPTION data dictionary view. Any user can query this view. If Oracle Database Vault is enabled, the query returns TRUE. Otherwise, it returns FALSE.

Remember that the PARAMETER column value is case sensitive. For example:

SELECT * FROM V$OPTION WHERE PARAMETER = 'Oracle Database Vault';

If Oracle Database Vault is enabled, the following output appears:

PARAMETER VALUE
----------------------------- -----------------------
Oracle Database Vault TRUE

Follow these steps to disable Oracle Database Vault on UNIX systems:

Turn off the software processes. Make sure that the environment variables, ORACLE_HOME, ORACLE_SID, and PATH are correctly set.

Stop the dbconsole process in case it is running. For both single-instance and Oracle Real Application Clusters installations, run the following command at a command prompt:

emctl stop dbconsole

For single-instance installations, shut down the database instance:

sqlplus sys as sysoper
Enter password: password

SHUTDOWN NORMAL
EXIT

For Oracle Real Application Clusters (Oracle RAC) installations, shut down each database instance as follows, from a command prompt:

srvctl stop database -d db_name

If you cannot connect to the database, then proceed to the next step.

At a command prompt, run the following commands to turn off the Oracle Database Vault option:

cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk dv_off ioracle

For Oracle RAC installations, run these commands on all nodes.

In SQL*Plus, start the database.

For single-instance database installations:

sqlplus sys as sysoper
Enter password: password

STARTUP

For Oracle RAC installations:

srvctl start database -d db_name
---------------

--Run the catalog upgrade script.
SQL> spool catupgrdelog
SQL> @?/rdbms/admin/catupgrd.sql;
DOC>#######################################################################
DOC>#######################################################################
DOC>
DOC> The first time this script is run, there should be no error messages
DOC> generated; all normal upgrade error messages are suppressed.
DOC>
DOC> If this script is being re-run after correcting some problem, then
DOC> expect the following error which is not automatically suppressed:
...
...
...
...
...

PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> /*****************************************************************************/
SQL> /* Step 10 - SHUTDOWN THE DATABASE..!!!!!
SQL> */
SQL> /*****************************************************************************/
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.


Now start the database in normal mode and run post upgrade scripts.


SQL> startup
ORACLE instance started.

Total System Global Area 355717120 bytes
Fixed Size 1299876 bytes
Variable Size 150997596 bytes
Database Buffers 197132288 bytes
Redo Buffers 6287360 bytes
Database mounted.
Database opened.

SQL> select * from V$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for Linux: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

SQL> @?/rdbms/admin/utlrp.sql; -- ( To Recompile all the invlaid objects)
SQL> @?/rdbms/admin/catuppst.sql;
Rem DESCRIPTION
Rem This post-upgrade script performs remaining upgrade actions that
Rem dont require that the database be open in UPGRADE mode. It
Rem can be run at the same time utlrp.sql is being run.
SQL> @?/rdbms/admin/utlu111s.sql;

Oracle Database 11.1 Post-Upgrade Status Tool 05-31-2011 09:02:13
.
Component Status Version HH:MM:SS
.
Oracle Server
. VALID 11.1.0.6.0 00:13:30
JServer JAVA Virtual Machine
. VALID 11.1.0.6.0 00:04:11
Oracle Workspace Manager
. VALID 11.1.0.6.0 00:00:55
OLAP Analytic Workspace
. VALID 11.1.0.6.0 00:00:39
OLAP Catalog
. VALID 11.1.0.6.0 00:00:55
Oracle OLAP API
. VALID 11.1.0.6.0 00:00:23
Oracle Enterprise Manager
. VALID 11.1.0.6.0 00:07:39
Oracle XDK
. VALID 11.1.0.6.0 00:00:36
Oracle Text
. VALID 11.1.0.6.0 00:00:42
Oracle XML Database
. VALID 11.1.0.6.0 00:02:57
Oracle Database Java Packages
. VALID 11.1.0.6.0 00:00:20
Oracle Multimedia
. VALID 11.1.0.6.0 00:03:21
Spatial
. VALID 11.1.0.6.0 00:03:58
Oracle Expression Filter
. VALID 11.1.0.6.0 00:00:12
Oracle Rules Manager
. VALID 11.1.0.6.0 00:00:09
Gathering Statistics
. 00:03:48
Total Upgrade Time: 00:44:27


The above status shows that all the components has been upgraded to 11.1.0.6. and it
is in valid state now.

SQL> select cname,version, status, date_upgraded,org_version,prv_version from registry$;

CNAME VERSION STATUS DATE_UPGR ORG_VERSION PRV_VERSION
-------------------------------------------------- ------------------------------ ---------- --------- ------------------------------ ------------------------------
Oracle Database Catalog Views 11.1.0.6.0 1 10.2.0.1.0 10.2.0.1.0
Oracle Database Packages and Types 11.1.0.6.0 1 10.2.0.1.0 10.2.0.1.0
Oracle Workspace Manager 11.1.0.6.0 1 30-MAY-11 10.2.0.1.0
JServer JAVA Virtual Machine 11.1.0.6.0 1 10.2.0.1.0 10.2.0.1.0
Oracle XDK 11.1.0.6.0 1 10.2.0.1.0 10.2.0.1.0
Oracle Database Java Packages 11.1.0.6.0 1 10.2.0.1.0 10.2.0.1.0
Oracle Expression Filter 11.1.0.6.0 1 30-MAY-11 10.2.0.1.0
Oracle Data Mining 11.1.0.6.0 1 30-MAY-11 10.2.0.1.0
Oracle Text 11.1.0.6.0 1 30-MAY-11 10.2.0.1.0 10.2.0.1.0
Oracle XML Database 11.1.0.6.0 1 30-MAY-11 10.2.0.1.0 10.2.0.1.0
Oracle Rules Manager 11.1.0.6.0 1 30-MAY-11 10.2.0.1.0
Oracle Multimedia 11.1.0.6.0 1 30-MAY-11 10.2.0.1.0
OLAP Analytic Workspace 11.1.0.6.0 1 30-MAY-11 10.2.0.1.0
Oracle OLAP API 11.1.0.6.0 1 30-MAY-11 11.1.0.6.0 10.2.0.1.0
OLAP Catalog 11.1.0.6.0 1 30-MAY-11 10.2.0.1.0 10.2.0.1.0
Spatial 11.1.0.6.0 1 30-MAY-11 10.2.0.1.0 10.2.0.1.0
Oracle Enterprise Manager 11.1.0.6.0 1 30-MAY-11 10.2.0.1.0

17 rows selected.


-------------Upgrade process completed here-------------------------


Downgrade to 10g from 11g.


Steps:-

1) shutdown the database which been upgrade to 11g.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

2) Startup the database in downgrade mode.

SQL> startup downgrade;
ORACLE instance started.

Total System Global Area 355717120 bytes
Fixed Size 1299876 bytes
Variable Size 150997596 bytes
Database Buffers 197132288 bytes
Redo Buffers 6287360 bytes
Database mounted.
Database opened.

3) Drop the SYSMAN user inorder to drop EM config errors.

SQL> drop user sysman cascasde;


4) Run the catalog downgrade script.

SQL> @?/rdbms/admin/catdwngrd.sql


5) Shutdown the database.


6) Modify the parameter files and remove the 11g parameters and include the 10g parameters.
eg:-
Add :- background_dump_dest, user_dump_dest,core_dump_dest
Remove :- diagnostic_dest


7) Set the Oracle_home environment variables pointing to 10g home.

8) Startup the database in upgrade mode using the modified pfile and run the catalog reload script.

$ sqlplus / as sysdba

SQL> create spfile from pfile = '/u01/upg211g';

sql> startup upgrade;

sql> @?/rdbms/admin/catrelod.sql;

sql> shutdown immediate;

9) Startup the database in normal mode and check the version and status.

End of downgrade steps.




==================================================

To upgrade in Windows environment


1) Stop the oracle service which runs in the 10g home

> net stop oracleservicename

2) Delete the oracle service

> oradim -delete -sid oraclesidname

3) Create new oracle service in 11g home

> oradim -new -sid oraclesidname -pfile pfile_path

4) Set your oracle home and oracle sid environment variables pointing to the
11g home and startup database in upgrade mode.

> set oracle_home =
> set oracle_sid =
> sqlplus / as sysdba

sql> startup upgrade;

sql> @?\RDBMS\ADMIN\catupgrd.sql;

once upgrade is completed upgrade the timezone file if required.

5) Relocate the listener from 10g home to 11g home

> net stop oraclelistenerservicename

usage of sc -

sc is a command tool program to interact with the service
control manage or services .


Delete the listener service running in 10g home

> sc delete oraclelistenername

Create a new listener service in 11g home

> sc create oraclelistnername binPath= "C:\oracle11\bin\tnslsnr.exe"


================================================================












Point to remember:-

While upgrade from 10.2.0.4 to 11.2.0.1 while running the utlu112i.sql script i got the error as below.

SQL> @/u01/app/oracle/product/11.2.0/db_1/rdbms/admin/utlu112i.sql;
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 3652

On googling i have found a link
http://davidalejomarcos.wordpress.com/2010/05/07/ora-06512-executing-utlu112i-sql/

while states to disable the flashback option. Go through the link for more info.

Pls check the below link for 11.2 upgrade.

http://oracleflash.com/33/Oracle-11g-Release-2-Pre-Upgrade-tool-utlu112i-sql.html

DBMS_XPLAN EXamples

Reference
PL/SQL Packages and Types Reference
10g Release 1 (10.1)


DBMS_XPLAN

The DBMS_XPLAN package provides an easy way to display the output of the EXPLAIN PLAN command in several, predefined formats. You can also use the DBMS_XPLAN package to display the plan of a statement stored in the Automatic Workload Repository (AWR).It further provides a way to display the SQL execution plan and SQL execution runtime statistics for cached SQL cursors based on the information stored in the V$SQL_PLAN and V$SQL_PLAN_STATISTICS_ALL fixed views.

For more information on the EXPLAIN PLAN command and the AWR, see Oracle Database Performance Tuning Guide. For more information on the V$SQL_PLAN and V$SQL_PLAN_STATISTICS fixed views, see Oracle Database Reference.


Overview

The DBMS_XPLAN package supplies three table functions:

DISPLAY, to format and display the contents of a plan table
DISPLAY_CURSOR, to format and display the contents of the execution plan of any loaded cursor.
DISPLAY_AWR to format and display the contents of the execution plan of a stored SQL statement in the AWR.

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

Security Model
This package runs with the privileges of the calling user, not the package owner SYS). The table function DISPLAY_CURSOR requires to have select privileges on the following fixed views: V$SQL_PLAN, V$SESSION and V$SQL_PLAN_STATISTICS_ALL.

Using the DISPLAY_AWR function requires to have SELECT privileges on DBA_HIST_SQL_PLAN, DBA_HIST_SQLTEXT, and V$DATABASE.

All these privileges are automatically granted as part of the SELECT_CATALOG role.
--------------------------------------------------------------------------------

Examples
Displaying a Plan Table Using DBMS_XPLAN.DISPLAY
Execute an explain plan command on a SELECT statement:

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


This query produces the following output:

Plan hash value: 3693697075
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 57 | 6 (34)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 57 | 6 (34)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| EMP | 1 | 37 | 3 (34)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (34)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E"."DEPTNO"="D"."DEPTNO")
2 - filter("E"."ENAME"='benoit')

15 rows selected.

Displaying a Cursor Execution Plan Using DBMS_XPLAN.DISPLAY_CURSOR
By default, the table function DISPLAY_CURSOR formats the execution plan for the last SQL statement executed by the session. For example:

SELECT ename FROM emp e, dept d
WHERE e.deptno = d.deptno
AND e.empno=7369;

ENAME
----------
SMITH


To display the execution plan of the last executed statement for that session:

SET PAGESIZE 0
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR);


This query produces the following output:

Plan hash value: 3693697075, SQL hash value: 2096952573, child number: 0
------------------------------------------------------------------
SELECT ename FROM emp e, dept d WHERE e.deptno = d.deptno
AND e.empno=7369

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
|* 1 | HASH JOIN | | 1 | 16 | 6 (34)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| EMP | 1 | 13 | 3 (34)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DEPT | 4 | 12 | 3 (34)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E"."DEPTNO"="D"."DEPTNO")
2 - filter("E"."EMPNO"=7369)

21 rows selected.


You can also use the table function DISPLAY_CURSOR to display the execution plan for any loaded cursor stored in the cursor cache. In that case, you must supply a reference to the child cursor to the table function. This includes the SQL ID of the statement and optionally the child number.

Run a query with a distinctive comment:

SELECT /* TOTO */ ename, dname
FROM dept d join emp e USING (deptno);


Get sql_id and child_number for the preceding statement:

SELECT sql_id, child_number
FROM v$sql
WHERE sql_text LIKE '%TOTO%';

SQL_ID CHILD_NUMBER
---------- -----------------------------
gwp663cqh5qbf 0


Display the execution plan for the cursor:

SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(('gwp663cqh5qbf',0));

Plan hash value: 3693697075, SQL ID: gwp663cqh5qbf, child number: 0
--------------------------------------------------------
SELECT /* TOTO */ ename, dname
FROM dept d JOIN emp e USING (deptno);

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 7 (100)| |
| 1 | SORT GROUP BY | | 4 | 64 | 7 (43)| 00:00:01 |
|* 2 | HASH JOIN | | 14 | 224 | 6 (34)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DEPT | 4 | 44 | 3 (34)| 00:00:01 |
| 4 | TABLE ACCESS FULL| EMP | 14 | 70 | 3 (34)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("E"."DEPTNO"="D"."DEPTNO")


Instead of issuing two queries, one to the get the sql_id and child_number pair and one to display the plan, you can combine these in a single query:

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%';


Displaying a Plan Table with Parallel Information
By default, only relevant information is reported by the display and display_cursor table functions. In Displaying a Plan Table Using DBMS_XPLAN.DISPLAY, the query does not execute in parallel. Hence, information related to the parallelization of the plan is not reported. As shown in the following example, parallel information is reported only if the query executes in parallel.

ALTER TABLE emp PARALLEL;
EXPLAIN PLAN for
SELECT * FROM emp e, dept d
WHERE e.deptno = d.deptno
AND e.ename ='hermann'
ORDER BY e.empno;


Display the plan using the DBMS_XPLAN.DISPLAY table function

SET LINESIZE 130
SET PAGESIZE 0
SELECT * FROM table(DBMS_XPLAN.DISPLAY);
Plan hash value: 3693697345


-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |INOUT |PQ Distrib |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 117 | 6 (50) | 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (ORDER) |:TQ10003 | 1 | 117 | 6 (50) | 00:00:01 | Q1,03 | P->S | QC (ORDER)|
| 3 | SORT ORDER BY | | 1 | 117 | 6 (50) | 00:00:01 | Q1,03 | PCWP | |
| 4 | PX RECEIVE | | 1 | 117 | 5 (40) | 00:00:01 | Q1,03 | PCWP | |
| 5 | PX SEND RANGE |:TQ10002 | 1 | 117 | 5 (40) | 00:00:01 | Q1,02 | P->P | RANGE |
|* 6 | HASH JOIN | | 1 | 117 | 5 (40) | 00:00:01 | Q1,02 | PCWP | |
| 7 | PX RECEIVE | | 1 | 87 | 2 (50) | 00:00:01 | Q1,02 | PCWP | |
| 8 | PX SEND HASH |:TQ10001 | 1 | 87 | 2 (50) | 00:00:01 | Q1,01 | P->P | HASH |
| 9 | PX BLOCK ITERATOR | | 1 | 87 | 2 (50) | 00:00:01 | Q1,01 | PCWC | |
|* 10| TABLE ACCESS FULL | EMP | 1 | 87 | 2 (50) | 00:00:01 | Q1,01 | PCWP | |
| 11 | BUFFER SORT | | | | | | Q1,02 | PCWC | |
| 12 | PX RECEIVE | | 4 | 120 | 3 (34) | 00:00:01 | Q1,02 | PCWP | |
| 13 | PX SEND HASH |:TQ10000 | 4 | 120 | 3 (34) | 00:00:01 | | S->P | HASH |
| 14 | TABLE ACCESS FULL | DEPT | 4 | 120 | 3 (34) | 00:00:01 | | | |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("E"."DEPTNO"="D"."DEPTNO")
10 - filter("E"."ENAME"='hermann')
---------------------------------------------------


When the query is parallel, information related to parallelism is reported: table queue number (TQ column), table queue type (INOUT) and table queue distribution method (PQ Distrib).

By default, if several plans in the plan table match the statement_id parameter passed to the display table function (default value is NULL), only the plan corresponding to the last EXPLAIN PLAN command is displayed. Hence, there is no need to purge the plan table after each EXPLAIN PLAN. However, you should purge the plan table regularly (for example, by using the TRUNCATE TABLE command) to ensure good performance in the execution of the DISPLAY table function.

For ease of use, you can define a view on top of the display table function and then use that view to display the output of the EXPLAIN PLAN command:

Using a View to Display Last Explain Plan
# define plan view
CREATE VIEW PLAN AS SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

# display the output of the last explain plan command
SELECT * FROM PLAN;


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

Summary of DBMS_XPLAN Subprograms
Table 120-1 DBMS_XPLAN Package Subprograms
Subprogram Description
DISPLAY_AWR Function
Displays the contents of an execution plan stored in the AWR

DISPLAY Function
Displays the contents of the plan table

DISPLAY_CURSOR Function
Displays the execution plan of any cursor in the cursor cache


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

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

Parameter Description
sql_id
Specifies the SQL_ID of the SQL statement. You can retrieve the appropriate value for the SQL statement of interest by querying the column SQL_ID in DBA_HIST_SQLTEXT.

plan_hash_value
Specifies the PLAN_HASH_VALUE of a SQL statement. This parameter is optional. If omitted, the table function will return all stored execution plans for a given SQL_ID.

db_id
Specifies the database_id for which the plan of the SQL statement, identified by SQL_ID should be displayed. If not supplied, the database_id of the local database will be used, as shown in V$DATABASE.

format
Controls the level of details for the plan. It has the same set of values than the table function DISPLAY, that is, BASIC, TYPICAL, SERIAL and ALL.


Usage Notes
To use the DISPLAY_AWR functionality, the calling user must have SELECT privilege on DBA_HIST_SQL_PLAN. DBA_HIST_SQLTEXT, and V$DATABASE, otherwise it will show an appropriate error message.

Examples
To display the execution plan of all children associated to the SQL ID 'atfwcg8anrykp':

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


To display the execution plan of all stored SQL statements containing the string 'TOTO':

SELECT tf.* FROM DBA_HIST_SQLTEXT ht, table
(DBMS_XPLAN.DISPLAY_AWR(ht.sql_id,null, null, 'ALL' )) tf
WHERE ht.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');

Parameters
table_name
Specifies the table name where the plan is stored. This parameter defaults to PLAN_TABLE, which is the default plan table for the EXPLAIN PLAN command. If NULL is specified it also defaults to PLAN_TABLE.

statement_id
Specifies the statement_id of the plan to be displayed. This parameter defaults to NULL, which is the default when the EXPLAIN PLAN command is executed without a set statement_id clause.If no statement_id is specified, the function will show you the plan of the most recent explained statement.

format
Controls the level of details for the plan. It accepts four values:

BASIC: Displays the minimum information in the plan--the operation ID, the object name, and the operation option.
TYPICAL: This is the default. Displays the most relevant information in the plan. Partition pruning, parallelism, and predicates are displayed only when available.
ALL: Maximum level. Includes information displayed with the TYPICAL level and adds projection information as well as SQL statements generated for parallel execution servers (only if parallel).
SERIAL: Like TYPICAL except that the parallel information is not displayed, even if the plan executes in parallel.


Examples
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'));


To display the plan for a statement identified by 'foo', such as statement_id='foo':

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


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

DISPLAY_CURSOR Function
This table function displays the explain plan of any cursor loaded in the cursor cache.

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

Parameters
sql_id
Specifies the SQL_ID of the SQL statement in the cursor cache. You can retrieve the appropriate value by querying the column SQL_ID in V$SQL or V$SQLAREA. Alternatively, you could choose the column PREV_SQL_ID for a specific session out of V$SESSION. This parameter defaults to NULL in which case the plan of the last cursor executed by the session will be displayed.

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.

format
Controls the level of details for the plan. It has the same set of values than the table function 'DISPLAY', that is, 'BASIC', 'TYPICAL', 'SERIAL' and 'ALL'. Two additional values are also supported to display run-time statistics for the cursor:

RUNSTATS_LAST: Displays the runtime statistics for the last execution of the cursor.
RUNSTATS_TOT: Displays the total aggregated runtime statistics for all executions of a specific SQL statement since the statement was first parsed and executed.
Format options 'RUNSTATS_LAST' and 'RUNSTATS_TOT' can only be used if the target cursor was compiled and executed with the initialization parameter 'statistics_level' set to 'ALL'.


Usage Notes:
To use the DISPLAY_CURSOR functionality, the calling user must have SELECT privilege on the fixed views V$SQL_PLAN_STATISTICS_ALL, V$SQL and V$SQL_PLAN, otherwise it will show an appropriate error message.

Examples
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 to the SQL ID 'atfwcg8anrykp':

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


To display runtime statistics for the cursor included in the preceding statement:

SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('atfwcg8anrykp', null, 'RUNSTATS_
LAST');


Understanding % CPU Cost in Explain Plan

What is the Meaning of the %CPU Column in an Explain Plan?

Let’s try creating an explain plan on Oracle 11.2.0.1 for a query:
EXPLAIN PLAN FOR
SELECT
T1.C1,
T1.C2,
T1.C3
FROM
T1,
(SELECT
C1,
C2
FROM
T1
WHERE
MOD(C1,3)=0) V
WHERE
T1.C1=V.C1(+)
AND V.C1 IS NULL
ORDER BY
T1.C1 DESC;
The above command wrote a couple of rows into the PLAN_TABLE table. At this point, we should probably consult the documentation to understand the columns in the

PLAN_TABLE table.

COST: Cost of the operation as estimated by the optimizer’s query approach. Cost is not determined for table access operations. The value of this column does not have any particular unit of measurement; it is merely a weighted value used to compare costs of execution plans. The value of this column is a function of the CPU_COST and IO_COST columns.

IO_COST: I/O cost of the operation as estimated by the query optimizer’s approach. The value of this column is proportional to the number of data blocks read by the operation. For statements that use the rule-based approach, this column is null.

CPU_COST: CPU cost of the operation as estimated by the query optimizer’s approach. The value of this column is proportional to the number of machine cycles required for the operation. For statements that use the rule-based approach, this column is null.

We found a couple of interesting columns in the PLAN_TABLE table, so let’s query the table


SELECT
ID,
COST,
IO_COST,
CPU_COST
FROM
PLAN_TABLE;

ID COST IO_COST CPU_COST
--- ----- -------- ----------
0 1482 1467 364928495
1 1482 1467 364928495
2 898 887 257272866
3 889 887 42272866
4 0 0 2150
Now let’s display the execution plan:
SELECT
*
FROM
TABLE(DBMS_XPLAN.DISPLAY);

Plan hash value: 1923834833

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 99000 | 1836K| | 1482 (2)| 00:00:18 |
| 1 | SORT ORDER BY | | 99000 | 1836K| 2736K| 1482 (2)| 00:00:18 |
| 2 | NESTED LOOPS ANTI | | 99000 | 1836K| | 898 (2)| 00:00:11 |
| 3 | TABLE ACCESS FULL| T1 | 100K| 1367K| | 889 (1)| 00:00:11 |
|* 4 | INDEX UNIQUE SCAN| SYS_C0018049 | 10 | 50 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T1"."C1"="C1")
filter(MOD("C1",3)=0)
The %CPU is 2 for ID 0, 1, and 2, and the %CPU is 1 for ID 3. Let’s return to the query of the PLAN_TABLE table and perform a couple of calculations:
SELECT
ID,
COST,
IO_COST,
COST-IO_COST DIFF,
CEIL(DECODE(COST,0,0,(COST-IO_COST)/COST)*100) PER_CPU,
CPU_COST
FROM
PLAN_TABLE;

ID COST IO_COST DIFF PER_CPU CPU_COST
--- ----- -------- ----- -------- ----------
0 1482 1467 15 2 364928495
1 1482 1467 15 2 364928495
2 898 887 11 2 257272866
3 889 887 2 1 42272866
4 0 0 0 0 2150
In the above, I subtracted the IO_COST column from the COST column to derive the DIFF column. I then divided the value in the DIFF column by the COST column, multiplied the result by 100 to convert the number to a percent, and then rounded up the result to derive the PER_CPU column. The PER_CPU column seems to match the %CPU column in the DBMS_XPLAN output. Let’s try another SQL statement:
DELETE FROM PLAN_TABLE;

EXPLAIN PLAN FOR
SELECT
C1
FROM
T1
WHERE
'A'||C1 LIKE 'A%';
Now let’s run the query against the PLAN_TABLE table to see if we are able to predict the values that will appear in the %CPU column of the DBMS_XPLAN output:
SELECT
ID,
COST,
IO_COST,
COST-IO_COST DIFF,
CEIL(DECODE(COST,0,0,(COST-IO_COST)/COST)*100) PER_CPU,
CPU_COST
FROM
PLAN_TABLE;

ID COST IO_COST DIFF PER_CPU CPU_COST
--- ----- -------- ----- -------- ----------
0 54 52 2 4 43331709
1 54 52 2 4 43331709
The above indicates that the %CPU column should show the number 4 on both rows of the execution plan.
SELECT
*
FROM
TABLE(DBMS_XPLAN.DISPLAY);

Plan hash value: 2950179127

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5000 | 25000 | 54 (4)| 00:00:01 |
|* 1 | INDEX FAST FULL SCAN| SYS_C0018049 | 5000 | 25000 | 54 (4)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter('A'||TO_CHAR("C1") LIKE 'A%')
One of my previous blog articles showed the following execution plan – this was the actual plan displayed by DBMS_XPLAN.DISPLAY_CURSOR after the SQL statement executed:
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 247 (100)| | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 10000 | 2236K| 247 (1)| 00:00:03 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 10000 | 2236K| 247 (1)| 00:00:03 | Q1,00 | PCWC | |
|* 4 | TABLE ACCESS FULL| T1 | 10000 | 2236K| 247 (1)| 00:00:03 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
4 - access(:Z>=:Z AND :Z<=:Z)
filter(("C1"<=10000 AND "C1">=1))
Is there anything strange about the %CPU column in the above plan?
Incidentally, a query of SYS.AUX_STATS$ shows the following output (values are used to determine the impact of the CPU_COST column that is displayed in the PLAN_TABLE table):
SELECT
PNAME,
PVAL1
FROM
SYS.AUX_STATS$
WHERE
PNAME IN ('CPUSPEED','CPUSPEEDNW');

PNAME PVAL1
---------- ----------
CPUSPEEDNW 2031.271
CPUSPEED

(Thanks Mr.Charles Hooper)

Configure Shared Servers

Notes from :-
Oracle® Database Administrator's Guide
11g Release 1 (11.1)


Configuring Oracle Database for Shared ServerShared memory resources are preconfigured to allow the enabling of shared server at run time. You need not configure it by specifying parameters in your initialization parameter file, but you can do so if that better suits your environment. You can start dispatchers and shared server processes (shared servers) dynamically using the ALTER SYSTEM statement.

This section discusses how to enable shared server and how to set or alter shared server initialization parameters. It contains the following topics:

•Initialization Parameters for Shared Server
•Enabling Shared Server
•Configuring Dispatchers
•Shared Server Data Dictionary Views

The following initialization parameters control shared server operation:

•SHARED_SERVERS: Specifies the initial number of shared servers to start and the minimum number of shared servers to keep. This is the only required parameter for using shared servers.

•MAX_SHARED_SERVERS: Specifies the maximum number of shared servers that can run simultaneously.

•SHARED_SERVER_SESSIONS: Specifies the total number of shared server user sessions that can run simultaneously. Setting this parameter enables you to reserve user sessions for dedicated servers.

•DISPATCHERS: Configures dispatcher processes in the shared server architecture.

•MAX_DISPATCHERS: Specifies the maximum number of dispatcher processes that can run simultaneously. This parameter can be ignored for now. It will only be useful in a future release when the number of dispatchers is auto-tuned according to the number of concurrent connections.

•CIRCUITS: Specifies the total number of virtual circuits that are available for inbound and outbound network sessions.


Shared server can be started dynamically by setting the SHARED_SERVERS parameter to a nonzero value with the ALTER SYSTEM statement, or SHARED_SERVERS can be included at database startup in the initialization parameter file. If SHARED_SERVERS is not included in the initialization parameter file, or is included but is set to 0, then shared server is not enabled at database startup.

Note:

For backward compatibility, if SHARED_SERVERS is not included in the initialization parameter file at database startup, but DISPATCHERS is included and it specifies at least one dispatcher, shared server is enabled. In this case, the default for SHARED_SERVERS is 1.
However, if neither SHARED_SERVERS nor DISPATCHERS is included in the initialization file, you cannot start shared server after the instance is brought up by just altering the DISPATCHERS parameter. You must specifically alter SHARED_SERVERS to a nonzero value to start shared server.

Determining a Value for SHARED_SERVERS
The SHARED_SERVERS initialization parameter specifies the minimum number of shared servers that you want created when the instance is started. After instance startup, Oracle Database can dynamically adjust the number of shared servers based on how busy existing shared servers are and the length of the request queue.

In typical systems, the number of shared servers stabilizes at a ratio of one shared server for every ten connections. For OLTP applications, when the rate of requests is low, or when the ratio of server usage to request is low, the connections-to-servers ratio could be higher. In contrast, in applications where the rate of requests is high or the server usage-to-request ratio is high, the connections-to-server ratio could be lower.

The PMON (process monitor) background process cannot terminate shared servers below the value specified by SHARED_SERVERS. Therefore, you can use this parameter to stabilize the load and minimize strain on the system by preventing PMON from terminating and then restarting shared servers because of coincidental fluctuations in load.

If you know the average load on your system, you can set SHARED_SERVERS to an optimal value. The following example shows how you can use this parameter:

Assume a database is being used by a telemarketing center staffed by 1000 agents. On average, each agent spends 90% of the time talking to customers and only 10% of the time looking up and updating records. To keep the shared servers from being terminated as agents talk to customers and then spawned again as agents access the database, a DBA specifies that the optimal number of shared servers is 100.

However, not all work shifts are staffed at the same level. On the night shift, only 200 agents are needed. Since SHARED_SERVERS is a dynamic parameter, a DBA reduces the number of shared servers to 20 at night, thus allowing resources to be freed up for other tasks such as batch jobs.

Decreasing the Number of Shared Server Processes
You can decrease the minimum number of shared servers that must be kept active by dynamically setting the SHARED_SERVERS parameter to a lower value. Thereafter, until the number of shared servers is decreased to the value of the SHARED_SERVERS parameter, any shared servers that become inactive are marked by PMON for termination.

The following statement reduces the number of shared servers:

ALTER SYSTEM SET SHARED_SERVERS = 5;
Setting SHARED_SERVERS to 0 disables shared server. For more information, please refer to "Disabling Shared Servers".

Limiting the Number of Shared Server Processes
The MAX_SHARED_SERVERS parameter specifies the maximum number of shared servers that can be automatically created by PMON. It has no default value. If no value is specified, then PMON starts as many shared servers as is required by the load, subject to these limitations:

•The process limit (set by the PROCESSES initialization parameter)

•A minimum number of free process slots (at least one-eighth of the total process slots, or two slots if PROCESSES is set to less than 24)

•System resources

Note:

On Windows NT, take care when setting MAX_SHARED_SERVERS to a high value, because each server is a thread in a common process.
The value of SHARED_SERVERS overrides the value of MAX_SHARED_SERVERS. Therefore, you can force PMON to start more shared servers than the MAX_SHARED_SERVERS value by setting SHARED_SERVERS to a value higher than MAX_SHARED_SERVERS. You can subsequently place a new upper limit on the number of shared servers by dynamically altering the MAX_SHARED_SERVERS to a value higher than SHARED_SERVERS.

The primary reason to limit the number of shared servers is to reserve resources, such as memory and CPU time, for other processes. For example, consider the case of the telemarketing center discussed previously:

The DBA wants to reserve two thirds of the resources for batch jobs at night. He sets MAX_SHARED_SERVERS to less than one third of the maximum number of processes (PROCESSES). By doing so, the DBA ensures that even if all agents happen to access the database at the same time, batch jobs can connect to dedicated servers without having to wait for the shared servers to be brought down after processing agents' requests.

Another reason to limit the number of shared servers is to prevent the concurrent run of too many server processes from slowing down the system due to heavy swapping, although PROCESSES can serve as the upper bound for this rather than MAX_SHARED_SERVERS.

Still other reasons to limit the number of shared servers are testing, debugging, performance analysis, and tuning. For example, to see how many shared servers are needed to efficiently support a certain user community, you can vary MAX_SHARED_SERVERS from a very small number upward until no delay in response time is noticed by the users.

Limiting the Number of Shared Server Sessions
The SHARED_SERVER_SESSIONS initialization parameter specifies the maximum number of concurrent shared server user sessions. Setting this parameter, which is a dynamic parameter, lets you reserve database sessions for dedicated servers. This in turn ensures that administrative tasks that require dedicated servers, such as backing up or recovering the database, are not preempted by shared server sessions.

This parameter has no default value. If it is not specified, the system can create shared server sessions as needed, limited by the SESSIONS initialization parameter.

Protecting Shared Memory
The CIRCUITS parameter sets a maximum limit on the number of virtual circuits that can be created in shared memory. This parameter has no default. If it is not specified, then the system can create circuits as needed, limited by the DISPATCHERS initialization parameter and system resources.

Configuring DispatchersThe DISPATCHERS initialization parameter configures dispatcher processes in the shared server architecture. At least one dispatcher process is required for shared server to work.If you do not specify a dispatcher, but you enable shared server by setting SHARED_SERVER to a nonzero value, then by default Oracle Database creates one dispatcher for the TCP protocol. The equivalent DISPATCHERS explicit setting of the initialization parameter for this configuration is:

dispatchers="(PROTOCOL=tcp)"
You can configure more dispatchers, using the DISPATCHERS initialization parameter, if either of the following conditions apply:

•You need to configure a protocol other than TCP/IP. You configure a protocol address with one of the following attributes of the DISPATCHERS parameter:

◦ADDRESS

◦DESCRIPTION

◦PROTOCOL

•You want to configure one or more of the optional dispatcher attributes:

◦DISPATCHERS

◦CONNECTIONS

◦SESSIONS

◦TICKS

◦LISTENER

◦MULTIPLEX

◦POOL

◦SERVICE

Note:

Database Configuration Assistant helps you configure this parameter.
DISPATCHERS Initialization Parameter Attributes
This section provides brief descriptions of the attributes that can be specified with the DISPATCHERS initialization parameter.

A protocol address is required and is specified using one or more of the following attributes:

Attribute Description
ADDRESS Specify the network protocol address of the endpoint on which the dispatchers listen.
DESCRIPTION Specify the network description of the endpoint on which the dispatchers listen, including the network protocol address. The syntax is as follows:
(DESCRIPTION=(ADDRESS=...))

PROTOCOL Specify the network protocol for which the dispatcher generates a listening endpoint. For example:
(PROTOCOL=tcp)
See the Oracle Database Net Services Reference for further information about protocol address syntax.



The following attribute specifies how many dispatchers this configuration should have. It is optional and defaults to 1.

Attribute Description
DISPATCHERS Specify the initial number of dispatchers to start.


The following attributes tell the instance about the network attributes of each dispatcher of this configuration. They are all optional.

Attribute Description
CONNECTIONS Specify the maximum number of network connections to allow for each dispatcher.
SESSIONS Specify the maximum number of network sessions to allow for each dispatcher.
TICKS Specify the duration of a TICK in seconds. A TICK is a unit of time in terms of which the connection pool timeout can be specified. Used for connection pooling.
LISTENER Specify an alias name for the listeners with which the PMON process registers dispatcher information. Set the alias to a name that is resolved through a naming method.
MULTIPLEX Used to enable the Oracle Connection Manager session multiplexing feature.
POOL Used to enable connection pooling.
SERVICE Specify the service names the dispatchers register with the listeners.


You can specify either an entire attribute name a substring consisting of at least the first three characters. For example, you can specify SESSIONS=3, SES=3, SESS=3, or SESSI=3, and so forth.

See Also:

Oracle Database Reference for more detailed descriptions of the attributes of the DISPATCHERS initialization parameter
Determining the Number of Dispatchers
Once you know the number of possible connections for each process for the operating system, calculate the initial number of dispatchers to create during instance startup, for each network protocol, using the following formula:

Number of dispatchers =
CEIL ( max. concurrent sessions / connections for each dispatcher )
CEIL returns the result roundest up to the next whole integer.

For example, assume a system that can support 970 connections for each process, and that has:

•A maximum of 4000 sessions concurrently connected through TCP/IP and
•A maximum of 2,500 sessions concurrently connected through TCP/IP with SSL

The DISPATCHERS attribute for TCP/IP should be set to a minimum of five dispatchers (4000 / 970), and for TCP/IP with SSL three dispatchers (2500 / 970:

DISPATCHERS='(PROT=tcp)(DISP=5)', '(PROT-tcps)(DISP=3)'
Depending on performance, you may need to adjust the number of dispatchers.

Setting the Initial Number of DispatchersYou can specify multiple dispatcher configurations by setting DISPATCHERS to a comma separated list of strings, or by specifying multiple DISPATCHERS parameters in the initialization file. If you specify DISPATCHERS multiple times, the lines must be adjacent to each other in the initialization parameter file. Internally, Oracle Database assigns an INDEX value (beginning with zero) to each DISPATCHERS parameter. You can later refer to that DISPATCHERS parameter in an ALTER SYSTEM statement by its index number.

Some examples of setting the DISPATCHERS initialization parameter follow.

Example: Typical
This is a typical example of setting the DISPATCHERS initialization parameter.

DISPATCHERS="(PROTOCOL=TCP)(DISPATCHERS=2)"
Example: Forcing the IP Address Used for Dispatchers
The following hypothetical example will create two dispatchers that will listen on the specified IP address. The address must be a valid IP address for the host that the instance is on. (The host may be configured with multiple IP addresses.)

DISPATCHERS="(ADDRESS=(PROTOCOL=TCP)(HOST=144.25.16.201))(DISPATCHERS=2)"
Example: Forcing the Port Used by Dispatchers
To force the dispatchers to use a specific port as the listening endpoint, add the PORT attribute as follows:

DISPATCHERS="(ADDRESS=(PROTOCOL=TCP)(PORT=5000))"
DISPATCHERS="(ADDRESS=(PROTOCOL=TCP)(PORT=5001))"
Altering the Number of Dispatchers
You can control the number of dispatcher processes in the instance. Unlike the number of shared servers, the number of dispatchers does not change automatically. You change the number of dispatchers explicitly with the ALTER SYSTEM statement. In this release of Oracle Database, you can increase the number of dispatchers to more than the limit specified by the MAX_DISPATCHERS parameter. It is planned that MAX_DISPATCHERS will be taken into consideration in a future release.

Monitor the following views to determine the load on the dispatcher processes:

•V$QUEUE

•V$DISPATCHER

•V$DISPATCHER_RATE

See Also:

Oracle Database Performance Tuning Guide for information about monitoring these views to determine dispatcher load and performance
If these views indicate that the load on the dispatcher processes is consistently high, then performance may be improved by starting additional dispatcher processes to route user requests. In contrast, if the load on dispatchers is consistently low, reducing the number of dispatchers may improve performance.

To dynamically alter the number of dispatchers when the instance is running, use the ALTER SYSTEM statement to modify the DISPATCHERS attribute setting for an existing dispatcher configuration. You can also add new dispatcher configurations to start dispatchers with different network attributes.

When you reduce the number of dispatchers for a particular dispatcher configuration, the dispatchers are not immediately removed. Rather, as users disconnect, Oracle Database terminates dispatchers down to the limit you specify in DISPATCHERS,

For example, suppose the instance was started with this DISPATCHERS setting in the initialization parameter file:

DISPATCHERS='(PROT=tcp)(DISP=2)', '(PROT=tcps)(DISP=2)'
To increase the number of dispatchers for the TCP/IP protocol from 2 to 3, and decrease the number of dispatchers for the TCP/IP with SSL protocol from 2 to 1, you can issue the following statement:

ALTER SYSTEM SET DISPATCHERS = '(INDEX=0)(DISP=3)', '(INDEX=1)(DISP=1)';
or

ALTER SYSTEM SET DISPATCHERS = '(PROT=tcp)(DISP=3)', '(PROT-tcps)(DISP=1)';
Note:

You need not specify (DISP=1). It is optional because 1 is the default value for the DISPATCHERS parameter.
If fewer than three dispatcher processes currently exist for TCP/IP, the database creates new ones. If more than one dispatcher process currently exists for TCP/IP with SSL, then the database terminates the extra ones as the connected users disconnect.

Suppose that instead of changing the number of dispatcher processes for the TCP/IP protocol, you want to add another TCP/IP dispatcher that supports connection pooling. You can do so by entering the following statement:

ALTER SYSTEM SET DISPATCHERS = '(INDEX=2)(PROT=tcp)(POOL=on)';
The INDEX attribute is needed to add the new dispatcher configuration. If you omit (INDEX=2) in the preceding statement, then the TCP/IP dispatcher configuration at INDEX 0 will be changed to support connection pooling, and the number of dispatchers for that configuration will be reduced to 1, which is the default when the number of dispatchers (attribute DISPATCHERS) is not specified.

Notes on Altering Dispatchers
•The INDEX keyword can be used to identify which dispatcher configuration to modify. If you do not specify INDEX, then the first dispatcher configuration matching the DESCRIPTION, ADDRESS, or PROTOCOL specified will be modified. If no match is found among the existing dispatcher configurations, then a new dispatcher will be added.

•The INDEX value can range from 0 to n-1, where n is the current number of dispatcher configurations. If your ALTER SYSTEM statement specifies an INDEX value equal to n, where n is the current number of dispatcher configurations, a new dispatcher configuration will be added.

•To see the values of the current dispatcher configurations--that is, the number of dispatchers, whether connection pooling is on, and so forth--query the V$DISPATCHER_CONFIG dynamic performance view. To see which dispatcher configuration a dispatcher is associated with, query the CONF_INDX column of the V$DISPATCHER view.

•When you change the DESCRIPTION, ADDRESS, PROTOCOL, CONNECTIONS, TICKS, MULTIPLEX, and POOL attributes of a dispatcher configuration, the change does not take effect for existing dispatchers but only for new dispatchers. Therefore, in order for the change to be effective for all dispatchers associated with a configuration, you must forcibly kill existing dispatchers after altering the DISPATCHERS parameter, and let the database start new ones in their place with the newly specified properties.

The attributes LISTENER and SERVICES are not subject to the same constraint. They apply to existing dispatchers associated with the modified configuration. Attribute SESSIONS applies to existing dispatchers only if its value is reduced. However, if its value is increased, it is applied only to newly started dispatchers.

Shutting Down Specific Dispatcher Processes
With the ALTER SYSTEM statement, you leave it up to the database to determine which dispatchers to shut down to reduce the number of dispatchers. Alternatively, it is possible to shut down specific dispatcher processes. To identify the name of the specific dispatcher process to shut down, use the V$DISPATCHER dynamic performance view.

SELECT NAME, NETWORK FROM V$DISPATCHER;
Each dispatcher is uniquely identified by a name of the form Dnnn.

To shut down dispatcher D002, issue the following statement:

ALTER SYSTEM SHUTDOWN IMMEDIATE 'D002';
The IMMEDIATE keyword stops the dispatcher from accepting new connections and the database immediately terminates all existing connections through that dispatcher. After all sessions are cleaned up, the dispatcher process shuts down. If IMMEDIATE were not specified, the dispatcher would wait until all of its users disconnected and all of its connections terminated before shutting down.

Disabling Shared Servers
You disable shared server by setting SHARED_SERVERS to 0. No new client can connect in shared mode. However, when you set SHARED_SERVERS to 0, Oracle Database retains some shared servers until all shared server connections are closed. The number of shared servers retained is either the number specified by the preceding setting of SHARED_SERVERS or the value of the MAX_SHARED_SERVERS parameter, whichever is smaller. If both SHARED_SERVERS and MAX_SHARED_SERVERS are set to 0, then all shared servers will terminate and requests from remaining shared server clients will be queued until the value of SHARED_SERVERS or MAX_SHARED_SERVERS is raised again.

To terminate dispatchers once all shared server clients disconnect, enter this statement:

ALTER SYSTEM SET DISPATCHERS = '';
Shared Server Data Dictionary ViewsThe following views are useful for obtaining information about your shared server configuration and for monitoring performance.

View Description
V$DISPATCHER Provides information on the dispatcher processes, including name, network address, status, various usage statistics, and index number.
V$DISPATCHER_CONFIG Provides configuration information about the dispatchers.
V$DISPATCHER_RATE Provides rate statistics for the dispatcher processes.
V$QUEUE Contains information on the shared server message queues.
V$SHARED_SERVER Contains information on the shared servers.
V$CIRCUIT Contains information about virtual circuits, which are user connections to the database through dispatchers and servers.
V$SHARED_SERVER_MONITOR Contains information for tuning shared server.
V$SGA Contains size information about various system global area (SGA) groups. May be useful when tuning shared server.
V$SGASTAT Contains detailed statistical information about the SGA, useful for tuning.
V$SHARED_POOL_RESERVED Lists statistics to help tune the reserved pool and space within the shared pool.



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

Configuring Shared Server in Oracle 10g


1. The DISPATCHERS parameter
When you are configuring Oracle Shared Server you must set the initialization parameters for your instance.

On most systems, you only need to configure the DISPATCHERS parameter. The other parameters are optional and have appropriate default settings.

Normally, the DISPATCHERS parameter is already configured to start one dispatcher for each network protocol and to service the XML database. However, this depends on the options selected when you created your database.

You can apply multiple sets of values in the DISPATCHERS parameter using the format

'< parameters for first set>','< parameters for second set>'

Various attributes, or arguments, can be added to each DISPATCHERS parameter using a name-value syntax similar to that used by Oracle Net Services.

The syntax allows you to specify existing and additional non-case-sensitive attributes in a position-independent manner.

For example, the following DISPATCHERS parameter contains two attributes that tell the dispatcher which protocol it is for and how many dispatchers it has to start:

DISPATCHERS = '(PROTOCOL=TCP) (DISPATCHERS=3)'

The DISPATCHERS parameter is a String parameter type. Its parameter class is Dynamic and its default value is NULL.

NULL means that, unless it is configured with at least one dispatcher, no dispatchers will be started.

DISPATCHERS = '(PROTOCOL=TCP) (DISPATCHERS=3)'

When estimating how many dispatchers to use, you must be aware that the number of connections a dispatcher can deal with depends on the type of workload.

However, a good rule of thumb is to provide one dispatcher for every fifty concurrent database connections.

Of all the dispatcher attributes, only PROTOCOL is required. Also, it's only necessary to enter the three-letter abbreviation of the argument when entering the attribute.

Some of the attributes that can be added to a DISPATCHERS parameter are:

•PROTOCOL ( PRO or PROT)
•DISPATCHERS ( DIS or DISP)
•SERVICE ( SER or SERV)
•LISTENER ( LIS or LIST)
•SESSION ( SES or SESS)
•CONNECTIONS ( CON or CONN)
PROTOCOL ( PRO or PROT)
PROTOCOL indicates the network protocol that the dispatcher is opening a listening endpoint for. The protocol usually used is TCP.
DISPATCHERS ( DIS or DISP)
DISPATCHERS provides the number of dispatchers to start. The default value is 1.
SERVICE ( SER or SERV)
SERVICE refers to the Oracle Net Services name that is registered with the listener by the dispatcher. If a service name is not provided, the dispatcher registers the values in SERVICE_NAMES.
LISTENER ( LIS or LIST)
LISTENER is the listener's alias name. This is used by the PMON process to register dispatcher information. The alias should be resolved through a naming method.
SESSION ( SES or SESS)
SESSION represents a value for the maximum number of network sessions that each dispatcher can handle. The default value depends on the operating system used.

CONNECTIONS ( CON or CONN)
CONNECTIONS represents a value for the maximum number of network connections that each dispatcher can handle. The default value depends on the operating system used. For example, Windows and Sun Solaris have a default value of 1024.
Question

Which dispatcher attribute must be entered in the DISPATCHER S parameter?

Options:

1.DISPATCHERS
2.LISTENER
3.PROTOCOL
4.SERVICE
Answer
PROTOCOL is the only dispatcher attribute that is required in the DISPATCHER S parameter.

Option 1 is incorrect. The value set in the DISPATCHERS attribute indicates the number of dispatchers to start for the network protocol.

Option 2 is incorrect. LISTENER is not required. This value provides the listener's alias name, which is used by the PMON process to register dispatcher information.

Option 3 is correct. PROTOCOL is the only required attribute and it indicates the name of the network protocol for which the dispatcher is opening a listening endpoint. Usually, the protocol used is TCP.

Option 4 is incorrect. Although it is not required, SERVICE refers to the Oracle Net Services name that is registered with the listener by the dispatcher.

To change the initialization parameters in Enterprise Manager you select All Initialization Parameters in the Instance section of the Administration tabbed page.

To find the dispatchers in the list of Initialization Parameters you enter d ispatchers in the Filter field and click Go.

The DISPATCHERS parameter in this server uses the protocol TCP and the Oracle Net Services name orcl1XDB.

You now want to set the number of dispatchers to 4 in the parameter.

You Type (DIS=4) in the Value field for the dispatchers parameter and click Apply.

The DISPATCHERS parameter is now updated to have a maximum of four dispatchers.

You can also specify other attributes in the parameter, such as Sessions, Listener, or Connections.

Suppose you now want to specify 200 as the maximum number of network connections to allow for each dispatcher.

You type (CONN=200) into the Value field for the DISPATCHERS parameter and click Apply.

The DISPATCHERS parameter now specifies that the maximum number of network connections is 200 for each dispatcher.

Question
You now have to update the DISPATCHERS parameter so that there will be five dispatchers for the instance.

This task requires you to enter the argument that sets 5 dispatchers to start for the TCP protocol. Which of these options allows you to complete this task?

Options:

1.You type (DIS=5) into the Value field in the Initialization Parameter page and click Apply.
2.You type (SESS=5) into the Value field in the Initialization Parameter page and click Apply.
Answer
You type (DIS=5) into the Value field in the Initialization Parameter page and click Apply.


2. SHARED_SERVERS and MAX_SHARED_SERVERS
Although the DISPATCHERS parameter is the only parameter that needs to be configured in a shared server, other parameters can be changed when configuring the instance.

For example, you may want to set the maximum and minimum number of server processes that can run concurrently.

To do this you set the MAX_SHARED_SERVERS and SHARED_SERVERS parameters.

To specify the minimum number of server processes that can run concurrently, you configure the SHARED_SERVER parameter.

It is not essential to set this parameter because the Common Request Queue is monitored by the instance.

The instance can add shared servers as the queue needs them, and take them off again when they are no longer required.

To find the SHARED_SERVERS parameter in the Initialization Parameters page, you enter shared_servers into the Filter field and click Go.

The SHARED_SERVERS parameter type is Integer and its class is Dynamic. Its default value is 0 if DISPATCHERS is NULL , and 1 if DISPATCHERS is set.

The range of values that can be used for the SHARED_SERVERS parameter varies according to the operating system.

Common practice suggests that you set one shared server for every 25 concurrent database connections.

You now want to set the SHARED_SERVERS parameter with enough shared servers for 75 concurrent database connections.

You type 3 in the Value field for the SHARED_SERVERS parameter and click Apply.

The SHARED_SERVERS parameter is now set to 3.

You configure the MAX_SHARED_SERVERS parameter to specify the maximum number of server processes that can run simultaneously.

It is important to set this parameter to control the instance, as it creates additional shared servers to service the needs of the common request queue.

The MAX_SHARED_SERVERS is an Integer parameter type, and its class is Dynamic. It has no default value, and its range of values depends on the operating system.

You now want to set the maximum number of shared servers that can be run simultaneously to eight.

You type 8 into the Value field for the MAX_SHARED_SERVERS parameter and click Apply.

The MAX_SHARED_SERVERS parameter is now set to 8.

It's best to set the MAX_SHARED_SERVERS parameter when activity is at its highest. You should check the limit against the activity of the shared servers to find the right setting.

You can find the maximum number of servers started by querying the V$SHARED_SERVER_MONITOR data dictionary view.

Question
Although the instance can add shared servers as required by the Common Request Queue, you decide to set it in advance to be sure that there is an adequate number of shared servers to meet the number of concurrent database connections.

This task requires you to input the value to ensure that a minimum of twelve shared server processes will run simultaneously. Which of these options allows you to complete this task?

Options:

1.You type 12 into the Value field for the max_shared_servers parameter in the Initialization Parameter page and click Apply.
2.You type 12 into the Value field for the shared_servers parameter in the Initialization Parameter page and click Apply.
Answer
You type 12 into the Value field for the SHARED_SERVERS parameter in the Initialization Parameter page and click Apply.


3. CIRCUITS and SHARED SERVER SESSIONS
The CIRCUITS parameter enables you to control user connections to the database through dispatchers and servers. These connections are known as virtual circuits.

The CIRCUITS parameter determines the total number of virtual circuits that are available for inbound and outbound network sessions.

To find the virtual circuits parameter in the Initialization Parameter page, you enter circuits into the Filter field and click Go.

The CIRCUITS parameter type is Integer, and its class is Dynamic.

Its default value is the same as that of SESSIONS if the Oracle Shared Server is configured.

If not, the default value is 0.

Now you want to change the total number of virtual circuits that are available for inbound and outbound network sessions.

You type 100 in the Value field and click Apply.

The CIRCUITS parameter is now set to 100.

This parameter is one of several that provide the total SGA requirements for an instance.

However, you set it only if you want to limit the total number of connections users may make while using the shared server architecture.

To control the total number of shared server sessions that can run concurrently, you set the SHARED_SERVER_SESSIONS parameter.

This parameter can also be used to reserve user sessions for dedicated servers.

To find it in the Intialization Parameters page, you enter shared_server_sessions in the Filter field and click Go.

You now want to change the total number of shared server sessions that can run concurrently to 100.

You type 100 in the Value field and click Apply.

The total number of shared server sessions that can open concurrently is now set at 100.

Question
You need to change one of the initialization parameters for the instance so as to control the user connections to the database through dispatchers and servers.

This task requires you to set the appropriate parameter so that a total of 80 connections only can be made. Which of these options allows you to complete this task?

Options:

1.You click All Initialization Parameters in the Instance section of the Administration tabbed page. You type circuits in the Filter field and click Go. You then type 80 into the Value field for the CIRCUITS parameter and click Apply.
2.You click All Initialization Parameters in the Security section of the Administration tabbed page. You type virtual_circuits in the Filter field and click Go. You then type 80 into the CIRCUITS field and click Apply.
Answer
You click All Initialization Parameters in the Instance section of the Administration tabbed page. You type circuits in the Filter field and click Go. You then type 80 into the Value field for the CIRCUITS parameter and click Apply.


Question

Match the initialization parameters used to configure Shared Server to their corresponding definitions.

Options:

1.CIRCUITS
2.MAX_SHARED_SERVERS
3.SHARED_SERVERS
4.SHARED_SERVER_SESSIONS
Targets:

1.The maximum number of server processes that can run simultaneously
2.The minimum number of server processes that can run concurrently
3.The total number of virtual circuits for inbound and outbound network sessions
4.The total number of shared server sessions that can run concurrently
Answer
MAX_SHARED_SERVERS sets the maximum number of server processes, and SHARED_SERVERS sets the minimum number of server processes. CIRCUITS sets the total number of virtual circuits for sessions, and SHARED_SERVER_SESSIONS sets the total number of shared server sessions that can run concurrently.

The CIRCUITS parameter controls the number of virtual circuits, which are the user connections made to the database through dispatchers and servers.

The MAX_SHARED_SERVERSparameter is needed to control the instance, which creates additional shared servers to service the needs of the Common Request Queue.

It is not essential to set SHARED_SERVERS , because the instance monitors the Common Request Queue and adds shared servers accordingly.

The SHARED_SERVER_SESSIONS parameter can also be used to reserve user sessions for dedicated servers.

Summary
In order to configure Oracle Shared Server you must update the initialization parameters for the instance.

However, only the DISPATCHERS parameter is required in most systems. This parameter is used to start one or more dispatchers for the network protocol. You can also set various attributes for each dispatcher using the name-value syntax. Only the protocol attribute, usually TCP, is required.

To control the minimum and maximum number of server processes that can run concurrently, you use the SHARED_SERVERS and MAX_SHARED_SERVERS parameters respectively. The SHARED_SERVERS parameter is less important, as the instance monitors the Common Request Queue so it knows when to add shared servers. However, the MAX_SHARED_SERVERS parameter is needed to limit how many shared servers are added.

You can also set the CIRCUITS parameter to control how many virtual circuits are available for inbound and outbound network sessions. To control the total number of shared server sessions that can run concurrently, you set the SHARED_SERVER_SESSIONS parameter.