| What is Bucket/Chunk and how to check the fragment on shared pool [message #65467] |
Mon, 27 September 2004 07:02 |
Eric Huang
Messages: 3
Registered: September 2004
|
Junior Member |
|
|
Hi,
I run the below SQL to check whether there has fragment on the shared pool:
select decode( sign(ksmchsiz - 812), -1, (ksmchsiz - 16) / 4, decode( sign(ksmchsiz - 4012), -1, trunc((ksmchsiz + 11924) / 64),
decode( sign(ksmchsiz - 65548), -1, trunc(1/log(ksmchsiz - 11, 2)) + 238, 254 ) ) ) bucket, sum(ksmchsiz) free_space,
count(*) free_chunks, trunc(avg(ksmchsiz)) average_size, max(ksmchsiz) biggest
from sys.x$ksmsp
where inst_id = userenv('Instance') and ksmchcls = 'free'
group by decode( sign(ksmchsiz - 812), -1, (ksmchsiz - 16) / 4, decode( sign(ksmchsiz - 4012), -1,
trunc((ksmchsiz + 11924) / 64), decode( sign(ksmchsiz - 65548), -1, trunc(1/log(ksmchsiz - 11, 2)) + 238, 254 ) ) )
Result is below:
BUCKET FREE_SPACE FREE_CHUNKS AVERAGE_SIZE BIGGEST
====== ========== =========== =========== =======
2 65904 2746 24 24
4 78592 2456 32 32
6 91000 2275 40 40
May I have the below questions:
1.What is the BUCKET and CHUNKS/FREE_CHUNKS?
2.From the above SQL/result, how to determine whether there has fragmentation on the shared_pool? If not, how to check the shared_pool is fragmented or not?
Best Regards,
Eric Huang
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
