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: Mark W. Farnham <mwf_at_rsiz.com>
Date: Tue, 22 Jun 2004 14:22:13 -0400
Message-ID: <KNEIIDHFLNJDHOOCFCDKMEMHEOAA.mwf@rsiz.com> 





left out the word "not" before numeric. sorry.



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


From: oracle-l-bounce_at_freelists.org


[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Mark W. Farnham
Sent: Tuesday, June 22, 2004 2:08 PM


To: oracle-l_at_freelists.org


Subject: RE: Slow running Query.




grumble. we hand it a list of its first two columns of its pk and it doesn't
find that plan?



hmm. I wonder if a type mismatch is happening.



okay, if



where (as_clcode, as_date) in (select cl_code, '200312' ..... doesn't get
it,



then it's time to type check and trace.



Speaking of type checking, your original query 2 used string literals for
both args, so I didn't


worry about it much. If cl_clcode is a number, you might need to convert it
on the query (if the ca_clcode underlying as_clcode is numeric).



ditto for the virtual table thingy.



doh! I just noticed that your as_date is a substr/concat of the underlying
table's column. You might


add an unmodified as_full_date as ca_date to the view, and toss in a
as_full_date like '2003__12%' (is that underbar or a ?
for a single char wildcard in like? anyway use the right one). Then the
optimizer might know it can use the first two columns to some good effect
and that might tip the score.



We're probably doing something that confuses the optimizer about being able
to use as_clcode being the leading edge of an index.
Since it works in your orginal query 2, it seems unlikely it is missing
stats, so I guess I'd wager a donut on a type mismatch at this point.




mwf



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


From: oracle-l-bounce_at_freelists.org


[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Kumar, Dharminder
Sent: Tuesday, June 22, 2004 1:04 PM


To: oracle-l_at_freelists.org


Subject: RE: Slow running Query.




Mark,


I tried both the options suggested by you. Following is the result.



Thought 1.


Query


=3D=3D=3D=3D=3D


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 ( select cl_clcode from perfclient,client
                                WHERE cl_decmkr=3D'64501013'
                                AND cl_clcode=3Dpc_clcode
                                AND pc_prfcomb in ('B', 'Y')
                                AND pc_grpdte !=3D '0000-00-00' -- dkumar a=
dded
                                AND substr(pc_grpdte,1,4)||substr(pc_grpdte=
,6,2) <=3D '200312')
        and   as_date =3D'200312'





Explain Plan


=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

OPERATION                 OPTIONS                   OBJECT_NAME            =


POSITION


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






SELECT STATEMENT


  SORT                    AGGREGATE                                        =
       1
    MERGE JOIN                                                             =
       1
      SORT                JOIN                                             =
       1
        VIEW                                        ASSETS_VIEW            =
       1
          SORT            GROUP BY                                         =
       1
            TABLE ACCESS  FULL                      ME_CLIENT_ASSETS       =
       1
      SORT                JOIN                                             =
       2
        VIEW                                        VW_NSO_1               =
       1
          SORT            UNIQUE                                           =
       1
            NESTED LOOPS                                                   =
       1
              TABLE ACCES BY INDEX ROWID            CLIENT                 =
       1
                INDEX     RANGE SCAN                CL_DECMKR_IDX          =
       1
              TABLE ACCES BY INDEX ROWID            PERFCLIENT             =
       2
                INDEX     RANGE SCAN                PC_CLCODE_IDX          =
       1






Thought 2 ( Hope I got it right)


=3D=3D=3D=3D=3D=3D=3D=3D=3D


Query 2


=3D=3D=3D=3D=3D=3D=3DSELECT  sum(nvl(as_ofcsh,0)),sum(nvl(as_offxd,0)),

                   sum(nvl(as_ofeqty,0)),sum(nvl(as_ofai,0))
        FROM assets_view ,(select cl_clcode,rownum from  client,perfclient
        WHERE cl_decmkr=3D'64501013'
        AND cl_clcode=3Dpc_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')
        where as_clcode =3D cl_clcode
        and as_date=3D'200312'






Explain Plan


=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

OPERATION                 OPTIONS                   OBJECT_NAME            =


POSITION


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






SELECT STATEMENT


  SORT                    AGGREGATE                                        =
       1
    MERGE JOIN                                                             =
       1
      SORT                JOIN                                             =
       1
        VIEW                                                               =
       1
          COUNT                                                            =
       1
            NESTED LOOPS                                                   =
       1
              TABLE ACCES BY INDEX ROWID            CLIENT                 =
       1
                INDEX     RANGE SCAN                CL_DECMKR_IDX          =
       1
              TABLE ACCES BY INDEX ROWID            PERFCLIENT             =
       2
                INDEX     RANGE SCAN                PC_CLCODE_IDX          =
       1
      SORT                JOIN                                             =
       2
        VIEW                                        ASSETS_VIEW            =
       1
          SORT            GROUP BY                                         =
       1
            TABLE ACCESS  FULL                      ME_CLIENT_ASSETS       =
       1





So you can see that in both the cases, queries are not using the INDEX in q=
uestion.


Let me know if you have any other suggestion.
Thanks.



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


From: oracle-l-bounce_at_freelists.org


[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Mark W. Farnham
Sent: June 21, 2004 6:18 PM


To: oracle-l_at_freelists.org


Subject: RE: Slow running Query.




Two thoughts:



Take your first fast query and modify the where clause using the second fast
query as the IN list.



where as_clcode in (select cl_clcode from .....your second fast query)



If you're on a release that supports order by, ordering the IN list query by
cl_clcode *might* make the view's group by operation faster and probably
costs little.



Second thought:



make the entire part of the query that delivers you the cl_clcode list you
need a virtual table in the from clause. If this doesn't "fix" the plan
selected, consider adding a dummy rownum column in the query part of the
virtual table so it forces projection.



Now if your list of cl_clcodes actually delivered is very long, the
sub-seconds for each part may add up to the few minutes you mentioned.



The existence of the referenced features varies on release, and your mileage
may vary.



mwf



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


From: oracle-l-bounce_at_freelists.org


[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Kumar, Dharminder
Sent: Monday, June 21, 2004 5:06 PM


To: oracle-l_at_freelists.org


Subject: Slow running Query.




> 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



<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>





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
-----------------------------------------------------------------


----------------------------------------------------------------
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
-----------------------------------------------------------------


Received on Tue Jun 22 2004 - 13:25:42 CDT












Original text of this message













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