Re: Tuning a Query

Sanjay Mishra wrote:
>
> I am having difficulty in tuning the following query. It takes more
> than one hour to insert 1.4 million rows.
>
> INSERT INTO TABLE_O (c1, c2, c3, c4, c5, c6)
> SELECT
> TABLE_A.c1 , TABLE_A.c2 , TABLE_B.MEASURE_ID ,
> TABLE_C.BUCKET_ID , TABLE_A.QUANTITY , TABLE_A.partition_id
> FROM
> TABLE_C , TABLE_B , TABLE_A
> WHERE TABLE_A.c2 = TABLE_B.c2
> AND TABLE_A.c1 = TABLE_B.c1
> AND TABLE_B.c3 = TABLE_C.c3
> AND TABLE_B.MEASURE_ID = 'FORECAST'
> AND TABLE_C.START_DATE = TABLE_A.START_DATE
> AND TABLE_C.END_DATE = TABLE_A.END_DATE
> AND TABLE_C.BUCKET_ID = ( SELECT
> (bs.BUCKET_ID )
> FROM
> TABLE_C bs , TABLE_D tp
> WHERE tp.FC_CURRENT < bs.END_DATE
> AND tp.FC_CURRENT >= bs.START_DATE )
>
> The OPTIMIZER_MODE = CHOOSE. I have analyzed all the tables using
> COMPUTE.
>
> Number of records in the tables:
>
> TABLE_C : 168
> TABLE_B : 2935956
> TABLE_A : 17615736
> TABLE_D : 1
> TABLE_O : 3019096(prior to INSERT)
>
> Number of records expected to be inserted: 1467978
>
> Each of these tables (except TABLE_D) has just one index, the primary
> key, and they are:
>
> TABLE_C : (c3, BUCKET_ID)
> TABLE_B : (c1, c2, MEASURE_ID)
> TABLE_A : (c1, c2, START_DATE)
> TABLE_D : No primary key. No index.
> TABLE_O : (c1, c2, MEASURE_ID, BUCKET_ID)
>
> The explain plan is:
> INSERT STATEMENT Cost = 7381
> FILTER
> HASH JOIN
> NESTED LOOPS
> TABLE ACCESS FULL TABLE_C
> TABLE ACCESS FULL TABLE_A
> TABLE ACCESS FULL TABLE_B
> NESTED LOOPS
> TABLE ACCESS FULL TABLE_D
> TABLE ACCESS FULL TABLE_C
>
> I am surprised to see that all the tables are being accessed in FULL
> TABLE SCANS.
>
> I tried to use a hint to use the index on the largest table, i.e.,
> TABLE_A. The new explain plan is:
>
> INSERT STATEMENT Cost = 1196
> FILTER
> HASH JOIN
> NESTED LOOPS
> TABLE ACCESS FULL TABLE_C
> TABLE ACCESS BY INDEX ROWID TABLE_A
> INDEX FULL SCAN PK_TABLE_A
> TABLE ACCESS FULL TABLE_B
> NESTED LOOPS
> TABLE ACCESS FULL TABLE_D
> TABLE ACCESS FULL TABLE_C
>
> Even though the cost has reduced significantly, there was hardly any
> improvement in the running time of the query.
>
> I have tried using different ordering of the tables in the FROM
> clause,
> and different ordering of conditions in the WHERE clause, but of no
> avail.
> (I believe that these ordering doesn't matter in CBO, anyway).
>
> I tried using ORDERED and FIRST_ROWS hints as well, but of no avail.
>
> Any suggestion to improve the performance of the query is highly
> appreciated.
>
> Thanks,

Did you include INDEXED COLUMNAS in your analyze? Did you analyze the indexes? Next step, try hints.

-- 
Ron Reidy
Oracle DBA
Reidy Consulting, L.L.C.