LAST UPDATE:
Jun 15, 2011
APPLIES TO:
Oracle Server - Enterprise Edition - Version: 9.2.0.1 to 11.1.0.8 - Release: 9.2 to 11.1 Information in this document applies to any platform. Customers should have limited access to these scripts. There can be database hangs and impacts to performance running these too often.
PURPOSE:
There are a number of x$ queries in various notes in WebIV. This note is an attempt to collect the commonly used scripts for investigating Shared Pool problems in one place. Attached is a ZIP file with the common scripts for analysis of fragmentation and/or memory problems.
Click here (last updated 2-11-2009) to download the attached zip file.
SOFTWARE REQUIREMENTS/PREREQUISITES:
These scripts access data from the X$ dictionary tables directly. To run the scripts you will need to login with SYS privileges. CONFIGURING THE SAMPLE CODE No special instructions for these scripts. It may be necessary to adjust the spool output file to use a directory path where you have write privileges.
RUNNING THE SAMPLE CODE:
Scripts:
==>ChunkOverview.sql: spools to chunkoverview.out If only able to run one script on the X$ dictionary tables, run this one. You will see overview information from V$SGA and a high level breakdown of the categories of memory chunks in the Shared pool. You will also see a more detailed breakdown of chunks with summing up large chunks vs. small chunks.
==>ChunkClassBreakdown.sql:
spools to chunksummary.out
Provides just the high level breakdown of the categories of memory chunks in the Shared Pool.
General rules of thumb:
a) if free memory (Tot Size) 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)
The key data from this high level look is free and freeable average chunk size.
If that is in the 100s or even low 1000s of bytes, this points to excessive fragmentation.
==>ChunkByTypeClass.sql: spools to chunkbytype.out Shows a more detailed breakdown of what memory is used where in the Shared Pool showing both memory type and memory class. This information not needed very often.
==>ChunkBreakdown.sql: spools to chunks.out Shows the lowest level information of on the memory chunks being used in the Shared Pool. This information is not needed very often.
CAUTION:
This sample code is provided for educational purposes only and not supported by Oracle Support Services. It has been tested internally, however, and works as documented. We do not guarantee that it will work for you, so be sure to test it in your environment before relying on it. Proofread this sample code before using it! Due to the differences in the way text editors, e-mail packages and operating systems handle text formatting (spaces, tabs and carriage returns), this sample code may not be in an executable state when you first receive it. Check over the sample code to ensure that errors of this type are corrected.
SAMPLE CODE:
The scripts are attached in a zip file as there are often problems between platforms with cut and paste from a note.
SAMPLE CODE OUTPUT:
ChunkOverview.sql spools to chunkoverview.out
Name BYTES Auto
---------------------------------------- ---------- ----
Fixed SGA Size 1334380 No
Redo Buffers 5844992 No
Buffer Cache Size 268435456 Yes
...
Chunk
Class Num Chunks Min Size Max Size Avg Size Tot Size
---------- -------------------------- -------------------- ---------------------- -------------------- -----------------
recr 60,248 32 3,977,156 969 58,395,008
perm 474 8 3,981,268 109,155 51,739,720
freeabl 49,926 16 334,348 1,823 91,062,956
free 841 20 1,058,464 2,198 1,849,228
R-freea 102 24 24 24 2,448
R-free 51 212,888 212,888 212,888 10,857,288
----------------
Total Alloc 213,906,648
Bucket From Count Biggest AvgSize
-------------- ------------- --------------- ------------------ ------------
0 (<4400) 0 805 392 68
500 11 936 794
1,000 2 1,276 1,142
1,500 2 1,892 1,844
2,000 7 2,144 2,097
2,500 1 2,852 2,852
3,000 2 3,260 3,146
********** ------------
sum 830
6+ (4108+) 6,000 1 6,288 6,288
7,000 1 7,576 7,576
12,000 1 12,288 12,288
16,000 1 16,504 16,504
21,000 1 21,264 21,264
32,000 1 32,888 32,888
71,000 1 71,704 71,704
74,000 1 74,544 74,544
454,000 1 454,184 454,184
1,058,000 1 1,058,464 1,058,464
********** ------------
sum 10
ChunkClassBreakdown.sql spools to chunksummary.out
Chunk Class Num Chunks Min Size Max Size Avg Size Tot Size ----------------- -------------------------- --------------- ---------------- ---------------- ---------------- recr 60,216 32 3,977,156 969 58,374,128 perm 473 8 3,981,268 109,386 51,739,720 freeabl 49,879 16 334,348 1,823 90,959,200 free 843 20 1,172,680 2,341 1,973,864 R-freea 102 24 24 24 2,448 R-free 51 212,888 212,888 212,888 10,857,288 ---------------- Total Allo 213,906,648
ChunkByTypeClass.sql spools to chunkbytype.out
Allocation Chunk Subpool Type Class No. of Chunks Min Size Max Size Avg Size Tot Size ----------- ---------------- --------------- ---------------------- --------------- --------------- --------------- --------------- 1 CCursor freeabl 8976 1,072 3,616 1,187 10,660,112 1 recr 3511 1,072 1,072 1,072 3,763,792 *************** --------------- Total Mem 14,423,904 1 CPM trailer perm 2 8 8 8 16 *************** --------------- Total Mem 16 1 Cursor Stats freeabl 302 4,096 4,096 4,096 1,236,992 1 recr 4 4,096 4,096 4,096 16,384 *************** --------------- Total Mem 1,253,376 ....
ChunkBreakdown.sql spools to chunks.out
Subpool Allocation Type NUM_CHUNKS Min Size Max Size Avg Size Tot Size ------------ ----------------------------- -------------------------- --------------- --------------- --------------- ---------------- 1 CPM trailer 2 8 8 8 16 1 kodosgi kodos 1 16 16 16 16 1 listener addres 1 16 16 16 16 1 KSN WaitID 3 16 16 16 48 ... 1 reserved stoppe 102 24 24 24 2,448 ...
Attachments:
Various internal scripts – 02-11-2009(2.59 KB)
参考：
Common X$ dictionary table queries for ORA-4031 investigation (Doc ID 742575.1 INTERNAL)