蓝的成长记——追逐DBA（8）：重拾SP报告，回忆oracle的STATSPACK实验
时间：2022-03-14 00:35
***********************************************声明***********************************************************************
原创作品，出自 “深蓝的blog” 博客，欢迎转载，转载时请务必注明出处，否则追究版权法律责任。
深蓝的blog：
****************************************************************************************************************************
蓝的成长记——追逐DBA（8）：重拾SP报告，回忆oracle的STATSPACK实验
**************************************************简介********************************************************************
个人在oracle路上的成长记录，其中以蓝自喻，分享成长中的情感、眼界与技术的变化与成长。敏感信息均以英文形式代替，不会泄露任何企业机密，纯为技术分享。
创作灵感源于对自己的自省和记录。若能对刚刚起步的库友起到些许的帮助或共鸣，欣慰不已。
欢迎拍砖，如有关技术细节表述有错误之处，请您留言或邮件（hyldba@163.com）指明，不胜感激。
***************************************************************************************************************************
今天有些慵懒，整理过往学习中的一个实验，忆起oracle的SP报告。
——深蓝
**************************************************前言********************************************************************
这是一部个人记录的成长杂记，既然步入到oracle的这片蓝海，免不了一路的奔波与不断的考验。借由此杂记与库友们分享蓝的成长历程。
不知何时起对蓝有了一种说不出来的痴迷，痴迷其广博，痴迷其深邃，痴迷于近在咫尺却又遥不可及。
而又说不清从何时起，注视于oracle的红色耀眼，照亮出眼前的一道光，未知与迷惑在自己的脚下开始初露些许人生的充实与青春的回馈。
在追逐于DBA梦想的道路上步步前行。
***************************************************************************************************************************
时间有些久了，有些淡忘了SP报告的方法了，今天就利用闲暇的时光，重新拾起熟悉又陌生的STATSPACK报告的实验。
实验计划：
1、模拟某业务环境，制定快照计划；
2、生成初始状态数据库的statspack报告，分析数据；
3、调整数据缓冲区尺寸，生成 statspack报告，分析数据；
4、创建索引，生成statspack报告，分析数据；
5、使用绑定变量，生成 statspack报告，分析数据。
******************************************************************************************
步骤一：模拟业务环境，制定快照计划
目标：
1、关闭sga自动管理，调整DB cache、sharepool大小；
2、部署statspack；
3、部署模拟现场环境；
*****************************************************************************************
1、关闭sga自动管理，调整DB cache、sharepool大小，模拟现场环境
SQL> alter system set memory_target=0 scope=spfile; --11g中关闭内存自动管理 SQL> alter system set sga_target=0; SQL> alter system set db_cache_size=30m scope=spfile; --修改DB cache大小 SQL> alter system set shared_pool_size=70m scope=spfile; --修改share pool大小 SQL> startup force; --重启数据库 SQL> select component,current_size/1024/1024 from v$sga_dynamic_components; --查询修改后的缓冲区大小 COMPONENT CURRENT_SIZE/1024/1024 ---------------------------------------- ---------------------- shared pool 72 DEFAULT buffer cache 32
2、部署statspack
SQL> create tablespace tools datafile '/u01/app/oracle/oradata/PROD/disk6/tools01.dbf' size 300m; --创建statspack专用的tools表空间 SQL> @?/rdbms/admin/spcreate.sql --以sysdba身份执行spcreate脚本，用于创建spcreate对象 输入值设置： Enter value for perfstat_password: oracle Enter value for default_tablespace: tools Enter value for temporary_tablespace:回车 $ vi /u01/app/oracle/product/11.2.0/db_1/rdbms/admin/spauto.sql --设置自动快照时间，间隔30分钟生成一次快照 编辑如下： begin select instance_number into :instno from v$instance; dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/48,'MI'), 'trunc(SYSDATE+1/48,''MI'')', TRUE, :instno); commit; end; SQL>exec statspack.modify_statspack_parameter(i_snap_level=>7); --设置快照默认级别为7 SQL> conn scott/tiger SQL>CREATE SEQUENCE emp2_empno INCREMENT BY 1 START WITH 1 MAXVALUE 100000000 CACHE 10000 NOCYCLE; --执行创建序列语句
3、部署模拟现场环境
SQL> create table emp2 as select * from emp where 1=2; --创建实验表emp2，结构同emp表
SQL> alter table emp2 modify empno number(10);
SQL> alter table emp2 modify ename varchar(30);
SQL> alter table emp2 nologging; --为加快数据插入速度，关闭日志记录
--插入2千万行数据
SQL>begin
for i in 1..20000000 loop
insert into emp2
values (emp2_empno.nextval,'cuug'||i,'SALESMAN',7698,sysdate,1600,300,30);
if mod(i,1000)=0 then
commit;
end if;
end loop;
commit;
end;
/
SQL> alter table emp2 logging; --开启日志记录
$ vi script/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
$ sh script/bin/share_pool_sql_1.sh --执行脚本，模拟“查询业务”
*****************************************************************************************
步骤二：生成原始statspack报告，分析报告
目标：
1、开启自动快照；
2、生成、导出报告；
3、关闭job；
4、分析报告。
*****************************************************************************************
1、开启自动快照
<span style="font-size:12px;">SQL> conn perfstat/oracle --开启快照及查询相关业务时，需要以perfstat身份登录
SQL>@?/rdbms/admin/spauto --执行脚本，开启自动快照</span>
2、生成报告
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; --设置查看格式，便于查询 SQL> select snap_id,snap_time,snap_level from stats$snapshot order by snap_time; --查询快照数量，是否满足生成statspack报告条件 SQL> @?/rdbms/admin/spreport --生成statspack报告 手工设置： Enter value for begin_snap:快照起点 Enter value for end_snap: 快照终点 Enter value for report_name:默认或指定报告名称 --使用x-manager将报告拷贝到windows主机
3、关闭job
SQL> select job,log_user,last_date,next_date from user_jobs; --查询需要关闭的job号
SQL> exec dbms_job.remove('21'); --将job号为21的任务删除
4、分析报告
关注点：
①buffer hit
②library hit
③Top 5 Timed Events
④造成最大物理读的sql
⑤Buffer Pool Advisory
⑥time model system stats
⑦Latch Sleep breakdown
① buffer hit、②library hit
|
direct path read |
32,014,645 |
814 |
|
|
db file sequential read |
1,697 |
6 |
|
|
log file parallel write |
706 |
5 |
|
|
18:12:00 ~18:42:00 |
direct path read |
32,095,337 |
816 |
|
log file parallel write |
898 |
5 |
|
|
os thread startup |
50 |
9 |
|
|
18:42:00~ 19:12:05 |
direct path read |
32,438,303 |
816 |
|
log file parallel write |
816 |
7 |
|
|
control file parallel write |
493 |
1 |
|
|
19:12:05~ 19:42:03 |
direct path read |
32,255,547 |
816 |
|
log file parallel write |
716 |
5 |
|
|
control file parallel write |
491 |
1 |
分析：
direct path read的磁盘I/O产生量最大，db file sequential read、log file parallel write、control file parallel write也会产生部分磁盘I/O。
④查出造成物理读最大的前几个sql语句，产生执行计划
SQL>select sql_text from v$sql where disk_reads=(select max(disk_reads) from v$sql); --查询造成最大物理读的sql语句
……
select * from emp2 where empno=2215
select * from emp2 where empno=2270
select * from emp2 where empno=2208
……
SQL> set autotrace on；
SQL> set timing on;
SQL> select * from emp2 where empno=2208； --执行一条语句，查看执行计划，可以发现方式为全表扫描，在oracle11g下全表扫描时，库缓冲区将直接从磁盘中查询数据,磁盘I/O较大。cost值、physical read较大
<span style="font-family:SimSun;font-size:12px;"> EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------------ --------------------- ------------------ ---------- ----------- ---------- ------------ --------------
2208 cuug2207 SALESMAN 7698 03-JUN-14 1600 300 30
</span>Elapsed: 00:00:00.94
Execution Plan
----------------------------------------------------------
Plan hash value: 2941272003
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 48 | 40046 (1)| 00:08:01 |
|* 1 | TABLE ACCESS FULL| EMP2 | 1 | 48 | 40046 (1)| 00:08:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
147357 consistent gets
147349 physical reads
0 redo size
869 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
分析： 未发现造成物理读最大的sql。但发现查询语句为全表扫描，每条语句物理读都相对较大。
⑤Buffer Pool Advisory
|
statistics |
||||||||
|
example |
||||||||
|
Time :17:42:01~ 18:12:00 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.2 21 20 .4 D 8 .3 1 1.1 19 17 .3 D 12 .4 1 1.1 18 16 .3 D 16 .5 2 1.0 18 16 .3 D 20 .6 2 1.0 18 15 .3 D 24 .8 3 1.0 17 15 .3 D 28 .9 3 1.0 17 15 .3 D 32 1.0 4 1.0 17 15 .3 D 36 1.1 4 1.0 17 15 .3 D 40 1.3 5 1.0 17 15 .3 D 44 1.4 5 1.0 17 15 .3 D 48 1.5 6 1.0 17 15 .3 D 52 1.6 6 1.0 17 15 .3 D 56 1.8 7 1.0 17 15 .3 D 60 1.9 7 1.0 17 15 .3 D 64 2.0 8 1.0 17 15 .3 |
分析： 对比4个时间段中的最佳buffer pool建议及第一时间段下的详细趋势列表，buffer pool设置为32m并未影响到性能。
⑥time model system stats
time：17:42:01~ 18:12:00
Statistic Time (s) % DB time
----------------------------------- -------------------- ---------
sql execute elapsed time 1,772.4 99.3
DB CPU 1,747.0 97.9
parse time elapsed 62.4 3.5
hard parse elapsed time 58.0 3.3
connection management call elapsed 6.2 .3
PL/SQL execution elapsed time 6.1 .3
hard parse (sharing criteria) elaps 6.1 .3
hard parse (bind mismatch) elapsed 3.9 .2
PL/SQL compilation elapsed time 0.7 .0
repeated bind elapsed time 0.4 .0
sequence load elapsed time 0.1 .0
DB time 1,784.9
background elapsed time 26.5
background cpu time 3.7
time：18:12:00 ~18:42:00
Statistic Time (s) % DB time
----------------------------------- -------------------- ---------
sql execute elapsed time 2,549.1 99.5
DB CPU 1,752.4 68.4
parse time elapsed 60.2 2.4
hard parse elapsed time 57.0 2.2
PL/SQL execution elapsed time 6.2 .2
hard parse (sharing criteria) elaps 6.2 .2
connection management call elapsed 6.1 .2
hard parse (bind mismatch) elapsed 4.0 .2
PL/SQL compilation elapsed time 0.7 .0
repeated bind elapsed time 0.4 .0
sequence load elapsed time 0.1 .0
DB time 2,561.0
background elapsed time 21.2
background cpu time 1.9
time：18:42:00~ 19:12:05
Statistic Time (s) % DB time
----------------------------------- -------------------- ---------
sql execute elapsed time 3,548.9 99.6
DB CPU 1,751.7 49.2
parse time elapsed 37.7 1.1
hard parse elapsed time 34.9 1.0
connection management call elapsed 7.3 .2
hard parse (sharing criteria) elaps 3.6 .1
PL/SQL execution elapsed time 3.2 .1
hard parse (bind mismatch) elapsed 2.1 .1
PL/SQL compilation elapsed time 0.5 .0
repeated bind elapsed time 0.4 .0
sequence load elapsed time 0.1 .0
DB time 3,563.0
background elapsed time 30.8
background cpu time 3.7
time：19:12:05~ 19:42:03
Statistic Time (s) % DB time
----------------------------------- -------------------- ---------
sql execute elapsed time 3,541.3 99.7
DB CPU 1,746.9 49.2
parse time elapsed 37.9 1.1
hard parse elapsed time 35.2 1.0
connection management call elapsed 5.3 .2
hard parse (sharing criteria) elaps 3.7 .1
PL/SQL execution elapsed time 3.2 .1
hard parse (bind mismatch) elapsed 2.0 .1
PL/SQL compilation elapsed time 0.4 .0
repeated bind elapsed time 0.3 .0
sequence load elapsed time 0.1 .0
DB time 3,552.3
background elapsed time 22.0
background cpu time 1.4
分析： 对比4个时间段的time model system stats，发现有硬解析存在。
⑦Latch Sleep breakdown
time：17:42:01~ 18:12:00
Get Spin
Latch Name Requests Misses Sleeps Gets
-------------------------- --------------- ------------ ----------- -----------
qmn task queue latch 252 11 11 0
shared pool 669,111 7 7 0
space background task latc 1,114 6 6 0
cache buffers chains 1,415,617 1 1 0
JS Sh mem access 4 1 1 0
OS process allocation 4,354 1 1 0
FOB s.o list latch 7,177 1 1 0
messages 16,597 1 1 0
In memory undo latch 60,562 1 1 0
time：18:12:00 ~18:42:00
Get Spin
Latch Name Requests Misses Sleeps Gets
-------------------------- --------------- ------------ ----------- -----------
qmn task queue latch 260 13 13 0
space background task latc 1,109 4 4 0
SQL memory manager latch 3,316 1 1 0
JS Sh mem access 5 1 1 0
FOB s.o list latch 7,103 1 1 0
shared pool 666,763 1 1 0
time：18:42:00~ 19:12:05
Get Spin
Latch Name Requests Misses Sleeps Gets
-------------------------- --------------- ------------ ----------- -----------
qmn task queue latch 256 10 10 0
space background task latc 1,125 7 7 0
JS Sh mem access 6 2 2 0
shared pool 657,424 1 1 0
time：19:12:05~ 19:42:03
Get Spin
Latch Name Requests Misses Sleeps Gets
-------------------------- --------------- ------------ ----------- -----------
qmn task queue latch 256 10 10 0
space background task latc 1,109 5 5 0
JS Sh mem access 3 1 1 0
分析： 观察Latch Sleep breakdown，在4个时间段中出现有miss、sleep的次数没有明显突显趋势，对性能没产生根本性影响。
总结：
通过以上几点分析发现，影响性能的方面有： ① library hit 平均值为86.64%低于标准值95%； ② direct path read产生磁盘I/O量最大，生成执行计划后发现，查询数据的方式是全表扫描； ③ time model system stats信息显示出，业务中存在一定量的hard parse。
*****************************************************************************************
步骤三：调整数据缓冲区尺寸，生成statspack报告，分析数据
思路：
通过初始报告分析，需提高库缓冲区的命中率及加索引以改变全表扫描的查询方式。而加入索
引后，对于数据的查询，会由目前直接从磁盘读取变为通过数据缓冲区读取，I/O性能会得到提
高。会对数据缓冲区有一定要求，目前数据缓冲区设为32m，由于查询业务是通过全表扫描方式，
未能体现出缓冲区大小是否对性能有影响，但发现由于目前可分配内存资源较多，先将其调整
为64m，观察是否会对性能有影响，为之后继续调优做准备。
目标：
1、调整缓冲区尺寸；
2、生成statspack报告；
3、分析报告
*****************************************************************************************
1、调整缓冲区尺寸
SQL> alter system set db_cache_size=64m;
2、生成statspack报告
SQL> @?/rdbms/admin/spreport --重新生成statspack分析报告
3、分析报告
关注点：
①buffer hit
②library hit
③Top 5 Timed Events
④造成最大物理读的sql
⑤Buffer Pool Advisory
⑥time model system stats
⑦Latch Sleep breakdown
① buffer hit、②library hit
|
direct path read |
17,621,085 |
750 |
|
|
log file parallel write |
694 |
1 |
|
|
control file parallel write |
523 |
1 |
|
|
06:22:03 ~06:52:02 |
direct path read |
17,661,174 |
754 |
|
log file parallel write |
422 |
1 |
|
|
control file parallel write |
523 |
1 |
|
|
06:52:02 ~ 07:22:01 |
direct path read |
17,596,117 |
752 |
|
log file parallel write |
534 |
1 |
|
|
control file parallel write |
522 |
1 |
|
|
07:22:01 ~ 07:52:04 |
direct path read |
17,644,217 |
754 |
|
log file parallel write |
440 |
1 |
|
|
control file parallel write |
524 |
1 |
|
|
avg |
direct path read |
17,630,648 |
|
分析：
虽然direct path read的磁盘I/O产生量仍然为最大，但是数据比较于未调整缓冲区大小之前，由平均32,438,303下降为17,630,648，仍是有显著改善的。log file parallel write、control file parallel write仍会产生部分磁盘I/O。由此，可通过全表扫描的I/O等待事件高，预判出需要在经常访问的列上加索引，以通过减少磁盘I/O产生量最大的事件的I/O来降低磁盘的I/O，进而提升性能。
④查出造成物理读最大的前几个sql语句，产生执行计划
SQL>select sql_text from v$sql where disk_reads=(select max(disk_reads) from v$sql); --查询造成最大物理读的sql语句
……
select * from emp2 where empno=4009
select * from emp2 where empno=4012
select * from emp2 where empno=4023
……
SQL> set autotrace on；
SQL> set timing on;
SQL> select * from emp2 where empno=4009； --执行一条语句，查看执行计划，可以发现方式为全表扫描,磁盘I/O较大。cost值、physical read较大
<span style="font-size:12px;"> EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------------ --------------------- ------------------ ---------- ----------- ---------- ------------ --------------
4009 cuug4008 SALESMAN 7698 03-JUN-14 1600 300 30
</span>Elapsed: 00:00:00.94
Execution Plan
----------------------------------------------------------
Plan hash value: 2941272003
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 48 | 40046 (1)| 00:08:01 |
|* 1 | TABLE ACCESS FULL| EMP2 | 1 | 48 | 40046 (1)| 00:08:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
147357 consistent gets
147349 physical reads
0 redo size
869 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
分析： 未发现造成物理读最大的sql。但发现查询语句为全表扫描，每条语句物理读都相对较大，预判需对该表添加索引。
⑤Buffer Pool Advisory
|
statistics |
||||||||
|
example |
||||||||
|
Time : 05:52:00 ~06:22:03 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 31.1 300 206 1.0 D 8 .1 1 14.4 139 95 .5 D 12 .2 1 6.5 62 43 .2 D 16 .3 2 4.4 43 29 .1 D 20 .3 2 3.0 29 20 .1 D 24 .4 3 2.4 23 16 .1 D 28 .4 3 1.8 17 12 .1 D 32 .5 4 1.7 16 11 .1 D 36 .6 4 1.5 15 10 .1 D 40 .6 5 1.3 13 9 .0 D 44 .7 5 1.0 10 7 .0 D 48 .8 6 1.0 10 7 .0 D 52 .8 6 1.0 10 7 .0 D 56 .9 7 1.0 10 7 .0 D 60 .9 7 1.0 10 7 .0 D 64 1.0 8 1.0 10 7 .0 D 68 1.1 8 1.0 10 7 .0 D 72 1.1 9 1.0 10 7 .0 D 76 1.2 9 1.0 10 7 .0 D 80 1.3 10 1.0 10 7 .0 |
相关推荐
- xshell怎么连接数据库
- MySQL数据库设计规范（仅供参考）
- SQLServer字符串查找(判断字符串是否含数字或字母)
- MySQL源码安装5.7-CentOS7
- 数据库常用架构和同步工作原理
- Apache HBase 1.7.1 发布，分布式数据库
- SQL Server 数据库分离和附加
- sqlSugar 简单封装及使用案例
- 【MySQL】-索引类型
- mysql的数据类型详解
电脑软件
热门排行
今日推荐
-
心动相机高清版
版本：v1.0.0.0611
大小：32.14MB
日期：2024-10-05
-
glaze安卓版
版本：v2.3.4
大小：12.81MB
日期：2024-10-05
-
逗拍民族服装特效正式版
版本：v10.1.0
大小：42.36MB
日期：2024-10-05
-
face甜甜相机正式版
版本：v1.3
大小：54.41MB
日期：2024-10-05
-
百变相机高清版
版本：v2.2.1
大小：17.71MB
日期：2024-10-05
-
萌印相机正式版
版本：vV1.02
大小：29.45MB
日期：2024-10-05
热门手游
-
古中国建造者官方版
版本：v1.0
大小：97.55MB
日期：2024-10-05
-
人类帮派跌落梦境完整版
版本：v1.0
大小：73.08MB
日期：2024-10-05
-
中华时代建设者正式版
版本：v1.0
大小：42.92MB
日期：2024-10-05
-
落樱2千乐之章官方版
版本：vAndroidM3839First_1.0.44
大小：913.59MB
日期：2024-10-05
-
逆袭之星途闪耀2官方版
版本：v1.0.0
大小：712.9MB
日期：2024-10-05
-
我的公众号完整版
版本：v1.0.3
大小：30.15MB
日期：2024-10-05