对比前后执行计划，发现问题
作者 ：OoNiceDream【转载时请务必以超链接形式标明文章原始出处和作者信息】
链接：http://www.dbaroad.me/archives/2008/11/find-sqlplan-differ.html
链接：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" |
查看其历史执行计划信息：（通过查询V$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' |
— The End —
关键字: SQL优化 | 性能优化
站内搜索