ORACLE学习笔记--性能优化一

2/9/2008来源:Oracle教程人气:7370

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  ); 可以发现执行计划恢复正常。