Re: Explain plan from hell

Hi!

>The SQL is about 4 pages long,

The plan report is very good indeed.
So short and simple.
I have seen tens and tens pages long plans.

Now you have found the reason.
Next step is to fix it ;)

"Muz" <themuzicman_at_gmail.com> wrote in message news:1161361675.138248.216860_at_i42g2000cwa.googlegroups.com...
> Well, it's not hard to interpret, but I had to share this:
>
> Never seen an explain plan want to return 5.2 sextillion bytes
> before...
>
> Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
>
> SELECT STATEMENT 10568368 G 52751 G
> VIEW 10568368 G 4921280203G 52751 G
> SORT ORDER BY 10568368 G 4350411700G 52751 G
>
> MERGE JOIN OUTER 10568368 G 4350411700G 43345 G
>
> SORT JOIN 10568368 G 3858283679G 43345 G
>
> VIEW 10568368 G 3858283679G 33945 G
>
> SORT ORDER BY 10568368 G 4606318270G 33945 G
>
> MERGE JOIN OUTER 10568368 G 4606318270G 24552 G
>
> SORT JOIN 10568368 G 4340569139G 24552 G
>
> VIEW 10568368 G 4340569139G 15153 G
>
> SORT ORDER BY 10568368 G 3681117592G 15153 G
>
> MERGE JOIN CARTESIAN 10568368 G 3681117592G 5756
> G
> MERGE JOIN CARTESIAN 237 G 80849G 137 M
>
> MERGE JOIN CARTESIAN 5 M 1G 4165
>
> MERGE JOIN CARTESIAN 1 K 448 K 17
>
> MERGE JOIN CARTESIAN 1 340 9
>
> MERGE JOIN CARTESIAN 1 336 6
>
> MERGE JOIN CARTESIAN 1 331 5
>
> MERGE JOIN CARTESIAN 1 328 4
>
> TABLE ACCESS
> FULL WRK.WRK_ETL_IRIS_DMR_ISSUE 1 325 3
> BUFFER SORT 20 60 1
>
> INDEX FULL
> SCAN DIM.IQM_MATERIAL_ERROR_DIM_PK 20 60 2
>
> BUFFER SORT 20 60 3
>
> INDEX FULL
> SCAN DIM.IQM_MATERIAL_ERROR_DIM_PK 20 60 2
>
> BUFFER SORT 700 3 K 4
>
> INDEX FULL
> SCAN DIM.IQM_DEFECT_CLASSIFICATION_PK 700 3 K 2
>
> BUFFER SORT 3 K 15 K 7
>
> INDEX FAST FULL
> SCAN DIM.EDW_FISCAL_TIME_DIM_PK 3 K 15 K 4
> BUFFER SORT 1 K 15 K 13
>
> TABLE ACCESS
> FULL LKP.EDW_IDENTIFIER_LKP 1 K 15 K 9
> BUFFER SORT 3 K 15 K 4156
>
> INDEX FAST FULL
> SCAN DIM.EDW_FISCAL_TIME_DIM_PK 3 K 15 K 4
> BUFFER SORT 47 K 370 K 137 M
>
> INDEX FAST FULL
> SCAN DIM.EDW_SUPPLIER_DIM_PK 47 K 370 K 28
> BUFFER SORT 44 K 390 K 15153 G
>
> INDEX FAST FULL
> SCAN DIM.EDW_DMR_ISSUE_DIM_PK 44 K 390 K 25
> FILTER
> SORT JOIN
> INDEX FAST FULL
> SCAN DIM.EDW_PLANT_PROFILE_DIM_AK4 925 24 K 3
>
> SORT JOIN 906 K 43 M 24134
> TABLE ACCESS FULL DIM.EDW_PLANT_PART_DIM 906 K 43 M 16819
>
Received on Mon Oct 23 2006 - 06:33:59 CDT