RE: direct path write waits, please help
FYI The application that is causing the wait events is a third party product that really sucks (autocommit, no bind variables, bad data model, etc., etc.) We're on EMC Symmetrix. There are hardly any wait-io's measurable on AIX; the log file sync problem is not so much of a problem; moving to raw volumes for the redologs should put the log file sync waits down in the top-n. Indeed, the direct path writes have a neglible effect on overall response time. I just want to get a good understanding of the 'direct path writes'. sorts (disk) =4 physical writes direct = 2,444,555 physical writes = 2,470,809 Those are statistics gathered in a two hour interval. Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Tue, 29 Jul 2003 10:14:29 -0800 But, I would like to know how this seemingly high wait for 'direct path write' is affecting the overall response time. (ResponseTime = WaitTime + ServiceTime) If the 'CPU used by this session' is not considered in light of these wait times, aren't you getting ready to bark at the wrong tree? - Kirti --- John Kanagaraj [EMAIL PROTECTED] wrote: Hans, Now let me guess Your disks are all RAID 5, right? And you possibly are bottlenecking on CPU as well? It is clear from the Top 5 that writes are an issue across the board, to TEMP (direct path write), Redo (log file sync) and DB files (db file parallel writes). Creating a RAID 1 set of disks and moving at least the TEMP, RBS, Redo (and Arch if present) to this will definitely help. John Kanagaraj Phone: 408-970-7002 (W) Fax: 408 327 3086 (Call/Email prior to fax) -Original Message- Sent: Tuesday, July 29, 2003 8:54 AM To: Multiple recipients of list ORACLE-L Hi All, Please help me tune this i/o related wait event. This is my 8.1.6 statspack top-5 wait list: Top 5 Wait Events ~ Wait % Total Event Waits Time (cs) Wt Time --- direct path write 304,867 35,925 49.83 log file sync 145,015 23,441 32.52 db file sequential read11,3703,684 5.11 file open 9813,326 4.61 db file parallel write 1,8933,115 4.32 You'll notice that 'direct path write' is the most expensive one in the list. I cannot find enough info on the net about this wait event, therefore I'm asking the real experts. What events in Oracle trigger this wait event? In what way is this event different from db file parallel write? I mostly read comments that suggest lots of sorting and parallallel queries. However, most sorts are done in memory and degree = 0 for all tables. Any suggestions are very welcome. Thanks, Hans de Git __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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). _ Chatten met je online vrienden via MSN Messenger. http://messenger.msn.nl/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hans de Git INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
Re: direct path write waits, please help
Hi!
Either your 4 disk sorts are huge generating lot's of IO or there direct
writes aren't because of sorting.
They could be because NOCACHE LOB access for example (also CTAS and direct
path insert). You should view 10046 level 8 output and check in which file
are the IOs occurring.
Tanel.
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Wednesday, July 30, 2003 11:34 AM
FYI
The application that is causing the wait events is a third party product
that really sucks (autocommit, no bind variables, bad data model, etc.,
etc.) We're on EMC Symmetrix. There are hardly any wait-io's measurable on
AIX; the log file sync problem is not so much of a problem; moving to raw
volumes for the redologs should put the log file sync waits down in the
top-n.
Indeed, the direct path writes have a neglible effect on overall response
time. I just want to get a good understanding of the 'direct path writes'.
sorts (disk) =4
physical writes direct = 2,444,555
physical writes = 2,470,809
Those are statistics gathered in a two hour interval.
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Date: Tue, 29 Jul 2003 10:14:29 -0800
But, I would like to know how this seemingly high wait for 'direct path
write' is affecting the
overall response time. (ResponseTime = WaitTime + ServiceTime)
If the 'CPU used by this session' is not considered in light of these wait
times, aren't you
getting ready to bark at the wrong tree?
- Kirti
--- John Kanagaraj [EMAIL PROTECTED] wrote:
Hans,
Now let me guess Your disks are all RAID 5, right? And you possibly
are
bottlenecking on CPU as well? It is clear from the Top 5 that writes
are
an
issue across the board, to TEMP (direct path write), Redo (log file
sync)
and DB files (db file parallel writes). Creating a RAID 1 set of disks
and
moving at least the TEMP, RBS, Redo (and Arch if present) to this will
definitely help.
John Kanagaraj
Phone: 408-970-7002 (W)
Fax: 408 327 3086 (Call/Email prior to fax)
-Original Message-
Sent: Tuesday, July 29, 2003 8:54 AM
To: Multiple recipients of list ORACLE-L
Hi All,
Please help me tune this i/o related wait event. This is my 8.1.6
statspack
top-5 wait list:
Top 5 Wait Events
~ Wait
%
Total
Event Waits Time (cs)
Wt
Time
---
direct path write 304,867 35,925
49.83
log file sync 145,015 23,441
32.52
db file sequential read11,3703,684
5.11
file open 9813,326
4.61
db file parallel write 1,8933,115
4.32
You'll notice that 'direct path write' is the most expensive one in the
list. I cannot find enough info on the net about this wait event,
therefore
I'm asking the real experts.
What events in Oracle trigger this wait event? In what way is this
event
different from db file parallel write?
I mostly read comments that suggest lots of sorting and parallallel
queries.
However, most sorts are done in memory and degree = 0 for all tables.
Any suggestions are very welcome.
Thanks,
Hans de Git
__
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Kirtikumar Deshpande
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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).
_
Chatten met je online vrienden via MSN Messenger. http://messenger.msn.nl/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Hans de Git
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru')
Re: direct path write waits, please help
Could it be that hash joins account for the writes to TEMP without
increasing the sort stats? Or 'group by' statements, perhaps?
In a 10 minute interval, I can see no increase in the number of sorts to
disk, but the writes and reads from v$tempstat increase by thousands.
If that's the case, then I think I should increase sort_area_size and/or
hash_area_size (memory is not an issue...). Please correct me if i'm wrong.
Would it be beneficial to change optimizer_index_caching or
optimizer_index_cost_adj to force Oracle into using more nested loops?
Don't get me wrong: I'm all against throwing hardware at an application that
is so poorly written. But we've past that point, the supplier will not
change its behaviour, and from a functional point of view, the end-users are
very satisfied. Bummer..
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Date: Wed, 30 Jul 2003 01:24:24 -0800
Hi!
Either your 4 disk sorts are huge generating lot's of IO or there direct
writes aren't because of sorting.
They could be because NOCACHE LOB access for example (also CTAS and direct
path insert). You should view 10046 level 8 output and check in which file
are the IOs occurring.
Tanel.
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Wednesday, July 30, 2003 11:34 AM
FYI
The application that is causing the wait events is a third party product
that really sucks (autocommit, no bind variables, bad data model, etc.,
etc.) We're on EMC Symmetrix. There are hardly any wait-io's measurable
on
AIX; the log file sync problem is not so much of a problem; moving to raw
volumes for the redologs should put the log file sync waits down in the
top-n.
Indeed, the direct path writes have a neglible effect on overall response
time. I just want to get a good understanding of the 'direct path
writes'.
sorts (disk) =4
physical writes direct = 2,444,555
physical writes = 2,470,809
Those are statistics gathered in a two hour interval.
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Date: Tue, 29 Jul 2003 10:14:29 -0800
But, I would like to know how this seemingly high wait for 'direct path
write' is affecting the
overall response time. (ResponseTime = WaitTime + ServiceTime)
If the 'CPU used by this session' is not considered in light of these
wait
times, aren't you
getting ready to bark at the wrong tree?
- Kirti
--- John Kanagaraj [EMAIL PROTECTED] wrote:
Hans,
Now let me guess Your disks are all RAID 5, right? And you
possibly
are
bottlenecking on CPU as well? It is clear from the Top 5 that writes
are
an
issue across the board, to TEMP (direct path write), Redo (log file
sync)
and DB files (db file parallel writes). Creating a RAID 1 set of disks
and
moving at least the TEMP, RBS, Redo (and Arch if present) to this will
definitely help.
John Kanagaraj
Phone: 408-970-7002 (W)
Fax: 408 327 3086 (Call/Email prior to fax)
-Original Message-
Sent: Tuesday, July 29, 2003 8:54 AM
To: Multiple recipients of list ORACLE-L
Hi All,
Please help me tune this i/o related wait event. This is my 8.1.6
statspack
top-5 wait list:
Top 5 Wait Events
~ Wait
%
Total
Event Waits Time (cs)
Wt
Time
---
direct path write 304,867 35,925
49.83
log file sync 145,015 23,441
32.52
db file sequential read11,3703,684
5.11
file open 9813,326
4.61
db file parallel write 1,8933,115
4.32
You'll notice that 'direct path write' is the most expensive one in
the
list. I cannot find enough info on the net about this wait event,
therefore
I'm asking the real experts.
What events in Oracle trigger this wait event? In what way is this
event
different from db file parallel write?
I mostly read comments that suggest lots of sorting and parallallel
queries.
However, most sorts are done in memory and degree = 0 for all tables.
Any suggestions are very welcome.
Thanks,
Hans de Git
__
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Kirtikumar Deshpande
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
Re: direct path write waits, please help
Hi! Group by is still doing sorting, and is accounted in sorts stats (unless an index scan wasn't used to get rows in desired order). But yes, hash joins don't increase sort stats by themselves. You should check 10046 level 8 output, find which SQL statement is doing direct path writes, then get execution plan for these statements to see whether they are using hash joins (since you are on 8.1.6, it can be bit problematic, because execution path information is stored in raw trace file starting from 8.1.7 AFAIK. Problematic in sense that, when doing explain plan under regular session, some session parameters might be different than using the application). But if you find out, that statements with hash join execution plans are the ones waiting on direct path access on temp datafiles, you should also enable event 10104 at level 1 to get hash join trace information. Maybe your statistics are not up to date, that CBO thinks based on ancient statistics it's good idea to hash join because one row set is fairly small, but when it starts building hash build partitions, they actually don't fit into hash area, and some of the partitions have to be written to temp. Check under PHASE 1 in 10104 trace, how many total build partitions you got and how may of them fit into memory. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, July 30, 2003 2:14 PM Could it be that hash joins account for the writes to TEMP without increasing the sort stats? Or 'group by' statements, perhaps? In a 10 minute interval, I can see no increase in the number of sorts to disk, but the writes and reads from v$tempstat increase by thousands. If that's the case, then I think I should increase sort_area_size and/or hash_area_size (memory is not an issue...). Please correct me if i'm wrong. Would it be beneficial to change optimizer_index_caching or optimizer_index_cost_adj to force Oracle into using more nested loops? Don't get me wrong: I'm all against throwing hardware at an application that is so poorly written. But we've past that point, the supplier will not change its behaviour, and from a functional point of view, the end-users are very satisfied. Bummer.. Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Wed, 30 Jul 2003 01:24:24 -0800 Hi! Either your 4 disk sorts are huge generating lot's of IO or there direct writes aren't because of sorting. They could be because NOCACHE LOB access for example (also CTAS and direct path insert). You should view 10046 level 8 output and check in which file are the IOs occurring. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, July 30, 2003 11:34 AM FYI The application that is causing the wait events is a third party product that really sucks (autocommit, no bind variables, bad data model, etc., etc.) We're on EMC Symmetrix. There are hardly any wait-io's measurable on AIX; the log file sync problem is not so much of a problem; moving to raw volumes for the redologs should put the log file sync waits down in the top-n. Indeed, the direct path writes have a neglible effect on overall response time. I just want to get a good understanding of the 'direct path writes'. sorts (disk) =4 physical writes direct = 2,444,555 physical writes = 2,470,809 Those are statistics gathered in a two hour interval. Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Tue, 29 Jul 2003 10:14:29 -0800 But, I would like to know how this seemingly high wait for 'direct path write' is affecting the overall response time. (ResponseTime = WaitTime + ServiceTime) If the 'CPU used by this session' is not considered in light of these wait times, aren't you getting ready to bark at the wrong tree? - Kirti --- John Kanagaraj [EMAIL PROTECTED] wrote: Hans, Now let me guess Your disks are all RAID 5, right? And you possibly are bottlenecking on CPU as well? It is clear from the Top 5 that writes are an issue across the board, to TEMP (direct path write), Redo (log file sync) and DB files (db file parallel writes). Creating a RAID 1 set of disks and moving at least the TEMP, RBS, Redo (and Arch if present) to this will definitely help. John Kanagaraj Phone: 408-970-7002 (W) Fax: 408 327 3086 (Call/Email prior to fax) -Original Message- Sent: Tuesday, July 29, 2003 8:54 AM To: Multiple recipients of list ORACLE-L Hi All, Please help me tune this i/o related wait event. This is my 8.1.6 statspack top-5 wait list: Top 5 Wait Events ~ Wait % Total Event
Re: direct path write waits, please help
Quote: Group by is still doing sorting, and is accounted in sorts stats (unless an index scan wasn't used to get rows in desired order). But yes, hash joins don't increase sort stats by themselves. end of quote I think you meant was usedin sted of wasn't used. Just like you said, is's all hash joins. It's a production system; I can only peek via the perfstat schema, but I know the application and instance well enough. Case closed, as far as I'm concerned. Thank you all for the input. Regards, Hans de Git Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Wed, 30 Jul 2003 03:59:23 -0800 Hi! Group by is still doing sorting, and is accounted in sorts stats (unless an index scan wasn't used to get rows in desired order). But yes, hash joins don't increase sort stats by themselves. You should check 10046 level 8 output, find which SQL statement is doing direct path writes, then get execution plan for these statements to see whether they are using hash joins (since you are on 8.1.6, it can be bit problematic, because execution path information is stored in raw trace file starting from 8.1.7 AFAIK. Problematic in sense that, when doing explain plan under regular session, some session parameters might be different than using the application). But if you find out, that statements with hash join execution plans are the ones waiting on direct path access on temp datafiles, you should also enable event 10104 at level 1 to get hash join trace information. Maybe your statistics are not up to date, that CBO thinks based on ancient statistics it's good idea to hash join because one row set is fairly small, but when it starts building hash build partitions, they actually don't fit into hash area, and some of the partitions have to be written to temp. Check under PHASE 1 in 10104 trace, how many total build partitions you got and how may of them fit into memory. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, July 30, 2003 2:14 PM Could it be that hash joins account for the writes to TEMP without increasing the sort stats? Or 'group by' statements, perhaps? In a 10 minute interval, I can see no increase in the number of sorts to disk, but the writes and reads from v$tempstat increase by thousands. If that's the case, then I think I should increase sort_area_size and/or hash_area_size (memory is not an issue...). Please correct me if i'm wrong. Would it be beneficial to change optimizer_index_caching or optimizer_index_cost_adj to force Oracle into using more nested loops? Don't get me wrong: I'm all against throwing hardware at an application that is so poorly written. But we've past that point, the supplier will not change its behaviour, and from a functional point of view, the end-users are very satisfied. Bummer.. Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Wed, 30 Jul 2003 01:24:24 -0800 Hi! Either your 4 disk sorts are huge generating lot's of IO or there direct writes aren't because of sorting. They could be because NOCACHE LOB access for example (also CTAS and direct path insert). You should view 10046 level 8 output and check in which file are the IOs occurring. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, July 30, 2003 11:34 AM FYI The application that is causing the wait events is a third party product that really sucks (autocommit, no bind variables, bad data model, etc., etc.) We're on EMC Symmetrix. There are hardly any wait-io's measurable on AIX; the log file sync problem is not so much of a problem; moving to raw volumes for the redologs should put the log file sync waits down in the top-n. Indeed, the direct path writes have a neglible effect on overall response time. I just want to get a good understanding of the 'direct path writes'. sorts (disk) =4 physical writes direct = 2,444,555 physical writes = 2,470,809 Those are statistics gathered in a two hour interval. Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Tue, 29 Jul 2003 10:14:29 -0800 But, I would like to know how this seemingly high wait for 'direct path write' is affecting the overall response time. (ResponseTime = WaitTime + ServiceTime) If the 'CPU used by this session' is not considered in light of these wait times, aren't you getting ready to bark at the wrong tree? - Kirti --- John Kanagaraj [EMAIL PROTECTED] wrote: Hans, Now let me guess Your disks are all RAID 5, right? And you possibly are bottlenecking on CPU as well? It is clear from the Top 5 that writes are an issue across the board, to TEMP (direct path write), Redo (log file sync) and DB files (db file parallel
Re: direct path write waits, please help
Quote: Group by is still doing sorting, and is accounted in sorts stats (unless an index scan wasn't used to get rows in desired order). But yes, hash joins don't increase sort stats by themselves. end of quote I think you meant was usedin sted of wasn't used. Just like you said, is's all hash joins. It's a production system; I can only peek via the perfstat schema, but I know the application and instance well enough. Yeah, that's what I meant, sorry for this typo. Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
direct path write waits, please help
Hi All, Please help me tune this i/o related wait event. This is my 8.1.6 statspack top-5 wait list: Top 5 Wait Events ~ Wait % Total Event Waits Time (cs) Wt Time --- direct path write 304,867 35,925 49.83 log file sync 145,015 23,441 32.52 db file sequential read11,3703,684 5.11 file open 9813,326 4.61 db file parallel write 1,8933,115 4.32 You'll notice that 'direct path write' is the most expensive one in the list. I cannot find enough info on the net about this wait event, therefore I'm asking the real experts. What events in Oracle trigger this wait event? In what way is this event different from db file parallel write? I mostly read comments that suggest lots of sorting and parallallel queries. However, most sorts are done in memory and degree = 0 for all tables. Any suggestions are very welcome. Thanks, Hans de Git _ MSN Zoeken, voor duidelijke zoekresultaten! http://search.msn.nl -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hans de Git INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
RE: direct path write waits, please help
Hi Hans, direct path write cause would be writing temporary segments when sorts are too large to be performed in memory, direct path operations like INSERT /+ APPEND or LOB access. It's hard to guess which one is causing your problems, so I'd suggest you profiling the call having performance issues : alter session set events = '10046 trace name context forever, level 8'; do what is an issue alter session set events = '10046 trace name context off'; Find trace file in udump directory and use tkprof then to identify SQL statement waiting on direct path write. You will need 9i tkprof (any platform is good) to report wait events, otherwise you could review raw trace file . HTH Vadim -Original Message- Sent: Tuesday, July 29, 2003 11:54 AM To: Multiple recipients of list ORACLE-L Hi All, Please help me tune this i/o related wait event. This is my 8.1.6 statspack top-5 wait list: Top 5 Wait Events ~ Wait % Total Event Waits Time (cs) Wt Time --- direct path write 304,867 35,925 49.83 log file sync 145,015 23,441 32.52 db file sequential read11,3703,684 5.11 file open 9813,326 4.61 db file parallel write 1,8933,115 4.32 You'll notice that 'direct path write' is the most expensive one in the list. I cannot find enough info on the net about this wait event, therefore I'm asking the real experts. What events in Oracle trigger this wait event? In what way is this event different from db file parallel write? I mostly read comments that suggest lots of sorting and parallallel queries. However, most sorts are done in memory and degree = 0 for all tables. Any suggestions are very welcome. Thanks, Hans de Git _ MSN Zoeken, voor duidelijke zoekresultaten! http://search.msn.nl -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hans de Git INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gorbounov,Vadim INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
RE: direct path write waits, please help
Hans I did a search on Google, and found this article: http://www.faqchest.com/prgm/oracle-l/ora-02/ora-0206/ora-020638/ora02062414 _22868.html (you'll probably have to patch the link together) According to this article, your biggest and second biggest wait may be connected. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, July 29, 2003 10:54 AM To: Multiple recipients of list ORACLE-L Hi All, Please help me tune this i/o related wait event. This is my 8.1.6 statspack top-5 wait list: Top 5 Wait Events ~ Wait % Total Event Waits Time (cs) Wt Time --- direct path write 304,867 35,925 49.83 log file sync 145,015 23,441 32.52 db file sequential read11,3703,684 5.11 file open 9813,326 4.61 db file parallel write 1,8933,115 4.32 You'll notice that 'direct path write' is the most expensive one in the list. I cannot find enough info on the net about this wait event, therefore I'm asking the real experts. What events in Oracle trigger this wait event? In what way is this event different from db file parallel write? I mostly read comments that suggest lots of sorting and parallallel queries. However, most sorts are done in memory and degree = 0 for all tables. Any suggestions are very welcome. Thanks, Hans de Git _ MSN Zoeken, voor duidelijke zoekresultaten! http://search.msn.nl -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hans de Git INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
RE: direct path write waits, please help
Hans, Now let me guess Your disks are all RAID 5, right? And you possibly are bottlenecking on CPU as well? It is clear from the Top 5 that writes are an issue across the board, to TEMP (direct path write), Redo (log file sync) and DB files (db file parallel writes). Creating a RAID 1 set of disks and moving at least the TEMP, RBS, Redo (and Arch if present) to this will definitely help. John Kanagaraj Phone: 408-970-7002 (W) Fax: 408 327 3086 (Call/Email prior to fax) -Original Message- Sent: Tuesday, July 29, 2003 8:54 AM To: Multiple recipients of list ORACLE-L Hi All, Please help me tune this i/o related wait event. This is my 8.1.6 statspack top-5 wait list: Top 5 Wait Events ~ Wait % Total Event Waits Time (cs) Wt Time --- direct path write 304,867 35,925 49.83 log file sync 145,015 23,441 32.52 db file sequential read11,3703,684 5.11 file open 9813,326 4.61 db file parallel write 1,8933,115 4.32 You'll notice that 'direct path write' is the most expensive one in the list. I cannot find enough info on the net about this wait event, therefore I'm asking the real experts. What events in Oracle trigger this wait event? In what way is this event different from db file parallel write? I mostly read comments that suggest lots of sorting and parallallel queries. However, most sorts are done in memory and degree = 0 for all tables. Any suggestions are very welcome. Thanks, Hans de Git _ MSN Zoeken, voor duidelijke zoekresultaten! http://search.msn.nl -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hans de Git INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
RE: direct path write waits, please help
But, I would like to know how this seemingly high wait for 'direct path write' is
affecting the
overall response time. (ResponseTime = WaitTime + ServiceTime)
If the 'CPU used by this session' is not considered in light of these wait times,
aren't you
getting ready to bark at the wrong tree?
- Kirti
--- John Kanagaraj [EMAIL PROTECTED] wrote:
Hans,
Now let me guess Your disks are all RAID 5, right? And you possibly are
bottlenecking on CPU as well? It is clear from the Top 5 that writes are an
issue across the board, to TEMP (direct path write), Redo (log file sync)
and DB files (db file parallel writes). Creating a RAID 1 set of disks and
moving at least the TEMP, RBS, Redo (and Arch if present) to this will
definitely help.
John Kanagaraj
Phone: 408-970-7002 (W)
Fax: 408 327 3086 (Call/Email prior to fax)
-Original Message-
Sent: Tuesday, July 29, 2003 8:54 AM
To: Multiple recipients of list ORACLE-L
Hi All,
Please help me tune this i/o related wait event. This is my 8.1.6 statspack
top-5 wait list:
Top 5 Wait Events
~ Wait %
Total
Event Waits Time (cs) Wt
Time
---
direct path write 304,867 35,925
49.83
log file sync 145,015 23,441
32.52
db file sequential read11,3703,684
5.11
file open 9813,326
4.61
db file parallel write 1,8933,115
4.32
You'll notice that 'direct path write' is the most expensive one in the
list. I cannot find enough info on the net about this wait event, therefore
I'm asking the real experts.
What events in Oracle trigger this wait event? In what way is this event
different from db file parallel write?
I mostly read comments that suggest lots of sorting and parallallel queries.
However, most sorts are done in memory and degree = 0 for all tables.
Any suggestions are very welcome.
Thanks,
Hans de Git
__
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Kirtikumar Deshpande
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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).
Re: direct path write waits, please help
WHO LET THE DOGS OUT!!! on 7/29/03 11:14 AM, Kirtikumar Deshpande at [EMAIL PROTECTED] wrote: But, I would like to know how this seemingly high wait for 'direct path write' is affecting the overall response time. (ResponseTime = WaitTime + ServiceTime) If the 'CPU used by this session' is not considered in light of these wait times, aren't you getting ready to bark at the wrong tree? - Kirti --- John Kanagaraj [EMAIL PROTECTED] wrote: Hans, Now let me guess Your disks are all RAID 5, right? And you possibly are bottlenecking on CPU as well? It is clear from the Top 5 that writes are an issue across the board, to TEMP (direct path write), Redo (log file sync) and DB files (db file parallel writes). Creating a RAID 1 set of disks and moving at least the TEMP, RBS, Redo (and Arch if present) to this will definitely help. John Kanagaraj Phone: 408-970-7002 (W) Fax: 408 327 3086 (Call/Email prior to fax) -Original Message- Sent: Tuesday, July 29, 2003 8:54 AM To: Multiple recipients of list ORACLE-L Hi All, Please help me tune this i/o related wait event. This is my 8.1.6 statspack top-5 wait list: Top 5 Wait Events ~ Wait % Total Event Waits Time (cs) Wt Time --- direct path write 304,867 35,925 49.83 log file sync 145,015 23,441 32.52 db file sequential read11,3703,684 5.11 file open 9813,326 4.61 db file parallel write 1,8933,115 4.32 You'll notice that 'direct path write' is the most expensive one in the list. I cannot find enough info on the net about this wait event, therefore I'm asking the real experts. What events in Oracle trigger this wait event? In what way is this event different from db file parallel write? I mostly read comments that suggest lots of sorting and parallallel queries. However, most sorts are done in memory and degree = 0 for all tables. Any suggestions are very welcome. Thanks, Hans de Git __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Gorman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
