Re: Huge Execution Plan Cost Change For Fixed Tables

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Wed, 25 Jun 2008 11:39:28 -0700 (PDT)
Message-ID: <2806a53c-b6b0-4898-8491-29ea9ca98031@i76g2000hsf.googlegroups.com>


On Jun 25, 1:53 pm, saurangshu <saurang..._at_gmail.com> wrote:

> Hi,
>
> We are using certain sql to get the column information for a group of
> tables (in Oracle 10gR2 database). The sql is based on the underline
> Oracle data dictionary tables/views and it is taking a huge amount of
> time to execute only after the recent restoration of our development
> database.

I see at least one Cartesian Merge Join in the plan that you posted. You might want to look at this thread, which appears to be related: "A potential bug (infinite loop) in Oracle: querying v$access" http://groups.google.com/group/comp.databases.oracle.server/browse_thread/thread/e0599d3e043fc199

As Mark mentions, cost alone should not be the only consideration with a query, but the plan might be important. The query that you posted, when executed on one of my databases, produces the following DBMS Xplan output (note that there is no Cartesian Merge Join in the plan):



| Id | Operation |
Name | Starts | A-Rows | A-Time | Buffers |

| 1 | SORT UNIQUE
| | 1 | 0 |00:00:00.01 | 3 |
| 2 | UNION-ALL
| | 1 | 0 |00:00:00.01 | 3 |
|*  3 |    FILTER

