部署statspack工具（二）之解決方案2
解決方案二：在emp2的empno列上面創(chuàng)建索引，再執(zhí)行share_pool_sql_1.sh腳本，查看sp報告
在常州等地區(qū)，都構(gòu)建了全面的區(qū)域性戰(zhàn)略布局，加強(qiáng)發(fā)展的系統(tǒng)性、市場前瞻性、產(chǎn)品創(chuàng)新能力，以專注、極致的服務(wù)理念，為客戶提供成都網(wǎng)站設(shè)計、成都網(wǎng)站制作 網(wǎng)站設(shè)計制作按需網(wǎng)站設(shè)計,公司網(wǎng)站建設(shè),企業(yè)網(wǎng)站建設(shè),品牌網(wǎng)站制作,成都全網(wǎng)營銷推廣,外貿(mào)網(wǎng)站制作,常州網(wǎng)站建設(shè)費(fèi)用合理。
8.1在emp2的empno列上創(chuàng)建索引
sys@TESTDB12>create index ind_empno on scott.emp2(empno);
8.2重新執(zhí)行share_pool_sql_1.sh腳本并重新開啟statspack自動快照
{oracle@Redhat55.cuug.net:/home/oracle/script/bin}$sh share_pool_sql_1.sh
SQL>@?/rdbms/admin/spauto
8.3生成statspack報告
perfstat@TESTDB12>selectsnap_id,snap_time,snap_level from stats$snapshot order by snap_time;
perfstat@TESTDB12>selectsnap_id,snap_time,snap_level from stats$snapshot order by snap_time;
SNAP_ID SNAP_TIME SNAP_LEVEL
---------- -------------------
1 28-JUL-14 7
11 28-JUL-14 7
21 28-JUL-14 7
31 28-JUL-14 7
41 29-JUL-14 7
51 29-JUL-14 7
61 29-JUL-14 7
71 29-JUL-14 7
81 29-JUL-14 7
91 29-JUL-14 7
101 29-JUL-14 7
111 29-JUL-14 7
121 29-JUL-14 7
131 29-JUL-14 7
141 29-JUL-14 7
151 29-JUL-14 7
161 29-JUL-14 7
171 29-JUL-14 7
181 29-JUL-14 7
191 29-JUL-14 7
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap:131
Enter value for end_snap:141
Enter value for report_name:
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap:141
Enter value for end_snap:151
Enter value for report_name:
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap:151
Enter value for end_snap:161
Enter value for report_name:
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap:161
Enter value for end_snap:171
Enter value for report_name:
8.4通過新生成的4個statspack報告對比各個時間段的數(shù)據(jù)緩沖區(qū)的命中率和庫緩沖區(qū)的命中率：
時間 | Buffer Hit（%） | Library Hit（%） |
05:19:01~05:34:01 | 99.99 | 89.78 |
05:34:01 ~05:49:00 | 99.99 | 89.72 |
05:49:00~06:04:05 | 99.98 | 89.45 |
06:04:05~06:13:00 | 99.95 | 88.79 |
在emp2的empno列上創(chuàng)建索引后通過對比發(fā)現(xiàn)數(shù)據(jù)緩沖區(qū)的命中率明顯得到了改善，達(dá)到了的99%以上；而庫緩沖區(qū)的命中率也得到小幅度提升
8.5查看Top 5 Timed Events找出4個報告中各個時間段跟磁盤I/O相關(guān)的等待事件
時間 | name | Wait(s) | Time(s) |
05:19:01~05:34:01 | log file parallel write | 45,110 | 54 |
log file sync | 6,240 | 46 | |
os thread startup | 34 | 5 | |
control file parallel write | 332 | 3 | |
05:34:01 ~05:49:00 | log file parallel write | 48,413 | 36 |
log file sync | 3,563 | 28 | |
os thread startup | 33 | 5 | |
db file sequential read | 2,018 | 2 | |
05:49:00~06:04:05 | log file parallel write | 49,564 | 23 |
log file sync | 455 | 15 | |
db file sequential read | 3,955 | 9 | |
os thread startup | 39 | 6 | |
06:04:05~06:13:00 | log file parallel write | 28,273 | 8 |
db file sequential read | 2,928 | 5 | |
log file sync | 231 | 4 | |
os thread startup | 21 | 3 |
通過4個報告的對比Top 5 Timed Events中direct path read不見了，說明解決了全表掃描等待I\O的問題；但log file parallel write和log file sync的磁盤I/O都還比較大，而且新增了control fileparallel write I/O，沒有什么大的耗資源的任務(wù)，說明系統(tǒng)性能得以提升
8.6造成物理讀最大的前幾個sql語句在報告中未找到，用sql語句查詢得出這些語句:select sql_text from v$sqlwhere disk_reads=(select max(disk_reads) from v$sql);
時間 | Executions | Rows per Exec | Sql語句 |
05:19:01~05:34:01 | 10,840 | 16.1 | select /*+ rule */ bucket, endpoint, col#, epvalue from histgrm$ where obj#=:1 and intcol#=:2 and row#=:3 order by bucket |
05:34:01 ~05:49:00 | 12,565 | 16.1 | select /*+ rule */ bucket, endpoint, col#, epvalue from histgrm$ where obj#=:1 and intcol#=:2 and row#=:3 order by bucket |
05:49:00~06:04:05 | 15,112 | 16.0 | select /*+ rule */ bucket, endpoint, col#, epvalue from histgrm$ where obj#=:1 and intcol#=:2 and row#=:3 order by bucket |
06:04:05~06:13:00 | 20,814 | 16.4 | select /*+ rule */ bucket, endpoint, col#, epvalue from histgrm$ where obj#=:1 and intcol#=:2 and row#=:3 order by bucket |
通過對比各時間段最消耗資源的SQL語句，發(fā)現(xiàn)仍有相同或相似的執(zhí)行計劃，應(yīng)該使用綁定變量，來提高執(zhí)行效率。
生成語句的執(zhí)行計劃: set autotrace traceonly select * from scott.emp2
idle>select *from scott.emp2 where empno=1484;
Execution Plan
----------------------------------------------------------
Plan hash value:2918945472
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 48 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP2 | 1 | 48 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN |IND_EMPNO | 1 | | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
PredicateInformation (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=1484)
Statistics
----------------------------------------------------------
55 recursive calls
0 db block gets
78 consistent gets
4 physical reads
0 redo size
1033 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
7 sorts (memory)
0 sorts (disk)
1 rows processed
8.7查看Buffer Pool Advisory并把Buffer cache的大小設(shè)置為推薦的大小
05:19:01~05:34:01時間段的Buffer PoolAdvisory
Est
Phys Estimated Est
Size for Size Buffers Read Phys Reads Est Phys % dbtime
P Est (M) Factr (thousands) Factr (thousands) Read Time for Rds
--- -------- ----------------- ------ -------------- ------------ --------
D 4 .1 0 8.0 261 345 5.2
D 8 .2 1 1.1 35 37 .6
D 12 .3 1 1.1 34 36 .5
D 16 .3 2 1.0 33 35 .5
D 20 .4 2 1.0 33 34 .5
D 24 .5 3 1.0 33 34 .5
D 28 .6 3 1.0 33 34 .5
D 32 .7 4 1.0 33 33 .5
D 36 .8 4 1.0 33 33 .5
D 40 .8 5 1.0 32 33 .5
D 44 .9 5 1.0 32 33 .5
D 48 1.0 6 1.0 32 33 .5
D 52 1.1 6 1.0 32 33 .5
D 56 1.2 7 1.0 32 33 .5
D 60 1.3 7 1.0 32 33 .5
D 64 1.3 8 1.0 32 33 .5
D 68 1.4 8 1.0 32 33 .5
D 72 1.5 9 1.0 32 33 .5
D 76 1.6 9 1.0 32 33 .5
D 80 1.7 10 1.0 32 33 .5
05:34:01 ~05:49:00時間段的Buffer PoolAdvisory
Est
Phys Estimated Est
Size for Size Buffers Read Phys Reads Est Phys % dbtime
P Est (M) Factr (thousands) Factr (thousands) Read Time for Rds
--- -------- ----------------- ------ -------------- ------------ --------
D 4 .1 0 7.8 273 357 5.1
D 8 .2 1 1.1 37 39 .6
D 12 .3 1 1.0 37 38 .5
D 16 .3 2 1.0 36 37 .5
D 20 .4 2 1.0 35 37 .5
D 24 .5 3 1.0 35 36 .5
D 28 .6 3 1.0 35 36 .5
D 32 .7 4 1.0 35 36 .5
D 36 .8 4 1.0 35 36 .5
D 40 .8 5 1.0 35 36 .5
D 44 .9 5 1.0 35 36 .5
D 48 1.0 6 1.0 35 36 .5
D 52 1.1 6 1.0 35 36 .5
D 56 1.2 7 1.0 35 36 .5
D 60 1.3 7 1.0 35 36 .5
D 64 1.3 8 1.0 35 36 .5
D 68 1.4 8 1.0 35 36 .5
D 72 1.5 9 1.0 35 36 .5
D 76 1.6 9 1.0 35 36 .5
D 80 1.7 10 1.0 35 36 .5
05:49:00~06:04:05時間段的Buffer PoolAdvisory
Est
Phys Estimated Est
Size for Size Buffers Read Phys Reads Est Phys % dbtime
P Est (M) Factr (thousands) Factr (thousands) Read Time for Rds
--- -------- ----------------- ------ -------------- ------------ --------
D 4 .1 0 7.6 302 438 6.0
D 8 .2 1 1.1 42 49 .7
D 12 .3 1 1.0 41 48 .7
D 16 .3 2 1.0 40 47 .6
D 20 .4 2 1.0 40 46 .6
D 24 .5 3 1.0 40 46 .6
D 28 .6 3 1.0 40 46 .6
D 32 .7 4 1.0 40 46 .6
D 36 .8 4 1.0 40 46 .6
D 40 .8 5 1.0 40 46 .6
D 44 .9 5 1.0 40 46 .6
D 48 1.0 6 1.0 40 46 .6
D 52 1.1 6 1.0 40 46 .6
D 56 1.2 7 1.0 40 46 .6
D 60 1.3 7 1.0 40 46 .6
D 64 1.3 8 1.0 40 46 .6
D 68 1.4 8 1.0 40 46 .6
D 72 1.5 9 1.0 40 46 .6
D 76 1.6 9 1.0 40 46 .6
D 80 1.7 10 1.0 40 46 .6
06:04:05~06:13:00時間段的Buffer PoolAdvisory
Est
Phys Estimated Est
Size for Size Buffers Read Phys Reads Est Phys % dbtime
P Est (M) Factr (thousands) Factr (thousands) Read Time for Rds
--- -------- ----------------- ------ -------------- ------------ --------
D 4 .1 0 7.6 338 497 6.6
D 8 .2 1 1.0 47 56 .7
D 12 .3 1 1.0 46 55 .7
D 16 .3 2 1.0 45 54 .7
D 20 .4 2 1.0 45 54 .7
D 24 .5 3 1.0 45 54 .7
D 28 .6 3 1.0 45 53 .7
D 32 .7 4 1.0 45 53 .7
D 36 .8 4 1.0 45 53 .7
D 40 .8 5 1.0 45 53 .7
D 44 .9 5 1.0 45 53 .7
D 48 1.0 6 1.0 45 53 .7
D 52 1.1 6 1.0 45 53 .7
D 56 1.2 7 1.0 45 53 .7
D 60 1.3 7 1.0 45 53 .7
D 64 1.3 8 1.0 45 53 .7
D 68 1.4 8 1.0 45 53 .7
D 72 1.5 9 1.0 45 53 .7
D 76 1.6 9 1.0 45 53 .7
D 80 1.7 10 1.0 45 53 .7
通過以上4個時間段中Buffer Pool Advisory建議可以看的出來，對于增加Buffer cache的大小對性能的影響并不明顯。
8.8查看Time Model System Stats
05:19:01~ 05:34:01時間段Time Model System Stats |
Statistic Time (s) % DB time ----------------------------------- -------------------- --------- DB CPU 440.5 119.9 parse time elapsed 158.5 43.1 sql execute elapsed time 145.1 39.5 hard parse elapsed time 135.0 36.8 connection management call elapsed 108.8 29.6 PL/SQL execution elapsed time 5.7 1.6 hard parse (sharing criteria) elaps 1.3 .3 hard parse (bind mismatch) elapsed 1.2 .3 PL/SQL compilation elapsed time 0.8 .2 repeated bind elapsed time 0.4 .1 sequence load elapsed time 0.1 .0 DB time 367.4 background elapsed time 75.1 background cpu time 20.1 05:34:01 ~05:49:00時間段Time Model System Stats |
Statistic Time (s) % DB time ----------------------------------- -------------------- --------- DB CPU 455.9 124.3 parse time elapsed 155.5 42.4 sql execute elapsed time 149.9 40.9 hard parse elapsed time 128.2 35.0 connection management call elapsed 104.6 28.5 PL/SQL execution elapsed time 6.8 1.9 hard parse (sharing criteria) elaps 2.5 .7 hard parse (bind mismatch) elapsed 2.4 .7 PL/SQL compilation elapsed time 0.8 .2 repeated bind elapsed time 0.5 .1 sequence load elapsed time 0.3 .1 DB time 366.8 background elapsed time 54.4 background cpu time 20.1 05:49:00~ 06:04:05時間段Time Model System Stats |
Statistic Time (s) % DB time ----------------------------------- -------------------- --------- DB CPU 463.3 122.2 parse time elapsed 160.9 42.4 sql execute elapsed time 158.6 41.9 hard parse elapsed time 133.8 35.3 connection management call elapsed 103.6 27.3 PL/SQL execution elapsed time 7.3 1.9 hard parse (sharing criteria) elaps 2.1 .6 hard parse (bind mismatch) elapsed 1.9 .5 PL/SQL compilation elapsed time 1.1 .3 repeated bind elapsed time 0.5 .1 sequence load elapsed time 0.2 .0 DB time 379.0 background elapsed time 52.7 background cpu time 23.0 06:04:05~06:13:00時間段Time Model System Stats |
Statistic Time (s) % DB time ----------------------------------- -------------------- --------- DB CPU 269.2 119.5 parse time elapsed 105.7 46.9 sql execute elapsed time 102.9 45.6 hard parse elapsed time 89.9 39.9 connection management call elapsed 58.2 25.8 PL/SQL execution elapsed time 4.0 1.8 hard parse (sharing criteria) elaps 2.0 .9 hard parse (bind mismatch) elapsed 1.6 .7 PL/SQL compilation elapsed time 1.1 .5 repeated bind elapsed time 0.6 .3 sequence load elapsed time 0.1 .1 DB time 225.4 background elapsed time 19.6 background cpu time 12.2 |
通過對比4個報告各個時間段中的Time Model System Stats，發(fā)現(xiàn)產(chǎn)生的硬解析明顯增加了。
8.9查看Latch Sleep breakdown
05:19:01~ 05:34:01時間段的Latch Sleep breakdown |
Latch Name Requests Misses Sleeps Gets -------------------------- --------------- ------------ ----------- ----------- shared pool 3,787,761 4 4 0 |
05:34:01 ~05:49:00時間段的Latch Sleep breakdown |
Latch Name Requests Misses Sleeps &n
本文名稱：部署statspack工具（二）之解決方案2
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供微信公眾號、軟件開發(fā)、網(wǎng)站建設(shè)、面包屑導(dǎo)航、服務(wù)器托管、 聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián) 猜你還喜歡下面的內(nèi)容 
|