Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Slow running Query.

RE: Slow running Query.

From: Kumar, Dharminder <Dharminder.Kumar_at_Bmonb.com>
Date: Tue, 22 Jun 2004 11:38:00 -0400
Message-ID: <00135E0349FB494EBB1D6B582EFCFAF2C7822C@NBNOTOCEXCH3.nesbittburns.ca> 





Juan,


Let me add the Oracle Version being used is Oracle 8.1.7 and so I believe t=
hat NO_MERGE hints won't have any impact on the query. However following is=
 the execution plans for the three queries, I mentioned in my first message.



Execution Plan for statement 1.


OPERATION                 OPTIONS                   OBJECT_NAME            =


POSITION


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






SELECT STATEMENT


  SORT                    AGGREGATE                                        =
       1
    NESTED LOOPS                                                           =
       1
      NESTED LOOPS                                                         =
       1
        VIEW                                        ASSETS_VIEW            =
       1
          SORT            GROUP BY                                         =
       1
            TABLE ACCESS  FULL                      ME_CLIENT_ASSETS       =
       1
        TABLE ACCESS      BY INDEX ROWID            CLIENT                 =
       2
          INDEX           UNIQUE SCAN               CLIENT_IDX             =
       1
      TABLE ACCESS        BY INDEX ROWID            PERFCLIENT             =
       2
        INDEX             RANGE SCAN                PC_CLCODE_IDX          =
       1





Execution Plan for Statement 2.


OPERATION                 OPTIONS                   OBJECT_NAME            =


POSITION


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






SELECT STATEMENT


  SORT                    AGGREGATE                                        =
       1
    VIEW                                            ASSETS_VIEW            =
       1
      SORT                GROUP BY                                         =
       1
        TABLE ACCESS      BY INDEX ROWID            ME_CLIENT_ASSETS       =
       1
          INDEX           RANGE SCAN                PK_ME_CLIENT_ASSETS    =
       1





Execution Plan for Statement 3.



QL> @expsql pru3



OPERATION                 OPTIONS                   OBJECT_NAME            =


POSITION


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






SELECT STATEMENT


  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               RANGE SCAN                PC_CLCODE_IDX          =
       1





As you can see that first statement is doing full tablescan of table ME_CLI=
ENT_ASSETS


(   TABLE ACCESS  FULL                      ME_CLIENT_ASSETS              1)


on which the view assets_vies is based and that is why the query is taking =
long time to finish.



Thanks.



-----Original Message-----


From: oracle-l-bounce_at_freelists.org


