Hi Ian,
You are right, in this case the contention may be
"self-inflicted" by the process that you are running.
Hopefully you are using "both prongs" in the 2-prong
method that we suggest. The O/S prong is very useful
especially in cases such as these. The state of
"waited short time" indicates that the wait events are
for a duration of < 1/100th of a second (centisecond).
Such wait events are usually not a "source for
concern" unless they occur many times within a short
period of time (say the duration of running the SQL
statement).
Although "many" cannot be defined and generalized, a
frequency of many thousands of times within the short
period should get your attention. You also should use
the 10046 wait event with level 12 for the offending
session, to trap the wait-events at the SQL
statement-level. Details on how to do that has been
described in "chapter 2", in the section "Trapping
Wait Events to a trace file". You can then look at the
ora_SPID.trc file and search for the keyword "WAIT"
and go from there.
To determine what P1 is in this case and in the
future, you can query P1_TEXT for a description of P1.
The same holds good for P2 and P3. Below is a
copy/paste of the "direct path read" wait event from
the Reference Guide:
direct path read
During Direct Path operations the data is
asynchronously read from the database files. At some
stage the session needs to make sure that all
outstanding asynchronous I/O have been completed to
disk. This can also happen if during a direct read no
more slots are available to store outstanding load
requests (a load request could consist of multiple
I/Os).
Wait Time: 10 seconds. The session will be posted by
the completing asynchronous I/O. It will never wait
the entire 10 seconds. The session waits in a tight
loop until all outstanding I/Os have completed.
Parameters:
descriptor address
This is a pointer to the I/O context of outstanding
direct I/Os on which the session is currently waiting.
first dba
The dba of the oldest I/O in the context referenced
by the descriptor address.
block cnt
Number of valid buffers in the context referenced by
the descriptor address.
One of the related init.ora parameters that affects
this is DB_FILE_DIRECT_IO_COUNT. Keep us posted on how
things progress.
Cheers,
Gaja
- "MacGregor, Ian A." <ian_at_SLAC.Stanford.EDU> wrote:
> I ran the Celko algorthm against scott.emp. Wow
> was that fast! Then I ran it against the 70,000
> record table which my alogorthm took 8 seconds to
> compute. The statment hung. Armed with "ORACLE
> 101 Performance Tuning" by Gaja, Kirti, and a John
> A. Kostelac, I discovered that the event being
> waited for was "direct path read." The explanation
> states this is usually due to contention on
> devices.
>
> I was running this on a test machine the with no
> other user sql statements being run. The only
> conflict must be self-inflicted. I then went to
> the v$session_wait table to further clarify the
> contention, but was not successful.
> V$SESSION_EVENT showed that the total_waits columns
> was only being incremented for the "direct path
> read" event. The state of the wait for "direct
> path read" event was always "waited short time"
>
> What does the p1 value of v$session_wait indicate in
> this case? It does not appear to be a file_id.
>
> I killed the query after several minutes. The
> "direct path read" wait events continued to
> increment until then.
>
> Ian MacGregor
> Stanford Linear Accelerator Center
> ian_at_slac.stanford.edu
>
>
>
> -----Original Message-----
> Sent: Monday, July 02, 2001 7:47 AM
> To: Multiple recipients of list ORACLE-L
> Code
>
>
> Ian,
>
> Nice stuff!
>
> Since this subject was brought back up, I thought
> maybe some would be
> interested in the following. I've never had a need
> to calculate a median,
> but, I knew Celko's SQL for Smarties had a few
> variations and examples from
> various people, each with caveats. And then there
> were differences between
> what he termed statistical and financial mean, and
> some other things as
> well. Anyway, a google search turned up another
> Celko solution. And this one
> also brings up the concept of weighted median.
>
> Here is his example,
>
http://www.intelligententerprise.com/db_area/archives/1999/992004/celko.shtm
> l, modified by me to use the standard EMP table's
> SAL column:
>
> SQL> SELECT AVG(DISTINCT x.sal)
> 2 FROM (SELECT F1.sal
> 3 FROM emp F1, emp F2
> 4 GROUP BY F1.empno, F1.sal
> 5 HAVING SUM(CASE WHEN F2.sal = F1.sal
> 6 THEN 1 ELSE 0 END)
> 7 >= ABS(SUM(CASE WHEN F2.sal < F1.sal
> THEN 1
> 8 WHEN F2.sal > F1.sal
> THEN -1
> 9 ELSE 0 END)))
> 10 X
> 11 /
>
> AVG(DISTINCTX.SAL)
> ------------------
> 1550
>
> The link above goes into some detail regarding the
> logic behind the query
> and how his query finally reached the form above. I
> may never need to do a
> median, but, this subject has been a good
> opportunity for learning. I've
> tested the above with even, odd, multiple occurences
> of SAL, null,s etc. It
> seems to work, but, everyone have a whack at it if
> you like.
>
> Regards,
>
> Larry G. Elkins
> elkinsl_at_flash.net
>
>
> > -----Original Message-----
> > From: root_at_fatcity.com [mailto:root_at_fatcity.com]On
> Behalf Of MacGregor,
> > Ian A.
> > Sent: Saturday, June 30, 2001 9:00 PM
> > To: Multiple recipients of list ORACLE-L
> > Subject: Calculating The Median: Error Discovered
> in Oracle SQL 101 Code
> >
> >
> > I ran the code from Oracle SQL 101 which Jared
> posted modifying
> > it to find the median of the sal column on that
> table
> >
> > SQL> select
> > 2 rownum,
> > 3 sal
> > 4 from (
> > 5 select sal
> > 6 from scott.emp
> > 7 where sal is not null
> > 8 union
> > 9 select 1 from dual where 1=2
> > 10 )
> > 11 group by sal, rownum
> > 12 having rownum >= (
> > 13 select decode( mod(total_freq,2),
> > 14 1,trunc(total_freq/2 + 1),
> > 15 0,trunc(total_freq/2)
> > 16 )
> > 17 from (
> > 18 select count(*) total_freq
> > 19 from scott.emp
> > 20 where sal is not null
> > 21 )
> > 22 )
> > 23 and rownum <= (
> > 24 select decode( mod(total_freq,2),
> > 25 1,trunc(total_freq/2 + 1),
> > 26 0,trunc(total_freq/2 + 1)
> > 27 )
> > 28 from (
> > 29 select count(*) total_freq
> > 30 from scott.emp
> > 31 where sal is not null
> > 32 )
> > 33 )
> > 34 /
> >
> > values
> > averaged
> > ROWNUM in median
> > ---------- ----------
> > 7 1600
> > 8 2450
> > ----------
> > Median 2025
> >
> >
>
> > --------------------
> > This answer is different from the result of the
> code I posted
> > which uses the new analytical functions.
> >
> > select
> > case
> > when mod(number_salaried,2) = 0 then
> > (select sum(sal)/2 from(select sal,
> row_number()
> > over ( order by sal) as salrank
> > from scott.emp)
> > where salrank = number_salaried/2
> > or salrank = number_salaried/2 +1)
> > else
> > (select sal from(select sal, row_number()
> > over ( order by sal) as salrank
> > from scott.emp)
> > where salrank = ceil(number_salaried/2))
> > end median
> > from (select sal,rank() over (order by sal) as rk
> from scott.emp),
> > (select count(sal) number_salaried from
> scott.emp)
> > where rk = 1
> > /
> >
> > MEDIAN
> > ---------
> > 1550
> >
>
> > ------------------------------
> > Emp is a 14 row table . The median should be the
> average of the
> > seventh and eighth values.
> > I cleared the computes and columns and ran the
> first part of the
> > SQL 101 code
> >
> > clear computes
> > utes cleared
> > select
>
=== message truncated ===
Gaja Krishna Vaidyanatha
Director, Storage Management Products,
Quest Software, Inc.
Co-author - Oracle Performance Tuning 101
http://www.osborne.com/database_erp/0072131454/0072131454.shtml
Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail
http://personal.mail.yahoo.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Gaja Krishna Vaidyanatha
INET: oraperfman_at_yahoo.com
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Mon Jul 02 2001 - 15:39:11 CDT
