Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2002
    Location
    Chennai, India
    Posts
    184

    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.

    How can this issue be resolved?

    Thanks in advance.

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Aug 2002
    Location
    Chennai, India
    Posts
    184
    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.

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Just a thought ... Have you tried increasing stmtheap and checked if this makes a difference ?

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Aug 2002
    Location
    Chennai, India
    Posts
    184
    STMTHEAP is about 64 MB.
    SQL statement heap (4KB) (STMTHEAP) = 16384
    However, let me check and see if this makes a difference.

    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •