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 nee
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 PROTECTE
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, a
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 depen
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
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
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
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
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 colu
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 ca
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]>
wr
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 sc
Hi Jared,
Here is an example -
The following part of unix script does not work-
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]>
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 u
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
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 t
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/deta
DBMS_XPLAN
Stephane Faroult
<[E
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
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
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
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 Die
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 WILLIA
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
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 deve
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)"
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
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
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: Mon
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: "Mu
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
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
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
>
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
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]>
>
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 rec
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
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
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, W
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
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
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 s
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
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
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-
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 a
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 m
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: <[EMAI
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?
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 m
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
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 15
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
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) wh
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
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
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
> start
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
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
go
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. Versi
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 yo
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
64 matches
Mail list logo