[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Juan Carlos Reyes
Pacheco


Sent: June 21, 2004 5:29 PM


To: oracle-l_at_freelists.org


Subject: Re: Slow running Query.




HI,  If you are in 9i, only to try, to use the /*+ NO_MERGE */ hint and tell
me what happen, once this solved the problem
select  /*+ NO_MERGE */  sum.........



If not get the execution plan, because thre is the answer.
-------Original Message-------


=20


From: oracle-l_at_freelists.org


Date: 06/21/04 17:18:53


To: oracle-l_at_freelists.org


Subject: Slow running Query.


=20


> Hi all,

> I have the following query which is running very slow as it takes few min=

=3D


utes to complete.

>=3D20




> SELECT sum(nvl(as_ofcsh,0)),sum(nvl(as_offxd,0)),

> sum(nvl(as_ofeqty,0)),sum(nvl(as_ofai,0))

> FROM perfclient, assets_view, client

> WHERE cl_decmkr=3D3D'64501013'

> AND cl_clcode=3D3Das_clcode

> AND as_date=3D3D '200312'

> AND cl_clcode=3D3Dpc_clcode

> AND pc_prfcomb in ('B', 'Y')

> AND pc_grpdte !=3D3D '0000-00-00'

> AND substr(pc_grpdte,1,4)||substr(pc_grpdte,6,2) <=3D3D '200312'

>=3D20


> But if I run the following queries the response time is less than a seco=

=3D


nd.

>=3D20




> SELECT sum(nvl(as_ofcsh,0)),sum(nvl(as_offxd,0)),

> sum(nvl(as_ofeqty,0)),sum(nvl(as_ofai,0))

> FROM assets_view

> WHERE as_clcode in '64501013'=3D20

> and as_date=3D3D'200312'


>=3D20




> select cl_clcode from=3D20

> perfclient,client

> WHERE cl_decmkr=3D3D'64501013'

> AND cl_clcode=3D3Dpc_clcode

> AND pc_prfcomb in ('B', 'Y')

> AND pc_grpdte !=3D3D '0000-00-00'

> AND substr(pc_grpdte,1,4)||substr(pc_grpdte,6,2) <=3D3D '200312'


>=3D20




> Following is the definiton of VIEW assets_view


>=3D20




> CREATE OR REPLACE VIEW retail.assets_view

> ( as_clcode

> , as_date

> , as_ofcsh

> , as_offxd

> , as_ofeqty

> , as_delcsh

> , as_delfxd

> , as_deleqty

> , as_uncsh

> , as_unfxd

> , as_uneqty

> , as_ofai

> , as_delai

> , as_unai

> )

> AS

> select ca_clcode as_clcode,

> substr(ca_date,1,4)||substr(ca_date,6,2) as_date,

> sum(decode(ca_status, 'O', decode(ca_class, 'C', nvl(ca_mktval, 0), 0), 0=

=3D


)) as_ofcsh,


> sum(decode(ca_status, 'O', decode(ca_class, 'F', nvl(ca_mktval, 0), 0), 0=

=3D


)) as_offxd,


> sum(decode(ca_status, 'O', decode(ca_class, 'E', nvl(ca_mktval, 0), 0), 0=

=3D


)) as_ofeqty,


> sum(decode(ca_status, 'D', decode(ca_class, 'C', nvl(ca_mktval, 0), 0), 0=

=3D


)) as_delcsh,


> sum(decode(ca_status, 'D', decode(ca_class, 'F', nvl(ca_mktval, 0), 0), 0=

=3D


)) as_delfxd,


> sum(decode(ca_status, 'D', decode(ca_class, 'E', nvl(ca_mktval, 0), 0), 0=

=3D


)) as_deleqty,


> sum(decode(ca_status, 'U', decode(ca_class, 'C', nvl(ca_mktval, 0), 0), 0=

=3D


)) as_uncsh,


> sum(decode(ca_status, 'U', decode(ca_class, 'F', nvl(ca_mktval, 0), 0), 0=

=3D


)) as_unfxd,


> sum(decode(ca_status, 'U', decode(ca_class, 'E', nvl(ca_mktval, 0), 0), 0=

=3D


)) as_uneqty,


> sum(decode(ca_status, 'O', nvl(ca_mktval_ai,0),0)) as_ofai,

> sum(decode(ca_status, 'D', nvl(ca_mktval_ai,0),0)) as_delai,

> sum(decode(ca_status, 'U', nvl(ca_mktval_ai,0),0)) as_unai

> from me_client_assets

> group by ca_clcode, ca_date


>=3D20




> (ME_CLIENT_ASSETS table has UNIQUE INDEX pk_me_client_assets

> ON me_client_assets

> ( ca_clcode,

> ca_date,

> ca_status,

> ca_class,

> ca_risk,

> ca_rspelig,

> ca_short,

> ca_foreign )

> CLIENT table has index on column "cl_clcode" and another index on "cl_dec=

=3D


mkr".


> PERFCLIENT table has index on column "pc_clcode"


>=3D20




> So can you suggest some ways to improve this query.

> Thanks in advance.=3D20


>=3D20
>=3D20
>=3D20
>=3D20




> Dharminder Kumar



>=3D20


=20


=20

<FONT SIZE =3D3D 1>********************************************************=


**=3D




This e-mail and any attachments may contain confidential and privileged inf=
=3D


ormation. If you are not the intended recipient, please notify the sender i=
=3D


mmediately by return e-mail, delete this e-mail and destroy any copies. Any=
=3D


dissemination or use of this information by a person other than the intend=
=3D


ed recipient is unauthorized and may be illegal. Unless otherwise stated, o=
=3D


pinions expressed in this e-mail are those of the author and are not endors=
=3D


ed by the author's employer.</FONT>


=20




Please see the official ORACLE-L FAQ: http://www.orafaq.com




To unsubscribe send email to: oracle-l-request_at_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_at_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
-----------------------------------------------------------------


<FONT SIZE =3D 1>**********************************************************=
******************
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.</FONT>

----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_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
-----------------------------------------------------------------


Received on Tue Jun 22 2004 - 10:35:18 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US