1. 禁用sga自动管理机制，分配比较小的数据缓冲区(30m)和共享池(70m)空间
1.1关闭SGA自动管理机制
查看是否开启了ASSM
关闭sga自动管理机制：
1.2设置数据缓冲区的大小为30m
1.3 设置共享池的大小为70m
1.4 验证设置好的数据缓冲区和共享池的大小
2.部署statspack
2.1创建一个专门用于statspack的表空间tools
SQL> create tablespace tools
2 datafile'/u01/app/oracle/oradata/PROD/disk4/tools01.dbf'
3 size 300m;
注：红色加粗部分为修改后的内容
2.2以sysdba身份执行创建prefstat对象的脚本
SQL> @?/rdbms/admin/spcreate.sql
Choose the PERFSTAT user's password
-----------------------------------
Not specifying a password will result in the installation FAILING
Enter value forperfstat_password: oracle
Choose the Default tablespace for the PERFSTAT user
---------------------------------------------------
Below is the list of online tablespaces in this database which can
store user data. Specifyingthe SYSTEM tablespace for the user's
default tablespace will result in the installation FAILING, as
using SYSTEM for performance data is not supported.
Choose the PERFSTAT users's default tablespace. This is the tablespace
in which the STATSPACK tables and indexes will be created.
TABLESPACE_NAME CONTENTS STATSPACK DEFAULTTABLESPACE
------------------------------ -------------------------------------
EXAMPLE PERMANENT
SYSAUX PERMANENT *
TOOLS PERMANENT
USERS PERMANENT
Pressing <return> will result in STATSPACK's recommendeddefault
tablespace (identified by *) being used.
Enter value for default_tablespace: tools
Using tablespace TOOLS as PERFSTAT default tablespace.
Choose the Temporary tablespace for the PERFSTAT user
-----------------------------------------------------
Below is the list of online tablespaces in this database which can
store temporary data (e.g. for sort workareas). Specifying the SYSTEM
tablespace for the user's temporary tablespace will result in the
installation FAILING, as using SYSTEM for workareas is notsupported.
Choose the PERFSTAT user's Temporary tablespace.
TABLESPACE_NAME CONTENTS DB DEFAULT TEMPTABLESPACE
------------------------------ --------- --------------------------
TEMP TEMPORARY *
Pressing <return> will result in the database's defaultTemporary
tablespace (identified by *) being used.
Enter value fortemporary_tablespace:回车
… …
NOTE:
SPCPKG complete. Please check spcpkg.lis for any errors.
Statstack安装完成。
2.3设置statspack自动产生快照的间隔时间为15分钟
[oracle@localhost admin]$ vi /u01/app/oracle/product/10.2.0/db_1/rdbms/admin/spauto.sql
variable jobno number;
variable instno number;
begin
select instance_number into :instno from v$instance;
dbms_job.submit(:jobno,'statspack.snap;', trunc(sysdate+1/96,'MI'), 'trunc(SYSDATE+1/96,''MI'')', TRUE, :instno);
commit;
end;
2.4设置快照的默认级别为7级
SQL> SQL> exec statspack.modify_statspack_parameter(i_snap_level=>7);
创建序列
SQL> conn scott/tiger
CREATE SEQUENCE emp2_empno
INCREMENT BY 1
START WITH 1
MAXVALUE 100000000
CACHE 10000
NOCYCLE;
3搭建查询环境
3.1创建新表并插入数据
SQL> create table emp2 as select * fromemp where 1=2;
SQL> alter table emp2 modify empnonumber(10);
SQL> alter table emp2 modify enamevarchar(30);
将emp2表设为nologging
SQL> alter table emp2 nologging;
插入2千万行数据：
begin
for iin 1..20000000 loop
insert into emp2
values(emp2_empno.nextval,'cuug'||i,'SALESMAN',7698,sysdate,1600,300,30);
ifmod(i,1000)=0 then
commit;
endif;
endloop;
commit;
end;
/
3.2编写查询业务脚本
[oracle@gc1 ~]$ viscript/bin/share_pool_sql_1.sh
#!/bin/bash
CNT=1
while [ $CNT -lt 20000000 ]
do
sqlplus scott/tiger <<EOF
select * from emp2 where empno=$CNT;
exit
EOF
CNT=`expr $CNT + 1`
done
4.运行查询业务脚本并产生statspack报告
4.1运行查询业务脚本并启动statspack的自动快照
[oracle@gc1 ~]$ shscript/bin/share_pool_sql_1.sh
SQL> conn perfstat/oracle
@?/rdbms/admin/spauto
4.2验证statspack自动生成的报告
SQL>alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
SQL> select snap_id,snap_time,snap_levelfrom stats$snapshot order bysnap_time;
SNAP_ID SNAP_TIME SNAP_LEVEL
---------- ------------------- ----------
105 2013-11-11 15:52:02 7
101 2013-11-11 14:52:05 7
102 2013-11-11 15:07:02 7
103 2013-11-11 15:22:04 7
104 2013-11-11 15:37:05 7
生成statspack分析报告
SQL> @?/rdbms/admin/spreport
Instance DB Name Snap Id Snap Started Level Comment
------------ ------------ -------------------------- ----- --------------------
PROD PROD 1 09 Oct 2014 10:49 7
11 09 Oct 201411:04 7
12 09 Oct 201411:19 7
21 09 Oct 201411:34 7
22 09 Oct 201411:49 7
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value forbegin_snap:1
Enter value forend_snap: 11
Enter value for report_name:
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value forbegin_snap: 11
Enter value forend_snap: 12
Enter value for report_name:
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value forbegin_snap: 12
Enter value for end_snap:21
Enter value for report_name:
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value forbegin_snap: 21
Enter value forend_snap: 22
Enter value for report_name:
4.3取消statspack自动生成快照
SQL> select job,log_user,last_date,next_datefrom user_jobs;
SQL> exec dbms_job.remove('21');
5.启samba共享并对生成的statspack报告copy到windows主机
[root@localhost ~]# service smb start //启动samba服务
[root@localhost ~]# chkconfig smb on //开机自动启动
6.分析statspack报告查出瓶颈并尝试解决
6.0 Load Profile
| 时间 | Logical reads(Per Second) | Physical reads(Per Second) |
| 10:49:06~ 11:04:01 | 112,904.06 | 113,218.05 |
| 11:04:01 ~ 11:19:02 | 113,385.81 | 113,799.50 |
| 11:19:02~ 11:34:03 | 112,699.07 | 113,030.69 |
| 11:34:03~ 11:49:03 | 111,080.63 | 111,504.01 |
通过对比发现逻辑读的block数量每秒达到110万以上；物理读的每秒block数量也达到110万以上。
6.1通过生成的4个statspack报告对比各个时间段的数据缓冲区的命中率和库缓冲区的命中率：
| 时间 | Buffer Hit（%） | Library Hit（%） |
| 10:49:06~ 11:04:01 | -0.28 | 89.21 |
| 11:04:01 ~ 11:19:02 | -0.36 | 91.44 |
| 11:19:02~ 11:34:03 | -0.29 | 91.01 |
| 11:34:03~ 11:49:03 | -0.38 | 91.24 |
通过对比发现数据缓冲区严重低于oracle标准的90%以上的要求；库缓冲区的命中率低于oracle标准的库缓冲区命中率95%。
6.2查看Top 5 Timed Events前5个等待事件
| 时间 | name | waits | Time (s) |
| 10:49:06~ 11:04:01
| db file scattered read | 936,112 | 234 |
| db file sequential read | 32,718 | 6 | |
| log file parallel write | 119 | 1 | |
| log file sync | 13 | 0 | |
| CPU time |
| 874 | |
| 11:04:01 ~11:19:02
| db file scattered read | 946,922 | 236 |
| db file sequential read | 31,610 | 1 | |
| log file parallel write | 129 | 1 | |
| log file sync | 10 | 0 | |
| CPU time |
| 877 | |
| 11:19:02~ 11:34:03
| db file scattered read | 940,908 | 236 |
| db file sequential read | 31,963 | 1 | |
| log file parallel write | 86 | 1 | |
| log file sync | 11 | 0 | |
| CPU time |
| 871 | |
| 11:34:03~ 11:49:03 | db file scattered read | 926,749 | 237 |
| db file sequential read | 30,860 | 1 | |
| log file parallel write | 96 | 1 | |
| log file sync | 15 | 0 | |
| CPU time |
| 860 |
通过4个报告的对比Top 5 Timed Events中db file scattered read、db file sequentialread的磁盘I/O都比较大。
6.3造成物理读最大的前几个sql语句在报告中未找到，用sql语句查询得出这些语句:select sql_text from v$sqlwhere disk_reads=(select max(disk_reads) from v$sql);
SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSENO_PARALLEL(SAMPLES
UB) opt_param('parallel_execution_enabled','false') NO_PARALLEL_INDEX(SAMPLES
UB) NO_SQL_TUNE */NVL(SUM(C1),:"SYS_B_0"), NVL(SUM(C2),:"SYS_B_1") FROM (SELE
CT /*+ IGNORE_WHERE_CLAUSENO_PARALLEL("EMP2") FULL("EMP2") NO_PARALLEL_INDEX(
"EMP2") */ :"SYS_B_2"AS C1, CASE WHEN "EMP2"."EMPNO"=:"SYS_B_3" THEN:"SYS_B_
4" ELSE :"SYS_B_5" END AS C2FROM "EMP2" SAMPLE BLOCK (:"SYS_B_6" , :"SYS_B_7"
) SEED (:"SYS_B_8")"EMP2") SAMPLESUB
生成语句的执行计划:
6.4查看Buffer Pool Advisory并把Buffer cache的大小设置为推荐的大小
10:49:06~ 11:04:01时间段的Buffer Pool Advisory
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 1.0 202,358 489 15.3
D 8 .3 1 1.0 202,353 488 15.2
D 12 .4 1 1.0 202,352 488 15.2
D 16 .5 2 1.0 202,352 488 15.2
D 20 .6 2 1.0 202,351 488 15.2
D 24 .8 3 1.0 202,350 488 15.2
D 28 .9 3 1.0 202,350 488 15.2
D 32 1.0 4 1.0 202,350 488 15.2
D 36 1.1 4 1.0 202,343 486 15.2
D 40 1.3 5 1.0 200,234 475 14.8
D 44 1.4 5 1.0 200,230 475 14.8
D 48 1.5 6 1.0 200,133 475 14.8
D 52 1.6 6 1.0 200,130 475 14.8
D 56 1.8 7 1.0 199,540 475 14.8
D 60 1.9 7 1.0 199,425 475 14.8
D 64 2.0 8 1.0 199,424 475 14.8
第二个报告的Buffer Pool Advisory:
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 1.0 304,892 726 17.9
D 8 .3 1 1.0 304,887 725 17.8
D 12 .4 1 1.0 304,886 725 17.8
D 16 .5 2 1.0 304,885 725 17.8
D 20 .6 2 1.0 304,885 725 17.8
D 24 .8 3 1.0 304,884 725 17.8
D 28 .9 3 1.0 304,884 725 17.8
D 32 1.0 4 1.0 304,883 725 17.8
D 36 1.1 4 1.0 304,876 724 17.8
D 40 1.3 5 1.0 302,765 711 17.5
D 44 1.4 5 1.0 302,762 711 17.5
D 48 1.5 6 1.0 302,665 711 17.5
D 52 1.6 6 1.0 302,662 711 17.5
D 56 1.8 7 1.0 302,071 711 17.5
D 60 1.9 7 1.0 301,536 711 17.5
D 64 2.0 8 1.0 301,534 711 17.5
第三个报告的Buffer Pool Advisory:
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 1.0 406,737 963 19.6
D 8 .3 1 1.0 406,731 962 19.5
D 12 .4 1 1.0 406,730 962 19.5
D 16 .5 2 1.0 406,729 962 19.5
D 20 .6 2 1.0 406,729 962 19.5
D 24 .8 3 1.0 406,728 962 19.5
D 28 .9 3 1.0 406,727 962 19.5
D 32 1.0 4 1.0 406,727 962 19.5
D 36 1.1 4 1.0 406,720 961 19.5
D 40 1.3 5 1.0 404,609 947 19.2
D 44 1.4 5 1.0 404,605 947 19.2
D 48 1.5 6 1.0 404,508 947 19.2
D 52 1.6 6 1.0 404,505 947 19.2
D 56 1.8 7 1.0 403,914 947 19.2
D 60 1.9 7 1.0 402,981 947 19.2
D 64 2.0 8 1.0 402,980 947 19.2
第四个报告的Buffer Pool Advisory:
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 1.0 507,088 1,201 20.8
D 8 .3 1 1.0 507,081 1,200 20.7
D 12 .4 1 1.0 507,080 1,200 20.7
D 16 .5 2 1.0 507,080 1,200 20.7
D 20 .6 2 1.0 507,079 1,200 20.7
D 24 .8 3 1.0 507,078 1,200 20.7
D 28 .9 3 1.0 507,078 1,200 20.7
D 32 1.0 4 1.0 507,077 1,200 20.7
D 36 1.1 4 1.0 507,070 1,199 20.7
D 40 1.3 5 1.0 504,958 1,184 20.5
D 44 1.4 5 1.0 504,955 1,184 20.5
D 48 1.5 6 1.0 504,858 1,184 20.5
D 52 1.6 6 1.0 504,855 1,184 20.5
D 56 1.8 7 1.0 504,264 1,184 20.5
D 60 1.9 7 1.0 503,331 1,184 20.5
D 64 2.0 8 1.0 503,038 1,184 20.5
通过以上4个时间段中Buffer Pool Advisory建议可以看的出来，….：
6.5查看Time Model System Stats
10:49:06~11:04:01时间段Time Model SystemStats
Statistic Time (s)% of DB time
------------------------------------------------------- ------------
sqlexecute elapsed time 858.9 99.7
DBCPU 852.7 98.9
parsetime elapsed 23.2 2.7
hardparse elapsed time 22.4 2.6
connectionmanagement call elapsed 1.6 .2
PL/SQLexecution elapsed time 0.2 .0
sequenceload elapsed time 0.0 .0
repeatedbind elapsed time 0.0 .0
PL/SQLcompilation elapsed time 0.0 .0
DBtime 861.9
backgroundelapsed time 9.3
backgroundcpu time 3.3
11:04:01 ~11:19:02时间段Time Model SystemStats
Statistic Time (s)% of DB time
------------------------------------------------------- ------------
DB CPU 858.4 99.8
sql execute elapsed time 858.2 99.7
parse time elapsed 21.6 2.5
hard parse elapsed time 20.8 2.4
connection management call elapsed 1.7 .2
PL/SQL execution elapsed time 0.2 .0
sequence load elapsed time 0.0 .0
repeated bind elapsed time 0.0 .0
PL/SQL compilation elapsed time 0.0 .0
DB time 860.5
background elapsed time 2.2
background cpu time 1.3
11:19:02~11:34:03时间段Time Model SystemStats
Statistic Time (s)% of DB time
------------------------------------------------------- ------------
sql execute elapsedtime 858.7 99.7
DB CPU 858.4 99.6
parse timeelapsed 20.4 2.4
hard parse elapsed time 19.7 2.3
connectionmanagement call elapsed 1.8 .2
PL/SQL executionelapsed time 0.2 .0
repeated bindelapsed time 0.1 .0
sequence loadelapsed time 0.1 .0
DB time 861.4
background elapsedtime 2.0
background cputime 1.5
11:34:03~11:49:03时间段Time Model SystemStats
Statistic Time (s) %of DB time
------------------------------------------------------- ------------
sql execute elapsedtime 858.4 99.6
DB CPU 857.3 99.5
parse timeelapsed 21.3 2.5
hard parse elapsedtime 20.6 2.4
connectionmanagement call elapsed 2.0 .2
PL/SQL executionelapsed time 0.1 .0
sequence loadelapsed time 0.1 .0
repeated bindelapsed time 0.0 .0
DB time 861.6
background elapsed time 2.0
background cpu time 1.4
通过对比4个报告各个时间段中的Time Model System Stats，发现产生的硬解析比较少。
6.6查看Latch Sleep breakdown
10:49:06~11:04:01时间段的LatchSleep breakdown
Get Spin
Latch Name Requests Misses Sleeps Gets
-------------------------- --------------------------- ----------- -----------
cache buffers lru chain 101,323,852 61 61 0
simulator lru latch 12,615,506 6 6 0
object queue header operat 202,642,681 5 5 0
shared pool 100,122 1 1 0
cache buffers chains 303,385,835 1 1 0
multiblock read objects 1,873,052 1 1 0
11:04:01 ~11:19:02时间段的LatchSleep breakdown
|
Get Spin Latch Name Requests Misses Sleeps Gets -------------------------- --------------- ------------ ----------- ----------- cache buffers lru chain 102,534,571 5 5 0 object queue header operat 205,068,330 2 2 0 redo allocation 846 1 1 0 cache buffers chains 306,853,245 1 1 0 |
11:19:02~ 11:34:03时间段的Latch Sleep breakdown
Get Spin
Latch Name Requests Misses Sleeps Gets
-------------------------- --------------------------- ----------- -----------
object queue header operat 203,686,904 4 4 0
cache buffers lru chain 101,843,760 3 3 0
simulator lru latch 12,681,940 1 1 0
11:34:03~11:49:03时间段的LatchSleep breakdown
Get Spin
Latch Name Requests Misses Sleeps Gets
-------------------------- --------------------------- ----------- -----------
cache buffers lru chain 100,352,581 8 8 0
object queue header operat 200,704,370 3 3 0
OS process allocation 1,556 1 1 0
library cache 125,792 1 1 0
process allocation 1,252 1 1 0
通过以上4个sp报告各个时间段的LatchSleep breakdown的内容，发现cachebuffers lru chain的miss和sleep的次数并不多。
7.解决方案一：调整buffercache
SQL> alter system set db_cache_size=64m;
7.1重新生成新的statspack报告
SQL> @?/rdbms/admin/spreport
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value forbegin_snap: 35
Enter value forend_snap: 36
Enter value for report_name:
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value forbegin_snap: 36
Enter value forend_snap: 37
Enter value for report_name:
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value forbegin_snap: 37
Enter value forend_snap: 38
Enter value for report_name:
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value forbegin_snap: 38
Enter value forend_snap: 39
Enter value for report_name:
7.2 Load Profile
| 时间 | Logical reads(Per Second) | Physical reads(Per Second) |
| 10:49:06~ 11:04:01 | 112,904.06 | 113,218.05 |
| 11:04:01 ~ 11:19:02 | 113,385.81 | 113,799.50 |
| 11:19:02~ 11:34:03 | 112,699.07 | 113,030.69 |
| 11:34:03~ 11:49:03 | 111,080.63 | 111,504.01 |
通过对比发现逻辑读的block数量每秒达到110万以上；物理读的每秒block数量也达到110万以上。
7.3通过新生成的4个statspack报告对比各个时间段的数据缓冲区的命中率和库缓冲区的命中率：
| 时间 | Buffer Hit（%） | Library Hit（%） |
| 0.21 | 92.30 | |
| 14:16:05 ~ 14:31:06 | 0.23 | 94.38 |
| 14:31:06 ~ 14:46:01 | 0.21 | 94.13 |
| 14:46:01 ~ 15:01:02 | 0.24 | 92.33 |
通过增加buffer cache的大小并没有现buffer cache的命中率有什么大的区别。
7.4查看Top 5 Timed Events找出4 个报告中各个时间段跟磁盘I/O相关的等
| 时间 | name | waits | Time (s) |
| 14:02:00~ 14:16:05
| db file scattered read | 929,265 | 210 |
| db file sequential read | 32,135 | 1 | |
| os thread startup | 20 | 24 | |
| latch free | 2 | 21 | |
| CPU time |
| 824 | |
| 14:16:05 ~14:31:06
| db file scattered read | 993,924 | 224 |
| db file sequential read | 34,169 | 1 | |
| log file parallel write | 69 | 0 | |
| log file sync | 10 | 0 | |
| CPU time |
| 881 | |
| 14:31:06 ~14:46:01
| db file scattered read | 991,839 | 223 |
| db file sequential read | 31,963 | 1 | |
| log file parallel write | 70 | 1 | |
| log file sync | 10 | 0 | |
| CPU time |
| 877 | |
| 14:46:01 ~15:01:02 | db file scattered read | 971,261 | 226 |
| db file sequential read | 33,578 | 1 | |
| log file parallel write | 72 | 1 | |
| log file sync | 11 | 0 | |
| CPU time |
| 880 |
通过4个报告的对比Top 5 Timed Events中db file scattered read、db file sequentialread的磁盘I/O都比较大。
7.5造成物理读最大的前几个sql语句在报告中未找到，用sql语句查询得出这些语句:select sql_text from v$sql where disk_reads=(select max(disk_reads)from v$sql);
生成语句的执行计划:
SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWSIGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLES
UB) opt_param('parallel_execution_enabled','false') NO_PARALLEL_INDEX(SAMPLES
UB) NO_SQL_TUNE */NVL(SUM(C1),:"SYS_B_0"), NVL(SUM(C2),:"SYS_B_1") FROM (SELE
CT /*+ IGNORE_WHERE_CLAUSENO_PARALLEL("EMP2") FULL("EMP2") NO_PARALLEL_INDEX(
"EMP2") */ :"SYS_B_2"AS C1, CASE WHEN "EMP2"."EMPNO"=:"SYS_B_3" THEN:"SYS_B_
4" ELSE :"SYS_B_5" END AS C2FROM "EMP2" SAMPLE BLOCK (:"SYS_B_6" , :"SYS_B_7"
) SEED (:"SYS_B_8")"EMP2") SAMPLESUB
7.6查看Buffer Pool Advisory并把Buffer cache的大小设置为推荐的大小
14:02:00~ 14:16:05时间段的Buffer Pool Advisory
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 1.0 103,249 218 1.3
D 8 .1 1 1.0 103,249 218 1.3
D 12 .2 1 1.0 103,248 218 1.3
D 16 .3 2 1.0 103,248 218 1.3
D 20 .3 2 1.0 103,248 218 1.3
D 24 .4 3 1.0 103,246 218 1.3
D 28 .4 3 1.0 103,246 218 1.3
D 32 .5 4 1.0 103,246 218 1.3
D 36 .6 4 1.0 103,246 218 1.3
D 40 .6 5 1.0 103,246 218 1.3
D 44 .7 5 1.0 103,246 218 1.3
D 48 .8 6 1.0 103,246 218 1.3
D 52 .8 6 1.0 103,246 218 1.3
D 56 .9 7 1.0 103,246 218 1.3
D 60 .9 7 1.0 103,246 218 1.3
D 64 1.0 8 1.0 103,246 218 1.3
D 68 1.1 8 1.0 103,246 218 1.3
D 72 1.1 9 1.0 103,246 218 1.3
D 76 1.2 9 1.0 103,246 218 1.3
D 80 1.3 10 1.0 103,246 218 1.3
14:16:05 ~14:31:06时间段的Buffer Pool Advisory
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 1.0 210,259 443 2.4
D 8 .1 1 1.0 210,258 443 2.4
D 12 .2 1 1.0 210,258 443 2.4
D 16 .3 2 1.0 210,257 443 2.4
D 20 .3 2 1.0 210,257 443 2.4
D 24 .4 3 1.0 210,256 443 2.4
D 28 .4 3 1.0 210,256 443 2.4
D 32 .5 4 1.0 210,256 443 2.4
D 36 .6 4 1.0 210,256 443 2.4
D 40 .6 5 1.0 210,256 443 2.4
D 44 .7 5 1.0 210,256 443 2.4
D 48 .8 6 1.0 210,256 443 2.4
D 52 .8 6 1.0 210,256 443 2.4
D 56 .9 7 1.0 210,256 443 2.4
D 60 .9 7 1.0 210,256 443 2.4
D 64 1.0 8 1.0 210,256 443 2.4
D 68 1.1 8 1.0 210,256 443 2.4
D 72 1.1 9 1.0 210,256 443 2.4
D 76 1.2 9 1.0 210,256 443 2.4
D 80 1.3 10 1.0 210,256 443 2.4
14:31:06 ~14:46:01时间段的Buffer Pool Advisory
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 1.0 317,159 667 3.5
D 8 .1 1 1.0 317,158 667 3.5
D 12 .2 1 1.0 317,158 667 3.5
D 16 .3 2 1.0 317,157 667 3.5
D 20 .3 2 1.0 317,157 667 3.5
D 24 .4 3 1.0 317,156 667 3.5
D 28 .4 3 1.0 317,156 667 3.5
D 32 .5 4 1.0 317,155 667 3.5
D 36 .6 4 1.0 317,155 667 3.5
D 40 .6 5 1.0 317,155 667 3.5
D 44 .7 5 1.0 317,155 667 3.5
D 48 .8 6 1.0 317,155 667 3.5
D 52 .8 6 1.0 317,155 667 3.5
D 56 .9 7 1.0 317,155 667 3.5
D 60 .9 7 1.0 317,155 667 3.5
D 64 1.0 8 1.0 317,155 667 3.5
D 68 1.1 8 1.0 317,155 667 3.5
D 72 1.1 9 1.0 317,155 667 3.5
D 76 1.2 9 1.0 317,155 667 3.5
D 80 1.3 10 1.0 317,151 667 3.5
14:46:01 ~15:01:02时间段的Buffer Pool Advisory
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 1.0 421,822 894 4.5
D 8 .1 1 1.0 421,821 894 4.5
D 12 .2 1 1.0 421,820 894 4.5
D 16 .3 2 1.0 421,820 894 4.5
D 20 .3 2 1.0 421,819 894 4.5
D 24 .4 3 1.0 421,817 894 4.5
D 28 .4 3 1.0 421,816 894 4.5
D 32 .5 4 1.0 421,816 894 4.5
D 36 .6 4 1.0 421,816 894 4.5
D 40 .6 5 1.0 421,816 894 4.5
D 44 .7 5 1.0 421,816 894 4.5
D 48 .8 6 1.0 421,816 894 4.5
D 52 .8 6 1.0 421,816 894 4.5
D 56 .9 7 1.0 421,816 894 4.5
D 60 .9 7 1.0 421,816 894 4.5
D 64 1.0 8 1.0 421,816 894 4.5
D 68 1.1 8 1.0 421,816 894 4.5
D 72 1.1 9 1.0 421,816 894 4.5
D 76 1.2 9 1.0 421,816 894 4.5
D 80 1.3 10 1.0 421,811 894 4.5
通过以上4个时间段中BufferPool Advisory建议可以看的出来，对于增加Buffer cache的大小对性能的影响并不明显；又根据Top 5 Timed Events的全表扫描的I/O等待事件高的情况可以推断出，需要在经常访问的列上加索引。
7.7查看Time Model System Stats
14:02:00~14:16:05时间段Time Model SystemStats
Statistic Time (s)% of DB time
------------------------------------------------------- ------------
sql execute elapsed time 4,200.6 99.9
DB CPU 803.9 19.1
parse time elapsed 18.8 .4
hard parse elapsed time 18.0 .4
connection management call elapsed 1.8 .0
PL/SQL execution elapsed time 0.1 .0
sequence load elapsed time 0.0 .0
repeated bind elapsed time 0.0 .0
DB time 4,203.5
background elapsed time 25.8
background cpu time 2.0
14:16:05 ~14:31:06时间段Time Model SystemStats
Statistic Time (s)% of DB time
------------------------------------------------------- ------------
DB CPU 860.3 99.9
sql execute elapsed time 858.6 99.7
parse time elapsed 19.5 2.3
hard parse elapsed time 18.7 2.2
connection management call elapsed 1.7 .2
PL/SQL execution elapsed time 0.1 .0
sequence load elapsed time 0.0 .0
repeated bind elapsed time 0.0 .0
DB time 861.5
background elapsed time 1.7
background cpu time 1.3
14:31:06 ~14:46:01时间段Time Model SystemStats
Statistic Time (s) % of DB time
------------------------------------------------------- ------------
DB CPU 855.6 99.8
sql execute elapsed time 854.0 99.6
parse time elapsed 19.1 2.2
hard parse elapsed time 18.3 2.1
connection management call elapsed 1.8 .2
PL/SQL execution elapsed time 0.1 .0
sequence load elapsed time 0.0 .0
repeated bind elapsed time 0.0 .0
DB time 857.0
background elapsed time 1.8
background cpu time 1.2-
14:46:01 ~15:01:02时间段Time Model SystemStats
Statistic Time (s)% of DB time
------------------------------------------------------- ------------
DB CPU 858.6 99.8
sql execute elapsed time 858.0 99.7
parse time elapsed 19.1 2.2
hard parse elapsed time 18.3 2.1
connection management call elapsed 1.6 .2
PL/SQL execution elapsed time 0.1 .0
sequence load elapsed time 0.0 .0
repeated bind elapsed time 0.0 .0
DB time 860.7
background elapsed time 3.1
background cpu time 2.2
通过对比4个报告各个时间段中的Time Model System Stats，发现产生的硬解析依然比较少。
7.8查看Latch Sleep breakdown
14:02:00~14:16:05时间段的LatchSleep breakdown
Get Spin
Latch Name Requests Misses Sleeps Gets
-------------------------- --------------------------- ----------- -----------
cache buffers lru chain 100,176,796 5 6 0
object queue header operat 200,352,258 3 3 0
library cache 125,043 1 1 0
cache buffers chains 300,950,437 1 1 0
14:16:05 ~14:31:06时间段的LatchSleep breakdown
Get Spin
Latch Name Requests Misses Sleeps Gets
-------------------------- --------------------------- ----------- -----------
object queue header operat 214,021,214 2 2 0
cache buffers lru chain 107,011,056 1 1 0
14:31:06 ~14:46:015时间段的LatchSleep breakdown
Get Spin
Latch Name Requests Misses Sleeps Gets
-------------------------- --------------------------- ----------- -----------
object queue header operat 213,801,737 8 8 0
cache buffers lru chain 106,901,262 1 1 0
simulator lru latch 13,379,725 1 1 0
14:46:01 ~15:01:02时间段的LatchSleep breakdown
Get Spin
Latch Name Requests Misses Sleeps Gets
-------------------------- --------------------------- ----------- -----------
object queue header operat 209,321,268 6 6 0
cache buffers lru chain 104,661,061 2 2 0
simulator lru latch 13,102,038 1 1 0
process allocation 1,316 1 1 0
cache buffers chains 314,485,793 1 1 0
通过以上4个sp报告各个时间段的LatchSleep breakdown的内容，发现cachebuffers lru chain的miss和sleep的次数并不多。
8解决方案二：在emp2的empno列上面创建索引，再执行share_pool_sql_1.sh脚本，查看sp报告
8.1在emp2的empno列上创建索引
8.2 重新执行share_pool_sql_1.sh脚本并重新开启statspack自动快照
[oracle@Gc1 ~]$ shscript/bin/share_pool_sql_1.sh
SQL>@?/rdbms/admin/spauto
8.3生成statspack报告
SQL> @?/rdbms/admin/spreport
8.4 Load Profile
| 时间 | Logical reads(Per Second) | Physical reads(Per Second) |
| 16:58:29~ 17:17:05 | 2,442.35 | 1.02 |
| 17:17:05 ~ 17:32:02 | 2,846.49 | 0.22 |
| 17:32:02~ 17:47:03 | 3,023.32 | 0.25 |
| 17:47:03~ 18:02:04 | 3,164.08 | 0.27 |
8.5通过新生成的4个statspack报告对比各个时间段的数据缓冲区的命中率和库缓冲区的命中率：
| 时间 | Buffer Hit（%） | Library Hit（%） |
| 16:58:29~ 17:17:05 | 99.96 | 94.26 |
| 17:17:05 ~ 17:32:02 | 99.99 | 94.20 |
| 17:32:02~ 17:47:03 | 99.99 | 94.22 |
| 17:47:03~ 18:02:04 | 99.99 | 94.16 |
在emp2的empno列上创建索引后通过对比发现数据缓冲区的命中率明显得到了改善，达到了oracle标准的90%以上的要求；而库缓冲区的命中率还是低于oracle标准的库缓冲区命中率99%的命中率，需要进行一进行分析。
8.6查看Top 5 Timed Events找出4 个报告中各个时间段跟磁盘I/O相关的等待事件
| 时间 | name | waits | Time (s) |
| 16:58:29~ 17:17:05 | log file parallel write | 1,460 | 5 |
| db file sequential read | 739 | 2 | |
| log file sync | 330 | 2 | |
| db file scattered read | 71 | 0 | |
| CPU time |
| 299 | |
| 17:17:05 ~17:32:02 | log file parallel write | 1,050 | 1 |
| log file sync | 245 | 1 | |
| db file sequential read | 196 | 0 | |
| control file parallel write | 299 | 0 | |
| CPU time |
| 306 | |
| 17:32:02~ 17:47:03
| log file parallel write | 1,127 | 2 |
| log file sync | 265 | 1 | |
| db file sequential read | 223 | 0 | |
| control file parallel write | 300 | 0 | |
| CPU time |
| 877 | |
| 17:47:03~ 18:02:04 | log file parallel write | 1,157 | 2 |
| log file sync | 278 | 2 | |
| db file sequential read | 246 | 0 | |
| control file parallel write | 299 | 0 | |
| CPU time |
| 302 |
通过4个报告的对比Top 5 Timed Events中的logfile parallel write磁盘I/O都比较大。
8.7造成物理读最大的前几个sql语句在报告中未找到，用sql语句查询得出这些语句:select sql_text from v$sql where disk_reads=(select max(disk_reads)from v$sql);
生成语句的执行计划:
begin dbms_stats.gather_table_stats('SYS',:bind1, cascade => TRUE, method_opt
=> 'FOR ALL COLUMNS SIZE 1',estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE)
; end;
通过对比物理读最大的sql的执行计划发现有相同或相似的执行计划，应该使用绑定变量。
8.8查看Buffer Pool Advisory并把Buffer cache的大小设置为推荐的大小
16:58:29~ 17:17:05时间段的Buffer Pool Advisory
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 1.0 814,336 1,749 7.0
D 8 .1 1 1.0 814,319 1,748 7.0
D 12 .2 1 1.0 814,316 1,748 7.0
D 16 .3 2 1.0 814,313 1,748 7.0
D 20 .3 2 1.0 814,312 1,748 7.0
D 24 .4 3 1.0 814,309 1,748 7.0
D 28 .4 3 1.0 814,307 1,748 7.0
D 32 .5 4 1.0 814,305 1,748 7.0
D 36 .6 4 1.0 814,304 1,748 7.0
D 40 .6 5 1.0 814,303 1,747 7.0
D 44 .7 5 1.0 814,303 1,747 7.0
D 48 .8 6 1.0 814,303 1,747 7.0
D 52 .8 6 1.0 814,303 1,747 7.0
D 56 .9 7 1.0 814,303 1,747 7.0
D 60 .9 7 1.0 814,303 1,747 7.0
D 64 1.0 8 1.0 814,303 1,747 7.0
D 68 1.1 8 1.0 814,303 1,747 7.0
D 72 1.1 9 1.0 814,303 1,747 7.0
D 76 1.2 9 1.0 814,303 1,747 7.0
D 80 1.3 10 1.0 814,022 1,733 6.9
17:17:05 ~17:32:02时间段的Buffer Pool Advisory
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 1.0 814,338 1,750 6.9
D 8 .1 1 1.0 814,320 1,749 6.9
D 12 .2 1 1.0 814,316 1,748 6.9
D 16 .3 2 1.0 814,314 1,748 6.9
D 20 .3 2 1.0 814,312 1,748 6.9
D 24 .4 3 1.0 814,310 1,748 6.9
D 28 .4 3 1.0 814,308 1,748 6.9
D 32 .5 4 1.0 814,306 1,748 6.9
D 36 .6 4 1.0 814,304 1,748 6.9
D 40 .6 5 1.0 814,303 1,748 6.9
D 44 .7 5 1.0 814,303 1,748 6.9
D 48 .8 6 1.0 814,303 1,748 6.9
D 52 .8 6 1.0 814,303 1,748 6.9
D 56 .9 7 1.0 814,303 1,748 6.9
D 60 .9 7 1.0 814,303 1,748 6.9
D 64 1.0 8 1.0 814,303 1,748 6.9
D 68 1.1 8 1.0 814,303 1,748 6.9
D 72 1.1 9 1.0 814,303 1,748 6.9
D 76 1.2 9 1.0 814,303 1,748 6.9
D 80 1.3 10 1.0 814,023 1,733 6.9
17:32:02~17:47:03时间段的Buffer Pool Advisory
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 1.0 814,339 1,750 6.9
D 8 .1 1 1.0 814,321 1,749 6.9
D 12 .2 1 1.0 814,317 1,749 6.9
D 16 .3 2 1.0 814,314 1,748 6.9
D 20 .3 2 1.0 814,313 1,748 6.9
D 24 .4 3 1.0 814,310 1,748 6.9
D 28 .4 3 1.0 814,308 1,748 6.9
D 32 .5 4 1.0 814,306 1,748 6.9
D 36 .6 4 1.0 814,305 1,748 6.9
D 40 .6 5 1.0 814,304 1,748 6.9
D 44 .7 5 1.0 814,303 1,748 6.9
D 48 .8 6 1.0 814,303 1,748 6.9
D 52 .8 6 1.0 814,303 1,748 6.9
D 56 .9 7 1.0 814,303 1,748 6.9
D 60 .9 7 1.0 814,303 1,748 6.9
D 64 1.0 8 1.0 814,303 1,748 6.9
D 68 1.1 8 1.0 814,303 1,748 6.9
D 72 1.1 9 1.0 814,303 1,748 6.9
D 76 1.2 9 1.0 814,303 1,748 6.9
D 80 1.3 10 1.0 814,023 1,733 6.8
17:47:03~18:02:04时间段的Buffer Pool Advisory
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 1.0 814,341 1,750 6.8
D 8 .1 1 1.0 814,323 1,749 6.8
D 12 .2 1 1.0 814,318 1,749 6.8
D 16 .3 2 1.0 814,316 1,749 6.8
D 20 .3 2 1.0 814,314 1,749 6.8
D 24 .4 3 1.0 814,311 1,749 6.8
D 28 .4 3 1.0 814,309 1,749 6.8
D 32 .5 4 1.0 814,307 1,748 6.8
D 36 .6 4 1.0 814,305 1,748 6.8
D 40 .6 5 1.0 814,304 1,748 6.8
D 44 .7 5 1.0 814,304 1,748 6.8
D 48 .8 6 1.0 814,304 1,748 6.8
D 52 .8 6 1.0 814,303 1,748 6.8
D 56 .9 7 1.0 814,303 1,748 6.8
D 60 .9 7 1.0 814,303 1,748 6.8
D 64 1.0 8 1.0 814,303 1,748 6.8
D 68 1.1 8 1.0 814,303 1,748 6.8
D 72 1.1 9 1.0 814,303 1,748 6.8
D 76 1.2 9 1.0 814,303 1,748 6.8
D 80 1.3 10 1.0 814,024 1,733 6.7
通过以上4个时间段中BufferPool Advisory建议可以看的出来，对于增加Buffer cache的大小对性能的影响并不明显。
8.9查看Time Model System Stats
16:58:29~17:17:05时间段Time Model SystemStats
Statistic Time (s)% of DB time
------------------------------------------------------- ------------
DBCPU 233.4 97.4
parsetime elapsed 180.1 75.2
hardparse elapsed time 151.2 63.1
sqlexecute elapsed time 135.8 56.7
connectionmanagement call elapsed 46.5 19.4
PL/SQLexecution elapsed time 4.2 1.7
sequenceload elapsed time 1.2 .5
repeatedbind elapsed time 0.0 .0
PL/SQLcompilation elapsed time 0.0 .0
failedparse elapsed time 0.0 .0
hardparse (sharing criteria) elaps 0.0 .0
DBtime 239.5
backgroundelapsed time 13.2
backgroundcpu time 6.1
17:17:05 ~17:32:02时间段Time Model SystemStats
Statistic Time (s)% of DB time
------------------------------------------------------- ------------
DB CPU 240.1 97.3
parse time elapsed 187.0 75.8
hard parse elapsed time 155.6 63.1
sql execute elapsed time 38.1 56.0
connection management call elapsed 46.5 18.8
PL/SQL execution elapsed time 4.2 1.7
sequence load elapsed time 1.2 .5
repeated bind elapsed time 0.0 .0
DB time 246.7
background elapsed time 2.8
background cpu time 1.8
17:32:02~17:47:03时间段Time Model SystemStats
Statistic Time (s)% of DB time
------------------------------------------------------- ------------
DB CPU 247.4 98.1
parse time elapsed 189.4 75.1
hard parse elapsed time 158.7 63.0
sql execute elapsed time 143.0 56.7
connection management call elapsed 49.0 19.4
PL/SQL execution elapsed time 4.4 1.8
sequence load elapsed time 1.3 .5
repeated bind elapsed time 0.0 .0
DB time 252.1
background elapsed time 3.0
background cpu time 1.7
17:47:03~18:02:04时间段Time Model SystemStats
Statistic Time (s)% of DB time
------------------------------------------------------- ------------
DB CPU 249.1 97.7
parse time elapsed 191.7 75.2
hard parse elapsed time 159.3 62.4
sql execute elapsed time 144.5 56.7
connection management call elapsed 49.5 19.4
PL/SQL execution elapsed time 4.5 1.8
sequence load elapsed time 1.3 .5
repeated bind elapsed time 0.0 .0
DB time 255.0
background elapsed time 4.8
background cpu time 2.7
通过对比4个报告各个时间段中的Time Model System Stats，发现产生的硬解析明显增加了。
8.10查看Latch Sleep breakdown
16:58:29~17:17:05时间段的LatchSleep breakdown
Get Spin
Latch Name Requests Misses Sleeps Gets
-------------------------- --------------------------- ----------- -----------
library cache 3,473,070 8 8 0
slave class create 8 1 1 0
row cache objects 2,077,268 1 1 0
session allocation 970,802 1 1 0
OS process allocation 40,661 1 1 0
17:17:05 ~17:32:02时间段的LatchSleep breakdown
Get Spin
Latch Name Requests Misses Sleeps Gets
-------------------------- --------------------------- ----------- -----------
library cache 3,331,290 6 6 0
row cache objects 1,969,896 1 1 0
17:32:02~17:47:03时间段的LatchSleep breakdown
Get Spin
Latch Name Requests Misses Sleeps Gets
-------------------------- --------------------------- ----------- -----------
library cache 3,552,846 3 3 0
channel operations parent 67,869 2 2 0
17:47:03~18:02:04时间段的LatchSleep breakdown
Get Spin
Latch Name Requests Misses Sleeps Gets
-------------------------- --------------------------- ----------- -----------
library cache 3,700,680 7 7 0
simulator lru latch 95,831 2 2 0
library cache lock 2,103,537 1 1 0
OS process: request alloca 44,002 1 1 0
shared pool 1,084,348 1 1 0
通过以上4个sp报告各个时间段的LatchSleep breakdown的内容，发现c library cache的miss和sleep的次数并不多。
9解决方案三：使用绑定变量并生成报告进行分析
9．1重新修改查询业务语句加入绑定变量
sqlplus scott/tiger <<EOF
declare
v_empno emp2.empno%type;
v_emp2 emp2%rowtype;
begin
v_empno := 0;
while v_empno<=2000000 loop
v_empno :=v_empno + 1;
select * into v_emp2
from emp2 where empno=v_empno;
ifv_empno=2000000 then
v_empno:=0;
endif;
end loop;
end;
/
9.2执行新的查询业务脚本并启动statspack自动快照
[oracle@gc1 ~]$ sh share_pool_sql_2.sh
9.3生成statspack新的分析报告
SQL> @?/rdbms/admin/spreport
9.4 Load Profile
| 时间 | Logical reads(Per Second) | Physical reads(Per Second) |
| 00:08:02~00:23:02 | 141,939.94 | 130,633.92 |
| 00:23:02~00:38:03 | 140,644.86 | 129,183.51 |
| 00:38:03~00:53:04 | 130,343.47 | 119,753.05 |
| 00:53:04~01:08:05 | 134,068.38 | 122,726.76 |
9.5通过新生成的4个statspack报告对比各个时间段的数据缓冲区的命中率和库缓冲区的命中率：
| 时间 | Buffer Hit（%） | Library Hit（%） |
| 7.97 | 87.49 | |
| 00:23:02~00:38:03 | 8.15 | 96.11 |
| 00:38:03~00:53:04 | 8.13 | 97.39 |
| 00:53:04~01:08:05 | 8.46 | 87.58 |
通过对比以上4个报告，使用绑定变量后，。
9.6查看Top 5 TimedEvents找出4 个报告中各个时间段跟磁盘I/O相关的等待事件
| 时间 | name | waits | Time (s) |
| 00:08:02~00:23:02 | db file scattered read | 1,164,285 | 240 |
|
| db file sequential read | 783 | 0 |
|
| log file parallel write | 14 | 0 |
|
| control file parallel write | 300 | 0 |
|
| CPU time |
| 897 |
| 00:23:02~00:38:03 | log file parallel write | 1,050 | 1 |
|
| log file sync | 245 | 1 |
|
| db file sequential read | 196 | 0 |
|
| control file parallel write | 299 | 0 |
|
| CPU time |
| 306 |
| 00:38:03~00:53:04 | log file parallel write | 1,127 | 2 |
|
| log file sync | 265 | 1 |
|
| db file sequential read | 223 | 0 |
|
| control file parallel write | 300 | 0 |
|
| CPU time |
| 877 |
| 00:53:04~01:08:05 | log file parallel write | 1,157 | 2 |
|
| log file sync | 278 | 2 |
|
| db file sequential read | 246 | 0 |
|
| control file parallel write | 299 | 0 |
|
| CPU time |
| 302 |
9.7造成物理读最大的前几个sql语句在报告中未找到，用sql语句查询得出这些语句:select sql_text from v$sql where disk_reads=(select max(disk_reads)from v$sql);
SELECT* FROM EMP2 WHERE EMPNO=:B1
通过对比发现还是有重用执行计划，但重用的执行计划已经使用了绑定变量
9.8查看Buffer Pool Advisory并把Buffer cache的大小设置为推荐的大小
00:08:02~00:23:02时间段的Buffer Pool Advisory
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 28 .1 3 1.1 265,861 52,417 ######
D 56 .2 7 1.1 265,859 52,410 ######
D 84 .3 10 1.1 265,858 52,410 ######
D 112 .4 14 1.0 248,297 583 19.6
D 140 .4 17 1.0 248,293 574 19.3
D 168 .5 21 1.0 248,290 565 19.0
D 196 .6 24 1.0 248,288 557 18.7
D 224 .7 28 1.0 248,286 553 18.6
D 252 .8 31 1.0 248,285 548 18.4
D 280 .9 35 1.0 248,283 543 18.3
D 308 1.0 38 1.0 248,281 538 18.1
D 312 1.0 39 1.0 248,281 537 18.1
D 336 1.1 42 1.0 248,280 533 17.9
D 364 1.2 45 1.0 248,278 529 17.8
D 392 1.3 49 1.0 237,905 524 17.6
D 420 1.3 52 0.9 215,109 524 17.6
D 448 1.4 56 0.9 215,107 524 17.6
D 476 1.5 59 0.9 215,105 524 17.6
D 504 1.6 63 0.9 215,104 524 17.6
D 532 1.7 66 0.9 215,102 524 17.6
D 560 1.8 70 0.9 215,101 524 17.6
00:23:02~00:38:03时间段的Buffer Pool Advisory
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 28 .1 3 1.1 390,507 72,151 ######
D 56 .2 7 1.1 390,505 72,145 ######
D 84 .3 10 1.1 390,505 72,145 ######
D 112 .4 14 1.0 364,690 821 21.3
D 140 .4 17 1.0 364,687 812 21.1
D 168 .5 21 1.0 364,684 803 20.8
D 196 .6 24 1.0 364,682 796 20.7
D 224 .7 28 1.0 364,680 792 20.6
D 252 .8 31 1.0 364,678 787 20.4
D 280 .9 35 1.0 364,677 783 20.3
D 308 1.0 38 1.0 364,675 778 20.2
D 316 1.0 39 1.0 364,675 777 20.2
D 336 1.1 42 1.0 364,673 774 20.1
D 364 1.2 45 1.0 364,672 770 20.0
D 392 1.2 49 1.0 354,303 763 19.8
D 420 1.3 52 0.9 316,626 763 19.8
D 448 1.4 56 0.9 316,624 763 19.8
D 476 1.5 59 0.9 316,623 763 19.8
D 504 1.6 63 0.9 316,621 763 19.8
D 532 1.7 66 0.9 316,619 763 19.8
D 560 1.8 70 0.9 316,618 763 19.8
00:38:03~00:53:04时间段的Buffer Pool Advisory
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 28 .1 3 1.1 506,067 92,972 ######
D 56 .2 7 1.1 506,065 92,966 ######
D 84 .3 10 1.1 506,065 92,965 ######
D 112 .4 14 1.0 472,599 1,061 22.4
D 140 .4 17 1.0 472,596 1,052 22.2
D 168 .5 21 1.0 472,593 1,044 22.1
D 196 .6 24 1.0 472,590 1,037 21.9
D 224 .7 28 1.0 472,588 1,033 21.8
D 252 .8 31 1.0 472,587 1,028 21.7
D 280 .9 35 1.0 472,585 1,024 21.6
D 308 1.0 38 1.0 472,583 1,019 21.5
D 316 1.0 39 1.0 472,583 1,018 21.5
D 336 1.1 42 1.0 472,582 1,015 21.4
D 364 1.2 45 1.0 472,580 1,010 21.3
D 392 1.2 49 1.0 462,212 1,004 21.2
D 420 1.3 52 0.9 410,869 1,004 21.2
D 448 1.4 56 0.9 410,867 1,004 21.2
D 476 1.5 59 0.9 410,865 1,004 21.2
D 504 1.6 63 0.9 410,864 1,004 21.2
D 532 1.7 66 0.9 410,862 1,004 21.2
D 560 1.8 70 0.9 410,861 1,004 21.2
00:53:04~01:08:05时间段的Buffer Pool Advisory
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 28 .1 3 1.1 624,478 113,279 ######
D 56 .2 7 1.1 624,476 113,274 ######
D 84 .3 10 1.1 624,476 113,273 ######
D 112 .4 14 1.0 583,164 1,300 23.2
D 140 .4 17 1.0 583,161 1,292 23.0
D 168 .5 21 1.0 583,158 1,284 22.9
D 196 .6 24 1.0 583,156 1,277 22.7
D 224 .7 28 1.0 583,154 1,273 22.7
D 252 .8 31 1.0 583,152 1,268 22.6
D 280 .9 35 1.0 583,151 1,264 22.5
D 308 1.0 38 1.0 583,149 1,259 22.4
D 316 1.0 39 1.0 583,149 1,258 22.4
D 336 1.1 42 1.0 583,148 1,255 22.4
D 364 1.2 45 1.0 583,146 1,251 22.3
D 392 1.2 49 1.0 572,786 1,244 22.2
D 420 1.3 52 0.9 507,684 1,244 22.2
D 448 1.4 56 0.9 507,682 1,244 22.2
D 476 1.5 59 0.9 507,680 1,244 22.2
D 504 1.6 63 0.9 507,679 1,244 22.2
D 532 1.7 66 0.9 507,677 1,244 22.2
D 560 1.8 70 0.9 507,676 1,244 22.2
通过以上4个时间段中BufferPool Advisory建议可以看的出来，对于增加Buffer cache的大小对性能的影响并不明显，不用再对buffer cache进行调整。
9.9查看Time Model System Stats
00:08:02~00:23:02时间段Time Model System Stats
Statistic Time (s)% of DB time
------------------------------------------------------- ------------
sqlexecute elapsed time 884.2 100.3
DBCPU 879.5 99.7
parsetime elapsed 1.4 .2
hardparse elapsed time 1.4 .2
PL/SQLexecution elapsed time 0.1 .0
repeatedbind elapsed time 0.0 .0
hardparse (sharing criteria) elaps 0.0 .0
PL/SQLcompilation elapsed time 0.0 .0
sequenceload elapsed time 0.0 .0
DBtime 881.9
backgroundelapsed time 1.7
backgroundcpu time 1.2
00:23:02~00:38:03时间段Time Model System Stats
Statistic Time (s)% of DB time
------------------------------------------------------- ------------
sql execute elapsed time 881.3 100.1
DB CPU 879.4 99.9
parse time elapsed 0.5 .1
hard parse elapsed time 0.5 .1
hard parse (sharing criteria) elaps 0.2 .0
PL/SQL execution elapsed time 0.0 .0
PL/SQL compilation elapsed time 0.0 .0
repeated bind elapsed time 0.0 .0
DB time 880.3
background elapsed time 1.9
background cpu time 1.6
00:38:03~00:53:04时间段Time Model System Stats
Statistic Time (s)% of DB time
------------------------------------------------------- ------------
sql executeelapsed time 881.6 100.1
DB CPU 880.3 99.9
parse timeelapsed 0.4 .0
hard parseelapsed time 0.4 .0
hard parse(sharing criteria) elaps 0.2 .0
PL/SQLexecution elapsed time 0.0 .0
PL/SQLcompilation elapsed time 0.0 .0
repeatedbind elapsed time 0.0 .0
DB time 880.9
backgroundelapsed time 1.5
backgroundcpu time 1.4
00:53:04~01:08:05时间段Time Model System Stats
Statistic Time (s) % ofDB time
------------------------------------------------------- ------------
sql executeelapsed time 881.1 100.1
DB CPU 878.7 99.8
parse timeelapsed 0.3 .0
hard parseelapsed time 0.2 .0
PL/SQLexecution elapsed time 0.0 .0
hard parse(sharing criteria) elaps 0.0 .0
PL/SQLcompilation elapsed time 0.0 .0
repeatedbind elapsed time 0.0 .0
DB time 880.5
backgroundelapsed time 4.0
backgroundcpu time 2.6
通过对比4个报告各个时间段中的hard parse elapsed time，发现产生的硬解析比前几次都少，几个没有，说明使用绑定变量后的重复的执行计划，得到了有效的利用。
9.10查看Latch Sleep breakdown
00:08:02~00:23:02时间段的Latch Sleep breakdown
Get Spin
Latch Name Requests Misses Sleeps Gets
-------------------------- --------------------------- ----------- -----------
cache buffers lru chain 117,562,359 16 16 0
simulator lru latch 15,434,313 9 9 0
cache buffers chains 373,034,885 2 1 1
object queue header operat 235,121,491 2 2 0
Memory Management Latch 55 2 2 0
00:23:02~00:38:03时间段的Latch Sleep breakdown
Get Spin
Latch Name Requests Misses Sleeps Gets
-------------------------- --------------------------- ----------- -----------
cache buffers lru chain 116,395,712 11 11 0
simulator lru latch 15,311,439 4 4 0
object queue header operat 232,789,755 4 4 0
Memory Management Latch 35 2 2 0
00:38:03~00:53:04时间段的Latch Sleep breakdown
Get Spin
Latch Name Requests Misses Sleeps Gets
-------------------------- --------------------------- ----------- -----------
cache buffers lru chain 107,909,186 13 13 0
object queue header operat 215,817,850 6 6 0
simulator lru latch 14,191,435 5 5 0
00:53:04~01:08:05时间段的Latch Sleep breakdown
Get Spin
Latch Name Requests Misses Sleeps Gets
-------------------------- --------------------------- ----------- -----------
cache buffers lru chain 110,567,951 25 25 0
simulator lru latch 14,592,139 9 9 0
object queue header operat 221,134,764 5 5 0
redo allocation 859 2 2 0
shared pool 29,061 1 1 0
通过以上4个sp报告各个时间段的LatchSleep breakdown的内容，发现cachebuffers lru chain已经没有了，cache buffers chains的miss和sleep的次数都很少。
313
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
