Oracle FAQ (一)

转载 2006年06月09日 09:38:00

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


数据库学习FAQ

数据库学习FAQ 简述数据、数据库、数据库管理系统、数据库系统的含义及其联系?数据:数据是信息的符号记录。数据是数据库处理和研究的对象 数据库:长期存储在计算机内,有组织、可共享的数据集合。数据按一定...
  • pupilxmk
  • pupilxmk
  • 2016年05月25日 08:59
  • 487

FAQ = Fuck you?

剛才在看Denyhosts的FAQ的時候忽然想到FAQ的讀音跟fuck you似乎是一樣的?證明發下∵    F:[f]       A:[ʌ]       Q:[kju:]∴    FAQ=[fʌk...
  • hodrag
  • hodrag
  • 2009年04月22日 08:24
  • 2181

运维路上的FAQ

遇到的看见的,长期更新~~~ 1.linux乱码问题     [root@vps software]# vi  /etc/sysconfig/i18n     LANG='zh_...
  • u010718976
  • u010718976
  • 2017年01月10日 11:00
  • 206

discuz (dzx) FAQ帮助页面 图文并茂版

使 discuz (dzx) FAQ帮助页面 图文并茂版
  • zhangxueleishamo
  • zhangxueleishamo
  • 2017年06月19日 16:05
  • 140

TCP FAQ

什么是MSS(Maximum Segment Size)? TCP用MSS选项允许接收方指定它可以接收的最大段长度。传输的双方通过这个选项协 商出一个上方都接受的最大段长。这个值太大或太小都不好。太...
  • dolphin98629
  • dolphin98629
  • 2014年05月26日 15:42
  • 382

FAQ手册

1、怎么样查询特殊字符,如通配符%与_ select * from table where name like 'A\_%' escape '\' 2、如何插入单引号到数据库表中 [A]可以用A...
  • haoxiaoyan
  • haoxiaoyan
  • 2013年11月25日 16:33
  • 952

什么是FAQ,它有何作用

FAQ就是Frequently Asked Questions(常见问题), 由于这些问题出现的比较普遍,一般都推荐用户先看看FAQ, 看自己的问题是否符合FAQ中的情况,如果符合的话FAQ中 ...
  • q1054261752
  • q1054261752
  • 2015年12月25日 16:45
  • 355

FAQ整理(不断添加中)

FAQ1: VC7中,如何在非MFC中如何使用CString -- 包含头文件 atlstr.h 即可,需要用到ATLFAQ2: 检查某键是否被按下-- if( GetKeyState(VK_LSHI...
  • lbird
  • lbird
  • 2007年02月09日 12:04
  • 1196

开源FAQ系统:KnowledgebasePublisher

 文章来源于http://www.lupaworld.com  KnowledgebasePublisher是一个开源FAQ系统,也可做为一个用于发布文章的内容管理器。提供问题分类,术语表,强大的WY...
  • flying_hawk
  • flying_hawk
  • 2009年08月26日 20:29
  • 1319

新版FAQ的设计

准备不变的地方:1、仍然是已经解帖的帖子才可以整理FAQ;2、仍然是任何人都可以提交、整理FAQ。3、只有审核通过的FAQ,才可以阅读。4、未审核通过的FAQ,FAQ管理者和提交者可以对FAQ进行修改...
  • ghj1976
  • ghj1976
  • 2005年09月26日 16:53
  • 1934
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Oracle FAQ (一)
举报原因:
原因补充:

(最多只允许输入30个字)