Oracle--DBA总结

pennate

　　1. 数据字典
1.1查询数据字典。
dictionary -----该字典包含所有的字典信息SQL>select * from dict where instr(comments,'index')>0; -----查询所有有关index的数据字典名称。1.2设置查询结果格式。
Set heading on/off：打开/关闭查询结果表头的显示，默认为ON。Set feedback on/off：打开/关闭查询结果中返回行数的显示，默认为ON。Set echo on/off：打开/关闭命令的回显，默认为ON。Set time on/off：打开/关闭时间显示，默认为OFF。2.系统I/O调整
2.1查询磁盘I/O情况。
#select d.tablespace_name tablespace,d.file_name,f.phyrds,f.phywrtsfrom v$filestat f,dba_data_files dwhere f.file#=d.file_id;           ---------查询数据文件的读写情况#db_file_multiblock_read_count    ---------一次性读入块的数量，可以在session级别设置。 3. SQL调整
3.1 关于sql的初始化参数调整。
#cursor_sharing   -------是否会自动匹配变量参数| exact（完全不会）------该参数如果不配合绑定变量会使得系统开销很大| similar（智能匹配） -------推荐使用，但性能不如exact加绑定变量好|force（强制） -------会产生BUG|#查看sqlarea中的sql语句来断定其sql所执行的次数（字段EXECUTIONS）。其动态性能视图为v$sqlarea3.2 打开游标和使用绑定变量的方法。
游标：variable x refcursor  ----定义x为一个游标      Begin      Open :x for select XXXXXXX from XXXXXXXX;      End;      /      (如果你想读取游标中的数，可以使用print x)绑定变量：sqlplus中可以这样绑定SQL> var n varchar2(30);SQL>  exec :n := 'IM_USER';PL/SQL 过程已成功完成。SQL>  select * from tab where tname = :n;TNAME                          TABTYPE  CLUSTERID------------------------------ ------- ----------IM_USER                        SYNONYM3.3 sql_trace使用。
#打开跟踪文件Alter session set sql_trace=true;grant>自己的跟踪文件的位置（要使用下面的语句必须具有可查找动态性能视图的权限）select c.value||'/'||d.instance_name||'_ora_'||a.spid||'.trc' tracefrom v$process a,v$session b,v$parameter c,v$instance dwhere a.addr=b.paddr and b.audsid=userenv('sessionid') and c.name ='user_dump_dest';跟踪别人的跟踪文件的位置select c.value||'/'||d.instance_name||'_ora_'||a.spid||'.trc' tracefrom v$process a,v$session b,v$parameter c,v$instance dwhere a.addr=b.paddr and b.audsid=&a and c.name ='user_dump_dest';b.audsid的值指定了所跟踪的session 可以由select SID,SERIAL#,AUDSID,username from v$session;得出#加标记Alter  session  set  tracefile_identifier=’你想加的标记名’;再打开跟踪文件#跟踪别人的sql如果跟踪别人的会话，需要调用一个包　　exec dbms_system.set_sql_trace_in_session(sid,serial#,true|false)
跟踪的信息在user_dump_dest 目录下可以找到 可以通过Tkprof来解析跟踪文件，如　　Tkprof
原文件 目标文件 sys=n 3.4 10046tarce
10046是sql_trace的一个提升，它能够提供更为详细的信息alter session set events '10046 trace name context forever, level xx';（必须确保timed_statistics为true）10046event的追踪级别大致有：　　level 1
：跟踪sql语句，包括解析、执行、提取、提交和回滚等。　　level 4
：包括变量的周详信息　　level 8
：包括等待事件　　level 12
：包括绑定变量和等待事件关闭命令为alter session set events '10046 trace name context off';#查看当前10046等级（11g貌似有问题，待研究）Grant execute on dbms_system to xxxxx; set serveroutput ondeclare i_event number;beginsys.dbms_system.read_ev(10046,i_event); dbms_output.put_line('the session sql_trace level is:'||i_event);end; /#如果要监控别人的在拥有dbms_system.set_ev权限的用户上运行exec dbms_system.set_ev(SID, SERIAL#,10046,等级,'');（SID, SERIAL#在v$session上获得）等级为（1，4，8，12，0）---0为关闭。所跟踪产生的文件为被跟踪session的tracefile.3.5关于sql的执行计划。
SET AUTOTRACE OFF ---------------- 不生成AUTOTRACE 报告，这是缺省模式　　SET AUTOTRACE ON EXPLAIN ------ AUTOTRACE
只显示优化器执行路径报告　　SET AUTOTRACE ON STATISTICS --
只显示执行统计信息　　SET AUTOTRACE ON -----------------
包含执行计划和统计信息　　SET AUTOTRACE TRACEONLY ------
同set autotrace on，但是不显示查询输出SET AUTOTRACE TRACEONLY EXPLAIN  -----只显示执行计划，并不执行语句。AUTOTRACE Statistics常用列解释db block gets    -----从buffer cache中读取的block的数量consistent gets从buffer cache -----中读取的undo数据的block的数量physical reads -----从磁盘读取的block的数量redo>sorts (memory) -----在内存执行的排序量sorts (disk) -----在磁盘上执行的排序量3.6 tkprof解读
parse(分析)：在共享池中找到该查询（软分析）或者创建该查询的新计划（硬分析）   execute(执行)：执行查询的所有工作   fetch(提取)：显示select的提取工作，对于update，则没有内容   count(计数):执行的次数   cpu:此阶段cpu的耗时，以毫秒为单位   elapsed(占用时间):挂钟时间，如果大于cpu时间，则有等待时间   disk(磁盘)：执行物理I/O的次数   QUERY(查询):检索一致性执行的I/O次数   CURRENT(当前)：到当前多执行的逻辑I/O次数   ROW:此阶段被处理或者受到影响的行   如果一个UPDATE语句EXECUTE的QUERY,CURRENT，ROWS分别为2000 1000 500,表示这个语句访问了2000个块找到需要UPDATE的行记录，在UPDATE的时候只访问了1000个块，一共更新了500行。如果只获取很少的数据，而要访问了大量的块，表明SQL与需要优化了。   MISSES 缓存命中率：0 表示已经通过软分析   OPTIMIZER GOAL（优化程序目标）    3.7巧用rownum。
记住rownum是伪例，他永远从1开始，也就是说select xxxx from xxxxx where rownum>5这样的是不可能产生结果的。那如果你一定要这么做怎么办呢可以使用select * from (select rownum as rn,t.* from xxxx t) where rn>10；