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

From: Dave Wotton <Dave.Wotton_at_dwotton.nospam.clara.co.uk>
Date: 2000/06/28
Message-ID: <ZDt65.108$Lg5.19426@nnrp3.clara.net>

gdas wrote in message <2308e887.c41854a7_at_usw-ex0102-013.remarq.com>...
> I tried your hint as follows and did receive a different query
> plan, however, the query still runs very slowly. It does seem
> faster though. Initially, the query would run and I would lose
> patience and kill it after about 5 minutes. This time around, I
> think my answer came back in about 3-4 minutes.
 

> Here's the sql I sent:

[snip]

> Query Plan
> ------------------------------------
> SELECT STATEMENT Cost = 1236762
> SORT GROUP BY
> NESTED LOOPS
> NESTED LOOPS
> NESTED LOOPS
> TABLE ACCESS FULL ORG
> COUNT STOPKEY
> VIEW
> SORT ORDER BY STOPKEY
> SORT GROUP BY
> HASH JOIN
> TABLE ACCESS FULL ORG
> HASH JOIN
> TABLE ACCESS FULL SUBJECT
> HASH JOIN
> TABLE ACCESS FULL DATASTAGE
> TABLE ACCESS FULL HIT
> TABLE ACCESS BY INDEX ROWID SUBJECT
> INDEX RANGE SCAN SUBJECT_ORG_ID_IDX
> TABLE ACCESS BY INDEX ROWID HIT
> INDEX RANGE SCAN HIT_SUBJ_ID_IDX
> TABLE ACCESS BY INDEX ROWID DATASTAGE
> INDEX RANGE SCAN DATASTAGE_UQ_ID_IDX
Hmmm. It almost worked, and in theory should have done. The documentation says: "The PUSH_SUBQ hint causes nonmerged subqueries to be evaluated at the earliest possible place in the execution plan."

I'd have thought that means the first operation in the execution plan. The optimiser seems to think it means the second operation, after a FTS of the ORG table. It now seems that the subquery is being executed once for each row in the ORG table. I assume you've got about 50-60 rows in that table (you said the subquery takes about 3 seconds: 3 secs * 60 = 3 minutes). I don't seem to be able to find any combination of hints which will push it higher.

Interestingly, the HIT table is now being accessed by an indexed retrieval using the HIT_SUBJ_ID_IDX index. Try removing the /*+ PUSH_SUBQ */ hint but leave the '+ 0's. This will drop the subquery back to the end of the execution plan, but hopefully leave the indexed retrieval on the HIT table. It *might* be faster. The question is whether returning all rows in the (ORG,DATASTAGE,HIT,SUBJECT) join + one execution of the subquery is quicker than executing the subquery once for each ORG + executing the join (DATASTAGE, HIT, SUBJECT) for the single ORG which gets through the subquery.

Another approach might be to leave the PUSH_SUBQ hint in and try to speed up the subquery. An index on DATASTAGE.ACCOUNT_ID might do the trick. If you can make it run in a fraction of a second, then this will have a significant effect on the main query as it is being executed once for each ORG.

Other than that I've run out of ideas and suggest you revert to my original suggestion of splitting the query into two separate queries which can be optimised independently, giving a total running time of a couple of seconds.

> Also, I've never seen the tactic of adding '+ 0' or
> concatenating null to columns in joins. I always thought that
> expressions in the join clause would effectively disable the
> indexes (unless of course you're using function based indexes).
> I know I've picked your brain enough about this tuning question,
> but can you briefly explain why this tactic in the join clause
> works?

You're right: I'm selectively disabling indexes to force the query to be optimised the way I want. Let's look at a simpler example:

   select d.dname, d.deptno, e.empno, e.ename

     from dept d,
          emp e

    where d.deptno = e.deptno

Assume there are indexes DEPT$DEPTNO and EMP$DEPTNO on DEPT.DEPTNO and EMP.DEPTNO respectively.

The optimiser can execute this in one of two ways:

     NESTED LOOPS
        TABLE ACCESS FULL DEPT
        TABLE ACCESS BY INDEX ROWID EMP
          INDEX RANGE SCAN EMP$DEPTNO

which can be visualised as pseudo-code:

    for each row in DEPT /* Full table scan     {

       read rows from EMP using index EMP$DEPTNO
       where EMP.DEPTNO = current DEPT.DEPTNO
       {
           build row from all available data
       }

    }

or

     NESTED LOOPS
        TABLE ACCESS FULL EMP
        TABLE ACCESS BY INDEX ROWID DEPT
          INDEX RANGE SCAN DEPT$DEPTNO

which can be visualised as pseudo-code:

    for each row in EMP /* Full table scan     {

       read rows from DEPT using index DEPT$DEPTNO
       where DEPT.DEPTNO = current EMP.DEPTNO
       {
           build row from all available data
       }

    }

if we change the condition to be:

   where e.deptno = d.deptno + 0

we disable the optimiser from using the index on DEPT.DEPTNO so it is unable to use the second query plan. It is forced to optimise using DEPT as the driving table. Conversely, changing the condition to:

   where e.deptno + 0 = d.deptno

prevents the optimiser using the first query plan. It is forced to optimise using EMP as the driving table.

In this example it makes no difference. But if there was another non-indexed condition on EMP (say) then we would want to force the second execution plan.

This tuning technique dates back to before the CBO and the hints syntax, but still works and is unlikely ever to be designed out. Of course, you could use hints to do the same job but I find it more manageable in some cases as a means of controlling the optimiser than using hints (especially where you might want to specify multiple 'hints'). But remember, you are *forcing* the optimiser to work in a certain way, so you, the query designer, take responsibility for the performance of the query, rather than delegating that responsibility to the optimiser. But sometimes you can do a better job than the optimiser can :-)

Dave.

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

Received on Wed Jun 28 2000 - 00:00:00 CDT