Tom,
The request was for small direct writes.
I put the commit in the loop to deal with the ORA-12838 error that would follow
a second attempt to append.
Using commit write wait doesn't affect the number of direct path writes.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________
From: Tom Dale [tom.dale@xxxxxxxxxxxx]
Sent: 24 November 2015 09:00
To: Mark W. Farnham
Cc: Richard Amick; Jonathan Lewis; Oracle-L Freelists
Subject: Re: High IOPS direct small writes?
Thanks for the feedback Mark,
and maybe this should be a separate thread?
all I was trying to say, is the commit can be optimised if it is in a plsql
loop.
So if high small io is what is required, then a COMMIT WRITE WAIT might be what
was wanted, although you are bring more redo writes into the test.
If the database is in archivelog mode then these will be in redo anyway.
There is always Kevin Closson's slob to use?
I hope I am not go off topic, maybe I am checked the wrong counters?
noarchivelog test result
base counters
physical write total IO requests 4519
1 row selected.
user commits 0
redo synch writes 1
10,000 inserts and 'normal' commit.
2 rows selected.
PL/SQL procedure successfully completed.
physical write total IO requests 8507
1 row selected.
user commits 10000
redo synch writes 2
10,000 inserts and commit write
2 rows selected.
PL/SQL procedure successfully completed.
physical write total IO requests 50158
1 row selected.
user commits 20000
redo synch writes 10003
This is the test run in archivelog database
base counters
physical write total IO requests 33147085
1 row selected.
user commits 46072
redo synch writes 21123
10,000 inserts and 'normal' commit
2 rows selected.
PL/SQL procedure successfully completed.
physical write total IO requests 33149771
1 row selected.
user commits 56072
redo synch writes 21124
We see only one log synch, its been optimised
10,000 inserts and commit write
2 rows selected.
PL/SQL procedure successfully completed.
physical write total IO requests 33192427
1 row selected.
user commits 66072
redo synch writes 31125
commit write wait we get the behaviour we were expecting
2 rows selected.
----------------------------------------
test case
----------------------------------------
SET linesize 200
SET pagesize 0
COL name format a50
CREATE TABLE a(a INT)
/
SELECT name,value FROM v$sysstat WHERE name = 'physical write total IO requests'
/
SELECT sn.name<http://sn.name>,ms.value FROM v$mystat ms JOIN v$statname sn ON
ms.statistic# = sn.statistic# WHERE sn.name<http://sn.name> IN ('user
commits','redo synch writes')
/
BEGIN
FOR i IN 1..10000 LOOP
INSERT /*+append*/ INTO a VALUES(i);
COMMIT;
END LOOP;
END;
/
SELECT name,value FROM v$sysstat WHERE name = 'physical write total IO requests'
/
SELECT sn.name<http://sn.name>,ms.value FROM v$mystat ms JOIN v$statname sn ON
ms.statistic# = sn.statistic# WHERE sn.name<http://sn.name> IN ('user
commits','redo synch writes')
/
BEGIN
FOR i IN 1..10000 LOOP
INSERT /*+append*/ INTO a VALUES(i);
COMMIT WRITE WAIT;
END LOOP;
END;
/
SELECT name,value FROM v$sysstat WHERE name = 'physical write total IO requests'
/
SELECT sn.name<http://sn.name>,ms.value FROM v$mystat ms JOIN v$statname sn ON
ms.statistic# = sn.statistic# WHERE sn.name<http://sn.name> IN ('user
commits','redo synch writes')
/
On Mon, Nov 23, 2015 at 8:25 PM, Mark W. Farnham
<mwf@xxxxxxxx<mailto:mwf@xxxxxxxx>> wrote:
The commit parameters relate to management of how redo is batched by Log Writer
and whether the write is confirmed before control is returned to the client.
This thread was about generating direct I/O to a database file, which is being
driven by the 10,000 distinct insert appends in JL’s example.
Please explain more about the context in which you think a “COMMIT WRITE WAIT”
would be needed in PL/SQL to get useful feedback. I’d suggest a whole new
thread.
mwf
From: oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
[mailto:oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>] On
Behalf Of Tom Dale
Sent: Monday, November 23, 2015 10:36 AM
To: richa03@xxxxxxxxx<mailto:richa03@xxxxxxxxx>
Cc: Jonathan Lewis; Oracle-L Freelists
Subject: Re: High IOPS direct small writes?
Does the append hint remove the need for
COMMIT WRITE WAIT;
If its in plsql?
Tom
On Thu, Nov 19, 2015 at 7:58 PM, Rich
<richa03@xxxxxxxxx<mailto:richa03@xxxxxxxxx>> wrote:
Perfect - thanks!
On Thu, Nov 19, 2015 at 11:32 AM, Jonathan Lewis
<jonathan@xxxxxxxxxxxxxxxxxx<mailto:jonathan@xxxxxxxxxxxxxxxxxx>> wrote:
Unless I've misunderstood your question you could just use insert/append
differently:
begin
for i in 1..10000 loop
insert /*+ append */ into t1 select 1 from dual;
commit;
end loop;
end;
/
That's 10,000 single block direct path writes - requiring 10,000 blocks
allocated.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
[oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>] on behalf
of Rich [richa03@xxxxxxxxx<mailto:richa03@xxxxxxxxx>]
Sent: 19 November 2015 19:27
To: Oracle-L Freelists
Subject: High IOPS direct small writes?
Hi All,
This is 11.2.0.3 on AIX.
I know how to create direct IO load with insert+append, however, I'd like to
know how I can create high IOPS, direct small writes through the Oracle code -
preferably using SQL*Plus. I'd also prefer the target be a "normal object"
(like a table or index; not redo logs, LOB nor temp).
Any ideas?
TIA,
Rich
