There is a complex query involving 12 tables (1 very huge table & 5 huge tables) having a cost of about 400 timerons. However, to generate the access plan it takes about 15 minutes. The optimization level is at default 5. If the optimization level is changed to 1, the access plan is generated instantaneously. No aggregate function is used in the SQL statement.

Cannot change DFT_QUERYOPT as this will affect all SQL statements from the application to the database.

Did consider changing DFT_DEGREE, however, this applies to all SQL statements which could affect online.

How can this issue be resolved?

Thanks in advance.

Are you telling about the Visual explain tool taking long to show the explain path ... if this is the case, I wouldn't bother how long it takes to generate the access plan ...What is the query compile time (can be captured from the stmt snapshot) for the two cases as compared to the query execution time?

Have you considered SET CURRENT QUERY OPTIMIZATION stmt?

Sathyaram

Thanks for the input.

The time taken for the access plan to be displayed is the approximate compile time for the SQL... which is about 15 minutes... however the cost of the SQL is 200 timerons.

Have now resolved to use 'SET CURRENT QUERY OPTIMIZATION' for select SQL where the compile time is high.

Just a thought ... Have you tried increasing stmtheap and checked if this makes a difference ?

Sathyaram

STMTHEAP is about 64 MB.
SQL statement heap (4KB) (STMTHEAP) = 16384
However, let me check and see if this makes a difference.

Thanks