| | 1 | 0 |00:00:00.01 | 1 |
| 4 | NESTED LOOPS OUTER
| | 1 | 0 |00:00:00.01 | 1 |
| 5 | NESTED LOOPS OUTER
| | 1 | 0 |00:00:00.01 | 1 |
| 6 | NESTED LOOPS OUTER
| | 1 | 0 |00:00:00.01 | 1 |
| 7 | NESTED LOOPS OUTER
| | 1 | 0 |00:00:00.01 | 1 |
| 8 | NESTED LOOPS
| | 1 | 0 |00:00:00.01 | 1 |
| 9 | NESTED LOOPS
| | 1 | 0 |00:00:00.01 | 1 |
| 10 | NESTED LOOPS
| | 1 | 0 |00:00:00.01 | 1 |
| 11 | NESTED LOOPS
| | 1 | 0 |00:00:00.01 | 1 |
| 12 | TABLE ACCESS BY INDEX ROWID | USER
$ | 1 | 0 |00:00:00.01 | 1 | |* 13 | INDEX UNIQUE SCAN | I_USER1 | 1 | 0 |00:00:00.01 | 1 |
| 14 | TABLE ACCESS BY INDEX ROWID | USER
$ | 0 | 0 |00:00:00.01 | 0 | |* 15 | INDEX UNIQUE SCAN | I_USER1 | 0 | 0 |00:00:00.01 | 0 |
| 16 | TABLE ACCESS BY INDEX ROWID | OBJ
$ | 0 | 0 |00:00:00.01 | 0 | |* 17 | INDEX RANGE SCAN | I_OBJ2 | 0 | 0 |00:00:00.01 | 0 |
| 18 | TABLE ACCESS BY INDEX ROWID | OBJ
$ | 0 | 0 |00:00:00.01 | 0 | |* 19 | INDEX RANGE SCAN | I_OBJ2 | 0 | 0 |00:00:00.01 | 0 | |* 20 | TABLE ACCESS CLUSTER | COL $ | 0 | 0 |00:00:00.01 | 0 | |* 21 | INDEX UNIQUE SCAN | I_OBJ# | 0 | 0 |00:00:00.01 | 0 | |* 22 | TABLE ACCESS CLUSTER | COLTYPE $ | 0 | 0 |00:00:00.01 | 0 | |* 23 | INDEX RANGE SCAN | I_HH_OBJ#_INTCOL# | 0 | 0 |00:00:00.01 | 0 | |* 24 | TABLE ACCESS BY INDEX ROWID | OBJ $ | 0 | 0 |00:00:00.01 | 0 | |* 25 | INDEX RANGE SCAN | I_OBJ3 | 0 | 0 |00:00:00.01 | 0 |
| 26 | TABLE ACCESS CLUSTER | USER
$ | 0 | 0 |00:00:00.01 | 0 | |* 27 | INDEX UNIQUE SCAN | I_USER# | 0 | 0 |00:00:00.01 | 0 | |* 28 | TABLE ACCESS BY INDEX ROWID | SUM $ | 0 | 0 |00:00:00.01 | 0 | |* 29 | INDEX UNIQUE SCAN | I_SUM $_1 | 0 | 0 |00:00:00.01 | 0 | |* 30 | TABLE ACCESS BY INDEX ROWID | IND $ | 0 | 0 |00:00:00.01 | 0 | |* 31 | INDEX UNIQUE SCAN | I_IND1 | 0 | 0 |00:00:00.01 | 0 | |* 32 | TABLE ACCESS BY INDEX ROWID | OBJAUTH $ | 0 | 0 |00:00:00.01 | 0 |
| 33 | NESTED LOOPS
| | 0 | 0 |00:00:00.01 | 0 |
| 34 | FIXED TABLE FULL | X
$KZSRO | 0 | 0 |00:00:00.01 | 0 | |* 35 | INDEX RANGE SCAN | I_OBJAUTH2 | 0 | 0 |00:00:00.01 | 0 | |* 36 | FIXED TABLE FULL | X $KZSPR | 0 | 0 |00:00:00.01 | 0 | |* 37 | FIXED TABLE FULL | X $KZSPR | 0 | 0 |00:00:00.01 | 0 | |* 38 | FIXED TABLE FULL | X $KZSPR | 0 | 0 |00:00:00.01 | 0 | |* 39 | FIXED TABLE FULL | X $KZSPR | 0 | 0 |00:00:00.01 | 0 | |* 40 | FIXED TABLE FULL | X $KZSPR | 0 | 0 |00:00:00.01 | 0 | |* 41 | FIXED TABLE FULL | X $KZSPR | 0 | 0 |00:00:00.01 | 0 | |* 42 | FIXED TABLE FULL | X $KZSPR | 0 | 0 |00:00:00.01 | 0 | |* 43 | FIXED TABLE FULL | X $KZSPR | 0 | 0 |00:00:00.01 | 0 | |* 44 | FIXED TABLE FULL | X $KZSPR | 0 | 0 |00:00:00.01 | 0 | |* 45 | FIXED TABLE FULL | X $KZSPR | 0 | 0 |00:00:00.01 | 0 | |* 46 | FIXED TABLE FULL | X $KZSPR | 0 | 0 |00:00:00.01 | 0 | |* 47 | FIXED TABLE FULL | X $KZSPR | 0 | 0 |00:00:00.01 | 0 | |* 48 | FIXED TABLE FULL | X $KZSPR | 0 | 0 |00:00:00.01 | 0 | |* 49 | FIXED TABLE FULL | X $KZSPR | 0 | 0 |00:00:00.01 | 0 | |* 50 | FIXED TABLE FULL | X $KZSPR | 0 | 0 |00:00:00.01 | 0 |
| 51 | VIEW
| | 0 | 0 |00:00:00.01 | 0 |
| 52 | FAST DUAL
| | 0 | 0 |00:00:00.01 | 0 |
|* 53 | FIXED TABLE FULL | X $KZSPR | 0 | 0 |00:00:00.01 | 0 | |* 54 | FIXED TABLE FULL | X $KZSPR | 0 | 0 |00:00:00.01 | 0 | |* 55 | FIXED TABLE FULL | X $KZSPR | 0 | 0 |00:00:00.01 | 0 | |* 56 | FIXED TABLE FULL | X $KZSPR | 0 | 0 |00:00:00.01 | 0 | |* 57 | FIXED TABLE FULL | X $KZSPR | 0 | 0 |00:00:00.01 | 0 | |* 58 | FIXED TABLE FULL | X $KZSPR | 0 | 0 |00:00:00.01 | 0 | |* 59 | TABLE ACCESS CLUSTER | TAB $ | 0 | 0 |00:00:00.01 | 0 | |* 60 | INDEX UNIQUE SCAN | I_OBJ# | 0 | 0 |00:00:00.01 | 0 |
| 61 | NESTED LOOPS
| | 0 | 0 |00:00:00.01 | 0 |
| 62 | FIXED TABLE FULL | X
$KZSRO | 0 | 0 |00:00:00.01 | 0 | |* 63 | INDEX RANGE SCAN | I_OBJAUTH2 | 0 | 0 |00:00:00.01 | 0 | |* 64 | FIXED TABLE FULL | X $KZSPR | 0 | 0 |00:00:00.01 | 0 | |* 65 | FILTER
| | 1 | 0 |00:00:00.01 | 2 |
| 66 | NESTED LOOPS OUTER
| | 1 | 0 |00:00:00.01 | 2 |
| 67 | NESTED LOOPS OUTER
| | 1 | 0 |00:00:00.01 | 2 |
| 68 | NESTED LOOPS OUTER
| | 1 | 0 |00:00:00.01 | 2 |
| 69 | NESTED LOOPS OUTER
| | 1 | 0 |00:00:00.01 | 2 |
| 70 | NESTED LOOPS
| | 1 | 0 |00:00:00.01 | 2 |
| 71 | NESTED LOOPS
| | 1 | 0 |00:00:00.01 | 2 |
| 72 | NESTED LOOPS
| | 1 | 0 |00:00:00.01 | 2 |
| 73 | VIEW |
ALL_SYNONYMS | 1 | 0 |00:00:00.01 | 2 |
| 74 | SORT UNIQUE
| | 1 | 0 |00:00:00.01 | 2 |
| 75 | UNION-ALL
| | 1 | 0 |00:00:00.01 | 2 |
|* 76 | FILTER
| | 1 | 0 |00:00:00.01 | 1 |
| 77 | NESTED LOOPS
| | 1 | 0 |00:00:00.01 | 1 |
| 78 | NESTED LOOPS
| | 1 | 0 |00:00:00.01 | 1 |
| 79 | TABLE ACCESS BY INDEX ROWID | USER
$ | 1 | 0 |00:00:00.01 | 1 | |* 80 | INDEX UNIQUE SCAN | I_USER1 | 1 | 0 |00:00:00.01 | 1 | |* 81 | TABLE ACCESS BY INDEX ROWID | OBJ $ | 0 | 0 |00:00:00.01 | 0 | |* 82 | INDEX RANGE SCAN | I_OBJ2 | 0 | 0 |00:00:00.01 | 0 |
| 83 | TABLE ACCESS BY INDEX ROWID | SYN
$ | 0 | 0 |00:00:00.01 | 0 | |* 84 | INDEX UNIQUE SCAN | I_SYN1 | 0 | 0 |00:00:00.01 | 0 | |* 85 | FILTER
| | 0 | 0 |00:00:00.01 | 0 |
|* 86 | FILTER
| | 0 | 0 |00:00:00.01 | 0 |
| 87 | NESTED LOOPS
| | 0 | 0 |00:00:00.01 | 0 |
| 88 | NESTED LOOPS
| | 0 | 0 |00:00:00.01 | 0 |
| 89 | TABLE ACCESS BY INDEX ROWID | USER
$ | 0 | 0 |00:00:00.01 | 0 | |* 90 | INDEX UNIQUE SCAN | I_USER1 | 0 | 0 |00:00:00.01 | 0 |
| 91 | TABLE ACCESS BY INDEX ROWID | OBJ
$ | 0 | 0 |00:00:00.01 | 0 | |* 92 | INDEX RANGE SCAN | I_OBJ2 | 0 | 0 |00:00:00.01 | 0 | |* 93 | INDEX RANGE SCAN | I_OBJAUTH1 | 0 | 0 |00:00:00.01 | 0 | |* 94 | FIXED TABLE FULL | X $KZSRO | 0 | 0 |00:00:00.01 | 0 | |* 95 | FIXED TABLE FULL | X $KZSPR | 0 | 0 |00:00:00.01 | 0 |
| 96 | NESTED LOOPS
| | 1 | 0 |00:00:00.01 | 1 |
| 97 | NESTED LOOPS
| | 1 | 0 |00:00:00.01 | 1 |
| 98 | NESTED LOOPS
| | 1 | 0 |00:00:00.01 | 1 |
| 99 | TABLE ACCESS BY INDEX ROWID | USER
$ | 1 | 0 |00:00:00.01 | 1 | |*100 | INDEX UNIQUE SCAN | I_USER1 | 1 | 0 |00:00:00.01 | 1 |
| 101 | VIEW |
_ALL_SYNONYMS_TREE | 0 | 0 |00:00:00.01 | 0 | |*102 | CONNECT BY WITHOUT FILTERING
| | 0 | 0 |00:00:00.01 | 0 |
|*103 | FILTER
| | 0 | 0 |00:00:00.01 | 0 |
| 104 | COUNT
| | 0 | 0 |00:00:00.01 | 0 |
| 105 | NESTED LOOPS
| | 0 | 0 |00:00:00.01 | 0 |
| 106 | NESTED LOOPS
| | 0 | 0 |00:00:00.01 | 0 |
| 107 | NESTED LOOPS
| | 0 | 0 |00:00:00.01 | 0 |
| 108 | NESTED LOOPS
| | 0 | 0 |00:00:00.01 | 0 |
| 109 | TABLE ACCESS FULL | USER
$ | 0 | 0 |00:00:00.01 | 0 |
| 110 | TABLE ACCESS BY INDEX ROWID| OBJ
$ | 0 | 0 |00:00:00.01 | 0 | |*111 | INDEX RANGE SCAN | I_OBJ2 | 0 | 0 |00:00:00.01 | 0 |
| 112 | TABLE ACCESS BY INDEX ROWID | SYN
$ | 0 | 0 |00:00:00.01 | 0 | |*113 | INDEX UNIQUE SCAN | I_SYN1 | 0 | 0 |00:00:00.01 | 0 |
| 114 | TABLE ACCESS BY INDEX ROWID | USER
$ | 0 | 0 |00:00:00.01 | 0 | |*115 | INDEX UNIQUE SCAN | I_USER1 | 0 | 0 |00:00:00.01 | 0 | |*116 | TABLE ACCESS BY INDEX ROWID | OBJ $ | 0 | 0 |00:00:00.01 | 0 | |*117 | INDEX RANGE SCAN | I_OBJ2 | 0 | 0 |00:00:00.01 | 0 | |*118 | FILTER
| | 0 | 0 |00:00:00.01 | 0 |
| 119 | NESTED LOOPS
| | 0 | 0 |00:00:00.01 | 0 |
| 120 | NESTED LOOPS
| | 0 | 0 |00:00:00.01 | 0 |
| 121 | NESTED LOOPS
| | 0 | 0 |00:00:00.01 | 0 |
|*122 | TABLE ACCESS BY INDEX ROWID | SYN $ | 0 | 0 |00:00:00.01 | 0 | |*123 | INDEX UNIQUE SCAN | I_SYN1 | 0 | 0 |00:00:00.01 | 0 |
| 124 | TABLE ACCESS BY INDEX ROWID | USER
$ | 0 | 0 |00:00:00.01 | 0 | |*125 | INDEX UNIQUE SCAN | I_USER1 | 0 | 0 |00:00:00.01 | 0 |
| 126 | TABLE ACCESS BY INDEX ROWID | OBJ
$ | 0 | 0 |00:00:00.01 | 0 | |*127 | INDEX RANGE SCAN | I_OBJ2 | 0 | 0 |00:00:00.01 | 0 | |*128 | INDEX RANGE SCAN | I_OBJAUTH1 | 0 | 0 |00:00:00.01 | 0 | |*129 | FIXED TABLE FULL | X $KZSRO | 0 | 0 |00:00:00.01 | 0 |
| 130 | COUNT
| | 0 | 0 |00:00:00.01 | 0 |
| 131 | NESTED LOOPS
| | 0 | 0 |00:00:00.01 | 0 |
| 132 | NESTED LOOPS
| | 0 | 0 |00:00:00.01 | 0 |
| 133 | NESTED LOOPS
| | 0 | 0 |00:00:00.01 | 0 |
| 134 | NESTED LOOPS
| | 0 | 0 |00:00:00.01 | 0 |
| 135 | TABLE ACCESS FULL | USER
$ | 0 | 0 |00:00:00.01 | 0 |
| 136 | TABLE ACCESS BY INDEX ROWID | OBJ
$ | 0 | 0 |00:00:00.01 | 0 | |*137 | INDEX RANGE SCAN | I_OBJ2 | 0 | 0 |00:00:00.01 | 0 |
| 138 | TABLE ACCESS BY INDEX ROWID | SYN
$ | 0 | 0 |00:00:00.01 | 0 | |*139 | INDEX UNIQUE SCAN | I_SYN1 | 0 | 0 |00:00:00.01 | 0 |
| 140 | TABLE ACCESS BY INDEX ROWID | USER
$ | 0 | 0 |00:00:00.01 | 0 | |*141 | INDEX UNIQUE SCAN | I_USER1 | 0 | 0 |00:00:00.01 | 0 | |*142 | TABLE ACCESS BY INDEX ROWID | OBJ $ | 0 | 0 |00:00:00.01 | 0 | |*143 | INDEX RANGE SCAN | I_OBJ2 | 0 | 0 |00:00:00.01 | 0 | |*144 | TABLE ACCESS BY INDEX ROWID | OBJ $ | 0 | 0 |00:00:00.01 | 0 | |*145 | INDEX UNIQUE SCAN | I_OBJ1 | 0 | 0 |00:00:00.01 | 0 |
| 146 | TABLE ACCESS BY INDEX ROWID | SYN
$ | 0 | 0 |00:00:00.01 | 0 | |*147 | INDEX UNIQUE SCAN | I_SYN1 | 0 | 0 |00:00:00.01 | 0 |
| 148 | TABLE ACCESS BY INDEX ROWID | USER
$ | 0 | 0 |00:00:00.01 | 0 | |*149 | INDEX UNIQUE SCAN | I_USER1 | 0 | 0 |00:00:00.01 | 0 |
| 150 | TABLE ACCESS BY INDEX ROWID | OBJ
$ | 0 | 0 |00:00:00.01 | 0 | |*151 | INDEX RANGE SCAN | I_OBJ2 | 0 | 0 |00:00:00.01 | 0 | |*152 | TABLE ACCESS CLUSTER | COL $ | 0 | 0 |00:00:00.01 | 0 | |*153 | INDEX UNIQUE SCAN | I_OBJ# | 0 | 0 |00:00:00.01 | 0 | |*154 | TABLE ACCESS CLUSTER | COLTYPE $ | 0 | 0 |00:00:00.01 | 0 | |*155 | INDEX RANGE SCAN | I_HH_OBJ#_INTCOL# | 0 | 0 |00:00:00.01 | 0 | |*156 | TABLE ACCESS BY INDEX ROWID | OBJ $ | 0 | 0 |00:00:00.01 | 0 | |*157 | INDEX RANGE SCAN | I_OBJ3 | 0 | 0 |00:00:00.01 | 0 |
| 158 | TABLE ACCESS CLUSTER | USER
$ | 0 | 0 |00:00:00.01 | 0 | |*159 | INDEX UNIQUE SCAN | I_USER# | 0 | 0 |00:00:00.01 | 0 | |*160 | TABLE ACCESS CLUSTER | TAB $ | 0 | 0 |00:00:00.01 | 0 | |*161 | INDEX UNIQUE SCAN | I_OBJ# | 0 | 0 |00:00:00.01 | 0 |
| 162 | NESTED LOOPS
| | 0 | 0 |00:00:00.01 | 0 |
| 163 | FIXED TABLE FULL | X
$KZSRO | 0 | 0 |00:00:00.01 | 0 | |*164 | INDEX RANGE SCAN | I_OBJAUTH2 | 0 | 0 |00:00:00.01 | 0 | |*165 | FIXED TABLE FULL | X $KZSPR | 0 | 0 |00:00:00.01 | 0 | ---------------------------------------------------------------------------------------------------------------------------

You might also consider switching to a UNION ALL, rather than using a UNION. Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Wed Jun 25 2008 - 13:39:28 CDT

Original text of this message