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,ms.value FROM v$mystat ms JOIN v$statname sn ON
ms.statistic# = sn.statistic# WHERE 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,ms.value FROM v$mystat ms JOIN v$statname sn ON
ms.statistic# = sn.statistic# WHERE 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,ms.value FROM v$mystat ms JOIN v$statname sn ON
ms.statistic# = sn.statistic# WHERE sn.name IN ('user commits','redo synch
writes')
/
On Mon, Nov 23, 2015 at 8:25 PM, Mark W. Farnham <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] *On Behalf Of *Tom Dale
*Sent:* Monday, November 23, 2015 10:36 AM
*To:* 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> wrote:
Perfect - thanks!
On Thu, Nov 19, 2015 at 11:32 AM, Jonathan Lewis <
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 [oracle-l-bounce@xxxxxxxxxxxxx] on
behalf of Rich [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
