REPOST: Re: hot sql problem

My guess would be that the I.STATUS_CODE = 'A' is not evenly distributed with other values and that if you use a hard coded value the optimizer can use histograms (optimizer does not use histograms with bind variables.). What happens if you take the order by off? Jim

"Mike F" <u518615722_at_spawnkill.ip-mobilphone.net> wrote in message news:l.1011927183.1448516845_at_pool-151-197-238-45.phil.east.verizon.net...
> We are using 8.1.7.2
>
> One of our top sql statement seems to a huge disk read on our temp
> tablespace. The disk read is 699300 and buffer gets is 813062. The
> statement usually returns only 10-20 results, but why it needs so many
> sorts? ( I guess the disk reads is from the sorting). But when I
> replace the bind value with hard coded value, the disk read is only 9,
> buffer gets is 137, but why all the books suggests to use binding
> value instead of hard coded statement?
>
> I was trying to increase the sort_area_size from 650k to 2m, but the
> whole system become so slow and CPU idle become 0%, because all the
> fifty applications (JAVA) are issuing the same statement. If I kept
> sort_area_size to 650k, the hot file is always the temp tablespace with
> the i/o almost 80% of all the i/o on database files.
>
> The statement is as follows,
> SELECT I.ID,
> I.DESCRIPTION,
> I.LOCATION,
> I.ENTRY_TIME,
> I.START_TIME,
> I.END_TIME,
> I.LAST_UPDATED_TIME,
> I.ORDER_ID,
> I.DEPT_ID,
> I.DIRECTION_ID,
> I.TYPE_ID,
> NVL(I.ZONE_DESC,'Unspecified') ZONE_DESC,
> NVL(I.ZONE_SHORT_DESC,'Unspecified'ZONE_SHORT_DESC,
> I.CRITICALITY_SHORT_DESC CRITICALITY_DESC,
> NVL(I.TYPE_SHORT_DESC,'Unspecified') TYPE_SHORT_DESC,
> NVL(I.TYPE_DESC,'Unspecified') TYPE_DESC,
> NVL(I.DIRECTION_SHORT_DESC,'Unspecified') DIRECTION_DESC,
> (I.END_TIME - :b1 ) * (24 * 60 ) MINUTES_REMAINING,
> I.GEOLOC.SDO_POINT.X LNG,
> I.GEOLOC.SDO_POINT.Y LAT
> FROM ORDER_INFO_VIEW I
> WHERE I.START_TIME < :b1
> AND I.END_TIME > :b1
> AND I.STATUS_CODE = 'A'
> AND I.METRO_ID = :b4
> ORDER BY I.CRITICALITY_SHORT_DESC
>
> Could somebody tell me how should I deal with the problem?
>
> Thanks for you help.
>
>
>
>
>
> --
> Sent by dbadba62 from hotmail piece from com
> This is a spam protected message. Please answer with reference header.
> Posted via http://www.usenet-replayer.com/cgi/content/new

• WAS CANCELLED BY =======: From: "Jim Kennedy" <kennedy-family_at_attbi.com> Control: cancel <%x648.2937$a%4.10916_at_rwcrnsc51.ops.asp.att.net> Subject: cmsg cancel <%x648.2937$a%4.10916_at_rwcrnsc51.ops.asp.att.net> Date: Sun, 27 Jan 2002 23:07:40 GMT Message-ID: <cancel.%x648.2937$a%4.10916_at_rwcrnsc51.ops.asp.att.net> X-No-Archive: yes Newsgroups: microsoft.test,alt.flame.niggers,comp.databases.oracle.misc NNTP-Posting-Host: w088.z064003087.lax-ca.dsl.cnc.net 64.3.87.88 Lines: 1 Path: news.uni-stuttgart.de!dns.phoenix-ag.de!newsfeed01.sul.t-online.de!t-online.de!fr.clara.net!heighliner.fr.clara.net!grolier!news.stealth.net!msrtrans1!msrnewsc1!cppssbbsa01.microsoft.com!tkmsftngp01!tkmsftngp03!u&n&a&c&anceller Xref: news.uni-stuttgart.de control:40716442

This message was cancelled from within The Unacanceller's glorious new software, Lotus 1-2-3 For Rogue Cancellers. Received on Fri Jan 25 2002 - 00:02:35 CST