From oracle-l-bounce@freelists.org Wed May 25 04:41:38 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j4P9fcj2014181 for ; Wed, 25 May 2005 04:41:38 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j4P9fbNi014176 for ; Wed, 25 May 2005 04:41:37 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 400651B0024; Wed, 25 May 2005 03:38:40 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 08603-04; Wed, 25 May 2005 03:38:40 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id BC68219D696; Wed, 25 May 2005 03:38:39 -0500 (EST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition; b=ScawI3yoWb6PNjp8Ve5wnmAjEFzII1e3s+pBfxInut/Y8oKpk9ByW5Sr40TOjMbooKKuArxQbSjTTTgsoo+l4crOuv2v8FHev3HvNwtQVUepOwPTzeuD+PlTm5RrdG3r04X+vnKzyJI7Y+ezWnnFanqTODHjlRzgr+ZIpqKcx2s= Message-ID: <97b7fd2f05052501364cd8dbef@mail.gmail.com> Date: Wed, 25 May 2005 11:36:48 +0300 From: The Human Fly To: Oracle-L Freelists Subject: query with cost 2 taking end-less time. Mime-Version: 1.0 Content-type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit Content-Disposition: inline X-archive-position: 20235 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: sjaffarhussain@gmail.com Precedence: normal Reply-To: sjaffarhussain@gmail.com X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.63 (2004-01-11) on air891.startdedicated.com X-Spam-Status: No, hits=0.2 required=5.0 tests=AWL,LINES_OF_YELLING, UPPERCASE_25_50 autolearn=no version=2.63 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