对比前后执行计划，发现问题
链接：http://www.dbaroad.me/archives/2008/11/find-sqlplan-differ.html
检查数据库,发现实例2 latch free较高，TOPAS发现操作系统CPU较高
2 latch free 3487375171 PGATE 7
2 buffer busy global CR 3487375171 PGATE 7
检查导致latch free的SQL语句，如下：
((SELECT a.PT_id, b.acct_code,
NVL (c.description, 'EFG') certificate_type,
NVL (d.description, 'ABC') payed_method,
NVL (e.orgniztion, 'EFG') PT_location,
TO_CHAR (pay_date, 'YYYY-MM-DD HH24:MI:SS') PT_time,
a.amount PT_amount, a.operation_type operation_type
FROM PT_75 a,
acct_75 b,
sys_field_desc c,
sys_field_desc d,
(SELECT x.operator_id, NVL (o.party_role_name, 'EFG') orgniztion
FROM sys_operator x, party_roley, party_role_relat z,
party_role o
WHERE x.party_id = y.party_id(+)
AND y.party_role_id = z.party2_role_id(+)
AND z.party1_role_id = o.party_role_id(+)
AND NVL (x.region_id, 0) IN (75, 0)) e
WHERE a.acct_id = b.acct_id
AND a.amount != 0
AND a.staff_id = e.operator_id(+)
AND c.table_name = 'PT'
AND c.column_name = 'CERTIFICATE_TYPE'
AND a.certificate_type = c.VALUE(+)
AND d.table_name = 'PT_PLAN'
AND d.column_name = 'PT_METHOD'
AND a.payed_method = d.VALUE(+)
AND b.acct_code = :1
AND TO_CHAR (a.pay_date, 'yyyymmdd') >= :2
AND TO_CHAR (a.pay_date, 'yyyymmdd') < = :3))
ORDER BY pay_date DESC
查看其执行计划如下：
[Execution Plan Information]
-------------------------------------------------------------------------------------------------
| Operation | PHV/Object Name | Rows | Bytes| Cost |
-------------------------------------------------------------------------------------------------
|00[00]SELECT STATEMENT |---- 3487375171.0 ---| | | 28 |
|01[01]SORT ORDER BY | | 1 | 218 | 28 |
|02[02] FILTER | | | | |
|03[03] NESTED LOOPS | | 1 | 218 | 12 |
|04[04] NESTED LOOPS OUTER | | 1 | 196 | 10 |
|05[05] NESTED LOOPS | | 1 | 138 | 9 |
|06[06] MERGE JOIN CARTESIAN | | 1 | 86 | 4 |
|07[07] TABLE ACCESS BY INDEX ROWID |SYS_FIELD_DESC | 1 | 43 | 2 |
|08[08] INDEX RANGE SCAN |SYS_FIELD_DESC_1 | 1 | | 1 |
|09[07] BUFFER SORT | | 1 | 43 | 2 |
|10[08] TABLE ACCESS FULL |SYS_FIELD_DESC | 1 | 43 | 2 |
|11[06] TABLE ACCESS BY GLOBAL INDEX ROWID |PT_75 | 1 | 52 | 5 |
|12[07] INDEX RANGE SCAN |IDX_PT_75_DAY | 56K| | 1 |
|13[05] VIEW PUSHED PREDICATE | | 10 | 580 | 1 |
|14[06] NESTED LOOPS OUTER | | 1 | 58 | 7 |
|15[07] NESTED LOOPS OUTER | | 1 | 42 | 6 |
|16[08] NESTED LOOPS OUTER | | 1 | 29 | 4 |
|17[09] TABLE ACCESS BY INDEX ROWID |SYS_OPERATOR | 1 | 15 | 2 |
|18[10] INDEX UNIQUE SCAN |PK_SYS_OPERATOR | 1 | | 1 |
|19[09] TABLE ACCESS BY INDEX ROWID |PARTY_ROLE | 1 | 14 | 2 |
|20[10] INDEX RANGE SCAN |IDX_PARTYROLE_PARTY_ | 1 | | 1 |
|21[08] TABLE ACCESS BY INDEX ROWID |PARTY_ROLE_RELAT | 1 | 13 | 2 |
|22[09] INDEX RANGE SCAN |IDX_PARTY2_ROLE_ID | 1 | | 1 |
|23[07] TABLE ACCESS BY INDEX ROWID |PARTY_ROLE | 1 | 16 | 1 |
|24[08] INDEX UNIQUE SCAN |PK_PARTY_ROLE | 1 | | |
|25[04] TABLE ACCESS BY INDEX ROWID |ACCT_75 | 1 | 22 | 2 |
|26[05] INDEX UNIQUE SCAN |PK_ACCT_75 | 1 | | 1 |
-------------------------------------------------------------------
[Predicate Information]
-------------------------------------------------------------------
2 filter::2< =:3
8 access:"D"."TABLE_NAME"='PT_PLAN' AND "D"."COLUMN_NAME"='PT_
METHOD'
10 filter:"C"."TABLE_NAME"='PT' AND "C"."COLUMN_NAME"='CERTIFICATE_T
YPE'
11 filter:"A"."AMOUNT"<>0 AND "A"."CERTIFICATE_TYPE"="C"."VALUE" AND "A".
"PAYED_METHOD"="D"."VALUE"
12 access:TO_CHAR("A"."PAY_DATE",'yyyymmdd')>=:2 AND TO_CHAR("A"."PAY_DAT
E",'yyyymmdd')< =:3
17 filter:NVL("X"."REGION_ID",0)=75 OR NVL("X"."REGION_ID",0)=0
18 access:"A"."STAFF_ID"="X"."OPERATOR_ID"
20 access:"X"."PARTY_ID"="Y"."PARTY_ID"(+)
22 access:"Y"."PARTY_ROLE_ID"="Z"."PARTY2_ROLE_ID"(+)
24 access:"Z"."PARTY1_ROLE_ID"="O"."PARTY_ROLE_ID"(+)
25 filter:"B"."ACCT_CODE"=:1
26 access:"A"."ACCT_ID"="B"."ACCT_ID"
查看其历史执行计划信息：（通过查询 stats$sql_plan_usage）
------------------------------------------- Instance 1 ------------------------------------
INSTANCE_NUMBER SNAP_ID SNAP_TIME HASH_VALUE PLAN_HASH_VALUE COST OPTIMIZER
--------------- ------- ------------------- ---------- --------------- ---------- ----------
1 95945 2008-10-30 09:05:43 3487375171 378545151 28 CHOOSE
1 95944 2008-10-30 08:45:43 3487375171 378545151 28 CHOOSE
1 95940 2008-10-30 08:25:44 3487375171 378545151 28 CHOOSE
1 95939 2008-10-30 08:05:43 3487375171 378545151 28 CHOOSE
1 95938 2008-10-30 07:45:43 3487375171 378545151 28 CHOOSE
1 95937 2008-10-30 07:25:43 3487375171 378545151 28 CHOOSE
1 95936 2008-10-30 07:05:43 3487375171 378545151 28 CHOOSE
1 95935 2008-10-30 06:45:43 3487375171 378545151 28 CHOOSE
1 95934 2008-10-30 06:25:42 3487375171 378545151 28 CHOOSE
1 95923 2008-10-30 06:05:42 3487375171 378545151 28 CHOOSE
1 95922 2008-10-30 05:45:43 3487375171 378545151 28 CHOOSE
1 95921 2008-10-30 05:25:44 3487375171 378545151 28 CHOOSE
1 95920 2008-10-30 05:05:42 3487375171 378545151 28 CHOOSE
1 95919 2008-10-30 04:45:43 3487375171 378545151 28 CHOOSE
1 95918 2008-10-30 04:25:43 3487375171 378545151 28 CHOOSE
1 95917 2008-10-30 04:05:42 3487375171 378545151 28 CHOOSE
1 95916 2008-10-30 03:45:42 3487375171 378545151 28 CHOOSE
1 95915 2008-10-30 03:25:42 3487375171 378545151 28 CHOOSE
1 95914 2008-10-30 03:05:43 3487375171 378545151 28 CHOOSE
1 95903 2008-10-30 02:45:42 3487375171 378545151 28 CHOOSE
1 95902 2008-10-30 02:25:42 3487375171 378545151 28 CHOOSE
1 95901 2008-10-30 02:05:42 3487375171 378545151 28 CHOOSE
1 95900 2008-10-30 01:45:42 3487375171 378545151 28 CHOOSE
1 95899 2008-10-30 01:25:42 3487375171 378545151 28 CHOOSE
1 95898 2008-10-30 01:05:42 3487375171 378545151 28 CHOOSE
1 95897 2008-10-30 00:45:43 3487375171 378545151 28 CHOOSE
1 95896 2008-10-30 00:25:42 3487375171 378545151 28 CHOOSE
1 95895 2008-10-30 00:05:43 3487375171 378545151 28 CHOOSE
1 95894 2008-10-29 23:45:42 3487375171 378545151 28 CHOOSE
1 95883 2008-10-29 23:25:42 3487375171 378545151 28 CHOOSE
1 95881 2008-10-29 22:45:42 3487375171 177359624 223 CHOOSE
1 95880 2008-10-29 22:25:41 3487375171 177359624 223 CHOOSE
1 95879 2008-10-29 22:05:42 3487375171 177359624 223 CHOOSE
1 95878 2008-10-29 21:45:42 3487375171 177359624 223 CHOOSE
1 95877 2008-10-29 21:25:42 3487375171 177359624 223 CHOOSE
1 95876 2008-10-29 21:05:42 3487375171 177359624 223 CHOOSE
1 95875 2008-10-29 20:45:42 3487375171 177359624 223 CHOOSE
1 95874 2008-10-29 20:25:42 3487375171 177359624 223 CHOOSE
1 95863 2008-10-29 20:05:42 3487375171 177359624 223 CHOOSE
1 95862 2008-10-29 19:45:42 3487375171 177359624 223 CHOOSE
1 95861 2008-10-29 19:25:42 3487375171 177359624 223 CHOOSE
1 95860 2008-10-29 19:05:42 3487375171 177359624 223 CHOOSE
1 95859 2008-10-29 18:45:42 3487375171 177359624 223 CHOOSE
1 95858 2008-10-29 18:25:42 3487375171 177359624 223 CHOOSE
1 95857 2008-10-29 18:05:41 3487375171 177359624 223 CHOOSE
1 95856 2008-10-29 17:45:41 3487375171 177359624 223 CHOOSE
1 95855 2008-10-29 17:25:41 3487375171 177359624 223 CHOOSE
1 95854 2008-10-29 17:05:42 3487375171 177359624 223 CHOOSE
1 95838 2008-10-29 16:45:42 3487375171 177359624 223 CHOOSE
1 95805 2008-10-29 11:05:41 3487375171 177359624 223 CHOOSE
1 95761 2008-10-29 08:05:41 3487375171 177359624 223 CHOOSE
1 95760 2008-10-29 07:45:41 3487375171 177359624 223 CHOOSE
1 95759 2008-10-29 07:25:41 3487375171 177359624 223 CHOOSE
1 95758 2008-10-29 07:05:42 3487375171 177359624 223 CHOOSE
1 95757 2008-10-29 06:45:41 3487375171 177359624 223 CHOOSE
1 95756 2008-10-29 06:25:40 3487375171 177359624 223 CHOOSE
1 95755 2008-10-29 06:05:41 3487375171 177359624 223 CHOOSE
1 95754 2008-10-29 05:45:41 3487375171 177359624 223 CHOOSE
1 95743 2008-10-29 05:25:41 3487375171 177359624 223 CHOOSE
1 95742 2008-10-29 05:05:41 3487375171 177359624 223 CHOOSE
1 95741 2008-10-29 04:45:40 3487375171 177359624 223 CHOOSE
1 95740 2008-10-29 04:25:41 3487375171 177359624 223 CHOOSE
1 95739 2008-10-29 04:05:40 3487375171 177359624 223 CHOOSE
1 95738 2008-10-29 03:45:40 3487375171 177359624 223 CHOOSE
1 95737 2008-10-29 03:25:41 3487375171 177359624 223 CHOOSE
1 95736 2008-10-29 03:05:40 3487375171 177359624 223 CHOOSE
1 95735 2008-10-29 02:45:41 3487375171 177359624 223 CHOOSE
1 95734 2008-10-29 02:25:42 3487375171 177359624 223 CHOOSE
1 95723 2008-10-29 02:05:40 3487375171 177359624 223 CHOOSE
1 95722 2008-10-29 01:45:41 3487375171 177359624 223 CHOOSE
1 95721 2008-10-29 01:25:41 3487375171 177359624 223 CHOOSE
1 95720 2008-10-29 01:05:41 3487375171 177359624 223 CHOOSE
1 95719 2008-10-29 00:45:41 3487375171 177359624 223 CHOOSE
1 95718 2008-10-29 00:25:41 3487375171 177359624 223 CHOOSE
1 95717 2008-10-29 00:05:40 3487375171 177359624 223 CHOOSE
1 95716 2008-10-28 23:45:40 3487375171 177359624 223 CHOOSE
1 95715 2008-10-28 23:25:40 3487375171 177359624 223 CHOOSE
1 95714 2008-10-28 23:05:40 3487375171 177359624 223 CHOOSE
1 95703 2008-10-28 22:45:40 3487375171 177359624 223 CHOOSE
1 95702 2008-10-28 22:25:40 3487375171 177359624 223 CHOOSE
1 95701 2008-10-28 22:05:40 3487375171 177359624 223 CHOOSE
1 95700 2008-10-28 21:45:40 3487375171 177359624 223 CHOOSE
1 95699 2008-10-28 21:25:39 3487375171 177359624 223 CHOOSE
1 95698 2008-10-28 21:05:40 3487375171 177359624 223 CHOOSE
1 95697 2008-10-28 20:45:40 3487375171 177359624 223 CHOOSE
1 95696 2008-10-28 20:25:40 3487375171 177359624 223 CHOOSE
1 95695 2008-10-28 20:05:40 3487375171 177359624 223 CHOOSE
1 95694 2008-10-28 19:45:40 3487375171 177359624 223 CHOOSE
1 95683 2008-10-28 19:25:40 3487375171 177359624 223 CHOOSE
1 95682 2008-10-28 19:05:40 3487375171 177359624 223 CHOOSE
1 95681 2008-10-28 18:45:40 3487375171 177359624 223 CHOOSE
1 95680 2008-10-28 18:25:41 3487375171 177359624 223 CHOOSE
1 95679 2008-10-28 18:05:40 3487375171 177359624 223 CHOOSE
1 95678 2008-10-28 17:45:39 3487375171 177359624 223 CHOOSE
1 95677 2008-10-28 17:25:41 3487375171 177359624 223 CHOOSE
1 95676 2008-10-28 17:05:40 3487375171 177359624 223 CHOOSE
1 95664 2008-10-28 16:05:41 3487375171 177359624 223 CHOOSE
1 95607 2008-10-28 12:25:40 3487375171 177359624 223 CHOOSE
1 95606 2008-10-28 12:05:40 3487375171 177359624 223 CHOOSE
1 95605 2008-10-28 11:45:39 3487375171 177359624 223 CHOOSE
------------------------------------------- Instance 2 ------------------------------------
INSTANCE_NUMBER SNAP_ID SNAP_TIME HASH_VALUE PLAN_HASH_VALUE COST OPTIMIZER
--------------- ------- ------------------- ---------- --------------- ---------- ----------
2 95954 2008-10-30 09:05:43 3487375171 378545151 28 CHOOSE
2 95933 2008-10-30 08:45:43 3487375171 378545151 28 CHOOSE
2 95932 2008-10-30 08:25:43 3487375171 378545151 28 CHOOSE
2 95931 2008-10-30 08:05:43 3487375171 378545151 28 CHOOSE
2 95930 2008-10-30 07:45:43 3487375171 378545151 28 CHOOSE
2 95929 2008-10-30 07:25:43 3487375171 378545151 28 CHOOSE
2 95928 2008-10-30 07:05:43 3487375171 378545151 28 CHOOSE
2 95927 2008-10-30 06:45:43 3487375171 378545151 28 CHOOSE
2 95926 2008-10-30 06:25:42 3487375171 378545151 28 CHOOSE
2 95925 2008-10-30 06:05:42 3487375171 378545151 28 CHOOSE
2 95924 2008-10-30 05:45:42 3487375171 378545151 28 CHOOSE
2 95913 2008-10-30 05:25:43 3487375171 378545151 28 CHOOSE
2 95912 2008-10-30 05:05:42 3487375171 378545151 28 CHOOSE
2 95911 2008-10-30 04:45:43 3487375171 378545151 28 CHOOSE
2 95910 2008-10-30 04:25:43 3487375171 378545151 28 CHOOSE
2 95909 2008-10-30 04:05:42 3487375171 378545151 28 CHOOSE
2 95908 2008-10-30 03:45:42 3487375171 378545151 28 CHOOSE
2 95907 2008-10-30 03:25:42 3487375171 378545151 28 CHOOSE
2 95906 2008-10-30 03:05:43 3487375171 378545151 28 CHOOSE
2 95905 2008-10-30 02:45:42 3487375171 378545151 28 CHOOSE
2 95904 2008-10-30 02:25:42 3487375171 378545151 28 CHOOSE
2 95893 2008-10-30 02:05:42 3487375171 378545151 28 CHOOSE
2 95892 2008-10-30 01:45:42 3487375171 378545151 28 CHOOSE
2 95891 2008-10-30 01:25:42 3487375171 378545151 28 CHOOSE
2 95890 2008-10-30 01:05:42 3487375171 378545151 28 CHOOSE
2 95889 2008-10-30 00:45:43 3487375171 378545151 28 CHOOSE
2 95888 2008-10-30 00:25:42 3487375171 378545151 28 CHOOSE
2 95887 2008-10-30 00:05:43 3487375171 378545151 28 CHOOSE
2 95886 2008-10-29 23:45:42 3487375171 378545151 28 CHOOSE
2 95885 2008-10-29 23:25:42 3487375171 378545151 28 CHOOSE
2 95873 2008-10-29 22:45:42 3487375171 177359624 223 CHOOSE
2 95872 2008-10-29 22:25:41 3487375171 177359624 223 CHOOSE
2 95871 2008-10-29 22:05:42 3487375171 177359624 223 CHOOSE
2 95870 2008-10-29 21:45:42 3487375171 177359624 223 CHOOSE
2 95869 2008-10-29 21:25:42 3487375171 177359624 223 CHOOSE
2 95868 2008-10-29 21:05:42 3487375171 177359624 223 CHOOSE
2 95867 2008-10-29 20:45:42 3487375171 177359624 223 CHOOSE
2 95866 2008-10-29 20:25:42 3487375171 177359624 223 CHOOSE
2 95865 2008-10-29 20:05:42 3487375171 177359624 223 CHOOSE
2 95864 2008-10-29 19:45:42 3487375171 177359624 223 CHOOSE
2 95853 2008-10-29 19:25:42 3487375171 177359624 223 CHOOSE
2 95852 2008-10-29 19:05:41 3487375171 177359624 223 CHOOSE
2 95851 2008-10-29 18:45:42 3487375171 177359624 223 CHOOSE
2 95850 2008-10-29 18:25:42 3487375171 177359624 223 CHOOSE
2 95849 2008-10-29 18:05:42 3487375171 177359624 223 CHOOSE
2 95848 2008-10-29 17:45:41 3487375171 177359624 223 CHOOSE
2 95847 2008-10-29 17:25:41 3487375171 177359624 223 CHOOSE
2 95846 2008-10-29 17:05:42 3487375171 177359624 223 CHOOSE
2 95845 2008-10-29 16:45:42 3487375171 177359624 223 CHOOSE
2 95844 2008-10-29 16:25:42 3487375171 177359624 223 CHOOSE
2 95830 2008-10-29 16:05:42 3487375171 177359624 223 CHOOSE
2 95827 2008-10-29 15:05:42 3487375171 177359624 223 CHOOSE
2 95826 2008-10-29 14:45:41 3487375171 177359624 223 CHOOSE
2 95825 2008-10-29 14:25:41 3487375171 177359624 223 CHOOSE
2 95824 2008-10-29 14:05:42 3487375171 177359624 223 CHOOSE
2 95803 2008-10-29 13:45:41 3487375171 177359624 223 CHOOSE
2 95802 2008-10-29 13:25:41 3487375171 177359624 223 CHOOSE
2 95801 2008-10-29 13:05:42 3487375171 177359624 223 CHOOSE
2 95800 2008-10-29 12:45:41 3487375171 177359624 223 CHOOSE
2 95799 2008-10-29 12:25:41 3487375171 177359624 223 CHOOSE
2 95798 2008-10-29 12:05:41 3487375171 177359624 223 CHOOSE
2 95797 2008-10-29 11:45:41 3487375171 177359624 223 CHOOSE
2 95796 2008-10-29 11:25:41 3487375171 177359624 223 CHOOSE
2 95794 2008-10-29 10:45:42 3487375171 177359624 223 CHOOSE
2 95787 2008-10-29 10:25:41 3487375171 177359624 223 CHOOSE
2 95785 2008-10-29 09:45:41 3487375171 177359624 223 CHOOSE
2 95771 2008-10-29 08:25:41 3487375171 177359624 223 CHOOSE
2 95770 2008-10-29 08:05:41 3487375171 177359624 223 CHOOSE
2 95769 2008-10-29 07:45:41 3487375171 177359624 223 CHOOSE
2 95768 2008-10-29 07:25:41 3487375171 177359624 223 CHOOSE
2 95767 2008-10-29 07:05:41 3487375171 177359624 223 CHOOSE
2 95766 2008-10-29 06:45:41 3487375171 177359624 223 CHOOSE
2 95765 2008-10-29 06:25:40 3487375171 177359624 223 CHOOSE
2 95764 2008-10-29 06:05:41 3487375171 177359624 223 CHOOSE
2 95753 2008-10-29 05:45:41 3487375171 177359624 223 CHOOSE
2 95752 2008-10-29 05:25:41 3487375171 177359624 223 CHOOSE
2 95751 2008-10-29 05:05:41 3487375171 177359624 223 CHOOSE
2 95750 2008-10-29 04:45:40 3487375171 177359624 223 CHOOSE
2 95749 2008-10-29 04:25:41 3487375171 177359624 223 CHOOSE
2 95748 2008-10-29 04:05:40 3487375171 177359624 223 CHOOSE
2 95747 2008-10-29 03:45:40 3487375171 177359624 223 CHOOSE
2 95746 2008-10-29 03:25:41 3487375171 177359624 223 CHOOSE
2 95745 2008-10-29 03:05:40 3487375171 177359624 223 CHOOSE
2 95744 2008-10-29 02:45:41 3487375171 177359624 223 CHOOSE
2 95733 2008-10-29 02:25:41 3487375171 177359624 223 CHOOSE
2 95732 2008-10-29 02:05:40 3487375171 177359624 223 CHOOSE
2 95731 2008-10-29 01:45:41 3487375171 177359624 223 CHOOSE
2 95730 2008-10-29 01:25:40 3487375171 177359624 223 CHOOSE
2 95729 2008-10-29 01:05:40 3487375171 177359624 223 CHOOSE
2 95728 2008-10-29 00:45:41 3487375171 177359624 223 CHOOSE
2 95727 2008-10-29 00:25:41 3487375171 177359624 223 CHOOSE
2 95726 2008-10-29 00:05:40 3487375171 177359624 223 CHOOSE
2 95725 2008-10-28 23:45:40 3487375171 177359624 223 CHOOSE
2 95724 2008-10-28 23:25:40 3487375171 177359624 223 CHOOSE
2 95713 2008-10-28 23:05:40 3487375171 177359624 223 CHOOSE
2 95712 2008-10-28 22:45:40 3487375171 177359624 223 CHOOSE
2 95711 2008-10-28 22:25:40 3487375171 177359624 223 CHOOSE
2 95710 2008-10-28 22:05:40 3487375171 177359624 223 CHOOSE
2 95709 2008-10-28 21:45:40 3487375171 177359624 223 CHOOSE
2 95708 2008-10-28 21:25:39 3487375171 177359624 223 CHOOSE
2 95707 2008-10-28 21:05:40 3487375171 177359624 223 CHOOSE
2 95706 2008-10-28 20:45:40 3487375171 177359624 223 CHOOSE
2 95705 2008-10-28 20:25:40 3487375171 177359624 223 CHOOSE
2 95704 2008-10-28 20:05:40 3487375171 177359624 223 CHOOSE
2 95693 2008-10-28 19:45:40 3487375171 177359624 223 CHOOSE
2 95692 2008-10-28 19:25:40 3487375171 177359624 223 CHOOSE
2 95691 2008-10-28 19:05:40 3487375171 177359624 223 CHOOSE
2 95690 2008-10-28 18:45:40 3487375171 177359624 223 CHOOSE
2 95689 2008-10-28 18:25:40 3487375171 177359624 223 CHOOSE
2 95684 2008-10-28 16:45:40 3487375171 177359624 223 CHOOSE
2 95656 2008-10-28 16:25:40 3487375171 177359624 223 CHOOSE
2 95655 2008-10-28 16:05:41 3487375171 177359624 223 CHOOSE
2 95602 2008-10-28 13:45:40 3487375171 177359624 223 CHOOSE
2 95601 2008-10-28 13:25:40 3487375171 177359624 223 CHOOSE
2 95600 2008-10-28 13:05:40 3487375171 177359624 223 CHOOSE
2 95599 2008-10-28 12:45:40 3487375171 177359624 223 CHOOSE
2 95598 2008-10-28 12:25:40 3487375171 177359624 223 CHOOSE
2 95597 2008-10-28 12:05:40 3487375171 177359624 223 CHOOSE
2 95596 2008-10-28 11:45:39 3487375171 177359624 223 CHOOSE
2 95595 2008-10-28 11:25:40 3487375171 177359624 223 CHOOSE
2 95594 2008-10-28 11:05:40 3487375171 177359624 223 CHOOSE
2 95567 2008-10-28 10:45:39 3487375171 177359624 223 CHOOSE
可以看出，在2008-10-29 23:25左右时执行计划发生了改变。
查看历史执执行计划：（通过查询v$sql_plan，使用plan_hash_value）
[Execution Plan Information]
--------------------------------------------------------------------------------------------------
| Operation | PHV/Object Name | Rows | Bytes| Cost |
--------------------------------------------------------------------------------------------------
|00[00]SELECT STATEMENT |plan_hash_value 17735| | | 216 |
|01[01]SORT ORDER BY | | 1 | 207 | 216 |
|02[02] FILTER | | | | |
|03[03] TABLE ACCESS BY INDEX ROWID |SYS_FIELD_DESC | 1 | 43 | 2 |
|04[04] NESTED LOOPS | | 1 | 207 | 200 |
|05[05] NESTED LOOPS OUTER | | 1 | 164 | 198 |
|06[06] NESTED LOOPS | | 1 | 111 | 197 |
|07[07] MERGE JOIN CARTESIAN | | 1 | 65 | 5 |
|08[08] TABLE ACCESS FULL |SYS_FIELD_DESC | 1 | 43 | 2 |
|09[08] BUFFER SORT | | 1 | 22 | 3 |
|10[09] TABLE ACCESS BY INDEX ROWID |ACCT_75 | 1 | 22 | 3 |
|11[10] INDEX RANGE SCAN |IDX_ACCT_CODE_75 | 1 | | 2 |
|12[07] TABLE ACCESS BY GLOBAL INDEX ROWID |PT_75 | 1 | 46 | 192 |
|13[08] INDEX RANGE SCAN |IDX_PT_75_ACCTI | 11 | | 2 |
|14[06] VIEW PUSHED PREDICATE | | 9 | 477 | 1 |
|15[07] NESTED LOOPS OUTER | | 1 | 53 | 7 |
|16[08] NESTED LOOPS OUTER | | 1 | 38 | 6 |
|17[09] NESTED LOOPS OUTER | | 1 | 26 | 4 |
|18[10] TABLE ACCESS BY INDEX ROWID |SYS_OPERATOR | 1 | 14 | 2 |
|19[11] INDEX UNIQUE SCAN |PK_SYS_OPERATOR | 1 | | 1 |
|20[10] TABLE ACCESS BY INDEX ROWID |PARTY_ROLE | 1 | 12 | 2 |
|21[11] INDEX RANGE SCAN |IDX_PARTYROLE_PARTY_ | 1 | | 1 |
|22[09] TABLE ACCESS BY INDEX ROWID |PARTY_ROLE_RELAT | 1 | 12 | 2 |
|23[10] INDEX RANGE SCAN |IDX_PARTY2_ROLE_ID | 1 | | 1 |
|24[08] TABLE ACCESS BY INDEX ROWID |PARTY_ROLE | 1 | 15 | 1 |
|25[09] INDEX UNIQUE SCAN |PK_PARTY_ROLE | 1 | | |
|26[05] INDEX RANGE SCAN |SYS_FIELD_DESC_1 | 1 | | 1 |
--------------------------------------------------------------------
通过对比前后执行计划，可以看出，之前的执行计划，走索引IDX_PT_75_ACCTI，而当前走索引IDX_PT_75_DAY。
通过查询dba_objects，发现IDX_PT_75_DAY正是10月29号23点创建的。因而导致了执行计划的改变。
SQL>SELECT owner, object_name, object_type, created, last_ddl_time, status
2 FROM dba_objects
3 WHERE object_name ='IDX_PT_75_DAY';
OWNER OBJECT_NAME OBJECT_TYPE CREATED LAST_DDL_TIME STATUS
-------- ------------------- ------------ ------------------- ------------------- ------
PGATE IDX_PT_75_DAY INDEX 2008-10-29 23:05:22 2008-10-29 23:05:22 VALID
通知应用开发人员，开发人员DROP IDX_PT_75_DAY索引后，数据库恢复正常。
查看执行计划信息：
---------------------------------------------- Instance 1 ------------------------------------
INSTANCE_NUMBER SNAP_ID SNAP_TIME HASH_VALUE PLAN_HASH_VALUE COST OPTIMIZER
--------------- ---------- ------------------- ---------- --------------- ---------- ----------
1 95991 2008-10-30 12:25:43 3487375171 177359624 223 CHOOSE
1 95990 2008-10-30 12:05:43 3487375171 177359624 223 CHOOSE
1 95989 2008-10-30 11:45:43 3487375171 177359624 223 CHOOSE
1 95985 2008-10-30 10:25:43 3487375171 177359624 223 CHOOSE
1 95985 2008-10-30 10:25:43 3487375171 378545151 28 CHOOSE
---------------------------------------------- Instance 2 ------------------------------------
INSTANCE_NUMBER SNAP_ID SNAP_TIME HASH_VALUE PLAN_HASH_VALUE COST OPTIMIZER
--------------- ---------- ------------------- ---------- --------------- ---------- ----------
2 95983 2008-10-30 12:45:43 3487375171 177359624 223 CHOOSE
2 95982 2008-10-30 12:25:43 3487375171 177359624 223 CHOOSE
2 95981 2008-10-30 12:05:43 3487375171 177359624 223 CHOOSE
2 95980 2008-10-30 11:45:43 3487375171 177359624 223 CHOOSE
2 95977 2008-10-30 10:45:43 3487375171 177359624 223 CHOOSE
2 95976 2008-10-30 10:25:43 3487375171 177359624 223 CHOOSE
2 95976 2008-10-30 10:25:43 3487375171 378545151 28 CHOOSE
执行计划已恢复正常。
查看当前执行计划：
[Execution Plan Information]
--------------------------------------------------------------------------------------------------
| Operation | PHV/Object Name | Rows | Bytes| Cost |
--------------------------------------------------------------------------------------------------
|00[00]SELECT STATEMENT |---- 3487375171.0 ---| | | 223 |
|01[01]SORT ORDER BY | | 1 | 218 | 223 |
|02[02] FILTER | | | | |
|03[03] TABLE ACCESS BY INDEX ROWID |SYS_FIELD_DESC | 1 | 43 | 2 |
|04[04] NESTED LOOPS | | 1 | 218 | 207 |
|05[05] NESTED LOOPS OUTER | | 1 | 175 | 205 |
|06[06] NESTED LOOPS | | 1 | 117 | 204 |
|07[07] MERGE JOIN CARTESIAN | | 1 | 65 | 6 |
|08[08] TABLE ACCESS FULL |SYS_FIELD_DESC | 1 | 43 | 2 |
|09[08] BUFFER SORT | | 1 | 22 | 4 |
|10[09] TABLE ACCESS BY INDEX ROWID |ACCT_75 | 1 | 22 | 4 |
|11[10] INDEX RANGE SCAN |IDX_ACCT_CODE_75 | 1 | | 2 |
|12[07] TABLE ACCESS BY GLOBAL INDEX ROWID |PT_75 | 1 | 52 | 198 |
|13[08] INDEX RANGE SCAN |IDX_PT_75_ACCTI | 11 | | 2 |
|14[06] VIEW PUSHED PREDICATE | | 10 | 580 | 1 |
|15[07] NESTED LOOPS OUTER | | 1 | 58 | 7 |
|16[08] NESTED LOOPS OUTER | | 1 | 42 | 6 |
|17[09] NESTED LOOPS OUTER | | 1 | 29 | 4 |
|18[10] TABLE ACCESS BY INDEX ROWID |SYS_OPERATOR | 1 | 15 | 2 |
|19[11] INDEX UNIQUE SCAN |PK_SYS_OPERATOR | 1 | | 1 |
|20[10] TABLE ACCESS BY INDEX ROWID |PARTY_ROLE | 1 | 14 | 2 |
|21[11] INDEX RANGE SCAN |IDX_PARTYROLE_PARTY_ | 1 | | 1 |
|22[09] TABLE ACCESS BY INDEX ROWID |PARTY_ROLE_RELAT | 1 | 13 | 2 |
|23[10] INDEX RANGE SCAN |IDX_PARTY2_ROLE_ID | 1 | | 1 |
|24[08] TABLE ACCESS BY INDEX ROWID |PARTY_ROLE | 1 | 16 | 1 |
|25[09] INDEX UNIQUE SCAN |PK_PARTY_ROLE | 1 | | |
|26[05] INDEX RANGE SCAN |SYS_FIELD_DESC_1 | 1 | | 1 |
--------------------------------------------------------------------
[Predicate Information]
--------------------------------------------------------------------
2 filter::2< =:3
3 filter:"A"."PAYED_METHOD"="D"."VALUE"
8 filter:"C"."TABLE_NAME"='PT' AND "C"."COLUMN_NAME"='CERTIFICATE_T
YPE'
11 access:"B"."ACCT_CODE"=:1
12 filter:"A"."AMOUNT"<>0 AND "A"."CERTIFICATE_TYPE"="C"."VALUE" AND TO_C
HAR("A"."PAY_DATE",'yyyymmdd')>=:2 AND TO_CHAR("A"."PAY_DATE",'yyyymmd
d')< =:3
13 access:"A"."ACCT_ID"="B"."ACCT_ID"
18 filter:NVL("X"."REGION_ID",0)=75 OR NVL("X"."REGION_ID",0)=0
19 access:"A"."STAFF_ID"="X"."OPERATOR_ID"
21 access:"X"."PARTY_ID"="Y"."PARTY_ID"(+)
23 access:"Y"."PARTY_ROLE_ID"="Z"."PARTY2_ROLE_ID"(+)
25 access:"Z"."PARTY1_ROLE_ID"="O"."PARTY_ROLE_ID"(+)
26 access:"D"."TABLE_NAME"='PT_PLAN' AND "D"."COLUMN_NAME"='PT_
METHOD'
链接：http://www.dbaroad.me/archives/2008/11/find-sqlplan-differ.html
检查数据库,发现实例2 latch free较高，TOPAS发现操作系统CPU较高
2 latch free 3487375171 PGATE 7
2 buffer busy global CR 3487375171 PGATE 7
检查导致latch free的SQL语句，如下：
((SELECT a.PT_id, b.acct_code,
NVL (c.description, 'EFG') certificate_type,
NVL (d.description, 'ABC') payed_method,
NVL (e.orgniztion, 'EFG') PT_location,
TO_CHAR (pay_date, 'YYYY-MM-DD HH24:MI:SS') PT_time,
a.amount PT_amount, a.operation_type operation_type
FROM PT_75 a,
acct_75 b,
sys_field_desc c,
sys_field_desc d,
(SELECT x.operator_id, NVL (o.party_role_name, 'EFG') orgniztion
FROM sys_operator x, party_roley, party_role_relat z,
party_role o
WHERE x.party_id = y.party_id(+)
AND y.party_role_id = z.party2_role_id(+)
AND z.party1_role_id = o.party_role_id(+)
AND NVL (x.region_id, 0) IN (75, 0)) e
WHERE a.acct_id = b.acct_id
AND a.amount != 0
AND a.staff_id = e.operator_id(+)
AND c.table_name = 'PT'
AND c.column_name = 'CERTIFICATE_TYPE'
AND a.certificate_type = c.VALUE(+)
AND d.table_name = 'PT_PLAN'
AND d.column_name = 'PT_METHOD'
AND a.payed_method = d.VALUE(+)
AND b.acct_code = :1
AND TO_CHAR (a.pay_date, 'yyyymmdd') >= :2
AND TO_CHAR (a.pay_date, 'yyyymmdd') < = :3))
ORDER BY pay_date DESC
查看其执行计划如下：
[Execution Plan Information]
-------------------------------------------------------------------------------------------------
| Operation | PHV/Object Name | Rows | Bytes| Cost |
-------------------------------------------------------------------------------------------------
|00[00]SELECT STATEMENT |---- 3487375171.0 ---| | | 28 |
|01[01]SORT ORDER BY | | 1 | 218 | 28 |
|02[02] FILTER | | | | |
|03[03] NESTED LOOPS | | 1 | 218 | 12 |
|04[04] NESTED LOOPS OUTER | | 1 | 196 | 10 |
|05[05] NESTED LOOPS | | 1 | 138 | 9 |
|06[06] MERGE JOIN CARTESIAN | | 1 | 86 | 4 |
|07[07] TABLE ACCESS BY INDEX ROWID |SYS_FIELD_DESC | 1 | 43 | 2 |
|08[08] INDEX RANGE SCAN |SYS_FIELD_DESC_1 | 1 | | 1 |
|09[07] BUFFER SORT | | 1 | 43 | 2 |
|10[08] TABLE ACCESS FULL |SYS_FIELD_DESC | 1 | 43 | 2 |
|11[06] TABLE ACCESS BY GLOBAL INDEX ROWID |PT_75 | 1 | 52 | 5 |
|12[07] INDEX RANGE SCAN |IDX_PT_75_DAY | 56K| | 1 |
|13[05] VIEW PUSHED PREDICATE | | 10 | 580 | 1 |
|14[06] NESTED LOOPS OUTER | | 1 | 58 | 7 |
|15[07] NESTED LOOPS OUTER | | 1 | 42 | 6 |
|16[08] NESTED LOOPS OUTER | | 1 | 29 | 4 |
|17[09] TABLE ACCESS BY INDEX ROWID |SYS_OPERATOR | 1 | 15 | 2 |
|18[10] INDEX UNIQUE SCAN |PK_SYS_OPERATOR | 1 | | 1 |
|19[09] TABLE ACCESS BY INDEX ROWID |PARTY_ROLE | 1 | 14 | 2 |
|20[10] INDEX RANGE SCAN |IDX_PARTYROLE_PARTY_ | 1 | | 1 |
|21[08] TABLE ACCESS BY INDEX ROWID |PARTY_ROLE_RELAT | 1 | 13 | 2 |
|22[09] INDEX RANGE SCAN |IDX_PARTY2_ROLE_ID | 1 | | 1 |
|23[07] TABLE ACCESS BY INDEX ROWID |PARTY_ROLE | 1 | 16 | 1 |
|24[08] INDEX UNIQUE SCAN |PK_PARTY_ROLE | 1 | | |
|25[04] TABLE ACCESS BY INDEX ROWID |ACCT_75 | 1 | 22 | 2 |
|26[05] INDEX UNIQUE SCAN |PK_ACCT_75 | 1 | | 1 |
-------------------------------------------------------------------
[Predicate Information]
-------------------------------------------------------------------
2 filter::2< =:3
8 access:"D"."TABLE_NAME"='PT_PLAN' AND "D"."COLUMN_NAME"='PT_
METHOD'
10 filter:"C"."TABLE_NAME"='PT' AND "C"."COLUMN_NAME"='CERTIFICATE_T
YPE'
11 filter:"A"."AMOUNT"<>0 AND "A"."CERTIFICATE_TYPE"="C"."VALUE" AND "A".
"PAYED_METHOD"="D"."VALUE"
12 access:TO_CHAR("A"."PAY_DATE",'yyyymmdd')>=:2 AND TO_CHAR("A"."PAY_DAT
E",'yyyymmdd')< =:3
17 filter:NVL("X"."REGION_ID",0)=75 OR NVL("X"."REGION_ID",0)=0
18 access:"A"."STAFF_ID"="X"."OPERATOR_ID"
20 access:"X"."PARTY_ID"="Y"."PARTY_ID"(+)
22 access:"Y"."PARTY_ROLE_ID"="Z"."PARTY2_ROLE_ID"(+)
24 access:"Z"."PARTY1_ROLE_ID"="O"."PARTY_ROLE_ID"(+)
25 filter:"B"."ACCT_CODE"=:1
26 access:"A"."ACCT_ID"="B"."ACCT_ID"
查看其历史执行计划信息：（通过查询 stats$sql_plan_usage）
------------------------------------------- Instance 1 ------------------------------------
INSTANCE_NUMBER SNAP_ID SNAP_TIME HASH_VALUE PLAN_HASH_VALUE COST OPTIMIZER
--------------- ------- ------------------- ---------- --------------- ---------- ----------
1 95945 2008-10-30 09:05:43 3487375171 378545151 28 CHOOSE
1 95944 2008-10-30 08:45:43 3487375171 378545151 28 CHOOSE
1 95940 2008-10-30 08:25:44 3487375171 378545151 28 CHOOSE
1 95939 2008-10-30 08:05:43 3487375171 378545151 28 CHOOSE
1 95938 2008-10-30 07:45:43 3487375171 378545151 28 CHOOSE
1 95937 2008-10-30 07:25:43 3487375171 378545151 28 CHOOSE
1 95936 2008-10-30 07:05:43 3487375171 378545151 28 CHOOSE
1 95935 2008-10-30 06:45:43 3487375171 378545151 28 CHOOSE
1 95934 2008-10-30 06:25:42 3487375171 378545151 28 CHOOSE
1 95923 2008-10-30 06:05:42 3487375171 378545151 28 CHOOSE
1 95922 2008-10-30 05:45:43 3487375171 378545151 28 CHOOSE
1 95921 2008-10-30 05:25:44 3487375171 378545151 28 CHOOSE
1 95920 2008-10-30 05:05:42 3487375171 378545151 28 CHOOSE
1 95919 2008-10-30 04:45:43 3487375171 378545151 28 CHOOSE
1 95918 2008-10-30 04:25:43 3487375171 378545151 28 CHOOSE
1 95917 2008-10-30 04:05:42 3487375171 378545151 28 CHOOSE
1 95916 2008-10-30 03:45:42 3487375171 378545151 28 CHOOSE
1 95915 2008-10-30 03:25:42 3487375171 378545151 28 CHOOSE
1 95914 2008-10-30 03:05:43 3487375171 378545151 28 CHOOSE
1 95903 2008-10-30 02:45:42 3487375171 378545151 28 CHOOSE
1 95902 2008-10-30 02:25:42 3487375171 378545151 28 CHOOSE
1 95901 2008-10-30 02:05:42 3487375171 378545151 28 CHOOSE
1 95900 2008-10-30 01:45:42 3487375171 378545151 28 CHOOSE
1 95899 2008-10-30 01:25:42 3487375171 378545151 28 CHOOSE
1 95898 2008-10-30 01:05:42 3487375171 378545151 28 CHOOSE
1 95897 2008-10-30 00:45:43 3487375171 378545151 28 CHOOSE
1 95896 2008-10-30 00:25:42 3487375171 378545151 28 CHOOSE
1 95895 2008-10-30 00:05:43 3487375171 378545151 28 CHOOSE
1 95894 2008-10-29 23:45:42 3487375171 378545151 28 CHOOSE
1 95883 2008-10-29 23:25:42 3487375171 378545151 28 CHOOSE
1 95881 2008-10-29 22:45:42 3487375171 177359624 223 CHOOSE
1 95880 2008-10-29 22:25:41 3487375171 177359624 223 CHOOSE
1 95879 2008-10-29 22:05:42 3487375171 177359624 223 CHOOSE
1 95878 2008-10-29 21:45:42 3487375171 177359624 223 CHOOSE
1 95877 2008-10-29 21:25:42 3487375171 177359624 223 CHOOSE
1 95876 2008-10-29 21:05:42 3487375171 177359624 223 CHOOSE
1 95875 2008-10-29 20:45:42 3487375171 177359624 223 CHOOSE
1 95874 2008-10-29 20:25:42 3487375171 177359624 223 CHOOSE
1 95863 2008-10-29 20:05:42 3487375171 177359624 223 CHOOSE
1 95862 2008-10-29 19:45:42 3487375171 177359624 223 CHOOSE
1 95861 2008-10-29 19:25:42 3487375171 177359624 223 CHOOSE
1 95860 2008-10-29 19:05:42 3487375171 177359624 223 CHOOSE
1 95859 2008-10-29 18:45:42 3487375171 177359624 223 CHOOSE
1 95858 2008-10-29 18:25:42 3487375171 177359624 223 CHOOSE
1 95857 2008-10-29 18:05:41 3487375171 177359624 223 CHOOSE
1 95856 2008-10-29 17:45:41 3487375171 177359624 223 CHOOSE
1 95855 2008-10-29 17:25:41 3487375171 177359624 223 CHOOSE
1 95854 2008-10-29 17:05:42 3487375171 177359624 223 CHOOSE
1 95838 2008-10-29 16:45:42 3487375171 177359624 223 CHOOSE
1 95805 2008-10-29 11:05:41 3487375171 177359624 223 CHOOSE
1 95761 2008-10-29 08:05:41 3487375171 177359624 223 CHOOSE
1 95760 2008-10-29 07:45:41 3487375171 177359624 223 CHOOSE
1 95759 2008-10-29 07:25:41 3487375171 177359624 223 CHOOSE
1 95758 2008-10-29 07:05:42 3487375171 177359624 223 CHOOSE
1 95757 2008-10-29 06:45:41 3487375171 177359624 223 CHOOSE
1 95756 2008-10-29 06:25:40 3487375171 177359624 223 CHOOSE
1 95755 2008-10-29 06:05:41 3487375171 177359624 223 CHOOSE
1 95754 2008-10-29 05:45:41 3487375171 177359624 223 CHOOSE
1 95743 2008-10-29 05:25:41 3487375171 177359624 223 CHOOSE
1 95742 2008-10-29 05:05:41 3487375171 177359624 223 CHOOSE
1 95741 2008-10-29 04:45:40 3487375171 177359624 223 CHOOSE
1 95740 2008-10-29 04:25:41 3487375171 177359624 223 CHOOSE
1 95739 2008-10-29 04:05:40 3487375171 177359624 223 CHOOSE
1 95738 2008-10-29 03:45:40 3487375171 177359624 223 CHOOSE
1 95737 2008-10-29 03:25:41 3487375171 177359624 223 CHOOSE
1 95736 2008-10-29 03:05:40 3487375171 177359624 223 CHOOSE
1 95735 2008-10-29 02:45:41 3487375171 177359624 223 CHOOSE
1 95734 2008-10-29 02:25:42 3487375171 177359624 223 CHOOSE
1 95723 2008-10-29 02:05:40 3487375171 177359624 223 CHOOSE
1 95722 2008-10-29 01:45:41 3487375171 177359624 223 CHOOSE
1 95721 2008-10-29 01:25:41 3487375171 177359624 223 CHOOSE
1 95720 2008-10-29 01:05:41 3487375171 177359624 223 CHOOSE
1 95719 2008-10-29 00:45:41 3487375171 177359624 223 CHOOSE
1 95718 2008-10-29 00:25:41 3487375171 177359624 223 CHOOSE
1 95717 2008-10-29 00:05:40 3487375171 177359624 223 CHOOSE
1 95716 2008-10-28 23:45:40 3487375171 177359624 223 CHOOSE
1 95715 2008-10-28 23:25:40 3487375171 177359624 223 CHOOSE
1 95714 2008-10-28 23:05:40 3487375171 177359624 223 CHOOSE
1 95703 2008-10-28 22:45:40 3487375171 177359624 223 CHOOSE
1 95702 2008-10-28 22:25:40 3487375171 177359624 223 CHOOSE
1 95701 2008-10-28 22:05:40 3487375171 177359624 223 CHOOSE
1 95700 2008-10-28 21:45:40 3487375171 177359624 223 CHOOSE
1 95699 2008-10-28 21:25:39 3487375171 177359624 223 CHOOSE
1 95698 2008-10-28 21:05:40 3487375171 177359624 223 CHOOSE
1 95697 2008-10-28 20:45:40 3487375171 177359624 223 CHOOSE
1 95696 2008-10-28 20:25:40 3487375171 177359624 223 CHOOSE
1 95695 2008-10-28 20:05:40 3487375171 177359624 223 CHOOSE
1 95694 2008-10-28 19:45:40 3487375171 177359624 223 CHOOSE
1 95683 2008-10-28 19:25:40 3487375171 177359624 223 CHOOSE
1 95682 2008-10-28 19:05:40 3487375171 177359624 223 CHOOSE
1 95681 2008-10-28 18:45:40 3487375171 177359624 223 CHOOSE
1 95680 2008-10-28 18:25:41 3487375171 177359624 223 CHOOSE
1 95679 2008-10-28 18:05:40 3487375171 177359624 223 CHOOSE
1 95678 2008-10-28 17:45:39 3487375171 177359624 223 CHOOSE
1 95677 2008-10-28 17:25:41 3487375171 177359624 223 CHOOSE
1 95676 2008-10-28 17:05:40 3487375171 177359624 223 CHOOSE
1 95664 2008-10-28 16:05:41 3487375171 177359624 223 CHOOSE
1 95607 2008-10-28 12:25:40 3487375171 177359624 223 CHOOSE
1 95606 2008-10-28 12:05:40 3487375171 177359624 223 CHOOSE
1 95605 2008-10-28 11:45:39 3487375171 177359624 223 CHOOSE
------------------------------------------- Instance 2 ------------------------------------
INSTANCE_NUMBER SNAP_ID SNAP_TIME HASH_VALUE PLAN_HASH_VALUE COST OPTIMIZER
--------------- ------- ------------------- ---------- --------------- ---------- ----------
2 95954 2008-10-30 09:05:43 3487375171 378545151 28 CHOOSE
2 95933 2008-10-30 08:45:43 3487375171 378545151 28 CHOOSE
2 95932 2008-10-30 08:25:43 3487375171 378545151 28 CHOOSE
2 95931 2008-10-30 08:05:43 3487375171 378545151 28 CHOOSE
2 95930 2008-10-30 07:45:43 3487375171 378545151 28 CHOOSE
2 95929 2008-10-30 07:25:43 3487375171 378545151 28 CHOOSE
2 95928 2008-10-30 07:05:43 3487375171 378545151 28 CHOOSE
2 95927 2008-10-30 06:45:43 3487375171 378545151 28 CHOOSE
2 95926 2008-10-30 06:25:42 3487375171 378545151 28 CHOOSE
2 95925 2008-10-30 06:05:42 3487375171 378545151 28 CHOOSE
2 95924 2008-10-30 05:45:42 3487375171 378545151 28 CHOOSE
2 95913 2008-10-30 05:25:43 3487375171 378545151 28 CHOOSE
2 95912 2008-10-30 05:05:42 3487375171 378545151 28 CHOOSE
2 95911 2008-10-30 04:45:43 3487375171 378545151 28 CHOOSE
2 95910 2008-10-30 04:25:43 3487375171 378545151 28 CHOOSE
2 95909 2008-10-30 04:05:42 3487375171 378545151 28 CHOOSE
2 95908 2008-10-30 03:45:42 3487375171 378545151 28 CHOOSE
2 95907 2008-10-30 03:25:42 3487375171 378545151 28 CHOOSE
2 95906 2008-10-30 03:05:43 3487375171 378545151 28 CHOOSE
2 95905 2008-10-30 02:45:42 3487375171 378545151 28 CHOOSE
2 95904 2008-10-30 02:25:42 3487375171 378545151 28 CHOOSE
2 95893 2008-10-30 02:05:42 3487375171 378545151 28 CHOOSE
2 95892 2008-10-30 01:45:42 3487375171 378545151 28 CHOOSE
2 95891 2008-10-30 01:25:42 3487375171 378545151 28 CHOOSE
2 95890 2008-10-30 01:05:42 3487375171 378545151 28 CHOOSE
2 95889 2008-10-30 00:45:43 3487375171 378545151 28 CHOOSE
2 95888 2008-10-30 00:25:42 3487375171 378545151 28 CHOOSE
2 95887 2008-10-30 00:05:43 3487375171 378545151 28 CHOOSE
2 95886 2008-10-29 23:45:42 3487375171 378545151 28 CHOOSE
2 95885 2008-10-29 23:25:42 3487375171 378545151 28 CHOOSE
2 95873 2008-10-29 22:45:42 3487375171 177359624 223 CHOOSE
2 95872 2008-10-29 22:25:41 3487375171 177359624 223 CHOOSE
2 95871 2008-10-29 22:05:42 3487375171 177359624 223 CHOOSE
2 95870 2008-10-29 21:45:42 3487375171 177359624 223 CHOOSE
2 95869 2008-10-29 21:25:42 3487375171 177359624 223 CHOOSE
2 95868 2008-10-29 21:05:42 3487375171 177359624 223 CHOOSE
2 95867 2008-10-29 20:45:42 3487375171 177359624 223 CHOOSE
2 95866 2008-10-29 20:25:42 3487375171 177359624 223 CHOOSE
2 95865 2008-10-29 20:05:42 3487375171 177359624 223 CHOOSE
2 95864 2008-10-29 19:45:42 3487375171 177359624 223 CHOOSE
2 95853 2008-10-29 19:25:42 3487375171 177359624 223 CHOOSE
2 95852 2008-10-29 19:05:41 3487375171 177359624 223 CHOOSE
2 95851 2008-10-29 18:45:42 3487375171 177359624 223 CHOOSE
2 95850 2008-10-29 18:25:42 3487375171 177359624 223 CHOOSE
2 95849 2008-10-29 18:05:42 3487375171 177359624 223 CHOOSE
2 95848 2008-10-29 17:45:41 3487375171 177359624 223 CHOOSE
2 95847 2008-10-29 17:25:41 3487375171 177359624 223 CHOOSE
2 95846 2008-10-29 17:05:42 3487375171 177359624 223 CHOOSE
2 95845 2008-10-29 16:45:42 3487375171 177359624 223 CHOOSE
2 95844 2008-10-29 16:25:42 3487375171 177359624 223 CHOOSE
2 95830 2008-10-29 16:05:42 3487375171 177359624 223 CHOOSE
2 95827 2008-10-29 15:05:42 3487375171 177359624 223 CHOOSE
2 95826 2008-10-29 14:45:41 3487375171 177359624 223 CHOOSE
2 95825 2008-10-29 14:25:41 3487375171 177359624 223 CHOOSE
2 95824 2008-10-29 14:05:42 3487375171 177359624 223 CHOOSE
2 95803 2008-10-29 13:45:41 3487375171 177359624 223 CHOOSE
2 95802 2008-10-29 13:25:41 3487375171 177359624 223 CHOOSE
2 95801 2008-10-29 13:05:42 3487375171 177359624 223 CHOOSE
2 95800 2008-10-29 12:45:41 3487375171 177359624 223 CHOOSE
2 95799 2008-10-29 12:25:41 3487375171 177359624 223 CHOOSE
2 95798 2008-10-29 12:05:41 3487375171 177359624 223 CHOOSE
2 95797 2008-10-29 11:45:41 3487375171 177359624 223 CHOOSE
2 95796 2008-10-29 11:25:41 3487375171 177359624 223 CHOOSE
2 95794 2008-10-29 10:45:42 3487375171 177359624 223 CHOOSE
2 95787 2008-10-29 10:25:41 3487375171 177359624 223 CHOOSE
2 95785 2008-10-29 09:45:41 3487375171 177359624 223 CHOOSE
2 95771 2008-10-29 08:25:41 3487375171 177359624 223 CHOOSE
2 95770 2008-10-29 08:05:41 3487375171 177359624 223 CHOOSE
2 95769 2008-10-29 07:45:41 3487375171 177359624 223 CHOOSE
2 95768 2008-10-29 07:25:41 3487375171 177359624 223 CHOOSE
2 95767 2008-10-29 07:05:41 3487375171 177359624 223 CHOOSE
2 95766 2008-10-29 06:45:41 3487375171 177359624 223 CHOOSE
2 95765 2008-10-29 06:25:40 3487375171 177359624 223 CHOOSE
2 95764 2008-10-29 06:05:41 3487375171 177359624 223 CHOOSE
2 95753 2008-10-29 05:45:41 3487375171 177359624 223 CHOOSE
2 95752 2008-10-29 05:25:41 3487375171 177359624 223 CHOOSE
2 95751 2008-10-29 05:05:41 3487375171 177359624 223 CHOOSE
2 95750 2008-10-29 04:45:40 3487375171 177359624 223 CHOOSE
2 95749 2008-10-29 04:25:41 3487375171 177359624 223 CHOOSE
2 95748 2008-10-29 04:05:40 3487375171 177359624 223 CHOOSE
2 95747 2008-10-29 03:45:40 3487375171 177359624 223 CHOOSE
2 95746 2008-10-29 03:25:41 3487375171 177359624 223 CHOOSE
2 95745 2008-10-29 03:05:40 3487375171 177359624 223 CHOOSE
2 95744 2008-10-29 02:45:41 3487375171 177359624 223 CHOOSE
2 95733 2008-10-29 02:25:41 3487375171 177359624 223 CHOOSE
2 95732 2008-10-29 02:05:40 3487375171 177359624 223 CHOOSE
2 95731 2008-10-29 01:45:41 3487375171 177359624 223 CHOOSE
2 95730 2008-10-29 01:25:40 3487375171 177359624 223 CHOOSE
2 95729 2008-10-29 01:05:40 3487375171 177359624 223 CHOOSE
2 95728 2008-10-29 00:45:41 3487375171 177359624 223 CHOOSE
2 95727 2008-10-29 00:25:41 3487375171 177359624 223 CHOOSE
2 95726 2008-10-29 00:05:40 3487375171 177359624 223 CHOOSE
2 95725 2008-10-28 23:45:40 3487375171 177359624 223 CHOOSE
2 95724 2008-10-28 23:25:40 3487375171 177359624 223 CHOOSE
2 95713 2008-10-28 23:05:40 3487375171 177359624 223 CHOOSE
2 95712 2008-10-28 22:45:40 3487375171 177359624 223 CHOOSE
2 95711 2008-10-28 22:25:40 3487375171 177359624 223 CHOOSE
2 95710 2008-10-28 22:05:40 3487375171 177359624 223 CHOOSE
2 95709 2008-10-28 21:45:40 3487375171 177359624 223 CHOOSE
2 95708 2008-10-28 21:25:39 3487375171 177359624 223 CHOOSE
2 95707 2008-10-28 21:05:40 3487375171 177359624 223 CHOOSE
2 95706 2008-10-28 20:45:40 3487375171 177359624 223 CHOOSE
2 95705 2008-10-28 20:25:40 3487375171 177359624 223 CHOOSE
2 95704 2008-10-28 20:05:40 3487375171 177359624 223 CHOOSE
2 95693 2008-10-28 19:45:40 3487375171 177359624 223 CHOOSE
2 95692 2008-10-28 19:25:40 3487375171 177359624 223 CHOOSE
2 95691 2008-10-28 19:05:40 3487375171 177359624 223 CHOOSE
2 95690 2008-10-28 18:45:40 3487375171 177359624 223 CHOOSE
2 95689 2008-10-28 18:25:40 3487375171 177359624 223 CHOOSE
2 95684 2008-10-28 16:45:40 3487375171 177359624 223 CHOOSE
2 95656 2008-10-28 16:25:40 3487375171 177359624 223 CHOOSE
2 95655 2008-10-28 16:05:41 3487375171 177359624 223 CHOOSE
2 95602 2008-10-28 13:45:40 3487375171 177359624 223 CHOOSE
2 95601 2008-10-28 13:25:40 3487375171 177359624 223 CHOOSE
2 95600 2008-10-28 13:05:40 3487375171 177359624 223 CHOOSE
2 95599 2008-10-28 12:45:40 3487375171 177359624 223 CHOOSE
2 95598 2008-10-28 12:25:40 3487375171 177359624 223 CHOOSE
2 95597 2008-10-28 12:05:40 3487375171 177359624 223 CHOOSE
2 95596 2008-10-28 11:45:39 3487375171 177359624 223 CHOOSE
2 95595 2008-10-28 11:25:40 3487375171 177359624 223 CHOOSE
2 95594 2008-10-28 11:05:40 3487375171 177359624 223 CHOOSE
2 95567 2008-10-28 10:45:39 3487375171 177359624 223 CHOOSE
可以看出，在2008-10-29 23:25左右时执行计划发生了改变。
查看历史执执行计划：（通过查询v$sql_plan，使用plan_hash_value）
[Execution Plan Information]
--------------------------------------------------------------------------------------------------
| Operation | PHV/Object Name | Rows | Bytes| Cost |
--------------------------------------------------------------------------------------------------
|00[00]SELECT STATEMENT |plan_hash_value 17735| | | 216 |
|01[01]SORT ORDER BY | | 1 | 207 | 216 |
|02[02] FILTER | | | | |
|03[03] TABLE ACCESS BY INDEX ROWID |SYS_FIELD_DESC | 1 | 43 | 2 |
|04[04] NESTED LOOPS | | 1 | 207 | 200 |
|05[05] NESTED LOOPS OUTER | | 1 | 164 | 198 |
|06[06] NESTED LOOPS | | 1 | 111 | 197 |
|07[07] MERGE JOIN CARTESIAN | | 1 | 65 | 5 |
|08[08] TABLE ACCESS FULL |SYS_FIELD_DESC | 1 | 43 | 2 |
|09[08] BUFFER SORT | | 1 | 22 | 3 |
|10[09] TABLE ACCESS BY INDEX ROWID |ACCT_75 | 1 | 22 | 3 |
|11[10] INDEX RANGE SCAN |IDX_ACCT_CODE_75 | 1 | | 2 |
|12[07] TABLE ACCESS BY GLOBAL INDEX ROWID |PT_75 | 1 | 46 | 192 |
|13[08] INDEX RANGE SCAN |IDX_PT_75_ACCTI | 11 | | 2 |
|14[06] VIEW PUSHED PREDICATE | | 9 | 477 | 1 |
|15[07] NESTED LOOPS OUTER | | 1 | 53 | 7 |
|16[08] NESTED LOOPS OUTER | | 1 | 38 | 6 |
|17[09] NESTED LOOPS OUTER | | 1 | 26 | 4 |
|18[10] TABLE ACCESS BY INDEX ROWID |SYS_OPERATOR | 1 | 14 | 2 |
|19[11] INDEX UNIQUE SCAN |PK_SYS_OPERATOR | 1 | | 1 |
|20[10] TABLE ACCESS BY INDEX ROWID |PARTY_ROLE | 1 | 12 | 2 |
|21[11] INDEX RANGE SCAN |IDX_PARTYROLE_PARTY_ | 1 | | 1 |
|22[09] TABLE ACCESS BY INDEX ROWID |PARTY_ROLE_RELAT | 1 | 12 | 2 |
|23[10] INDEX RANGE SCAN |IDX_PARTY2_ROLE_ID | 1 | | 1 |
|24[08] TABLE ACCESS BY INDEX ROWID |PARTY_ROLE | 1 | 15 | 1 |
|25[09] INDEX UNIQUE SCAN |PK_PARTY_ROLE | 1 | | |
|26[05] INDEX RANGE SCAN |SYS_FIELD_DESC_1 | 1 | | 1 |
--------------------------------------------------------------------
通过对比前后执行计划，可以看出，之前的执行计划，走索引IDX_PT_75_ACCTI，而当前走索引IDX_PT_75_DAY。
通过查询dba_objects，发现IDX_PT_75_DAY正是10月29号23点创建的。因而导致了执行计划的改变。
SQL>SELECT owner, object_name, object_type, created, last_ddl_time, status
2 FROM dba_objects
3 WHERE object_name ='IDX_PT_75_DAY';
OWNER OBJECT_NAME OBJECT_TYPE CREATED LAST_DDL_TIME STATUS
-------- ------------------- ------------ ------------------- ------------------- ------
PGATE IDX_PT_75_DAY INDEX 2008-10-29 23:05:22 2008-10-29 23:05:22 VALID
通知应用开发人员，开发人员DROP IDX_PT_75_DAY索引后，数据库恢复正常。
查看执行计划信息：
---------------------------------------------- Instance 1 ------------------------------------
INSTANCE_NUMBER SNAP_ID SNAP_TIME HASH_VALUE PLAN_HASH_VALUE COST OPTIMIZER
--------------- ---------- ------------------- ---------- --------------- ---------- ----------
1 95991 2008-10-30 12:25:43 3487375171 177359624 223 CHOOSE
1 95990 2008-10-30 12:05:43 3487375171 177359624 223 CHOOSE
1 95989 2008-10-30 11:45:43 3487375171 177359624 223 CHOOSE
1 95985 2008-10-30 10:25:43 3487375171 177359624 223 CHOOSE
1 95985 2008-10-30 10:25:43 3487375171 378545151 28 CHOOSE
---------------------------------------------- Instance 2 ------------------------------------
INSTANCE_NUMBER SNAP_ID SNAP_TIME HASH_VALUE PLAN_HASH_VALUE COST OPTIMIZER
--------------- ---------- ------------------- ---------- --------------- ---------- ----------
2 95983 2008-10-30 12:45:43 3487375171 177359624 223 CHOOSE
2 95982 2008-10-30 12:25:43 3487375171 177359624 223 CHOOSE
2 95981 2008-10-30 12:05:43 3487375171 177359624 223 CHOOSE
2 95980 2008-10-30 11:45:43 3487375171 177359624 223 CHOOSE
2 95977 2008-10-30 10:45:43 3487375171 177359624 223 CHOOSE
2 95976 2008-10-30 10:25:43 3487375171 177359624 223 CHOOSE
2 95976 2008-10-30 10:25:43 3487375171 378545151 28 CHOOSE
执行计划已恢复正常。
查看当前执行计划：
[Execution Plan Information]
--------------------------------------------------------------------------------------------------
| Operation | PHV/Object Name | Rows | Bytes| Cost |
--------------------------------------------------------------------------------------------------
|00[00]SELECT STATEMENT |---- 3487375171.0 ---| | | 223 |
|01[01]SORT ORDER BY | | 1 | 218 | 223 |
|02[02] FILTER | | | | |
|03[03] TABLE ACCESS BY INDEX ROWID |SYS_FIELD_DESC | 1 | 43 | 2 |
|04[04] NESTED LOOPS | | 1 | 218 | 207 |
|05[05] NESTED LOOPS OUTER | | 1 | 175 | 205 |
|06[06] NESTED LOOPS | | 1 | 117 | 204 |
|07[07] MERGE JOIN CARTESIAN | | 1 | 65 | 6 |
|08[08] TABLE ACCESS FULL |SYS_FIELD_DESC | 1 | 43 | 2 |
|09[08] BUFFER SORT | | 1 | 22 | 4 |
|10[09] TABLE ACCESS BY INDEX ROWID |ACCT_75 | 1 | 22 | 4 |
|11[10] INDEX RANGE SCAN |IDX_ACCT_CODE_75 | 1 | | 2 |
|12[07] TABLE ACCESS BY GLOBAL INDEX ROWID |PT_75 | 1 | 52 | 198 |
|13[08] INDEX RANGE SCAN |IDX_PT_75_ACCTI | 11 | | 2 |
|14[06] VIEW PUSHED PREDICATE | | 10 | 580 | 1 |
|15[07] NESTED LOOPS OUTER | | 1 | 58 | 7 |
|16[08] NESTED LOOPS OUTER | | 1 | 42 | 6 |
|17[09] NESTED LOOPS OUTER | | 1 | 29 | 4 |
|18[10] TABLE ACCESS BY INDEX ROWID |SYS_OPERATOR | 1 | 15 | 2 |
|19[11] INDEX UNIQUE SCAN |PK_SYS_OPERATOR | 1 | | 1 |
|20[10] TABLE ACCESS BY INDEX ROWID |PARTY_ROLE | 1 | 14 | 2 |
|21[11] INDEX RANGE SCAN |IDX_PARTYROLE_PARTY_ | 1 | | 1 |
|22[09] TABLE ACCESS BY INDEX ROWID |PARTY_ROLE_RELAT | 1 | 13 | 2 |
|23[10] INDEX RANGE SCAN |IDX_PARTY2_ROLE_ID | 1 | | 1 |
|24[08] TABLE ACCESS BY INDEX ROWID |PARTY_ROLE | 1 | 16 | 1 |
|25[09] INDEX UNIQUE SCAN |PK_PARTY_ROLE | 1 | | |
|26[05] INDEX RANGE SCAN |SYS_FIELD_DESC_1 | 1 | | 1 |
--------------------------------------------------------------------
[Predicate Information]
--------------------------------------------------------------------
2 filter::2< =:3
3 filter:"A"."PAYED_METHOD"="D"."VALUE"
8 filter:"C"."TABLE_NAME"='PT' AND "C"."COLUMN_NAME"='CERTIFICATE_T
YPE'
11 access:"B"."ACCT_CODE"=:1
12 filter:"A"."AMOUNT"<>0 AND "A"."CERTIFICATE_TYPE"="C"."VALUE" AND TO_C
HAR("A"."PAY_DATE",'yyyymmdd')>=:2 AND TO_CHAR("A"."PAY_DATE",'yyyymmd
d')< =:3
13 access:"A"."ACCT_ID"="B"."ACCT_ID"
18 filter:NVL("X"."REGION_ID",0)=75 OR NVL("X"."REGION_ID",0)=0
19 access:"A"."STAFF_ID"="X"."OPERATOR_ID"
21 access:"X"."PARTY_ID"="Y"."PARTY_ID"(+)
23 access:"Y"."PARTY_ROLE_ID"="Z"."PARTY2_ROLE_ID"(+)
25 access:"Z"."PARTY1_ROLE_ID"="O"."PARTY_ROLE_ID"(+)
26 access:"D"."TABLE_NAME"='PT_PLAN' AND "D"."COLUMN_NAME"='PT_
METHOD'
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/22578826/viewspace-741756/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
