商务合作:179001057@qq.com

Oracle FAQ (一)

技术2022-05-11  0


某平台价值19860元的编程课程资料免费领取【点我领取】


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 02 13 24 35 46 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)