6. Re-execute script that inserts 1000 rows from a different session. Runtime > 20 min. There were no indexes on the table.

Insert into table containing uncomitted DELETE should not be significantly slower than insert into table without DELETE.

My test results follow.

I executed the test on 64 bit Windows with a fairly slow disk system (little front end caching from the disk subsystem) running Oracle 11.1.0.7, 8KB block size, with the __DB_CACHE_SIZE currently floating at 0.9375GB due to a much larger DB_KEEP_CACHE_SIZE value. What do I see?

69,802 physical block reads, 69,793 consistent gets, 83,979 current mode gets, 18.03 seconds spent performing single block reads. This seems to be behaving similar to the bug that Jonathan Lewis found with ASSM 16KB block size tablespaces in 2008 when column values in existing rows were changed from NULL to a value. In that case, the current mode gets were the tipoff that there was a problem.

—

I repeated the test with an ASSM tablespace with 1MB uniform extents. The first insert performed 71,250 physical block reads, 71,206 consistent gets, 85,473 current mode gets, 18.85 seconds performing single block reads with an elapsed time of 21.53 and for some reason 0 CPU seconds (the next insert reported 3.59 CPU seconds).

I also repeated the test with a locally managed table with with 1MB uniform extents without ASSM: “SIZE 2G REUSE AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M SEGMENT SPACE MANAGEMENT MANUAL”. The results in the final test were a little disappointing. The totals from the script execution for all of the inserts:

The totals for all of the inserts performed 0 physical block reads, 1,051 consistent gets, 1,343 current mode gets, 0 seconds performing single block reads with an elapsed time of 0.374468 seconds (0.312034 of that was for parsing) and 0.265202 CPU seconds (0.234002 of that for parsing).

—

A couple of additional tests, since some of the posters in the Usenet thread reported different behavior.

We now have a new locally managed tablespace with 1MB extents not using ASSM, and another new tablespace using ASSM with autoallocated extents (my original test used an old ASSM autoallocate tablespace containing other data).

Each insert statement after the first reported an elapsed time of 0.14 to 0.15 seconds. __DB_CACHE_SIZE floated to 1,073,741,824

The execution time was about the same as with the larger __DB_CACHE_SIZE. Apparently only the first insert experienced a large number of ‘db file sequential read’ waits, totalling about 28 seconds based on the timing reported in SQL*Plus.

What if we flood the KEEP and DEFAULT buffer pools:

(Session 3 connected as SYS)

SET LINESIZE 150
SET PAGESIZE 10000
SPOOL C:\TABLES.SQL
SELECT
'SELECT * FROM '||OWNER||'.'||TABLE_NAME||' ORDER BY 1;' T
FROM
DBA_TABLES;
SPOOL OFF

Each insert statement after the first reported an elapsed time of 0.17 to 0.19 seconds. OK, that increased the time slightly, but not as much seen earlier.

Maybe it has to due with the process ID – luck of the draw regarding which blocks session 2 attempts to insert into due to the way ASSM attempts to reduce block contention? I repeated the test again using the same old ASSM tablespace which I used earlier – insert times for the second session where roughly 0.15 seconds each after the first insert completed. Of course, I bounced the database since the test run the day before, so maybe that has an impact?

The first couple of EXEC and STAT lines from the first of the most recent traces with the 6GB KEEP pool in effect:

This is partial output from the 10046 trace file captured yesterday and today, which targetted a pre-existing ASSM tablespace with roughly the same size default buffer cache in effect (note that the database was bounced between runs, and that may be a source of the time difference).
Yesterday:

In yesterday’s run, Oracle kept performing single block reads on exactly the same blocks for each insert statement (additional blocks were added one at a time on later inserts). Today this only happened for the first insert statement, with occasional single block reads after that point.

Jonathan had commented somewhere that ASSM is like freelists(16) (or maybe it was freelist groups (16)). The blocks selected for insert are dependent on the v$process.pid for the session (I have seen a couple of good descriptions of how this works, but cannot locate those descriptions right now). See the follow up to comment 10 here:http://jonathanlewis.wordpress.com/2009/01/14/books/

I suspect that this might have something to do with the problem I experienced yesterday, but not today (which free blocks are available to the session).

In this link, there are a couple of posts which describe this, or a similar problem:asktom.oracle.com Article 1
(April 24, 2003 by Jan van Mourik)
(December 21, 2004 by Steve)

On December 24, 2008 a thread was started on Oracle’s OTN forums titled “Performance degradation of repeated delete/inserts” that seems to describe a similar problem. The thread has since disappeared from Oracle’s OTN forums. In that thread someone told me “YOU CANNOT PROVE ANYTHING ABOUT ORACLE PERFORMANCE. EVER. NO EQUATIONS, NO PROOFS, NO WAY, NO HOW…“. I think that it was comments like this that eventually contributed to the thread being pulled from the OTN forums.

I believe that this blog article and the contents of the two Usenet threads demonstrates the potential value of test cases.

——————————————————

Take some time to read the two Usenet threads. I think that you will find that Jonathan Lewis and other contributors were able to identify the root cause of the performance difference between the tests with the ASSM and non-ASSM tablespaces.

Hints for Posting Code Sections in Comments

********************
When the spacing of text in a comment section is important for readability (execution plans, PL/SQL blocks, SQL, SQL*Plus output, etc.) please use a <pre> tag before the code section and a </pre> tag after the code section:
<pre>

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;
TODAY
---------
01-MAR-12

</pre>
********************
When posting test case samples, it is much easier for people to reproduce the test case when the SQL*Plus line prefixes are not included - if possible, please remove those line prefixes. This:

SELECT
SYSDATE TODAY
FROM
DUAL;

Is easier to execute in a test case script than this:

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;

********************
Greater than and Less than signs in code sections are often interpretted as HTML formatting commands. Please replace these characters in the code sections with the HTML equivalents for these characters: