溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
解決方案二：在emp2的empno列上面創建索引，再執行share_pool_sql_1.sh腳本，查看sp報告
8.1在emp2的empno列上創建索引
sys@TESTDB12>create index ind_empno on scott.emp2(empno);
8.2 重新執行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報告對比各個時間段的數據緩沖區的命中率和庫緩沖區的命中率：
時間 | 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列上創建索引后通過對比發現數據緩沖區的命中率明顯得到了改善，達到了的99%以上；而庫緩沖區的命中率也得到小幅度提升
8.5查看Top 5 Timed Events找出4 個報告中各個時間段跟磁盤I/O相關的等待事件
時間 | 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，沒有什么大的耗資源的任務，說明系統性能得以提升
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語句，發現仍有相同或相似的執行計劃，應該使用綁定變量，來提高執行效率。
生成語句的執行計劃: 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的大小設置為推薦的大小
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，發現產生的硬解析明顯增加了。
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 Gets -------------------------- --------------- ------------ ----------- ----------- shared pool 4,107,841 5 5 0 JS Sh mem access 3 1 1 0 enqueue hash chains 320,877 1 1 0 |
05:49:00 ~ 06:04:05時間段的Latch Sleep breakdown |
Latch Name Requests Misses Sleeps Gets -------------------------- --------------- ------------ ----------- ----------- shared pool 4,257,852 8 8 0 row cache objects 3,956,966 3 3 0 call allocation 110,566 1 1 0 redo allocation 99,927 1 1 0 |
06:04:05 ~06:13:00時間段的Latch Sleep breakdown |
Latch Name Requests Misses Sleeps Gets -------------------------- --------------- ------------ ----------- ----------- shared pool 2,595,386 6 6 0 row cache objects 2,500,734 1 1 0 |
通過以上4個sp報告各個時間段的Latch Sleepbreakdown的內容，發現cache bufferslru chain已經沒有了，但是shared pool次數上來了。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
