Hi. I have a query that seems to be waiting for a PS
enqueu for about 2 days. When querying the
v$session_wait, I'm getting the following:
SID EVENT S-I-WT P1
P2 P3
- - -- --
--- --
48
, there are 264 waits in 132 pairs (1 for each
named cursor) of SQL*Net to/from.
The next activity is parsing of cursor #132, with the associated
activity (in fact, this cursor is reused quite heavily). Then
#127 is parsed and normal activity, then #128, then #17, etc.
I'm not quite certain how to intrepet
Title: Resolved - Row level security and latch waits
Thanks everyone for your input, the development email is rewriting their code using application contexts.
your help is greatly appreciated.
Raj
Rajendra dot
ah so i was right? wow... with all those posts. I figured i had been mistaken.
From: Jamadagni, Rajendra [EMAIL PROTECTED]
Date: 2003/08/26 Tue AM 09:49:34 EDT
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: Resolved - Row level security and latch waits
Thanks
Title: RE: Resolved - Row level security and latch waits
Everyone was right ... RTFM and STFW and listening to people on this list always helps.
We are making a better effort with design of the contexts. The developers have been redirected to appropriate sections on the FM.
Raj
- Original Message -
Policy function:
DPA42HP92.DP_PREDICATE_FUNCTION_PKG.DP_PREDICATE_FUNCTION
RLS view :
SELECT CURRENCY,CURRENCY_CODE,CURRENCY_DESC,CURRENCY_KEY FROM
DPA42HP9
2.DPR70_CURRENCY_D DPR70_CURRENCY_D WHERE (CURRENCY_CODE in (select
value_
v
from
- Original Message -
lets execute another query
SQL select count(*) from dpr70_gl_acct_balance_f;
COUNT(*)
--
2974
from v$SQL
SQL_TEXT
PARSE_CALLS EXECUTIONS
PROTECTED]
Date: 2003/08/20 Wed AM 11:21:59 EDT
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: Re: Re: Row level security and latch waits
My understanding was that the rls predicate was added
at parse time (hence the importance of the contexts
and avoiding things
Hi Nuno
I don't think this is the issue at all. The SQL in the function
that generates the predicate WILL use bind variables and that
is perfectly expected. That's why you don't see an increase in parses
on the SQL INSIDE the function.
That is correct. I should have posted the predicate
PROTECTED]
Date: 2003/08/20 Wed AM 11:21:59 EDT
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: Re: Re: Row level security and latch waits
My understanding was that the rls predicate was added
at parse time (hence the importance of the contexts
and avoiding
- Original Message -
- Use context values within predicates, as bind variables NOTE THIS
LINE.
None of that implies it's not appending a where clause like it
was explained.
It uses the context values (if that's the case) in a function
which then returns a string. That string
My understanding was that the rls predicate was added
at parse time (hence the importance of the contexts
and avoiding things like 'sysdate')
But also if I remember correctly, this behaviour was
changed in v9 to process the security function with
each execution (and hence probably increase the
, RajendraSent: Tuesday, August 19, 2003 11:49
AMTo: Multiple recipients of list ORACLE-LSubject: RE:
Row level security and latch waits
sorry this is 9202 and little less than 100
users.
Raj
so in 9i dbms_rls increases the soft parses?
From: Connor McDonald [EMAIL PROTECTED]
Date: 2003/08/20 Wed AM 11:21:59 EDT
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: Re: Re: Row level security and latch waits
My understanding was that the rls predicate was added
:
Sent by: Subject: Re: Re: Row level security
and latch waits
[EMAIL PROTECTED
some other people were saying if you use it with a 'context' it binds it.
is that accurate?
From: [EMAIL PROTECTED]
Date: 2003/08/20 Wed PM 04:09:26 EDT
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: Re: Row level security and latch waits
I got the 9.2 docs
Title: RE: Row level security and latch waits
Jack,
This is from TFM ...
manual
Starting from 9i, queries against Fine Grained Access enabled objects always execute the policy function to make sure most up to date predicate is used for each policy. For example, in case of the time based
:
[EMAIL PROTECTED] Subject: RE: Row level
security and latch waits
Sent
Hi,
With the way we have implemented FGAC, we do not have any parses
occurring with application context.
The application context does act like bind variables and I will try to
illustrate that with an example.
We set the context of the users logging in to a particular group/role
which gets
Title: RE: Row level security and latch waits
Sorry Jack,
I didn't preserve the title ... it was copied from an internal email. Also the _ parameters are never documented in official ora docs, Metalink is good for that.
Raj
-Original Message-
From: [EMAIL PROTECTED]
[mailto
Title: Row level security and latch waits
hi all,
in the latest code release, a group implemented RLS and since then spotlight is constantly flagging 'latch waits' in the system. Yesterday the latch waits were upwards of 90%.
Most active sessions seem to run the policy function defined
implemented RLS and since then spotlight
is constantly flagging 'latch waits' in the system. Yesterday the latch
waits were upwards of 90%.
Most active sessions seem to run the policy function defined as part of RLS.
The worst part was all this wait was only on one node, the other node was
healthy.
While
Title: Row level security and latch waits
I've
developped 3 functions for fined-grain access control and the users have tested
them.
There
are only 3-4users in the acceptance env. and I did not noticed
anything.
What
version are you running and how many users ?
We're
on 8172
Someday
security and latch waits
hi all,
in the latest code release, a group implemented RLS and since then spotlight
is constantly flagging 'latch waits' in the system. Yesterday the latch
waits were upwards of 90%.
Most active sessions seem to run the policy function defined as part of RLS
Title: Row level security and latch waits
sorry this is 9202 and little less than 100
users.
Raj
Rajendra dot Jamadagni at nospamespn dot
com All Views expressed in this email
are strictly personal. QOTD
Title: RE: Row level security and latch waits
Yup.
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
Title: RE: Row level security and latch waits
This is interesting do you know where it is documented quickly? I'll do a rtfm/stfw in the mean time.
Thanks
Raj
Rajendra dot Jamadagni at nospamespn dot com
im fairly certain its because DBMS_RLS doesnt use bind variables now. latch waits
indicates plus your 100 concurrent users.
you can check this by going to v$sqlarea and checking for similiar sql_text
statements. if they are there, then your not using bind variables. there is a query in
tom
PROTECTED]
Date: 2003/08/19 Tue PM 02:14:25 EDT
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: Re: Row level security and latch waits
RLS doesn't use bind variables? How then does Oracle treat the Application
Context variables that you include in the predicates generated
by: cc:
[EMAIL PROTECTED]Subject: Re: Row level security and
latch waits
.com
: Re: Row level security
and latch waits
.com
and latch waits
OK, I went and looked in the 8i Concepts manual. It seems pretty clear
that Application Context variables are used as bind variables. It may have
changed for 9i, but I can't see how or why.
Application Context
Application context facilitates the implementation of fine
PROTECTED]Subject: Re: Re: Row level
security and latch waits
.com
08/19/2003 01:39
PM
Please respond to
ORACLE-L
its just appending
of list
ORACLE-L [EMAIL PROTECTED]
Sent by: cc:
[EMAIL PROTECTED]Subject: Re: Re: Row level security
and latch waits
Not completely true.
If the dbwr is going to write a buffer, it will set a bit that the buffer is
being written. In the good old days, this meant that the buffer could be
changed until the block was written ('write complete waits'). However in 8.1,
cloning of buffers was introduced. So now
to raw
volumes for the redologs should put the log file sync waits down in the
top-n.
Indeed, the direct path writes have a neglible effect on overall response
time. I just want to get a good understanding of the 'direct path writes'.
sorts (disk) =4
physical writes direct = 2,444,555
physical
Symmetrix. There are hardly any wait-io's measurable on
AIX; the log file sync problem is not so much of a problem; moving to raw
volumes for the redologs should put the log file sync waits down in the
top-n.
Indeed, the direct path writes have a neglible effect on overall response
time. I
data model, etc.,
etc.) We're on EMC Symmetrix. There are hardly any wait-io's measurable
on
AIX; the log file sync problem is not so much of a problem; moving to raw
volumes for the redologs should put the log file sync waits down in the
top-n.
Indeed, the direct path writes have a neglible
Symmetrix. There are hardly any wait-io's measurable
on
AIX; the log file sync problem is not so much of a problem; moving to
raw
volumes for the redologs should put the log file sync waits down in the
top-n.
Indeed, the direct path writes have a neglible effect on overall
response
time
model,
etc.,
etc.) We're on EMC Symmetrix. There are hardly any wait-io's
measurable
on
AIX; the log file sync problem is not so much of a problem; moving to
raw
volumes for the redologs should put the log file sync waits down in
the
top-n.
Indeed, the direct path writes have
channels writing to disk, and as such, I/O is to
be expected. I generated statspack reports during the times the backup
runs, and the tablespace I/O summary does show high avg reads/ms. Also, I
see higher than normal total waits in the tablespace I/O summary. This, I
guess, should be the buffer
writing to disk, and as such, I/O is to
be expected. I generated statspack reports during the times the backup
runs, and the tablespace I/O summary does show high avg reads/ms. Also, I
see higher than normal total waits in the tablespace I/O summary. This, I
guess, should be the buffer busy wait
Quote:
Group by is still doing sorting, and is accounted in sorts stats (unless
an index scan wasn't used to get rows in desired order).
But yes, hash joins don't increase sort stats by themselves.
end of quote
I think you meant was usedin sted of wasn't used. Just like you said,
is's
Hi All,
Please help me tune this i/o related wait event. This is my 8.1.6 statspack
top-5 wait list:
Top 5 Wait Events
~ Wait %
Total
Event Waits Time (cs) Wt
Time
-5 wait list:
Top 5 Wait Events
~ Wait %
Total
Event Waits Time (cs) Wt
Time
---
direct path write
~ Wait %
Total
Event Waits Time (cs) Wt
Time
---
direct path write 304,867 35,925
,
Please help me tune this i/o related wait event. This is my 8.1.6 statspack
top-5 wait list:
Top 5 Wait Events
~ Wait %
Total
Event Waits Time (cs) Wt
Time
Waits Time (cs) Wt
Time
---
direct path write 304,867 35,925
49.83
log file sync 145,015 23,441
32.52
~ Wait %
Total
Event Waits Time (cs) Wt
Time
---
direct path write 304,867
Folks,
Say a session issues a read request, and finds another session already
reading the block into the buffer cache. If this session waits N ms on a
buffer busy waits event, does this N ms of wait get added to the read
times in v$filestat? Or is the readtim in v$filestat purely physical I/O
) with
an appropriate title: Resolving Intense and Random Buffer Busy Wait
Performance Problems. Buffer busy waits are usually a consequence of I/O
subsystem not being to provide enough throughput to the database. What can you
do with v$filestat? You can find where are your hot spots and fix the problem
and that wait
is computed as a write complete wait and not as BBW.
please correct me if I'm wrong.
thanks
HTH
Greetings
Diego Cutrone
Folks,
Say a session issues a read request, and finds
another session already
reading the block into the buffer cache. If this
session waits N ms on a
buffer busy
That's On 2003.07.29 19:59, Diego Cutrone wrote:
Another thing I think (I'm sorry to disagree with
Mladen on this) is that when DBWR hasn't finished
writing a buffer to the disk, and a session wants that
buffer in exclusive mode, there's a wait and that wait
is computed as a write complete wait
consistent backup. That is the reason
why RMAN doesn't need alter tablespace begin backup command.
To make the long story short, there is a note on metalink (Note:155971.1) with
an appropriate title: Resolving Intense and Random Buffer Busy Wait
Performance Problems. Buffer busy waits are usually
Performance Problems. Buffer busy waits are usually a consequence of I/O
subsystem not being to provide enough throughput to the database. What can
you
do with v$filestat? You can find where are your hot spots and fix the
problem.
--
Mladen Gogala
Oracle DBA
--
Please see the official ORACLE-L FAQ
Thanks a lot for your explanation Gogala.
Eagerly waiting for Steve's revised edition of Oracle Internals.
Regards,
Jp.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Prem Khanna J
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051
the
problem. This apps server generally has
about 2-3 JDBC connections to the database.
I just started looking at it today (it's not one of my databases) and when
it recurred I started checking wait events.
The top 10 wait events for about a 2 minute
period were as follows:
EVENT Delta
Waits Delta
the fact table with period_key
and mortgage_loan_key. The cluster factor of the index now came close to
the number of blocks per partition but On running the above query, the
waits on sequential file reads were still very significant.
Then we did one last thing were we arranged the temp
We were running a serial update on a fact table (45 mill rows) using the
old tech of
declare
cursor ...table a
begin
for c1rec in c1 loop
update fact
where period_key = c1rec.period_key and loan_key = c1rec.loan_key
commit every 10,000 rows
end loop;
end;
fACT table
When we looked at the cluster factor the index..it was close to the
number of rows..so we decided to rearrange the fact table with period_key
and mortgage_loan_key. The cluster factor of the index now came close to
the number of blocks per partition but On running the above query, the
waits
: Intresting Statistics -- DB FILE SEQUENTIAL READ waits
We were running a serial update on a fact table (45 mill rows) using the
old tech of
declare
cursor ...table a
begin
for c1rec in c1 loop
update fact
where period_key = c1rec.period_key and loan_key = c1rec.loan_key
table with period_key
and mortgage_loan_key. The cluster factor of the index now came close to
the number of blocks per partition but On running the above query, the
waits on sequential file reads were still very significant.
Then we did one last thing were we arranged the temp table(table
to rearrange the fact table with period_key
and mortgage_loan_key. The cluster factor of the index now came close to
the number of blocks per partition but On running the above query, the
waits on sequential file reads were still very significant.
Then we did one last thing were we arranged
PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Tuesday, June 17, 2003 11:49 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: sequential waits -- how to proceed
Thanks for your reply ...
I understand that it is a sql per row update but the same
update on UAT
environment works at a rate
and
then killed it.
On doing a tkprof on the trace file with waits set to Y, i get
call count cpuelapsed disk querycurrent
rows
--- -- -- -- -- --
--
Parse1 0.00 0.00 0 0
a pl/sql
procedure which does an update on a fact table. There is an unique
index on the fact, with clearly shows up in the explain plan for
udapte.
I ran 10046 event for a 18 min duration during this update process and
then killed it.
On doing a tkprof on the trace file with waits set
on a fact table. There is an unique
index on the fact, with clearly shows up in the explain plan for
udapte.
I ran 10046 event for a 18 min duration during this update process and
then killed it.
On doing a tkprof on the trace file with waits set to Y, i get
call count cpu
Does the UAT have the same data volume, same nr of rows and blocks in the
table being updated?
At 08:49 PM 6/17/2003 -0800, you wrote:
Thanks for your reply ...
I understand that it is a sql per row update but the same update on UAT
environment works at a rate of
about 2000 rows per sec. Though
encoded content removed -- binaries not allowed by ListGuru
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
Today on a busy production system I saw dozens of sessions waiting on buffer busy
waits events. p1 and p2 from v$session_wait were the same and mapped to one of our
very large unique indexes on a large table.
For all but one of the sessions, p3=130. Using Metalink and Steve Adams's website
on buffer busy
waits events.
p1 and p2 from v$session_wait were the same and mapped to one of our very large
unique indexes
on a large table.
For all but one of the sessions, p3=130. Using Metalink and Steve Adams's website,
it seems
p3=130 means that the block is being read by another
on the key, and updating the
rows with values pulled from these other tables. After setting the
db_cache_size parameter, the build script hangs in the update loop
and
the session is experiencing huge numbers of latch free waits on the
cache buffer chains.
We recreated the temporary table
configuration?
NB - when you say 'hanging' does you mean:
The CPU is working hard but the process
is not completing
The above is the condition I observed. There was a select cursor that
joined the temp table to several others that was showing the cache
buffer chain waits; the update cursor
NB - when you say 'hanging' does you mean:
The CPU is working hard but the process
is not completing
The above is the condition I observed. There was a select cursor
that
joined the temp table to several others that was showing the cache
buffer chain waits; the update cursor
We are having some performance problems at a client. I ran a 10046 trace,
level 8 on the DB for about 5 minutes of test operations. From the trace
files generated I find someting of the following:
SELECT Tm_trade_in_error.trade_id, Tm_trade_in_error.account_number,
Tm_trade_in_error.aps_groupid,
No, I don't think you're reading this wrong. May be you've had
a network problem or a NIC acting up?
-Original Message-
Sent: Tuesday, March 18, 2003 9:09 AM
To: Multiple recipients of list ORACLE-L
We are having some performance problems at a client. I ran a 10046 trace,
level 8 on the
This is trace data from 9.2.0.2+ (you can tell because of the new STAT
data). Therefore the timings are expressed in microseconds (0.01-second
units).
Technically, to answer your question, you have to know whether or not the
application invokes the fetch immediately after the execute. It
Title: RE: Excessive SQL*Net message from client waits
I recently worked on tracing and tuning a process where developer retrieved one row, did a bunch of pl/sql stuff and update ... lather, rinse and repeat.
There were a lot of SQL*Net message from/to client. I finally opened up
Bear in mind that when you are talking about a
load process, your client is another computer
program, and should not (you hope) need any
think time. This is the one case where the
SQL*Net message from client is a threat
rather than (as statspack puts it, I think) an
idle event.
Regards
) - and therefore even if you make
Oracle
run infinitely fast you will only improve the application overall by
10%.
Perhaps someone else can verify this.
Jonathan explained, quite well, why the waits are so high... It the
application spawns 10 sessions per user then each session will only
of 15 that was done and all show the
same SQL*Net waits being, on average, 90% or above of the total time.
The network guys did some testing and came back saying that the network
couldn't possibly be the problem (do they ever?). Here's what they said:
There are 0 Symptoms and Diagnoses that occur
But if one user spawns 10 sessions, then whilst
one session is being 'clicked' the other 9 are idle -
no matter how fast the user is being bounced from
one to the next - so on average every session is
going to be waiting for SQL*Net message from client
90% of the time.
Regards
Jonathan
Good point, but what if each user only has a single session?
Not that I've noticed this exact same situation here on one of our
Engineering support databases whose clients are Java, and I'm not wondering
if it has something to do with the application or if I can possibly speed it
up with tweaks
Title: RE: Excessive SQL*Net message from client waits
This is an idle wait event that just means the server is waiting to be given some work from the client. Looks to me like you won't be needing to do any tuning on this database.
-Original Message-
From: Karen Morton [mailto:[EMAIL
I'd start by being doubtful about anybody
being able to work so fast that the can avoid
a high percentage of time in 'sql*net from client' -
in fact, it the percentage was low (when the
client was a person at a terminal) I would
write myself a memo to check whether the
client code was executing
with one screen at a time. A session is spawned to do some initialization
stuff...this one sticks around and may see a bit more activity before the
logout...so I can see how this one would have the waits. But the other
spawned sessions connect, do something and disconnect. These spawned
sessions come
and writes was high, and the number of
log file sync waits was very high - with
a surprising max wait on log file sync.
The application seems to be committing
over-enthusiastically - which stresses the
log writer and log buffer latching anyway -
but there is also a lot of stress on the
I/O system
%.
Perhaps someone else can verify this.
Jonathan explained, quite well, why the waits are so high... It the
application spawns 10 sessions per user then each session will only be
called once per approx. 10 SQL statements. Reducing the number of sessions
will reduce the wait time on the report
]
14/03/2003 cc:
08:55Subject: RE: Excessive SQL*Net message
from client waits(Document
link: Mark Richard
%
If you want to see the whole profile please check at
www.morton-consulting.com/pdfs/sqlnetwaitstrace.pdf.
By the way, this single trace is one of 15 that was done and all show the
same SQL*Net waits being, on average, 90% or above of the total time.
The network guys did some testing and came back
100.0%
If you want to see the whole profile please check at
www.morton-consulting.com/pdfs/sqlnetwaitstrace.pdf.
By the way, this single trace is one of 15 that was done and all show the
same SQL*Net waits being, on average, 90% or above of the total time.
The network guys did some testing and came
waited (ela column)
From A - B (33 seconds)
sum of elapsed in DIRECT PATH READ = 394 ( i think this is cs)
From A - C (1 min 40 sec)
sum of elapsed in DIRECT PATH READ = 6251
The P1 of all of these waits point to the same file (#4 part of TEMP
tablespace)
Why should the change
-L
Subject: direct path read waits
All
We are doing some performance testing while moving from one server to
another. I was trying to time some index rebuilds and noticed
something
that I cant explain.
I am rebuilding an index on a 1 million row table. Lets say its on
tablespace
Tablespace C is on the I/O path as TEMP tablespace maybe???
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Thursday, February 20, 2003 1:41 PM
To: Multiple recipients of list ORACLE-L
Subject: direct path read waits
All
We are doing some
]
ds.com cc:
Sent by: Subject: RE: direct path read waits
To: Multiple recipients
of list ORACLE-L [EMAIL PROTECTED]
ds.com cc:
Sent by: Subject: RE: direct path
read waits
The P1 of all of these waits point to the same file (#4 part of TEMP
tablespace)
Why should the change in the target tablespace affect the time taken to
read from the TEMP tablespace?
TIA
Babu
_
This e-mail transmission and any attachments to it are intended solely for
the use
Babu,
You:
All the tablespaces are on different disks - Sorry I should
have mentioned
this in the original post itself...
Me:
Your answer was buried in the question itself. If the TEMP
tablespace had to contend with Tablespace C on the same 'disk' then such a
result is
expected.
Note
by: Subject: RE: direct path read waits
[EMAIL PROTECTED
:
Sent by: Subject: RE: direct path read waits
[EMAIL PROTECTED
1 - 100 of 308 matches
Mail list logo
