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