Subject: What is ocssd.bin ? How To Stop/Kill This Process To Apply A Patch ?
Doc ID: Note:311647.1 Type: PROBLEM
Last Revision Date: 03-JUL-2006 Status: MODERATED
In this Document
Symptoms
Cause
Solution
References
--------------------------------------------------------------------------------
This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) Rapid Visibility (RaV) process, and therefore has not been subject to an independent technical review.
Applies to: Oracle Server - Enterprise Edition - Version: 10.1.0.0 to 10.1.0.4
This problem can occur on any platform.
SymptomsYou have installed Oracle Database 10g Products into the Oracle Home.Now you're trying to
apply the 10.1.0.4 patchset.You get an error box during the installation.
Oracle Universal Installer has detected that there are processes running in the currently selected Oracle Home.
The following processes need to be shutdown before continuing:
/lwoods_soft/10.1.0/bin/ocssd.bin
Help Retry Cancel
There is no db tied to the Oracle Home yet, but there is an ocssd.bin process
running.
CauseThe OCSSD.bin process is used for RAC and ASM. Even if you don't use the two
options the process is still ran.
Note 266079.1 OCSSD.BIN Process is Running in a Non-RAC Environment
Killing the process will not do it, because the the process is set to respawn,
when detected dead by initd.
SolutionYou can comment off the following line in /etc/inittab and reboot the server and you wont have the ocssd running.
After patching you can uncomment it and reboot the server.
The line is similar to : h1:35:respawn:/etc/init.d/init.cssd run >/dev/null 2>&1
In a non-RAC environment, the ocssd.bin process is used for communication between a database instance and the ASM (Automatic
Storage Management) instance. Even if you are not using ASM this process will be run out of the inittab:
stsun7% more /etc/inittab | grep cssd
h1:3:respawn:/etc/init.d/init.cssd run >/dev/null 2>&1
ReferencesNote 266079.1 - OCSSD.BIN Process is Running in a Non-RAC Environment
Tuesday, January 30, 2007
Sunday, January 28, 2007
SQL Parsing Flow Diagram
Subject: SQL Parsing Flow Diagram
Doc ID: Note:32895.1 Type: REFERENCE
Last Revision Date: 20-OCT-2005 Status: PUBLISHED
PURPOSE
~~~~~~~
The document gives a overview of Parsing.
SCOPE & APPLICATION
~~~~~~~~~~~~~~~~~~~
For users requiring a general overview of how parsing works.
Introduction
============
This article show the parsing mechanism as a flow diagram.
Its main purpose is to show the difference between a 'soft' and a
'hard' parse.
It is intended to give a feel of how parsing operates to make
explanation of parsing activity easier.
SQL Parsing Flow Diagram
========================
Statement
Submitted
|
Is it in an open cursor?--------------YES----V
| |
NO |
| |
Is SESSION_CACHED_CURSORS = Value |
and cursor in --------------YES----V In these 3 cases we
Session Cursor cache? | know that the cursor has
| | already been parsed, so
NO | re-parsing is
| | unnecessary.
Is HOLD_CURSOR=Y |
and cursor in --------------YES----V
Held cursor cache? |
| |
NO |
| | ^
OPEN A CURSOR | CLIENT SIDE |
| | -------------|
Statement is Hashed and compared | SERVER SIDE |
with the Hashed value in the sql area | V
| V
Is it in sql area? --YES-(Soft Parse)--> ---------
| | |
NO | EXECUTE |
| | |
PARSE STATEMENT ('Hard' Parse)---------> ---------
NOTES
=====
1. A cursor is an address on the client that points to the memory
location of a SQL statement on the server.
Multiple-client cursors may point at the same address on the server.
2. Remember that 'Client' and 'Server' sides may reside on the same
machine - in which case Client/Server is a logical distinction.
3. If a cursor is open, then the statement will be in the sql_area,
so no parsing is necessary.
This is why locks may remain when a client is terminated
abnormally (such as a PC Client being turned off without
closing open cursors).
4. SESSION_CACHED_CURSORS is the initialisation parameter that
specifies how many cursors to hold open for a particular session.
5. HOLD_CURSOR is an precompiler parameter that specifies that an
individual cursor should be held open.
See Page 11-3 of the Programmer's guide to the Oracle Precompilers.
6. Both the soft and hard parse register as a parse in tkprof. Hashing the
current statement updates the parse count.
7. Soft parse avoids many of the steps taken during the parse phase for a
particular statement. Initial syntactic and semantic checks are made and
then the statement is hashed and compared with hashed statements in
the SQL area. If a match is found, then existing information is used
and relatively expensive steps (such as query optimization etc.) are avoided.
8. The 10053 event is only invoked during a hard parse.
Additional Search Words
~~~~~~~~~~~~~~~~~~~~~~~
HARD; HOLD_CURSOR; PARSE; SESSION_CACHED_CURSORS; SOFT; SQL;
----------------------------------------------------------------
Doc ID: Note:32895.1 Type: REFERENCE
Last Revision Date: 20-OCT-2005 Status: PUBLISHED
PURPOSE
~~~~~~~
The document gives a overview of Parsing.
SCOPE & APPLICATION
~~~~~~~~~~~~~~~~~~~
For users requiring a general overview of how parsing works.
Introduction
============
This article show the parsing mechanism as a flow diagram.
Its main purpose is to show the difference between a 'soft' and a
'hard' parse.
It is intended to give a feel of how parsing operates to make
explanation of parsing activity easier.
SQL Parsing Flow Diagram
========================
Statement
Submitted
|
Is it in an open cursor?--------------YES----V
| |
NO |
| |
Is SESSION_CACHED_CURSORS = Value |
and cursor in --------------YES----V In these 3 cases we
Session Cursor cache? | know that the cursor has
| | already been parsed, so
NO | re-parsing is
| | unnecessary.
Is HOLD_CURSOR=Y |
and cursor in --------------YES----V
Held cursor cache? |
| |
NO |
| | ^
OPEN A CURSOR | CLIENT SIDE |
| | -------------|
Statement is Hashed and compared | SERVER SIDE |
with the Hashed value in the sql area | V
| V
Is it in sql area? --YES-(Soft Parse)--> ---------
| | |
NO | EXECUTE |
| | |
PARSE STATEMENT ('Hard' Parse)---------> ---------
NOTES
=====
1. A cursor is an address on the client that points to the memory
location of a SQL statement on the server.
Multiple-client cursors may point at the same address on the server.
2. Remember that 'Client' and 'Server' sides may reside on the same
machine - in which case Client/Server is a logical distinction.
3. If a cursor is open, then the statement will be in the sql_area,
so no parsing is necessary.
This is why locks may remain when a client is terminated
abnormally (such as a PC Client being turned off without
closing open cursors).
4. SESSION_CACHED_CURSORS is the initialisation parameter that
specifies how many cursors to hold open for a particular session.
5. HOLD_CURSOR is an precompiler parameter that specifies that an
individual cursor should be held open.
See Page 11-3 of the Programmer's guide to the Oracle Precompilers.
6. Both the soft and hard parse register as a parse in tkprof. Hashing the
current statement updates the parse count.
7. Soft parse avoids many of the steps taken during the parse phase for a
particular statement. Initial syntactic and semantic checks are made and
then the statement is hashed and compared with hashed statements in
the SQL area. If a match is found, then existing information is used
and relatively expensive steps (such as query optimization etc.) are avoided.
8. The 10053 event is only invoked during a hard parse.
Additional Search Words
~~~~~~~~~~~~~~~~~~~~~~~
HARD; HOLD_CURSOR; PARSE; SESSION_CACHED_CURSORS; SOFT; SQL;
----------------------------------------------------------------
Handling and resolving unshared cursors/large version_counts(MetaLink)
Doc ID: Note:296377.1 Type: TROUBLESHOOTING
Last Revision Date: 18-JAN-2007 Status: REVIEWED
Troubleshooting Guide to high version_counts
This document is intended to explain how SQL sharing works and give examples of diagnostics which can be used to help determine why SQL sharing may not occur
RDBMS
DBPERF
Version [All versions]
Author: James Cremonini
The information in this document has been reviewed and is current as of 01-JAN-2005.
--------------------------------------------------------------------------------
Instructions for the reader: The Troubleshooting Guide is provided to assist in debugging SQL sharing issues. When possible, diagnostic tools are included in the document to assist in troubleshooting problems. This document does not contain bugs/patches as these topics are addressed in the articles referenced at the bottom of this document.
--------------------------------------------------------------------------------
Troubleshooting Summary
Introduction: What is shared SQL ?
Diagnostics: How do I see the versions and why they are not shared ?
Documentation: What do the reasons given in v$SQL_SHARED_CURSOR mean?
Best Practises: How do I make SQL more shareable - are there any parameters which can help?
Q&A: Are there any times when a high version count is expected even though BINDS are being used?
Prob: What articles, white papers, or manuals should I read for more information on [subcomponent]?
--------------------------------------------------------------------------------
Troubleshooting Details
1. What is shared SQL ?
The first thing to remember is that all SQL is implicitly sharable. When a SQL statement is entered, the RDBMS will create a hash value for text of the statement and that hash value then helps the RDBMS to easily find SQL already in the shared pool. It is not in the scope of this article to discuss this in any great detail, so let's just assume entering a series of text results in a hash value being created
For instance :- 'select count(*) from emp' hashes to the value 4085390015
We now create a parent cursor for this sql and a single child. It does not matter that a SQL statement may never be shared - when it is first parsed a parent and a single child are created. The easy way to think of this is that the PARENT cursor is a representation of the hash value and the child cursor(s) represent the metadata for that SQL
What is 'SQL Metadata'?
Metadata is all the information which enables a statement to run. For instance, in the example I have given EMP is owned by scott and therefore has an OBJECT_ID which points to the EMP table owned by this user. When the user SCOTT logged in, optimizer parameters are initialised in that session for use by the statement, so this too is used by the optimizer and is therefore metadata. There are other examples of Metadata which will be mentioned further in this document.
Let's say this session logs out and back in again now. It then runs the same command again (as the same user). This time we already have the SQL in the shared pool (but we don't know this yet). What we do is hash the statement and then search for that hash value in the shared pool. If we find it, we can then search through the children to determine if any of them are usable by us (ie the metadata is the same). If it is, then we can share that SQL statement
I would still have one version of that SQL in the shared pool because the metadata enabled me to share the statement with the already existent child. The fundementals are that the parent is not shared, it is the children which determine shareability.
Now - another user 'TEST' has it's own version of EMP. If that user was to now run the select statement above then what would happen is :-
1. The statement is hashed - it is hashed to the value 4085390015
2. The SQL will be found in the shared pool as it already exists
3. The children are scanned (at this point we have one child)
4. Because the OBJECT_ID of the EMP table owned by TEST is different the OBJECT_ID owned by scott we have a 'mismatch'
(Essentially, what happens here is that we have a linked list of children which we traverse, comparing the metadata of the current SQL with that of all the children. If there were 100 children then we would scan each of them (looking for a possible mismatch and moving on) until we found one we could share. If we cannot share any (ie. have exhausted the list of children) then we need to create a new child)
5. We therefore have to create a new child - we now have 1 PARENT and 2 CHILDREN.
[top]2. How do I see the versions and why they are not shared ?
Lets use the example above and take a look at what SQL we can use to see this in the shared pool.
SCOTT runs select count(*) from emp
I can now run the following to see the PARENT statement and it's hash value and address
select sql_text, hash_value,address from v$sqlarea where sql_text like 'select count(*) from emp%';
SQL_TEXT HASH_VALUE ADDRESS
------------------------------------- ----------------
select count(*) from emp 4085390015 0000000386BC2E58
To see the CHILDREN (I expect to see 1 at this point) :-
9i - select * from v$sql_shared_cursor where kglhdpar = '0000000386BC2E58'
10G - select * from v$sql_shared_cursor where address = '0000000386BC2E58'
ADDRESS KGLHDPAR U S O O S L S E B P I S T A B D L T R I I R L I O S M U T N F
---------------- ---------------- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
0000000386BC2D08 0000000386BC2E58 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N
We can see we have a single child (ADDRESS 0000000386BC2D08). The mismatch information (USOOSL etc) is all N because this is the first child. Now, if I log in as another user and run the same select (select count(*) from emp) and look again I will get the following output:-
ADDRESS KGLHDPAR U S O O S L S E B P I S T A B D L T R I I R L I O S M U T N F
---------------- ---------------- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
0000000386BC2D08 0000000386BC2E58 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N
0000000386A91AA0 0000000386BC2E58 N N N N N N N N N N N N N Y N N N Y N N N N N N N N N N N N N
We can now see the 2nd child ( 0000000386A91AA0) and also the reasons why it could not be shared with the first (The 'Y's denote a mismatch) The reasons are (1) AUTH_CHECK_MISMATCH and (2) TRANSLATION_MISMATCH This is basically because the objects under my new user do not map to those of SCOTT (the current child). So , authentication fails because I cannot access SCOTTs objects and translation fails because we have different object_ids.
[top]
3. What do the reasons given in v$SQL_SHARED_CURSOR mean?
Below are the list of reasons given as well as some workable examples (Those denoted by ** are the ones most often seen) :-
UNBOUND_CURSOR - The existing child cursor was not fully built (in other words, it was not optimized)
SQL_TYPE_MISMATCH - The SQL type does not match the existing child cursor
**OPTIMIZER_MISMATCH - The optimizer environment does not match the existing child cursor
select count(*) from emp; -> 1 PARENT, 1 CHILD
alter session set optimizer_mode=ALL_ROWS
select count(*) from emp; -> 1 PARENT, 2 CHILDREN (The optimizer mode has changed and therefore the existing child cannot be reused)
(The same applies with events - if I turned on tracing with 10046 than I would get the OPTIMIZER_MISMATCH again and a 3rd child)
OUTLINE_MISMATCH - The outlines do not match the existing child cursor
If my user had created stored outlines previously for this command and they were stored in seperate categories (say "OUTLINES1" and "OUTLINES2") running:-
alter session set use_stored_outlines = OUTLINES1;
select count(*) from emp;
alter session set use_stored_oulines= OUTLINES2;
select count(*) from emp; --> Would create a 2nd child as the outline used is different than the first run.
STATS_ROW_MISMATCH - The existing statistics do not match the existing child cursor
Check that 10046/sql_trace is not set on all sessions as this can cause this.
LITERAL_MISMATCH - Non-data literal values do not match the existing child cursor
SEC_DEPTH_MISMATCH - Security level does not match the existing child cursor
EXPLAIN_PLAN_CURSOR - The child cursor is an explain plan cursor and should not be shared
Explain plan statements will generate a new child by default - the mismatch will be this.
BUFFERED_DML_MISMATCH - Buffered DML does not match the existing child cursor
PDML_ENV_MISMATCH - PDML environment does not match the existing child cursor
INST_DRTLD_MISMATCH - Insert direct load does not match the existing child cursor
SLAVE_QC_MISMATCH -The existing child cursor is a slave cursor and the new one was issued by the coordinator
(or, the existing child cursor was issued by the coordinator and the new one is a slave cursor).
TYPECHECK_MISMATCH - The existing child cursor is not fully optimized
AUTH_CHECK_MISMATCH - Authorization/translation check failed for the existing child cursor
**BIND_MISMATCH - The bind metadata does not match the existing child cursor
variable a varchar2(100);
select count(*) from emp where ename = :a -> 1 PARENT, 1 CHILD
variable a varchar2(400);
select count(*) from emp where ename = :a -> 1 PARENT, 2 CHILDREN (The bind 'a' has now changed in definition)
DESCRIBE_MISMATCH - The typecheck heap is not present during the describe for the child cursor
LANGUAGE_MISMATCH - The language handle does not match the existing child cursor
TRANSLATION_MISMATCH - The base objects of the existing child cursor do not match
ROW_LEVEL_SEC_MISMATCH - The row level security policies do not match
INSUFF_PRIVS - Insufficient privileges on objects referenced by the existing child cursor
INSUFF_PRIVS_REM - Insufficient privileges on remote objects referenced by the existing child cursor
REMOTE_TRANS_MISMATCH - The remote base objects of the existing child cursor do not match
USER1: select count(*) from table@remote_db
USER2: select count(*) from table@remote_db (Although the SQL is identical, the dblink pointed to by remote_db may be a private dblink which resolves
to a different object altogether)
LOGMINER_SESSION_MISMATCH
INCOMP_LTRL_MISMATCH
OVERLAP_TIME_MISMATCH - error_on_overlap_time mismatch
SQL_REDIRECT_MISMATCH - sql redirection mismatch
MV_QUERY_GEN_MISMATCH - materialized view query generation
USER_BIND_PEEK_MISMATCH - user bind peek mismatch
TYPCHK_DEP_MISMATCH - cursor has typecheck dependencies
NO_TRIGGER_MISMATCH - no trigger mismatch
FLASHBACK_CURSOR - No cursor sharing for flashback
ANYDATA_TRANSFORMATION - anydata transformation change
INCOMPLETE_CURSOR - incomplte cursor
TOP_LEVEL_RPI_CURSOR - top level/rpi cursor
DIFFERENT_LONG_LENGTH - different long length
LOGICAL_STANDBY_APPLY - logical standby apply mismatch
DIFF_CALL_DURN - different call duration
BIND_UACS_DIFF - bind uacs mismatch
PLSQL_CMP_SWITCHS_DIFF - plsql compiler switches mismatch
CURSOR_PARTS_MISMATCH - cursor-parts executed mismatch
STB_OBJECT_MISMATCH - STB object different (now exists)
ROW_SHIP_MISMATCH - row shipping capability mismatch
PQ_SLAVE_MISMATCH - PQ slave mismatch
Check you want to be using PX with this reason code, as the problem could be caused by running lots of small SQL statements which do not really need PX. If you are on < 11i you may be hitting BUG4367986
TOP_LEVEL_DDL_MISMATCH - top-level DDL cursor
MULTI_PX_MISMATCH - multi-px and slave-compiled cursor
BIND_PEEKED_PQ_MISMATCH - bind-peeked PQ cursor
MV_REWRITE_MISMATCH - MV rewrite cursor
ROLL_INVALID_MISMATCH - rolling invalidation window exceeded
I suspect this can occur when you see a mix of this reason and some other one in v$sql_shared_cursor, together with a library cache latch issue. My suspicion is this is as a result of that 'other' reason so you should address that first to relieve the latch.
OPTIMIZER_MODE_MISMATCH - optimizer mode mismatch
PX_MISMATCH - parallel query mismatch
MV_STALEOBJ_MISMATCH - mv stale object mismatch
FLASHBACK_TABLE_MISMATCH - flashback table mismatch
LITREP_COMP_MISMATCH - literal replacement compilation mismatch
[top]
4. What further tracing is available ?
Solution:
In 10G it is possible to use CURSORTRACE to aid the investigation of why cursors are not being shared. This event should only be used under the guidance of support and the resultant trace file is undocumented. To get the trace for a particular SQL statement you first of all need to get the hash_value (See the above select from v$sqlarea). You then set the trace on using:-
alter system set events
'immediate trace name cursortrace level 577, address hash_value';
This will write a trace file to user_dump_dest each time we try to reuse the cursor.
[top]
5. Are there any times when a high version count is expected even though BINDS are being used?
Solution:
Consider the following where cursor_sharing=SIMILAR
select /* TEST */ * from emp where sal > 100;
select /* TEST */ * from emp where sal > 101;
select /* TEST */ * from emp where sal > 102;
select /* TEST */ * from emp where sal > 103;
select /* TEST */ * from emp where sal > 104;
SELECT sql_text,version_count,address
FROM V$SQLAREA
WHERE sql_text like 'select /* TEST */%';
SELECT * FROM V$SQL_SHARED_CURSOR WHERE kglhdpar = '&my_addr';
You will see several versions , each with no obvious reason for not being shared
Explanation:
One of the cursor sharing criteria when literal replacement is enabled with cursor_sharing as similar is that bind value should match initial bind value
if the execution plan is going to change depending on the value of the literal. The reason for this is we _might_ get a sub optimal plan if we use the same
cursor. This would typically happen when depending on the value of the literal optimizer is going to chose a different plan. Thus in this test case we have a
predicate with > , if this was a equality we would always share the same child cursor. If application developers are ready to live with a sub-optimal plan and
save on memory , then they need to set the parameter to force.
"The difference between SIMILAR and FORCE is that SIMILAR forces similar statements to share the SQL area without deteriorating execution plans.
Setting CURSOR_SHARING to FORCE forces similar statements to share the SQL area potentially deteriorating execution plans."
Last Revision Date: 18-JAN-2007 Status: REVIEWED
Troubleshooting Guide to high version_counts
This document is intended to explain how SQL sharing works and give examples of diagnostics which can be used to help determine why SQL sharing may not occur
RDBMS
DBPERF
Version [All versions]
Author: James Cremonini
The information in this document has been reviewed and is current as of 01-JAN-2005.
--------------------------------------------------------------------------------
Instructions for the reader: The Troubleshooting Guide is provided to assist in debugging SQL sharing issues. When possible, diagnostic tools are included in the document to assist in troubleshooting problems. This document does not contain bugs/patches as these topics are addressed in the articles referenced at the bottom of this document.
--------------------------------------------------------------------------------
Troubleshooting Summary
Introduction: What is shared SQL ?
Diagnostics: How do I see the versions and why they are not shared ?
Documentation: What do the reasons given in v$SQL_SHARED_CURSOR mean?
Best Practises: How do I make SQL more shareable - are there any parameters which can help?
Q&A: Are there any times when a high version count is expected even though BINDS are being used?
Prob: What articles, white papers, or manuals should I read for more information on [subcomponent]?
--------------------------------------------------------------------------------
Troubleshooting Details
1. What is shared SQL ?
The first thing to remember is that all SQL is implicitly sharable. When a SQL statement is entered, the RDBMS will create a hash value for text of the statement and that hash value then helps the RDBMS to easily find SQL already in the shared pool. It is not in the scope of this article to discuss this in any great detail, so let's just assume entering a series of text results in a hash value being created
For instance :- 'select count(*) from emp' hashes to the value 4085390015
We now create a parent cursor for this sql and a single child. It does not matter that a SQL statement may never be shared - when it is first parsed a parent and a single child are created. The easy way to think of this is that the PARENT cursor is a representation of the hash value and the child cursor(s) represent the metadata for that SQL
What is 'SQL Metadata'?
Metadata is all the information which enables a statement to run. For instance, in the example I have given EMP is owned by scott and therefore has an OBJECT_ID which points to the EMP table owned by this user. When the user SCOTT logged in, optimizer parameters are initialised in that session for use by the statement, so this too is used by the optimizer and is therefore metadata. There are other examples of Metadata which will be mentioned further in this document.
Let's say this session logs out and back in again now. It then runs the same command again (as the same user). This time we already have the SQL in the shared pool (but we don't know this yet). What we do is hash the statement and then search for that hash value in the shared pool. If we find it, we can then search through the children to determine if any of them are usable by us (ie the metadata is the same). If it is, then we can share that SQL statement
I would still have one version of that SQL in the shared pool because the metadata enabled me to share the statement with the already existent child. The fundementals are that the parent is not shared, it is the children which determine shareability.
Now - another user 'TEST' has it's own version of EMP. If that user was to now run the select statement above then what would happen is :-
1. The statement is hashed - it is hashed to the value 4085390015
2. The SQL will be found in the shared pool as it already exists
3. The children are scanned (at this point we have one child)
4. Because the OBJECT_ID of the EMP table owned by TEST is different the OBJECT_ID owned by scott we have a 'mismatch'
(Essentially, what happens here is that we have a linked list of children which we traverse, comparing the metadata of the current SQL with that of all the children. If there were 100 children then we would scan each of them (looking for a possible mismatch and moving on) until we found one we could share. If we cannot share any (ie. have exhausted the list of children) then we need to create a new child)
5. We therefore have to create a new child - we now have 1 PARENT and 2 CHILDREN.
[top]2. How do I see the versions and why they are not shared ?
Lets use the example above and take a look at what SQL we can use to see this in the shared pool.
SCOTT runs select count(*) from emp
I can now run the following to see the PARENT statement and it's hash value and address
select sql_text, hash_value,address from v$sqlarea where sql_text like 'select count(*) from emp%';
SQL_TEXT HASH_VALUE ADDRESS
------------------------------------- ----------------
select count(*) from emp 4085390015 0000000386BC2E58
To see the CHILDREN (I expect to see 1 at this point) :-
9i - select * from v$sql_shared_cursor where kglhdpar = '0000000386BC2E58'
10G - select * from v$sql_shared_cursor where address = '0000000386BC2E58'
ADDRESS KGLHDPAR U S O O S L S E B P I S T A B D L T R I I R L I O S M U T N F
---------------- ---------------- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
0000000386BC2D08 0000000386BC2E58 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N
We can see we have a single child (ADDRESS 0000000386BC2D08). The mismatch information (USOOSL etc) is all N because this is the first child. Now, if I log in as another user and run the same select (select count(*) from emp) and look again I will get the following output:-
ADDRESS KGLHDPAR U S O O S L S E B P I S T A B D L T R I I R L I O S M U T N F
---------------- ---------------- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
0000000386BC2D08 0000000386BC2E58 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N
0000000386A91AA0 0000000386BC2E58 N N N N N N N N N N N N N Y N N N Y N N N N N N N N N N N N N
We can now see the 2nd child ( 0000000386A91AA0) and also the reasons why it could not be shared with the first (The 'Y's denote a mismatch) The reasons are (1) AUTH_CHECK_MISMATCH and (2) TRANSLATION_MISMATCH This is basically because the objects under my new user do not map to those of SCOTT (the current child). So , authentication fails because I cannot access SCOTTs objects and translation fails because we have different object_ids.
[top]
3. What do the reasons given in v$SQL_SHARED_CURSOR mean?
Below are the list of reasons given as well as some workable examples (Those denoted by ** are the ones most often seen) :-
UNBOUND_CURSOR - The existing child cursor was not fully built (in other words, it was not optimized)
SQL_TYPE_MISMATCH - The SQL type does not match the existing child cursor
**OPTIMIZER_MISMATCH - The optimizer environment does not match the existing child cursor
select count(*) from emp; -> 1 PARENT, 1 CHILD
alter session set optimizer_mode=ALL_ROWS
select count(*) from emp; -> 1 PARENT, 2 CHILDREN (The optimizer mode has changed and therefore the existing child cannot be reused)
(The same applies with events - if I turned on tracing with 10046 than I would get the OPTIMIZER_MISMATCH again and a 3rd child)
OUTLINE_MISMATCH - The outlines do not match the existing child cursor
If my user had created stored outlines previously for this command and they were stored in seperate categories (say "OUTLINES1" and "OUTLINES2") running:-
alter session set use_stored_outlines = OUTLINES1;
select count(*) from emp;
alter session set use_stored_oulines= OUTLINES2;
select count(*) from emp; --> Would create a 2nd child as the outline used is different than the first run.
STATS_ROW_MISMATCH - The existing statistics do not match the existing child cursor
Check that 10046/sql_trace is not set on all sessions as this can cause this.
LITERAL_MISMATCH - Non-data literal values do not match the existing child cursor
SEC_DEPTH_MISMATCH - Security level does not match the existing child cursor
EXPLAIN_PLAN_CURSOR - The child cursor is an explain plan cursor and should not be shared
Explain plan statements will generate a new child by default - the mismatch will be this.
BUFFERED_DML_MISMATCH - Buffered DML does not match the existing child cursor
PDML_ENV_MISMATCH - PDML environment does not match the existing child cursor
INST_DRTLD_MISMATCH - Insert direct load does not match the existing child cursor
SLAVE_QC_MISMATCH -The existing child cursor is a slave cursor and the new one was issued by the coordinator
(or, the existing child cursor was issued by the coordinator and the new one is a slave cursor).
TYPECHECK_MISMATCH - The existing child cursor is not fully optimized
AUTH_CHECK_MISMATCH - Authorization/translation check failed for the existing child cursor
**BIND_MISMATCH - The bind metadata does not match the existing child cursor
variable a varchar2(100);
select count(*) from emp where ename = :a -> 1 PARENT, 1 CHILD
variable a varchar2(400);
select count(*) from emp where ename = :a -> 1 PARENT, 2 CHILDREN (The bind 'a' has now changed in definition)
DESCRIBE_MISMATCH - The typecheck heap is not present during the describe for the child cursor
LANGUAGE_MISMATCH - The language handle does not match the existing child cursor
TRANSLATION_MISMATCH - The base objects of the existing child cursor do not match
ROW_LEVEL_SEC_MISMATCH - The row level security policies do not match
INSUFF_PRIVS - Insufficient privileges on objects referenced by the existing child cursor
INSUFF_PRIVS_REM - Insufficient privileges on remote objects referenced by the existing child cursor
REMOTE_TRANS_MISMATCH - The remote base objects of the existing child cursor do not match
USER1: select count(*) from table@remote_db
USER2: select count(*) from table@remote_db (Although the SQL is identical, the dblink pointed to by remote_db may be a private dblink which resolves
to a different object altogether)
LOGMINER_SESSION_MISMATCH
INCOMP_LTRL_MISMATCH
OVERLAP_TIME_MISMATCH - error_on_overlap_time mismatch
SQL_REDIRECT_MISMATCH - sql redirection mismatch
MV_QUERY_GEN_MISMATCH - materialized view query generation
USER_BIND_PEEK_MISMATCH - user bind peek mismatch
TYPCHK_DEP_MISMATCH - cursor has typecheck dependencies
NO_TRIGGER_MISMATCH - no trigger mismatch
FLASHBACK_CURSOR - No cursor sharing for flashback
ANYDATA_TRANSFORMATION - anydata transformation change
INCOMPLETE_CURSOR - incomplte cursor
TOP_LEVEL_RPI_CURSOR - top level/rpi cursor
DIFFERENT_LONG_LENGTH - different long length
LOGICAL_STANDBY_APPLY - logical standby apply mismatch
DIFF_CALL_DURN - different call duration
BIND_UACS_DIFF - bind uacs mismatch
PLSQL_CMP_SWITCHS_DIFF - plsql compiler switches mismatch
CURSOR_PARTS_MISMATCH - cursor-parts executed mismatch
STB_OBJECT_MISMATCH - STB object different (now exists)
ROW_SHIP_MISMATCH - row shipping capability mismatch
PQ_SLAVE_MISMATCH - PQ slave mismatch
Check you want to be using PX with this reason code, as the problem could be caused by running lots of small SQL statements which do not really need PX. If you are on < 11i you may be hitting BUG4367986
TOP_LEVEL_DDL_MISMATCH - top-level DDL cursor
MULTI_PX_MISMATCH - multi-px and slave-compiled cursor
BIND_PEEKED_PQ_MISMATCH - bind-peeked PQ cursor
MV_REWRITE_MISMATCH - MV rewrite cursor
ROLL_INVALID_MISMATCH - rolling invalidation window exceeded
I suspect this can occur when you see a mix of this reason and some other one in v$sql_shared_cursor, together with a library cache latch issue. My suspicion is this is as a result of that 'other' reason so you should address that first to relieve the latch.
OPTIMIZER_MODE_MISMATCH - optimizer mode mismatch
PX_MISMATCH - parallel query mismatch
MV_STALEOBJ_MISMATCH - mv stale object mismatch
FLASHBACK_TABLE_MISMATCH - flashback table mismatch
LITREP_COMP_MISMATCH - literal replacement compilation mismatch
[top]
4. What further tracing is available ?
Solution:
In 10G it is possible to use CURSORTRACE to aid the investigation of why cursors are not being shared. This event should only be used under the guidance of support and the resultant trace file is undocumented. To get the trace for a particular SQL statement you first of all need to get the hash_value (See the above select from v$sqlarea). You then set the trace on using:-
alter system set events
'immediate trace name cursortrace level 577, address hash_value';
This will write a trace file to user_dump_dest each time we try to reuse the cursor.
[top]
5. Are there any times when a high version count is expected even though BINDS are being used?
Solution:
Consider the following where cursor_sharing=SIMILAR
select /* TEST */ * from emp where sal > 100;
select /* TEST */ * from emp where sal > 101;
select /* TEST */ * from emp where sal > 102;
select /* TEST */ * from emp where sal > 103;
select /* TEST */ * from emp where sal > 104;
SELECT sql_text,version_count,address
FROM V$SQLAREA
WHERE sql_text like 'select /* TEST */%';
SELECT * FROM V$SQL_SHARED_CURSOR WHERE kglhdpar = '&my_addr';
You will see several versions , each with no obvious reason for not being shared
Explanation:
One of the cursor sharing criteria when literal replacement is enabled with cursor_sharing as similar is that bind value should match initial bind value
if the execution plan is going to change depending on the value of the literal. The reason for this is we _might_ get a sub optimal plan if we use the same
cursor. This would typically happen when depending on the value of the literal optimizer is going to chose a different plan. Thus in this test case we have a
predicate with > , if this was a equality we would always share the same child cursor. If application developers are ready to live with a sub-optimal plan and
save on memory , then they need to set the parameter to force.
"The difference between SIMILAR and FORCE is that SIMILAR forces similar statements to share the SQL area without deteriorating execution plans.
Setting CURSOR_SHARING to FORCE forces similar statements to share the SQL area potentially deteriorating execution plans."
Wednesday, January 24, 2007
Diagnosing and Resolving Error ORA-04031 (MetaLink)
Subject: Diagnosing and Resolving Error ORA-04031
Doc ID: Note:146599.1 Type: TROUBLESHOOTING
Last Revision Date: 08-DEC-2006 Status: PUBLISHED
Purpose:
The purpose of this document is to provide an easy to use, step by step guide to resolving ORA-04031 errors.
Contents:
1. Instance parameters related with the Shared Pool
2. Diagnosing ORA-04031 errors
3. Resolving error ORA-04031
Known Oracle BUGs
ORA-4031 when compiling Java code
Small shared pool size
Library Cache Hit Ratio
Shared Pool Size Calculation
Shared Pool Fragmentation
V$SQLAREA View
X$KSMLRU View
X$KSMSP View (Similar to Heapdump Information
4. ORA-04031 error and Large Pool
5. ORA-04031 and Flushing the SHARED POOL
6. Advanced analysis to ORA-04031 error
DIAGNOSING AND RESOLVING ORA-04031 ERROR
When any attempt to allocate a large piece of contiguous memory in the shared pool fails Oracle first flushes all objects that are not currently in use from the pool and the resulting free memory chunks are merged. If there is still not a single chunk large enough to satisfy the request the ORA-04031 error is returned. NOTE: These errors can occur on an ASM instance as well. The default shared_pool_size should be sufficient in most environments, but can be increased if you are experiencing ORA-04031 errors.
The message that you will get when this error appears is the following:
04031, 00000, "unable to allocate %s bytes of shared memory (\"%s\",\"%s\",\"%s\",\"%s\")"
// *Cause: More shared memory is needed than was allocated in the shared
// pool.
// *Action: If the shared pool is out of memory, either use the
// dbms_shared_pool package to pin large packages,
// reduce your use of shared memory, or increase the amount of
// available shared memory by increasing the value of the
// INIT.ORA parameters "shared_pool_reserved_size" and
// "shared_pool_size".
// If the large pool is out of memory, increase the INIT.ORA
// parameter "large_pool_size".
1. Instance parameters related with the Shared Pool
Before continuing, understanding the following instance parameters will be essential:
SHARED_POOL_SIZE - This parameter specifies the size of the shared pool in bytes and can accept
a numerical values or a number followed by the suffix "K" or "M" where "K" means "multiply by 1000"
and "M" means "multiply by 1000000"
SHARED_POOL_RESERVED_SIZE - It specifies the shared pool space which is reserved for large contiguous requests for shared pool memory. This parameter along with the SHARED_POOL_RESERVED_MIN_ALLOC parameter, can be used to avoid the occurrence of this error from situations where shared pool fragmentation forces Oracle to search for and free
chunks of unused pool to satisfy the current request.
Ideally, this parameter should be large enough to satisfy any request scanning for memory on the reserved list without flushing objects from the shared pool. Since the operating system memory may constraint the size of the shared pool, in general, you should set this parameter to 10% of the SHARED_POOL_SIZE parameter.
SHARED_POOL_RESERVED_MIN_ALLOC - The value of this parameter controls allocation of reserved memory. Memory allocation larger than this value can allocate space from the reserved list if a chunk of memory of sufficient size is not found on the shared pool free lists. The default value is adequate for most systems. If you increase the value, then the Oracle server will allow fewer allocations from the reserved list and will request more memory from the shared pool list. This parameter is hidden in Oracle 8i and higer, but it can be found by executing the following SQL statement:
select nam.ksppinm NAME,
val.KSPPSTVL VALUE
from x$ksppi nam,
x$ksppsv val
where nam.indx = val.indx
and nam.ksppinm like '%shared%'
order by 1;
NOTE: This parameter was obsoleted with 8i. The parameter can still be modified via the underscore parameter _SHARED_POOL_RESERVED_MIN_ALLOC.
10g Note: In Oracle 10g a new feature called "automatic memory management" allows the dba to reserve a pool of shared memory that is used to allocate the shared pool, the buffer cache, the java pool and the large pool.
In general, when the database needs to allocate a large object into the shared pool and cannot find contiguous space available, it will automatically increase the shared pool size using free space from other SGA structure.
Since the space allocation is automatically managed by Oracle, the probability of getting ora-4031 errors may be greatly reduced. Automatic Memory Management is enabled when the parameter SGA_TARGET is greater than zero and the current setting can be obtained quering the v$sga_dynamic_components view.
Please refer to the 10g Administration Manual for further reference.
2. Diagnosing error ORA-04031:
Note: Most common ORA-4031 occurrences are related to the SHARED POOL SIZE, therefore the diagnostic steps provided in this article will mostly address issues related to the shared pool. For other areas like large_pool or java_pool where the memory allocation algorith is simpler, normally the error is caused by an undersized structure.
ORA-04031 error can be due to either an inadequeate sizing of the SHARED POOL size or due to heavy fragmentation leading the database to not finding large enough chuncks of memory. NOTE: There have been many reports where the default size for shared_pool_size on an ASM instance is too small. If you experience ORA-04031 error on your ASM instance, increase the shared_pool_size parameter to 50M. If the problem persists, then increase the parameter again in increments of 10M until you stop seeing the error.
Inadequate Sizing: The first thing is determining if the ORA-04031 error is a result of lack of contiguous space in the library cache by verifying the following from V$SHARED_POOL_RESERVED:
REQUEST_FAILURES is > 0 and LAST_FAILURE_SIZE is <
SHARED_POOL_RESERVED_MIN_ALLOC
or
REQUEST_FAILURES is 0 and LAST_FAILURE_SIZE is < SHARED_POOL_RESERVED_MIN_ALLOC
If this is the case, consider lowering SHARED_POOL_RESERVED_MIN_ALLOC to
allow the database putting more objects into the shared pool reserved space and then increase the
SHARED_POOL_SIZE if the problem is not resolved.
NOTE: A bug was discoverd where LAST_FAILURE_SIZE can be wrong in cases where multiple pools are used. The value in LAST_FAILURE_SIZE can be a sum of failure sizes across all pools. This is fixed as of 9.2.0.7, 10.1.0.4, and 10.2.x.
Fragmentation: If this is not the case, then you must determine if the ORA-04031 was a result of fragmentation in the library cache or in the shared pool reserved space by following this rule:
REQUEST_FAILURES is > 0 and LAST_FAILURE_SIZE is >
SHARED_POOL_RESERVED_MIN_ALLOC.
To resolve this consider increasing SHARED_POOL_RESERVED_MIN_ALLOC to lower
the number of objects being cached into the shared pool reserved space and
increase SHARED_POOL_RESERVED_SIZE and SHARED_POOL_SIZE to increase the
available memory in the shared pool reserved space.
Another consideration: - Pre-9i, changing OPTIMIZER_MAX_PERMUTATIONS to 2000 can reduce shared pool space pressure
3. Resolving error ORA-04031:
Oracle BUGs
Oracle recommends to apply the latest patchser available for your platform. Most of the ORA-4031 errors related to BUGs can be avoided by applying these patchsets. The following table summarize the most common BUGs related with this error, possible workaround and the patchset that fixes the problem.
BUG Description Workaround Fixed
Bug 1397603 ORA-4031 / SGA leak of PERMANENT memory occurs for buffer handles. _db_handles_cached = 0 8172, 901
Bug 1640583 ORA-4031 due to leak / cache buffer chain contention from AND-EQUAL access Not available 8171, 901
Bug:1318267
Not Public INSERT AS SELECT statements may not be shared when they should be if TIMED_STATISTICS. It can lead to ORA-4031 _SQLEXEC_PROGRESSION_COST=0 8171, 8200
Bug:1193003
Not Public Cursors may not be shared in 8.1 when they should be Not available 8162, 8170, 901
Bug 2104071 ORA-4031/excessive "miscellaneous"
shared pool usage possible.
(many PINS) None-> This is known to affect the XML parser. 8174, 9013, 9201
Bug 3910149 KGLHDDEP PROBLEM IN RAC
Slow SGA memory leak in internal permanent space (KGL handle). Backports are available on various platforms and release levels Restart problem node at intervals
(flushing shared pool doesn't
clear permanent structures. 9207, 10105, 10201
Note 263791.1 Several number of BUGs related
to ORA-4031 errors were fixed
in the 9.2.0.5 patchset N/A 9205
ORA-4031 when compiling Java code:
If you run out of memory while compiling a java code (within loadjava or deployejb), you should see an error:
A SQL exception occurred while compiling: : ORA-04031: unable to allocate bytes of shared memory ("shared pool","unknown object","joxlod: init h", "JOX: ioc_allocate_pal")
The solution is to shut down the database and set JAVA_POOL_SIZE to a larger value. The mention of "shared pool" in the error message is a misleading reference to running out of memory in the "Shared Global Area". It does not mean you should increase your SHARED_POOL_SIZE. Instead, you must increase your JAVA_POOL_SIZE, restart your server, and try again.
See Bug 2736601
Small shared pool size
In many cases, a small shared pool can be the cause of the ORA-04031 error.
The following information will help you to adjust the size of the shared pool:
Library Cache Hit Ratio
The hit ratio helps to measure the usage of the shared pool based on how many times a SQL/PLSQL statement needed to be parsed instead of being reused. The following SQL statement help you to calculate the library cache hit ratio:
SELECT SUM(PINS) "EXECUTIONS",
SUM(RELOADS) "CACHE MISSES WHILE EXECUTING"
FROM V$LIBRARYCACHE;
If the ratio of misses to executions is more than 1%, then try to reduce the library cache misses by increasing the shared pool size.
Shared Pool Size Calculation
To calculate the size of the shared pool that best fits to your current workload, please refer to:
Note 1012046.6: HOW TO CALCULATE YOUR SHARED POOL SIZE.
Shared Pool Fragmentation:
Every time a SQL or PL/SQL statement needs to be executed the parse representation is loaded in the library cache requiring a specific amount of free contiguous space. The first resource where the database scans is the free memory available in the shared pool. Once the free memory is exhausted, the database looks for reusing an already allocated piece not in use. If a chunk with the exact size is not available, the scan continues looking for space based on the following criteria:
- The chunk size is larger than the required size
- The space is contiguous
- The chunk is available (not in use)
Then that chunk is split and the remaining free space is added to the appropriate free space list. When the database is operating in this way for a certain period of time the shared pool structure will be fragmented.
When the shared pool is suffering fragmentation ORA-04031 errors (when the database cannot find a contiguous piece of free memory) may occur. Also as a concequence , the allocation of a piece of free space takes more time an the performance may be affected (the "chunk allocation" is protected by a single latch called "shared pool latch" which is held during the whole operation). However, ORA-4031 errors don't always affect the performance of the database.
If the SHARED_POOL_SIZE is large enough, most ORA-04031 errors are a result of dynamic sql fragmenting the shared pool. This can be caused by:
o Not sharing SQL
o Making unnecessary parse calls (soft)
o Not using bind variables
To reduce fragmentation you will need to address one or more of the causes described before. In general to reduce fragmentation you must analyze how the application is using the shared pool and maximize the use of sharable cursors.
Please refer to Note 62143.1, which describes these options in greater detail. This note contains as well further detail on how the shared pool works.
The following views will help you to identify non-sharable versions of SQL/PLSQL text in the shared pool:
V$SQLAREA View
This view keeps information of every SQL statement and PL/SQL block executed in the database. The following SQL can show you statements with literal values or candidates to include bind variables:
SELECT substr(sql_text,1,40) "SQL",
count(*) ,
sum(executions) "TotExecs"
FROM v$sqlarea
WHERE executions < 5
GROUP BY substr(sql_text,1,40)
HAVING count(*) > 30
ORDER BY 2;
Note: The number "30" in the having section of the statement can be adjusted as needed to get more detailed information.
X$KSMLRU View
There is a fixed table called x$ksmlru that tracks allocations in the shared pool that cause other objects in the shared pool to be aged out. This fixed table can be used to identify what is causing the large allocation.
If many objects are being periodically flushed from the shared pool then this will cause response time problems and will likely cause library cache latch contention problems when the objects are reloaded into the shared pool.
One unusual thing about the x$ksmlru fixed table is that the contents of the fixed table are erased whenever someone selects from the fixed table. This is done since the fixed table stores only the largest allocations that have occurred. The values are reset after being selected so that subsequent large allocations can be noted even if they were not quite as large as others that occurred previously. Because of this resetting, the output of selecting from this table should be carefully kept since it cannot be retrieved back after the query is issued.
To monitor this fixed table just run the following:
SELECT * FROM X$KSMLRU WHERE ksmlrsiz > 0;
This view can only be queried by connected as the SYS.
X$KSMSP View (Similar to Heapdump Information)
Using this view you will be able to find out how the free space is currently allocated, which will be helpful to undrestand the level of fragmentation of the shared pool. As it was described before, the first place to find a chunck big enough for the cursor allocation is the free list. The following SQL shows the chunks available in the free list:
select '0 (<140)' BUCKET, KSMCHCLS, KSMCHIDX, 10*trunc(KSMCHSIZ/10) "From",
count(*) "Count" , max(KSMCHSIZ) "Biggest",
trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total"
from x$ksmsp
where KSMCHSIZ<140
and KSMCHCLS='free'
group by KSMCHCLS, KSMCHIDX, 10*trunc(KSMCHSIZ/10)
UNION ALL
select '1 (140-267)' BUCKET, KSMCHCLS, KSMCHIDX,20*trunc(KSMCHSIZ/20) ,
count(*) , max(KSMCHSIZ) ,
trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total"
from x$ksmsp
where KSMCHSIZ between 140 and 267
and KSMCHCLS='free'
group by KSMCHCLS, KSMCHIDX, 20*trunc(KSMCHSIZ/20)
UNION ALL
select '2 (268-523)' BUCKET, KSMCHCLS, KSMCHIDX, 50*trunc(KSMCHSIZ/50) ,
count(*) , max(KSMCHSIZ) ,
trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total"
from x$ksmsp
where KSMCHSIZ between 268 and 523
and KSMCHCLS='free'
group by KSMCHCLS, KSMCHIDX, 50*trunc(KSMCHSIZ/50)
UNION ALL
select '3-5 (524-4107)' BUCKET, KSMCHCLS, KSMCHIDX, 500*trunc(KSMCHSIZ/500) ,
count(*) , max(KSMCHSIZ) ,
trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total"
from x$ksmsp
where KSMCHSIZ between 524 and 4107
and KSMCHCLS='free'
group by KSMCHCLS, KSMCHIDX, 500*trunc(KSMCHSIZ/500)
UNION ALL
select '6+ (4108+)' BUCKET, KSMCHCLS, KSMCHIDX, 1000*trunc(KSMCHSIZ/1000) ,
count(*) , max(KSMCHSIZ) ,
trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total"
from x$ksmsp
where KSMCHSIZ >= 4108
and KSMCHCLS='free'
group by KSMCHCLS, KSMCHIDX, 1000*trunc(KSMCHSIZ/1000);
Note: The information available in this view is the same that is generated as part of a HEAPDUMP level 2.
Also be aware that running this query too often is likely to cause other memory issues in the shared pool.
There is also a port specific bug filed on HP and 10g where running queries on x$ksmsp will hang the database.
If the result of the above query shows that must of the space available is on the top part of the list (meaning available only in very small chuncks). It is very likely that the error is due to a heavy fragmentation.
You can also use this view as follows to review overall memory usage in the SGA
SQL> SELECT KSMCHCLS CLASS, COUNT(KSMCHCLS) NUM, SUM(KSMCHSIZ) SIZ,
To_char( ((SUM(KSMCHSIZ)/COUNT(KSMCHCLS)/1024)),'999,999.00')||'k' "AVG SIZE"
FROM X$KSMSP GROUP BY KSMCHCLS;
CLASS NUM SIZ AVG SIZE
-------- ---------- ---------- ------------
R-free 12 8059200 655.86k <= Reserved List
R-freea 24 960 .04k <= Reserved List
free 331 151736448 447.67k <= Free Memory
freeabl 4768 7514504 1.54k <= Memory for user / system processing
perm 2 30765848 15,022.39k <= Memory allocated to the system
recr 3577 3248864 .89k <= Memory for user / system processing
a) if free memory (SIZ) is low (less than 5mb or so) you may need to increase the shared_pool_size and shared_pool_reserved_size.
b) if perm continually grows then it is possible you are seeing system memory leak.
c) if freeabl and recr are always huge, this indicates that you have lots of cursor info stored that is not releasing.
d) if free is huge but you are still getting 4031 errors, (you can correlate that with the reloads and invalids causing fragmentation)
4. ORA-04031 error and Large Pool
The Large pool is an optional memory area that can be configured to provide large memory allocations for one of the following operations :
session memory for the multi-threaded server and the Oracle XA interface.
The memory ( Buffers ) for Oracle backup and restore operations and for I/O server processes.
Parallel Execution messaging buffers.
The Large pool does not have a LRU list. It is different from reserved space in the shared pool, which uses the same LRU list as other memory allocated from the shared pool.
Chunks of memory are never aged out of the large pool,memory has to be explicitly allocated and freed by each session.
If there is no free memory left when a request is made then an ORA-4031 will be signalled similar to this :
ORA-04031: unable to allocate XXXX bytes of shared memory
("large pool","unknown object","session heap","frame")
Few things can be checked when this error occurs:
1- Check V$SGASTAT and see how much memory is used and free using the following SQL statement:
SELECT pool,name,bytes FROM v$sgastat where pool = 'large pool';
2- You can also take a heapdump level 32 to dump the large pool heap and check free chunks sizes.
Memory is allocated from the large pool in chunks of LARGE_POOL_MIN_ALLOC bytes to help avoid fragmentation. Any request to allocate a chunk size less LARGE_POOL_MIN_ALLOC will be allocated with size of LARGE_POOL_MIN_ALLOC. In general you may see more memory usage when using Large Pool compared to Shared Pool.
Usually to resolve an ORA-4031 in the large pool the LARGE_POOL_SIZE size must be increased.
5. ORA-04031 and SHARED POOL FLUSHING
There are several technics to increase cursor sharability so that shared pool fragmentation is reduce as well as likeability of ORA-4031 errors. The best way is by modifying the application to use bind variables. Another workaround when the application cannot be modified is using CURSOR_SHARING to a value different of EXACT (Be aware that this may cause changes in execution plan, so it is advisable to test the application first). When none of the above techniques can be used and fragmentation is considearble heavy in the system, flushing the shared pool might help alliviating the fragmentation. However some considerations must be taken into account:
Flushing the shared pool will cause that all the cursor that are not in use are removed from the library cache. Therefore just after the shared pool flusing is issued, most of the SQL and PL/SQL cursors will have to be hard parsed. This will increase the CPU usage of the system and will also increase the latch activity.
When applications don't use bind variables and have heavy possibilities of many users doing frequen similar operations (like in OLTP systems) it is common that soon after the flush is issued the fragmentation is back in place. So be advice that flushing the shared pool is not always the solution for a bad application.
For large shared pool flushing the shared pool may cause a halt of the system, specially when the instance is very active. It is recommended to flush the shared pool during off-peak hours.
6. Advanced analysis to ORA-04031error
If none of the techniques provided cannot resolve the occurence of ORA-04031 errors, additional tracing may be needed to get a snapshot of the shared pool when the problem is in place.
Modify the init.ora paramater to add the following events to get a trace file with additional information about the problem:
event = "4031 trace name errorstack level 3"
event = "4031 trace name HEAPDUMP level 3"
Note: This parameter will take not effect unless the instance is bounced.
Starting with 9.2.0.5, instead of requesting heapdump level 1,2 , 3 or 32 you can use level those same levels plus (536870912).
This will generate the 5 largest subheaps AND the 5 largest heap areas within each of those.
If the problem is reproducible, the event can be set at session level using the following statement before the execution of the faulty SQL statement:
SQL> alter session set events '4031 trace name errorstack level 3';
SQL> alter session set events '4031 trace name HEAPDUMP level 536870915';
This trace file should be sent to Oracle Support for troubleshooting.
Important Note: In Oracle 9.2.0.5 and Oracle 10g releases a trace file is generated BY DEFAULT every time an ORA-4031 error occurs, and can be located in the user_dump_dest directory. If your database version is one of these, you don't need to follow the steps described before to generate additional tracing.
RELATED DOCUMENTS
Note 396940.1 FAQ: ORA-4031
Note 1012046.6 How to Calculate Your Shared Pool Size
Note 62143.1 Understanding and Tuning the Shared Pool
Note 61623.1 Resolving Shared Pool Fragmentation In Oracle7
Note 1012049.6 Tuning Library Cache Latch Contention
Note 105813.1 SCRIPT TO SUGGEST MINIMUM SHARED POOL SIZE
Note 316138.1 ORA-4031 / Continuos Growth of 'miscellaneous' in v$sgastat when STATISTICS_LEVEL is set to TYPICAL or ALL
Note 367392.1 ORA-4031 with calls to ksfd_alloc_sgabuffer, ksfd_alloc_contig_buffer, ksfd_get_contig_buffer
Keywords
SHARED~POOL LIBRARY~CACHE
Doc ID: Note:146599.1 Type: TROUBLESHOOTING
Last Revision Date: 08-DEC-2006 Status: PUBLISHED
Purpose:
The purpose of this document is to provide an easy to use, step by step guide to resolving ORA-04031 errors.
Contents:
1. Instance parameters related with the Shared Pool
2. Diagnosing ORA-04031 errors
3. Resolving error ORA-04031
Known Oracle BUGs
ORA-4031 when compiling Java code
Small shared pool size
Library Cache Hit Ratio
Shared Pool Size Calculation
Shared Pool Fragmentation
V$SQLAREA View
X$KSMLRU View
X$KSMSP View (Similar to Heapdump Information
4. ORA-04031 error and Large Pool
5. ORA-04031 and Flushing the SHARED POOL
6. Advanced analysis to ORA-04031 error
DIAGNOSING AND RESOLVING ORA-04031 ERROR
When any attempt to allocate a large piece of contiguous memory in the shared pool fails Oracle first flushes all objects that are not currently in use from the pool and the resulting free memory chunks are merged. If there is still not a single chunk large enough to satisfy the request the ORA-04031 error is returned. NOTE: These errors can occur on an ASM instance as well. The default shared_pool_size should be sufficient in most environments, but can be increased if you are experiencing ORA-04031 errors.
The message that you will get when this error appears is the following:
04031, 00000, "unable to allocate %s bytes of shared memory (\"%s\",\"%s\",\"%s\",\"%s\")"
// *Cause: More shared memory is needed than was allocated in the shared
// pool.
// *Action: If the shared pool is out of memory, either use the
// dbms_shared_pool package to pin large packages,
// reduce your use of shared memory, or increase the amount of
// available shared memory by increasing the value of the
// INIT.ORA parameters "shared_pool_reserved_size" and
// "shared_pool_size".
// If the large pool is out of memory, increase the INIT.ORA
// parameter "large_pool_size".
1. Instance parameters related with the Shared Pool
Before continuing, understanding the following instance parameters will be essential:
SHARED_POOL_SIZE - This parameter specifies the size of the shared pool in bytes and can accept
a numerical values or a number followed by the suffix "K" or "M" where "K" means "multiply by 1000"
and "M" means "multiply by 1000000"
SHARED_POOL_RESERVED_SIZE - It specifies the shared pool space which is reserved for large contiguous requests for shared pool memory. This parameter along with the SHARED_POOL_RESERVED_MIN_ALLOC parameter, can be used to avoid the occurrence of this error from situations where shared pool fragmentation forces Oracle to search for and free
chunks of unused pool to satisfy the current request.
Ideally, this parameter should be large enough to satisfy any request scanning for memory on the reserved list without flushing objects from the shared pool. Since the operating system memory may constraint the size of the shared pool, in general, you should set this parameter to 10% of the SHARED_POOL_SIZE parameter.
SHARED_POOL_RESERVED_MIN_ALLOC - The value of this parameter controls allocation of reserved memory. Memory allocation larger than this value can allocate space from the reserved list if a chunk of memory of sufficient size is not found on the shared pool free lists. The default value is adequate for most systems. If you increase the value, then the Oracle server will allow fewer allocations from the reserved list and will request more memory from the shared pool list. This parameter is hidden in Oracle 8i and higer, but it can be found by executing the following SQL statement:
select nam.ksppinm NAME,
val.KSPPSTVL VALUE
from x$ksppi nam,
x$ksppsv val
where nam.indx = val.indx
and nam.ksppinm like '%shared%'
order by 1;
NOTE: This parameter was obsoleted with 8i. The parameter can still be modified via the underscore parameter _SHARED_POOL_RESERVED_MIN_ALLOC.
10g Note: In Oracle 10g a new feature called "automatic memory management" allows the dba to reserve a pool of shared memory that is used to allocate the shared pool, the buffer cache, the java pool and the large pool.
In general, when the database needs to allocate a large object into the shared pool and cannot find contiguous space available, it will automatically increase the shared pool size using free space from other SGA structure.
Since the space allocation is automatically managed by Oracle, the probability of getting ora-4031 errors may be greatly reduced. Automatic Memory Management is enabled when the parameter SGA_TARGET is greater than zero and the current setting can be obtained quering the v$sga_dynamic_components view.
Please refer to the 10g Administration Manual for further reference.
2. Diagnosing error ORA-04031:
Note: Most common ORA-4031 occurrences are related to the SHARED POOL SIZE, therefore the diagnostic steps provided in this article will mostly address issues related to the shared pool. For other areas like large_pool or java_pool where the memory allocation algorith is simpler, normally the error is caused by an undersized structure.
ORA-04031 error can be due to either an inadequeate sizing of the SHARED POOL size or due to heavy fragmentation leading the database to not finding large enough chuncks of memory. NOTE: There have been many reports where the default size for shared_pool_size on an ASM instance is too small. If you experience ORA-04031 error on your ASM instance, increase the shared_pool_size parameter to 50M. If the problem persists, then increase the parameter again in increments of 10M until you stop seeing the error.
Inadequate Sizing: The first thing is determining if the ORA-04031 error is a result of lack of contiguous space in the library cache by verifying the following from V$SHARED_POOL_RESERVED:
REQUEST_FAILURES is > 0 and LAST_FAILURE_SIZE is <
SHARED_POOL_RESERVED_MIN_ALLOC
or
REQUEST_FAILURES is 0 and LAST_FAILURE_SIZE is < SHARED_POOL_RESERVED_MIN_ALLOC
If this is the case, consider lowering SHARED_POOL_RESERVED_MIN_ALLOC to
allow the database putting more objects into the shared pool reserved space and then increase the
SHARED_POOL_SIZE if the problem is not resolved.
NOTE: A bug was discoverd where LAST_FAILURE_SIZE can be wrong in cases where multiple pools are used. The value in LAST_FAILURE_SIZE can be a sum of failure sizes across all pools. This is fixed as of 9.2.0.7, 10.1.0.4, and 10.2.x.
Fragmentation: If this is not the case, then you must determine if the ORA-04031 was a result of fragmentation in the library cache or in the shared pool reserved space by following this rule:
REQUEST_FAILURES is > 0 and LAST_FAILURE_SIZE is >
SHARED_POOL_RESERVED_MIN_ALLOC.
To resolve this consider increasing SHARED_POOL_RESERVED_MIN_ALLOC to lower
the number of objects being cached into the shared pool reserved space and
increase SHARED_POOL_RESERVED_SIZE and SHARED_POOL_SIZE to increase the
available memory in the shared pool reserved space.
Another consideration: - Pre-9i, changing OPTIMIZER_MAX_PERMUTATIONS to 2000 can reduce shared pool space pressure
3. Resolving error ORA-04031:
Oracle BUGs
Oracle recommends to apply the latest patchser available for your platform. Most of the ORA-4031 errors related to BUGs can be avoided by applying these patchsets. The following table summarize the most common BUGs related with this error, possible workaround and the patchset that fixes the problem.
BUG Description Workaround Fixed
Bug 1397603 ORA-4031 / SGA leak of PERMANENT memory occurs for buffer handles. _db_handles_cached = 0 8172, 901
Bug 1640583 ORA-4031 due to leak / cache buffer chain contention from AND-EQUAL access Not available 8171, 901
Bug:1318267
Not Public INSERT AS SELECT statements may not be shared when they should be if TIMED_STATISTICS. It can lead to ORA-4031 _SQLEXEC_PROGRESSION_COST=0 8171, 8200
Bug:1193003
Not Public Cursors may not be shared in 8.1 when they should be Not available 8162, 8170, 901
Bug 2104071 ORA-4031/excessive "miscellaneous"
shared pool usage possible.
(many PINS) None-> This is known to affect the XML parser. 8174, 9013, 9201
Bug 3910149 KGLHDDEP PROBLEM IN RAC
Slow SGA memory leak in internal permanent space (KGL handle). Backports are available on various platforms and release levels Restart problem node at intervals
(flushing shared pool doesn't
clear permanent structures. 9207, 10105, 10201
Note 263791.1 Several number of BUGs related
to ORA-4031 errors were fixed
in the 9.2.0.5 patchset N/A 9205
ORA-4031 when compiling Java code:
If you run out of memory while compiling a java code (within loadjava or deployejb), you should see an error:
A SQL exception occurred while compiling: : ORA-04031: unable to allocate bytes of shared memory ("shared pool","unknown object","joxlod: init h", "JOX: ioc_allocate_pal")
The solution is to shut down the database and set JAVA_POOL_SIZE to a larger value. The mention of "shared pool" in the error message is a misleading reference to running out of memory in the "Shared Global Area". It does not mean you should increase your SHARED_POOL_SIZE. Instead, you must increase your JAVA_POOL_SIZE, restart your server, and try again.
See Bug 2736601
Small shared pool size
In many cases, a small shared pool can be the cause of the ORA-04031 error.
The following information will help you to adjust the size of the shared pool:
Library Cache Hit Ratio
The hit ratio helps to measure the usage of the shared pool based on how many times a SQL/PLSQL statement needed to be parsed instead of being reused. The following SQL statement help you to calculate the library cache hit ratio:
SELECT SUM(PINS) "EXECUTIONS",
SUM(RELOADS) "CACHE MISSES WHILE EXECUTING"
FROM V$LIBRARYCACHE;
If the ratio of misses to executions is more than 1%, then try to reduce the library cache misses by increasing the shared pool size.
Shared Pool Size Calculation
To calculate the size of the shared pool that best fits to your current workload, please refer to:
Note 1012046.6: HOW TO CALCULATE YOUR SHARED POOL SIZE.
Shared Pool Fragmentation:
Every time a SQL or PL/SQL statement needs to be executed the parse representation is loaded in the library cache requiring a specific amount of free contiguous space. The first resource where the database scans is the free memory available in the shared pool. Once the free memory is exhausted, the database looks for reusing an already allocated piece not in use. If a chunk with the exact size is not available, the scan continues looking for space based on the following criteria:
- The chunk size is larger than the required size
- The space is contiguous
- The chunk is available (not in use)
Then that chunk is split and the remaining free space is added to the appropriate free space list. When the database is operating in this way for a certain period of time the shared pool structure will be fragmented.
When the shared pool is suffering fragmentation ORA-04031 errors (when the database cannot find a contiguous piece of free memory) may occur. Also as a concequence , the allocation of a piece of free space takes more time an the performance may be affected (the "chunk allocation" is protected by a single latch called "shared pool latch" which is held during the whole operation). However, ORA-4031 errors don't always affect the performance of the database.
If the SHARED_POOL_SIZE is large enough, most ORA-04031 errors are a result of dynamic sql fragmenting the shared pool. This can be caused by:
o Not sharing SQL
o Making unnecessary parse calls (soft)
o Not using bind variables
To reduce fragmentation you will need to address one or more of the causes described before. In general to reduce fragmentation you must analyze how the application is using the shared pool and maximize the use of sharable cursors.
Please refer to Note 62143.1, which describes these options in greater detail. This note contains as well further detail on how the shared pool works.
The following views will help you to identify non-sharable versions of SQL/PLSQL text in the shared pool:
V$SQLAREA View
This view keeps information of every SQL statement and PL/SQL block executed in the database. The following SQL can show you statements with literal values or candidates to include bind variables:
SELECT substr(sql_text,1,40) "SQL",
count(*) ,
sum(executions) "TotExecs"
FROM v$sqlarea
WHERE executions < 5
GROUP BY substr(sql_text,1,40)
HAVING count(*) > 30
ORDER BY 2;
Note: The number "30" in the having section of the statement can be adjusted as needed to get more detailed information.
X$KSMLRU View
There is a fixed table called x$ksmlru that tracks allocations in the shared pool that cause other objects in the shared pool to be aged out. This fixed table can be used to identify what is causing the large allocation.
If many objects are being periodically flushed from the shared pool then this will cause response time problems and will likely cause library cache latch contention problems when the objects are reloaded into the shared pool.
One unusual thing about the x$ksmlru fixed table is that the contents of the fixed table are erased whenever someone selects from the fixed table. This is done since the fixed table stores only the largest allocations that have occurred. The values are reset after being selected so that subsequent large allocations can be noted even if they were not quite as large as others that occurred previously. Because of this resetting, the output of selecting from this table should be carefully kept since it cannot be retrieved back after the query is issued.
To monitor this fixed table just run the following:
SELECT * FROM X$KSMLRU WHERE ksmlrsiz > 0;
This view can only be queried by connected as the SYS.
X$KSMSP View (Similar to Heapdump Information)
Using this view you will be able to find out how the free space is currently allocated, which will be helpful to undrestand the level of fragmentation of the shared pool. As it was described before, the first place to find a chunck big enough for the cursor allocation is the free list. The following SQL shows the chunks available in the free list:
select '0 (<140)' BUCKET, KSMCHCLS, KSMCHIDX, 10*trunc(KSMCHSIZ/10) "From",
count(*) "Count" , max(KSMCHSIZ) "Biggest",
trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total"
from x$ksmsp
where KSMCHSIZ<140
and KSMCHCLS='free'
group by KSMCHCLS, KSMCHIDX, 10*trunc(KSMCHSIZ/10)
UNION ALL
select '1 (140-267)' BUCKET, KSMCHCLS, KSMCHIDX,20*trunc(KSMCHSIZ/20) ,
count(*) , max(KSMCHSIZ) ,
trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total"
from x$ksmsp
where KSMCHSIZ between 140 and 267
and KSMCHCLS='free'
group by KSMCHCLS, KSMCHIDX, 20*trunc(KSMCHSIZ/20)
UNION ALL
select '2 (268-523)' BUCKET, KSMCHCLS, KSMCHIDX, 50*trunc(KSMCHSIZ/50) ,
count(*) , max(KSMCHSIZ) ,
trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total"
from x$ksmsp
where KSMCHSIZ between 268 and 523
and KSMCHCLS='free'
group by KSMCHCLS, KSMCHIDX, 50*trunc(KSMCHSIZ/50)
UNION ALL
select '3-5 (524-4107)' BUCKET, KSMCHCLS, KSMCHIDX, 500*trunc(KSMCHSIZ/500) ,
count(*) , max(KSMCHSIZ) ,
trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total"
from x$ksmsp
where KSMCHSIZ between 524 and 4107
and KSMCHCLS='free'
group by KSMCHCLS, KSMCHIDX, 500*trunc(KSMCHSIZ/500)
UNION ALL
select '6+ (4108+)' BUCKET, KSMCHCLS, KSMCHIDX, 1000*trunc(KSMCHSIZ/1000) ,
count(*) , max(KSMCHSIZ) ,
trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total"
from x$ksmsp
where KSMCHSIZ >= 4108
and KSMCHCLS='free'
group by KSMCHCLS, KSMCHIDX, 1000*trunc(KSMCHSIZ/1000);
Note: The information available in this view is the same that is generated as part of a HEAPDUMP level 2.
Also be aware that running this query too often is likely to cause other memory issues in the shared pool.
There is also a port specific bug filed on HP and 10g where running queries on x$ksmsp will hang the database.
If the result of the above query shows that must of the space available is on the top part of the list (meaning available only in very small chuncks). It is very likely that the error is due to a heavy fragmentation.
You can also use this view as follows to review overall memory usage in the SGA
SQL> SELECT KSMCHCLS CLASS, COUNT(KSMCHCLS) NUM, SUM(KSMCHSIZ) SIZ,
To_char( ((SUM(KSMCHSIZ)/COUNT(KSMCHCLS)/1024)),'999,999.00')||'k' "AVG SIZE"
FROM X$KSMSP GROUP BY KSMCHCLS;
CLASS NUM SIZ AVG SIZE
-------- ---------- ---------- ------------
R-free 12 8059200 655.86k <= Reserved List
R-freea 24 960 .04k <= Reserved List
free 331 151736448 447.67k <= Free Memory
freeabl 4768 7514504 1.54k <= Memory for user / system processing
perm 2 30765848 15,022.39k <= Memory allocated to the system
recr 3577 3248864 .89k <= Memory for user / system processing
a) if free memory (SIZ) is low (less than 5mb or so) you may need to increase the shared_pool_size and shared_pool_reserved_size.
b) if perm continually grows then it is possible you are seeing system memory leak.
c) if freeabl and recr are always huge, this indicates that you have lots of cursor info stored that is not releasing.
d) if free is huge but you are still getting 4031 errors, (you can correlate that with the reloads and invalids causing fragmentation)
4. ORA-04031 error and Large Pool
The Large pool is an optional memory area that can be configured to provide large memory allocations for one of the following operations :
session memory for the multi-threaded server and the Oracle XA interface.
The memory ( Buffers ) for Oracle backup and restore operations and for I/O server processes.
Parallel Execution messaging buffers.
The Large pool does not have a LRU list. It is different from reserved space in the shared pool, which uses the same LRU list as other memory allocated from the shared pool.
Chunks of memory are never aged out of the large pool,memory has to be explicitly allocated and freed by each session.
If there is no free memory left when a request is made then an ORA-4031 will be signalled similar to this :
ORA-04031: unable to allocate XXXX bytes of shared memory
("large pool","unknown object","session heap","frame")
Few things can be checked when this error occurs:
1- Check V$SGASTAT and see how much memory is used and free using the following SQL statement:
SELECT pool,name,bytes FROM v$sgastat where pool = 'large pool';
2- You can also take a heapdump level 32 to dump the large pool heap and check free chunks sizes.
Memory is allocated from the large pool in chunks of LARGE_POOL_MIN_ALLOC bytes to help avoid fragmentation. Any request to allocate a chunk size less LARGE_POOL_MIN_ALLOC will be allocated with size of LARGE_POOL_MIN_ALLOC. In general you may see more memory usage when using Large Pool compared to Shared Pool.
Usually to resolve an ORA-4031 in the large pool the LARGE_POOL_SIZE size must be increased.
5. ORA-04031 and SHARED POOL FLUSHING
There are several technics to increase cursor sharability so that shared pool fragmentation is reduce as well as likeability of ORA-4031 errors. The best way is by modifying the application to use bind variables. Another workaround when the application cannot be modified is using CURSOR_SHARING to a value different of EXACT (Be aware that this may cause changes in execution plan, so it is advisable to test the application first). When none of the above techniques can be used and fragmentation is considearble heavy in the system, flushing the shared pool might help alliviating the fragmentation. However some considerations must be taken into account:
Flushing the shared pool will cause that all the cursor that are not in use are removed from the library cache. Therefore just after the shared pool flusing is issued, most of the SQL and PL/SQL cursors will have to be hard parsed. This will increase the CPU usage of the system and will also increase the latch activity.
When applications don't use bind variables and have heavy possibilities of many users doing frequen similar operations (like in OLTP systems) it is common that soon after the flush is issued the fragmentation is back in place. So be advice that flushing the shared pool is not always the solution for a bad application.
For large shared pool flushing the shared pool may cause a halt of the system, specially when the instance is very active. It is recommended to flush the shared pool during off-peak hours.
6. Advanced analysis to ORA-04031error
If none of the techniques provided cannot resolve the occurence of ORA-04031 errors, additional tracing may be needed to get a snapshot of the shared pool when the problem is in place.
Modify the init.ora paramater to add the following events to get a trace file with additional information about the problem:
event = "4031 trace name errorstack level 3"
event = "4031 trace name HEAPDUMP level 3"
Note: This parameter will take not effect unless the instance is bounced.
Starting with 9.2.0.5, instead of requesting heapdump level 1,2 , 3 or 32 you can use level those same levels plus (536870912).
This will generate the 5 largest subheaps AND the 5 largest heap areas within each of those.
If the problem is reproducible, the event can be set at session level using the following statement before the execution of the faulty SQL statement:
SQL> alter session set events '4031 trace name errorstack level 3';
SQL> alter session set events '4031 trace name HEAPDUMP level 536870915';
This trace file should be sent to Oracle Support for troubleshooting.
Important Note: In Oracle 9.2.0.5 and Oracle 10g releases a trace file is generated BY DEFAULT every time an ORA-4031 error occurs, and can be located in the user_dump_dest directory. If your database version is one of these, you don't need to follow the steps described before to generate additional tracing.
RELATED DOCUMENTS
Note 396940.1 FAQ: ORA-4031
Note 1012046.6 How to Calculate Your Shared Pool Size
Note 62143.1 Understanding and Tuning the Shared Pool
Note 61623.1 Resolving Shared Pool Fragmentation In Oracle7
Note 1012049.6 Tuning Library Cache Latch Contention
Note 105813.1 SCRIPT TO SUGGEST MINIMUM SHARED POOL SIZE
Note 316138.1 ORA-4031 / Continuos Growth of 'miscellaneous' in v$sgastat when STATISTICS_LEVEL is set to TYPICAL or ALL
Note 367392.1 ORA-4031 with calls to ksfd_alloc_sgabuffer, ksfd_alloc_contig_buffer, ksfd_get_contig_buffer
Keywords
SHARED~POOL LIBRARY~CACHE
Thursday, January 18, 2007
utl_file csv output
[質問]
以下のパッケージを作成しておけば、select文によって表のデータをCSV形式で
ファイルに出力させることが可能です。
各表ごとにパッケージを作成する必要はありません。
なお、本パッケージはR8i以降でご使用いただけます。
[回答]
以下にパッケージを作成するスクリプト(mkCSV.sql)を示します。
注：スクリプト内の、<ファイルを出力するディレクトリを指定>には適切なディレクトリ
を指定してください｡
（なお、本スクリプトはお客様の便宜のために公開しているものですので、スクリプト
自体に対するご質問はご遠慮下さいますようお願い申し上げます。）
---------- cut ---------------------- cut -------------- cut --------------
CREATE OR REPLACE PACKAGE print_csv_pack
AS
FUNCTION print_csv_exe(v_rowid in varchar2, v_owner in varchar2, v_table in varchar2)
RETURN varchar2;
FUNCTION print_csv(v_rowid in varchar2, v_rownum in number)
RETURN varchar2;
-- 引数のROWIDの行が存在する表の所有者名
v_owner varchar2(30);
-- 引数のROWIDの行が存在する表名
v_object_name varchar2(30);
END print_csv_pack;
/
show error
CREATE OR REPLACE PACKAGE BODY print_csv_pack
AS
/* 引数の表の各行の値をCSV形式でファイルに出力するファンクション */
FUNCTION print_csv_exe
(v_rowid in varchar2,
v_owner in varchar2,
v_table in varchar2)
RETURN varchar2
IS
file_handl utl_file.file_type;
-- dba_tab_columnsより、引数の表の列名を取得
cursor dba_tab_columns_cur is
select column_name
from sys.dba_tab_columns
where owner = v_owner and table_name = v_table;
-- 1列目には','を挿入しないため、1列目かどうかを判定するフラグ
loop_flag number := 1;
-- 動的に作成されるSQL文を格納する変数
sql_text varchar2(2000);
-- 各列の値
column_value varchar2(32767);
-- CSV形式で列値をつなげた文字列
column_values varchar2(32767);
BEGIN
file_handl := utl_file.fopen( '<ファイルを出力するディレクトリを指定>','csv.out','a',32767);
for dba_tab_columns_cur_rec in dba_tab_columns_cur loop
-- dba_tab_columnsのcolumn_nameの値を使用し、引数の表の各列値を取得するSELECT文を作成
sql_text := 'select ' || dba_tab_columns_cur_rec.column_name
|| ' from ' || v_owner || '.' || v_table ||
' where rowid = ''' || v_rowid || '''';
-- 作成したSQL文を実行
execute immediate sql_text into column_value;
-- 取得した列値を','をはさんで接続し、CSV形式の文字列を作成
if loop_flag = 1 then
column_values := column_values || column_value;
loop_flag := 0;
else
column_values := column_values || ',' || column_value;
end if;
end loop;
utl_file.put_line(file_handl, column_values);
utl_file.fclose(file_handl);
return 'Success!!';
END print_csv_exe;
/* 引数のROWIDの行が存在する表を特定し、特定した表を引数としてprint_csv_exeを実行する
ファンクション */
FUNCTION print_csv
(v_rowid in varchar2, v_rownum in number)
RETURN varchar2
IS
-- CSV形式で列値をつなげた文字列
column_values varchar2(32767);
BEGIN
-- rownum=1の場合のみ、所有者名、表名を取得
if v_rownum = 1 then
select owner, object_name into v_owner, v_object_name
from sys.dba_objects
where object_id = dbms_rowid.rowid_object(v_rowid);
end if;
-- print_csv_exeを実行し、CSV形式で列値をつなげた文字列を取得
column_values := print_csv_exe(v_rowid, v_owner, v_object_name);
return column_values;
END print_csv;
END print_csv_pack;
/
show error
---------- cut ---------------------- cut -------------- cut --------------
scott.empをCSV形式で出力する例を示します。
1.初期化パラメータutl_file_dirに、ファイルを出力させるディレクトリを設定して
インスタンスを起動します｡
2.以下の手順で上記パッケージを実行します｡
SQL> connect / as sysdba
SQL> grant select on dba_tab_columns to scott; <== パッケージを作成するスキーマには、
SQL> grant select on dba_objects to scott; <== 両ビューのselect権限を個別に与えて
SQL> connect scott/tiger おく必要があります。
SQL> @mkCSV.sql
SQL> select print_csv_pack.print_csv(rowid, rownum) from scott.emp
where empno >= 7800;
PRINT_CSV_PACK.PRINT_CSV(ROWID,ROWNUM)
--------------------------------------------------------------------------------
Success!!
Success!!
Success!!
Success!!
Success!!
Success!!
以下、csv.outファイルの内容。
7839,KING,PRESIDENT,,81-11-17,5000,,10
7844,TURNER,SALESMAN,7698,81-09-08,1500,0,30
7876,ADAMS,CLERK,7788,83-01-12,1100,,20
7900,JAMES,CLERK,7698,81-12-03,950,,30
7902,FORD,ANALYST,7566,81-12-03,3000,,20
7934,MILLER,CLERK,7782,82-01-23,1300,,10
以下のパッケージを作成しておけば、select文によって表のデータをCSV形式で
ファイルに出力させることが可能です。
各表ごとにパッケージを作成する必要はありません。
なお、本パッケージはR8i以降でご使用いただけます。
[回答]
以下にパッケージを作成するスクリプト(mkCSV.sql)を示します。
注：スクリプト内の、<ファイルを出力するディレクトリを指定>には適切なディレクトリ
を指定してください｡
（なお、本スクリプトはお客様の便宜のために公開しているものですので、スクリプト
自体に対するご質問はご遠慮下さいますようお願い申し上げます。）
---------- cut ---------------------- cut -------------- cut --------------
CREATE OR REPLACE PACKAGE print_csv_pack
AS
FUNCTION print_csv_exe(v_rowid in varchar2, v_owner in varchar2, v_table in varchar2)
RETURN varchar2;
FUNCTION print_csv(v_rowid in varchar2, v_rownum in number)
RETURN varchar2;
-- 引数のROWIDの行が存在する表の所有者名
v_owner varchar2(30);
-- 引数のROWIDの行が存在する表名
v_object_name varchar2(30);
END print_csv_pack;
/
show error
CREATE OR REPLACE PACKAGE BODY print_csv_pack
AS
/* 引数の表の各行の値をCSV形式でファイルに出力するファンクション */
FUNCTION print_csv_exe
(v_rowid in varchar2,
v_owner in varchar2,
v_table in varchar2)
RETURN varchar2
IS
file_handl utl_file.file_type;
-- dba_tab_columnsより、引数の表の列名を取得
cursor dba_tab_columns_cur is
select column_name
from sys.dba_tab_columns
where owner = v_owner and table_name = v_table;
-- 1列目には','を挿入しないため、1列目かどうかを判定するフラグ
loop_flag number := 1;
-- 動的に作成されるSQL文を格納する変数
sql_text varchar2(2000);
-- 各列の値
column_value varchar2(32767);
-- CSV形式で列値をつなげた文字列
column_values varchar2(32767);
BEGIN
file_handl := utl_file.fopen( '<ファイルを出力するディレクトリを指定>','csv.out','a',32767);
for dba_tab_columns_cur_rec in dba_tab_columns_cur loop
-- dba_tab_columnsのcolumn_nameの値を使用し、引数の表の各列値を取得するSELECT文を作成
sql_text := 'select ' || dba_tab_columns_cur_rec.column_name
|| ' from ' || v_owner || '.' || v_table ||
' where rowid = ''' || v_rowid || '''';
-- 作成したSQL文を実行
execute immediate sql_text into column_value;
-- 取得した列値を','をはさんで接続し、CSV形式の文字列を作成
if loop_flag = 1 then
column_values := column_values || column_value;
loop_flag := 0;
else
column_values := column_values || ',' || column_value;
end if;
end loop;
utl_file.put_line(file_handl, column_values);
utl_file.fclose(file_handl);
return 'Success!!';
END print_csv_exe;
/* 引数のROWIDの行が存在する表を特定し、特定した表を引数としてprint_csv_exeを実行する
ファンクション */
FUNCTION print_csv
(v_rowid in varchar2, v_rownum in number)
RETURN varchar2
IS
-- CSV形式で列値をつなげた文字列
column_values varchar2(32767);
BEGIN
-- rownum=1の場合のみ、所有者名、表名を取得
if v_rownum = 1 then
select owner, object_name into v_owner, v_object_name
from sys.dba_objects
where object_id = dbms_rowid.rowid_object(v_rowid);
end if;
-- print_csv_exeを実行し、CSV形式で列値をつなげた文字列を取得
column_values := print_csv_exe(v_rowid, v_owner, v_object_name);
return column_values;
END print_csv;
END print_csv_pack;
/
show error
---------- cut ---------------------- cut -------------- cut --------------
scott.empをCSV形式で出力する例を示します。
1.初期化パラメータutl_file_dirに、ファイルを出力させるディレクトリを設定して
インスタンスを起動します｡
2.以下の手順で上記パッケージを実行します｡
SQL> connect / as sysdba
SQL> grant select on dba_tab_columns to scott; <== パッケージを作成するスキーマには、
SQL> grant select on dba_objects to scott; <== 両ビューのselect権限を個別に与えて
SQL> connect scott/tiger おく必要があります。
SQL> @mkCSV.sql
SQL> select print_csv_pack.print_csv(rowid, rownum) from scott.emp
where empno >= 7800;
PRINT_CSV_PACK.PRINT_CSV(ROWID,ROWNUM)
--------------------------------------------------------------------------------
Success!!
Success!!
Success!!
Success!!
Success!!
Success!!
以下、csv.outファイルの内容。
7839,KING,PRESIDENT,,81-11-17,5000,,10
7844,TURNER,SALESMAN,7698,81-09-08,1500,0,30
7876,ADAMS,CLERK,7788,83-01-12,1100,,20
7900,JAMES,CLERK,7698,81-12-03,950,,30
7902,FORD,ANALYST,7566,81-12-03,3000,,20
7934,MILLER,CLERK,7782,82-01-23,1300,,10
Thursday, January 11, 2007
How to create outlines for sql statements with bind variables and/or key word "into"
alter session set create_stored_outlines=true;
variable x number;
select count(*) into :x from test;
alter session set create_stored_outlines=false;
select sql_text from outln.ol$;
-------------------------------------------------
Now you can see the result,
with both bind variable ":x" and key word "into".
variable x number;
select count(*) into :x from test;
alter session set create_stored_outlines=false;
select sql_text from outln.ol$;
-------------------------------------------------
Now you can see the result,
with both bind variable ":x" and key word "into".
Tuesday, January 09, 2007
skip_unusable_indexes
有关10g中datapump import的skip_unusable_indexes参数的说明
如果导出时有无效的索引（status：unusable）
按照10g的手册
这个参数设成Y会导致导入时跳过无效索引所在的表，
而默认值N会导致导入时出错，导致导入失败。
根据实际测试的结果（10.1.0.5）
这个参数无论是设成Y还是默认值N，导入都成功，
并且包括无效索引和它所在的表，并且索引的状态为valid。
Test details:
create table testdp(id number(5));
insert into tsetdp values(100);
commit;
create index inddp on testdp(id);
alter table testdp move tablespace test;
select status from dba_indexes where index_name='INDDP';
--Here you can see the status of the index is UNUSABLE.
export the schema
drop the schema
import the schema
select status from dba_indexes where index_name='INDDP';
--Here you can see the status of the index is VALID.
如果导出时有无效的索引（status：unusable）
按照10g的手册
这个参数设成Y会导致导入时跳过无效索引所在的表，
而默认值N会导致导入时出错，导致导入失败。
根据实际测试的结果（10.1.0.5）
这个参数无论是设成Y还是默认值N，导入都成功，
并且包括无效索引和它所在的表，并且索引的状态为valid。
Test details:
create table testdp(id number(5));
insert into tsetdp values(100);
commit;
create index inddp on testdp(id);
alter table testdp move tablespace test;
select status from dba_indexes where index_name='INDDP';
--Here you can see the status of the index is UNUSABLE.
export the schema
drop the schema
import the schema
select status from dba_indexes where index_name='INDDP';
--Here you can see the status of the index is VALID.
Monday, January 08, 2007
[Oracle Data Pump] ダンプ･ファイルを途中で削除した場合、ジョブが残存する
[概要]
Oracle Data Pump Export/Importのジョブを停止中(STATE: NOT RUNNING)に、OSコマンド
などにより手動でダンプ・ファイルを削除すると、ジョブへの接続が行なえなくなり、結
果としてジョブが残存します。
このような現象が発生する原因と対処方法について下記に説明します。
[対象リリース]
Oracle Database 10g Release1 (10.1.0)
[対象プラットフォーム]
すべてのプラットフォーム
[詳細]
(a) 具体例
本現象が発生する具体例を以下に示します。
1. Data Pump Exportを実行し、対話方式インタラクティブ・モードに切り替えて、stop_job
コマンドを実行します。
% expdp scott/tiger directory=test_dir dumpfile=scott.dmp nologfile=y job_name=scott_job
Export: Release 10.1.0.4.0 - 64bit Production on 金曜日, 08 7月, 2005 15:16
Copyright (c) 2003, Oracle. All rights reserved.
接続先: Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
FLASHBACKでは、データベース整合性が自動的に維持されます。
"SCOTT"."SCOTT_JOB"を起動しています: scott/******** directory=test_dir dumpfile=scott.dmp nolo
gfile=y job_name=scott_job
BLOCKSメソッドを使用して見積り中です...
^C
Export> stop_job
このジョブを停止しますか([y]/n): yes
2. dba_datapump_jobs ビューを確認して、1. で停止したジョブの状態が 'NOT RUNNING' にな
ることを確認します。
SQL> select * from dba_datapump_jobs where JOB_NAME='SCOTT_JOB';
OWNER_NAME JOB_NAME OPERATION
------------------------ ------------------------------ ------------------------
JOB_MODE STATE DEGREE ATTACHED_SESSIONS
------------------------ ------------------------------ ---------- -----------------
SCOTT SCOTT_JOB EXPORT
SCHEMA NOT RUNNING 0 0
3. 出力されたダンプファイルの存在を確認し、これを削除します。
% ls -l /home/pires/ora10104/work/scott.dmp
-rw-r----- 1 ora10104 dba 4096 7月 8日 15:16 /home/pires/ora10104/work/scott.dmp
% rm scott.dmp
rm: scott.dmp を消去しますか (yes/no)? y
4. attach パラメータを使用して、上記で停止したジョブに再度アタッチします。すると、下記の
ようなエラーが発生し、アタッチに失敗します。
% expdp scott/tiger attach=scott_job
Export: Release 10.1.0.4.0 - 64bit Production on 金曜日, 08 7月, 2005 15:20
Copyright (c) 2003, Oracle. All rights reserved.
接続先: Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
ORA-39002: 操作が無効です
ORA-39000: ダンプ・ファイル指定が無効です
ORA-31640: ダンプ・ファイル"/home/pires/ora10104/work/scott.dmp"を読取りのためにオープンできません
ORA-27037: ファイル・ステータスを取得できません。
SVR4 Error: 2: No such file or directory
Additional information: 3
5. dba_datapump_jobs ビューを確認すると、該当のジョブ(ジョブ名がSCOTT_JOB）が
残存していることが分かります。
SQL> select * from dba_datapump_jobs where JOB_NAME='SCOTT_JOB';
OWNER_NAME JOB_NAME OPERATION
------------------------ ------------------------------ ------------------------
JOB_MODE STATE DEGREE ATTACHED_SESSIONS
------------------------ ------------------------------ ---------- -----------------
SCOTT SCOTT_JOB EXPORT
SCHEMA NOT RUNNING 0 0
(b) 発生原因
Oracle Data Pump ではジョブを実行する際に、ユーザのスキーマにジョブに関する様々な情報を
格納する 'マスター表' という表が作成されます。これは Data Pump の機能の中心的役割を果た
すものです。
このマスター表には、expdp もしくは impdp を実行するオブジェクトやジョブの実行パラメータ
および各プロセスの状態、ダンプファイルの作成状況などの情報を格納しています。
マスター表は、expdp を実行中に作成され、expdp の最終段階でダンプファイルに書き込まれます。
これとは逆に、impdp 実行時にはその最初の段階で'マスター表をロードするようになっています。
対象のジョブにアタッチする際にも、ジョブの情報を取得するために、ファイルセットにアクセス
して情報を取得しようとします。ジョブのステータスが 'NOT RUNNING' （停止中）であっても、
その途中でマスターテーブルの情報を包含したダンプファイルを削除してしまうことで、ジョブに
関する情報を取得することができないため、このようにエラーを返し、さらに dba_datapump_jobs
ビューにエントリが残ってしまうという状況が発生します。
本現象の影響は、dba_datapump_jobs ビューにエントリが残るのみとなります。
(c) 対処方法
下記のいずれかの方法を実施することにより、dba_datapump_jobs ビューからジョブのエントリを
削除することができます。
- ジョブを起動したユーザを削除する
- マスター表を削除する
マスター表の削除についてさらに詳しく説明します。
上記の具体例で示したように、ビューにエントリが残るのは、データベース上にマスターテーブル
が不正な形で残ってしまっているためとなります。正常に処理が終了した場合にはこのようにエン
トリが残ることはありません。
マスター表は、実行したジョブ名と同じ名前で、そのジョブを開始したユーザのスキーマ内に作成
されます。これを手動で削除することによりビューに不正に残存してしまったエントリを削除する
ことができます。
なお、これを手動で削除することは問題となるオペレーションではありません。
下記にエントリを削除する方法を紹介します。
1. dba_tables/user_tables などのビューから Data Pump のジョブを起動・開始したユーザの所有
する表を確認します。マスター表は実行ジョブ名と同じ名称となります。
今回の例では、マスター表の名前は 'SCOTT_JOB' です。
SQL> connect scott/tiger
接続されました。
SQL> select table_name from user_tables;
TABLE_NAME
------------------------------
SCOTT_JOB
SALGRADE
BONUS
EMP
DEPT
5行が選択されました。
2. マスター表を削除します。
SQL> drop table SCOTT_JOB purge;
表が削除されました。
3. dba_tables/user_tables ビューおよび dba_datapump_jobs ビューを参照し、該当エントリが削除
されていることが確認できます。
SQL> select table_name from user_tables where table_name='SCOTT_JOB';
レコードが選択されませんでした。
SQL> select * from dba_datapump_jobs where job_name='SCOTT_JOB';
レコードが選択されませんでした。
[参照情報]
『Oracle Database ユーティリティ 10g リリース1（10.1）』
[更新履歴]
2005/07/08 10.1.0.4での検証結果をもとに全面的に修正し公開区分を「サポート契約あり」に変更
2004/03/18 本文書を公開（社内のみ）
Oracle Data Pump Export/Importのジョブを停止中(STATE: NOT RUNNING)に、OSコマンド
などにより手動でダンプ・ファイルを削除すると、ジョブへの接続が行なえなくなり、結
果としてジョブが残存します。
このような現象が発生する原因と対処方法について下記に説明します。
[対象リリース]
Oracle Database 10g Release1 (10.1.0)
[対象プラットフォーム]
すべてのプラットフォーム
[詳細]
(a) 具体例
本現象が発生する具体例を以下に示します。
1. Data Pump Exportを実行し、対話方式インタラクティブ・モードに切り替えて、stop_job
コマンドを実行します。
% expdp scott/tiger directory=test_dir dumpfile=scott.dmp nologfile=y job_name=scott_job
Export: Release 10.1.0.4.0 - 64bit Production on 金曜日, 08 7月, 2005 15:16
Copyright (c) 2003, Oracle. All rights reserved.
接続先: Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
FLASHBACKでは、データベース整合性が自動的に維持されます。
"SCOTT"."SCOTT_JOB"を起動しています: scott/******** directory=test_dir dumpfile=scott.dmp nolo
gfile=y job_name=scott_job
BLOCKSメソッドを使用して見積り中です...
^C
Export> stop_job
このジョブを停止しますか([y]/n): yes
2. dba_datapump_jobs ビューを確認して、1. で停止したジョブの状態が 'NOT RUNNING' にな
ることを確認します。
SQL> select * from dba_datapump_jobs where JOB_NAME='SCOTT_JOB';
OWNER_NAME JOB_NAME OPERATION
------------------------ ------------------------------ ------------------------
JOB_MODE STATE DEGREE ATTACHED_SESSIONS
------------------------ ------------------------------ ---------- -----------------
SCOTT SCOTT_JOB EXPORT
SCHEMA NOT RUNNING 0 0
3. 出力されたダンプファイルの存在を確認し、これを削除します。
% ls -l /home/pires/ora10104/work/scott.dmp
-rw-r----- 1 ora10104 dba 4096 7月 8日 15:16 /home/pires/ora10104/work/scott.dmp
% rm scott.dmp
rm: scott.dmp を消去しますか (yes/no)? y
4. attach パラメータを使用して、上記で停止したジョブに再度アタッチします。すると、下記の
ようなエラーが発生し、アタッチに失敗します。
% expdp scott/tiger attach=scott_job
Export: Release 10.1.0.4.0 - 64bit Production on 金曜日, 08 7月, 2005 15:20
Copyright (c) 2003, Oracle. All rights reserved.
接続先: Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
ORA-39002: 操作が無効です
ORA-39000: ダンプ・ファイル指定が無効です
ORA-31640: ダンプ・ファイル"/home/pires/ora10104/work/scott.dmp"を読取りのためにオープンできません
ORA-27037: ファイル・ステータスを取得できません。
SVR4 Error: 2: No such file or directory
Additional information: 3
5. dba_datapump_jobs ビューを確認すると、該当のジョブ(ジョブ名がSCOTT_JOB）が
残存していることが分かります。
SQL> select * from dba_datapump_jobs where JOB_NAME='SCOTT_JOB';
OWNER_NAME JOB_NAME OPERATION
------------------------ ------------------------------ ------------------------
JOB_MODE STATE DEGREE ATTACHED_SESSIONS
------------------------ ------------------------------ ---------- -----------------
SCOTT SCOTT_JOB EXPORT
SCHEMA NOT RUNNING 0 0
(b) 発生原因
Oracle Data Pump ではジョブを実行する際に、ユーザのスキーマにジョブに関する様々な情報を
格納する 'マスター表' という表が作成されます。これは Data Pump の機能の中心的役割を果た
すものです。
このマスター表には、expdp もしくは impdp を実行するオブジェクトやジョブの実行パラメータ
および各プロセスの状態、ダンプファイルの作成状況などの情報を格納しています。
マスター表は、expdp を実行中に作成され、expdp の最終段階でダンプファイルに書き込まれます。
これとは逆に、impdp 実行時にはその最初の段階で'マスター表をロードするようになっています。
対象のジョブにアタッチする際にも、ジョブの情報を取得するために、ファイルセットにアクセス
して情報を取得しようとします。ジョブのステータスが 'NOT RUNNING' （停止中）であっても、
その途中でマスターテーブルの情報を包含したダンプファイルを削除してしまうことで、ジョブに
関する情報を取得することができないため、このようにエラーを返し、さらに dba_datapump_jobs
ビューにエントリが残ってしまうという状況が発生します。
本現象の影響は、dba_datapump_jobs ビューにエントリが残るのみとなります。
(c) 対処方法
下記のいずれかの方法を実施することにより、dba_datapump_jobs ビューからジョブのエントリを
削除することができます。
- ジョブを起動したユーザを削除する
- マスター表を削除する
マスター表の削除についてさらに詳しく説明します。
上記の具体例で示したように、ビューにエントリが残るのは、データベース上にマスターテーブル
が不正な形で残ってしまっているためとなります。正常に処理が終了した場合にはこのようにエン
トリが残ることはありません。
マスター表は、実行したジョブ名と同じ名前で、そのジョブを開始したユーザのスキーマ内に作成
されます。これを手動で削除することによりビューに不正に残存してしまったエントリを削除する
ことができます。
なお、これを手動で削除することは問題となるオペレーションではありません。
下記にエントリを削除する方法を紹介します。
1. dba_tables/user_tables などのビューから Data Pump のジョブを起動・開始したユーザの所有
する表を確認します。マスター表は実行ジョブ名と同じ名称となります。
今回の例では、マスター表の名前は 'SCOTT_JOB' です。
SQL> connect scott/tiger
接続されました。
SQL> select table_name from user_tables;
TABLE_NAME
------------------------------
SCOTT_JOB
SALGRADE
BONUS
EMP
DEPT
5行が選択されました。
2. マスター表を削除します。
SQL> drop table SCOTT_JOB purge;
表が削除されました。
3. dba_tables/user_tables ビューおよび dba_datapump_jobs ビューを参照し、該当エントリが削除
されていることが確認できます。
SQL> select table_name from user_tables where table_name='SCOTT_JOB';
レコードが選択されませんでした。
SQL> select * from dba_datapump_jobs where job_name='SCOTT_JOB';
レコードが選択されませんでした。
[参照情報]
『Oracle Database ユーティリティ 10g リリース1（10.1）』
[更新履歴]
2005/07/08 10.1.0.4での検証結果をもとに全面的に修正し公開区分を「サポート契約あり」に変更
2004/03/18 本文書を公開（社内のみ）
When Oracle will use RBO
If OPTIMIZER_MODE=CHOOSE, if statistics do not exist, and if you do not add hints to SQL statements, then SQL statements use the RBO.(9iR2 manual)
10g中OPTIMIZER_MODE参数已经不能设为CHOOSE了。
10g中OPTIMIZER_MODE参数已经不能设为CHOOSE了。
Sunday, January 07, 2007
How to open English OUI in other language OS
PSR 9.2.0.6 のOUIを英語モードで起動する方法 -> DocID:334894
setup.exe -J-Duser.language="en" -record -destinationFile C:\temp\oui_9206_en.rsp
-- エラーメッセージ
Could not create the Java virtual machibe
setup.exe -record -destinationFile C:\temp\oui_9206_en.rsp -J-Duser.language="en"
英語モードでレスポンスファイルを作成すると"DEPENDENCY_LIST"のパラメータが存在する。
setup.exe -J-Duser.language="en" -record -destinationFile C:\temp\oui_9206_en.rsp
-- エラーメッセージ
Could not create the Java virtual machibe
setup.exe -record -destinationFile C:\temp\oui_9206_en.rsp -J-Duser.language="en"
英語モードでレスポンスファイルを作成すると"DEPENDENCY_LIST"のパラメータが存在する。
Friday, January 05, 2007
DEPENDENCY_LIST format
DEPENDENCY_LIST参数在マニュアル中和9iR2のCD
里自带的レスポンスファイル中的格式是"oracle.swd.oui","2.2.0.12.0"。
但是在生成的レスポンスファイル中的格式是"oracle.swd.oui:2.2.0.12.0"。
经过测试，「"oracle.swd.oui","X.X.X.X.X"」，「"oracle.swd.oui"」，
「"oracle.swd.oui:2.2.0.12.0"」这三种格式都可以正常的安装9iR2のCD中的
OUI2.2.0.12.0。而「"oracle.swd.oui:X.X.X.X.X"」在X.X.X.X.X不为2.2.0.12.0
时将导致OUI没有被安装。
这个现象说明，只有"oracle.swd.oui:2.2.0.12.0"这种格式中，版本号是起作用的。
里自带的レスポンスファイル中的格式是"oracle.swd.oui","2.2.0.12.0"。
但是在生成的レスポンスファイル中的格式是"oracle.swd.oui:2.2.0.12.0"。
经过测试，「"oracle.swd.oui","X.X.X.X.X"」，「"oracle.swd.oui"」，
「"oracle.swd.oui:2.2.0.12.0"」这三种格式都可以正常的安装9iR2のCD中的
OUI2.2.0.12.0。而「"oracle.swd.oui:X.X.X.X.X"」在X.X.X.X.X不为2.2.0.12.0
时将导致OUI没有被安装。
这个现象说明，只有"oracle.swd.oui:2.2.0.12.0"这种格式中，版本号是起作用的。
Pseudocolumn: rownum
Oracle中处理诸如“最大10条”之类的问题
我们都知道Oracle提供了假列rownum，但是互联网上对于rownum的中文介绍
大多数是错误的。比如对于选取id最大的10条记录这个问题，按照互联网上
提供的方法是：
select id from test_table where rownum<11 order by id desc;
这样的sql语句是错误的，每条记录的rownum在排序以前就已经被赋值，正确
的方法是使用subquery：
select id from (select id from test_table order by id desc)
where rownum<11;
我们都知道Oracle提供了假列rownum，但是互联网上对于rownum的中文介绍
大多数是错误的。比如对于选取id最大的10条记录这个问题，按照互联网上
提供的方法是：
select id from test_table where rownum<11 order by id desc;
这样的sql语句是错误的，每条记录的rownum在排序以前就已经被赋值，正确
的方法是使用subquery：
select id from (select id from test_table order by id desc)
where rownum<11;
Subscribe to:
Posts (Atom)
