From oracle-l-bounce@freelists.org Wed Jul 7 10:27:26 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i67FQxR17042 for ; Wed, 7 Jul 2004 10:27:09 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i67FQn617005 for ; Wed, 7 Jul 2004 10:26:59 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 2E8B172CE54; Wed, 7 Jul 2004 10:08:13 -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 14820-37; Wed, 7 Jul 2004 10:08:12 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 31AA572CF5D; Wed, 7 Jul 2004 10:08:12 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Wed, 07 Jul 2004 10:06:44 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 2343272CF28 for ; Wed, 7 Jul 2004 10:06:44 -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 14323-61 for ; Wed, 7 Jul 2004 10:06:43 -0500 (EST) Received: from mail.acelerate.com (mail.acelerate.com [200.105.128.132]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with SMTP id F0BEE72CF18 for ; Wed, 7 Jul 2004 10:06:42 -0500 (EST) Received: (qmail 1624 invoked from network); 7 Jul 2004 15:30:25 -0000 Received: from dazasoftware.com (HELO cachitoss) (200.105.151.94) by 0 with SMTP; 7 Jul 2004 15:30:25 -0000 MIME-Version: 1.0 Message-Id: <40EC160A.00000F.03356@CACHITOSS> Date: Wed, 7 Jul 2004 11:26:02 -0400 Content-type: text/plain; charset=iso-8859-1 X-Mailer: IncrediMail (3001524) From: "Juan Carlos Reyes Pacheco" References: <00135E0349FB494EBB1D6B582EFCFAF2C78234@NBNOTOCEXCH3.nesbittburns.ca> To: Subject: RE: Slow running Query. X-FID: PLAINTXT-NONE-0000-0000-000000000000 Content-Transfer-Encoding: 8bit X-Priority: 3 X-Virus-Scanned: by amavisd-new at freelists.org X-archive-position: 4564 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: jreyes@dazasoftware.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org Hi, you didn't sent me your init.ora neither a export from that tables as I ask you If you do I'll test your query in my computer. I want to see if you set the QUERY_REWRITE_ENABLED and QUERY_REWRITE_INTEGRITY parameters, you need them to get cbo uses function indexes. -------Original Message------- From: oracle-l@freelists.org Date: 07/07/04 11:23:51 To: oracle-l@freelists.org Subject: RE: Slow running Query. Wolfgang and Juan, First of all sorry for reply after so many days as I was too busy at workp= lace. Here is my findings. After doing compute statistics on the tables. The following query=20 select * from me_client_assets=20 where ca_clcode in=20 ( select cl_clcode from client where cl_decmkr=3D'2929292') and substr(ca_date,1,4)||substr(ca_date,6,2)=3D'20012' used the required INDEX in both the cases ( 'CHOOSE', 'RULE). But still on my original query, I was not able to=20 get INDEX being used (in both RULE, CHOSSE sessions).=20 SELECT sum(nvl(as_ofcsh,0)),sum(nvl(as_offxd,0)), sum(nvl(as_ofeqty,0)),sum(nvl(as_ofai,0)) FROM assets_view,perfclient, client WHERE as_clcode=3Dpc_clcode and as_date=3D '200312' AND pc_clcode=3Dcl_clcode AND pc_prfcomb in ('B', 'Y') AND pc_grpdte !=3D '0000-00-00' -- dkumar added AND substr(pc_grpdte,1,4)||substr(pc_grpdte,6,2) <=3D '200312' and cl_decmkr=3D'645010103'; EXPLAIN PLAN (for CHOOSE session)=20 ------------ OPERATION OPTIONS OBJECT_NAME = POSITION ------------------------- ------------------------- -------------------- --= -------- SELECT STATEMENT = 2923 SORT AGGREGATE = 1 HASH JOIN = 1 NESTED LOOPS = 1 TABLE ACCESS BY INDEX ROWID CLIENT = 1 INDEX RANGE SCAN CL_DECMKR_IDX = 1 TABLE ACCESS BY INDEX ROWID PERFCLIENT = 2 INDEX UNIQUE SCAN PC_CLCODE_IDX = 1 VIEW ASSETS_VIEW = 2 SORT GROUP BY = 1 TABLE ACCESS FULL ME_CLIENT_ASSETS = 1 So I decided to use table name instead of view in the query as follows and now finally it uses the required INDEX on table ME_CLIENT_ASSETS table. select sum(decode(ca_status, 'O', decode(ca_class, 'C', nvl(ca_mktval, 0), 0), 0))= , sum(decode(ca_status, 'O', decode(ca_class, 'F', nvl(ca_mktval, 0), 0), 0))= , sum(decode(ca_status, 'O', decode(ca_class, 'E', nvl(ca_mktval, 0), 0), 0))= , sum(decode(ca_status, 'O', nvl(ca_mktval_ai,0),0)) from me_client_assets,client,perfclient where ca_clcode=3Dpc_clcode and substr(ca_date,1,4)||substr(ca_date,6,2)=3D '200312' AND pc_clcode=3Dcl_clcode AND pc_prfcomb in ('B', 'Y') AND pc_grpdte !=3D '0000-00-00' -- dkumar added AND substr(pc_grpdte,1,4)||substr(pc_grpdte,6,2) <=3D '200312' and cl_decmkr=3D'32002590' group by ca_clcode,ca_date and following is the EXPLAIN PLAN ( same for both CHOOSE, RULE). SQL> @expsql view OPERATION OPTIONS OBJECT_NAME = POSITION ------------------------- ------------------------- -------------------- --= -------- SELECT STATEMENT SORT GROUP BY = 1 NESTED LOOPS = 1 NESTED LOOPS = 1 TABLE ACCESS BY INDEX ROWID CLIENT = 1 INDEX RANGE SCAN CL_DECMKR_IDX = 1 TABLE ACCESS BY INDEX ROWID PERFCLIENT = 2 INDEX UNIQUE SCAN PC_CLCODE_IDX = 1 TABLE ACCESS BY INDEX ROWID ME_CLIENT_ASSETS = 2 INDEX RANGE SCAN PK_ME_CLIENT_ASSETS = 1 Once again thanks for all your inputs that helped me make this query work e= fficiently. -----Original Message----- From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org]On Behalf Of Wolfgang Breitling Sent: June 25, 2004 1:00 PM To: oracle-l@freelists.org Subject: RE: Slow running Query. In a case like this, the statistics on the tables, columns and indexes are= vitally important and nobody could accurately reproduce those in a test=20 environment. Can you export your statistics to a stattab table and post the export of th= at=20 table to the same website? Quoting "Kumar, Dharminder" : >=20 > As mail server does not allow for SQL files to be attached to the email, I > =3D > have put the scripts for tables, views, queries in question at the > followin=3D > g web adress, incase somebody is interestig in doing tests. > http://dharm-renu.tripod.com/sqltuning.htm >=20 --=20 regards Wolfgang Breitling Oracle 7,8,8i,9i OCP DBA Centrex Consulting Corporation www.centrexcc.com ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- **********************************************************= ****************** This e-mail and any attachments may contain confidential and privileged inf= ormation. If you are not the intended recipient, please notify the sender i= mmediately by return e-mail, delete this e-mail and destroy any copies. Any= dissemination or use of this information by a person other than the intend= ed recipient is unauthorized and may be illegal. Unless otherwise stated, o= pinions expressed in this e-mail are those of the author and are not endors= ed by the author's employer. ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------