LAST UPDATE:
|
1 |
Jun 15, 2011 |
APPLIES TO:
|
1 2 3 |
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:
|
1 2 3 4 |
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:
|
1 2 3 4 |
==>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. |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
==>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. |
|
1 2 3 4 |
==>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. |
|
1 2 3 4 |
==>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:
|
1 2 |
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:
|
1 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 |
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
|
1 2 3 4 5 6 7 8 9 10 11 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
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
|
1 2 3 4 5 6 7 8 9 |
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)