RE: What is this Session Doing

Ganesh,

        Considering the pain you're going through I'd take a stab at re-writing this non-optimal query.

        What you say about the cost is true. I had to twist the arm of a client to get my version of code to replace his because his cost was less. Mine just ran 80% faster.

        Larry Klein from HOTSOS gave me the tip that you can get the true cost of a query by putting it in an anonymous block and then get the explain plan. (Thanks Larry)

	Generally if your cost is 14 digits or more it's gonna take a while.
	(Did I stretch that a couple of digits?  Must be fisherman in me.)

	Larry

-----Original Message-----
From: Ganesh Raja [mailto:ganesh.raja_at_gmail.com] Sent: Wednesday, September 29, 2004 7:36 AM To: Wolfson Larry - lwolfs
Cc: Zhu, Chao; oracle-l_at_freelists.org
Subject: Re: What is this Session Doing

Larry,

Disabling Parallel and Hash Join Seemed to make the Query atleast Do Direct Path Reads which it was not doing in the Earlier Case .. But the Plan of the Query is horrendous and is not worth for a Production Box.

Still Unclear why the query did not work with Parallel Slaves and Hash Joins...

I also got a directive from Oracle Support which said that 14M on the COst is Too high and u need to Reduce the cost of the Query .... But i always thought that u can never compare the COst and say if a query will run Fast or Not... I know that the COST has a Direct Relation to the Physical Reads Performed by the Query ... But How can u determine that a query wll not run fast if it has a cost of 14M or say 300 ...

Any Thoughts ..

Cheers
Ganesh R

On Wed, 29 Sep 2004 10:31:00 +0100, Ganesh Raja <ganesh.raja_at_gmail.com> wrote:
> Larry,
>
> No That is my Last Option ... And will try it in another few Mins ...
>
> Oracle is saying that the query is executing but the Truss Does not
> Show any Change in the output and it is as though the Query is Waiting
> to Build an Hash Table in the Temp Segment ... !!!!
>
> Every 10 Mns or so i am getting a Semctl operation on the trruss and
> it seems to be looping.
>
> The Query as such is not Written in a Very Optimal Way but i would not
> expect the Process to use the CPU for 36 Hrs and do nothing !!!!
>
> Any Thoughts ..
>
> And as usual .. Thanks ...
>
> Cheers
> Ganesh R
>
>
>
>
> On Tue, 28 Sep 2004 20:17:27 -0500, Wolfson Larry - lwolfs
> <lawrence.wolfson_at_acxiom.com> wrote:
> > Ganesh, did you also try running it non-parallel?
> >
> > -----Original Message-----
> > From: oracle-l-bounce_at_freelists.org
> > [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Ganesh Raja
> > Sent: Friday, September 24, 2004 6:49 AM
> > To: Zhu, Chao
> > Cc: oracle-l_at_freelists.org
> > Subject: Re: What is this Session Doing
> >
> > Zhu,
> >
> > Truss may give me some output. Will Try that.
> >
> > But 10046 Should not give me anything rite because it is consuming CPU
> > and not waiting for anything so the Trace file is Just Going to be
> > Sitting there without writing anything to the File.
> >
> > Cheers
> > Ganesh=20
> >
> >
> > On Fri, 24 Sep 2004 19:40:32 +0800, Zhu, Chao <chzhu_at_ebay.com> wrote:
> > > Hi,
> > > (It seems that my outlook is incompatible with oracle-l, so if
it =
> > mess code, please forward it to the list).
> > > Try truss -p $pid , and find out what the process is doing in
the =
> > unix level and using event 10046 to trace it in oracle.
> > > I have seen similar things on my solaris box. Maybe it is doing
po=
> > ll() system call, or yield() system call.(I hit such problem).
Workaround i=
> > s to use another execution path for this specific SQL.
> > > V$sesstat won't update the statistics until the current SQL has
fi=
> > nished.
> > >=20
> > > Regards
> > > Zhu Chao
> > > eBay e-commerce Technology Operations (Shanghai) Co.,Ltd.
> > > Tel: 86-21-32174588x8667
> > > Fax: 86-21-63404100
> > > 12/F., Raffles City Shanghai, No. 268 Xi Zang Road, Central
> > > Shanghai, China, 200001
> > >=20
> > >=20
> > >=20
> > >=20
> > > -----Original Message-----
> > > From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org=
> > ] On Behalf Of Ganesh Raja
> > > Sent: 2004=E5=B9=B49=E6=9C=8824=E6=97=A5 18:29
> > > To: Oracle-L
> > > Subject: Re: What is this Session Doing
> > >=20
> > > Sorry here are the versions.
> > >=20
> > > Solaris 64 Bit 9.2.0.5
> > >=20
> > > Thanks.
> > >=20
> > > On Fri, 24 Sep 2004 11:28:12 +0100, Ganesh Raja
<ganesh.raja_at_gmail.com> w=
> > rote:
> > > > Hi,
> > > >
> > > > I have a query that has spawned parallel slaves. All the slaves are
> > > > now waiting for a single slave to respond back. This slave process
is
> > > > churning away and eating up lot of CPU. [According to prstat]
> > > >
> > > > When I see in v$sess_io i don't see any activity of this Process and
> > > > since it is on the CPU it is not waiting .. So what is happening
here.
> > > >
> > > > The weird thing is that that the CPU statistics for this process are
> > > > not being updated on v$sessstat
> > > >
> > > > Any help is greatly appreciated.
> > > >
> > > > Cheers
> > > > Ganesh R
> > > >
> > > --
> > > http://www.freelists.org/webpage/oracle-l
> > >=20
> > >
> > --
> > http://www.freelists.org/webpage/oracle-l
> >
> >
> > **********************************************************************
> > The information contained in this communication is
> > confidential, is intended only for the use of the recipient
> > named above, and may be legally privileged.
> > If the reader of this message is not the intended
> > recipient, you are hereby notified that any dissemination,
> > distribution, or copying of this communication is strictly
> > prohibited.
> > If you have received this communication in error,
> > please re-send this communication to the sender and
> > delete the original message or any copy of it from your
> > computer system. Thank You.
> >
> >

>

--
http://www.freelists.org/webpage/oracle-l