Re: why does this statement take so long to return?

Hi Gavin/Yong,

Yong: I agree, the documentation says the USE_NL hint takes a table parameter, but when I tried it, it didn't work (Oracle V8.1.5.0.0, Solaris 2.5.1). Without the table parameter I got a nested loops execution plan, as I expected. Using a table parameter, I got a hash join! I tried both "table" names: e1 and e2. Changing the table-name changed the order in which the tables/views were joined (as you'd expect), so the hint was being used, but on both occasions a hash join was used. I suspect this is a bug.

Gavin: Unfortunately, this dump of the plan table doesn't show the structure of the query. To be able to work out what's happening we need to know how each step is related to the others. ie. we need to know the value of the PARENT_ID column in the plan_table. Far better is to use a query on the plan_table which shows the structure of the query.

There are three ways of doing this (you probably know this, but just in case)

1. Create a plan_table for your userid using the script: $ORACLE_HOME/dbms/admin/utlxplan.sql

    Execute your procedure using ....

        EXPLAIN PLAN SET STATEMENT_ID = 'something' FOR
           <your query>;

    Display the plan using the query:

        SELECT LPAD(' ',2*(LEVEL-1))||operation||' '||options||' '||
               object_name ||' '||
               DECODE(id, 0, 'Cost = '||position) "Query Plan"
          FROM plan_table
         START WITH id = 0
           AND statement_id = '&&1'
       CONNECT BY PRIOR id = parent_id
           AND statement_id ='&&1';

2) Use the autotrace facility under SQL*Plus:

    SET AUTOTRACE ON     Execute your query.

(Your system administrator (you?) may need to execute the

     $ORACLE_HOME/sqlplus/admin/plustrce.sql script first to create the
     PLUSTRACE role, then issue the command GRANT PLUSTRACE TO
     <YOUR ORACLE LOGIN ID>; to enable you to use this facility)

3) Use tkprof to analyse the trace file for a session which executes

    the query.

    First switch on tracing for your session: Under SQL*Plus issue the     command: ALTER SESSION SET SQL_TRACE = TRUE;

    Then execute your query.

    Then locate your trace file. It'll be under the directory specified     by user_dump_dest in your database's init.ora parameters.

    Then execute the utility tkprof on it:

        tkprof <trace file> <output file> EXPLAIN=userid/password

    Where userid/password are the logon userid/password of the person who     executed the query. This userid will need to have a plan_table set up     using utlxplan as described in (1) above.

(It is possible to switch on monitoring for one session from within

     another, or to switch on tracing for ALL database sessions, but I
     won't go into this here.)

In all three cases, the output from the explain plan will be in a tree-format which shows the structure of the query so we can analyse it.

Dave.

--
If you reply to this posting by email, remove the "nospam" from my email
address first.