Home » RDBMS Server » Performance Tuning » What is Bucket/Chunk and how to check the fragment on shared pool
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
Previous Topic: Regarding cost and Card
Next Topic: LONG RAW performance problem
Goto Forum:
  


Current Time: Thu Feb 29 10:23:06 CST 2024