What is a local write wait?
Was creating an index with a degree of 4, and in unrecoverable manner? There were few waits for an event called local write wait. Can anyone shed more light on this wait? Thanks Raj -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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: re Rebuilding Indexes in Oracle Apps -- an update
Richard et al,
{for those who've been following the thread on Rebuilding Indexes ...}
I've just been reading the AskTom thread on rebuilding indexes
at
http://asktom.oracle.com/pls/ask/f?p=4950:8:F4950_P8_DISPLAYID:6601312252730
and picked on the important line
Coalesce... reclaim the free space from mostly empty index leaf blocks
that will not be reused otherwise due to your increasing sequence.
Richard has also pointed COALESCE as a better option.
COALESCE would be a better option than REBUILD for Indexes on
monotonically increasing sequences where older values are purged periodically.
Hemant K Chitale
Oracle 9i Database Administrator Certified Professional
My personal web site is : http://hkchital.tripod.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Hemant K Chitale
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:How to drop a datafile ?
Perhaps your guess is right Dennis. Dennis For odd features like this marketing probably gets a list from support. /Dennis But Dennis,what is so odd in having this feature ? i think,only ORACLE CORP. knows. Thanx Regards, Jp. 1-11-2003 02:49:30, DENNIS WILLIAMS [EMAIL PROTECTED] wrote: Prem I will provide my guesses and maybe someone that knows the real story will reply. First, in order for Oracle to drop a datafile, you would somehow have to ensure that it is empty. I would guess if Oracle added this feature, then Oracle Support would receive a lot of questions about why it erred out for them. Second, for any software system, what is the portion most likely to cause an error? The part that changed last. If they created this feature at this late date, it would probably produce weird behavior sometime. Finally, developers develop what they are requested to develop. Those requests generally come from marketing. For odd features like this marketing probably gets a list from support. So, if many customers aren't requesting a feature, it will probably never get on the development feature list. But that is all just a guess based on my personal experience working for a software vendor long ago on a faraway planet, and that vendor certainly wasn't Oracle. Dennis Williams -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Prem Khanna J 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: Finding overlapping time periods - suggestions please
Here's a simple programmatic method (careful, I haven't tested it). declare v_activity_count number := 0; begin for rec in ( select start_time event_time, 'start' event_type from some_table union select end_time event_time, 'end' event_type from some_table order by 1 ) loop if(rec.event_type = 'start') then v_activity_count:= v_activity_count + 1; else v_activity_count:= v_activity_count - 1; end if if(v_activity_count = 1) then -- One activity elseif(v_activity_count 1) then -- Overlapping activities else -- Nothing going on end if; end loop; end; Regards David Lord -Original Message- [mailto:[EMAIL PROTECTED] Sent: 31 October 2003 18:25 To: Multiple recipients of list ORACLE-L I was wondering if anyone had the need to find overlapping time periods and how to identify them efficiently. Here is the scenario: Elapsed minutes refer to the actual clock time either spent on a given task. Thus an activity that started at 9:00 am and finished at 11:00 am on the same day is said to have 120 elapsed minutes. If one task overlaps another (either completely or partially with another task), then the tasks are said to be multitasked. In that case the system will store the portion of the elapsed time that was multitasked as elapsed multitask minutes and the portion of the time that was not overlapped as elapsed single minutes. In addition, for the portion of time that two or more activities were simultaneously taking place; their time will be divided by the number of simultaneous activities and stored as prorated multi minutes. The sum of Elapsed Single Minutes and Prorated Minutes will equal the actual clock time that a vehicle was active. The following example should help to illustrate these concepts. In the table below a list of fictitious activities for a vehicle are shown in addition to how the time is allocated to the various measures: ActivityStart Time End TimeElapsed Minutes Elapsed Multitask Minutes Elapsed Single Minutes Prorated Multi Minutes Prorated Minutes 1 10:00 12:00 120 60 60 25 85 3 11:00 13:00 120 120 0 55 55 4 11:30 13:30 120 90 30 40 70 7 13:30 16:00 150 0 150 0 150 Totals 510 270 240 120 360 The vehicle was active from 10:00 to 16:00, a total of 6 hours (360 minutes) which is equal to the total of Prorated Minutes. The vehicle performed 8 ½ hours (510 minutes) of work during that 6-hour time span. This can be arrived at by adding the total of Elapsed Multitask Minutes (270) + the total of Elapsed Single Minutes (240). *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** This e-mail and its attachments are intended for the author's addressee only and may be confidential. If they have come to you in error you must take no action based on them, nor must you copy or show them to anyone; please reply to this e-mail and highlight the error. Please note that this e-mail has been created in the knowledge that Internet e-mail is not a 100% secure communications medium. We advise that you understand and observe this lack of security when e-mailing us. Steps have been taken to ensure this e-mail and attachments are free from any virus, but advise the recipient to ensure they are actually virus free. The views, opinions and judgments expressed in this message are solely those of the author. The message contents have not been reviewed or approved by Iron Mountain. *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Lord David 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: 9i on RHAS3
Read operation from evenone-ROW table will
benefit from index - if you index all necessary columns. A full table scan
reads segment header and the datablock. Index scan requires only one read of
leaf block in case of one-block index.
Also, you may get other benefits, if you have
unique index or constraint on the table, Oracle won't search for more rows once
first one is retrieved - and in join condition these one-row row sources can be
put first in join order, helping in performance.
Tanel.
- Original Message -
From:
[EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L
Sent: Saturday, November 01, 2003 12:09
AM
Subject: RE: 9i on RHAS3
If the question is 'Should I
use an index with a small table, even \ one that fits in a single block", then the answer is very possibly
"yes". Best to test with your SQL,
but for simple selects the use of an index makes the SQL much more scalable. Search the archives on 'run_stats', as that was the
name of a script used to compare
indexed vs. non-indexed. Jared
"Jesse, Rich"
[EMAIL PROTECTED] Sent by: [EMAIL PROTECTED]
10/31/2003 01:09 PM
Please respond to ORACLE-L
To:
Multiple recipients of list ORACLE-L
[EMAIL PROTECTED] cc:
Subject:RE: 9i on
RHAS3Wasn't there a whitepaper somewhere that said that it may be more
efficientto use an index with NL, even if the entire table fits in a
single block? Aquick scan of my saved ORACLE-L messages didn't
reveal anything.RichRich Jesse
System/Database
Administrator[EMAIL PROTECTED]
Quad/Tech Inc, Sussex, WI USA
-Original Message- From: Mladen Gogala
[mailto:[EMAIL PROTECTED] Sent: Friday, October 31, 2003 11:25
AM To: Multiple recipients of list ORACLE-L Subject: Re: 9i on
RHAS3 Rich, I don't have AS 3.0, I'm using regular
RH 9 and RH 8 based worsktations, with gcc 3.2
(gcc-gnat-3.2.2-5,gcc-3.2.2-5) and curiously enough, the installation
worked right out of the box, with a quirk with linking context
("undefined symbol"), but I was able to ignore the error and
proceed. Oracle works well, no complaints so far. There is a thing
that confuses me, but it's generic. I tried on a Solaris8 box and the
result was the same (9.2.0.4). Here is what confuses me.
Here are two execution plans, for the same query (autotrace on
explain, timing on). More expensive plan takes less time. Shouldn't it
be the other way round? Elapsed: 00:00:00.00
Execution Plan
--
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7
Card=14 Bytes=77 0)
1 0 NESTED LOOPS (Cost=7
Card=14 Bytes=770) 2 1 TABLE
ACCESS (BY INDEX ROWID) OF 'CP_ACTIONS' (Cost=2 Ca
rd=14 Bytes=588) 3
2INDEX (RANGE SCAN) OF 'CP_ACTIONS_EFF_I'
(NON-UNIQUE) (Cost=2
Card=14) 4 1
TABLE ACCESS (BY INDEX ROWID) OF 'CHG_TKR' (Cost=2 Card=
1 Bytes=13) 5
4INDEX (UNIQUE SCAN) OF 'SYS_C004800' (UNIQUE)
(Cost=1 Card=1)
Elapsed: 00:00:00.01
Execution Plan
--
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=6
Card=14 Bytes=77 0)
1 0 HASH JOIN (Cost=6 Card=14
Bytes=770) 2 1 TABLE ACCESS
(BY INDEX ROWID) OF 'CP_ACTIONS' (Cost=2 Ca
rd=14 Bytes=588) 3
2INDEX (RANGE SCAN) OF 'CP_ACTIONS_EFF_I'
(NON-UNIQUE) (Cost=2
Card=14) 4 1
TABLE ACCESS (FULL) OF 'CHG_TKR' (Cost=4 Card=1602 Bytes
=20826)
Has anyone tried 9i on RHAS3 yet? Metalink 252217.1 and the
venerable Werner Puschitz's site http://www.puschitz.com have
many icky hacks that seem to have to be done, including
temporarily dropping gcc323 to the highly unstable and
buggy v2.96 (even GNU says not to use it! -- it's not even
listed as a release on their website).I'm
guessing that Mr. Puschitz isn't on this list? Looks like he
knows the Oracle install on RedHat quite well.
How's about it, Mladen? I'm not willing to scrap my Gentoo
box to test it. :) Rich-- Please see
the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Jesse,
RichINET: [EMAIL PROTECTED]Fat City Network Services
-- 858-538-5051 http://www.fatcity.comSan Diego, California
-- Mailing list and web hosting
services-To
REMOVE yourself from this mailing list, send an E-Mail messageto:
[EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message
BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing
list you want to be removed from). You mayalso send the HELP command
for other information (like
subscribing).
Re: Client Search Info Needed
Tracy,
IMHO the simplest and most efficient solution is :
1) to define a name_cleanup() function which does something like
replace(translate(replace(upper(arg), 'MC', 'MAC'), '- ', '##'),
'#', '')
(this is of course a very simple example)
2) to maintain by trigger an indexed CLEANED_UP_NAME which is just
name_cleanup(last_name) (you can do the same for first_name)
3) and to have your queries being written as
CLEANED_UP_NAME like name_cleanup(input) || '%'
Somebody has mentioned soundex, I am no great fan of soundex :
SQL select soundex('mac gregor'), soundex('mcgregor')
2 from dual;
SOUN SOUN
M226 M262
SQL select soundex('thompson'), soundex('thomson')
2 from dual;
SOUN SOUN
T512 T525
HTH,
Stephane Faroult
Tracy Rahmlow wrote:
I am looking for an efficient solution to the following:
We intend to capture information about a client such as:
first name - John
last name - McDonald
phone numer - 222.222.
zip code - 4
state - FL
client number - 123343
The names will be stored in mixed case for proper printing on client documents.
The reps would like the flexiblity to enter the search criteria in a number of
formats such as:
1) last name like mcdon* (wildcard) and first name = john
2) client number = 123343 (note: some clients do not always have their client
number handy so it can not be the only available search mechanism)
3) last name = mac gregor (and locate both macgregor and mac gregor)
4) last name = kinney-jones (and locate both kinney-jones and kinney jones)
How many indexes and of what type are required? Does the leading the column of
an index have to be specified for the index to be used? I thought I remember
hearing that that was a limitation of an older release, but that is no longer
the case with 8 and up. Are there any white papers available that address the
topic of client search and best practices?
Thanks for your help!!
American Express made the following
annotations on 10/30/2003 04:11:07 PM
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Stephane Faroult
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: Finding overlapping time periods - suggestions please
[EMAIL PROTECTED] wrote: I was wondering if anyone had the need to find overlapping time periods and how to identify them efficiently. Here is the scenario: Elapsed minutes refer to the actual clock time either spent on a given task. Thus an activity that started at 9:00 am and finished at 11:00 am on the same day is said to have 120 elapsed minutes. If one task overlaps another (either completely or partially with another task), then the tasks are said to be multitasked. In that case the system will store the portion of the elapsed time that was multitasked as elapsed multitask minutes and the portion of the time that was not overlapped as elapsed single minutes. In addition, for the portion of time that two or more activities were simultaneously taking place; their time will be divided by the number of simultaneous activities and stored as prorated multi minutes. The sum of Elapsed Single Minutes and Prorated Minutes will equal the actual clock time that a vehicle was active. The following example should help to illustrate these concepts. In the table below a list of fictitious activities for a vehicle are shown in addition to how the time is allocated to the various measures: ActivityStart Time End TimeElapsed Minutes Elapsed Multitask Minutes Elapsed Single Minutes Prorated Multi Minutes Prorated Minutes 1 10:00 12:00 120 60 60 25 85 3 11:00 13:00 120 120 0 55 55 4 11:30 13:30 120 90 30 40 70 7 13:30 16:00 150 0 150 0 150 Totals 510 270 240 120 360 The vehicle was active from 10:00 to 16:00, a total of 6 hours (360 minutes) which is equal to the total of Prorated Minutes. The vehicle performed 8 ½ hours (510 minutes) of work during that 6-hour time span. This can be arrived at by adding the total of Elapsed Multitask Minutes (270) + the total of Elapsed Single Minutes (240). Babette, I see the problem as quite similar to the 'let's fill up the calendar' problem. Basically the problem is to have time slices and to know what is going on during those slices. It's pretty easy to build up a view returning one row per minute in the timespan which matters; I am using all_tab_columns as a table with more rows than I need, a smarter solution would be the infinite_dual once suggested by Tim Goraman : select y.t0 + rn / 1440 current_time from (select rownum rn from all_tab_columns where rownum (select (max(end_time) - min(start_time)) * 1440 from activities)) x, (select min(start_time) t0 from activities) y If ACTIVITIES (which I supposed to be made of ACTIVITY, START_TIME, END_TIME) is indexed on both START_TIME and END_TIME, it should be fast enough. From there, it is easy enough to build up a kind of 'bitmap' of activities - this for instance shows a '1' when a given task is active, '0' when it is not : select b.current_time, a.activity, decode(sign(b.current_time - a.start_time), -1, 0, decode(sign(a.end_time - b.current_time), 1, 1, 0)) active from activities a, (select y.t0 + rn / 1440 current_time from (select rownum rn from all_tab_columns where rownum = (select (max(end_time) - min(start_time)) * 1440 from activities)) x, (select min(start_time) t0 from activities) y) b / a SUM() and a GROUP BY on the current time tell you how many tasks are concurrently active at a given time, etc. Should be enough to get you started ... -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult 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: Outsourcing's dirty secret
Hi All DBA's all here hmm...well if i were the CFO i would have been lookin for the fast=true parameter if i were bald i would have been looking for a pill or a cream that would give me hairs overnight..the time of instant nirvana has come :-)..software bpo call centeres they are all same..today the jobs are being moved to india becaz the CEO's see that they are able to save 10$ on paper. or maybe just becoz your competitor is doin so..capital always moves in serach of labour thats the bottom line ..:-) i read argentina programmers are cheaper then indians well hehehe..hehee ...:-)..argentians woman are they more beautifulthe author in my opinion has provided a skewewed version of outsourcing ..the real competition for india will come from ETHOPIA i beelive ..provided the ethopians start learning english...:-) and will start working for food :-) as for us we americans we can come out with a dirty trick like not sharing information on fear of getting sacked with indian programmers :-) regards Hrishy --- Loughmiller, Greg [EMAIL PROTECTED] wrote: not only salries, but job opportunities as well:-) greg -Original Message- Sent: Thursday, October 30, 2003 3:39 PM To: Multiple recipients of list ORACLE-L But sure as hell does drive salaries down over here. On 10/30/2003 03:04:24 PM, [EMAIL PROTECTED] wrote: The perception of outsourcing has been that you can send your work offshore, and get it done cheaper, with higher quality. I think that this article helps to dispel that as a myth. It may or may not be less expensive, it may or may not be better. Jared Jamadagni, Rajendra [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/30/2003 09:49 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Outsourcing's dirty secret What is also unfortunate that the company X which outsourced its project to India, didn't do its job right ... If you just want the cheapest Rolex, you can't complain about its quality later on. I am not saying this couldn't have happened, whatever happened is unfortunate, but I am just saying that the company didn't understand CYA sufficiently, it is just a blame game now. C'mon ... I think that article is one side of the coin. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Thursday, October 30, 2003 11:59 AM To: Multiple recipients of list ORACLE-L List - If a manager seems to be contemplating outsourcing, you might want to post this. Unless you work for an outsourcer. ;-) http://techupdate.zdnet.com/techupdate/stories/main/Hidden_Costs_of_IT_Outso urcing.html Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -- ** 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. **5 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra 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). Mladen Gogala Oracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC
Re: memory usage by dbw very high
Just for clarification, do you actually see swapping when starting a new process or you just guess linux would swap because you don't see "free" memory in top output? Tanel. - Original Message - From: Sai Selvaganesan To: Multiple recipients of list ORACLE-L Sent: Saturday, November 01, 2003 1:34 AM Subject: RE: memory usage by dbw very high rich the ipcs output shows 1.1 gb. so nearly 2 gb(total ram size is 3.08)is used by non shared memory size. i went thru all the processes and found dbwr using the max %mem. what could be the reason? sai"Jesse, Rich" [EMAIL PROTECTED] wrote: If I'm not mistaken, this figure includes the size of the shared memorysegment from the SGA. Take the output of the "oracle" line of "ipcs -a"(hopefully you'll only have one!) and subtract it from the process size toget a better idea of the non-shared memory size of the process.RichRich Jesse System/Database Administrator[EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA-Original Message-Sent: Friday, October 31, 2003 3:49 PMTo: Multiple recipients of list ORACLE-Lhii have a system that has no active users at this point of time. the memoryused by the dbw process is very high leading to a lot of swapping when anyprocess starts.here are the spcesversion:9.2.0.4os:Linux 2.4.9-e.24smpo/p from top:1:44pm up 29 days, 23:55, 4 users, load average: 1.73, 1.68, 1.35132 proces! ses: 131 sleeping, 1 running, 0 zombie, 0 stoppedCPU0 states: 24.4% user, 2.2% system, 0.0% nice, 72.2% idleCPU1 states: 0.5% user, 0.5% system, 0.0% nice, 98.0% idleCPU2 states: 0.0% user, 0.1% system, 0.0% nice, 99.4% idleCPU3 states: 0.3% user, 0.4% system, 0.0% nice, 98.3% idleMem: 3089964K av, 3083380K used, 6584K free, 846848K shrd, 193448KbuffSwap: 2048152K av, 1652K used, 2046500K free 1852468Kcachedsga size:Total System Global Area 1084823632 bytesFixed Size 452688 bytesVariable Size 335544320 bytesDatabase Buffers 738197504 bytesRedo Buffers 10629120 bytespga aggregate size:700Mand ps o/p of dbw processUSER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMANDoracle 4062 0.0 16.4 1131260 508168 ? S 10:16 0:06ora_dbw0_revenueplease advise. what is really going on.thankssai-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Jesse, RichINET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
Re: What is a local write wait?
Rajesh: Typically DBWR has to free up some buffers when you want to read something from the disk. During this process there are chances that you will be waiting for your local buffer (i.e blocks dirtied/invalidated by your session) to be written to disk. During this time the waits are shown as local write waits. BTW do you have any other write waits or just seeing local waits? And also are you noticing any timeouts for this waits? Typically we wait for local wait up to one second and spin (or retry) again.. Timeouts for local write indicates a serious problem unless you have tiny buffer cache or extremely slow disk' KG - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, November 01, 2003 12:54 PM Was creating an index with a degree of 4, and in unrecoverable manner? There were few waits for an event called local write wait. Can anyone shed more light on this wait? Thanks Raj -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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: K Gopalakrishnan 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: Finding overlapping time periods - suggestions please
Stephane, my solution was suggested because the client was a telco which was offering each client billing period of their own choosing (weekly, bi-weekly, monthly) starting whenever the client wanted. Finding which calls fall in the certain period was a major hassle. Of course, the solution like the one that I've suggested (and I don't know whether it would really work) would not make sense for 3 time periods altogether. What they've ended up implementing was a bunch of external procedures based on C and bitmaps, which is, accidentally, similar in concept to my solution. On 2003.11.01 08:09, Stephane Faroult wrote: [EMAIL PROTECTED] wrote: I was wondering if anyone had the need to find overlapping time periods and how to identify them efficiently. Here is the scenario: Elapsed minutes refer to the actual clock time either spent on a given task. Thus an activity that started at 9:00 am and finished at 11:00 am on the same day is said to have 120 elapsed minutes. If one task overlaps another (either completely or partially with another task), then the tasks are said to be multitasked. In that case the system will store the portion of the elapsed time that was multitasked as elapsed multitask minutes and the portion of the time that was not overlapped as elapsed single minutes. In addition, for the portion of time that two or more activities were simultaneously taking place; their time will be divided by the number of simultaneous activities and stored as prorated multi minutes. The sum of Elapsed Single Minutes and Prorated Minutes will equal the actual clock time that a vehicle was active. The following example should help to illustrate these concepts. In the table below a list of fictitious activities for a vehicle are shown in addition to how the time is allocated to the various measures: ActivityStart Time End TimeElapsed Minutes Elapsed Multitask Minutes Elapsed Single Minutes Prorated Multi Minutes Prorated Minutes 1 10:00 12:00 120 60 60 25 85 3 11:00 13:00 120 120 0 55 55 4 11:30 13:30 120 90 30 40 70 7 13:30 16:00 150 0 150 0 150 Totals 510 270 240 120 360 The vehicle was active from 10:00 to 16:00, a total of 6 hours (360 minutes) which is equal to the total of Prorated Minutes. The vehicle performed 8 ½ hours (510 minutes) of work during that 6-hour time span. This can be arrived at by adding the total of Elapsed Multitask Minutes (270) + the total of Elapsed Single Minutes (240). Babette, I see the problem as quite similar to the 'let's fill up the calendar' problem. Basically the problem is to have time slices and to know what is going on during those slices. It's pretty easy to build up a view returning one row per minute in the timespan which matters; I am using all_tab_columns as a table with more rows than I need, a smarter solution would be the infinite_dual once suggested by Tim Goraman : select y.t0 + rn / 1440 current_time from (select rownum rn from all_tab_columns where rownum (select (max(end_time) - min(start_time)) * 1440 from activities)) x, (select min(start_time) t0 from activities) y If ACTIVITIES (which I supposed to be made of ACTIVITY, START_TIME, END_TIME) is indexed on both START_TIME and END_TIME, it should be fast enough. From there, it is easy enough to build up a kind of 'bitmap' of activities - this for instance shows a '1' when a given task is active, '0' when it is not : select b.current_time, a.activity, decode(sign(b.current_time - a.start_time), -1, 0, decode(sign(a.end_time - b.current_time), 1, 1, 0)) active from activities a, (select y.t0 + rn / 1440 current_time from (select rownum rn from all_tab_columns where rownum = (select (max(end_time) - min(start_time)) * 1440 from activities)) x, (select min(start_time) t0 from activities) y) b / a SUM() and a GROUP BY on the current time tell you how many tasks are concurrently active at a given time, etc. Should be enough to get you started ... -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
Re: memory usage by dbw very high
The whole thing comes as a consequence of using buffered I/O. New linux kernels (2.4.18 and later) have new memory management, which allows the kernel to grab more memory for buffers in periods of intense I./O activity. If you have a very active database on ReiserFS or Ext3, Linux is going to try to help you out by allocating more memory for the file system buffers, even by stealing pages from the active processes, which will, in turn. start paging. The only possible response is to eliminate the buffered I/ O and switch to non-buffered I/O. That is not so hard to do. On 2003.11.01 09:44, Tanel Poder wrote: Just for clarification, do you actually see swapping when starting a new process or you just guess linux would swap because you don't see free memory in top output? Tanel. - Original Message - From: Sai Selvaganesan To: Multiple recipients of list ORACLE-L Sent: Saturday, November 01, 2003 1:34 AM Subject: RE: memory usage by dbw very high rich the ipcs output shows 1.1 gb. so nearly 2 gb(total ram size is 3.08) is used by non shared memory size. i went thru all the processes and found dbwr using the max %mem. what could be the reason? sai Jesse, Rich [EMAIL PROTECTED] wrote: If I'm not mistaken, this figure includes the size of the shared memory segment from the SGA. Take the output of the oracle line of ipcs -a (hopefully you'll only have one!) and subtract it from the process size to get a better idea of the non-shared memory size of the process. Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA -Original Message- Sent: Friday, October 31, 2003 3:49 PM To: Multiple recipients of list ORACLE-L hi i have a system that has no active users at this point of time. the memory used by the dbw process is very high leading to a lot of swapping when any process starts. here are the spces version:9.2.0.4 os:Linux 2.4.9-e.24smp o/p from top: 1:44pm up 29 days, 23:55, 4 users, load average: 1.73, 1.68, 1.35 132 proces! ses: 131 sleeping, 1 running, 0 zombie, 0 stopped CPU0 states: 24.4% user, 2.2% system, 0.0% nice, 72.2% idle CPU1 states: 0.5% user, 0.5% system, 0.0% nice, 98.0% idle CPU2 states: 0.0% user, 0.1% system, 0.0% nice, 99.4% idle CPU3 states: 0.3% user, 0.4% system, 0.0% nice, 98.3% idle Mem: 3089964K av, 3083380K used, 6584K free, 846848K shrd, 193448K buff Swap: 2048152K av, 1652K used, 2046500K free 1852468K cached sga size: Total System Global Area 1084823632 bytes Fixed Size 452688 bytes Variable Size 335544320 bytes Database Buffers 738197504 bytes Redo Buffers 10629120 bytes pga aggregate size:700M and ps o/p of dbw process USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND oracle 4062 0.0 16.4 1131260 508168 ? S 10:16 0:06 ora_dbw0_revenue please advise. what is really going on. thanks sai -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich 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). -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala 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: What is a local write wait?
I found this on the Metalink: http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_datab ase_id=FORp_id=183745.995 Waleed -Original Message- Sent: Saturday, November 01, 2003 2:24 AM To: Multiple recipients of list ORACLE-L Was creating an index with a degree of 4, and in unrecoverable manner? There were few waits for an event called local write wait. Can anyone shed more light on this wait? Thanks Raj -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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: Khedr, Waleed 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: Finding overlapping time periods - suggestions please
Mladen, If you suggest a convoluted solution like this takes water when you have several million rows I fully agree :-). Funny enough, because it really looks like a purely relational problem, and yet it requires bending backwards. My feeling (and it definitely would deserve time to prove) is that quite possibly it's a design issue - perhaps the proper way would not be to say 'this activitity started then and ended then' but 'at this point in time that activity was running'; in fact, the convoluted part of what I suggest roughly means to do that, changing the design on the fly. SF Mladen Gogala wrote: Stephane, my solution was suggested because the client was a telco which was offering each client billing period of their own choosing (weekly, bi-weekly, monthly) starting whenever the client wanted. Finding which calls fall in the certain period was a major hassle. Of course, the solution like the one that I've suggested (and I don't know whether it would really work) would not make sense for 3 time periods altogether. What they've ended up implementing was a bunch of external procedures based on C and bitmaps, which is, accidentally, similar in concept to my solution. On 2003.11.01 08:09, Stephane Faroult wrote: [EMAIL PROTECTED] wrote: I was wondering if anyone had the need to find overlapping time periods and how to identify them efficiently. Here is the scenario: Elapsed minutes refer to the actual clock time either spent on a given task. Thus an activity that started at 9:00 am and finished at 11:00 am on the same day is said to have 120 elapsed minutes. If one task overlaps another (either completely or partially with another task), then the tasks are said to be multitasked. In that case the system will store the portion of the elapsed time that was multitasked as elapsed multitask minutes and the portion of the time that was not overlapped as elapsed single minutes. In addition, for the portion of time that two or more activities were simultaneously taking place; their time will be divided by the number of simultaneous activities and stored as prorated multi minutes. The sum of Elapsed Single Minutes and Prorated Minutes will equal the actual clock time that a vehicle was active. The following example should help to illustrate these concepts. In the table below a list of fictitious activities for a vehicle are shown in addition to how the time is allocated to the various measures: ActivityStart Time End TimeElapsed Minutes Elapsed Multitask Minutes Elapsed Single Minutes Prorated Multi Minutes Prorated Minutes 1 10:00 12:00 120 60 60 25 85 3 11:00 13:00 120 120 0 55 55 4 11:30 13:30 120 90 30 40 70 7 13:30 16:00 150 0 150 0 150 Totals 510 270 240 120 360 The vehicle was active from 10:00 to 16:00, a total of 6 hours (360 minutes) which is equal to the total of Prorated Minutes. The vehicle performed 8 ½ hours (510 minutes) of work during that 6-hour time span. This can be arrived at by adding the total of Elapsed Multitask Minutes (270) + the total of Elapsed Single Minutes (240). Babette, I see the problem as quite similar to the 'let's fill up the calendar' problem. Basically the problem is to have time slices and to know what is going on during those slices. It's pretty easy to build up a view returning one row per minute in the timespan which matters; I am using all_tab_columns as a table with more rows than I need, a smarter solution would be the infinite_dual once suggested by Tim Goraman : select y.t0 + rn / 1440 current_time from (select rownum rn from all_tab_columns where rownum (select (max(end_time) - min(start_time)) * 1440 from activities)) x, (select min(start_time) t0 from activities) y If ACTIVITIES (which I supposed to be made of ACTIVITY, START_TIME, END_TIME) is indexed on both START_TIME and END_TIME, it should be fast enough. From there, it is easy enough to build up a kind of 'bitmap' of activities - this for instance shows a '1' when a given task is active, '0' when it is not : select b.current_time, a.activity, decode(sign(b.current_time - a.start_time), -1, 0, decode(sign(a.end_time - b.current_time), 1, 1, 0)) active from activities a, (select y.t0 + rn / 1440 current_time from (select
RE: Finding overlapping time periods - suggestions please
Title: Finding overlapping time periods - suggestions please
Easy, this should
do it:
Create
a time dimensions--drop table
test_date_dim;create table test_date_dim (time_dt
date); Fill the dimension
for one day only--beginfor i
in 1..24*60 loopinsert into test_date_dim values (trunc(sysdate)+i/
(24 * 60));end
loop;commit;end;
Check the dimension
contents--select
to_char(time_dt,'mm/dd/yy hh24:mi:ss') mtimestamp from
test_date_dim; Create the
activity table--create table
test_activity ( activity_id number, start_dt date, end_dt date);insert into
test_activity values (1, to_date('10:00','hh24:mi'),
to_date('12:00','hh24:mi'));insert into test_activity values (3,
to_date('11:00','hh24:mi'), to_date('13:00','hh24:mi'));insert into
test_activity values (4, to_date('11:30','hh24:mi'),
to_date('13:30','hh24:mi'));insert into test_activity values (7,
to_date('13:30','hh24:mi'),
to_date('16:00','hh24:mi'));commit;
Check the activity table--select
* from test_activity; Easy
Solution--select
activity_id
activity_id,
count(*)
elapsed,
count(decode(activity_cnt,1,null,time_dt))
elapsed_multitask,
count(decode(activity_cnt,1,time_dt,null))
elapsed_single,
round(sum(decode(activity_cnt,1,0,1/activity_cnt)))
prorated_multi_minutes,
count(decode(activity_cnt,1,time_dt,null))
+
round(sum(decode(activity_cnt,1,0,1/activity_cnt))) prorated_minutes
from (select time_dt,
b.activity_id, count(distinct
b.activity_id) over (partition by time_dt) activity_cntfrom
test_date_dim a, test_activity bwhere a.time_dt = b.start_dt and
a.time_dt b.end_dt)group by
activity_id-
ACTIVITY_ID ELAPSED
ELAPSED_MULTITASK ELAPSED_SINGLE
PRORATED_MULTI_MINUTES
PRORATED_MINUTES1
120
60
60
25
853
120
120
0
55
554
120
90
30
40
707
150
0
150
0
150
Regards,
Waleed
-Original Message-From:
[EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]Sent: Friday,
October 31, 2003 1:25 PMTo: Multiple recipients of list
ORACLE-LSubject: Finding overlapping time periods - suggestions
please
I was wondering if anyone had the need to find
overlapping time periods and how to identify them efficiently.
Here is the scenario:
Elapsed minutes refer to
the actual "clock" time either spent on a given task. Thus an
activity that started at 9:00 am and finished at 11:00 am on the same day
is said to have 120 elapsed minutes.
If one task overlaps
another (either completely or partially with another task), then the tasks
are said to be "multitasked". In that case the system will store the
portion of the elapsed time that was multitasked as "elapsed multitask
minutes" and the portion of the time that was not overlapped as "elapsed
single minutes". In addition, for the portion of time that two or
more activities were simultaneously taking place; their time will be
divided by the number of simultaneous activities and stored as "prorated
multi minutes". The sum of Elapsed Single Minutes and Prorated
Minutes will equal the actual clock time that a vehicle was
active.
The following example
should help to illustrate these concepts. In the table below a list
of fictitious activities for a vehicle are shown in addition to how the
time is allocated to the various measures:
Activity
Start Time End
Time Elapsed Minutes Elapsed
Multitask Minutes Elapsed Single
Minutes Prorated Multi Minutes Prorated
Minutes1 10:00
12:00 120 60
60 25
853
11:00 13:00 120
120 0
55
554
11:30 13:30 120
90 30
40
707
13:30 16:00 150
0 150
0
150Totals
510
270 240
120 360The vehicle was active from 10:00 to 16:00, a total of
6 hours (360 minutes) which is equal to the total of Prorated
Minutes.
The vehicle performed 8 ½
hours (510 minutes) of work during that 6-hour time span. This can
be arrived at by adding the total of Elapsed Multitask Minutes (270) + the
total of Elapsed Single Minutes
(240).
