关闭

Oracle FAQ (一)

715人阅读 评论(0) 收藏 举报

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   
-------------   

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 true|false
  Or
  Exec dbms_session.set_sql_trace(TRUE);

  如果跟踪别人的会话,需要调用一个包
  exec dbms_system.set_sql_trace_in_session(sid,serial#,true|false)

  跟踪的信息在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 );
  可以发现执行计划恢复正常。


0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:23228次
    • 积分:340
    • 等级:
    • 排名:千里之外
    • 原创:8篇
    • 转载:15篇
    • 译文:0篇
    • 评论:1条
    文章分类