oracle 优化分析

记录下来，做数据库优化和索引分析的方案

案例：
1：在分区表中，全局索引可能会导致索引失效，这样执行计划中不走索引，改本地索引local


2：对表进行分析，不用analyze，用了会对执行计划产生影响，对表和索引分析建议用dbms

exec dbms_stats.gather_table_stats(user,upper('tb_ori_0076'),cascade => true,degree => 2);
exec dbms_stats.gather_table_stats(user,
                                   upper('tb_public_0005'),
                                   cascade => true,
                                   degree => 2);
exec dbms_stats.gather_table_stats(user,
                                   upper('tb_index_0056'),
                                   cascade => true,
                                   degree => 2);
exec dbms_stats.gather_table_stats(user,
                                   upper('tb_index_0055'),
                                   cascade => true,
                                   degree => 2);


exec dbms_stats.gather_index_stats(user,'IDX83882807',degree=>2);

3：学会查看explain plan for 执行计划，并创建合适的索引，必要的时候需要用到hint

使用方法：sqlplus user/username@sid

explain plan for sql语句
set timing on
set pagesize 1000
set linesize 150
spool c:/abc.lst
@?/rdbms/admin/utlxplp
spool off

4：使用跟踪事件10046，10046事件是Oracle提供的内部事件，是对SQL_TRACE的增强

SQL>select * from dual;
SQL>alter session set events '10046 trace name context forever,level 12';
SQL>粘贴要跟踪的sql并执行完成
SQL>alter session set events '10046 trace name context off';
SQL>exit
C:\>e:
E:\oracle\product\10.1.0\admin\TESTDB\udump>tkprof testdb_ora_1484.trc abc sys=no
可以在该目录中找到abc这个跟踪文件，进行分析

5：附件是2个写好的plsql命令，abc.sql是利用oracle后台job进行分区在某个时间自动新增分区，tabinfo.sql是分析数据库中当前表，并检索出表中各个字段情况。
通过此分析能快速帮我们排除哪些字段上建立索引最合适。

SQL>@c:\abc.sql 需要开启oracle后台作业进程oracleJobScheduler
SQL> select JOB_NAME,NEXT_RUN_DATE from dba_scheduler_jobs where job_name='MONTHLY_ADD_PARTITION';查看job任务

任务删除 drop_scheduler drop_job

   SQL>@c:\tabinfo.sql 输入用户名和表名即可

SELECT no_, stock_code, stock_name, price_, money_flow_5, business
  FROM (SELECT /*+ ordered index(t4  TB_ORA_0004_IDX1) index(t4_b TB_ORA_0004_IDX1) */ ROW_NUMBER () OVER (ORDER BY (  t4.f0004_028n- t4_b.f0004_028n) DESC) no_,
               t4.f0004_005v stock_code, t5.f0005_002v stock_name,
               t4.f0004_007n price_,
               t4.f0004_028n - t4_b.f0004_028n money_flow_5,
               t55.f0055_002v AS business
          FROM tb_ori_0004 t4
               INNER JOIN
               (SELECT /*+ index(tb_ori_0004 TB_ORA_0004_IDX1) */  f0004_005v, f0004_003v, f0004_004v,
                         MAX (f0004_002v) f0004_002v
                    FROM tb_ori_0004
                   WHERE f0004_001d = (SELECT MAX (f0076_001d)
                                         FROM tb_ori_0076)
                GROUP BY f0004_005v, f0004_003v, f0004_004v) t
               ON t4.f0004_003v = t.f0004_003v
             AND t4.f0004_004v = t.f0004_004v
             AND t4.f0004_005v = t.f0004_005v
             AND t4.f0004_002v = t.f0004_002v
             AND t4.f0004_001d = (SELECT MAX (f0076_001d)
                                    FROM tb_ori_0076)
               INNER JOIN tb_ori_0004 t4_b
               ON t4_b.f0004_003v = t.f0004_003v
             AND t4_b.f0004_004v = t.f0004_004v
             AND t4_b.f0004_005v = t.f0004_005v
             AND t4_b.f0004_002v =TO_CHAR (TO_DATE (t.f0004_002v, 'HH24MI') - 4 / (24 * 60),
                             'HH24MI'
                            )
             AND t4.f0004_001d = t4_b.f0004_001d
               INNER JOIN tb_public_0005 t5
               ON t4.f0004_003v = t5.f0005_007v
             AND t4.f0004_004v = t5.f0005_005v
             AND t4.f0004_005v = t5.f0005_001v
               INNER JOIN
               (SELECT t.f0056_003v, t.f0056_002n
                  FROM  tb_index_0056 t
                    WHERE  t.f0056_001d = (SELECT MAX (t.f0056_001d)
                                          FROM tb_index_0056 t)) t6
                ON t6.f0056_003v = t5.f0005_001v
                  INNER  JOIN
               (SELECT  t.f0055_001n, t.f0055_002v
                  FROM  tb_index_0055 t
                 WHERE t.f0055_003v = '新申万一级') t55
               ON t55.f0055_001n = t6.f0056_002n
               )WHERE no_ <=30;