Re: Tuning question...

From: Alex Filonov <afilonov_at_yahoo.com>
Date: 3 Dec 2002 20:45:05 -0800
Message-ID: <336da121.0212032045.94b5068_at_posting.google.com>

"Gigi Lipori" <pflugg_at_bellsouth.net> wrote in message news:<3ded2541_1_at_corp-news.newsgroups.com>...
> See the three queries below.
>
> Query 3 returns the desired results quite efficiently (< 1 sec). I'm trying
> to understand why, though. We found the 'solution' quite by accident.
>
> We started with Query 1, which runs in < 1 sec.
>
> We then went to Query 2 and added one more lookup (the care provider name
> for type 7). This runs pretty much indefinately (I've never let it go to
> completion -- we're talking way long).
>
> Why is the performance working this way? I'm happy we have the solution, but
> I need it to occur thoughtfully next time - not by sheer luck.
>
> Appreciate any comments....
>
Hard to tell without any information on table sizes. Execution plans would help too. Execution plan might depend on the order of tables in FROM clause. Usually it happens when rule-based optimizer is used or there is no statistics on the tables. To get better answer, you need to provide:

1. Which optimizer is used. Parameter optimizer_mode in init.ora
2. Were tables analyzed and how.
3. Execution plans from EXPLAIN PLAN or, better, from trace.
4. For cost-based optimizer only, parameter 

optimizer_max_permutations
5. Version of Oracle server. Should be first, actually.

> Query1
>
> SELECT
> PATIENT_DEMO.PAT_ID,
> PAT_REF_CARE7.CARE_ID,
> PAT_REF_CARE3.CARE_ID,
> CARE_PROVIDERS3.LAST_NAME
> FROM
> PATIENT_DEMO,
> PAT_REF_CARE PAT_REF_CARE7,
> PAT_REF_CARE PAT_REF_CARE3,
> CARE_PROVIDERS CARE_PROVIDERS3
> WHERE
> ( PAT_REF_CARE3.PAT_ID(+)=PATIENT_DEMO.PAT_ID )
> AND ( PAT_REF_CARE7.PAT_ID(+)=PATIENT_DEMO.PAT_ID )
> AND ( PAT_REF_CARE7.REFERRING_TYPE(+)=7 )
> AND ( PAT_REF_CARE3.REFERRING_TYPE(+)=3 )
> AND ( CARE_PROVIDERS3.CARE_ID=PAT_REF_CARE3.CARE_ID )
> AND (
> PATIENT_DEMO.PAT_ID = 111
> )
> ORDER BY
> 1
>
> Query 2
>
> SELECT
> PATIENT_DEMO.PAT_ID,
> PAT_REF_CARE7.CARE_ID,
> PAT_REF_CARE3.CARE_ID,
> CARE_PROVIDERS3.LAST_NAME,
> CARE_PROVIDERS7.LAST_NAME
> FROM
> PATIENT_DEMO,
> PAT_REF_CARE PAT_REF_CARE7,
> PAT_REF_CARE PAT_REF_CARE3,
> CARE_PROVIDERS CARE_PROVIDERS3,
> CARE_PROVIDERS CARE_PROVIDERS7
> WHERE
> ( PAT_REF_CARE3.PAT_ID(+)=PATIENT_DEMO.PAT_ID )
> AND ( PAT_REF_CARE7.PAT_ID(+)=PATIENT_DEMO.PAT_ID )
> AND ( PAT_REF_CARE7.REFERRING_TYPE(+)=7 )
> AND ( PAT_REF_CARE3.REFERRING_TYPE(+)=3 )
> AND ( CARE_PROVIDERS7.CARE_ID=PAT_REF_CARE7.CARE_ID )
> AND ( CARE_PROVIDERS3.CARE_ID=PAT_REF_CARE3.CARE_ID )
> AND (
> PATIENT_DEMO.PAT_ID = 111
> )
> ORDER BY
> 1
>
> Query 3
>
> SELECT
> PATIENT_DEMO.PAT_ID,
> PAT_REF_CARE7.CARE_ID,
> CARE_PROVIDERS7.LAST_NAME,
> PAT_REF_CARE3.CARE_ID,
> CARE_PROVIDERS3.LAST_NAME
> FROM
> PATIENT_DEMO,
> PAT_REF_CARE PAT_REF_CARE7,
> CARE_PROVIDERS CARE_PROVIDERS7,
> PAT_REF_CARE PAT_REF_CARE3,
> CARE_PROVIDERS CARE_PROVIDERS3
> WHERE
> ( PAT_REF_CARE3.PAT_ID(+)=PATIENT_DEMO.PAT_ID )
> AND ( PAT_REF_CARE7.PAT_ID(+)=PATIENT_DEMO.PAT_ID )
> AND ( PAT_REF_CARE7.REFERRING_TYPE(+)=7 )
> AND ( PAT_REF_CARE3.REFERRING_TYPE(+)=3 )
> AND ( CARE_PROVIDERS7.CARE_ID(+)=PAT_REF_CARE7.CARE_ID )
> AND ( CARE_PROVIDERS3.CARE_ID(+)=PAT_REF_CARE3.CARE_ID )
> AND (
> PATIENT_DEMO.PAT_ID = 111
> )
> ORDER BY
> 1
Received on Wed Dec 04 2002 - 05:45:05 CET