怎样给不同的柱子上添加不同的标准误差线(怎么给柱形图加误差线)
610
2022-05-29
oracle体系结构由实例和一组数据文件组成,实例由SGA内存区,SGA意思是共享内存区,由share pool(共享池)、data buffer(数据缓冲区)、log buffer(日志缓冲区)组成
SGA内存区的share pool是解析SQL并保存执行计划的,然后SQL根据执行计划获取数据时先看data buffer里是否有数据,没数据才从磁盘读,然后还是读到data buffer里,下次就直接读data buffer的,当SQL更新时,data buffer的数据就必须写入磁盘备份,为了保护这些数据,才有log buffer,这就是大概的原理简介
系统结构关系图如图,图来自《收获,不止SQL优化》一书:
下面介绍共享池、数据缓冲、日志缓冲方面调优的例子
共享池相关例子
未使用使用绑定变量的情况,进行一下批量写数据,在登录系统,经常用的sql是select * from sys_users where username='admin'或者什么什么的,假如有很多用户登录,就需要执行很多次这样类似的sql,能不能用一条SQL代表?意思是不需要Oracle优化器每次都解析sql获取执行计划,对于这种类似的sql是没必要的,Oracle提供了绑定变量的方法,可以用于调优sql,然后一堆sql就可以用
select * from sys_users where username=:x
1
这里用一个变量x表示,具体例子如下,
新建一张表来测试
create table t (x int);
1
不使用绑定遍历,批量写数据
begin for i in 1 .. 1000 loop execute immediate 'insert into t values('|| i ||')'; commit; end loop; end; /
1
2
3
4
5
6
7
8
9
输出
已用时间: 00: 00: 00.80
加上绑定遍历,绑定变量是用:x的形式
begin for i in 1 .. 100 loop execute immediate 'insert into t values( :x )' using i; commit; end loop; end; /
1
2
3
4
5
6
7
8
9
已用时间: 00: 00: 00.05
数据缓冲相关例子
这里介绍和数据缓存相关例子
(1) 清解析缓存
//创建一个表来测试 SQL> create table t as select * from dba_objects; 表已创建。 //设置打印行数 SQL> set linesize 1000 //设置执行计划开启 SQL> set autotrace on //打印出时间 SQL> set timing on //查询一下数据 SQL> select count(1) from t; COUNT(1) ---------- 72043 已用时间: 00: 00: 00.10 //清一下缓冲区缓存(ps:这个sql不能随便在生产环境执行) SQL> alter system flush buffer_cache; 系统已更改。 已用时间: 00: 00: 00.08 //清一下共享池缓存(ps:这个sql不能随便在生产环境执行) SQL> alter system flush shared_pool; //再次查询,发现查询快了 SQL> select count(1) from t; COUNT(1) ---------- 72043 已用时间: 00: 00: 00.12 SQL>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
日志缓冲相关例子
这里说明一下,日志关闭是可以提供性能的,不过在生生产环境还是不能随便用,只能说是一些特定创建,SQL如:
alter table [表名] nologging;
1
调优拓展知识
这些是看《收获,不止SQL优化》一书的小记
(1) 批量写数据事务问题
对于循环批量事务提交的问题,commit放在循环内和放在循环外的区别,
放在循环内,每次执行就提交一次事务,这种时间相对比较少的
begin for i in 1 .. 1000 loop execute immediate 'insert into t values('|| i ||')'; commit; end loop; end;
1
2
3
4
5
6
7
8
放在循环外,sql循环成功,再提交一次事务,这种时间相对比较多一点
begin for i in 1 .. 1000 loop execute immediate 'insert into t values('|| i ||')'; end loop; commit; end;
1
2
3
4
5
6
7
8
《收获,不止SQL优化》一书提供的脚本,用于查看逻辑读、解析、事务数等等情况:
select s.snap_date, decode(s.redosize, null, '--shutdown or end--', s.currtime) "TIME", to_char(round(s.seconds / 60, 2)) "elapse(min)", round(t.db_time / 1000000 / 60, 2) "DB time(min)", s.redosize redo, round(s.redosize / s.seconds, 2) "redo/s", s.logicalreads logical, round(s.logicalreads / s.seconds, 2) "logical/s", physicalreads physical, round(s.physicalreads / s.seconds, 2) "phy/s", s.executes execs, round(s.executes / s.seconds, 2) "execs/s", s.parse, round(s.parse / s.seconds, 2) "parse/s", s.hardparse, round(s.hardparse / s.seconds, 2) "hardparse/s", s.transactions trans, round(s.transactions / s.seconds, 2) "trans/s" from (select curr_redo - last_redo redosize, curr_logicalreads - last_logicalreads logicalreads, curr_physicalreads - last_physicalreads physicalreads, curr_executes - last_executes executes, curr_parse - last_parse parse, curr_hardparse - last_hardparse hardparse, curr_transactions - last_transactions transactions, round(((currtime + 0) - (lasttime + 0)) * 3600 * 24, 0) seconds, to_char(currtime, 'yy/mm/dd') snap_date, to_char(currtime, 'hh24:mi') currtime, currsnap_id endsnap_id, to_char(startup_time, 'yyyy-mm-dd hh24:mi:ss') startup_time from (select a.redo last_redo, a.logicalreads last_logicalreads, a.physicalreads last_physicalreads, a.executes last_executes, a.parse last_parse, a.hardparse last_hardparse, a.transactions last_transactions, lead(a.redo, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_redo, lead(a.logicalreads, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_logicalreads, lead(a.physicalreads, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_physicalreads, lead(a.executes, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_executes, lead(a.parse, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_parse, lead(a.hardparse, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_hardparse, lead(a.transactions, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_transactions, b.end_interval_time lasttime, lead(b.end_interval_time, 1, null) over(partition by b.startup_time order by b.end_interval_time) currtime, lead(b.snap_id, 1, null) over(partition by b.startup_time order by b.end_interval_time) currsnap_id, b.startup_time from (select snap_id, dbid, instance_number, sum(decode(stat_name, 'redo size', value, 0)) redo, sum(decode(stat_name, 'session logical reads', value, 0)) logicalreads, sum(decode(stat_name, 'physical reads', value, 0)) physicalreads, sum(decode(stat_name, 'execute count', value, 0)) executes, sum(decode(stat_name, 'parse count (total)', value, 0)) parse, sum(decode(stat_name, 'parse count (hard)', value, 0)) hardparse, sum(decode(stat_name, 'user rollbacks', value, 'user commits', value, 0)) transactions from dba_hist_sysstat where stat_name in ('redo size', 'session logical reads', 'physical reads', 'execute count', 'user rollbacks', 'user commits', 'parse count (hard)', 'parse count (total)') group by snap_id, dbid, instance_number) a, dba_hist_snapshot b where a.snap_id = b.snap_id and a.dbid = b.dbid and a.instance_number = b.instance_number order by end_interval_time)) s, (select lead(a.value, 1, null) over(partition by b.startup_time order by b.end_interval_time) - a.value db_time, lead(b.snap_id, 1, null) over(partition by b.startup_time order by b.end_interval_time) endsnap_id from dba_hist_sys_time_model a, dba_hist_snapshot b where a.snap_id = b.snap_id and a.dbid = b.dbid and a.instance_number = b.instance_number and a.stat_name = 'DB time') t where s.endsnap_id = t.endsnap_id order by s.snap_date, time desc;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
Oracle SQL
版权声明:本文内容由网络用户投稿,版权归原作者所有,本站不拥有其著作权,亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容,请联系我们jiasou666@gmail.com 处理,核实后本网站将在24小时内删除侵权内容。