分享
 
 
 

ORACLE学习笔记--性能优化(1)

王朝oracle·作者佚名  2008-05-31
窄屏简体版  字體: |||超大  

1. 查询正在执行语句的执行计划(也就是实际语句执行计划)

select * from v$sql_plan where hash_value = (select sql_hash_value from v$session where sid = 1111);

其中id和parent_id表示了执行数的结构,数值最大的为最先执行

比如

ID PARENT_ID

-------------

0

1 0

2 1

3 2

4 3

5 4

6 3

则执行计划树为

0

1

2

3

6 4

5

2.如何设置自动跟踪

用system登录

执行$Oracle_HOME/rdbms/admin/utlXPlan.sql创建计划表

执行$ORACLE_HOME/sqlplus/admin/plustrce.sql创建plustrace角色

假如想计划表让每个用户都能使用,则

SQL>create public synonym plan_table for plan_table;

SQL> grant all on plan_table to public;

假如想让自动跟踪的角色让每个用户都能使用,则

SQL> grant plustrace to public;

通过如下语句开启/停止跟踪

SET AUTOTRACE ON OFF ON EXPLAIN ON STATISTICS TRACEONLY TRACEONLY EXPLAIN

3.如何跟踪自己的会话或者是别人的会话

跟踪自己的会话很简单

Alter session set sql_trace truefalse

Or

Exec dbms_session.set_sql_trace(TRUE);

假如跟踪别人的会话,需要调用一个包

exec dbms_system.set_sql_trace_in_session(sid,serial#,truefalse)

跟踪的信息在user_dump_dest 目录下可以找到或通过如下脚本获得文件名称(适用于Win环境,假如是unix需要做一定修改)

SELECT p1.value'\'p2.value'_ora_'p.spid'.ora' filename

FROM

v$process p,

v$session s,

v$parameter p1,

v$parameter p2

WHERE p1.name = 'user_dump_dest'

AND p2.name = 'db_name'

AND p.addr = s.paddr

AND s.audsid = USERENV ('SESSIONID')

最后,可以通过Tkprof来解析跟踪文件,如

Tkprof 原文件 目标文件 sys=n

4.怎么设置整个数据库系统跟踪

其实文档上的alter system set sql_trace=true是不成功的,但是可以通过设置事件来完成这个工作,作用相等

alter system set events

'10046 trace name context forever,level 1';

假如关闭跟踪,可以用如下语句

alter system set events

'10046 trace name context off';

其中的level 1与上面的8都是跟踪级别

level 1:跟踪SQL语句,等于sql_trace=true

level 4:包括变量的具体信息

level 8:包括等待事件

level 12:包括绑定变量与等待事件

5.怎么样根据OS进程快速获得DB进程信息与正在执行的语句

有些时候,我们在OS上操作,象TOP之后我们得到的OS进程,怎么快速根据OS信息获得DB信息呢?

我们可以编写如下脚本:

$more whoit.sh

#!/bin/sh

sqlplus /nolog 100,cascade=> TRUE);

dbms_stats.gather_table_stats(User,TableName,degree => 4,cascade => true);

这是对命令与工具包的一些总结

<1>、对于分区表,建议使用DBMS_STATS,而不是使用Analyze语句。

a) 可以并行进行,对多个用户,多个Table

b) 可以得到整个分区表的数据和单个分区的数据。

c) 可以在不同级别上Compute Statistics:单个分区,子分区,全表,所有分区

d) 可以倒出统计信息

e) 可以用户自动收集统计信息

<2>、DBMS_STATS的缺点

a) 不能Validate StrUCture

b) 不能收集CHAINED ROWS, 不能收集CLUSTER TABLE的信息,这两个仍然需要使用Analyze语句。

c) DBMS_STATS 默认不对索引进行Analyze,因为默认Cascade是False,需要手工指定为True

<3>、对于oracle 9里面的External Table,Analyze不能使用,只能使用DBMS_STATS来收集信息。

6.怎么样快速重整索引

通过rebuild语句,可以快速重整或移动索引到别的表空间

rebuild有重建整个索引数的功能,可以在不删除原始索引的情况下改变索引的存储参数

语法为

alter index index_name rebuild tablespace ts_name

storage(......);

假如要快速重建整个用户下的索引,可以用如下脚本,当然,需要根据你自己的情况做相应修改

SQL> set heading off

SQL> set feedback off

SQL> spool d:\index.sql

SQL> SELECT 'alter index ' index_name ' rebuild '

'tablespace INDEXES storage(initial 256K next 256K pctincrease 0);'

FROM all_indexes

WHERE ( tablespace_name != 'INDEXES'

OR next_extent != ( 256 * 1024 )

)

AND owner = USER

SQL>spool off

另外一个合并索引的语句是

alter index index_name coalesce

这个语句仅仅是合并索引中同一级的leaf block,消耗不大,对于有些索引中存在大量空间浪费的情况下,有一些作用。

7.如何使用Hint提示

在select/delete/update后写/*+ hint */

如 select /*+ index(TABLE_NAME INDEX_NAME) */ col1...

注重/*和+之间不能有空格,如用hint指定使用某个索引

select /*+ index(cbotab) */ col1 from cbotab;

select /*+ index(cbotab cbotab1) */ col1 from cbotab;

