Re: Index Full Scan is taking a lot of time

10 views
Skip to first unread message
Message has been deleted

fitzj...@cox.net

unread,
Aug 10, 2007, 2:06:30 PM8/10/07
to ORACLE_DBA_EXPERTS

On Aug 10, 12:29 pm, Avi <avinash.kalyan...@gmail.com> wrote:
> Hello All,
>
> There are three tables A B C with 18 Million, 6 M and 8M records
> respectivly.
> select count(1) from
> ( Select * from A
> where
> not exists ( select 'x' from B where join on Keys )
> and not exists ( select 'x' from C where join on keys )
> )
>
> The explain plan shows that it is using all key indexes for all tables
> and cost is 7366 for table A Index full scan. And other tables have a
> index Range scan 3 and 4. What could be a reason for this
> discrepency.
>
> Help is much appreciated.
>
> Thanks and Regards,
> Avinash Kalyankar

No Oracle version, no operating system information, no query plan, no
query: no help.


David Fitzjarrell

Avi

unread,
Aug 10, 2007, 2:30:42 PM8/10/07
to ORACLE_DBA_EXPERTS
Hello David,

We are using oracle 9I , Linux operating system. For confidentiality I
have renamed the tables

Query:

select /*+ parallel */


*
from A where not exists

(select /*+ parallel */ 'X'
from b
where stud_id = A.stud_id
and cpnt_typ_id = A.cpnt_typ_id
and cpnt_id = A.cpnt_id
and rev_dte = A.rev_dte
and cmpl_stat_id = A.cmpl_stat_id
and compl_dte = a.compl_dte)
and not exists
(select /*+ parallel */ 'X'
from c
where stud_id = A.stud_id
and cpnt_typ_id =A.cpnt_typ_id
and cpnt_id = A.cpnt_id
and rev_dte = A.rev_dte
and cmpl_stat_id = A.cmpl_stat_id
and compl_dte = A.compl_dte)

Plan :


SELECT STATEMENT, GOAL = CHOOSECost=7400 Cardinality=1 Bytes=62 IO
cost=7400
SORT AGGREGATE Cardinality=1 Bytes=62
FILTER
INDEX FAST FULL SCAN Object Object name=PK_A Cost=7393
Cardinality=46754 Bytes=2898748 IO cost=7393
INDEX RANGE SCAN Object Object name=IDX_B_1 Cost=3 Cardinality=1
Bytes=58 IO cost=3
TABLE ACCESS BY INDEX ROWID Object Object name=PA_C Cost=4
Cardinality=1 Bytes=60 IO cost=4
INDEX RANGE SCAN Object Object name=IX_C Cost=3 Cardinality=1 IO
cost=3


Thanks and Regards,
Avi

fitzj...@cox.net

unread,
Aug 10, 2007, 3:16:22 PM8/10/07
to ORACLE_DBA_EXPERTS

'9i' says very little about which release of Oracle you're using as
that spans 9.0.1.x through 9.2.0.8 and there were substantial
improvements between 9.0.1 and 9.2.0. Report your version to four
numbers.

You likely need to set event 10046 at level 8 to get a usable session
trace. How current are your statistics? How skewed is the data? You
also need to see how the disks are performing, how your sorts, if any,
are being handled; sorts may be going to disk rather than memory which
would indicate your sort_area_size is too small. You may also have
disk 'hot spots' you can correct by moving datafiles to other drives.

There is much you need to do to determine the nature of this problem
and arrive at a solution. I've provided a few areas you can check; I
suggest you do so before you post again as there is still insufficient
information to solve this 'problem'.

Avi

unread,
Aug 10, 2007, 3:30:35 PM8/10/07
to ORACLE_DBA_EXPERTS
Sure Dave, I will do that.

> information to solve this 'problem'.- Hide quoted text -
>
> - Show quoted text -

Charles Hooper

unread,
Aug 10, 2007, 6:39:52 PM8/10/07
to ORACLE_DBA_EXPERTS

David makes several good points. The Oracle version in part
determines how Oracle will optimize and execute the SQL statement that
you submit. Certain transformations are performed automatically in 9i
for NOT EXISTS queries, regardless of whether or not the cost will
increase due to the transformation. The cardinality numbers reported
in the explain plan (a DBMS Xplan would be better) do not appear to be
consistent with three tables having 18 million, 6 million and 8
million rows. When collecting statistics, make certain that the
DBMS_STATS routine is called with CASCADE=>TRUE specified.

Jonathan Lewis has commented on his blog a couple times about using
PARALLEL, which only resulted in even longer execution times. You
might try reformatting the SQL statement into an equivalent form, and
removing the PARALLEL hints. Something like this:
SELECT
A.*
FROM
A,
(SELECT DISTINCT
STUD_ID,
CPNT_TYP_ID,
CPNT_ID,
REV_DTE,
CMPL_STAT_ID,
COMPL_DTE
FROM
B) B,
(SELECT DISTINCT
STUD_ID,
CPNT_TYP_ID,
CPNT_ID,
REV_DTE,
CMPL_STAT_ID,
COMPL_DTE
FROM
C) C
WHERE
A.STUD_ID = B.STUD_ID(+)
AND A.CPNT_TYP_ID = B.CPNT_TYP_ID(+)
AND A.CPNT_ID = B.CPNT_ID(+)
AND A.REV_DTE = B.REV_DTE(+)
AND A.CMPL_STAT_ID = B.CMPL_STAT_ID(+)
AND A.COMPL_DTE = B.COMPL_DTE(+)
AND B.STUD_ID IS NULL
AND A.STUD_ID = C.STUD_ID(+)
AND A.CPNT_TYP_ID = C.CPNT_TYP_ID(+)
AND A.CPNT_ID = C.CPNT_ID(+)
AND A.REV_DTE = C.REV_DTE(+)
AND A.CMPL_STAT_ID = C.CMPL_STAT_ID(+)
AND A.COMPL_DTE = C.COMPL_DTE(+)
AND C.STUD_ID IS NULL;

Compare the performance of the above with the performance of your
original SQL statement using a 10046 trace at level 8, as suggested by
David. The above will likely result in 2 sorting operations in 9i, so
the SORT_AREA_SIZE parameter may need to be adjusted.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.

Raj

unread,
Aug 15, 2007, 10:41:40 AM8/15/07
to ORACLE_DBA_EXPERTS

> > - Show quoted text -- Hide quoted text -


>
> - Show quoted text -

Hello Avinash,

Please update the group about the current status of your problem.

If you have found the solution then also let us know. So, the
everybody can be benefited.

- Rajeev

Reply all
Reply to author
Forward
0 new messages