Loader backlog (files) in OEM
Loader is a part of the Management Service that pushes metric data into the Management Repository at periodic intervals. when there is data pending load the Loader Backlog chart indicates that the backlog is high and Loader output is low, which may indicate a system bottleneck or the need for another Management Service. The chart shows the total backlog of files totaled over all Oracle Management Services for the past 24 hours. Click the image to display loader backlog charts for each individual Management Service over the past 24 hours.
Somtimes we face that /ora is 100% full and it becomes difficult to start the services using "opmnctl" and will throu errors like
ahc55(grid):/ora/product/oem/10203/oms10g/opmn/bin>opmnctl startall
opmnctl: starting opmn and all managed processes...
================================================================================
opmn id=ahc55:6200
5 of 6 processes started.
ias-instance id=EnterpriseManager0.ahc55
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
ias-component/process-type/process-set:
HTTP_Server/HTTP_Server/HTTP_Server
Error
--> Process (pid=28195)
failed to start a managed process after the maximum retry limit
Log:
/ora/product/oem/10203/oms10g/opmn/logs/HTTP_Server~1
In this case we need to clear the Sysman and Apache logs to make room for that or if this fails the we need to restart the reporsitory database.
we can use "():/ora/product/oem/10203/oms10g/opmn/bin>opmnctl status" command to check the status of this
[opmnctl is the supported tool for starting and stopping all components in an Oracle instance, with the exception of the Fusion Middleware Control Console. opmnctl provides a centralized way to control and monitor Oracle Application Server components from the command line]
opmnctl status
It generates the list of process running
ahc55():/ora/product/oem/10203/oms10g/opmn/bin>opmnctl status
Processes in Instance: EnterpriseManager0.ahc55
-------------------+--------------------+---------+---------
ias-component process-type pid status
-------------------+--------------------+---------+---------
DSA DSA N/A Down
HTTP_Server HTTP_Server 17007 Alive
LogLoader logloaderd N/A Down
dcm-daemon dcm-daemon N/A Down
OC4J home 17008 Alive
OC4J OC4J_EM 17010 Alive
WebCache WebCache 17011 Alive
WebCache WebCacheAdmin 17012 Alive
Solving ths issue
Normally we follow these steps to solve this issue..if every things fails we restart the repository database
Steps
Solution :
1) We need to clear the Apache/Sysman logs
2) Stop and Start the opmnctl
3) File upload should start
4) If that fails the step 5
5) If everything fails we need to start the database [pgrid]
1) Check the disk space
ahc55():/ora>df -k /ora
Filesystem kbytes used avail capacity Mounted on
/ora 18588650 18402767 0 100% /ora
2) Check the file upload status for backlog files at loader console or….
Or we can check this from prompt
ahc55():/ora/product/oem/10203/oms10g/sysman/recv>ls wc -l
93455
ahc55():/ora/product/oem/10203/oms10g/sysman/recv>ls wc -l
93487
ahc55():/ora/product/oem/10203/oms10g/sysman/recv>ls wc -l
93530
ahc55():/ora/product/oem/10203/oms10g/sysman/recv>ls wc -l
93566
If it is increasing the we need to follow the next steps
[this number should decrease instead of increase]
3) Stop and start the OMS
a) ahc55(grid):/ora/product/oem/10203/oms10g/opmn/bin>opmnctl stopall
b) ahc55(grid):/ora/product/oem/10203/oms10g/opmn/bin>opmnctl startall
4) Check if the file count is decreasing if not then follow next
Clean /ora/product/oem/10203/oms10g/Apache/Apache/logs
NOTE:- Except fastcgi and httpd.pid you can move all to /tmp
ahc55():/ora/product/oem/10203/oms10g/Apache/Apache/logs>ls -ltr
total 45502
drwx------ 3 oracle oinstall 512 Nov 27 15:35 fastcgi
-rw------- 1 oracle oinstall 1056768 Mar 22 11:35 mm.23113.mem
-rw------- 1 oracle oinstall 0 Mar 22 11:35 mm.23113.sem
-rw-r--r-- 1 oracle oinstall 0 Mar 22 11:35 ssl_request_log
-rw-r--r-- 1 oracle oinstall 6 Mar 22 11:35 httpd.pid
-rw------- 1 oracle oinstall 1056768 Mar 22 11:35 mod_oc4j.23113.shm.mem
-rw------- 1 oracle oinstall 0 Mar 22 11:35 mod_oc4j.23113.shm.sem
-rw------- 1 oracle oinstall 0 Mar 22 11:35 ssl_mutex.23113
-rw------- 1 oracle oinstall 0 Mar 22 11:35 ssl_scache.sem
-rw-r--r-- 1 oracle oinstall 257 Mar 22 11:35 ssl_engine_log
-rw------- 1 oracle oinstall 0 Mar 22 11:35 dms_metrics.23113.shm.sem
-rw------- 1 oracle oinstall 3072000 Mar 22 11:35 dms_metrics.23113.shm.mem
-rw------- 1 oracle oinstall 1572864 Mar 22 11:41 ssl_scache.mem
-rw-r--r-- 1 oracle oinstall 892604 Mar 22 12:01 error_log
-rw-r--r-- 1 oracle oinstall 11694 Mar 22 12:41 error_log.1269216000
-rw-r--r-- 1 oracle oinstall 454916 Mar 22 12:59 access_log.1269216000
-rw-r--r-- 1 oracle oinstall 5070917 Mar 22 19:06 access_log.1269259200
-rw-r--r-- 1 oracle oinstall 14718281 Mar 22 19:08 access_log
ahc55():/ora/product/oem/10203/oms10g/Apache/Apache/logs>
Next go to SYSMAN/log and clear the Logs except pafLogs (as we will be having space to restart the OMS)
ora/product/oem/10203/oms10g/sysman/log
ahc55():/ora/product/oem/10203/oms10g/sysman/log>ls -ltr
total 9794
drwxr-xr-x 2 oracle oinstall 512 Jul 30 2008 pafLogs
-rw-r--r-- 1 oracle oinstall 2498438 Mar 22 19:03 emoms.log
-rw-r--r-- 1 oracle oinstall 2498438 Mar 22 19:03 emoms.trc
ahc55():/ora/product/oem/10203/oms10g/sysman/log>
Check if uploading happening
ahc55(grid):/ora/product/oem/10203/oms10g/sysman/recv>ls wc -l
94045
ahc55(grid):/ora/product/oem/10203/oms10g/sysman/recv>ls wc -l
93902
Saturday, March 27, 2010
Thursday, March 25, 2010
INVALID PACKAGE AND COMPONENT IN DATABASE WHILE UPGRADING TO 10204 FROM 10203
While upgrading the server we faced this issue that one component and a package got invalid..
Database : AREL203
Server : tonot
Activity : Upgrade database from 10203 to 10204
Error : invalid object / component
COMP_NAME VERSION STATUS
---------------------------------------- ------------------------------ -----------
Oracle XML Database 10.2.0.4.0 VALID
Oracle Expression Filter 10.2.0.4.0 VALID
Oracle Workspace Manager 10.2.0.4.3 VALID
Oracle Database Catalog Views 10.2.0.4.0 VALID
Oracle Database Packages and Types 10.2.0.4.0 INVALID
JServer JAVA Virtual Machine 10.2.0.4.0 VALID
Oracle XDK 10.2.0.4.0 VALID
Oracle Database Java Packages 10.2.0.4.0 VALID
8 rows selected.
OWNER OBJECT_NAME STATUS
------ ---- ---------------------------------------- -------
SYS KUPW$WORKER INVALID
P1INTF INTF_DCRR_WAAI_S INVALID
P1INTF INTF_DCRR_SOIT_S INVALID
P1INTF INTF_DCRR_RELI_S INVALID
P1INTF INTF_DCRR_PPLD_S INVALID
P1INTF INTF_DCRR_PUOE_S INVALID
P1INTF INTF_DCRR_PPOL_S INVALID
P1INTF INTF_DCRR_PURO_S INVALID
P1INTF INTF_DCRR_ALTY_S INVALID
9 rows selected.
Solution:
There are two ways of doing this
1) Rebuild the pkg body
2) Chk the error and rectify that
Solution 1)
a) Run the script to rebuild the pkg body
@$ORACLE_HOME/rdbms/admin/catdpb.sql
After this chk the status
SQL> column comp_name format a40
select COMP_NAME,VERSION,STATUS from dba_registry;
column object_name format a40
column owner format a10
SQL>select owner,object_name,status from dba_objects where status='INVALID';
Me got the same status as above
Solution 2)
SQL> ALTER SESSION SET EVENTS'942 trace name errorstack level 3';
Session altered.
SQL> alter package KUPW$WORKER compile BODY;
Warning: Package Body altered with compilation errors.
SQL> show error
Errors for PACKAGE BODY KUPW$WORKER:
LINE/COL ERROR
-------- -----------------------------------------------------------------
15304/5 PL/SQL: SQL Statement ignored
15304/34 PL/SQL: ORA-00942: table or view does not exist
SQL>
SQL> show parameter dump
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
background_core_dump string partial
background_dump_dest string /ora/admin/arel203/bdump
core_dump_dest string /ora/admin/arel203/cdump
max_dump_file_size string UNLIMITED
shadow_core_dump string partial
user_dump_dest string /ora/admin/arel203/udump
SQL> !
tonto(arepl203):/var/opt/oracle>cd /ora/admin/arel203/udump
-rw-r----- 1 oracle oinstall 1019931 Mar 23 12:19 arepl203_ora_13845.trc
-rw-r----- 1 oracle oinstall 576 Mar 23 12:20 arepl203_ora_17182.trc
-rw-r----- 1 oracle oinstall 14843871 Mar 23 12:35 arepl203_ora_17230.trc
tonto(arepl203):/ora/admin/arepl203/udump>vi arepl203_ora_17230.trc
ksedmp: internal or fatal error
ORA-00942: table or view does not exist
No current SQL statement being executed.
SQL> CREATE GLOBAL TEMPORARY TABLE sys.ku$noexp_tab ON COMMIT PRESERVE ROWS AS SELECT * FROM sys.ku_noexp_view;
CREATE GLOBAL TEMPORARY TABLE sys.ku$noexp_tab ON COMMIT PRESERVE ROWS AS SELECT * FROM sys.ku_noexp_view
*
ERROR at line 1:
ORA-25153: Temporary Tablespace is Empty
SQL> alter tablespace temp add tempfile '/m003b/oradata/arel203/temp201.dbf' SIZE 200M;
Tablespace altered.
SQL> CREATE GLOBAL TEMPORARY TABLE sys.ku$noexp_tab ON COMMIT PRESERVE ROWS AS SELECT * FROM sys.ku_noexp_view;
Table created.
SQL> GRANT SELECT ON sys.ku$noexp_tab TO PUBLIC;
Grant succeeded.
SQL> GRANT INSERT ON sys.ku$noexp_tab TO PUBLIC;
Grant succeeded.
SQL> alter package KUPW$WORKER compile BODY;
Package body altered.
Check the INVALID
SQL> column object_name format a40
column owner format a10
select owner,object_name,status from dba_objects where status='INVALID';SQL> SQL>
OWNER OBJECT_NAME STATUS
---------- ---------------------------------------- -------
P1INTF INTF_DCRR_WAAI_S INVALID
P1INTF INTF_DCRR_SOIT_S INVALID
P1INTF INTF_DCRR_RELI_S INVALID
P1INTF INTF_DCRR_PPLD_S INVALID
P1INTF INTF_DCRR_PUOE_S INVALID
P1INTF INTF_DCRR_PPOL_S INVALID
P1INTF INTF_DCRR_PURO_S INVALID
P1INTF INTF_DCRR_ALTY_S INVALID
8 rows selected.
Same as before the start of activity…inform client and take approval to recompile.
COMP_NAME VERSION STATUS
---------------------------------------- ------------------------------ -----------
Oracle XML Database 10.2.0.4.0 VALID
Oracle Expression Filter 10.2.0.4.0 VALID
Oracle Workspace Manager 10.2.0.4.3 VALID
Oracle Database Catalog Views 10.2.0.4.0 VALID
Oracle Database Packages and Types 10.2.0.4.0 VALID
JServer JAVA Virtual Machine 10.2.0.4.0 VALID
Oracle XDK 10.2.0.4.0 VALID
Oracle Database Java Packages 10.2.0.4.0 VALID
8 rows selected.
Database : AREL203
Server : tonot
Activity : Upgrade database from 10203 to 10204
Error : invalid object / component
COMP_NAME VERSION STATUS
---------------------------------------- ------------------------------ -----------
Oracle XML Database 10.2.0.4.0 VALID
Oracle Expression Filter 10.2.0.4.0 VALID
Oracle Workspace Manager 10.2.0.4.3 VALID
Oracle Database Catalog Views 10.2.0.4.0 VALID
Oracle Database Packages and Types 10.2.0.4.0 INVALID
JServer JAVA Virtual Machine 10.2.0.4.0 VALID
Oracle XDK 10.2.0.4.0 VALID
Oracle Database Java Packages 10.2.0.4.0 VALID
8 rows selected.
OWNER OBJECT_NAME STATUS
------ ---- ---------------------------------------- -------
SYS KUPW$WORKER INVALID
P1INTF INTF_DCRR_WAAI_S INVALID
P1INTF INTF_DCRR_SOIT_S INVALID
P1INTF INTF_DCRR_RELI_S INVALID
P1INTF INTF_DCRR_PPLD_S INVALID
P1INTF INTF_DCRR_PUOE_S INVALID
P1INTF INTF_DCRR_PPOL_S INVALID
P1INTF INTF_DCRR_PURO_S INVALID
P1INTF INTF_DCRR_ALTY_S INVALID
9 rows selected.
Solution:
There are two ways of doing this
1) Rebuild the pkg body
2) Chk the error and rectify that
Solution 1)
a) Run the script to rebuild the pkg body
@$ORACLE_HOME/rdbms/admin/catdpb.sql
After this chk the status
SQL> column comp_name format a40
select COMP_NAME,VERSION,STATUS from dba_registry;
column object_name format a40
column owner format a10
SQL>select owner,object_name,status from dba_objects where status='INVALID';
Me got the same status as above
Solution 2)
SQL> ALTER SESSION SET EVENTS'942 trace name errorstack level 3';
Session altered.
SQL> alter package KUPW$WORKER compile BODY;
Warning: Package Body altered with compilation errors.
SQL> show error
Errors for PACKAGE BODY KUPW$WORKER:
LINE/COL ERROR
-------- -----------------------------------------------------------------
15304/5 PL/SQL: SQL Statement ignored
15304/34 PL/SQL: ORA-00942: table or view does not exist
SQL>
SQL> show parameter dump
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
background_core_dump string partial
background_dump_dest string /ora/admin/arel203/bdump
core_dump_dest string /ora/admin/arel203/cdump
max_dump_file_size string UNLIMITED
shadow_core_dump string partial
user_dump_dest string /ora/admin/arel203/udump
SQL> !
tonto(arepl203):/var/opt/oracle>cd /ora/admin/arel203/udump
-rw-r----- 1 oracle oinstall 1019931 Mar 23 12:19 arepl203_ora_13845.trc
-rw-r----- 1 oracle oinstall 576 Mar 23 12:20 arepl203_ora_17182.trc
-rw-r----- 1 oracle oinstall 14843871 Mar 23 12:35 arepl203_ora_17230.trc
tonto(arepl203):/ora/admin/arepl203/udump>vi arepl203_ora_17230.trc
ksedmp: internal or fatal error
ORA-00942: table or view does not exist
No current SQL statement being executed.
SQL> CREATE GLOBAL TEMPORARY TABLE sys.ku$noexp_tab ON COMMIT PRESERVE ROWS AS SELECT * FROM sys.ku_noexp_view;
CREATE GLOBAL TEMPORARY TABLE sys.ku$noexp_tab ON COMMIT PRESERVE ROWS AS SELECT * FROM sys.ku_noexp_view
*
ERROR at line 1:
ORA-25153: Temporary Tablespace is Empty
SQL> alter tablespace temp add tempfile '/m003b/oradata/arel203/temp201.dbf' SIZE 200M;
Tablespace altered.
SQL> CREATE GLOBAL TEMPORARY TABLE sys.ku$noexp_tab ON COMMIT PRESERVE ROWS AS SELECT * FROM sys.ku_noexp_view;
Table created.
SQL> GRANT SELECT ON sys.ku$noexp_tab TO PUBLIC;
Grant succeeded.
SQL> GRANT INSERT ON sys.ku$noexp_tab TO PUBLIC;
Grant succeeded.
SQL> alter package KUPW$WORKER compile BODY;
Package body altered.
Check the INVALID
SQL> column object_name format a40
column owner format a10
select owner,object_name,status from dba_objects where status='INVALID';SQL> SQL>
OWNER OBJECT_NAME STATUS
---------- ---------------------------------------- -------
P1INTF INTF_DCRR_WAAI_S INVALID
P1INTF INTF_DCRR_SOIT_S INVALID
P1INTF INTF_DCRR_RELI_S INVALID
P1INTF INTF_DCRR_PPLD_S INVALID
P1INTF INTF_DCRR_PUOE_S INVALID
P1INTF INTF_DCRR_PPOL_S INVALID
P1INTF INTF_DCRR_PURO_S INVALID
P1INTF INTF_DCRR_ALTY_S INVALID
8 rows selected.
Same as before the start of activity…inform client and take approval to recompile.
COMP_NAME VERSION STATUS
---------------------------------------- ------------------------------ -----------
Oracle XML Database 10.2.0.4.0 VALID
Oracle Expression Filter 10.2.0.4.0 VALID
Oracle Workspace Manager 10.2.0.4.3 VALID
Oracle Database Catalog Views 10.2.0.4.0 VALID
Oracle Database Packages and Types 10.2.0.4.0 VALID
JServer JAVA Virtual Machine 10.2.0.4.0 VALID
Oracle XDK 10.2.0.4.0 VALID
Oracle Database Java Packages 10.2.0.4.0 VALID
8 rows selected.
Monday, March 8, 2010
EXPORTING / IMPORTING OUTLINES IN ORACLE DATABASE...
As discussed in my privious write that we are getting the frequent requestes for setting outlines. somtimes we need to export the outlines from the different evnironment.
Recentrly we faced and issue where we had to export the ouline from the Acceptance to the production environment to put situation under control.
Actually during one of the database reboot due to some issue in database . we were unaware of the fact the there is a trigger which collects the fresh statistics the moment database get rebooted. so the moment it happend it started collecting the fresh stats and thing were going out of control and clients start shouting that Application going out of control..Temp utilization went up and then errors and ....;((
So to we just went to the acceptance and exported the outline and imported here in this production to make things better..for us and client ;) happy..
This is what we did to do so..
will write tmro...to late for the day...
Recentrly we faced and issue where we had to export the ouline from the Acceptance to the production environment to put situation under control.
Actually during one of the database reboot due to some issue in database . we were unaware of the fact the there is a trigger which collects the fresh statistics the moment database get rebooted. so the moment it happend it started collecting the fresh stats and thing were going out of control and clients start shouting that Application going out of control..Temp utilization went up and then errors and ....;((
So to we just went to the acceptance and exported the outline and imported here in this production to make things better..for us and client ;) happy..
This is what we did to do so..
will write tmro...to late for the day...
Sunday, March 7, 2010
SETTING OUTLINES FOR QUERY FOR GIVEN SQL_ID
Hi
As we all know that stored outline is a collection of hints associated with a specific SQL statement that allows a standard execution plan to be maintained, regardless of changes in the system environment or associated statistics.
Now a days we are getting frequent requests from the client that we need this plan for the query instead of the existing being used by the given sql_id. Client checks the performance of the query and somtimes finds that the other plan was better the the current one used by the query.generally the sql_id is provided by the client to make our job little bit easier and further he adds the PLAN_HASH_VALUE which is good and Bad
Once we get he request that we need to set different plan for the query we need to create the oulines for that sql to use that instead of jumping here and there.
As in this case clients identifies by using DB Control which plan_hash_value query is using right now. And which was better one used by the query.
Or else we need to dig further to chose that..
We follow this steps to identfy and fix that..the sql_id given here is '9t7tbvqnwux5p'
Step 1)
db33@prepl11 > select hash_value,CHILD_NUMBER, plan_hash_value from V$sql where sql_id = '9t7tbvqnwux5p';
HASH_VALUE CHILD_NUMBER PLAN_HASH_VALUE
---------- ------------ ---------------
2489344821 0 3607206622
2489344821 4 1085058146
2 rows selected.
Now as the client has already determined that the plan '3607206622' is not as efficient as plan '1085058146' is form his experience. it makes our job easier.
The next step is to create the oulines using the findings.
In our environment we have a default category defined as 'TUNED' which will be used by the query once oulines are created. we have to create the oulines and the switch them to the TUNED category so that query can use that outlines.
So the next step is to create the outlines
USER is "SYS"
sys@prepl11 > exec dbms_outln.create_outline(2489344821,4,'GOODSQLA');
sys@prepl11 > exec dbms_outln.create_outline(2489344821,0,'BADSQLA');
Now we can check there will be two oulines created one for bad and another for good. to compare using
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('', , 'OUTLINE');
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('', , 'ADVANCED');
db33@prepl101 > select NAME,CATEGORY,ENABLED,USED from dba_outlines where CATEGORY like '%SQLA';
NAME CATEGORY ENABLED USED
------------------------------ ------------------------------ -------- ------
SYS_OUTLINE_09120713441992221 GOODSQLA ENABLED UNUSED
SYS_OUTLINE_09120713444323823 BADSQLA ENABLED UNUSED
2 rows selected.
But when we do not have a time to compare..which can be done later if required jump to next step
Check how may oulines are already there in TUNED category
db33@prepl11 > select NAME,CATEGORY,ENABLED,USED from dba_outlines where CATEGORY like 'TUNED';
NAME CATEGORY ENABLED USED
------------------------------ --------------- --------------- -------- ------
SYS_OUTLINE_09110916403391908 TUNED ENABLED USED
SYS_OUTLINE_09110917012875212 TUNED ENABLED USED
2 rows selected.
So we have two oulines already stored and being used by some other sql_id. we need to move our to this one.
db33@prepl101 > exec outln_pkg.update_by_cat('GOODSQLA','TUNED');
PL/SQL procedure successfully completed.
db33@prepl101 > commit;
Commit complete.
Now we can check our is being used or not which we have just created
db33@prepl101 > select NAME,CATEGORY,ENABLED,USED from dba_outlines where CATEGORY like 'TUNED';
NAME CATEGORY ENABLED USED
------------------------------ ------------------------------ -------- ------
SYS_OUTLINE_09110916403391908 TUNED ENABLED USED
SYS_OUTLINE_09110917012875212 TUNED ENABLED USED
SYS_OUTLINE_09120713441992221 TUNED ENABLED USED
3 rows selected.
Get the confirmation from client :) if right plan is being used or you too can check from db control.
Vijay Kumar
As we all know that stored outline is a collection of hints associated with a specific SQL statement that allows a standard execution plan to be maintained, regardless of changes in the system environment or associated statistics.
Now a days we are getting frequent requests from the client that we need this plan for the query instead of the existing being used by the given sql_id. Client checks the performance of the query and somtimes finds that the other plan was better the the current one used by the query.generally the sql_id is provided by the client to make our job little bit easier and further he adds the PLAN_HASH_VALUE which is good and Bad
Once we get he request that we need to set different plan for the query we need to create the oulines for that sql to use that instead of jumping here and there.
As in this case clients identifies by using DB Control which plan_hash_value query is using right now. And which was better one used by the query.
Or else we need to dig further to chose that..
We follow this steps to identfy and fix that..the sql_id given here is '9t7tbvqnwux5p'
Step 1)
db33@prepl11 > select hash_value,CHILD_NUMBER, plan_hash_value from V$sql where sql_id = '9t7tbvqnwux5p';
HASH_VALUE CHILD_NUMBER PLAN_HASH_VALUE
---------- ------------ ---------------
2489344821 0 3607206622
2489344821 4 1085058146
2 rows selected.
Now as the client has already determined that the plan '3607206622' is not as efficient as plan '1085058146' is form his experience. it makes our job easier.
The next step is to create the oulines using the findings.
In our environment we have a default category defined as 'TUNED' which will be used by the query once oulines are created. we have to create the oulines and the switch them to the TUNED category so that query can use that outlines.
So the next step is to create the outlines
USER is "SYS"
sys@prepl11 > exec dbms_outln.create_outline(2489344821,4,'GOODSQLA');
sys@prepl11 > exec dbms_outln.create_outline(2489344821,0,'BADSQLA');
Now we can check there will be two oulines created one for bad and another for good. to compare using
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('
db33@prepl101 > select NAME,CATEGORY,ENABLED,USED from dba_outlines where CATEGORY like '%SQLA';
NAME CATEGORY ENABLED USED
------------------------------ ------------------------------ -------- ------
SYS_OUTLINE_09120713441992221 GOODSQLA ENABLED UNUSED
SYS_OUTLINE_09120713444323823 BADSQLA ENABLED UNUSED
2 rows selected.
But when we do not have a time to compare..which can be done later if required jump to next step
Check how may oulines are already there in TUNED category
db33@prepl11 > select NAME,CATEGORY,ENABLED,USED from dba_outlines where CATEGORY like 'TUNED';
NAME CATEGORY ENABLED USED
------------------------------ --------------- --------------- -------- ------
SYS_OUTLINE_09110916403391908 TUNED ENABLED USED
SYS_OUTLINE_09110917012875212 TUNED ENABLED USED
2 rows selected.
So we have two oulines already stored and being used by some other sql_id. we need to move our to this one.
db33@prepl101 > exec outln_pkg.update_by_cat('GOODSQLA','TUNED');
PL/SQL procedure successfully completed.
db33@prepl101 > commit;
Commit complete.
Now we can check our is being used or not which we have just created
db33@prepl101 > select NAME,CATEGORY,ENABLED,USED from dba_outlines where CATEGORY like 'TUNED';
NAME CATEGORY ENABLED USED
------------------------------ ------------------------------ -------- ------
SYS_OUTLINE_09110916403391908 TUNED ENABLED USED
SYS_OUTLINE_09110917012875212 TUNED ENABLED USED
SYS_OUTLINE_09120713441992221 TUNED ENABLED USED
3 rows selected.
Get the confirmation from client :) if right plan is being used or you too can check from db control.
Vijay Kumar
Subscribe to:
Posts (Atom)