Re: waits vs. logons
Well, let's hope the session does some work! ;-) You can check v$sesstat for time spent on various operations, for example, 'CPU used by this session'. That certainly is to be added to the total wait time. But now that you mention it, I don't know which stats should add up to equal time logged on. __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Baumgartel INET: [EMAIL PROTECTED] 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: [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: waits vs. logons
yep, look at CPU used by this session in V$sesstat. CPU + WAIT should get close to the logon_secs. There are a number of reasons why it can't and shouldn't but for simplicity go with it. Anjo. On Wednesday 04 September 2002 22:59, you wrote: I run the following query to compare the total waits for a session verses the seconds logged on: select a.sid, a.ontime longon_secs, round(b.waittime) wait_secs from (select sid, (sysdate - logon_time)*24*60*60 ontime from v$session ) a, (select sid, sum(time_waited)/100 waittime from v$session_event group by sid) b where a.sid = b.sid; I get the following results: SID LONGON_SECS WAIT_SECS -- --- -- 1 595995 595989 2 595994 471204 3 595994 595585 4 595994 594580 5 595994 595492 6 595994 593639 9 595993 577157 144943 1303 24 595844 588441 261733 1728 275308 2478 29 517269 0 32 53 0 35 415158 13 50 140478 140371 51 719507 56 14507 8706 592269221 I am very much a novice when it comes to wait statistics. When a session, for example #51, has been logged on for 719 seconds but has experienced only 507 wait seconds, what did it do the rest of the time? I am assuming some kind of work? Any way to determine what? Tom -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Anjo Kolk INET: [EMAIL PROTECTED] 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: [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: waits vs. logons
Title: Message First, youre missing user-mode CPU time consumed by the Oracle session. The statistic that is supposed to hold this number is CPU used by this session from v$sesstat. But there are bugs in that statistic. Second, some of the time that an Oracle process spends sleeping (because of context switches imposed by a timesharing operating system) isnt counted either. See www.hotsos.com/dnloads/1/kevents/unaccounted-for.html for a start. Our Hotsos Clinic explains in complete detail. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic, Oct 13 San Francisco, Oct 1517 Dallas, Dec 911 Honolulu - 2003 Hotsos Symposium on Oracle System Performance, Feb 912 Dallas - Next event: Miracle Database Forum, Sep 2022 Middlefart Denmark -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Terrian, Tom Sent: Wednesday, September 04, 2002 4:00 PM To: Multiple recipients of list ORACLE-L Subject: waits vs. logons I run the following query to compare the total waits for a session verses the seconds logged on: select a.sid, a.ontime longon_secs, round(b.waittime) wait_secs from (select sid, (sysdate - logon_time)*24*60*60 ontime from v$session ) a, (select sid, sum(time_waited)/100 waittime from v$session_event group by sid) b where a.sid = b.sid; I get the following results: SID LONGON_SECS WAIT_SECS -- --- -- 1 595995 595989 2 595994 471204 3 595994 595585 4 595994 594580 5 595994 595492 6 595994 593639 9 595993 577157 14 4943 1303 24 595844 588441 26 1733 1728 27 5308 2478 29 517269 0 32 53 0 35 415158 13 50 140478 140371 51 719 507 56 14507 8706 59 2269 221 I am very much a novice when it comes to wait statistics. When a session, for example #51, has been logged on for 719 seconds but has experienced only 507 wait seconds, what did it do the rest of the time? I am assuming some kind of work? Any way to determine what? Tom
Re: waits vs. logons
Could be SQL*Net message from client Query v$session_event to see what else a session has waited on: select sess.username, sess.sid, se.event, se.total_waits, se.total_timeouts, se.time_waited/100 time_waited, se.average_wait from v$session_event se, v$session sess where event like '%' and sess.sid = se.sid and sess.username is not null order by username, sid; Jared Terrian, Tom [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 09/04/2002 01:59 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:waits vs. logons I run the following query to compare the total waits for a session verses the seconds logged on: select a.sid, a.ontime longon_secs, round(b.waittime) wait_secs from (select sid, (sysdate - logon_time)*24*60*60 ontime from v$session ) a, (select sid, sum(time_waited)/100 waittime from v$session_event group by sid) b where a.sid = b.sid; I get the following results: SID LONGON_SECS WAIT_SECS -- --- -- 1 595995 595989 2 595994 471204 3 595994 595585 4 595994 594580 5 595994 595492 6 595994 593639 9 595993 577157 144943 1303 24 595844 588441 261733 1728 275308 2478 29 517269 0 32 53 0 35 415158 13 50 140478 140371 51 719507 56 14507 8706 592269221 I am very much a novice when it comes to wait statistics. When a session, for example #51, has been logged on for 719 seconds but has experienced only 507 wait seconds, what did it do the rest of the time? I am assuming some kind of work? Any way to determine what? Tom -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] 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: [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: Waits 8i vs. 9i??
Internal code changes= additional features=fine grained (event) reporting? Best Regards, K Gopalakrishnan Bangalore, INDIA - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, May 16, 2002 9:13 PM Has anyone noticed that the number of event waits in 9i seems much much higher than in 8i. This is the number, not the time waited mind you, so this doesn't really have performance implications. I'm just wondering if this betrays some internal code changes in the way Oracle is reporting these events. Thoughts RF -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Freeman, Robert INET: [EMAIL PROTECTED] 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: [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.com -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] 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: [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: Waits 8i vs. 9i??
Isn't that something to do with 9i being able to report wait times in nanoseconds instead of (milliseconds? or microseconds?) in previous versions ?? Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Thursday, May 16, 2002 12:20 PM To: Multiple recipients of list ORACLE-L Internal code changes= additional features=fine grained (event) reporting? Best Regards, K Gopalakrishnan Bangalore, INDIA *2 This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. *2
RE: Waits 8i vs. 9i??
Not exactly. The granularity of capturing times increased in 9i, but as Gopal implied, there are just a lot more wait events in 9i as compared to the previous releases. Check this link out to see what new events were introduced in 9i : http://www.oraperf.com/reference.html and click on Wait Events by Version. - Kirti -Original Message- Sent: Thursday, May 16, 2002 1:06 PM To: Multiple recipients of list ORACLE-L Isn't that something to do with 9i being able to report wait times in nanoseconds instead of (milliseconds? or microseconds?) in previous versions ?? Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Thursday, May 16, 2002 12:20 PM To: Multiple recipients of list ORACLE-L Internal code changes= additional features=fine grained (event) reporting? Best Regards, K Gopalakrishnan Bangalore, INDIA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] 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: [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: Waits 8i vs. 9i??
Raj: Oracle9i gives timing information in Micro Seconds. Not Nano Seconds though modern CPUs clocks ticks in nano seconds. The older versions (8i and below) give timing info in Centi Seconds (1/100th of a second) . Best Regards, K Gopalakrishnan Bangalore, INDIA - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, May 16, 2002 11:35 PM Isn't that something to do with 9i being able to report wait times in nanoseconds instead of (milliseconds? or microseconds?) in previous versions ?? Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Thursday, May 16, 2002 12:20 PM To: Multiple recipients of list ORACLE-L Internal code changes= additional features=fine grained (event) reporting? Best Regards, K Gopalakrishnan Bangalore, INDIA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] 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: [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: Waits 8i vs. 9i??
You might notice more total event completions in 9i because there are about 50% more segments of kernel code that are instrumented in 9i than there were in 8i (~200 events in 8i, ~300 in 9i). Clock granularity is 0.01 in 8i, so events that complete in the same 0.01-sec quantum as they began will register ela=0 in the trace data (WAITED SHORT TIME). But Oracle tallied a completion for every completed event, even if there were several completions of the same event within the same quantum. In 9i, the clock granularity is 0.01, so you'll get more non-zero times in 9i, but the number of event completions reported for a given sequence of events will remain the same. Cary Millsap Hotsos Enterprises, Ltd. [EMAIL PROTECTED] http://www.hotsos.com -Original Message- Rajendra Sent: Thursday, May 16, 2002 1:06 PM To: Multiple recipients of list ORACLE-L Isn't that something to do with 9i being able to report wait times in nanoseconds instead of (milliseconds? or microseconds?) in previous versions ?? Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Thursday, May 16, 2002 12:20 PM To: Multiple recipients of list ORACLE-L Internal code changes= additional features=fine grained (event) reporting? Best Regards, K Gopalakrishnan Bangalore, INDIA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cary Millsap INET: [EMAIL PROTECTED] 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: [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: Waits 8i vs. 9i??
There is microsecond granularity in some of the columns, but not all platforms support microsecond timing. So some of these are centi seconds multipled by 1. There are ofcourse exceptions (like Compaq Alpha, that suppports msec clocks). There are more wait events in Oracle9i but the top wait events shouldn't have changed. Just think about all the functionality changes and the code that supports that. May be Oracle9 is 50 percent larger than Oracle8i. Oh, btw Oracle8i supported already microsecond timing on some platforms but that was converted to centi seconds. Just to be consistent. Anjo. Freeman, Robert wrote: Has anyone noticed that the number of event waits in 9i seems much much higher than in 8i. This is the number, not the time waited mind you, so this doesn't really have performance implications. I'm just wondering if this betrays some internal code changes in the way Oracle is reporting these events. Thoughts RF -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Freeman, Robert INET: [EMAIL PROTECTED] 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: [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.com -- Author: Anjo Kolk INET: [EMAIL PROTECTED] 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: [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: WAITS
List, --- [EMAIL PROTECTED] wrote: Check out the statspack viewer tool at http://www.geocities.com/alexdabr/ The tool (at least the screen snapshots) does look nice, but the author seems to belong to the CHR camp (see tip 2 cut and paste below). I would take the recommendations with a full shaker of salt (i.e. never take it!) -- Quote -- The data buffer hit ratio (DBHR) statistic represents the percent at which a requested data block is found in the buffer pool. The more DBHR approaches 100%, the more the likelihood that the requested data block resides in memory. This reduces the expensive disk I/O resulting in better application response time. It is recommended to keep DBHR above the 95%. If it falls below 95%, you may experience performance problem with excessive disk I/O. -- Unquote -- John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 The manuals for Oracle are here: http://tahiti.oracle.com The manual for Life is here: http://www.gospelcom.net ** The opinions and statements above are entirely my own and not those of my employer or clients ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Kanagaraj INET: [EMAIL PROTECTED] 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: [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: WAITS
That's neat. I working on HTML/GUI interface to statspack. I think I've got most of the thing figured out. Right now, I'm able to display phys read/write IO directly from one of the statspack tables every hour displayed in graphincal format on a web page. Working on other reports as well. Would love to be able to pool resources here and bounce off some ideas. mkb --- Orr, Steve [EMAIL PROTECTED] wrote: Each morning I produce graphs... I think this is key. Having historical data graphically presented helps to establish the norm and when there may be performance issues to investigate. This follows step 2 of Gaja's Oracle Performance Tuning 101 Methodology which says, Measure and document current performance. To do this I created a DBA monitoring HTML display tool which gets data from V$SYSSTAT and V$SYSTEM_EVENT once a minute, stores it in a round robin database and displays it with RRDTool. I've accumulated 2 months of this data and it's amazing how lightweight it is. With graphs it's easy to see when something's amiss. We capture expensive SQL via StatsPack every 15 minutes and I have correlated a spike on a graph to specific SQL executed 2 hours earlier. Now I'm trying to decide on my next enhancement: 1) HTML/GUI interface to StatsPack data or; 2) Drill down to V$SESSION_WAIT ??? Steve Orr Bozeman, Montana -Original Message- Sent: Thursday, May 09, 2002 5:23 PM To: Multiple recipients of list ORACLE-L Importance: High In general There are two problems in using the top five waits out of statspack: it reports idle waits; no matter how well-tuned your database there will always be a top five. The numbers presented show total time-waited in csecs for the time period. As Jared said we don't know the time period. We don't know the average wait time. I have learned some rudimentary gnuplot skills. Each morning I produce graphs of what went on the in the databases the previous day on and hour by hour basis. If something is really askew I break the hour down into ten minute blocks. This helps me to better recognize patterns of database usage. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve INET: [EMAIL PROTECTED] 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: [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). __ Do You Yahoo!? LAUNCH - Your Yahoo! Music Experience http://launch.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: mkb INET: [EMAIL PROTECTED] 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: [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: WAITS
I'm able to display phys read/write IO directly from... statspack I started with data from StatsPack but instead went directly to the V$ tables to get it real time, or at least every minute. I get physical writes, physical reads, db block and consistent gets, queries executed with soft and hard parses, and SQL*Net bytes sent and received as part of my top level graphs to maintain a pulse on activity. Then I drill down into the wait interface for the details. Would love to be able to pool resources here and bounce off some ideas. Currently my code is a hodgepodge of Perl, Python, RRDTool, PHP, and is piggybacking on ORCA stuff too. I've thought about opening it up to some kind of SourceForge collaborative development but that means I'd have to turn the hodgepodge code into a unified set (probably Python) and commit to supporting it. Don't know if I'm ready for that. I've also thought about presenting it but rather experience the glory of an over-achiever I'm complacently willing to wallow in the anonymity and obscurity of an under-achiever. ;-) Steve Orr Bozeman, Montana -Original Message- Sent: Monday, May 13, 2002 8:03 AM To: Multiple recipients of list ORACLE-L That's neat. I working on HTML/GUI interface to statspack. I think I've got most of the thing figured out. Right now, I'm able to display phys read/write IO directly from one of the statspack tables every hour displayed in graphincal format on a web page. Working on other reports as well. Would love to be able to pool resources here and bounce off some ideas. mkb --- Orr, Steve [EMAIL PROTECTED] wrote: Each morning I produce graphs... I think this is key. Having historical data graphically presented helps to establish the norm and when there may be performance issues to investigate. This follows step 2 of Gaja's Oracle Performance Tuning 101 Methodology which says, Measure and document current performance. To do this I created a DBA monitoring HTML display tool which gets data from V$SYSSTAT and V$SYSTEM_EVENT once a minute, stores it in a round robin database and displays it with RRDTool. I've accumulated 2 months of this data and it's amazing how lightweight it is. With graphs it's easy to see when something's amiss. We capture expensive SQL via StatsPack every 15 minutes and I have correlated a spike on a graph to specific SQL executed 2 hours earlier. Now I'm trying to decide on my next enhancement: 1) HTML/GUI interface to StatsPack data or; 2) Drill down to V$SESSION_WAIT ??? Steve Orr Bozeman, Montana -Original Message- Sent: Thursday, May 09, 2002 5:23 PM To: Multiple recipients of list ORACLE-L Importance: High In general There are two problems in using the top five waits out of statspack: it reports idle waits; no matter how well-tuned your database there will always be a top five. The numbers presented show total time-waited in csecs for the time period. As Jared said we don't know the time period. We don't know the average wait time. I have learned some rudimentary gnuplot skills. Each morning I produce graphs of what went on the in the databases the previous day on and hour by hour basis. If something is really askew I break the hour down into ten minute blocks. This helps me to better recognize patterns of database usage. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve INET: [EMAIL PROTECTED] 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: [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: WAITS
Check out the statspack viewer tool at http://www.geocities.com/alexdabr/ mkb mkb125@yahooTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] .comcc: Sent by: Subject: RE: WAITS root@fatcity. com May 13, 2002 10:03 AM Please respond to ORACLE-L That's neat. I working on HTML/GUI interface to statspack. I think I've got most of the thing figured out. Right now, I'm able to display phys read/write IO directly from one of the statspack tables every hour displayed in graphincal format on a web page. Working on other reports as well. Would love to be able to pool resources here and bounce off some ideas. mkb --- Orr, Steve [EMAIL PROTECTED] wrote: Each morning I produce graphs... I think this is key. Having historical data graphically presented helps to establish the norm and when there may be performance issues to investigate. This follows step 2 of Gaja's Oracle Performance Tuning 101 Methodology which says, Measure and document current performance. To do this I created a DBA monitoring HTML display tool which gets data from V$SYSSTAT and V$SYSTEM_EVENT once a minute, stores it in a round robin database and displays it with RRDTool. I've accumulated 2 months of this data and it's amazing how lightweight it is. With graphs it's easy to see when something's amiss. We capture expensive SQL via StatsPack every 15 minutes and I have correlated a spike on a graph to specific SQL executed 2 hours earlier. Now I'm trying to decide on my next enhancement: 1) HTML/GUI interface to StatsPack data or; 2) Drill down to V$SESSION_WAIT ??? Steve Orr Bozeman, Montana -Original Message- Sent: Thursday, May 09, 2002 5:23 PM To: Multiple recipients of list ORACLE-L Importance: High In general There are two problems in using the top five waits out of statspack: it reports idle waits; no matter how well-tuned your database there will always be a top five. The numbers presented show total time-waited in csecs for the time period. As Jared said we don't know the time period. We don't know the average wait time. I have learned some rudimentary gnuplot skills. Each morning I produce graphs of what went on the in the databases the previous day on and hour by hour basis. If something is really askew I break the hour down into ten minute blocks. This helps me to better recognize patterns of database usage. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve INET: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] 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: [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: WAITS
Thanks Rajesh. Looks to be a very interesting tool. mkb --- [EMAIL PROTECTED] wrote: Check out the statspack viewer tool at http://www.geocities.com/alexdabr/ mkb mkb125@yahooTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] .comcc: Sent by: Subject: RE: WAITS root@fatcity. com May 13, 2002 10:03 AM Please respond to ORACLE-L That's neat. I working on HTML/GUI interface to statspack. I think I've got most of the thing figured out. Right now, I'm able to display phys read/write IO directly from one of the statspack tables every hour displayed in graphincal format on a web page. Working on other reports as well. Would love to be able to pool resources here and bounce off some ideas. mkb --- Orr, Steve [EMAIL PROTECTED] wrote: Each morning I produce graphs... I think this is key. Having historical data graphically presented helps to establish the norm and when there may be performance issues to investigate. This follows step 2 of Gaja's Oracle Performance Tuning 101 Methodology which says, Measure and document current performance. To do this I created a DBA monitoring HTML display tool which gets data from V$SYSSTAT and V$SYSTEM_EVENT once a minute, stores it in a round robin database and displays it with RRDTool. I've accumulated 2 months of this data and it's amazing how lightweight it is. With graphs it's easy to see when something's amiss. We capture expensive SQL via StatsPack every 15 minutes and I have correlated a spike on a graph to specific SQL executed 2 hours earlier. Now I'm trying to decide on my next enhancement: 1) HTML/GUI interface to StatsPack data or; 2) Drill down to V$SESSION_WAIT ??? Steve Orr Bozeman, Montana -Original Message- Sent: Thursday, May 09, 2002 5:23 PM To: Multiple recipients of list ORACLE-L Importance: High In general There are two problems in using the top five waits out of statspack: it reports idle waits; no matter how well-tuned your database there will always be a top five. The numbers presented show total time-waited in csecs for the time period. As Jared said we don't know the time period. We don't know the average wait time. I have learned some rudimentary gnuplot skills. Each morning I produce graphs of what went on the in the databases the previous day on and hour by hour basis. If something is really askew I break the hour down into ten minute blocks. This helps me to better recognize patterns of database usage. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve INET: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru
Re: WAITS
On Thu, May 09, 2002 at 03:23:24PM -0800, MacGregor, Ian A. wrote: I have learned some rudimentary gnuplot skills. Each morning I produce graphs of what went on the in the databases the previous day on and hour by hour basis. If something is really askew I break the hour down into ten minute blocks. This helps me to better recognize patterns of database usage. -- Can you talk about your data collection methodology? === Ray Stell [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ray Stell INET: [EMAIL PROTECTED] 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: [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: WAITS
Each morning I produce graphs... I think this is key. Having historical data graphically presented helps to establish the norm and when there may be performance issues to investigate. This follows step 2 of Gaja's Oracle Performance Tuning 101 Methodology which says, Measure and document current performance. To do this I created a DBA monitoring HTML display tool which gets data from V$SYSSTAT and V$SYSTEM_EVENT once a minute, stores it in a round robin database and displays it with RRDTool. I've accumulated 2 months of this data and it's amazing how lightweight it is. With graphs it's easy to see when something's amiss. We capture expensive SQL via StatsPack every 15 minutes and I have correlated a spike on a graph to specific SQL executed 2 hours earlier. Now I'm trying to decide on my next enhancement: 1) HTML/GUI interface to StatsPack data or; 2) Drill down to V$SESSION_WAIT ??? Steve Orr Bozeman, Montana -Original Message- Sent: Thursday, May 09, 2002 5:23 PM To: Multiple recipients of list ORACLE-L Importance: High In general There are two problems in using the top five waits out of statspack: it reports idle waits; no matter how well-tuned your database there will always be a top five. The numbers presented show total time-waited in csecs for the time period. As Jared said we don't know the time period. We don't know the average wait time. I have learned some rudimentary gnuplot skills. Each morning I produce graphs of what went on the in the databases the previous day on and hour by hour basis. If something is really askew I break the hour down into ten minute blocks. This helps me to better recognize patterns of database usage. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve INET: [EMAIL PROTECTED] 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: [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: WAITS
Seema - The SQL*Net more data to client means Oracle is waiting for the client to send something back so Oracle can do something more. I looked single-task message up on google and received: single-task message When running single task, this event indicates that the session waits for the client side of the executable. Wait Time: Total elapsed time that this session spent in the user application. Parameters: none So, these might be a problem, or not, depending on other factors in your system. For example, we have one program that runs in under a second on the local network, but takes more than a minute over the wide-area network. That would be an example where client waits are a problem with the application architecture. Do you have a problem, or are you just doing some standard analysis? Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, May 09, 2002 1:19 PM To: Multiple recipients of list ORACLE-L Hi I checked wait in my database and found the following are top 5 waits. Event Waits single-task message 1,411,047 log file sync 326,222 SQL*Net more data to client 7,363,877 control file parallel write27,674 latch free107,486 Any suggestions from group.what to do? Thx -Seema _ Chat with friends online, try MSN Messenger: http://messenger.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh INET: [EMAIL PROTECTED] 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: [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.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] 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: [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: WAITS
There is a vital piece of information missing, namely how long was the accumulated wait time for each wait, and over what period of time? Jared Seema Singh [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 05/09/2002 11:18 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:WAITS Hi I checked wait in my database and found the following are top 5 waits. Event Waits single-task message 1,411,047 log file sync 326,222 SQL*Net more data to client 7,363,877 control file parallel write27,674 latch free107,486 Any suggestions from group.what to do? Thx -Seema _ Chat with friends online, try MSN Messenger: http://messenger.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh INET: [EMAIL PROTECTED] 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: [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.com -- Author: INET: [EMAIL PROTECTED] 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: [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: WAITS
what does netstat -i tell you, on both client and server. On Thu, May 09, 2002 at 10:18:31AM -0800, Seema Singh wrote: Hi I checked wait in my database and found the following are top 5 waits. Event Waits single-task message 1,411,047 log file sync 326,222 SQL*Net more data to client 7,363,877 control file parallel write27,674 latch free107,486 Any suggestions from group.what to do? Thx -Seema _ Chat with friends online, try MSN Messenger: http://messenger.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh INET: [EMAIL PROTECTED] 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: [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). -- === Ray Stell [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ray Stell INET: [EMAIL PROTECTED] 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: [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: WAITS
Well why do you want to do something ? To look busy ? Or are there complaints about response times or throughput problems ? Anjo. Seema Singh wrote: Hi I checked wait in my database and found the following are top 5 waits. Event Waits single-task message 1,411,047 log file sync 326,222 SQL*Net more data to client 7,363,877 control file parallel write27,674 latch free107,486 Any suggestions from group.what to do? Thx -Seema _ Chat with friends online, try MSN Messenger: http://messenger.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh INET: [EMAIL PROTECTED] 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: [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.com -- Author: Anjo Kolk INET: [EMAIL PROTECTED] 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: [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: WAITS
In general There are two problems in using the top five waits out of statspack: it reports idle waits; no matter how well-tuned your database there will always be a top five. The numbers presented show total time-waited in csecs for the time period. As Jared said we don't know the time period. We don't know the average wait time. I have learned some rudimentary gnuplot skills. Each morning I produce graphs of what went on the in the databases the previous day on and hour by hour basis. If something is really askew I break the hour down into ten minute blocks. This helps me to better recognize patterns of database usage. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Thursday, May 09, 2002 11:49 AM To: Multiple recipients of list ORACLE-L There is a vital piece of information missing, namely how long was the accumulated wait time for each wait, and over what period of time? Jared Seema Singh [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 05/09/2002 11:18 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:WAITS Hi I checked wait in my database and found the following are top 5 waits. Event Waits single-task message 1,411,047 log file sync 326,222 SQL*Net more data to client 7,363,877 control file parallel write27,674 latch free107,486 Any suggestions from group.what to do? Thx -Seema _ Chat with friends online, try MSN Messenger: http://messenger.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh INET: [EMAIL PROTECTED] 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: [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.com -- Author: INET: [EMAIL PROTECTED] 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: [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.com -- Author: MacGregor, Ian A. INET: [EMAIL PROTECTED] 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: [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: waits on sequential scans - how did i solve it
Jack, I was going to suggest, what you did, but then I noticed the version Rahul was using. Igor Neyman, OCP DBA Perceptron, Inc. (734)414-4627 [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, July 24, 2001 5:58 PM Igor, How right you are! I answered without looking back at Rahul's original message. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- Sent: Tuesday, July 24, 2001 10:26 AM To: Multiple recipients of list ORACLE-L I don't think, 7.3.2 supports Index-Organized tables. Igor Neyman, OCP DBA Perceptron, Inc. (734)414-4627 [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, July 24, 2001 10:36 AM Rahul, If I'd known that there were only 8 columns in the table, I'd have included the recommendation to investigate an Index-Organized Table. That is where the table IS the index and the index IS the table. This saves disc space and cuts I/O in half for DML on the table, since a separate index is not maintained. Check out the docs on IOTs. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- Sent: Tuesday, July 24, 2001 8:53 AM To: Multiple recipients of list ORACLE-L list, based on the recommendation of posters (Jack) , i re-created the index with all the columns of a table ! (all 8 of them) analyzed the table/index and now all the queries are satisfied off an indexed range scan.. i also put the indexes on raw devices. regards -- From: Jack C. Applewhite[SMTP:[EMAIL PROTECTED]] Reply To: [EMAIL PROTECTED] Sent: Tuesday, July 17, 2001 8:25 PM To: Multiple recipients of list ORACLE-L Subject: RE: how to improve sequential scans ? Rahul, Could this table be partitioned and the partitions spread across multiple disks? Could the index be partitioned as well? The concept here is, of course, divide and conquer. Could a column or two (or three) be added to the index to satisfy the query without having to hit the table? How frequently are these literal queries being issued? Are they shredding your shared pool and chewing up CPU by making Oracle do extra work in shared pool memory management? Are there aggregation (vs aggravation g) functions or order bys in the queries that might be causing sorts to disk? If aggregation, could you use materialized views to satisfy the queries? ...just a few ideas. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- Sent: Tuesday, July 17, 2001 7:36 AM To: Multiple recipients of list ORACLE-L list (AIX, 7.3.2) 5 clients are shooting the same sql to read data from an 18 million rows table. each time the sql uses a different literal value in the where clause... no bind variables. I CANNOT TOUCH THE APPLICATION, and have been given the task to re-configure the DB to increase performance. i have moved the table and it;s associated index to separate disks. and iostat show that only that only those two disks are being read. the session wait show that all the times the sessions are waiting on db file sequential read the db file being sequentially read in the above sessiion is the TABLE from which all the sid's are reading the table is analyzed and the sql's issued use the index. how can i further tune this config. ? TIA Rahul PS: my next step is to put the files on raw disks. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite INET: [EMAIL PROTECTED] 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: [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.com -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051
RE: waits on sequential scans - how did i solve it
Rahul, If I'd known that there were only 8 columns in the table, I'd have included the recommendation to investigate an Index-Organized Table. That is where the table IS the index and the index IS the table. This saves disc space and cuts I/O in half for DML on the table, since a separate index is not maintained. Check out the docs on IOTs. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- Sent: Tuesday, July 24, 2001 8:53 AM To: Multiple recipients of list ORACLE-L list, based on the recommendation of posters (Jack) , i re-created the index with all the columns of a table ! (all 8 of them) analyzed the table/index and now all the queries are satisfied off an indexed range scan.. i also put the indexes on raw devices. regards -- From: Jack C. Applewhite[SMTP:[EMAIL PROTECTED]] Reply To: [EMAIL PROTECTED] Sent: Tuesday, July 17, 2001 8:25 PM To: Multiple recipients of list ORACLE-L Subject: RE: how to improve sequential scans ? Rahul, Could this table be partitioned and the partitions spread across multiple disks? Could the index be partitioned as well? The concept here is, of course, divide and conquer. Could a column or two (or three) be added to the index to satisfy the query without having to hit the table? How frequently are these literal queries being issued? Are they shredding your shared pool and chewing up CPU by making Oracle do extra work in shared pool memory management? Are there aggregation (vs aggravation g) functions or order bys in the queries that might be causing sorts to disk? If aggregation, could you use materialized views to satisfy the queries? ...just a few ideas. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- Sent: Tuesday, July 17, 2001 7:36 AM To: Multiple recipients of list ORACLE-L list (AIX, 7.3.2) 5 clients are shooting the same sql to read data from an 18 million rows table. each time the sql uses a different literal value in the where clause... no bind variables. I CANNOT TOUCH THE APPLICATION, and have been given the task to re-configure the DB to increase performance. i have moved the table and it;s associated index to separate disks. and iostat show that only that only those two disks are being read. the session wait show that all the times the sessions are waiting on db file sequential read the db file being sequentially read in the above sessiion is the TABLE from which all the sid's are reading the table is analyzed and the sql's issued use the index. how can i further tune this config. ? TIA Rahul PS: my next step is to put the files on raw disks. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite INET: [EMAIL PROTECTED] 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: [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: waits on sequential scans - how did i solve it
Title: RE: waits on sequential scans - how did i solve it Is this table/index now suitable to be a IOT, it will certainly save some space on an 18M row table John -Original Message- From: Rahul [mailto:[EMAIL PROTECTED]] Sent: 24 July 01 14:53 To: Multiple recipients of list ORACLE-L Subject: waits on sequential scans - how did i solve it list, based on the recommendation of posters (Jack) , i re-created the index with all the columns of a table ! (all 8 of them) analyzed the table/index and now all the queries are satisfied off an indexed range scan.. i also put the indexes on raw devices. regards -- From: Jack C. Applewhite[SMTP:[EMAIL PROTECTED]] Reply To: [EMAIL PROTECTED] Sent: Tuesday, July 17, 2001 8:25 PM To: Multiple recipients of list ORACLE-L Subject: RE: how to improve sequential scans ? Rahul, Could this table be partitioned and the partitions spread across multiple disks? Could the index be partitioned as well? The concept here is, of course, divide and conquer. Could a column or two (or three) be added to the index to satisfy the query without having to hit the table? How frequently are these literal queries being issued? Are they shredding your shared pool and chewing up CPU by making Oracle do extra work in shared pool memory management? Are there aggregation (vs aggravation g) functions or order bys in the queries that might be causing sorts to disk? If aggregation, could you use materialized views to satisfy the queries? ...just a few ideas. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- Sent: Tuesday, July 17, 2001 7:36 AM To: Multiple recipients of list ORACLE-L list (AIX, 7.3.2) 5 clients are shooting the same sql to read data from an 18 million rows table. each time the sql uses a different literal value in the where clause... no bind variables. I CANNOT TOUCH THE APPLICATION, and have been given the task to re-configure the DB to increase performance. i have moved the table and it;s associated index to separate disks. and iostat show that only that only those two disks are being read. the session wait show that all the times the sessions are waiting on db file sequential read the db file being sequentially read in the above sessiion is the TABLE from which all the sid's are reading the table is analyzed and the sql's issued use the index. how can i further tune this config. ? TIA Rahul PS: my next step is to put the files on raw disks. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite INET: [EMAIL PROTECTED] 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: [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.com -- Author: Rahul INET: [EMAIL PROTECTED] 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: [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). ** This email and any attachments may be confidential and the subject of legal professional privilege. Any disclosure, use, storage or copying of this email without the consent of the sender is strictly prohibited. Please notify the sender immediately if you are not the intended recipient and then delete the email from your inbox and do not disclose the contents to another person, use, copy or store the information in any medium. **
Re: waits on sequential scans - how did i solve it
I don't think, 7.3.2 supports Index-Organized tables. Igor Neyman, OCP DBA Perceptron, Inc. (734)414-4627 [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, July 24, 2001 10:36 AM Rahul, If I'd known that there were only 8 columns in the table, I'd have included the recommendation to investigate an Index-Organized Table. That is where the table IS the index and the index IS the table. This saves disc space and cuts I/O in half for DML on the table, since a separate index is not maintained. Check out the docs on IOTs. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- Sent: Tuesday, July 24, 2001 8:53 AM To: Multiple recipients of list ORACLE-L list, based on the recommendation of posters (Jack) , i re-created the index with all the columns of a table ! (all 8 of them) analyzed the table/index and now all the queries are satisfied off an indexed range scan.. i also put the indexes on raw devices. regards -- From: Jack C. Applewhite[SMTP:[EMAIL PROTECTED]] Reply To: [EMAIL PROTECTED] Sent: Tuesday, July 17, 2001 8:25 PM To: Multiple recipients of list ORACLE-L Subject: RE: how to improve sequential scans ? Rahul, Could this table be partitioned and the partitions spread across multiple disks? Could the index be partitioned as well? The concept here is, of course, divide and conquer. Could a column or two (or three) be added to the index to satisfy the query without having to hit the table? How frequently are these literal queries being issued? Are they shredding your shared pool and chewing up CPU by making Oracle do extra work in shared pool memory management? Are there aggregation (vs aggravation g) functions or order bys in the queries that might be causing sorts to disk? If aggregation, could you use materialized views to satisfy the queries? ...just a few ideas. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- Sent: Tuesday, July 17, 2001 7:36 AM To: Multiple recipients of list ORACLE-L list (AIX, 7.3.2) 5 clients are shooting the same sql to read data from an 18 million rows table. each time the sql uses a different literal value in the where clause... no bind variables. I CANNOT TOUCH THE APPLICATION, and have been given the task to re-configure the DB to increase performance. i have moved the table and it;s associated index to separate disks. and iostat show that only that only those two disks are being read. the session wait show that all the times the sessions are waiting on db file sequential read the db file being sequentially read in the above sessiion is the TABLE from which all the sid's are reading the table is analyzed and the sql's issued use the index. how can i further tune this config. ? TIA Rahul PS: my next step is to put the files on raw disks. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite INET: [EMAIL PROTECTED] 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: [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.com -- Author: Igor Neyman INET: [EMAIL PROTECTED] 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: [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: waits on sequential scans - how did i solve it
I was going to say the same thing until I saw what version of Oracle he was running. Its a Oracle8 and up feature so Rahul is SOL. -Original Message- Sent: Tuesday, July 24, 2001 7:36 AM To: Multiple recipients of list ORACLE-L Rahul, If I'd known that there were only 8 columns in the table, I'd have included the recommendation to investigate an Index-Organized Table. That is where the table IS the index and the index IS the table. This saves disc space and cuts I/O in half for DML on the table, since a separate index is not maintained. Check out the docs on IOTs. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- Sent: Tuesday, July 24, 2001 8:53 AM To: Multiple recipients of list ORACLE-L list, based on the recommendation of posters (Jack) , i re-created the index with all the columns of a table ! (all 8 of them) analyzed the table/index and now all the queries are satisfied off an indexed range scan.. i also put the indexes on raw devices. regards -- From: Jack C. Applewhite[SMTP:[EMAIL PROTECTED]] Reply To: [EMAIL PROTECTED] Sent: Tuesday, July 17, 2001 8:25 PM To: Multiple recipients of list ORACLE-L Subject: RE: how to improve sequential scans ? Rahul, Could this table be partitioned and the partitions spread across multiple disks? Could the index be partitioned as well? The concept here is, of course, divide and conquer. Could a column or two (or three) be added to the index to satisfy the query without having to hit the table? How frequently are these literal queries being issued? Are they shredding your shared pool and chewing up CPU by making Oracle do extra work in shared pool memory management? Are there aggregation (vs aggravation g) functions or order bys in the queries that might be causing sorts to disk? If aggregation, could you use materialized views to satisfy the queries? ...just a few ideas. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- Sent: Tuesday, July 17, 2001 7:36 AM To: Multiple recipients of list ORACLE-L list (AIX, 7.3.2) 5 clients are shooting the same sql to read data from an 18 million rows table. each time the sql uses a different literal value in the where clause... no bind variables. I CANNOT TOUCH THE APPLICATION, and have been given the task to re-configure the DB to increase performance. i have moved the table and it;s associated index to separate disks. and iostat show that only that only those two disks are being read. the session wait show that all the times the sessions are waiting on db file sequential read the db file being sequentially read in the above sessiion is the TABLE from which all the sid's are reading the table is analyzed and the sql's issued use the index. how can i further tune this config. ? TIA Rahul PS: my next step is to put the files on raw disks. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite INET: [EMAIL PROTECTED] 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: [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.com -- Author: Kimberly Smith INET: [EMAIL PROTECTED] 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: [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: Waits on cache buffers chains latch
Hi Bruce, Not sure whether you got a response on this, so here is one. First of all, I am hoping that you have some kind of performance problem on your hand, that you are trying to solve and that led you to checking out the wait events in your database. If so, great. If not our discussion is purely theoritical, and I do sincerely hope that you don't look at percentages once and conclude that you have a problem. I am assuming that the latch free wait event for the cache buffers chains latch occurs frequently and shows up in V$SESSION_WAIT. Contention or waits for the cache buffers chains latch usually indicates that there is too much logical I/O that is being performed in your environment. Contrary to common knowledge, logical I/O is not 3 orders of magnitude faster than physical I/O in an Oracle environment, as there is a lot more that goes on when Oracle performs a logical I/O, than just reading blocks from memory. So reducing logical I/O should also be one of the primary efforts one takes in tuning efforts. The cache buffers chains latch is a scarce resource that needs to be acquired for performing logical I/O and can cause serious contention (which you are probably experiencing). This could be caused because of the use of GTT with tablespace type of Permanent, as the blocks for temporary segment need to be processed via the database buffer cache, when the size of the data processed by the GTT (at the transaction or the session level) exceeds the size of SORT_AREA_SIZE. Your system may also be experiencing severe contention for the ST enqueue as a result of you changing your temporary tablespace to type permanent, as there could be constant allocation and deallocation of temp segments, which requires the ST enqueue. One solution to your problem is to upgrade to 8.1.7 (if possible and hoping that the bug is fixed) and flip your temporary tablespace back to type temporary. Another option is to increase the size of SORT_AREA_SIZE, so that you reduce the frequency with which your sessions generate temporary segments. You also should look into the option of creating a locally managed temporary tablespace with the CREATE TEMPORARY TABLESPACE command which again will alleviate the contention for blocks in the database buffer cache. Hope that helps, Gaja --- Reardon, Bruce (CALBBAY) [EMAIL PROTECTED] wrote: Hi, Our database is experiencing a very large number of waits on the cache buffers chains latch. I know the child latch# is 242 (details below). We are on Oracle 8.1.5.1.1 on NT4. The problems seem to have started appearing after starting large scale use of Global Temporary tables (GTT). Our temp tablespace was of type temporary but a suggestion from Oracle was to change this to Permanent (due to GTT related bugs in 815). This was done and the database was restarted but the waits are still occurring. What else should I try to look for? Thanks, Bruce Reardon mailto:[EMAIL PROTECTED] Our top waits in general are: SQL @system_times EVENT TIME_WAITED --- PX Idle Wait 581928737 PX Deq: Execution Msg 278990599 CPU used by this session 3812597 latch free 202949 db file sequential read 200926 SQL*Net more data to client 73342 db file scattered read 59797 enqueue 19755 Using Ixora's latch_sleeps script I get the following output: SQL @latch_sleeps LATCH TYPE IMPACT SLEEP RATE WAITS HOLDING LEVEL - --- -- - - cache buffers chains 12642171 0.39% 804 1 library cache7122 0.00% 48564 5 Checkpoint queue latch 7034 0.03% 24370 7 session allocation346 0.04% 762 5 parallel query stats 198 4.31% 0 8 messages 113 0.01% 1499 8 shared pool88 0.00% 320 7 cache buffers lru chain41 0.00% 20765 3 process queue reference14 0.00% 276 4 query server freelists 2 0.01%10 6 multiblock read objects 1 0.00% 6 3 redo writing
Re: Waits on cache buffers chains latch
Hi Gaja, I notice that you have advised Bruce to increase SORT_AREA_SIZE to gain proformance lost due to frequent allocation of temp segments. I too belived in the theory that disk sorts are always faster than memory sorts untill I stumbled on my own problem which leaves me a little confused. I had been noticing that direct path read amd write were always among top 5 wait events in my data warehouse. This warehouse loads data during weekends and during week days all data and index tablespaces are read only. We have four or five large fact tables ( 30 to 45GB) on which some users do some heavy sorting. So in order to improve sorting I wanted to make my temp tablespace datafiles QUICK I/O. But veritas advised against it. So I made the temp tablespace datafiles, tempfiles with local management. Though this helped finding that we had SORT_ARE_SIZE = 1M and SORT_MULTIBLOCK_READ_COUNT = 2. I tested a few queries with a larger SORT_AREA_SIZE (84M) and SOR_MULTIBLOCK_READ_COUNT = 4. I was assumming this should help. But all queries ran slower with larger SORT_AREA_SIZE. Could you throw some light on why this happens? --- Gaja Krishna Vaidyanatha [EMAIL PROTECTED] wrote: Hi Bruce, Not sure whether you got a response on this, so here is one. First of all, I am hoping that you have some kind of performance problem on your hand, that you are trying to solve and that led you to checking out the wait events in your database. If so, great. If not our discussion is purely theoritical, and I do sincerely hope that you don't look at percentages once and conclude that you have a problem. I am assuming that the latch free wait event for the cache buffers chains latch occurs frequently and shows up in V$SESSION_WAIT. Contention or waits for the cache buffers chains latch usually indicates that there is too much logical I/O that is being performed in your environment. Contrary to common knowledge, logical I/O is not 3 orders of magnitude faster than physical I/O in an Oracle environment, as there is a lot more that goes on when Oracle performs a logical I/O, than just reading blocks from memory. So reducing logical I/O should also be one of the primary efforts one takes in tuning efforts. The cache buffers chains latch is a scarce resource that needs to be acquired for performing logical I/O and can cause serious contention (which you are probably experiencing). This could be caused because of the use of GTT with tablespace type of Permanent, as the blocks for temporary segment need to be processed via the database buffer cache, when the size of the data processed by the GTT (at the transaction or the session level) exceeds the size of SORT_AREA_SIZE. Your system may also be experiencing severe contention for the ST enqueue as a result of you changing your temporary tablespace to type permanent, as there could be constant allocation and deallocation of temp segments, which requires the ST enqueue. One solution to your problem is to upgrade to 8.1.7 (if possible and hoping that the bug is fixed) and flip your temporary tablespace back to type temporary. Another option is to increase the size of SORT_AREA_SIZE, so that you reduce the frequency with which your sessions generate temporary segments. You also should look into the option of creating a locally managed temporary tablespace with the CREATE TEMPORARY TABLESPACE command which again will alleviate the contention for blocks in the database buffer cache. Hope that helps, Gaja --- Reardon, Bruce (CALBBAY) [EMAIL PROTECTED] wrote: Hi, Our database is experiencing a very large number of waits on the cache buffers chains latch. I know the child latch# is 242 (details below). We are on Oracle 8.1.5.1.1 on NT4. The problems seem to have started appearing after starting large scale use of Global Temporary tables (GTT). Our temp tablespace was of type temporary but a suggestion from Oracle was to change this to Permanent (due to GTT related bugs in 815). This was done and the database was restarted but the waits are still occurring. What else should I try to look for? Thanks, Bruce Reardon mailto:[EMAIL PROTECTED] Our top waits in general are: SQL @system_times EVENT TIME_WAITED --- PX Idle Wait 581928737 PX Deq: Execution Msg 278990599 CPU used by this session 3812597 latch free 202949 db file sequential read 200926 SQL*Net more data to client 73342 db
Re: Waits on cache buffers chains latch
Hi Bruce You need to find what blocks are hanging from the particular latch. You could get this information by joining x$bh and v$session_wait tables while the sessions are waiting for this latch. select distinct ts#, file#, dbarfil,dbablk from x$bh where hladdr in (select p1raw from v$session_wait where to_char(p2)=66 and lower(event) like 'latch free%' ) / (or) You could use the other method since you know the child latch#. select distinct ts#, file#, dbarfil,dbablk from x$bh where hladdr in (select addr from v$latch_children where latch#=66 and child#=242) From thie file#, dbarilf and dbablk you could find the segment that the latch is protecting. Please note here 66 is the latch# for cache buffers chains latch and check that is correct from v$latch in your environment. I would check the SQL statements also and make sure that they are not doing any full table scans. If you seen any waits for 'Db file scattered read' events in the v$session_wait then that could cause secondary cache buffers chains latch contention. Thanks Riyaj Re-yas Shamsudeen Certified Oracle DBA i2 technologies www.i2.com Reardon, Bruce (CALBBAY) [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 06/21/01 01:15 AM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Waits on cache buffers chains latch Hi, Our database is experiencing a very large number of waits on the cache buffers chains latch. I know the child latch# is 242 (details below). We are on Oracle 8.1.5.1.1 on NT4. The problems seem to have started appearing after starting large scale use of Global Temporary tables (GTT). Our temp tablespace was of type temporary but a suggestion from Oracle was to change this to Permanent (due to GTT related bugs in 815). This was done and the database was restarted but the waits are still occurring. What else should I try to look for? Thanks, Bruce Reardon mailto:[EMAIL PROTECTED] Our top waits in general are: SQL @system_times EVENT TIME_WAITED --- PX Idle Wait581928737 PX Deq: Execution Msg 278990599 CPU used by this session 3812597 latch free 202949 db file sequential read200926 SQL*Net more data to client 73342 db file scattered read 59797 enqueue19755 Using Ixora's latch_sleeps script I get the following output: SQL @latch_sleeps LATCH TYPE IMPACT SLEEP RATE WAITS HOLDING LEVEL - --- -- - - cache buffers chains 12642171 0.39% 804 1 library cache7122 0.00% 48564 5 Checkpoint queue latch7034 0.03% 24370 7 session allocation 346 0.04% 762 5 parallel query stats 198 4.31% 0 8 messages 113 0.01% 1499 8 shared pool 88 0.00% 320 7 cache buffers lru chain41 0.00% 20765 3 process queue reference14 0.00% 276 4 query server freelists 2 0.01% 10 6 multiblock read objects 1 0.00% 6 3 redo writing 1 0.00% 165 5 parallel query alloc buffer 1 0.00% 206 6 13 rows selected. and from latch_gets.sql LATCH TYPE SIMPLE GETS SPIN GETS SLEEP GETS -- -- cache buffers chains 3149562975 97.08% 82063196 2.53% 12562449 0.39% Virtually all of these waits are on the 1 child latch: LATCH TYPESLEEPS LATCHES -- cache buffers chains 0 951 1 to 2 61 3 to 5 5 10 to 13 3 131 1 652 1 4802 1 12596539 1 The child latch in question is child# 242 (from querying v$latch_children). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) INET: [EMAIL PROTECTED] 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: [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: Waits on cache buffers chains latch
Hi Johnson, I think you may be reading just a tad bit too much into what I wrote regarding the SORT_AREA_SIZE increase recommendation. The rationale for that was not based on disk sorts are always faster than memory sorts or vice versa. It was based on the fact that data for global temporary tables (GTT) are stored in the sort area of a session's PGA, and when the sort area becomes full, the session starts writing the data for the GTTs to temporary segments (or extents of an already allocated TRUE temp segment using the sort extent pool algorithm). If the TEMP tablespace is of type temporary, then the data blocks in the database buffer cache are NOT used. If the TEMP tablespace is of type permanent, then the temp segments (for sorts and for GTT data larger than SORT_AREA_SIZE) are written to blocks in the database buffer cache, which in-turn is written to the files of the temporary tablespace by DBWR (Like how TEMP tablespaces used to work prior to 7.3 when they were of type permanent). If the temporary tablespace is of type permanent then the temp segments (that store the data for the GTTs) are constantly allocated and deallocated (if the data in the GTTs are retained only at the transaction-level or if sessions live only for a very short duration). That can cause contention for the ST enqueue, plus as mentioned before, it also uses blocks in the database buffer cache. If the same set of blocks are accessed repeatedly (constantly re-reading the data from the GTT within the same session or transaction as the case may be), then the cache buffers chains latch will be required to perform logical I/O on the said blocks. In that case, setting a larger SORT_AREA_SIZE will help in storing more GTT data in the PGA, rather than the Database Buffer Cache. A key factor to note about GTTs is that it is local to a user's session or transaction and is stored in the sort area of the PGA, so long as it can fit there. Anything that does not fit goes to the user's temporary tablespace. Obviously, while engaging in any performance tuning engagement, we have to track the offending SQL that is causing the problem and this can be done by tracing your way back from V$SESSION_WAIT to V$SESSION to V$SQLAREA or V$SQL, to determine what is causing the problem. Regarding the strange behavior of the queries on your system (after increasing SORT_AREA_SIZE), one possibility could be that your system could have been experiencing memory starvation. The blindspot that gets most people while setting large sort areas is that for queries that are accessing tables or indexes with a degree of parallelism of n, the amount of memory that could be allocated for those queries is (n x SORT_AREA_SIZE). Now if a bunch of users(y) are executing these queries and you have a large enough pool of PARALLEL_MAX_SERVERS, then memory consumption becomes a product of (y x n x SORT_AREA_SIZE). To make things interesting, if a bunch of tables(z) is referenced in the query and they all have a degree of parallelism 1, then the memory consumption jumps to (z x y x n x SORT_AREA_SIZE). You can see where I am going with this. In closing, Oracle recommends setting SORT_MULTIBLOCK_READ_COUNT to 2 and I am still trying to find out why is it 2?. I will post a message when I determine the rationale behind that recommendation. Until then, you may be better served by leaving that at 2. Hope that clarifies things a bit, Gaja --- Johnson Poovathummoottil [EMAIL PROTECTED] wrote: Hi Gaja, I notice that you have advised Bruce to increase SORT_AREA_SIZE to gain proformance lost due to frequent allocation of temp segments. I too belived in the theory that disk sorts are always faster than memory sorts untill I stumbled on my own problem which leaves me a little confused. I had been noticing that direct path read amd write were always among top 5 wait events in my data warehouse. This warehouse loads data during weekends and during week days all data and index tablespaces are read only. We have four or five large fact tables ( 30 to 45GB) on which some users do some heavy sorting. So in order to improve sorting I wanted to make my temp tablespace datafiles QUICK I/O. But veritas advised against it. So I made the temp tablespace datafiles, tempfiles with local management. Though this helped finding that we had SORT_ARE_SIZE = 1M and SORT_MULTIBLOCK_READ_COUNT = 2. I tested a few queries with a larger SORT_AREA_SIZE (84M) and SOR_MULTIBLOCK_READ_COUNT = 4. I was assumming this should help. But all queries ran slower with larger SORT_AREA_SIZE. Could you throw some light on why this happens? --- Gaja Krishna Vaidyanatha [EMAIL PROTECTED] wrote: Hi Bruce, Not sure whether you got a response on this, so here is one. First of all, I am hoping that you have some kind of performance problem on your hand, that you are trying to solve and that led you to checking out the wait events in your database. If so,
RE: Waits on cache buffers chains latch
Hi Gaja and thanks also to Riyaj, If I didn't make it clear before we were having a major performance problem. A package call that should (ie normally / used to) take less than 1 second starting taking between 20 - 60 seconds. I appreciate your comments on logical IO and will look into that and I also used Riyaj's SQL to look at the segments protected by that latch. The solution was not reached systematically but rather another DBA in our company suggested gathering stats at a 40% estimate level (we normally gather stats via dbms_stats daily at 30% estimate). After doing the 40% estimate, the transaction times dropped back down to sub-second. So what changed - I imagine the CBO is choosing a different execution plan but this hasn't yet been investigated fully. For your info - we hope to upgrade to 817 in the next 2 weeks and will then be going to using stale statistics monitoring. Regards, Bruce -Original Message- Sent: Friday, 22 June 2001 4:36 Hi Bruce, Not sure whether you got a response on this, so here is one. First of all, I am hoping that you have some kind of performance problem on your hand, that you are trying to solve and that led you to checking out the wait events in your database. If so, great. If not our discussion is purely theoritical, and I do sincerely hope that you don't look at percentages once and conclude that you have a problem. I am assuming that the latch free wait event for the cache buffers chains latch occurs frequently and shows up in V$SESSION_WAIT. Contention or waits for the cache buffers chains latch usually indicates that there is too much logical I/O that is being performed in your environment. Contrary to common knowledge, logical I/O is not 3 orders of magnitude faster than physical I/O in an Oracle environment, as there is a lot more that goes on when Oracle performs a logical I/O, than just reading blocks from memory. So reducing logical I/O should also be one of the primary efforts one takes in tuning efforts. The cache buffers chains latch is a scarce resource that needs to be acquired for performing logical I/O and can cause serious contention (which you are probably experiencing). This could be caused because of the use of GTT with tablespace type of Permanent, as the blocks for temporary segment need to be processed via the database buffer cache, when the size of the data processed by the GTT (at the transaction or the session level) exceeds the size of SORT_AREA_SIZE. Your system may also be experiencing severe contention for the ST enqueue as a result of you changing your temporary tablespace to type permanent, as there could be constant allocation and deallocation of temp segments, which requires the ST enqueue. One solution to your problem is to upgrade to 8.1.7 (if possible and hoping that the bug is fixed) and flip your temporary tablespace back to type temporary. Another option is to increase the size of SORT_AREA_SIZE, so that you reduce the frequency with which your sessions generate temporary segments. You also should look into the option of creating a locally managed temporary tablespace with the CREATE TEMPORARY TABLESPACE command which again will alleviate the contention for blocks in the database buffer cache. Hope that helps, Gaja --- Reardon, Bruce (CALBBAY) [EMAIL PROTECTED] wrote: Hi, Our database is experiencing a very large number of waits on the cache buffers chains latch. I know the child latch# is 242 (details below). We are on Oracle 8.1.5.1.1 on NT4. The problems seem to have started appearing after starting large scale use of Global Temporary tables (GTT). Our temp tablespace was of type temporary but a suggestion from Oracle was to change this to Permanent (due to GTT related bugs in 815). This was done and the database was restarted but the waits are still occurring. What else should I try to look for? Thanks, Bruce Reardon mailto:[EMAIL PROTECTED] Our top waits in general are: SQL @system_times EVENT TIME_WAITED --- PX Idle Wait 581928737 PX Deq: Execution Msg 278990599 CPU used by this session 3812597 latch free 202949 db file sequential read 200926 SQL*Net more data to client 73342 db file scattered read 59797 enqueue 19755 Using Ixora's latch_sleeps script I get the following output: SQL @latch_sleeps LATCH TYPE IMPACT SLEEP RATE WAITS HOLDING LEVEL
RE: Waits on latch free for shared_pool library Cache
Bull's eye . Will Check out the Steve Adams' Book revert Thanks -Original Message- From: yong huang [SMTP:[EMAIL PROTECTED]] Sent: Monday, February 05, 2001 10:30 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: Waits on latch free for shared_pool library Cache Hi, Vivek, I can't answer your question. But I know where you get the understanding of freeabl and perm chunks of shared pool memory (Richard Niemiec, "Oracle Performance Tuning", p.615). I don't comment on the overall quality of the book here but I seriously doubt his explanation of "perm". Steve Adams's note says "perm: Permanent memory chunks contain persistent objects..." (Steve Adams' book, p.94) Yong Huang [EMAIL PROTECTED] you wrote: - Waits on Latch Free for shared_pool , Library Cache Phenominally High Qs. What may be Done for the Same ? [snipped] freeabl - most probably stands for the memory that has been used but is freeable perm - most probably stands for the free memory not yet moved to free area for use __ Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] 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: [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).
