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 onset pagesize 1000set linesize 150spool c:/abc.lst@?/rdbms/admin/utlxplpspool 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>exitC:\>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后台作业进程oracleJobSchedulerSQL> 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, businessFROM (SELECT /*+ ordered index(t4TB_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 FROMtb_index_0056 t WHEREt.f0056_001d = (SELECT MAX (t.f0056_001d) FROM tb_index_0056 t)) t6 ON t6.f0056_003v = t5.f0005_001v INNERJOIN (SELECTt.f0055_001n, t.f0055_002v FROMtb_index_0055 t WHERE t.f0055_003v = '新申万一级') t55 ON t55.f0055_001n = t6.f0056_002n )WHERE no_ <=30;
页:
[1]