读盖国强他们牛人编写的<oracle dba 性能书>系列的读后:
1 查询v$session视图,获取当前进程:
select sid,serial#,username from v$session where username is not null;
比如出来sid serial# username
7 286
然后对相应的应用会话用sql_trace跟踪
exec dbms_system.set_sql_trace_in_session(7,286,true)
然后再关闭
exec dbms_system.set_sql_trace_in_session(7,286,false)
2 比如有ora-01438错误,则通过errstack进行后台跟踪,获得详细信息
alter system set events='1438 trace name errorstack forevel,level 3';
然后执行出错的过程,再关闭:
alter system set events='1438 trace name errorstack off';
3 书上说,oracle 11g中,shared pool中每个subpool至少为512MB呀,所以内存还是要越大
越好
4 logminer的使用,具体见
http://blog.csdn.net/xujinyang/article/details/7031968一文,写的不错
5 oracle的in值里,不允许超过1000个值,即select * from id in (xxxxxxxx)
6 oracle的fetchsize默认为10,当需要从服务端拿大量数据时,可以加大这个参数,增
加性能,不要大于100,大于40好点
7 数据库访问的漏斗法则
先到后顺序:先减少数据访问(减少磁盘访问) 2 返回更少的数据(减少
网络传输) 3 减少交互次数 4 减少CPU开销 5 增加更多资源
8 查询当前数据库连接的程序,机器,SID,EVENT
select sid,serial#,program,machine,sql_id,event from v$session where
type='USER' and status='ACTIVE';
9 oracle flash 闪回空间:
增加闪回时间,增加到3小时
alter system set undo_retention=10800 scope=both;
10 同义词的好处
1)隐藏一个数据库对象的名字和拥有者 2)和视图类似实现更精细控制
3)简化SQL语句
create public synonym sales for xxx.xxxx;
11 查看一个SQL的真实执行计划
首先找出:
select hash_value,child_number,sql_text from v$sql where sql_text like
'%...%'
select * from table(
dbms_xplan.display_cursor('hash_value','child_number',advanced'));
如果要在oracle 9i下获得其执行计划,使用
http://www.laoxiong.net/oracle9i_display_CURSOR.html,用法
set serverouput on size 100000
比如查出oracle中os端top显示的spid为121345
exec printsql(123145,'SPID')
执行计划的执行顺序为:
先从计划开头一直往右看,直到最右边并列的代码部分,如果见到并列的,就从上往
下看,对于并列的步骤,靠上的先执行,对于不并列的步骤,靠右的先执行
10g/11g里执行计划的增强:
扩展的xplan包:
select * from table(xplan.display_cursor)
,代码在http://tomszrp.itpub.net/get/11835/xplan.rar
可以下载,下载后,安装
@xplan.sql
新增加了order列;以及E-ROWS估计行数,A-ROWS实际行数
select /*+ gather_plan_statistics */ ........
select from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
12 oracle 中的bind peeking问题:
使用绑定变量可以减少SQL PARSE,但是使用绑定变量有一个不好的地方,就是对于访
问具有倾斜的列,可能使用错误的执行计划。在Oracle 9i之前,如果WHERE 条件里面全
部使用绑定变量,那么只能使用固定的选择性参数来确定执行计划。
=操作和>=操作的选择性为5%,范围扫描的选择性为25%。缺省值的方式可能生成不好的执
行计划。所以Oracle 9i就出现了一个新的技术,bind peeking。什么是bind peeking呢
?当SQL第一次执行的时候,优化器会根据绑定变量来确定执行计划(如果存在柱状图)
。BIND PEEKING只有当该SQL第一次执行的时候,进行HARD PARSE的时候才进行,第二次
调用该SQL,就不会再次进行BIND PEEKING。这种情况下,就存在另外一个风险,如果某
个列的倾斜性很厉害,那么使用BIND PEEKING就是不安全的,因为不同的参数代入,只能
走第一次执行时的执行计划,那么执行计划就像掷色子一样,要靠运气了。碰到这种情况
,应用就不应该使用绑定变量,而应该改为直接值了。
这时可以使用刷新一下共享池alter system flush shared_pool;
或者alter session set "_optim_peek_user_binds"=false;
我们可以通过隐含的参数来调整数据库默认的bind peeking行为:
_OPTIM_PEEK_USER_BINDS。 如果我们想关闭Bind Variable Peeking,我们可以设置该参
数为 False 即可。
SQL>alter session set "_optim_peek_user_binds"=false
使用了Bind Var能提高性能主要是因为这样做可以尽量避免不必要的硬分析(Hard Parse)
而节约了时间,同时节约了大量的CPU资源。
当一个Client提交一条Sql给Oracle后,Oracle 首先会对其进行解析(Parse),然后
将解析结果提交给优化器(Optimiser)来进行优化而取得Oracle认为的最优的Query Plan
,然后再按照这个最优的Plan来执行这个Sql语句(当然在这之中如果只需要软解析的话会
少部分步骤)。
当Oracle接到 Client提交的Sql后会首先在共享池(Shared Pool)里面去查找是否有之前
已经解析好的与刚接到的这一个Sql完全相同的Sql(注意这里说的是完全相同,既要求语
句上的字符级别的完全相同,又要求涉及的对象也必须完全相同)。当发现有相同的以后
解析器就不再对新的Sql在此解析而直接用之前解析好的结果了。这里就节约了解析时间
以及解析时候消耗的CPU资源。尤其是在OLTP中运行着的大量的短小Sql,效果就会比较明
显了。因为一条两条Sql的时间可能不会有多少感觉,但是当量大了以后就会有比较明显
的感觉了。
但是,使用绑定变量的一个缺点是,给出的执行计划并不一定就是SQL在真正应用程序里
所使用的执行计划。这时我们就可以通过 event 10053 事件来查看。
13 SQL PROFILE:可以锁定或者稳定执行计划,在不能修改应用中的SQL
的情况下使用SQL语句按指定的执行计划执行
可以使用sql tuning advisor来尝试这个SQL;
dbms_sqltune.create_tuning_task
,具体参考oracle dba手记3 ,P141页
14 使用 SQL PROFILE稳定SQL语句的执行计划:
select * from table(dbms_xplan.display_cursor(null,null,'outline'));
然后使用工具sqlt.zip,从sharedpool,awr中获得sql执行时产生的多个
执行计划的统计信息,然后输入正确需要稳定的执行计划的HASH值,再产生另外
一个脚本,运行这个脚本,会产生对应的sql profile.
但sql profile适合于某些个别范围小的SQL,影响范围小,如果SQL发生改变,需要
重新生成sql profile.
15 关于NULL:
除了is null,is not null外,对null的任何操作结果还都是null;
要注意null的相关and和or操作
AND操作:null and true=null,null and false=false,null and null=null
or: null or true=true null or false=false,null or null=null
not null的值依然是null,因为null表示不确定,无论null为true还是FALSE,始终最
后不确定,为null.
注意的是:oracle 把null当作varchar2来处理
' '空字符串其实是null的字符类型的表现格式。
btree索引是不存储null值的,而位图索引是存储NULL值的,而如果复合索引中,如果
包含null值,也是可以的,只要有一个索引列不为空;如果是单列索引,IS NULL是肯定
不能使用索引的了,但IS NOT NULL可以。
大多数函数,如果输入为NULL,则输出也为null.NVL,NVL2,DECODE例外,它们在输入参
数为NULL的时候,结果可能不是NULL,如果这些输入参数均为NULL,则肯定为NULL,如果
不全为NULL,结果可能不是NULL。
聚集函数不会处理null值,不管为max,min,avg还是sum.
16 可以使用strace来跟踪listner,
先使用ps -ef|grep tnslsnr
得到的是父进程ppid=1的监听进程号
然后使用strace -fr -o /tmp/lsnr.out -p <listner pid监听进程号>
然后重新连接,再运行后CTRL+C停止STRACE运行。
1 查询v$session视图,获取当前进程:
select sid,serial#,username from v$session where username is not null;
比如出来sid serial# username
7 286
然后对相应的应用会话用sql_trace跟踪
exec dbms_system.set_sql_trace_in_session(7,286,true)
然后再关闭
exec dbms_system.set_sql_trace_in_session(7,286,false)
2 比如有ora-01438错误,则通过errstack进行后台跟踪,获得详细信息
alter system set events='1438 trace name errorstack forevel,level 3';
然后执行出错的过程,再关闭:
alter system set events='1438 trace name errorstack off';
3 书上说,oracle 11g中,shared pool中每个subpool至少为512MB呀,所以内存还是要越大
越好
4 logminer的使用,具体见
http://blog.csdn.net/xujinyang/article/details/7031968一文,写的不错
5 oracle的in值里,不允许超过1000个值,即select * from id in (xxxxxxxx)
6 oracle的fetchsize默认为10,当需要从服务端拿大量数据时,可以加大这个参数,增
加性能,不要大于100,大于40好点
7 数据库访问的漏斗法则
先到后顺序:先减少数据访问(减少磁盘访问) 2 返回更少的数据(减少
网络传输) 3 减少交互次数 4 减少CPU开销 5 增加更多资源
8 查询当前数据库连接的程序,机器,SID,EVENT
select sid,serial#,program,machine,sql_id,event from v$session where
type='USER' and status='ACTIVE';
9 oracle flash 闪回空间:
增加闪回时间,增加到3小时
alter system set undo_retention=10800 scope=both;
10 同义词的好处
1)隐藏一个数据库对象的名字和拥有者 2)和视图类似实现更精细控制
3)简化SQL语句
create public synonym sales for xxx.xxxx;
11 查看一个SQL的真实执行计划
首先找出:
select hash_value,child_number,sql_text from v$sql where sql_text like
'%...%'
select * from table(
dbms_xplan.display_cursor('hash_value','child_number',advanced'));
如果要在oracle 9i下获得其执行计划,使用
http://www.laoxiong.net/oracle9i_display_CURSOR.html,用法
set serverouput on size 100000
比如查出oracle中os端top显示的spid为121345
exec printsql(123145,'SPID')
执行计划的执行顺序为:
先从计划开头一直往右看,直到最右边并列的代码部分,如果见到并列的,就从上往
下看,对于并列的步骤,靠上的先执行,对于不并列的步骤,靠右的先执行
10g/11g里执行计划的增强:
扩展的xplan包:
select * from table(xplan.display_cursor)
,代码在http://tomszrp.itpub.net/get/11835/xplan.rar
可以下载,下载后,安装
@xplan.sql
新增加了order列;以及E-ROWS估计行数,A-ROWS实际行数
select /*+ gather_plan_statistics */ ........
select from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
12 oracle 中的bind peeking问题:
使用绑定变量可以减少SQL PARSE,但是使用绑定变量有一个不好的地方,就是对于访
问具有倾斜的列,可能使用错误的执行计划。在Oracle 9i之前,如果WHERE 条件里面全
部使用绑定变量,那么只能使用固定的选择性参数来确定执行计划。
=操作和>=操作的选择性为5%,范围扫描的选择性为25%。缺省值的方式可能生成不好的执
行计划。所以Oracle 9i就出现了一个新的技术,bind peeking。什么是bind peeking呢
?当SQL第一次执行的时候,优化器会根据绑定变量来确定执行计划(如果存在柱状图)
。BIND PEEKING只有当该SQL第一次执行的时候,进行HARD PARSE的时候才进行,第二次
调用该SQL,就不会再次进行BIND PEEKING。这种情况下,就存在另外一个风险,如果某
个列的倾斜性很厉害,那么使用BIND PEEKING就是不安全的,因为不同的参数代入,只能
走第一次执行时的执行计划,那么执行计划就像掷色子一样,要靠运气了。碰到这种情况
,应用就不应该使用绑定变量,而应该改为直接值了。
这时可以使用刷新一下共享池alter system flush shared_pool;
或者alter session set "_optim_peek_user_binds"=false;
我们可以通过隐含的参数来调整数据库默认的bind peeking行为:
_OPTIM_PEEK_USER_BINDS。 如果我们想关闭Bind Variable Peeking,我们可以设置该参
数为 False 即可。
SQL>alter session set "_optim_peek_user_binds"=false
使用了Bind Var能提高性能主要是因为这样做可以尽量避免不必要的硬分析(Hard Parse)
而节约了时间,同时节约了大量的CPU资源。
当一个Client提交一条Sql给Oracle后,Oracle 首先会对其进行解析(Parse),然后
将解析结果提交给优化器(Optimiser)来进行优化而取得Oracle认为的最优的Query Plan
,然后再按照这个最优的Plan来执行这个Sql语句(当然在这之中如果只需要软解析的话会
少部分步骤)。
当Oracle接到 Client提交的Sql后会首先在共享池(Shared Pool)里面去查找是否有之前
已经解析好的与刚接到的这一个Sql完全相同的Sql(注意这里说的是完全相同,既要求语
句上的字符级别的完全相同,又要求涉及的对象也必须完全相同)。当发现有相同的以后
解析器就不再对新的Sql在此解析而直接用之前解析好的结果了。这里就节约了解析时间
以及解析时候消耗的CPU资源。尤其是在OLTP中运行着的大量的短小Sql,效果就会比较明
显了。因为一条两条Sql的时间可能不会有多少感觉,但是当量大了以后就会有比较明显
的感觉了。
但是,使用绑定变量的一个缺点是,给出的执行计划并不一定就是SQL在真正应用程序里
所使用的执行计划。这时我们就可以通过 event 10053 事件来查看。
13 SQL PROFILE:可以锁定或者稳定执行计划,在不能修改应用中的SQL
的情况下使用SQL语句按指定的执行计划执行
可以使用sql tuning advisor来尝试这个SQL;
dbms_sqltune.create_tuning_task
,具体参考oracle dba手记3 ,P141页
14 使用 SQL PROFILE稳定SQL语句的执行计划:
select * from table(dbms_xplan.display_cursor(null,null,'outline'));
然后使用工具sqlt.zip,从sharedpool,awr中获得sql执行时产生的多个
执行计划的统计信息,然后输入正确需要稳定的执行计划的HASH值,再产生另外
一个脚本,运行这个脚本,会产生对应的sql profile.
但sql profile适合于某些个别范围小的SQL,影响范围小,如果SQL发生改变,需要
重新生成sql profile.
15 关于NULL:
除了is null,is not null外,对null的任何操作结果还都是null;
要注意null的相关and和or操作
AND操作:null and true=null,null and false=false,null and null=null
or: null or true=true null or false=false,null or null=null
not null的值依然是null,因为null表示不确定,无论null为true还是FALSE,始终最
后不确定,为null.
注意的是:oracle 把null当作varchar2来处理
' '空字符串其实是null的字符类型的表现格式。
btree索引是不存储null值的,而位图索引是存储NULL值的,而如果复合索引中,如果
包含null值,也是可以的,只要有一个索引列不为空;如果是单列索引,IS NULL是肯定
不能使用索引的了,但IS NOT NULL可以。
大多数函数,如果输入为NULL,则输出也为null.NVL,NVL2,DECODE例外,它们在输入参
数为NULL的时候,结果可能不是NULL,如果这些输入参数均为NULL,则肯定为NULL,如果
不全为NULL,结果可能不是NULL。
聚集函数不会处理null值,不管为max,min,avg还是sum.
16 可以使用strace来跟踪listner,
先使用ps -ef|grep tnslsnr
得到的是父进程ppid=1的监听进程号
然后使用strace -fr -o /tmp/lsnr.out -p <listner pid监听进程号>
然后重新连接,再运行后CTRL+C停止STRACE运行。