Unanswered: Access plan takes long - visual explain
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.
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?
Visit the new-look IDUG Website , register to gain access to the excellent content.