| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: v$sql problem
Hi!
> I think Oracle is doing latchless updates to these execution counts to
avoid
> contention/performance issues, that's how few of the incrementations
might
> get lost. However, losing nearly half of the updates seems somewhat
unlikely
> for a two session operation, so there might be some other issues.
I just wondered that is likelihood when EXECUTIONS can be lost.
To get the answer I have made stupid testcase again ;)
I have taken SQL
select COUNT(*) into vv from dual where 0=1;
end executed it 1 000 000 times in 4 parallel processes (whole testcase
source below).
Theoretically we need to get 4*1 000 000 executions I have made test twice on 2 different hosts
There are results.
TEST 1
TEST 2
Harvinder, as you can see likelihood is no so big ;)
I have suspicions ether you have bug in your test or Oracle have bug in
software.
What is HW specification you are running on?
Jurijs
9268222
CREATE OR REPLACE
PROCEDURE sys.testexec
as
vv NUMBER;
begin
for f in 1..1000000 loop
select COUNT(*) into vv from dual where 0=1;
end loop;
end;
/
$ cat testexec.sh
sqlplus "/ as sysdba" <<!
begin
testexec;
end;
/
exit
!
$ cat run_all.sh
time ./testexec.sh & time ./testexec.sh & time ./testexec.sh & time ./testexec.sh &
Tanel Põder <tanel.poder.003_at_mail.ee>
Sent by: oracle-l-bounce_at_freelists.org
08.07.2004 01:12
Please respond to oracle-l
To: <oracle-l_at_freelists.org>
cc:
Subject: Re: v$sql problem
> I am testing the insert performance into a table from 2 sessions both
> reading from same table. I am inserting in a batch of 1000 rows at a
> time and in 1 session we are inserting 1000 batches so total 1M rows
> inserted. Also we are doing commit after every 1000 rows. When I check
> the view v$sql and run statement from only 1 session it shows executions
> as 1000 (for 1000 batches) and increment in 1000 for each run. But when
> I run both sessions concurrently it only increases the execution by 1010
> instead of 2000. Is this the normal stats or it is not showing execution
> stats properly?
I think Oracle is doing latchless updates to these execution counts to
avoid
contention/performance issues, that's how few of the incrementations might
get lost. However, losing nearly half of the updates seems somewhat
unlikely
for a two session operation, so there might be some other issues.
Btw, what's your session_cached_cursors and cursor_space_for_time parameter's values? And which version/platform?
Tanel.
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Thu Jul 08 2004 - 08:47:44 CDT
 |
 |