RE: pga_aggregate_target
The formula in the book is just another form of hit ratio IMO. As Mark suggested, just check the max allocated a few times, or check it historically if you are running statspack. Set the pga_aggregate_target to that value plus a margin you feel comfortable with, and then monitor and adjust as needed. 9i gives you tools for managing memory that just didn't exist in previous versions. Jared On Mon, 2003-12-29 at 05:04, Jeffrey Beckstrom wrote: > Will look at what you suggest. The Oracle docs suggest you using a formula based on > amount of system memory. However, this is not the only database on the server. > > >>> [EMAIL PROTECTED] 12/26/03 3:29:26 PM >>> > Jeffrey, > > If you are simply looking at a conversion from 8i to 9iR2, and you're not > going to be implementing a bunch of new 9i features (that could affect PGA > usage), then it's probably reasonably straightforward. You can look at > 'session pga memory' and 'session pga memory max' statistics in V$SESSTAT to > get an idea of current PGA memory consumption in 8i. The 'max' statistic > is probably more interesting in terms of total aggregate memory consumption -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still 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: SQL CASE Statement
Is is just me, or is the code missing? On Mon, 2003-12-29 at 16:24, Pillai, Rajesh wrote: > Hi Jared, > Here is an example - > The following part of unix script does not work- > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still 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: large pl/sql table sucking up all memory on a server
I think Unix Kernel parameter limit should help in this case. It can prevent runaway process from consuming the whole machine resource. In most unix, there is kernel parameter(or ulimit) that restrict the maximum heap/data segment size.And the parameter name depend on the OS. Also, as other guys said, in oracle, there is also work around. You can use limit clause of bulk collect. Feature should be properly used. Regards Zhu Chao. - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Tuesday, December 30, 2003 1:34 AM > we dont have that level of granularity. everyone developers out of the same DBA > account(not my call). > > any parameter settings to limit the size of pl/sql tables? > > > > > From: "Jamadagni, Rajendra" <[EMAIL PROTECTED]> > > Date: 2003/12/29 Mon PM 12:14:24 EST > > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > > Subject: RE: large pl/sql table sucking up all memory on a server > > > > Assign the developer a "profile" that would do good. > > > > 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: Monday, December 29, 2003 12:00 PM > > To: Multiple recipients of list ORACLE-L > > > > > > One of our guys used a very large bulk collect into with a forall update. It > > sucked up all the swap space on our solaris box and noone could connect to it. So > > we had to bounce the server. > > > > I was under the impression that pl/sql tables go into the buffer cache and cannot > > go large than its size? Oracle typically holds your hand with memory usage issues. > > Are there any parameter settings I can use that limit the size of pl/sql tables? > > > > Or are they just dynamic arrays that can grow as large as you want. > > > > I know your supposed to use a 'limit' command on them. I didnt write it. I just > > dont want it to happen again. > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > -- > > Author: <[EMAIL PROTECTED] > > 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). > > > > ** > > 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. > > **4 > > -- > > 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). > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: <[EMAIL PROTECTED] > 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: zhu chao INET: [EMAIL PRO
Re: RE: large pl/sql table sucking up all memory on a server
This is probably old hat for you, but given it's Unix (Sun) and it's a client process, wouldn't you be able to use ulimit to stop memory allocation growing past a certain size? The other thing I'd try is to limit memory through the resource control in Oracle. But that is highly version dependent and I'm not sure which version you running. Cheers Nuno Souto [EMAIL PROTECTED] - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Tuesday, December 30, 2003 5:39 AM > it filled up the pga and then used 'swap' space on the hard drive. this filled up. > > didnt realize this was a feature. yeah, I know i can 'tell' them to do it. I was > hoping to disallow it though. > > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nuno Souto 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: getting estimate of result set from v$sql_plan
I'll add the missing part to one of statements in the beginning of my last mail: > 3rd is probably quite inaccurate, especially when histograms aren't > calculated on non-single row predicate columns (again, with bind > variables is useless). Also, if you want to use it, you have to find > the right row for the ... rowcount (cardinality). For select queries this is normally the execution plan row with id = 1 in v$sql_plan, but for update for example, it's the id=0 row that shows cardinality. Tanel.
Re: SQL CASE Statement
If you are in 8i, then it won't work. Only after 9i is the SQL and PL/SQL SQL engine the same. Cheers Nuno Souto [EMAIL PROTECTED] - Original Message - > Any clues on why does a CASE block does not work in an INSERT-APPEND-SELECT > statement called in a sql block in an UNIX script? If I replace the case block with a decode function then it works Whereas in a sql command prompt, both DECODE and CASE yields results. > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nuno Souto 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: getting estimate of result set from v$sql_plan
Hi!
Comments below:
> let me be clearer. I need to return an
estimate of the number of rows for> 'pagination'. The user will page
through 25 rows a time, but wants an> estimate on the total number of
rows returned. I want to avoid counts.> > tom kytes book says to
use v$sql_plan, but how do i get my exact query? Id> prefer to do it
without table joins. Since I have a very strict SLA.> > dbms_xplan
is returning the whole thing. I just want the cardinality and I> have to
put it in a variable.
I'll just post a quick raw idea how to do it
(although there are many problems which may render this functionality useless)
with a longer example.
Basically, in 9i there are four ways of finding out
how many rows will any query return:
1) select from the query and count
2) use v$sql_plan_statistics column output_rows for
already executed queries
3) use CBO estimates for parsed queries from
v$sql_plan
4) ask Larry Ellison
1st is the most accurate, but
resource-hungry
2nd has the problem that it only records last
rowcount for a given query (which means it's useless with bind
variables)
3rd is probably quite inaccurate, especially when
histograms aren't calculated on non-single row predicate columns (again, with
bind variables is useless). Also, if you want to use it, you have to find the
right row for the
4th isn't implemented before 11g
Anyway, here are few samples how Oracle estimates
& records rowcounts (this is long):
--
SQL> create table t (a, b) as select 1, 'A' from
sys.obj$;
Table created.
SQL> insert into t values (2,
'B');
1 row created.
SQL> commit;
Commit complete.
SQL> select a, count(*) from t group by
a;
A
COUNT(*)
-- --
1
7211
2
1
We got one 2 and lots of 1s in the table, 7212
records in total.
SQL> var v number;
SQL> exec :v:=2;
Lets use bind variables for our
query
PL/SQL procedure successfully
completed.
SQL> analyze table t compute
statistics;
Calculate stats without
histograms
Table analyzed.
SQL> select /* taneltest */ * from t where
a=:v;
A B
-- -
2 B
SQL> select
output_rows
2 from
v$sql_plan_statistics p
3 where (address,
hash_value) in (
4 select address,
hash_value from v$sql where sql_text like '%/* taneltest
*/%'
5
and sql_text not like '%hash_value%'
6
);
OUTPUT_ROWS
---
1
v$sql_plan_statistics showed that last time the
statement was executed, it returned 1
row.
SQL> exec :v:=1;
PL/SQL procedure successfully
completed.
SQL> select /* taneltest */ * from t where
a=:v;
A B
-- -
1 A
1 A
1 A
1 A
...
(pressed CTRL-C)
682 rows selected.
SQL> select
output_rows
2 from
v$sql_plan_statistics p
3 where (address,
hash_value) in (
4
select address, hash_value from v$sql where sql_text like '%/* taneltest
*/%'
5
and sql_text not like '%hash_value%'
6
);
OUTPUT_ROWS
---
692
The same statement now has returned 692 rows (less was
displayed because of my ctrl-c)
If we need an estimate without executions then we just
have to parse the statement and rely on CBO calculations (note that because my
lazyness I still executed the select without parsing it. Also in this example
Im using literal values, with binds the execution plan is probably not
generated before the first bind - and this execution plan will remain despite
bind value changes until it is invalidated by some
reason).
SQL> select /* taneltest2 */ * from t where
a=2;
A B
-- -
2 B
Lets estimate how many 2s we have (without any
histograms)
SQL> select p.child_number, p.id, rpad(' ',
p.depth) || p.operation || ' ' || p.options operation,
2
p.cost, p.cardinality, p.bytes, p.temp_space
3 from v$sql_plan
p
4 where (address,
hash_value) in (
5
select address, hash_value from v$sql where sql_text like '%/* taneltest2
*/%'
6
and sql_text not like '%hash_value%'
7
);
CHILD_NUMBER
ID OPERATION
COST CARDINALITY BYTES
TEMP_SPACE
-- --
-- --- -- --
0
0 SELECT STATEMENT
6
0
1 TABLE ACCESS FULL
6
3606
10818
CBO estimates that therell be 3606 2s in the table.
Note that 3606 is exactly half of 7212, the number of rows in the table (despite
no histograms we have rowcnt populated in tab$ and distcnt$ populated in
hist_head$, thus CBO can find the density by simply dividing these
two)
SQL> analyze table t compute statistics for columns
a size 100;
Lets generate a
histogram:
Table analyzed.
SQL> select /* taneltest2 */ * from t where
a=2;
Re: SQL CASE Statement
In 9i, the codebase should be the same, so syntax should be "portable". Tanel. - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Tuesday, December 30, 2003 2:44 AM > Hi: > > Sql and pl/sql use different engine internally. I found this is true for > "CASE" in oracle 8i. > > HTH. > > Guang > > On Mon, 29 Dec 2003, Pillai, Rajesh wrote: > > > Hi All, > > Any clues on why does a CASE block does not work in an INSERT-APPEND-SELECT statement called in a sql block in an UNIX script? If I replace the case block with a decode function then it works Whereas in a sql command prompt, both DECODE and CASE yields results. > > > > TIA for all ur hints. > > > > Thanks, > > Rajesh > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > -- > > Author: Pillai, Rajesh > > 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: Guang Mei > 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: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Correct way to accuse BCHR tuning method (Was: Hit ratio)
I think Dave Ensor, at the recent UKOUG conference, called it his "portable tuning kit": select elapsed_time, cpu_time from v$sql order by elapsed_time; Mogens Connor McDonald wrote: Yep. Simple example: Even though it seems to be sometimes a little on the 'random' side, the ELAPSED_TIME column on V$SQL in v9 is an absolute god send... Cheers Connor --- Mogens_Nørgaard <[EMAIL PROTECTED]> wrote: > And I think it's important to realise that ratios are useless as a starting point in the tuning process on any system, not only Oracle. Most OS'es and databases use not instrumented correctly to deal with response time measurements (makes you wonder: If response TIME is what matters, how can you then not measure exactly that - time?) - so in the other worlds (Unix, VMS, Windows, SQL Server, MySQL, DB2) people still beleive in the ratios because they have nothing better. Mogens Anjo Kolk wrote: BCHR tuning is useless as a starting point in the tuning process. Anjo. -Original Message- Yong Huang Sent: Wednesday, December 24, 2003 6:09 PM To: Multiple recipients of list ORACLE-L [This message is not technical, but educational. Readers interested in technical info only may want to skip] Hi, Cary and Gopal, My last message is misunderstood. Nowadays most DBAs that still use buffer cache hit ratio as a primary performance tuning method are those that rarely browse public forums. When we convince them that's a wrong method, we should not say "Look. I can bump up BCHR to an arbitrary value". If he doesn't think, he'll say "Indeed. If I can get any value, it must be rubbish". But if he's a logical person and thinks for a few minutes, he'll say "It's unfair to run that choose_a_hit_ratio program to get an arbitrary hit ratio and say the method is wrong, because you can use the same logic to write a program to get an arbitrary library cache hit ratio, OS in-core inode cache hit ratio or directory name cache hit..." My last message is not meant to revive the outdated and probably never correct tuning method. Instead it's meant to let oracle-l members know that when you need to convince those DBAs that still use that method, you need to accuse the BCHR method for correct reason, namely, BCHR does not contain sufficient information for tuning, not because you can raise its value by constantly scanning a table in Oracle; you won't be able to convince some stubbon DBAs who enjoy thinking in a quiet place. I agree that "It's not the ratio that needs condemning, it's the advice about..." What I disagree is the wrong educational tool people on public forums have recently used again and again to show the inadequacy of the BCHR tuning method. Yong Huang __ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?ISO-8859-1?Q?Mogens_N=F8rgaard?= 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). = Connor McDonald web: http://www.oracledba.co.uk web: http://www.oaktable.net email: [EMAIL PROTECTED] "GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day" Yahoo! Messenger - Communicate instantly..."Ping" your friends today! Download Messenger Now http://uk.messenger.yahoo.com/download/index.html -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?ISO-8859-1?Q?Mogens_N=F8rgaard?= 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: SQL CASE Statement
In 8i, CASE is supported in SQL but not PL/SQL. In 9i, it's supported
on both.
Workaround is to use dynamic SQL ("execute immediate"), or upgrade to 9i.
Regards,
Dave
[EMAIL PROTECTED] wrote:
Hi All,
Any clues on why does a CASE block does not work in an INSERT-APPEND-SELECT
statement called in a sql block in an UNIX script? If I replace the case block with a
decode function then it works Whereas in a sql command prompt, both DECODE and
CASE yields results.
TIA for all ur hints.
Thanks,
Rajesh
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: David Hau
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: SQL CASE Statement
Version?
select case when substr(banner, instr(banner,
'Release')+length('Release')+1, 1) < 9 then
'tough luck. sql and pl/sql parsers are different'
else 'check the syntax' end
from v$version where banner like 'Oracle%';
Thanks,
Boris Dali.
--- "Pillai, Rajesh" <[EMAIL PROTECTED]>
wrote: > Hi All,
> Any clues on why does a CASE block does not work in
> an INSERT-APPEND-SELECT statement called in a sql
> block in an UNIX script? If I replace the case block
> with a decode function then it works Whereas in
> a sql command prompt, both DECODE and CASE yields
> results.
>
> TIA for all ur hints.
>
> Thanks,
> Rajesh
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.net
> --
> Author: Pillai, Rajesh
> 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).
__
Post your free ad now! http://personals.yahoo.ca
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Boris Dali
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: SQL CASE Statement
Hi: Sql and pl/sql use different engine internally. I found this is true for "CASE" in oracle 8i. HTH. Guang On Mon, 29 Dec 2003, Pillai, Rajesh wrote: > Hi All, > Any clues on why does a CASE block does not work in an INSERT-APPEND-SELECT > statement called in a sql block in an UNIX script? If I replace the case block with > a decode function then it works Whereas in a sql command prompt, both DECODE and > CASE yields results. > > TIA for all ur hints. > > Thanks, > Rajesh > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Pillai, Rajesh > 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: Guang Mei 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: SQL CASE Statement
Hi Jared, Here is an example - The following part of unix script does not work-
Re: SQL CASE Statement
It would be easier to help if you supply an example that will reproduce the problem. Jared "Pillai, Rajesh" <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 12/29/2003 03:39 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject: SQL CASE Statement Hi All, Any clues on why does a CASE block does not work in an INSERT-APPEND-SELECT statement called in a sql block in an UNIX script? If I replace the case block with a decode function then it works Whereas in a sql command prompt, both DECODE and CASE yields results. TIA for all ur hints. Thanks, Rajesh -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pillai, Rajesh 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).
SQL CASE Statement
Hi All, Any clues on why does a CASE block does not work in an INSERT-APPEND-SELECT statement called in a sql block in an UNIX script? If I replace the case block with a decode function then it works Whereas in a sql command prompt, both DECODE and CASE yields results. TIA for all ur hints. Thanks, Rajesh -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pillai, Rajesh 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: getting estimate of result set from v$sql_plan
oh forget it. stupid question. I figured it out. Sorry. been really busy today. - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Monday, December 29, 2003 4:59 PM > i need to return the cardinality estimate to the user as a number. how do i do that? > > > > From: Stephane Faroult <[EMAIL PROTECTED]> > > Date: 2003/12/29 Mon PM 04:29:26 EST > > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > > Subject: Re: getting estimate of result set from v$sql_plan > > > > [EMAIL PROTECTED] wrote: > > > > > > can someone send me the query I use to hit v$sql_plan to get my estimated cardinality for a query? > > > > > > > > > > @$ORACLE_HOME/rdbms/admin/utlxpls.sql > > or > > $ORACLE_HOME/rdbms/admin/utlxplp.sql if you have parallelism. > > -- > > 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). > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: <[EMAIL PROTECTED] > 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: Ryan 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: getting estimate of result set from v$sql_plan
let me be clearer. I need to return an estimate of the number of rows for 'pagination'. The user will page through 25 rows a time, but wants an estimate on the total number of rows returned. I want to avoid counts. tom kytes book says to use v$sql_plan, but how do i get my exact query? Id prefer to do it without table joins. Since I have a very strict SLA. dbms_xplan is returning the whole thing. I just want the cardinality and I have to put it in a variable. - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Monday, December 29, 2003 4:59 PM > i need to return the cardinality estimate to the user as a number. how do i do that? > > > > From: Stephane Faroult <[EMAIL PROTECTED]> > > Date: 2003/12/29 Mon PM 04:29:26 EST > > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > > Subject: Re: getting estimate of result set from v$sql_plan > > > > [EMAIL PROTECTED] wrote: > > > > > > can someone send me the query I use to hit v$sql_plan to get my estimated cardinality for a query? > > > > > > > > > > @$ORACLE_HOME/rdbms/admin/utlxpls.sql > > or > > $ORACLE_HOME/rdbms/admin/utlxplp.sql if you have parallelism. > > -- > > 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). > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: <[EMAIL PROTECTED] > 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: Ryan 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: large pl/sql table sucking up all memory on a server
FYI. The USPS delivery just (10 minutes ago) arrived with my copy of "Mastering Oracle PL/SQL Practical Solutions", which I ordered from Book Pool, at: http://www.bookpool.com/.x/mzttmcaj4i/sm/1590592174 As you can see, its not available yet on Amazon: http://www.amazon.com/exec/obidos/tg/detail/-/1590592174 /qid=1072734291/sr=1-1/ref=sr_1_1/102-9815245-5757732?v=glance&s=books If you look on pg249, it discusses bulk collect and pga memory, e.g. "I crashed my database session (and shortly thereafter my laptop) because insufficient memory was available to hold the set of 100 employee records". ... "This is where a pipelined solution can help". I'm not sure if this will help in this case, but at least I hope this opens the discussion to include a 'new' reference on PL/SQL. Happy Holidays. Regards, Mike Thomas --- Tanel Poder <[EMAIL PROTECTED]> wrote: > Btw, PRIVATE_SGA only limits shared server SGA > memory usage, for limiting > PGA sizes you could use _pga_max_size (defaults to > 200M), but this is > getting kind of dirty and is unsupported (and works > starting from 9i) > > Tanel. > > - Original Message - > To: <[EMAIL PROTECTED]> > Sent: Monday, December 29, 2003 10:03 PM > > > > Check profile option PRIVATE_SGA (available from > 9i and needs > resource_limit > > parameter to be true). > > > > Tanel. > > > > - Original Message - > > From: <[EMAIL PROTECTED]> > > To: "Multiple recipients of list ORACLE-L" > <[EMAIL PROTECTED]> > > Sent: Monday, December 29, 2003 7:34 PM > > Subject: Re: RE: large pl/sql table sucking up all > memory on a server > > > > > > > we dont have that level of granularity. everyone > developers out of the > > same DBA account(not my call). > > > > > > any parameter settings to limit the size of > pl/sql tables? > > > > > > > > > > > From: "Jamadagni, Rajendra" > <[EMAIL PROTECTED]> > > > > Date: 2003/12/29 Mon PM 12:14:24 EST > > > > To: Multiple recipients of list ORACLE-L > <[EMAIL PROTECTED]> > > > > Subject: RE: large pl/sql table sucking up all > memory on a server > > > > > > > > Assign the developer a "profile" that > would do good. > > > > > > > > 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: Monday, December 29, 2003 12:00 PM > > > > To: Multiple recipients of list ORACLE-L > > > > > > > > > > > > One of our guys used a very large bulk collect > into with a forall > > update. It sucked up all the swap space on our > solaris box and noone could > > connect to it. So we had to bounce the server. > > > > > > > > I was under the impression that pl/sql tables > go into the buffer cache > > and cannot go large than its size? Oracle > typically holds your hand with > > memory usage issues. Are there any parameter > settings I can use that limit > > the size of pl/sql tables? > > > > > > > > Or are they just dynamic arrays that can grow > as large as you want. > > > > > > > > I know your supposed to use a 'limit' command > on them. I didnt write > it. > > I just dont want it to happen again. > > > > > > > > -- > > > > Please see the official ORACLE-L FAQ: > http://www.orafaq.net > > > > -- > > > > Author: <[EMAIL PROTECTED] > > > > 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). > > > > > > > > > > > > > ** > > > > 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. > > > > > > > > > **4 > > > > -- > > > > Please see the official ORACLE-L FAQ: > http://www.orafaq.net > > > > -- > > > > Author: Jamadagni, Rajendra > > > > INET: [EMAIL PROTECTED] > > > > > > > > Fat City Network Servi
Re: getting estimate of result set from v$sql_plan
DBMS_XPLAN Stephane Faroult <[EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> com> cc: Sent by: Subject: Re: getting estimate of result set from v$sql_plan [EMAIL PROTECTED] .com 12/29/2003 01:29 PM Please respond to ORACLE-L [EMAIL PROTECTED] wrote: > > can someone send me the query I use to hit v$sql_plan to get my estimated cardinality for a query? > > @$ORACLE_HOME/rdbms/admin/utlxpls.sql or $ORACLE_HOME/rdbms/admin/utlxplp.sql if you have parallelism. -- 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). -- 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: getting estimate of result set from v$sql_plan
i need to return the cardinality estimate to the user as a number. how do i do that? > > From: Stephane Faroult <[EMAIL PROTECTED]> > Date: 2003/12/29 Mon PM 04:29:26 EST > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Subject: Re: getting estimate of result set from v$sql_plan > > [EMAIL PROTECTED] wrote: > > > > can someone send me the query I use to hit v$sql_plan to get my estimated > > cardinality for a query? > > > > > > @$ORACLE_HOME/rdbms/admin/utlxpls.sql > or > $ORACLE_HOME/rdbms/admin/utlxplp.sql if you have parallelism. > -- > 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). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: <[EMAIL PROTECTED] 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: getting estimate of result set from v$sql_plan
[EMAIL PROTECTED] wrote: > > can someone send me the query I use to hit v$sql_plan to get my estimated > cardinality for a query? > > @$ORACLE_HOME/rdbms/admin/utlxpls.sql or $ORACLE_HOME/rdbms/admin/utlxplp.sql if you have parallelism. -- 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).
dc_sequences
Title: dc_sequences In statspack report on RAC 9.2.0.2 database, under section "Dictionary Cache Stats for DB" there are 64.3% miss for dc_sequences. What causes this? Thanks
Re: RE: large pl/sql table sucking up all memory on a server
Btw, PRIVATE_SGA only limits shared server SGA memory usage, for limiting PGA sizes you could use _pga_max_size (defaults to 200M), but this is getting kind of dirty and is unsupported (and works starting from 9i) Tanel. - Original Message - To: <[EMAIL PROTECTED]> Sent: Monday, December 29, 2003 10:03 PM > Check profile option PRIVATE_SGA (available from 9i and needs resource_limit > parameter to be true). > > Tanel. > > - Original Message - > From: <[EMAIL PROTECTED]> > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > Sent: Monday, December 29, 2003 7:34 PM > Subject: Re: RE: large pl/sql table sucking up all memory on a server > > > > we dont have that level of granularity. everyone developers out of the > same DBA account(not my call). > > > > any parameter settings to limit the size of pl/sql tables? > > > > > > > > From: "Jamadagni, Rajendra" <[EMAIL PROTECTED]> > > > Date: 2003/12/29 Mon PM 12:14:24 EST > > > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > > > Subject: RE: large pl/sql table sucking up all memory on a server > > > > > > Assign the developer a "profile" that would do good. > > > > > > 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: Monday, December 29, 2003 12:00 PM > > > To: Multiple recipients of list ORACLE-L > > > > > > > > > One of our guys used a very large bulk collect into with a forall > update. It sucked up all the swap space on our solaris box and noone could > connect to it. So we had to bounce the server. > > > > > > I was under the impression that pl/sql tables go into the buffer cache > and cannot go large than its size? Oracle typically holds your hand with > memory usage issues. Are there any parameter settings I can use that limit > the size of pl/sql tables? > > > > > > Or are they just dynamic arrays that can grow as large as you want. > > > > > > I know your supposed to use a 'limit' command on them. I didnt write it. > I just dont want it to happen again. > > > > > > -- > > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > > -- > > > Author: <[EMAIL PROTECTED] > > > 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). > > > > > > > > ** > > > 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. > > > > > **4 > > > -- > > > 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). > > > > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > -- > > Author: <[EMAIL PROTECTED] > > 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 y
getting estimate of result set from v$sql_plan
can someone send me the query I use to hit v$sql_plan to get my estimated cardinality for a query? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: <[EMAIL PROTECTED] 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: A performance problem
Dennis, Good advice. I will compare the explain plans. I was only half kidding about my head. As you may know some developers would blame the DBAs for anything they can think of such as snow, rain, poorly performing sql they wrote, etc Thanks, Venu -Original Message- DENNIS WILLIAMS Sent: Monday, December 29, 2003 2:39 PM To: Multiple recipients of list ORACLE-L Venu You are getting some good advice, but here is a different idea for you that nobody has mentioned. You say that the job formerly took 1 hour and now takes 20 hours. You also mention that you have a development environment. If you can locate the main SQL statement(s), you could run an EXPLAIN PLAN in both your production and development environments. This is not nearly as good a way to diagnose performance problems as the other advice you are receiving, but it has the advantage of being quick (Oracle doesn't actually execute the statement), and may put you on the track of what has changed with the execution plan. When they are after your head, quick is good. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, December 29, 2003 12:15 PM To: Multiple recipients of list ORACLE-L John, I can run this in our development environment and trace the job. But, the data is quite a bit larger in production. I can't really take on a refresh/clone now and the prodcution database is over 600GB in size. We do have trace for the job which was available because the program definition for this custom feed job has trace enabled in Apps. That trace file doesn't have any wait event information. This job does use db link. We know that for sure. I advised the developer who wrote this custom feed job to tune it but that is never a satisfactory answer for them. Venu Potluri -Original Message- John Kanagaraj Sent: Monday, December 29, 2003 12:35 PM To: Multiple recipients of list ORACLE-L Venu, Trying to solve the performance issue with a *single* job with Statspack is like searching for a needle in a haystack, especially in an Oracle Apps environment. You will need to trace the program *as it runs*, and if you cannot do that right now, see if you can clone the database to a test system and rerun it again. Btw, was this concurrent job an Oracle standard job or was it a custom program? Any recent changes or patches to the environment? Note that you *can* set trace (albeit just the plain vanilla level 1) on a Concurrent job in 11i... As for the DB Link, can you determine if this indeed does use a Dblink or it is from somewhere else... [See the problem with Statspack?!] John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Grace - Getting something we do NOT deserve Mercy - NOT getting something we DO deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** >-Original Message- >From: Potluri, Venu (CT Appl Suppt) [mailto:[EMAIL PROTECTED] >Sent: Monday, December 29, 2003 8:44 AM >To: Multiple recipients of list ORACLE-L >Subject: A performance problem > > >I have a performance issue in our 11.5.5 Oracle Apps >production environment (Oracle 8.1.7.4). A concurrent job that >feeds into another production envrironment (Oracle 9.2) and >runs less than an hour >typically suddenly took almost 20 hours to finish. The users >are as expected up in arms calling my head on a platter. I >looked at the statspack report for the database this job ran on. > >The Top5 Wait events were: > >Top 5 Wait Events >~ > >Wait Event Waits >Time (cs) % Total Wt Time >--- > >db file sequential read15,978,336 > 5,809,277 57.28 >SQL*Net message from dblink3,868 >1,960,168 19.33 >db file scattered read 2,460,279 >943,252 9.30 >control file sequential read 907,148 > 300,572 2.96 >pipe put2,033 >208,850 2.06 > - >-> cs - centisecond - 100th of a second >-> ms - millisecond - 1000th of a second >-> ordered by wait time desc, waits desc (idle events last) > > > Avg > > Total Waitwait Waits >Event WaitsTimeouts >Time (cs)(ms) /txn > -- >--- -- ---
RE: A performance problem
You are all correct. I am not really trying to figure out why this feed ran 20 hours from the statspack report. I am trying to find out what if anything happened in the database that might have contributed to this job running this long. We do analyze objects in some schemas via a Concurrent job in Oracle Apps called Gather Scehma Statistics and Gather Table Statistics. I will look into the explan plan for this job and compare it to the time it ran quicker. -Original Message- Wolfgang Breitling Sent: Monday, December 29, 2003 2:29 PM To: Multiple recipients of list ORACLE-L Over what time frame was the statspack report taken. The 5,809,277 cs of db file sequential read equates to 16+ hours and the 1,960,168 cs of SQL*Net message from dblink for 5+ hours. Of course, some of these waits could be concurrent rather than sequential. But, as John already pointed out, you can't analyze where a particular process spent its time and why it took so long from a statspack report (unless absolutely nothing else was happening in the DB, and even then not easily). You need to trace the problem process specifically. What changed? Did you re-analyze the tables involved recently? That could change the access plan for some sql in the job. Did the plan for the two statements change (presuming they are part of the problem job)? At 09:44 AM 12/29/2003, you wrote: >I have a performance issue in our 11.5.5 Oracle Apps production >environment (Oracle 8.1.7.4). A concurrent job that feeds into another >production envrironment (Oracle 9.2) and runs less than an hour >typically suddenly took almost 20 hours to finish. The users are as >expected up in arms calling my head on a platter. I looked at the >statspack report for the database this job ran on. > >The Top5 Wait events were: > >Top 5 Wait Events >~ > >Wait Event Waits Time >(cs) % Total Wt Time >--- >db file sequential >read 15,978,336 5,809,277 57.28 >SQL*Net message from >dblink 3,868 1,960,168 19.33 >db file scattered >read 2,460,279 943,2529.30 >control file sequential >read 907,148 300,572 2.96 >pipe >put2,033 >208,8502.06 > - >-> cs - centisecond - 100th of a second >-> ms - millisecond - 1000th of a second >-> ordered by wait time desc, waits desc (idle events last) > > >Avg > >Total Waitwait Waits >Event Waits TimeoutsTime >(cs)(ms) /txn > -- --- -- >- >db file sequential >read 15,978,336 0 5,809,277 4970.3 >SQL*Net message from >dblink 3,868 0 1,960,168 50680.2 >db file scattered >read 2,460,279 0 943,2524 >149.4 >control file sequential >read907,1480300,572355.1 >pipe >put2,033 2,032208,850 >1027 0.1 > > > >Breakdown of Wait time > >Event TimePercentage Avg. >Wait Per Execute Per User Call Per Transaction >db file sequential >read 5809277 60.16% 0.360.68 > 8.228762.11 >SQL*Net message from dblink >1960168 20.30% 506.77 0.232.77 > 2956.51 >db file scattered >read 943252 9.77% 0.380.111.34 >1422.70 >control file sequential read >300572 3.11% 0.330.040.43 >453.35 >pipe >put208850 2.16% 102.73 0.02 > 0.30315.01 > >Here are the top SQL statements ordered by physical reads per execute: >(these two happen to belong to this long running job) >Statement ExecutesPhysical >Reads Reads/Execute Hashs Value % of Total >INSERT INTO ML_MGMT_MCS_FEED SELECT /*+ ORDERED INDEX(MGNAL >ML_MGMT_DIST_NAT_AC_LKUP_X1) USE_MERGE(BAL) */SUBSTR(GLCC.SEGMENT3,1,6) >CENTER,SUBSTR(MGNAL.GL11PROD_ACCOUNT,1,5) >ACCT,SUBSTR(GLCC.SEGMENT2,1,10) NEW10,SUBSTR(GLCC.SEGMENT6,1,6) >PRODUCT,SUBSTR(GLCC.SEGMENT5,1,4) >TRANSTYPE,NVL(SUBSTR(MGNAL.GL11PROD_ACCOUNT,1,5 > 13 9737644 749049.54 >1419451399 30.18 >SELECT DISTINCT >ENTITY,ACCOUNT,COST_CENTER,INTERCOMPANY,TRANSACTION_TYPE,PRODUCT,LOCATION,CHANN
Re: RE: large pl/sql table sucking up all memory on a server
Check profile option PRIVATE_SGA (available from 9i and needs resource_limit parameter to be true). Tanel. - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Monday, December 29, 2003 7:34 PM > we dont have that level of granularity. everyone developers out of the same DBA account(not my call). > > any parameter settings to limit the size of pl/sql tables? > > > > > From: "Jamadagni, Rajendra" <[EMAIL PROTECTED]> > > Date: 2003/12/29 Mon PM 12:14:24 EST > > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > > Subject: RE: large pl/sql table sucking up all memory on a server > > > > Assign the developer a "profile" that would do good. > > > > 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: Monday, December 29, 2003 12:00 PM > > To: Multiple recipients of list ORACLE-L > > > > > > One of our guys used a very large bulk collect into with a forall update. It sucked up all the swap space on our solaris box and noone could connect to it. So we had to bounce the server. > > > > I was under the impression that pl/sql tables go into the buffer cache and cannot go large than its size? Oracle typically holds your hand with memory usage issues. Are there any parameter settings I can use that limit the size of pl/sql tables? > > > > Or are they just dynamic arrays that can grow as large as you want. > > > > I know your supposed to use a 'limit' command on them. I didnt write it. I just dont want it to happen again. > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > -- > > Author: <[EMAIL PROTECTED] > > 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). > > > > ** > > 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. > > **4 > > -- > > 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). > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: <[EMAIL PROTECTED] > 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: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or
Re: RE: A performance problem
go to metalink and get 'trace analyzer' read the install instructions. It will extract wait events from your output. if your in 9i and up wait events are in the tkprof. i think you have to do a 10046 trace to get the wait events? not just a sql_trace. > > From: "Potluri, Venu (CT Appl Suppt)" <[EMAIL PROTECTED]> > Date: 2003/12/29 Mon PM 01:14:34 EST > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Subject: RE: A performance problem > > John, > > I can run this in our development environment and trace the job. But, the data is > quite a bit larger in production. I can't really take on a refresh/clone now and the > prodcution database is over 600GB > in size. We do have trace for the job which was available because the program > definition for this custom feed job has trace enabled in Apps. That trace file > doesn't have any wait event information. > This job does use db link. We know that for sure. I advised the developer who wrote > this custom feed job to tune it but that is never a satisfactory answer for them. > > > Venu Potluri > > -Original Message- > John Kanagaraj > Sent: Monday, December 29, 2003 12:35 PM > To: Multiple recipients of list ORACLE-L > > > Venu, > > Trying to solve the performance issue with a *single* job with Statspack is > like searching for a needle in a haystack, especially in an Oracle Apps > environment. You will need to trace the program *as it runs*, and if you > cannot do that right now, see if you can clone the database to a test system > and rerun it again. Btw, was this concurrent job an Oracle standard job or > was it a custom program? Any recent changes or patches to the environment? > Note that you *can* set trace (albeit just the plain vanilla level 1) on a > Concurrent job in 11i... As for the DB Link, can you determine if this > indeed does use a Dblink or it is from somewhere else... [See the problem > with Statspack?!] > > John Kanagaraj > DB Soft Inc > Phone: 408-970-7002 (W) > > Grace - Getting something we do NOT deserve > Mercy - NOT getting something we DO deserve > Click on 'http://www.needhim.org' for Grace and Mercy that is freely > available! > > ** The opinions and facts contained in this message are entirely mine and do > not reflect those of my employer or customers ** > > >-Original Message- > >From: Potluri, Venu (CT Appl Suppt) [mailto:[EMAIL PROTECTED] > >Sent: Monday, December 29, 2003 8:44 AM > >To: Multiple recipients of list ORACLE-L > >Subject: A performance problem > > > > > >I have a performance issue in our 11.5.5 Oracle Apps > >production environment (Oracle 8.1.7.4). A concurrent job that > >feeds into another production envrironment (Oracle 9.2) and > >runs less than an hour > >typically suddenly took almost 20 hours to finish. The users > >are as expected up in arms calling my head on a platter. I > >looked at the statspack report for the database this job ran on. > > > >The Top5 Wait events were: > > > >Top 5 Wait Events > >~ > > > >Wait Event Waits > >Time (cs)% Total Wt Time > >--- > > > >db file sequential read 15,978,336 > > 5,809,277 57.28 > >SQL*Net message from dblink 3,868 > >1,960,16819.33 > >db file scattered read 2,460,279 > >943,2529.30 > >control file sequential read 907,148 > > 300,572 2.96 > >pipe put2,033 > >208,8502.06 > > - > >-> cs - centisecond - 100th of a second > >-> ms - millisecond - 1000th of a second > >-> ordered by wait time desc, waits desc (idle events last) > > > > > > Avg > > > > Total Waitwait Waits > >EventWaitsTimeouts > >Time (cs)(ms)/txn > > -- > >--- -- - > >db file sequential read 15,978,336 0 > > 5,809,277 4970.3 > >SQL*Net message from dblink 3,8680 > >1,960,168 5068 0.2 > >db file scattered read 2,460,279 0 > > 943,2524149.4 > >control file sequential read 907,1480 > > 300,572355.1 > >pipe put 2,033 2,032 > > 208,850 1027 0.1 > > > > > > > >Breakdown of Wait time > > > >Event
RE: A performance problem
Venu You are getting some good advice, but here is a different idea for you that nobody has mentioned. You say that the job formerly took 1 hour and now takes 20 hours. You also mention that you have a development environment. If you can locate the main SQL statement(s), you could run an EXPLAIN PLAN in both your production and development environments. This is not nearly as good a way to diagnose performance problems as the other advice you are receiving, but it has the advantage of being quick (Oracle doesn't actually execute the statement), and may put you on the track of what has changed with the execution plan. When they are after your head, quick is good. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, December 29, 2003 12:15 PM To: Multiple recipients of list ORACLE-L John, I can run this in our development environment and trace the job. But, the data is quite a bit larger in production. I can't really take on a refresh/clone now and the prodcution database is over 600GB in size. We do have trace for the job which was available because the program definition for this custom feed job has trace enabled in Apps. That trace file doesn't have any wait event information. This job does use db link. We know that for sure. I advised the developer who wrote this custom feed job to tune it but that is never a satisfactory answer for them. Venu Potluri -Original Message- John Kanagaraj Sent: Monday, December 29, 2003 12:35 PM To: Multiple recipients of list ORACLE-L Venu, Trying to solve the performance issue with a *single* job with Statspack is like searching for a needle in a haystack, especially in an Oracle Apps environment. You will need to trace the program *as it runs*, and if you cannot do that right now, see if you can clone the database to a test system and rerun it again. Btw, was this concurrent job an Oracle standard job or was it a custom program? Any recent changes or patches to the environment? Note that you *can* set trace (albeit just the plain vanilla level 1) on a Concurrent job in 11i... As for the DB Link, can you determine if this indeed does use a Dblink or it is from somewhere else... [See the problem with Statspack?!] John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Grace - Getting something we do NOT deserve Mercy - NOT getting something we DO deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** >-Original Message- >From: Potluri, Venu (CT Appl Suppt) [mailto:[EMAIL PROTECTED] >Sent: Monday, December 29, 2003 8:44 AM >To: Multiple recipients of list ORACLE-L >Subject: A performance problem > > >I have a performance issue in our 11.5.5 Oracle Apps >production environment (Oracle 8.1.7.4). A concurrent job that >feeds into another production envrironment (Oracle 9.2) and >runs less than an hour >typically suddenly took almost 20 hours to finish. The users >are as expected up in arms calling my head on a platter. I >looked at the statspack report for the database this job ran on. > >The Top5 Wait events were: > >Top 5 Wait Events >~ > >Wait Event Waits >Time (cs) % Total Wt Time >--- > >db file sequential read15,978,336 > 5,809,277 57.28 >SQL*Net message from dblink3,868 >1,960,168 19.33 >db file scattered read 2,460,279 >943,252 9.30 >control file sequential read 907,148 > 300,572 2.96 >pipe put2,033 >208,850 2.06 > - >-> cs - centisecond - 100th of a second >-> ms - millisecond - 1000th of a second >-> ordered by wait time desc, waits desc (idle events last) > > > Avg > > Total Waitwait Waits >Event WaitsTimeouts >Time (cs)(ms) /txn > -- >--- -- - >db file sequential read15,978,336 0 > 5,809,277 4970.3 >SQL*Net message from dblink 3,868 0 >1,960,168 5068 0.2 >db file scattered read 2,460,279 0 > 943,2524149.4 >control file sequential read 907,148
Re: A performance problem
Over what time frame was the statspack report taken. The 5,809,277 cs of db file sequential read equates to 16+ hours and the 1,960,168 cs of SQL*Net message from dblink for 5+ hours. Of course, some of these waits could be concurrent rather than sequential. But, as John already pointed out, you can't analyze where a particular process spent its time and why it took so long from a statspack report (unless absolutely nothing else was happening in the DB, and even then not easily). You need to trace the problem process specifically. What changed? Did you re-analyze the tables involved recently? That could change the access plan for some sql in the job. Did the plan for the two statements change (presuming they are part of the problem job)? At 09:44 AM 12/29/2003, you wrote: I have a performance issue in our 11.5.5 Oracle Apps production environment (Oracle 8.1.7.4). A concurrent job that feeds into another production envrironment (Oracle 9.2) and runs less than an hour typically suddenly took almost 20 hours to finish. The users are as expected up in arms calling my head on a platter. I looked at the statspack report for the database this job ran on. The Top5 Wait events were: Top 5 Wait Events ~ Wait Event Waits Time (cs) % Total Wt Time --- db file sequential read 15,978,336 5,809,277 57.28 SQL*Net message from dblink 3,868 1,960,168 19.33 db file scattered read 2,460,279 943,2529.30 control file sequential read 907,148 300,572 2.96 pipe put2,033 208,8502.06 - -> cs - centisecond - 100th of a second -> ms - millisecond - 1000th of a second -> ordered by wait time desc, waits desc (idle events last) Avg Total Waitwait Waits Event Waits TimeoutsTime (cs)(ms) /txn -- --- -- - db file sequential read 15,978,336 0 5,809,277 4970.3 SQL*Net message from dblink 3,868 0 1,960,168 50680.2 db file scattered read 2,460,279 0 943,2524 149.4 control file sequential read907,1480300,572355.1 pipe put2,033 2,032208,850 1027 0.1 Breakdown of Wait time Event TimePercentage Avg. Wait Per Execute Per User Call Per Transaction db file sequential read 5809277 60.16% 0.360.68 8.228762.11 SQL*Net message from dblink 1960168 20.30% 506.77 0.232.77 2956.51 db file scattered read 943252 9.77% 0.380.111.34 1422.70 control file sequential read 300572 3.11% 0.330.040.43 453.35 pipe put208850 2.16% 102.73 0.02 0.30315.01 Here are the top SQL statements ordered by physical reads per execute: (these two happen to belong to this long running job) Statement ExecutesPhysical Reads Reads/Execute Hashs Value % of Total INSERT INTO ML_MGMT_MCS_FEED SELECT /*+ ORDERED INDEX(MGNAL ML_MGMT_DIST_NAT_AC_LKUP_X1) USE_MERGE(BAL) */SUBSTR(GLCC.SEGMENT3,1,6) CENTER,SUBSTR(MGNAL.GL11PROD_ACCOUNT,1,5) ACCT,SUBSTR(GLCC.SEGMENT2,1,10) NEW10,SUBSTR(GLCC.SEGMENT6,1,6) PRODUCT,SUBSTR(GLCC.SEGMENT5,1,4) TRANSTYPE,NVL(SUBSTR(MGNAL.GL11PROD_ACCOUNT,1,5 13 9737644 749049.54 1419451399 30.18 SELECT DISTINCT ENTITY,ACCOUNT,COST_CENTER,INTERCOMPANY,TRANSACTION_TYPE,PRODUCT,LOCATION,CHANNEL,FUTURE,PERIOD_NAME,SUM(BAL) BALAMOUNT,SUM(MTD) MTDAMOUNT FROM (SELECT DISTINCT ENTITY,ACCOUNT,COST_CENTER,INTERCOMPANY,TRANSACTION_TYPE,PRODUCT,LOCATION,CHANNEL,FUTURE,PERIOD_NAME,0 BAL,(ABS(NVL(MTD_TRANSACTION_DR_AMOUNT 30 5839191 194639.70 2733501134 48.27 I am not sure on how to interpret the SQL*Net message from dblink wait event. Obviously we have a db link on this database pointing to another production database into which the data is being fed. Does this wait event indicate a network issue more so than a database issue? What else jumps out here? Thanks. Venu Potluri Oracle Financials DBA -- Please see
Re: A performance problem
I'm not an Apps expert; but purely from the database perspective, you can enable 10046 events using dbms_support.start_sql_trace_in_Session( , , TRUE, TRUE). Hope that answers your question. Arup - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Monday, December 29, 2003 1:14 PM > John, > > I can run this in our development environment and trace the job. But, the data is quite a bit larger in production. I can't really take on a refresh/clone now and the prodcution database is over 600GB > in size. We do have trace for the job which was available because the program definition for this custom feed job has trace enabled in Apps. That trace file doesn't have any wait event information. > This job does use db link. We know that for sure. I advised the developer who wrote this custom feed job to tune it but that is never a satisfactory answer for them. > > > Venu Potluri > > -Original Message- > John Kanagaraj > Sent: Monday, December 29, 2003 12:35 PM > To: Multiple recipients of list ORACLE-L > > > Venu, > > Trying to solve the performance issue with a *single* job with Statspack is > like searching for a needle in a haystack, especially in an Oracle Apps > environment. You will need to trace the program *as it runs*, and if you > cannot do that right now, see if you can clone the database to a test system > and rerun it again. Btw, was this concurrent job an Oracle standard job or > was it a custom program? Any recent changes or patches to the environment? > Note that you *can* set trace (albeit just the plain vanilla level 1) on a > Concurrent job in 11i... As for the DB Link, can you determine if this > indeed does use a Dblink or it is from somewhere else... [See the problem > with Statspack?!] > > John Kanagaraj > DB Soft Inc > Phone: 408-970-7002 (W) > > Grace - Getting something we do NOT deserve > Mercy - NOT getting something we DO deserve > Click on 'http://www.needhim.org' for Grace and Mercy that is freely > available! > > ** The opinions and facts contained in this message are entirely mine and do > not reflect those of my employer or customers ** > > >-Original Message- > >From: Potluri, Venu (CT Appl Suppt) [mailto:[EMAIL PROTECTED] > >Sent: Monday, December 29, 2003 8:44 AM > >To: Multiple recipients of list ORACLE-L > >Subject: A performance problem > > > > > >I have a performance issue in our 11.5.5 Oracle Apps > >production environment (Oracle 8.1.7.4). A concurrent job that > >feeds into another production envrironment (Oracle 9.2) and > >runs less than an hour > >typically suddenly took almost 20 hours to finish. The users > >are as expected up in arms calling my head on a platter. I > >looked at the statspack report for the database this job ran on. > > > >The Top5 Wait events were: > > > >Top 5 Wait Events > >~ > > > >Wait Event Waits > >Time (cs) % Total Wt Time > >--- > > > >db file sequential read 15,978,336 > > 5,809,27757.28 > >SQL*Net message from dblink3,868 > >1,960,16819.33 > >db file scattered read 2,460,279 > >943,252 9.30 > >control file sequential read 907,148 > > 300,572 2.96 > >pipe put2,033 > >208,850 2.06 > > - > >-> cs - centisecond - 100th of a second > >-> ms - millisecond - 1000th of a second > >-> ordered by wait time desc, waits desc (idle events last) > > > > > >Avg > > > > Total Waitwait Waits > >Event Waits Timeouts > >Time (cs)(ms) /txn > > -- > >--- -- - > >db file sequential read15,978,336 0 > > 5,809,277 4 970.3 > >SQL*Net message from dblink 3,868 0 > >1,960,168 50680.2 > >db file scattered read 2,460,279 0 > > 943,2524 149.4 > >control file sequential read 907,1480 > > 300,5723 55.1 > >pipe put2,033 2,032 > > 208,850 10270.1 > > > > > > > >Breakdown of Wait time > > > >Event Time Percentage Avg. > >Wait Per Execute Per User Call Per Transaction > >db file sequential read 5809277 60.16% > >0.36 0.68 8.22 8762.11 > >SQL*Net message from dblink 1960168 20.30% 506.77 > > 0.23 2.77 2956.51 > >db file scattered read 943252 9.77% > >0.38 0.11 1.34 1422.70 > >control file sequential read 300572 3.11% 0.33 > > 0.04 0.43 453.35 > >pipe put 208850 2.16% 102.73 > > 0.02 0.30 315.01 > > > >Here are the top SQL statements ordered by physical reads per > >execute: (these two happen to belong to this long running job) > >Statement Executes Physical Reads > >Reads/Execute Hashs Value % of Total > >INSERT INTO ML_
Re: Exporting a partition with transport tablespace
Hope it improved your hit ratio :) - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Monday, December 29, 2003 1:59 PM > Yes, that's why I went to a memory improvement training few weeks ago ;) > > Tanel. > > - Original Message - > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > Sent: Monday, December 29, 2003 6:59 PM > > > > > > Your brain is getting full. You should stop studying so much. See what > it > > does to you? > > > > > -Original Message- > > > > > > Yep, I didn't remember the exact clause in the exchange > > > partition syntax. > > > > > > Tanel. > > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > -- > > Author: <[EMAIL PROTECTED] > > 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: Tanel Poder > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Arup Nanda 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: Obtain SQL Statement from audit
You haven't specified the Oracle version. If it's 9i, you could use Fine Grained Auditing (FGA) to get the exact SQLs. Hope this helps. Arup - Original Message - From: Mauricio Vélez To: Multiple recipients of list ORACLE-L Sent: Monday, December 29, 2003 1:39 PM Subject: Obtain SQL Statement from audit Hello everybody I am auditing select statements on one table, so I put the initialization parameter audit_trail = DB and I query the dba_audit_trail and sys.aud$ views and I can get information but I can't get the sql statement. The question is how can I retrieve the sql statement used to select on my table? Regards, Mauricio Vélez Do you Yahoo!?Yahoo! Photos - Get your photo on the big screen in Times Square
RE: A performance problem
Venu, You can work out the trace file name for Conc jobs. The OS process for a CM job is stored in the ORACLE_PROCESS_ID in FND_CONCURRENT_REQUESTS for that particular REQUEST_ID. You can then use this process number to generate the trace file in udump (normally $ORACLE_HOME/admin//udump/**.trc in the case of a UNIX based 11i DB server). Although this would have been just a SQL_TRACE (10046 Level 1), you can *still* run a tkprof on it to determine which SQL consumed the most time Hth, John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Disappointment is inevitable, but Discouragement is optional! ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** >-Original Message- >From: Potluri, Venu (CT Appl Suppt) [mailto:[EMAIL PROTECTED] >Sent: Monday, December 29, 2003 10:15 AM >To: Multiple recipients of list ORACLE-L >Subject: RE: A performance problem > > >John, > >I can run this in our development environment and trace the >job. But, the data is quite a bit larger in production. I >can't really take on a refresh/clone now and the prodcution >database is over 600GB >in size. We do have trace for the job which was available >because the program definition for this custom feed job has >trace enabled in Apps. That trace file doesn't have any wait >event information. >This job does use db link. We know that for sure. I advised >the developer who wrote this custom feed job to tune it but >that is never a satisfactory answer for them. > > >Venu Potluri > >-Original Message- >John Kanagaraj >Sent: Monday, December 29, 2003 12:35 PM >To: Multiple recipients of list ORACLE-L > > >Venu, > >Trying to solve the performance issue with a *single* job with >Statspack is >like searching for a needle in a haystack, especially in an Oracle Apps >environment. You will need to trace the program *as it runs*, >and if you >cannot do that right now, see if you can clone the database to >a test system >and rerun it again. Btw, was this concurrent job an Oracle >standard job or >was it a custom program? Any recent changes or patches to the >environment? >Note that you *can* set trace (albeit just the plain vanilla >level 1) on a >Concurrent job in 11i... As for the DB Link, can you determine if this >indeed does use a Dblink or it is from somewhere else... [See >the problem >with Statspack?!] > >John Kanagaraj >DB Soft Inc >Phone: 408-970-7002 (W) > >Grace - Getting something we do NOT deserve >Mercy - NOT getting something we DO deserve >Click on 'http://www.needhim.org' for Grace and Mercy that is freely >available! > >** The opinions and facts contained in this message are >entirely mine and do >not reflect those of my employer or customers ** > >>-Original Message- >>From: Potluri, Venu (CT Appl Suppt) [mailto:[EMAIL PROTECTED] >>Sent: Monday, December 29, 2003 8:44 AM >>To: Multiple recipients of list ORACLE-L >>Subject: A performance problem >> >> >>I have a performance issue in our 11.5.5 Oracle Apps >>production environment (Oracle 8.1.7.4). A concurrent job that >>feeds into another production envrironment (Oracle 9.2) and >>runs less than an hour >>typically suddenly took almost 20 hours to finish. The users >>are as expected up in arms calling my head on a platter. I >>looked at the statspack report for the database this job ran on. >> >>The Top5 Wait events were: >> >>Top 5 Wait Events >>~ >> >>Wait EventWaits >>Time (cs) % Total Wt Time >>--- >> >>db file sequential read 15,978,336 >> 5,809,27757.28 >>SQL*Net message from dblink 3,868 >>1,960,168 19.33 >>db file scattered read 2,460,279 >>943,252 9.30 >>control file sequential read 907,148 >> 300,572 2.96 >>pipe put2,033 >>208,850 2.06 >> >- >>-> cs - centisecond - 100th of a second >>-> ms - millisecond - 1000th of a second >>-> ordered by wait time desc, waits desc (idle events last) >> >> >> Avg >> >> Total Waitwait Waits >>Event WaitsTimeouts >>Time (cs)(ms) /txn >> -- >>--- -- - >>db file sequential read 15,978,336 0 >> 5,809,277 4970.3 >>SQL*Net message from dblink 3,868
Re: Exporting a partition with transport tablespace
Yes, that's why I went to a memory improvement training few weeks ago ;) Tanel. - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Monday, December 29, 2003 6:59 PM > > Your brain is getting full. You should stop studying so much. See what it > does to you? > > > -Original Message- > > > > Yep, I didn't remember the exact clause in the exchange > > partition syntax. > > > > Tanel. > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: <[EMAIL PROTECTED] > 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: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Obtain SQL Statement from audit
Hello everybody I am auditing select statements on one table, so I put the initialization parameter audit_trail = DB and I query the dba_audit_trail and sys.aud$ views and I can get information but I can't get the sql statement. The question is how can I retrieve the sql statement used to select on my table? Regards, Mauricio Vélez Do you Yahoo!? Yahoo! Photos - Get your photo on the big screen in Times Square
Re: RE: A performance problem
you mean a dbms_job? execute immediate 'turn trace on' inside what ever is being called. then check it. or just run it manually. > > From: "Potluri, Venu (CT Appl Suppt)" <[EMAIL PROTECTED]> > Date: 2003/12/29 Mon PM 01:09:29 EST > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Subject: RE: A performance problem > > The other database in on a different server. > > I looked at the statspack report for the other database, for the time period in > question. > > Top 5 Timed Events > ~~% Total > Event Waits Time (s)Ela > Time > --- > --- > db file sequential read 5,802,489 48,722 44.14 > free buffer waits 31,015 26,670 24.16 > db file parallel write 9,817 12,298 11.14 > CPU time 7,020 6.36 > write complete waits 6,301 5,584 5.06 > > We do have increase in amount of data but not enought to account for a 20-hour run. > > I am looking at the statspack report during the times this job previoulsy ran. > > How do I enable 10046 trace for sql executed by a concurrent job? I do have a trace > file for this job but it was obtained by turning trace on in Oracle Apps for this > job and doesn't contain any wait > event information. > > > > -Original Message- > [EMAIL PROTECTED] > Sent: Monday, December 29, 2003 12:09 PM > To: Multiple recipients of list ORACLE-L > > > the sqlnet is a network issue. talk to your SAs. is the other database on a > different server? work from there. > > your big one is your read. could mean your SGA is too small. is anything else > running at this time? > > are you sure there is an equivalent amount of work to do? are you sure there isnt > more data involved? > > do you have a previous statspack report to compare it to? > you also need to run a 10046 trace on the queries involved and see what they are > doing. > > maybe the plan changed do to a change in data or you dont have accurate statistics > or a parameter setting changed? > > > > From: "Potluri, Venu (CT Appl Suppt)" <[EMAIL PROTECTED]> > > Date: 2003/12/29 Mon AM 11:44:24 EST > > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > > Subject: A performance problem > > > > I have a performance issue in our 11.5.5 Oracle Apps production environment > > (Oracle 8.1.7.4). A concurrent job that feeds into another production envrironment > > (Oracle 9.2) and runs less than an hour > > typically suddenly took almost 20 hours to finish. The users are as expected up in > > arms calling my head on a platter. I looked at the statspack report for the > > database this job ran on. > > > > The Top5 Wait events were: > > > > Top 5 Wait Events > > ~ > > > > Wait Event Waits Time (cs) % > > Total Wt Time > > --- > > db file sequential read 15,978,336 5,809,277 > > 57.28 > > SQL*Net message from dblink 3,868 1,960,168 > > 19.33 > > db file scattered read 2,460,279 943,252 > >9.30 > > control file sequential read 907,148 300,572 > >2.96 > > pipe put2,033 208,850 > >2.06 > > - > > -> cs - centisecond - 100th of a second > > -> ms - millisecond - 1000th of a second > > -> ordered by wait time desc, waits desc (idle events last) > > > > > > Avg > > Total > > Waitwait Waits > > Event WaitsTimeouts Time (cs) > > (ms) /txn > > -- --- -- > > - > > db file sequential read 15,978,336 0 5,809,277 > > 4970.3 > > SQL*Net message from dblink 3,868 0 1,960,168 5068 > > 0.2 > > db file scattered read 2,460,279 0 943,252 > > 4149.4 > > control file sequential read907,1480300,572 > > 355.1 > > pipe put2,033 2,032208,850 >
Re: RE: large pl/sql table sucking up all memory on a server
it filled up the pga and then used 'swap' space on the hard drive. this filled up. didnt realize this was a feature. yeah, I know i can 'tell' them to do it. I was hoping to disallow it though. > > From: "Bobak, Mark" <[EMAIL PROTECTED]> > Date: 2003/12/29 Mon PM 01:24:25 EST > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Subject: RE: large pl/sql table sucking up all memory on a server > > Ryan, > > First off, PL/SQL tables have nothing to do with the buffer cache. The > buffer cache is part of the SGA (shared memory) and is used to buffer > blocks of database datafiles. That's all that will ever be in the buffer > cache. > > PL/SQL tables are memory constructs that are allocated from the PGA (process > private memory). When you connect to an instance, (in dedicated server mode) > the background process on the server side that's allocated to serve your > connection has memory associated w/ it. That's your PGA (and UGA, for that > matter.) > > The best way to deal with this is to educate the developers. Teach them that > the LIMIT clause is their friend. Are you on 9i? PGA_AGGREGATE_TARGET may > help. I'm not sure, I've never tried that experiment on 9i. What happens > when PGA memory demand due to PL/SQL tables exceeds PGA_AGGREGATE_TARGET? > I'll have to try that test > > Anyhow, hope that helps, > > -Mark > > PS In the future, if this happens again, you shouldn't have to bounce the > server. Just kill the background process that's eating all the memory. > When you do that, that developers session will die, and things should quickly > return to normal. > > > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: Mon 12/29/2003 11:59 AM > To: Multiple recipients of list ORACLE-L > Cc: > Subject: large pl/sql table sucking up all memory on a server > One of our guys used a very large bulk collect into with a forall update. It sucked > up all the swap space on our solaris box and noone could connect to it. So we had to > bounce the server. > > I was under the impression that pl/sql tables go into the buffer cache and cannot go > large than its size? Oracle typically holds your hand with memory usage issues. Are > there any parameter settings I can use that limit the size of pl/sql tables? > > Or are they just dynamic arrays that can grow as large as you want. > > I know your supposed to use a 'limit' command on them. I didnt write it. I just > dont want it to happen again. > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: <[EMAIL PROTECTED] > 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). > > > > > Content-Type: application/ms-tnef; The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification.
RE: A performance problem
John, I can run this in our development environment and trace the job. But, the data is quite a bit larger in production. I can't really take on a refresh/clone now and the prodcution database is over 600GB in size. We do have trace for the job which was available because the program definition for this custom feed job has trace enabled in Apps. That trace file doesn't have any wait event information. This job does use db link. We know that for sure. I advised the developer who wrote this custom feed job to tune it but that is never a satisfactory answer for them. Venu Potluri -Original Message- John Kanagaraj Sent: Monday, December 29, 2003 12:35 PM To: Multiple recipients of list ORACLE-L Venu, Trying to solve the performance issue with a *single* job with Statspack is like searching for a needle in a haystack, especially in an Oracle Apps environment. You will need to trace the program *as it runs*, and if you cannot do that right now, see if you can clone the database to a test system and rerun it again. Btw, was this concurrent job an Oracle standard job or was it a custom program? Any recent changes or patches to the environment? Note that you *can* set trace (albeit just the plain vanilla level 1) on a Concurrent job in 11i... As for the DB Link, can you determine if this indeed does use a Dblink or it is from somewhere else... [See the problem with Statspack?!] John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Grace - Getting something we do NOT deserve Mercy - NOT getting something we DO deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** >-Original Message- >From: Potluri, Venu (CT Appl Suppt) [mailto:[EMAIL PROTECTED] >Sent: Monday, December 29, 2003 8:44 AM >To: Multiple recipients of list ORACLE-L >Subject: A performance problem > > >I have a performance issue in our 11.5.5 Oracle Apps >production environment (Oracle 8.1.7.4). A concurrent job that >feeds into another production envrironment (Oracle 9.2) and >runs less than an hour >typically suddenly took almost 20 hours to finish. The users >are as expected up in arms calling my head on a platter. I >looked at the statspack report for the database this job ran on. > >The Top5 Wait events were: > >Top 5 Wait Events >~ > >Wait Event Waits >Time (cs) % Total Wt Time >--- > >db file sequential read15,978,336 > 5,809,277 57.28 >SQL*Net message from dblink3,868 >1,960,168 19.33 >db file scattered read 2,460,279 >943,252 9.30 >control file sequential read 907,148 > 300,572 2.96 >pipe put2,033 >208,850 2.06 > - >-> cs - centisecond - 100th of a second >-> ms - millisecond - 1000th of a second >-> ordered by wait time desc, waits desc (idle events last) > > > Avg > > Total Waitwait Waits >Event WaitsTimeouts >Time (cs)(ms) /txn > -- >--- -- - >db file sequential read15,978,336 0 > 5,809,277 4970.3 >SQL*Net message from dblink 3,868 0 >1,960,168 5068 0.2 >db file scattered read 2,460,279 0 > 943,2524149.4 >control file sequential read 907,1480 > 300,572355.1 >pipe put 2,033 2,032 > 208,850 1027 0.1 > > > >Breakdown of Wait time > >Event TimePercentage Avg. >Wait Per Execute Per User Call Per Transaction >db file sequential read5809277 60.16% >0.36 0.688.228762.11 >SQL*Net message from dblink 196016820.30% 506.77 > 0.232.772956.51 >db file scattered read 943252 9.77% >0.38 0.111.341422.70 >control file sequential read 3005723.11% 0.33 > 0.040.43453.35 >pipe put 208850 2.16% 102.73 > 0.02
RE: large pl/sql table sucking up all memory on a server
Ryan, First off, PL/SQL tables have nothing to do with the buffer cache. The buffer cache is part of the SGA (shared memory) and is used to buffer blocks of database datafiles. That's all that will ever be in the buffer cache. PL/SQL tables are memory constructs that are allocated from the PGA (process private memory). When you connect to an instance, (in dedicated server mode) the background process on the server side that's allocated to serve your connection has memory associated w/ it. That's your PGA (and UGA, for that matter.) The best way to deal with this is to educate the developers. Teach them that the LIMIT clause is their friend. Are you on 9i? PGA_AGGREGATE_TARGET may help. I'm not sure, I've never tried that experiment on 9i. What happens when PGA memory demand due to PL/SQL tables exceeds PGA_AGGREGATE_TARGET? I'll have to try that test Anyhow, hope that helps, -Mark PS In the future, if this happens again, you shouldn't have to bounce the server. Just kill the background process that's eating all the memory. When you do that, that developers session will die, and things should quickly return to normal. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Mon 12/29/2003 11:59 AM To: Multiple recipients of list ORACLE-L Cc: Subject:large pl/sql table sucking up all memory on a server One of our guys used a very large bulk collect into with a forall update. It sucked up all the swap space on our solaris box and noone could connect to it. So we had to bounce the server. I was under the impression that pl/sql tables go into the buffer cache and cannot go large than its size? Oracle typically holds your hand with memory usage issues. Are there any parameter settings I can use that limit the size of pl/sql tables? Or are they just dynamic arrays that can grow as large as you want. I know your supposed to use a 'limit' command on them. I didnt write it. I just dont want it to happen again. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: <[EMAIL PROTECTED] 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: large pl/sql table sucking up all memory on a server
3 million records in a forall statement. we are bringing on temps and you know how that goes... Im hoping I can set a parameter somewhere to keep anyone from bringing down a server. such as 'memory for pl/sql table area limit hit' errors out what he is doing. i guess not :( > > From: "Khedr, Waleed" <[EMAIL PROTECTED]> > Date: 2003/12/29 Mon PM 12:29:32 EST > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Subject: RE: large pl/sql table sucking up all memory on a server > > Does he still have a job? :) > > Was it one session or many of them? How many rows got bulk processed? > If it's one session that caused this, then it's either: vary badly designed, > there is memory leak, or the system is already short in memory! > > Waleed > > -Original Message- > Sent: Monday, December 29, 2003 12:00 PM > To: Multiple recipients of list ORACLE-L > > > One of our guys used a very large bulk collect into with a forall update. It > sucked up all the swap space on our solaris box and noone could connect to > it. So we had to bounce the server. > > I was under the impression that pl/sql tables go into the buffer cache and > cannot go large than its size? Oracle typically holds your hand with memory > usage issues. Are there any parameter settings I can use that limit the size > of pl/sql tables? > > Or are they just dynamic arrays that can grow as large as you want. > > I know your supposed to use a 'limit' command on them. I didnt write it. I > just dont want it to happen again. > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: <[EMAIL PROTECTED] > 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). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: <[EMAIL PROTECTED] 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: A performance problem
The other database in on a different server. I looked at the statspack report for the other database, for the time period in question. Top 5 Timed Events ~~ % Total Event Waits Time (s)Ela Time --- --- db file sequential read 5,802,489 48,722 44.14 free buffer waits 31,015 26,670 24.16 db file parallel write 9,81712,298 11.14 CPU time7,020 6.36 write complete waits 6,3015,584 5.06 We do have increase in amount of data but not enought to account for a 20-hour run. I am looking at the statspack report during the times this job previoulsy ran. How do I enable 10046 trace for sql executed by a concurrent job? I do have a trace file for this job but it was obtained by turning trace on in Oracle Apps for this job and doesn't contain any wait event information. -Original Message- [EMAIL PROTECTED] Sent: Monday, December 29, 2003 12:09 PM To: Multiple recipients of list ORACLE-L the sqlnet is a network issue. talk to your SAs. is the other database on a different server? work from there. your big one is your read. could mean your SGA is too small. is anything else running at this time? are you sure there is an equivalent amount of work to do? are you sure there isnt more data involved? do you have a previous statspack report to compare it to? you also need to run a 10046 trace on the queries involved and see what they are doing. maybe the plan changed do to a change in data or you dont have accurate statistics or a parameter setting changed? > > From: "Potluri, Venu (CT Appl Suppt)" <[EMAIL PROTECTED]> > Date: 2003/12/29 Mon AM 11:44:24 EST > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Subject: A performance problem > > I have a performance issue in our 11.5.5 Oracle Apps production environment (Oracle > 8.1.7.4). A concurrent job that feeds into another production envrironment (Oracle > 9.2) and runs less than an hour > typically suddenly took almost 20 hours to finish. The users are as expected up in > arms calling my head on a platter. I looked at the statspack report for the database > this job ran on. > > The Top5 Wait events were: > > Top 5 Wait Events > ~ > > Wait EventWaits Time (cs) % > Total Wt Time > --- > db file sequential read 15,978,336 5,809,277 > 57.28 > SQL*Net message from dblink 3,868 1,960,168 > 19.33 > db file scattered read 2,460,279 943,252 >9.30 > control file sequential read 907,148 300,572 >2.96 > pipe put2,033 208,850 >2.06 > - > -> cs - centisecond - 100th of a second > -> ms - millisecond - 1000th of a second > -> ordered by wait time desc, waits desc (idle events last) > > > Avg > Total Wait > wait Waits > Event WaitsTimeouts Time (cs)(ms) > /txn > -- --- -- > - > db file sequential read 15,978,336 0 5,809,277 > 4970.3 > SQL*Net message from dblink 3,868 0 1,960,168 5068 > 0.2 > db file scattered read2,460,279 0 943,252 > 4149.4 > control file sequential read 907,1480300,572 > 355.1 > pipe put 2,033 2,032208,850 1027 > 0.1 > > > > Breakdown of Wait time > > Event TimePercentage Avg. Wait Per > Execute Per User Call Per Transaction > db file sequential read 5809277 60.16% 0.360.68 > 8.228762.11 > SQL*Net message from dblink 1960168 20.30% 506.77 0.23 > 2.772956.51 > db file scattered read943252 9.77% 0.380.11 > 1.341
Re: RE: large pl/sql table sucking up all memory on a server
we dont have that level of granularity. everyone developers out of the same DBA account(not my call). any parameter settings to limit the size of pl/sql tables? > > From: "Jamadagni, Rajendra" <[EMAIL PROTECTED]> > Date: 2003/12/29 Mon PM 12:14:24 EST > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Subject: RE: large pl/sql table sucking up all memory on a server > > Assign the developer a "profile" that would do good. > > 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: Monday, December 29, 2003 12:00 PM > To: Multiple recipients of list ORACLE-L > > > One of our guys used a very large bulk collect into with a forall update. It sucked > up all the swap space on our solaris box and noone could connect to it. So we had to > bounce the server. > > I was under the impression that pl/sql tables go into the buffer cache and cannot go > large than its size? Oracle typically holds your hand with memory usage issues. Are > there any parameter settings I can use that limit the size of pl/sql tables? > > Or are they just dynamic arrays that can grow as large as you want. > > I know your supposed to use a 'limit' command on them. I didnt write it. I just > dont want it to happen again. > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: <[EMAIL PROTECTED] > 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). > > ** > 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. > **4 > -- > 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). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: <[EMAIL PROTECTED] 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: A performance problem
Venu, Trying to solve the performance issue with a *single* job with Statspack is like searching for a needle in a haystack, especially in an Oracle Apps environment. You will need to trace the program *as it runs*, and if you cannot do that right now, see if you can clone the database to a test system and rerun it again. Btw, was this concurrent job an Oracle standard job or was it a custom program? Any recent changes or patches to the environment? Note that you *can* set trace (albeit just the plain vanilla level 1) on a Concurrent job in 11i... As for the DB Link, can you determine if this indeed does use a Dblink or it is from somewhere else... [See the problem with Statspack?!] John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Grace - Getting something we do NOT deserve Mercy - NOT getting something we DO deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** >-Original Message- >From: Potluri, Venu (CT Appl Suppt) [mailto:[EMAIL PROTECTED] >Sent: Monday, December 29, 2003 8:44 AM >To: Multiple recipients of list ORACLE-L >Subject: A performance problem > > >I have a performance issue in our 11.5.5 Oracle Apps >production environment (Oracle 8.1.7.4). A concurrent job that >feeds into another production envrironment (Oracle 9.2) and >runs less than an hour >typically suddenly took almost 20 hours to finish. The users >are as expected up in arms calling my head on a platter. I >looked at the statspack report for the database this job ran on. > >The Top5 Wait events were: > >Top 5 Wait Events >~ > >Wait Event Waits >Time (cs) % Total Wt Time >--- > >db file sequential read15,978,336 > 5,809,277 57.28 >SQL*Net message from dblink3,868 >1,960,168 19.33 >db file scattered read 2,460,279 >943,252 9.30 >control file sequential read 907,148 > 300,572 2.96 >pipe put2,033 >208,850 2.06 > - >-> cs - centisecond - 100th of a second >-> ms - millisecond - 1000th of a second >-> ordered by wait time desc, waits desc (idle events last) > > > Avg > > Total Waitwait Waits >Event WaitsTimeouts >Time (cs)(ms) /txn > -- >--- -- - >db file sequential read15,978,336 0 > 5,809,277 4970.3 >SQL*Net message from dblink 3,868 0 >1,960,168 5068 0.2 >db file scattered read 2,460,279 0 > 943,2524149.4 >control file sequential read 907,1480 > 300,572355.1 >pipe put 2,033 2,032 > 208,850 1027 0.1 > > > >Breakdown of Wait time > >Event TimePercentage Avg. >Wait Per Execute Per User Call Per Transaction >db file sequential read5809277 60.16% >0.36 0.688.228762.11 >SQL*Net message from dblink 196016820.30% 506.77 > 0.232.772956.51 >db file scattered read 943252 9.77% >0.38 0.111.341422.70 >control file sequential read 3005723.11% 0.33 > 0.040.43453.35 >pipe put 208850 2.16% 102.73 > 0.020.30315.01 > >Here are the top SQL statements ordered by physical reads per >execute: (these two happen to belong to this long running job) >Statement ExecutesPhysical Reads >Reads/Execute Hashs Value % of Total >INSERT INTO ML_MGMT_MCS_FEED SELECT /*+ ORDERED INDEX(MGNAL >ML_MGMT_DIST_NAT_AC_LKUP_X1) USE_MERGE(BAL) >*/SUBSTR(GLCC.SEGMENT3,1,6) CENTER,SUBSTR(MGNAL.GL11PROD_ACCOUNT,1,5) >ACCT,SUBSTR(GLCC.SEGMENT2,1,10) >NEW10,SUBSTR(GLCC.SEGMENT6,1,6) >PRODUCT,SUBSTR(GLCC.SEGMENT5,1,4) >TRANSTYPE,NVL(SUBSTR(MGNAL.GL11PROD_ACCOUNT,1,5 > 13 9737644 >749049.54 1419451399 30
RE: large pl/sql table sucking up all memory on a server
Does he still have a job? :) Was it one session or many of them? How many rows got bulk processed? If it's one session that caused this, then it's either: vary badly designed, there is memory leak, or the system is already short in memory! Waleed -Original Message- Sent: Monday, December 29, 2003 12:00 PM To: Multiple recipients of list ORACLE-L One of our guys used a very large bulk collect into with a forall update. It sucked up all the swap space on our solaris box and noone could connect to it. So we had to bounce the server. I was under the impression that pl/sql tables go into the buffer cache and cannot go large than its size? Oracle typically holds your hand with memory usage issues. Are there any parameter settings I can use that limit the size of pl/sql tables? Or are they just dynamic arrays that can grow as large as you want. I know your supposed to use a 'limit' command on them. I didnt write it. I just dont want it to happen again. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: <[EMAIL PROTECTED] 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).
ref cursors and parsing
do ref cursors always do a hard parse? or can i get them to always do a soft parse? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: <[EMAIL PROTECTED] 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: Exporting a partition with transport tablespace
Probably he needs to set: pga_aggregate_target :) Regards, Waleed -Original Message- Sent: Monday, December 29, 2003 11:59 AM To: Multiple recipients of list ORACLE-L Your brain is getting full. You should stop studying so much. See what it does to you? > -Original Message- > > Yep, I didn't remember the exact clause in the exchange > partition syntax. > > Tanel. > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: <[EMAIL PROTECTED] 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: large pl/sql table sucking up all memory on a server
Assign the developer a "profile" that would do good. 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: Monday, December 29, 2003 12:00 PM To: Multiple recipients of list ORACLE-L One of our guys used a very large bulk collect into with a forall update. It sucked up all the swap space on our solaris box and noone could connect to it. So we had to bounce the server. I was under the impression that pl/sql tables go into the buffer cache and cannot go large than its size? Oracle typically holds your hand with memory usage issues. Are there any parameter settings I can use that limit the size of pl/sql tables? Or are they just dynamic arrays that can grow as large as you want. I know your supposed to use a 'limit' command on them. I didnt write it. I just dont want it to happen again. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: <[EMAIL PROTECTED] 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). ** 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. **4 -- 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).
Re: A performance problem
the sqlnet is a network issue. talk to your SAs. is the other database on a different server? work from there. your big one is your read. could mean your SGA is too small. is anything else running at this time? are you sure there is an equivalent amount of work to do? are you sure there isnt more data involved? do you have a previous statspack report to compare it to? you also need to run a 10046 trace on the queries involved and see what they are doing. maybe the plan changed do to a change in data or you dont have accurate statistics or a parameter setting changed? > > From: "Potluri, Venu (CT Appl Suppt)" <[EMAIL PROTECTED]> > Date: 2003/12/29 Mon AM 11:44:24 EST > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Subject: A performance problem > > I have a performance issue in our 11.5.5 Oracle Apps production environment (Oracle > 8.1.7.4). A concurrent job that feeds into another production envrironment (Oracle > 9.2) and runs less than an hour > typically suddenly took almost 20 hours to finish. The users are as expected up in > arms calling my head on a platter. I looked at the statspack report for the database > this job ran on. > > The Top5 Wait events were: > > Top 5 Wait Events > ~ > > Wait EventWaits Time (cs) % > Total Wt Time > --- > db file sequential read 15,978,336 5,809,277 > 57.28 > SQL*Net message from dblink 3,868 1,960,168 > 19.33 > db file scattered read 2,460,279 943,252 >9.30 > control file sequential read 907,148 300,572 >2.96 > pipe put2,033 208,850 >2.06 > - > -> cs - centisecond - 100th of a second > -> ms - millisecond - 1000th of a second > -> ordered by wait time desc, waits desc (idle events last) > > > Avg > Total Wait > wait Waits > Event WaitsTimeouts Time (cs)(ms) > /txn > -- --- -- > - > db file sequential read 15,978,336 0 5,809,277 > 4970.3 > SQL*Net message from dblink 3,868 0 1,960,168 5068 > 0.2 > db file scattered read2,460,279 0 943,252 > 4149.4 > control file sequential read 907,1480300,572 > 355.1 > pipe put 2,033 2,032208,850 1027 > 0.1 > > > > Breakdown of Wait time > > Event TimePercentage Avg. Wait Per > Execute Per User Call Per Transaction > db file sequential read 5809277 60.16% 0.360.68 > 8.228762.11 > SQL*Net message from dblink 1960168 20.30% 506.77 0.23 > 2.772956.51 > db file scattered read943252 9.77% 0.380.11 > 1.341422.70 > control file sequential read 300572 3.11% 0.330.04 > 0.43453.35 > pipe put 208850 2.16% 102.73 0.02 > 0.30315.01 > > Here are the top SQL statements ordered by physical reads per execute: (these two > happen to belong to this long running job) > Statement ExecutesPhysical Reads Reads/Execute Hashs > Value % of Total > INSERT INTO ML_MGMT_MCS_FEED SELECT /*+ ORDERED INDEX(MGNAL > ML_MGMT_DIST_NAT_AC_LKUP_X1) USE_MERGE(BAL) */SUBSTR(GLCC.SEGMENT3,1,6) > CENTER,SUBSTR(MGNAL.GL11PROD_ACCOUNT,1,5) > ACCT,SUBSTR(GLCC.SEGMENT2,1,10) NEW10,SUBSTR(GLCC.SEGMENT6,1,6) > PRODUCT,SUBSTR(GLCC.SEGMENT5,1,4) TRANSTYPE,NVL(SUBSTR(MGNAL.GL11PROD_ACCOUNT,1,5 > 13 9737644 749049.54 > 1419451399 30.18 > SELECT DISTINCT > ENTITY,ACCOUNT,COST_CENTER,INTERCOMPANY,TRANSACTION_TYPE,PRODUCT,LOCATION,CHANNEL,FUTURE,PERIOD_NAME,SUM(BAL) > BALAMOUNT,SUM(MTD) MTDAMOUNT FROM (SELECT DISTINCT > ENTITY,ACCOUNT,COST_CENTER,INTERCOMPANY,TRANSACTION_TYPE,PRODUCT,LOCATION,CHANNEL,FUTURE,PERIOD_NAME,0 > BAL,(ABS(NVL(MTD_TRANSACTION_DR_AMOUNT > 30 5839191
RE: Exporting a partition with transport tablespace
Your brain is getting full. You should stop studying so much. See what it does to you? > -Original Message- > > Yep, I didn't remember the exact clause in the exchange > partition syntax. > > Tanel. > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: <[EMAIL PROTECTED] 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).
large pl/sql table sucking up all memory on a server
One of our guys used a very large bulk collect into with a forall update. It sucked up all the swap space on our solaris box and noone could connect to it. So we had to bounce the server. I was under the impression that pl/sql tables go into the buffer cache and cannot go large than its size? Oracle typically holds your hand with memory usage issues. Are there any parameter settings I can use that limit the size of pl/sql tables? Or are they just dynamic arrays that can grow as large as you want. I know your supposed to use a 'limit' command on them. I didnt write it. I just dont want it to happen again. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: <[EMAIL PROTECTED] 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).
A performance problem
I have a performance issue in our 11.5.5 Oracle Apps production environment (Oracle 8.1.7.4). A concurrent job that feeds into another production envrironment (Oracle 9.2) and runs less than an hour typically suddenly took almost 20 hours to finish. The users are as expected up in arms calling my head on a platter. I looked at the statspack report for the database this job ran on. The Top5 Wait events were: Top 5 Wait Events ~ Wait Event Waits Time (cs) % Total Wt Time --- db file sequential read 15,978,336 5,809,277 57.28 SQL*Net message from dblink 3,868 1,960,168 19.33 db file scattered read 2,460,279 943,252 9.30 control file sequential read 907,148 300,572 2.96 pipe put2,033 208,850 2.06 - -> cs - centisecond - 100th of a second -> ms - millisecond - 1000th of a second -> ordered by wait time desc, waits desc (idle events last) Avg Total Wait wait Waits Event WaitsTimeouts Time (cs)(ms) /txn -- --- -- - db file sequential read 15,978,336 0 5,809,277 4 970.3 SQL*Net message from dblink 3,868 0 1,960,168 5068 0.2 db file scattered read 2,460,279 0 943,252 4149.4 control file sequential read907,1480300,572 355.1 pipe put2,033 2,032208,850 1027 0.1 Breakdown of Wait time Event TimePercentage Avg. Wait Per Execute Per User Call Per Transaction db file sequential read 5809277 60.16% 0.360.68 8.228762.11 SQL*Net message from dblink 1960168 20.30% 506.77 0.23 2.772956.51 db file scattered read 943252 9.77% 0.380.11 1.341422.70 control file sequential read 300572 3.11% 0.330.04 0.43453.35 pipe put208850 2.16% 102.73 0.02 0.30315.01 Here are the top SQL statements ordered by physical reads per execute: (these two happen to belong to this long running job) Statement ExecutesPhysical Reads Reads/Execute Hashs Value % of Total INSERT INTO ML_MGMT_MCS_FEED SELECT /*+ ORDERED INDEX(MGNAL ML_MGMT_DIST_NAT_AC_LKUP_X1) USE_MERGE(BAL) */SUBSTR(GLCC.SEGMENT3,1,6) CENTER,SUBSTR(MGNAL.GL11PROD_ACCOUNT,1,5) ACCT,SUBSTR(GLCC.SEGMENT2,1,10) NEW10,SUBSTR(GLCC.SEGMENT6,1,6) PRODUCT,SUBSTR(GLCC.SEGMENT5,1,4) TRANSTYPE,NVL(SUBSTR(MGNAL.GL11PROD_ACCOUNT,1,5 13 9737644 749049.54 1419451399 30.18 SELECT DISTINCT ENTITY,ACCOUNT,COST_CENTER,INTERCOMPANY,TRANSACTION_TYPE,PRODUCT,LOCATION,CHANNEL,FUTURE,PERIOD_NAME,SUM(BAL) BALAMOUNT,SUM(MTD) MTDAMOUNT FROM (SELECT DISTINCT ENTITY,ACCOUNT,COST_CENTER,INTERCOMPANY,TRANSACTION_TYPE,PRODUCT,LOCATION,CHANNEL,FUTURE,PERIOD_NAME,0 BAL,(ABS(NVL(MTD_TRANSACTION_DR_AMOUNT 30 5839191 194639.70 2733501134 48.27 I am not sure on how to interpret the SQL*Net message from dblink wait event. Obviously we have a db link on this database pointing to another production database into which the data is being fed. Does this wait event indicate a network issue more so than a database issue? What else jumps out here? Thanks. Venu Potluri Oracle Financials DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Potluri, Venu (CT Appl Suppt) 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
sql trace - forward attribution
I don't have the book with me right now, but I am obviously missing something in the "forward attribution" concept as it doesn't seem to help me in explanation of the following lines: WAIT #31: nam='SQL*Net message to client' ela= 1 p1=1413697536 p2=1 p3=0 WAIT #31: nam='SQL*Net message from client' ela= 692 p1=1413697536 p2=1 p3=0 WAIT #31: nam='SQL*Net message to client' ela= 1 p1=1413697536 p2=1 p3=0 FETCH #31:c=0,e=261,p=0,cr=7,cu=0,mis=0,r=4,dep=0,og=4,tim=2001475650589 WAIT #31: nam='SQL*Net message from client' ela= 2295 p1=1413697536 p2=1 p3=0 Shouldn't 1 + 692 + 1 (and possibly + 2295 ???) be less than 261? Oracle 9.2.0.4.0 on HP-UX 11.11 Thanks, Boris Dali. __ Post your free ad now! http://personals.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boris Dali 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: TNSPING VS. REGULAR PING..! WHY SUCH A DIFFERENCE
Asan si Hannah? At 05:54 AM 12/23/2003 -0800, you wrote: PING doesnt actually use a port number, as it's not TCP/IP based - it's a part of the ICMP protocol.. Mark -Original Message- Ganesh Raja Sent: 23 December 2003 13:40 To: Multiple recipients of list ORACLE-L TNSping uses the Port 1521 to communicate .. Not Sure which Port Ping will use.. HTH Regards, Ganesh R HP : (+65)9067-8474 Mail : [EMAIL PROTECTED] == All Opinions expressed are my own and do not in anyway reflect those of my employer == -Original Message- Mercadante, Thomas F Sent: Tuesday, December 23, 2003 20:54 PM To: Multiple recipients of list ORACLE-L ANd one more thing about Ping vs Tnsping when going thru a firewall. Some firewalls are setup to not allow a Ping to pass thru, but sql connections are allowed. So a Ping will return not found, while a tnsping will return ok. I have that situation here all over the freakin place. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, December 23, 2003 5:54 AM To: Multiple recipients of list ORACLE-L I came across just this last week with one of our monitoring tools. We set up an execution of a script that was using PING to check whether the status of a list of remote POS devices to make sure they were available. The collection worked fine - until we shutdown the POS device, and physically switched it off - and the PING still replied! The POS devices have UPS built in to them.. The client also thought it may have been the way that DNS/DHCP was set up via the router. It stumped me for a time.. ;) We use NETSVC now to check for the status of a service. Mark === Mark Leith | T: +44 (0)1905 330 281 Sales & Marketing | F: +44 (0)870 127 5283 Cool Tools UK Ltd | E: [EMAIL PROTECTED] === http://www.cool-tools.co.uk Maximising throughput & performance -Original Message- Jared Still Sent: 23 December 2003 04:34 To: Multiple recipients of list ORACLE-L Paul mentioned a few reasons for this. Another is that a ping does not get past the NIC. The ping is answered by software running on the card. You may have noticed at times that a ping is not a reliable method for determining if a server is still functioning. The OS can crash, but the NIC still responds to a ping. Tnsping on the other hand must get a response from Oracle Net service or daemon running on the server, a much longer path as Paul pointed out. HTH Jared On Mon, 2003-12-22 at 17:29, Murali Vallath wrote: > I have recently noticed in this one situation that there is a great difference between a tnsping vs a regular ping to the same server. > > for example this tnsping took about 270 ms which is strange and its consistent > > Used TNSNAMES adapter to resolve the alias > Attempting to contact (DESCRIPTION = (ADD > RESS_LIST = (load_balance = on) (failover = on) (ADDRESS = (PROTOCOL = TCP)(HOST > = myhost1.com)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = myhost2.com)( > PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = xyzdb) (FAILOVER_MODE = (TYPE > = SELECT) (METHOD = BASIC) (RETRIES = 20) (DELAY = 15 > OK (270 msec) > > and a ping to the same host > > Ping statistics for x.x.x.x: > Packets: Sent = 4, Received = 4, Lost = 0 (0% loss), Approximate > round trip times in milli-seconds: > Minimum = 61ms, Maximum = 70ms, Average = 67ms > > Why could there be such a difference? > > > > > > > > - > Do you Yahoo!? > Yahoo! Photos - Get your photo on the big screen in Times Square -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still 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). --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.553 / Virus Database: 345 - Release Date: 18/12/2003 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.553 / Virus Database: 345 - Release Date: 18/12/2003 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Leith INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services --
Re: DBA Unemployment
that isnt a reliable statistic. doesnt track people forced to take low paying temp jobs either. besides, anyone can tell you that the job market is bad, by just putting out a job ad. when you get 100-150 resumes for 1 job... its a tight labor market. > > From: DENNIS WILLIAMS <[EMAIL PROTECTED]> > Date: 2003/12/29 Mon AM 09:09:25 EST > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Subject: DBA Unemployment > > The U.S. government now tracks DBA jobs as an employment category. There are > 75,610 people who call themselves DBAs and 6.46% are unemployed. > Unfortunately they just started so we can't see what it was during the > dot-com bust. > > http://informationweek.com/story/showArticle.jhtml?articleID=17100148 > > > Dennis Williams > DBA > Lifetouch, Inc. > [EMAIL PROTECTED] > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: DENNIS WILLIAMS > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: <[EMAIL PROTECTED] 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: DBA Unemployment
Rachel Carmichael scribbled on the wall in glitter crayon: > 1) where are they getting the job classifications from? > > 2) how accurately are the job classifications reported? (i.e., what is > a "computer scientist" as opposed to a "programmer" as opposed to a > "software engineer"?) > > 3) what about the varied flavors of "computer consultant"? what about the various databases? is DBA generic? -- Bill "Shrek" Thater ORACLE DBA "I'm going to work my ticket if I can..." -- Gilwell song [EMAIL PROTECTED] All wiyht. Rho sritched mg kegtops awound? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thater, William 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: DBA Unemployment
1) where are they getting the job classifications from? 2) how accurately are the job classifications reported? (i.e., what is a "computer scientist" as opposed to a "programmer" as opposed to a "software engineer"?) 3) what about the varied flavors of "computer consultant"? --- DENNIS WILLIAMS <[EMAIL PROTECTED]> wrote: > The U.S. government now tracks DBA jobs as an employment category. > There are > 75,610 people who call themselves DBAs and 6.46% are unemployed. > Unfortunately they just started so we can't see what it was during > the > dot-com bust. > > http://informationweek.com/story/showArticle.jhtml?articleID=17100148 > > > Dennis Williams > DBA > Lifetouch, Inc. > [EMAIL PROTECTED] > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: DENNIS WILLIAMS > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael 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: DBA Unemployment
I'm 1 of those 6.46%. Ken Janusz, CPIM - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Monday, December 29, 2003 8:09 AM > The U.S. government now tracks DBA jobs as an employment category. There are > 75,610 people who call themselves DBAs and 6.46% are unemployed. > Unfortunately they just started so we can't see what it was during the > dot-com bust. > > http://informationweek.com/story/showArticle.jhtml?articleID=17100148 > > > Dennis Williams > DBA > Lifetouch, Inc. > [EMAIL PROTECTED] > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: DENNIS WILLIAMS > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: KENNETH JANUSZ 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: Correct way to accuse BCHR tuning method (Was: Hit ratio)
Yep. Simple example: Even though it seems to be sometimes a little on the 'random' side, the ELAPSED_TIME column on V$SQL in v9 is an absolute god send... Cheers Connor --- Mogens_Nørgaard <[EMAIL PROTECTED]> wrote: > And I think it's important to realise that ratios > are useless as a > starting point in the tuning process on any system, > not only Oracle. > Most OS'es and databases use not instrumented > correctly to deal with > response time measurements (makes you wonder: If > response TIME is what > matters, how can you then not measure exactly that - > time?) - so in the > other worlds (Unix, VMS, Windows, SQL Server, MySQL, > DB2) people > still beleive in the ratios because they have > nothing better. > > Mogens > > Anjo Kolk wrote: > > >BCHR tuning is useless as a starting point in the > tuning process. > > > >Anjo. > > > >-Original Message- > >Yong Huang > >Sent: Wednesday, December 24, 2003 6:09 PM > >To: Multiple recipients of list ORACLE-L > > > > > >[This message is not technical, but educational. > Readers interested in > >technical info only may want to skip] > > > >Hi, Cary and Gopal, > > > >My last message is misunderstood. Nowadays most > DBAs that still use > >buffer cache hit ratio as a primary performance > tuning method are those > >that rarely browse public forums. When we convince > them that's a wrong > >method, we should not say "Look. I can bump up BCHR > to an arbitrary > >value". If he doesn't think, he'll say "Indeed. If > I can get any value, > >it must be rubbish". But if he's a logical person > and thinks for a few > >minutes, he'll say "It's unfair to run that > choose_a_hit_ratio program > >to get an arbitrary hit ratio and say the method is > wrong, because you > >can use the same logic to write a program to get an > arbitrary library > >cache hit ratio, OS in-core inode cache hit ratio > or directory name > >cache hit..." > > > >My last message is not meant to revive the outdated > and probably never > >correct tuning method. Instead it's meant to let > oracle-l members know > >that when you need to convince those DBAs that > still use that method, > >you need to accuse the BCHR method for correct > reason, namely, BCHR does > >not contain sufficient information for tuning, not > because you can raise > >its value by constantly scanning a table in Oracle; > you won't be able to > >convince some stubbon DBAs who enjoy thinking in a > quiet place. > > > >I agree that "It's not the ratio that needs > condemning, it's the advice > >about..." What I disagree is the wrong educational > tool people on public > >forums have recently used again and again to show > the inadequacy of the > >BCHR tuning method. > > > >Yong Huang > > > >__ > >Do you Yahoo!? > >New Yahoo! Photos - easier uploading and sharing. > >http://photos.yahoo.com/ > > > > > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.net > -- > Author: =?ISO-8859-1?Q?Mogens_N=F8rgaard?= > 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). = Connor McDonald web: http://www.oracledba.co.uk web: http://www.oaktable.net email: [EMAIL PROTECTED] "GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day" Yahoo! Messenger - Communicate instantly..."Ping" your friends today! Download Messenger Now http://uk.messenger.yahoo.com/download/index.html -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?q?Connor=20McDonald?= 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).
DBA Unemployment
The U.S. government now tracks DBA jobs as an employment category. There are 75,610 people who call themselves DBAs and 6.46% are unemployed. Unfortunately they just started so we can't see what it was during the dot-com bust. http://informationweek.com/story/showArticle.jhtml?articleID=17100148 Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: pga_aggregate_target
Will look at what you suggest. The Oracle docs suggest you using a formula based on amount of system memory. However, this is not the only database on the server. >>> [EMAIL PROTECTED] 12/26/03 3:29:26 PM >>> Jeffrey, If you are simply looking at a conversion from 8i to 9iR2, and you're not going to be implementing a bunch of new 9i features (that could affect PGA usage), then it's probably reasonably straightforward. You can look at 'session pga memory' and 'session pga memory max' statistics in V$SESSTAT to get an idea of current PGA memory consumption in 8i. The 'max' statistic is probably more interesting in terms of total aggregate memory consumption
IBM Workload Manager (WLM)
Hi List, Does anyone have experience in using IBM's Workload Manager together with Oracle? I'm with a consulting client, where server-consolidation is intended. This involves appr. 180 Oracle databases. Some of them 1 instance/1 server, max. is now 22 instances/server. appr. No OPS is used. Versions: 7.3.4, 8.1.x. 60 servers are used now. Goal is to reduce the # of servers with 40-60%. Replacement of the server farm by a reduced number of high-end servers is one of the options, but starting with the consolidation process within the current range of servers is considered as well. All databases will be migrated to 8.1.7 before consolidation takes place. HW/OS is RS6000/AIX, both 4.3.3 and 5.2. Oracle 9i is still under investigation. Applications vvary from Peoplesoft to Siebel to tailor-made software. There is an in-house development department, so there are development, test and production databases. Servers have mixed use: I've seen servers running development, test AND production instances, not necessarily of the same application! Storage is EMC. One of the ideas is using IBM's WLM to prevent the instances on 1 server damaging each others performance. Not to slice too small HW among too much instances, but to prevent one instance from grabbing too much recources on the cost of other instances. >From IBM's doc's I got the following information: As from maintenance level 8 on AIX 4.3.3, and on 5.2, WLM allows manual assignment of processes to classes. Before this feature classes could only be assigned based on program-name or username, which is not too useful for oracle. Explicit oracle examples are mentioned in the doc. Nice to know, but does this actually work? Regards, Carel-Jan === If you think education is expensive, try ignorance. (Derek Bok) === -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Carel-Jan Engel 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: Correct way to accuse BCHR tuning method (Was: Hit ratio)
And I think it's important to realise that ratios are useless as a starting point in the tuning process on any system, not only Oracle. Most OS'es and databases use not instrumented correctly to deal with response time measurements (makes you wonder: If response TIME is what matters, how can you then not measure exactly that - time?) - so in the other worlds (Unix, VMS, Windows, SQL Server, MySQL, DB2) people still beleive in the ratios because they have nothing better. Mogens Anjo Kolk wrote: BCHR tuning is useless as a starting point in the tuning process. Anjo. -Original Message- Yong Huang Sent: Wednesday, December 24, 2003 6:09 PM To: Multiple recipients of list ORACLE-L [This message is not technical, but educational. Readers interested in technical info only may want to skip] Hi, Cary and Gopal, My last message is misunderstood. Nowadays most DBAs that still use buffer cache hit ratio as a primary performance tuning method are those that rarely browse public forums. When we convince them that's a wrong method, we should not say "Look. I can bump up BCHR to an arbitrary value". If he doesn't think, he'll say "Indeed. If I can get any value, it must be rubbish". But if he's a logical person and thinks for a few minutes, he'll say "It's unfair to run that choose_a_hit_ratio program to get an arbitrary hit ratio and say the method is wrong, because you can use the same logic to write a program to get an arbitrary library cache hit ratio, OS in-core inode cache hit ratio or directory name cache hit..." My last message is not meant to revive the outdated and probably never correct tuning method. Instead it's meant to let oracle-l members know that when you need to convince those DBAs that still use that method, you need to accuse the BCHR method for correct reason, namely, BCHR does not contain sufficient information for tuning, not because you can raise its value by constantly scanning a table in Oracle; you won't be able to convince some stubbon DBAs who enjoy thinking in a quiet place. I agree that "It's not the ratio that needs condemning, it's the advice about..." What I disagree is the wrong educational tool people on public forums have recently used again and again to show the inadequacy of the BCHR tuning method. Yong Huang __ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?ISO-8859-1?Q?Mogens_N=F8rgaard?= 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).
OT: Re: 10g new features question for beta testers
Oh yes, the RDD story is good. Carel-Jan and I were in Paris for Oracle World, sharing the horrendous costs of a hotel room, and having one final beer when the phone rang at 2 am. It was a Dane, and it was also one of Miracle's support customers. Since our motto for Miracle Support is "Call us anytime about anything" I of course answered the phone. He was slightly drunk after the big Oracle party, he was lost in a deserted area with no roads, couldn't remember the name of his hotel or the street or the Metro station near it, and he had just been robbed by six guys. Initially they had been two, and he fought like a real Dane, but when the four others came around he went down, and they took his wallet. So we asked him several times if he could describe anything he could see, and he finally told us "I can zee the Raifel Tover". Is it close? we asked. "Noo, it's weeery far awaaay" he answered. So now we knew he was in Paris. Finally, after much talking and him walking around for a while, he managed to stop a taxi, who then spoke French to Carel-Jan. Carel-Jan directed the taxi to our hotel, paid it, and led the good Dane to our room. There we washed off the blood & mud on him, gave him a beer, and put him to sleep on the floor. He thought he had received splendid support :-). I'm sure this could become a textbook case in some future book by Tom Peters and other management gurus. Mogens Carel-Jan Engel wrote: -Original Message- Mogens Nørgaard Sent: Sunday, December 21, 2003 10:14 PM To: Multiple recipients of list ORACLE-L It's the Best of Breed versus One Vendor debate, and there are pros and cons galore. The perfect scenario, of course, is when they combine, so one vendor delivers the best of everything. That's what we have with Microsoft, isn't it? ;-) : Office stuff, OS, Database, ERP, CRM, video player, what have you... Then on the Support side of things, it's indeed good to be able to call One Vendor Only... if that vendor is good at Support. If he isn't, you might be better off if you have more than one option for calling. Mogens Or, when you happen to be a RDD (Robbed Drunk Dane) in Paris, and you're able to call a particular Danish non-Vendor. That might be even better ;-) Regards, Carel-Jan === If you think education is expensive, try ignorance. (Derek Bok) === -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?ISO-8859-1?Q?Mogens_N=F8rgaard?= 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).
