query with cost 2 taking end-less time.

From: The Human Fly <sjaffarhussain_at_gmail.com>
Date: Wed, 25 May 2005 11:36:48 +0300
Message-ID: <97b7fd2f05052501364cd8dbef@mail.gmail.com>

Hell list,

I found one interesting question in the oracle metalink, the originator of the question ask that with cost 2 his query is taking end-less timing to finish.

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D original question on the metalink =3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

Query with cost of 2 takes a long time

Any idea why this query is choking? It normally runs in minutes but now is taking an hour or so. Unix admins say everything on OS is fine

Also, what is with the cost of .00000000000000 in the explain plan?

tks

steve

SELECT A.PROJECT_ID, A.ACTIVITY_ID, B.ACTIVITY_TYPE,

A.BUSINESS_UNIT_GL, A.ACCOUNT, A.DEPTID, A.PRODUCT, A.ANALYSIS_TYPE,
A.RESOURCE_TYPE, TO_CHAR(A.TRANS_DT,'YYYY-MM-DD'), A.BUSINESS_UNIT_AP,
A.VENDOR_ID, A.VOUCHER_ID, A.VOUCHER_LINE_NUM, A.RESOURCE_AMOUNT,
A.OPRID, A.SYSTEM_SOURCE, A.DESCR, A.LINE_DESCR, A.RESOURCE_ID,

TO_CHAR(A.ACCOUNTING_DT,'YYYY-MM-DD')
FROM PS_PROJ_RESOURCE A, PS_PROJ_ACTIVITY B WHERE A.ACCOUNTING_DT > TO_DATE('2000-12-31','YYYY-MM-DD')

AND A.ACCOUNT > '20000'
AND A.BUSINESS_UNIT =3D B.BUSINESS_UNIT
AND A.PROJECT_ID =3D B.PROJECT_ID
AND A.ACTIVITY_ID =3D B.ACTIVITY_ID
AND A.ACTIVITY_ID IN ('6286','6287')
AND A.ANALYSIS_TYPE =3D 'ACT'

SELECT STATEMENT Optimizer=3DCHOOSE (Cost=3D2 Card=3D1 Bytes=3D193) NESTED LOOPS (Cost=3D2 Card=3D1 Bytes=3D193) PARTITION RANGE (ITERATOR)
TABLE ACCESS (BY LOCAL INDEX ROWID) OF PS_PROJ_RESOURCE (Cost=3D.00000000000000 Card=3D.00000000000000 Bytes=3D.00000000000000) INDEX (RANGE SCAN) OF PSAPROJ_RESOURCE (NON-UNIQUE) (Cost=3D.00000000000000 Card=3D.00000000000000) INLIST ITERATOR
TABLE ACCESS (BY INDEX ROWID) OF PS_PROJ_ACTIVITY (Cost=3D1 Card=3D19078 Bytes=3D305248)
INDEX (UNIQUE SCAN) OF PS_PROJ_ACTIVITY (UNIQUE) (Cost=3D1 Card=3D19078)
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

Some questioned him that his statsitics doesn't look good and need to re-collect again. He says that he has gathered stats using dbms_stats.gather_schema_stats two days ago, since then there is not much change in the data.

I am really surprised to see the cost, i.e. .0000000000000000

Can someone shed some light on this?

--=20
Best Regards,
Jaffar, OCP DBA
Banque Saudi Fransi
Saudi Arabia

---------------------------------------------------------------------------=

"It is your atittude, not your aptitude that determins your altitude."

--
http://www.freelists.org/webpage/oracle-l

Received on Wed May 25 2005 - 04:41:38 CDT