select /*+ index(a cbotab1) */ col1 from cbotab a;

其中

TABLE_NAME是必须要写的,且假如在查询中使用了表的别名,在hint也要用表的别名来代替表名;

INDEX_NAME可以不必写,Oracle会根据统计值选一个索引;

假如索引名或表名写错了,那这个hint就会被忽略;

8.怎么样快速复制表或者是插入数据

快速复制表可以指定Nologging选项

如:Create table t1 nologging

as select * from t2;

快速插入数据可以指定append提示,但是需要注重noarchivelog模式下,默认用了append就是nologging模式的。 在archivelog下,需要把表设置程Nologging模式。

如insert /*+ append */ into t1

select * from t2

注重:假如在9i环境中并设置了FORCE LOGGING,则以上操作是无效的,并不会加快,当然,可以通过如下语句设置为NO FORCE LOGGING。

Alter database no force logging;

是否开启了FORCE LOGGING,可以用如下语句查看

SQL> select force_logging from v$database;

9.怎么避免使用特定索引

在很多时候,Oracle会错误的使用索引而导致效率的明显下降,我们可以使用一点点技巧而避免使用不该使用的索引,如:

表test,有字段a,b,c,d,在a,b,c上建立联合索引inx_a(a,b,c),在b上单独建立了一个索引Inx_b(b)。

在正常情况下,where a=? and b=? and c=?会用到索引inx_a,where b=?会用到索引inx_b,但是,where a=? and b=? and c=? group by b会用到哪个索引呢?在分析数据不正确(很长时间没有分析)或根本没有分析数据的情况下,oracle往往会使用索引inx_b。通过执行计划的分析,这个索引的使用,将大大耗费查询时间。

当然,我们可以通过如下的技巧避免使用inx_b,而使用inx_a。

where a=? and b=? and c=? group by b'' --假如b是字符

where a=? and b=? and c=? group by b+0 --假如b是数字

通过这样简单的改变,往往可以是查询时间提交很多倍

当然,我们也可以使用no_index提示,相信很多人没有用过,也是一个不错的方法:

select /*+ no_index(t,inx_b) */ * from test t

where a=? and b=? and c=? group by b

举例:

本来在CM_USER上有索引IDX_CM_USER4(ACC_ID)和IDX_CM_USER8(BILL_ID),可是执行如下语句的时候很慢。

select * from CM_USER where acc_id =1200007175

and user_status>0 and bill_id like '13%' order by acc_id,bill_id

用explain分析,发现执行计划是用IDX_CM_USER8.如下查询

select * from user_indexes where table_name ='CM_USER' 发现IDX_CM_USER8没有分析过。

用下面语句执行计划改变

select /*+INDEX(CM_USER IDX_CM_USER4)*/* from CM_USER where acc_id =1200007175 and user_status>0 and bill_id like '13%' order by acc_id,bill_id

或者分析索引

exec dbms_stats.gather_index_stats(ownname => 'QACS1',indname => 'IDX_CM_USER8',estimate_percent => 5 );

可以发现执行计划恢复正常。

10.Oracle什么时候会使用跳跃式索引扫描

这是9i的一个新特性跳跃式索引扫描(Index Skip Scan).

例如表有索引index(a,b,c),当查询条件为where b=?的时候,可能会使用到索引index(a,b,c),如,执行计划中出现如下计划:

INDEX (SKIP SCAN) OF 'TEST_IDX' (NON-UNIQUE)

Oracle的优化器(这里指的是CBO)能对查询应用Index Skip Scans至少要有几个条件:

<1> 优化器认为是合适的。

<2> 索引中的前导列的唯一值的数量能满足一定的条件(如重复值很多

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
2023年上半年GDP全球前十五强
 百态   2023-10-24
美众议院议长启动对拜登的弹劾调查
 百态   2023-09-13
上海、济南、武汉等多地出现不明坠落物
 探索   2023-09-06
印度或要将国名改为“巴拉特”
 百态   2023-09-06
男子为女友送行,买票不登机被捕
 百态   2023-08-20
手机地震预警功能怎么开?
 干货   2023-08-06
女子4年卖2套房花700多万做美容:不但没变美脸,面部还出现变形
 百态   2023-08-04
住户一楼被水淹 还冲来8头猪
 百态   2023-07-31
女子体内爬出大量瓜子状活虫
 百态   2023-07-25
地球连续35年收到神秘规律性信号,网友:不要回答!
 探索   2023-07-21
全球镓价格本周大涨27%
 探索   2023-07-09
钱都流向了那些不缺钱的人,苦都留给了能吃苦的人
 探索   2023-07-02
倩女手游刀客魅者强控制(强混乱强眩晕强睡眠)和对应控制抗性的关系
 百态   2020-08-20
美国5月9日最新疫情:美国确诊人数突破131万
 百态   2020-05-09
荷兰政府宣布将集体辞职
 干货   2020-04-30
倩女幽魂手游师徒任务情义春秋猜成语答案逍遥观:鹏程万里
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案神机营:射石饮羽
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案昆仑山:拔刀相助
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案天工阁:鬼斧神工
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案丝路古道:单枪匹马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:与虎谋皮
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:李代桃僵
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:指鹿为马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:小鸟依人
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:千金买邻
 干货   2019-11-12
 
推荐阅读
 
 
 
>>返回首頁<<
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有