有没有简单的办法查看share pool的碎片

ccshu

我们的数据库似乎有share pool不够的现象，但是实际已经分配了20G，按理该够了，怀疑有碎片？有没有简单，对系统开销小一些的办法来确定这一点呢？谢谢！

Maclean Liu(刘相兵

referece ORA-4031 Common Analysis/Diagnostic Scripts
http://www.oracledatabase12g.com ... nostic-scripts.html

1. set pages 1000 lines 120
   
2. col name for a60
   
3. col value for a30
   
4. 
   
5. ttitle off
   
6. set heading on
   
7. set feedback on
   
8. 
   
9. 
   
10. SELECT KSMCHCLS CLASS, COUNT(KSMCHCLS) NUM, SUM(KSMCHSIZ) SIZ,
    
11. To_char( ((SUM(KSMCHSIZ)/COUNT(KSMCHCLS)/1024)),'999,999.00')||'k' "AVG SIZE"
    
12. FROM X$KSMSP GROUP BY KSMCHCLS;
    
13. 
    
14. 
    
15. select '0 (<140)' BUCKET,
    
16.        KSMCHCLS,
    
17.        KSMCHIDX,
    
18.        10 * trunc(KSMCHSIZ / 10) "From",
    
19.        count(*) "Count",
    
20.        max(KSMCHSIZ) "Biggest",
    
21.        trunc(avg(KSMCHSIZ)) "AvgSize",
    
22.        trunc(sum(KSMCHSIZ)) "Total"
    
23.   from x$ksmsp
    
24. where KSMCHSIZ < 140
    
25.    and KSMCHCLS = 'free'
    
26. group by KSMCHCLS, KSMCHIDX, 10 * trunc(KSMCHSIZ / 10)
    
27. UNION ALL
    
28. select '1 (140-267)' BUCKET,
    
29.        KSMCHCLS,
    
30.        KSMCHIDX,
    
31.        20 * trunc(KSMCHSIZ / 20),
    
32.        count(*),
    
33.        max(KSMCHSIZ),
    
34.        trunc(avg(KSMCHSIZ)) "AvgSize",
    
35.        trunc(sum(KSMCHSIZ)) "Total"
    
36.   from x$ksmsp
    
37. where KSMCHSIZ between 140 and 267
    
38.    and KSMCHCLS = 'free'
    
39. group by KSMCHCLS, KSMCHIDX, 20 * trunc(KSMCHSIZ / 20)
    
40. UNION ALL
    
41. select '2 (268-523)' BUCKET,
    
42.        KSMCHCLS,
    
43.        KSMCHIDX,
    
44.        50 * trunc(KSMCHSIZ / 50),
    
45.        count(*),
    
46.        max(KSMCHSIZ),
    
47.        trunc(avg(KSMCHSIZ)) "AvgSize",
    
48.        trunc(sum(KSMCHSIZ)) "Total"
    
49.   from x$ksmsp
    
50. where KSMCHSIZ between 268 and 523
    
51.    and KSMCHCLS = 'free'
    
52. group by KSMCHCLS, KSMCHIDX, 50 * trunc(KSMCHSIZ / 50)
    
53. UNION ALL
    
54. select '3-5 (524-4107)' BUCKET,
    
55.        KSMCHCLS,
    
56.        KSMCHIDX,
    
57.        500 * trunc(KSMCHSIZ / 500),
    
58.        count(*),
    
59.        max(KSMCHSIZ),
    
60.        trunc(avg(KSMCHSIZ)) "AvgSize",
    
61.        trunc(sum(KSMCHSIZ)) "Total"
    
62.   from x$ksmsp
    
63. where KSMCHSIZ between 524 and 4107
    
64.    and KSMCHCLS = 'free'
    
65. group by KSMCHCLS, KSMCHIDX, 500 * trunc(KSMCHSIZ / 500)
    
66. UNION ALL
    
67. select '6+ (4108+)' BUCKET,
    
68.        KSMCHCLS,
    
69.        KSMCHIDX,
    
70.        1000 * trunc(KSMCHSIZ / 1000),
    
71.        count(*),
    
72.        max(KSMCHSIZ),
    
73.        trunc(avg(KSMCHSIZ)) "AvgSize",
    
74.        trunc(sum(KSMCHSIZ)) "Total"
    
75.   from x$ksmsp
    
76. where KSMCHSIZ >= 4108
    
77.    and KSMCHCLS = 'free'
    
78. group by KSMCHCLS, KSMCHIDX, 1000 * trunc(KSMCHSIZ / 1000)
    
79. /
    
80. 
    
81. SELECT alloc_type, alloc_size, num_objs_flushed, object_loaded
    
82.   FROM (SELECT ksmlrcom alloc_type,
    
83.                ksmlrsiz alloc_size,
    
84.                ksmlrnum num_objs_flushed,
    
85.                ksmlrhon object_loaded,
    
86.                RANK() OVER(ORDER BY ksmlrsiz DESC) AS order_ranking
    
87.           FROM x$ksmlru
    
88.          WHERE inst_id = USERENV('INSTANCE')
    
89.            AND ksmlrsiz > 0)
    
90. WHERE order_ranking <= 100
    
91. ORDER BY order_ranking
    
92. /

复制代码

Maclean Liu(刘相兵

Sample output:

SQL> SELECT KSMCHCLS CLASS, COUNT(KSMCHCLS) NUM, SUM(KSMCHSIZ) SIZ,
To_char( ((SUM(KSMCHSIZ)/COUNT(KSMCHCLS)/1024)),'999,999.00')||'k' "AVG SIZE"
  2  FROM X$KSMSP GROUP BY KSMCHCLS;
  3  

CLASS           NUM        SIZ AVG SIZE
-------- ---------- ---------- ------------
freeabl       30511   91174848        2.92k
recr          41740   73426352        1.72k
R-freea         158       7584         .05k
perm             62  105311592    1,658.76k
R-free           79   16674496      206.12k
R-perm            7   28010320    3,907.69k
free           1619   12760680        7.70k
R-recr            1    3977192    3,883.98k

BUCKET         KSMCHCLS   KSMCHIDX       From      Count    Biggest    AvgSize      Total
-------------- -------- ---------- ---------- ---------- ---------- ---------- ----------
0 (<140)       free              1         50         72         56         56       4032
0 (<140)       free              1        110         62        112        112       6944
0 (<140)       free              1         70        116         72         72       8352
0 (<140)       free              1         80        142         88         84      11936
0 (<140)       free              1         40        229         48         42       9792
0 (<140)       free              1        120        126        128        124      15664
0 (<140)       free              1         60         76         64         64       4864
0 (<140)       free              1         90         81         96         96       7776
0 (<140)       free              1        130         72        136        136       9792
0 (<140)       free              1        100        116        104        104      12064
1 (140-267)    free              1        140        127        152        147      18720
1 (140-267)    free              1        200          1        208        208        208
1 (140-267)    free              1        160         62        160        160       9920
2 (268-523)    free              1        350         23        376        376       8648
3-5 (524-4107) free              1       1000          1       1112       1112       1112
          FROM x$ksmlru
6+ (4108+)     free              1      48000         14      48936      48572     680008
6+ (4108+)     free              1      40000         14      40976      40595     568336
6+ (4108+)     free              1      63000          5      63848      63395     316976
6+ (4108+)     free              1      54000          9      54968      54502     490520
6+ (4108+)     free              1      52000          6      52912      52482     314896
6+ (4108+)     free              1      85000          3      85824      85506     256520
6+ (4108+)     free              1     220000          1     220416     220416     220416
6+ (4108+)     free              1      99000          6      99968      99714     598288
6+ (4108+)     free              1     373000          1     373192     373192     373192
6+ (4108+)     free              1     104000          2     104264     104204     208408

若 发现存在大量 小的 free bucket 则说明 共享池碎片多。