The topic of deviating from the default 8KB block size in Oracle Database, or using multiple block sizes in a single database seems to surface every couple of months in the OTN forums, Oracle-L, comp.databases.oracle.server Usenet group, and similar discussion forums. I think that I understand why. A lot of information has been written that advocates using multiple block sizes in a single Oracle database, or using the largest possible block size to improve “full scan” or “range scan” performance. Such information is found in blogs, news articles, discussion forums, expert sites, books, and even Oracle’s download.oracle.com website. So, why do people ask questions about using larger than default block sizes or multiple block sizes in discussion forums if there are so many sources of information that say “just do it”. Well, chances are that the Google (or other search engine) search that found all of the sources recommending the use of non-standard settings also found several pages where people basically stated “stop, think, understand before making any changes.” See the Faulty Quotes 3 blog article.

So, you might be curious what my Google search found. Is it a best practice to implement multiple block sizes in a single database, and is it a best practice to move all of your indexes to a tablespace using the largest supported block size? (See chapter 1 of Expert Oracle Practices for a discussion on the topic of implementing “best practices”.) In the following quotes, I have attempted to quote the bare minimum of each article so that the quote is not taken too far out of context (I am attempting to avoid changing the meaning of what is being quoted).

“Oracle9i introduced a new feature that allowed a single instance of the database to have data structures with multiple block sizes. This feature is useful for databases that need the flexibility of using a small block size for transaction processing applications (OLTP); and a larger block size to support batch processing applications, decision support systems (DSS), or data warehousing. It can also be used to support more efficient access to larger data types like LOBs.”

“In Oracle databases 9i, 10g, and 11g, it is a best practice to use multiple block sizes; this allows you to tailor the block size to a specific type of access. Place tables and indexes in tablespaces sized (block size) according to access…”

“Larger oracle block sizes typically give fewer index levels and hence improved index access times to data. A single I/O will fetch many related rows and subsequent requests for the next rows will already be in the data buffer. This is one of the major benefits of a larger block size. Another benefit is that it will decrease the number of splits.”

dba-oracle.com/art_so_blocksize.htm

“Because the blocksize affects the number of keys within each index block, it follows that the blocksize will have an effect on the structure of the index tree. All else being equal, large 32k blocksizes will have more keys per block, resulting in a flatter index than the same index created in a 2k tablespace.”
“As you can see, the amount of logical reads has been reduced in half simply by using the new 16K tablespace and accompanying 16K data cache. Clearly, the benefits of properly using the new data caches and multi-block tablespace feature of Oracle9i and above are worth your investigation and trials in your own database.”

rampant-books.com/t_oracle_blocksize_disk_i_o.htm

“B-tree indexes with frequent index range scans perform best in the largest supported block size. This facilitates retrieval of as many index nodes as possible with a single I/O, especially for SQL during index range scans. Some indexes do not perform range scans, so the DBA should make sure to identify the right indexes”

praetoriate.com/t_oracle_tuning_data_buffer_pools.htm

“This is an important concept for Oracle indexes because indexes perform better when stored in large block size tablespaces. The indexes perform better because the b-trees may have a lower height and mode entries per index node, resulting in less overall disk overhead with sequential index node access.”

remote-dba.cc/s56.htm

“Indexes want large block sizes – B-tree indexes perform best in the largest supported block size and some experts recommend that all indexes should reside in 32K block size tablespaces. This facilitates retrieval of as many index nodes as possible with a single I/O, especially for SQL performing index range scans.”
“Many DBAs make their default db_block_size 32k and place indexes, the TEMP tablespace and tables with large-table full-table scans in it, using other block sizes for objects that require a smaller fetch size.”

remote-dba.net/unix_linux/multiple_block_sizes.htm

” Large blocks – Indexes, row-ordered tables, single-table clusters, and table with frequent full-table scans should reside in tablespaces with large block sizes.”

oracle-training.cc/s54.htm

“Larger block sizes are suitable for indexes, row-ordered tables, single-table clusters, and tables with frequent full-table scans. In this way, a single I/O will retrieve many related rows, and future requests for related rows will already be available in the data buffer.”

oracle-training.cc/oracle_tips_block_sizes.htm

“Indexes want large block sizes – Indexes will always favor the largest supported blocksize. You want to be able to retrieve as many index nodes as possible in a single I/O, especially for SQL that performs index range scans. Hence, all indexes should reside in tablespaces with a 32k block size.”

oracle-training.cc/t_oracle_multiple_buffers.htm

“One of the first things the Oracle9i DBA should do is to migrate all of their Oracle indexes into a large blocksize tablespace. Indexes will always favor the largest supported blocksize.”

“It’s pretty well established that RAC performs less pinging with 2k blocksizes”
“Large blocks gives more data transfer per I/O call.”
“Indexes like big blocks because index height can be lower and more space exists within the index branch nodes.”

dbapool.com/articles/040902.html

“Index Branches: Larger oracle block sizes typically give fewer index levels and hence improved index access times to data .This is one of the major benefits of a larger block size.”

“In Oracle9i and Oracle10g it is a good practice to use multiple block sizes, this allows you to tailor the block size to a specific type of access. Place tables and indexes in tablespaces according to access. For single block read type OLTP access, use 8k block sizes. For full table scan access such as with data warehouses use 16-32K block sizes. For index lookups use 8-16K block sizes. For indexes that are scanned or bitmap indexes, use 16-32K block sizes.”

“Because the blocksize affects the number of keys within each index block, it follows that the blocksize will have an effect on the structure of the index tree. All else being equal, large 32k blocksizes will have more keys per block, resulting in a flatter index than the same index created in a 2k tablespace… You can use the large (16-32K) blocksize data caches to contain data from indexes or tables that are the object of repeated large scans.”

dbazine.com/oracle/or-articles/burleson2

“Hence, one of the first things the Oracle9i database administrator will do is to create a 32K tablespace, a corresponding 32K data buffer, and then migrate all of the indexes in their system from their existing blocks into the 32K tablespace… Indexes will always favor the largest supported blocksize.”

statspackanalyzer.com/sample.asp

“You are not using large blocksizes for your index tablespaces. Oracle research proves that indexes will build flatter tree structures in larger blocksizes.”

“If you have large indexes in your database, you will need a large block size for their tablespaces.”
“Oracle provides separate pools for the various block sizes, and this leads to better use of Oracle memory.”

“… and using multiple block caches act as an intelligent cache differentiator that automatically leverage cache performance optimization. I have successfully tested, like many other DBAs and developers, that beyond any possible SGA tuning that using multiple-block-size database can certainly improve performance through this performance approach.”

“Simply by using the new 16K tablespace and accompanying 16K data cache, the amount of logical reads has been reduced by half. Most assuredly, the benefits of properly using the new data caches and multi-block tablespace feature of Oracle9i and later, are worth examination and trials in the DBA’s own database.”

“Objects that experience full scans and indexes with frequent range scans might benefit from being placed in a larger block size, with db_file_multiblock_read_count set to the block size for that tablespace.”

“At first, beginners denounced multiple block sizes because they were invented to support transportable tablespaces. Fortunately, Oracle has codified the benefits of multiple blocksizes, and the Oracle 11g Performance Tuning Guide notes that multiple blocksizes are indeed beneficial in large databases to eliminate superfluous I/O and isolate critical objects into a separate data buffer cache:

‘With segments that have atypical access patterns, store blocks from those segments in two different buffer pools: the KEEP pool and the RECYCLE pool…'”

————————-

Before deciding whether or not to implement a large block size (or a very small block size), or add either a larger or smaller than default block size tablespace, I suggest reviewing the following:

“But in most cases the administration overhead is much bigger than the performance benefit. You can easily end up with over- or undersized db_XXk_cache_size and the database can’t do anything about it. Then the performance will be better in some parts of the day and worse later on.”

“I would not recommend going into a system planning on using multiple blocksizes – they were invented for one thing, to transport data from a transactional system to a warehouse (where you might be going from 8k OLTP to 16/32k warehouse) and to be used only to extract/transform/load the OLTP data.”

“My block size is 4096 and my db_32k_cache_size=67108864
I want to create a tablespace with 32K and rebuild all indexes into this tablespace. These are
frequently used indexes. Do you think is there any benefit for using 32K block size in this scenerio”

“before you do something, you should have an identified goal in mind
so, tell us all – WHY would you do this? Don’t say “cause I read on some website it makes things super fast” (it doesn’t), tell us WHY you think YOU would derive benefit from this?
I do not think there is in general benefits to be gained from using multiple block size tablespaces – short of TRANSPORTING data from one block size to another for an ‘extract transform and load’ process.”

“BUT – do not use multiple block sizes for anything other than transporting data from database A to database B where the block size in A is different from B. No silver bullets with this ‘trick’, nothing you want to do in real life. The cases whereby multiple blocksizes are useful are typically limited to benchmarks, old wives tales, and very exceptionally rare conditions.”

“Use 8k. This is right in the middle, and won’t put you in an edge condition. Call it the Goldilocks block, not to small, not to big, just right.
For both OLTP and DSS, 8k is an optimal size. I use 8k, always.
There is minimal gains to be had in messing with block sizes. Having good db design and good execution plans is a better place to worry about performance.”

Summary of an OTN forums thread – what was likely the longest thread ever on the topic of block sizes (and very likely multiple block sizes in the same database) from June 2008. The message thread was too large to be supported on the new OTN software due to performance reasons. Fortunately, Jonathan Lewis obtained a copy of the thread content in a PDF file:http://jonathanlewis.wordpress.com/2008/07/19/block-sizes/

I posted a number of test cases in the above mentioned OTN thread where I simulated some of the activity in a data warehouse, and activity in an OLTP type database. To a large extent, the performance was very close to being identical in the databases with the default 8KB and 16KB tablespaces, with just a few exceptions. As I recall, the 16KB database encountered performance problems when a column with a NULL value was updated, and when a rollback was performed.

Below you will find the scripts to reproduce my test cases that appeared in the above mentioned OTN thread, and the performance results that I obtained. The OLTP test required roughly 10-12 hours to complete:Block Size Comparison (save with a .XLS extension and open with Microsoft Excel).

I guess the message is that you should verify that the swimming pool contains water before diving in head first.

So, what is the plan? There are a lot of resources available to help with understanding execution plans, some of which are much more effective than others. The most effective explanations that I have found are in the “Troubleshooting Oracle Performance” book, but the documentation (documentation2) is also helpful from time to time. There are also a lot of ways to look at execution plans, including directly examining V$SQL_PLAN in recent Oracle releases. This article shows some of the ways for generating execution plans, and some of the problems that might be encountered when attempting to obtain the “correct” execution plan.

For this blog article, the following script to create test tables will be used:

The above script creates four tables with 1,000,000 rows each. The first two tables have indexes on the primary key column due to the declared primary key column. Additionally, the script collects statistics for the first and third tables, but not the second and fourth tables (to help with a couple of demonstrations).

If you are running Oracle 10.1.0.1 or higher, you can use the DBMS_XPLAN.DISPLAY_CURSOR function to display execution plans. This function is called automatically, starting in Oracle 10.1.0.1, when AUTOTRACE is enabled in SQL*Plus. DBMS_XPLAN.DISPLAY_CURSOR is the preferred method for displaying execution plans, so we will start with that method.

First, we will generate an execution plan for a SQL statement with the OPTIMIZER_MODE set to ALL_ROWS:

The typical way to display an execution plan for a SQL statement that was just executed is as follows (note that the third parameter, the FORMAT parameter, could have also been set to NULL to produce the same result):

In the above, note that the calculated cost of the execution plan is 1893. Now, a little more experimentation, setting OPTIMIZER_MODE to FIRST_ROWS (Oracle assumes that only the first row will be retrieved, and the rest will likely be discarded):

In the above, notice that by changing the optimizer mode from ALL_ROWS to FIRST_ROWS_1 a new execution plan was created (the child number has increased by 1 and the Plan hash value has changed) that now uses a nested loops join, rather than a hash join, and the calculated cost has decreased. You might be wondering why Oracle did not pick this execution plan with the nested loops join, rather than the hash join when the OPTIMIZER_MODE was set to ALL_ROWS, since this plan has a lower calculated cost – we will leave that for another blog article (unless, of course, someone knows the answer and wants to share). Oddly, the estimated number of rows to be returned from table T3 has decreased when compared to the execution plan with the hash join, and the estimated execution time has also decreased. But, we really do not know anything about performance from just looking at the above plans. So, let’s repeat the test again, changing the SQL statement so that we are able to pass in ‘ALLSTATS LAST’ as the format parameter for the DBMS_XPLAN call:

The query completed in 4.92 seconds, retrieved 10 rows, and used 1,328 * 1,024 bytes of memory during the in-memory hash join. Wow, the full table scan of table T3 completed in 0.01 seconds while reading 37,125 blocks from disk. If each multiblock disk read were 128 blocks, that would be 290.04 multiblock reads in 0.01 seconds, for an average of 0.000034 seconds per 1MB multiblock read – who needs SSD when you can just read an execution plan incorrectly and make assumptions. 🙂 (Keep reading)

As shown above, with the FIRST_ROWS_1 optimizer mode, the query completed in 0.02 seconds. But, something is wrong. The execution plan shows that there were 37,125 blocks read from disk, just like the previous execution plan, which would mean that each of the 1MB physical reads required 0.000068 seconds if we only look at the A-Time column of ID 1. Who needs SSD when you can just read an execution plan incorrectly and make assumptions – oh, wait, I just said that.

The GATHER_PLAN_STATISTICS hint is helpful because it allows DBMS_XPLAN to output the actual execution statistics for the previous SQL statement. If you do not want to use that hint, it is also possible to change the STATISTICS_LEVEL to ALL at the session level level, as the following demonstrates:

Note that the change to the STATISTICS_LEVEL caused another hard parse for the SQL statement, and the child number is now listed as 2. Unlike the case where the GATHER_PLAN_STATISTICS hint was used, this time we see the actual time for the full table scan, rather than 0.01 seconds. If you look closely, you will also notice that the execution time increased from 4.92 seconds to 5.59 seconds as a result of changing the STATISTICS_LEVEL parameter.

Let’s try again with the FIRST_ROWS_1 value specified for OPTIMIZER_MODE.

Note again that there was a hard parse, and the child number increased by 1. This time, rather than completing in 0.02 seconds, the query required 4.79 seconds, with most of that time attributed to the full table scan. It is odd that the optimizer predicted that only 2 rows would be returned, rather than 9 or 10 rows.

Let’s try again, this time using bind variables rather than constants (literals):

Same execution plan as we saw earlier, with the same cardinality estimates due to bind variable peeking, except now there is a FILTER operation AT ID 1 with a predicate that states that the N1 bind variable must be less than or equal to the N2 bind variable – this is an automatically generated predicate caused by the BETWEEN syntax in the SQL statement.

Now we change the value of the second bind variable to a much larger value:

In the above, notice that there was no hard parse (same SQL_ID and child number as we saw earlier), and the E-Rows column is the same for the two DBMS_XPLAN outputs. The Used-Tmp column indicates that the hash join spilled to disk during the previous execution, using 43008 * 1024 bytes of space in the TEMP tablespace. Let’s repeat the test with the altered OPTIMIZER_MODE:

18.03 seconds to execute the SQL statement with OPTIMIZER_MODE set to FIRST_ROWS_1 and 18.48 seconds with OPTIMIZER_MODE set to ALL_ROWS (and 0.01 seconds compared to 5.60 seconds for the execution retrieving 10 rows). Obviously, this means that we should be running with OPTIMIZER_MODE set to FIRST_ROWS_1 if we want to optimize performance, right? Well, in short No. Maybe this will be something that will be investigated in a later blog article.

Now, turning to the unanalyzed tables. We modify the original SQL statement using bind variables to point to the two tables without up-to-date statistics on the tables and their indexes:

The only change here is that Oracle is now estimating that 10 rows will be returned rather than the 9 we saw earlier, and the note below the predicate information heading that states that dynamic sampling was used, oh – and the order of the row sources directly below the HASH JOIN line in the plan has changed (is this a problem?). Maybe dynamic sampling will be a topic for another blog article, but the topic is discussed in various books and articles on the Internet.

OK, this plan changed a bit from when the SQL statement referenced tables T1 and T3. The execution plan is no longer using a nested loops join – in fact it is using the same plan as was used when the OPTIMIZER_MODE was set to ALL_ROWS.

Oracle 9i and earlier Oracle releases had a default OPTIMIZER_MODE of CHOOSE, so let’s see what happens when we use that optimizer mode with the same two tables:

Note that the Note section indicates that the rule based optimizer was used, even though the documentation for Oracle 10.2 states that as of Oracle 10.1 the RULE based optimizer is no longer supported. Also note that the execution plan is now using a nested loops join, the FILTER operation no longer appears, and the full table scan is listed first below the NESTED LOOPS operation, just as it was when the OPTIMIZER_MODE was set to FIRST_ROWS_1 and the query accessed tables T1 and T3.

Let’s say that we are bored, and did not read chapter 15 by Pete Finnigan in the “Expert Oracle Practices” book… assume that column C1 contains a credit card number. Now for an experiment, we will retrieve all child cursors for SQL_ID cvq22z77c8fww with the bind variables that were submitted on the initial hard parse – be careful with who has access to this feature in a production environment, as it could expose sensitive information:

The above shows that the child cursors were created because of an optimizer mode mismatch (yes, we changed the OPTIMIZER_MODE). We could also check the bind variable definitions (not needed in this case because we used the undocumented PEEKED_BINDS format parameter with DBMS_XPLAN to see most of this information):

OK, now that we have moved off on a tangent, let’s return again to the topic of viewing execution plans. The above examples show the actual execution plan that was used, which may be different from that produced by explain plan. So, for fun, let’s look at the EXPLAIN PLAN FOR syntax (DBMS_XPLAN.DISPLAY is valid on Oracle 9.2.0.1 and higher):

Notice in the above that there are TO_NUMBER entries surrounding each of the bind variables in the Predicate Information section, even though those bind variables were declared as a NUMBER data type. The cost has also increased a bit.

Let’s use AUTOTRACE to see the execution plan (AUTOTRACE starting in Oracle 10.1.0.1 uses DBMS_XPLAN to output the formatted execution plan).

Note that the above plan is not necessarily the actual plan, even though we are looking at the actual runtime statistics. This could be confusing since we are seeing a rough guess for an execution plan with the actual execution statistics.

Now, another test. This time we will instruct Oracle to write the actual execution plan to a trace file every time Oracle must perform a hard parse. We will force a hard parse by adding a comment to the SQL statement:

Note in the above that we are able to see the actual execution plan, the peeked bind variables, the set of hints that will reproduce the execution plan, and a large number of normal and hidden optimizer parameters.

We could generate a 10053 trace at level 1 to determine why the above plan was selected, but we will skip that for now.

An example with a 10046 trace at level 4 (we must execute another SQL statement after the SQL statement under investigation so that the STAT lines for our SQL statement are written to the trace file):

We could, of course, just read the actual plan direct from the 10046 trace file, as well as the bind variable values and data types. But, we will use TKPROF instead (we will not be able to see the bind variable values when using TKPROF).

Of course, it seems to be a little too common that some people will try using EXPLAIN in TKPROF rather than just working with the Row Source Operation lines (what really happened – most of the time, unless of course you read this article):

Note that the resulting output could be very confusing if the “Row Source Operation” plan is completely different from the “Execution Plan”, since the first plan is the actual execution plan, while the second is essentially just an EXPLAIN PLAN FOR type of execution plan:

“work area: A private allocation of memory used for sorts, hash joins, and other operations that are memory-intensive. A sort operator uses a work area (the sort area) to perform the in-memory sort of a set of rows. Similarly, a hash-join operator uses a work area (the hash area) to build a hash table from its left input.”

There may be multiple active work areas in a single SQL statement. While not the original purpose of this blog article, the article does show how to see the amount of memory in use for active work areas. If you have a copy of the book “Troubleshooting Oracle Performance“, I highly recommend that you read pages 434 through 439 if you are curious about Oracle work areas. Those pages describe how hash joins work and provide a detailed description of work areas.

—

—

Back to the original question. While reading the plans, keep in mind that you are only looking at the optimizer’s estimates for the number of rows, time, memory usage, temp tablespace usage, and costs. If you are attempting to conclude which plan is faster/better based on the estimates in the first plan and an altered plan with a hinted cardinality estimate, you might be setting yourself up for failure. Note that the first plan has a calculated cost of about 45,000,000 while the second plan has a calculated cost of about 4,578,000. So obviously, the second plan is more efficient. Or is it? With the cardinality hint, the OP has effectively changed the number of rows that the optimizer expects to be returned from table A from roughly 1,241,000,000 to 10,000,000. Additionally, one should not directly compare the calcualted cost of one execution plan with that of a second execution plan. You probably should be thinking to yourself at this point: “Have you considered actually testing the performance?”

In the OTN thread Timur stated that both plans use the very same join order: B->A->C->D. Based on my understanding of execution plans, this is a correct statement, even though the plans look a bit different. Note that the OP was slightly incorrect in stating that the Predicate Information sections for the two plans were identical – the plan ID numbers should have been a bit different.

(Confession: I re-read the section of the book “Troubleshooting Oracle Performance” that discussed hash joins before providing the following response.) Essentially, the difference between the two plans is which table (or row source) is the build input, and which table is the probe input. The first table (or row source) listed below the words HASH JOIN is the source for the hash table (the optimizer typically tries to select the smaller estimated row source of the two row sources as the source for the hash table). The second table (or row source) is fully scanned, probing the generated hash table in search of a match. By artifically altering the optimizer’s estimated rows to be returned from table A, the OP has flipped which table (or row source) is the build input, and which table (or row source) is the probe input at each hash join – this could significantly increase, or significantly decrease, or have no impact on the amount of time required for the query to execute, the amount of memory used, or the amount of temp space needed.

My suggestion to the OP is to test the performance to see which execution plan is more efficient, rather than guessing. My blog article that is referenced above has SQL statements that may be used to see the number of work areas that are active at any point, as well as the amount of RAM and temp space in use. You could continue to guess about which plan is better, but why guess?

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.

Over the years I have seen a couple of interesting approaches to doing certain tasks with Oracle databases – something that makes me think – wow, that’s neat. Below are a couple of the items that I have found to be interesting, but I probably could not make interesting enough for a dedicated article.

First, an example that retrieves the DDL needed to recreate three tables and the indexes for those tables, output to a file. The definitions are complete with foreign keys, column constraints, and storage parameters. Note that in some cases it might be necessary to fix line wrapping problems in the resulting text file (specifically if a line is longer than 200 characters).

SET PAGESIZE 0
SET LONG 90000
SET LINESIZE 200
COLUMN OBJECT_DEF FORMAT A200
SPOOL 'GETMETA.SQL'
SELECT
DBMS_METADATA.GET_DDL('TABLE',TABLE_NAME,OWNER) OBJECT_DEF
FROM
DBA_TABLES
WHERE
TABLE_NAME IN ('T1','T2','T3')
UNION ALL
SELECT
DBMS_METADATA.GET_DDL('INDEX',INDEX_NAME,OWNER) OBJECT_DEF
FROM
DBA_INDEXES
WHERE
TABLE_NAME IN ('T1','T2','T3');
SPOOL OFF

——

Next, an example that creates a database link to another database. Note that the first example fails with some configurations – this is intentional in this example.

A Failed Attempt at a DB Link:

CREATE PUBLIC DATABASE LINK TEST_LINK CONNECT TO MY_USERNAME IDENTIFIED BY MY_PASS_HERE USING 'TEST';

“.WORLD”? – I don’t recall specifying that. I guess that we should consult the documentation.

“If the value of the GLOBAL_NAMES initialization parameter is TRUE, then the database link must have the same name as the database to which it connects. If the value of GLOBAL_NAMES is FALSE, and if you have changed the global name of the database, then you can specify the global name.”

Now, creating the database link correctly (note that a COMMIT or ROLLBACK should be used at some point after performing a query on a remote database):

In an earlier blog article I described several methods for enabling and disabling 10046 extended SQL traces, listed several keywords that are found in 10046 traces, and demonstrated output generated by my Toy Project for Performance Tuning as it processed the raw trace file.

Another way to enable a 10046 trace for a session is through the use of a logon trigger created by the SYS user. For example, the following trigger will enable a 10046 trace at level 12 for any program that begins with the letters MS or VB, even if the path to the program is included in the PROGRAM column of V$SESSION. With the use of DECODE, it is very easy to allow the trigger to enable tracing for an additional list of programs:

Obviously, if you create the trigger, you should drop the trigger when it is no longer needed using the following command.

DROP TRIGGER LOGON_10046_TRACE;

Let’s try creating a test trace file with Oracle Database 10.2.0.4. First, we need to create a couple of test tables with 10,000 rows each and collect statistics for the tables and primary key indexes:

From the above, the first execution required 9 consistent block gets, and 5 of those block gets involved reading the block from disk. The server sent 921 bytes to the client in 2 round trips, and 2 rows were retrieved. 2 round trips (we will see why later)? The second execution required 10,982 consistent block gets, and 404 of those involved physical reads. The server sent about 2.53MB to the client in 10,000 rows, using 201 round trips. Nice, but we can find out more about what happened.

We could process the trace file with tkprof using a command like this:

The above is a nice summary of what was found in the trace file for our specific test SQL statement, but what is it telling us? There were 2 parse calls (1 for each execution), and one of those parse calls resulted in a hard parse. There were 3 execution calls (I am only able to explain 2 of the execution calls). There were 203 fetch calls that retrieved a total of 10,002 rows – from this we could derive that on average the client fetched 49.27 rows per fetch call. All of the time for the execution happened on the fetch calls, which required 0.28 seconds of CPU time and a total of 0.38 clock seconds to execute. A total of 10,991 consistent gets were required and 409 blocks were read from disk.

The execution plan displayed is a little misleading, since it shows that only 2 rows were retrieved (note that if this example were executed on Oracle 11.1.0.6 or higher, the first and second executions of the SQL statement could have had different execution plans). An index range scan is performed on the index SYS_C0020548 (the primary key index for table T2) to locate all of the C1 values between 1 and 2 – note that the optimizer used transitive closure here since the restriction in the SQL statement was actually placed on the column C1 of table T1. The top line in the plan indicates that in total the query required 9 consistent gets, 5 physical block reads, and 0.019577 seconds to execute. The index range scan on SYS_C0020548 required 3 consistent gets, and 2 physical block reads were required to satisfy the 3 consistent gets. Table T2 required an additional 2 consistent gets and 1 physical block read. A nested loop operation was performed, driving into the primary key index for table T1 – this required an additional 4 consistent gets and 2 physical block reads. But, what about the second execution of the SQL statement?

The wait events show 409 waits on the “db file sequential read” wait event, which indicates physically reading 1 block at a time from disk – note that this exactly matches the “disk” column in the “fetch” line of the tkprof summary. Every block that had to be read from disk was read one block at a time, with an average read time of 0.000513 seconds per block read (the extremely fast average time likely indicate caching of blocks at the file system, RAID controller, SAN, or hard drive). There were 1200 waits on the “SQL*Net more data to client” wait, indicating that the SDU size was filled 1200 times when sending the data to the client computer.

We could also run the trace file though my Toy Project (below is one of 4 outputs from my program):

The above shows basically the same output as tkprof, just with greater resolution, both sets of bind variables, and both sets of execution plans.

We could also use any number of other 10046 trace file parsers including TRCANLZR (see Metalink Doc ID 224270.1), TVD$XTAT (see the book “Troubleshooting Oracle Performance”), ESQLTRCPROF (see the book “Secrets of the Oracle Database”), the Hotsos Profiler (Method R), OraSRP (www.oracledba.ru/orasrp/), or one of several other programs.

I typically either look at the output from my program or the raw 10046 trace file. That brings us to the raw 10046 trace file. So, what does the raw output of the trace file look like? Before diving into the raw trace file, let’s find a little information to help us later:

From the above output, the index on table T1 is named SYS_C0020547 and it has an OBJECT_ID of 114210. The index on table T2 is named SYS_C0020548 and it has an OBJECT_ID of 114211. Table T1 has an OBJECT_ID of 114209, and table T2 has an OBJECT_ID of 114207. Now on to a portion of the raw 10046 trace file:

From the above, we see that the first parse was a hard parse that required 0 CPU seconds and 0.001113 clock seconds. Additionally, two bind variables were passed in. A level 4 or level 12 10046 extended SQL trace file will include the submitted bind variable values, as shown above. It is possible to use the following list to decode the bind variable data type (oacdty), in the process determining that the bind variables are in fact, defined as numbers. See Metalink Doc IDs 67701.1 and 154170.1, the Oracle OCI documentation, or Julian Dyke’s site for a more complete list of datatype constants:

Above, we see the 5 physical block reads of blocks 1138316 and 1138318 of OBJECT_ID 114211 (index on table T2, SYS_C0020548), followed by a single block read of OBJECT_ID 114207 (table T2), and 2 single block reads of object 114210 (index on table T1, SYS_C0020547) – note that the final of the 5 physical block reads completed in 0.000236 seconds, which is about 20 times faster than the time it takes for 1 revolution of a 15,000 RPM hard drive platter. The first fetch call returned a single row, even though the array fetch size was explicitly set to 15 rows. That fetch required 5 consistent gets, which then required the 5 physical block reads. The 1 row was sent to the client which then fetched a second row (4963286588 – 4963286240 4963285717)/1,000,000 = 0.000348 0.000871 seconds later. The trace file continues:

Note that there was no hard parse this time. The first two fetches are complete at this point. Again, the first fetch returned a single row, while the second fetch returned 50 rows. Note the presence of the “SQL*Net more data to client” wait before the second fetch line printed – each of these lines indicates that the SDU size was filled on the previous send to the client. Notice that there was only a single physical block read of OBJECT_ID 114207 (table T2), requiring 0.002272 seconds, when fetching the first 51 rows (the other blocks were already in the buffer cache). The trace file continues below:

From the above, we see the execution plan for the second execution – this information was missing from the tkprof output. A hash join with two full table scans probably would have been more efficient than a nested loop join with index lookups, especially if the number of rows were larger. This is one of the potential problems with using bind variables, especially when bind variable peeking is enabled (by default in recent releases) – the execution plan is essentially locked after the initial hard parse. Oracle 11.1.0.6 introduced a feature known as adaptive cursor sharing that could potentially alter the plan on a future execution if Oracle senses that there will be significant changes in the number of rows returned when different bind variable values are submitted.

Incidentally, you may have noticed the keyword “oct” on the “PARSING IN CURSOR” lines in the above trace file. This keyword identifies the Oracle command type, which is related to the V$SESSION.COMMAND column and the V$SQL.COMMAND_TYPE column. Common command type values include:

There are a large number of initialization parameters that control the behavior of Oracle, and thus the performance of the database server. It would likely take hours (or many, many pages) to explain the ideal value of each of these parameters – the ideal parameter values are different for different databases, otherwise Oracle Database could simply default to the ideal parameters. Below are a couple of my notes on various parameters.

O7_DICTIONARY_ACCESSIBILITY: when set to true, permits non-sysdba users to query the data dictionary without explicitly granting permission to the users to view the data dictionary (setting this parameter to TRUE also allows the SYS user to connect without specifying AS SYSDBA). This parameter must be set to TRUE for certain applications’ functions to work correctly, but ideally should be set to FALSE, if possible. This parameter is set to FALSE by default on 9.0.1 and above. Changing the parameter requires bouncing the database.

COMPATIBLE: Sets the datafile level binary compatibility, allowing the database binaries to be rolled back to an earlier version and still access the database’s datafiles. Note that this parameter’s purpose is incorrectly described as controlling the query optimizer in a couple of Oracle related books.

PLSQL_CODE_TYPE: Watch out – there is a bug, at least on the Windows platform of 10.2.0.x where this setting will automatically change from interpreted to compiled when parameter values for other parameters are changed using Enterprise Manager Database Control.

PROCESSES and SESSIONS: Control the maximum number of client connections which may connect at any time. The SESSIONS parameter is typically 10 to 20 greater than the value for PROCESSES. The database must be bounced to change these parameters. See here for a related article.

RECYCLEBIN: Controls whether or not dropped tables and indexes will be saved to an area which will permit the objects to be recovered. If applications are using non-standard methods of determining objects belonging to a user (for example, directly querying SYS.OBJ$ and SYS.USER$), it is possible for the objects in the recylebin to be listed with the normal tables – attempting to assign permissions to objects in the recyclebin will result in the database returning errors to the client.

TIMED_STATISTICS: Should be set to TRUE to permit most forms of performance tuning, when set to FALSE, time deltas between events are not calculated. Setting this parameter to TRUE may impose a small performance penalty on database performance on some operating systems, but the penalty is typically small. This parameter defaults to TRUE when the STATISTICS_LEVEL parameter is set to TYPICAL or ALL.

DB_DOMAIN: Allows a database SID to be suffixed with a DNS style domain name. If set, it may cause problems when database links are created between databases (only one name is valid for a database link when the DB_DOMAIN parameter is set, a custom name cannot be assigned to the database link).

UNDO_MANAGEMENT: When set to AUTO, rollback segments are no longer used – instead, the system automatically manages undo segments.

UNDO_RETENTION: Specifies the suggested minimum number of seconds that undo information should remain available. Used to limit the frequency of “snapshot too old” error messages.

DB_RECOVERY_FILE_DEST_SIZE: Specifies a hard upper limit of the number of bytes available to store archived redo log, backups, and other items in the flash recovery area. Note that if files are removed from the flash recovery area using operating system commands, Oracle may incorrectly calculate the space used in the flash recovery area, potentially creating problems if copies of archived redo logs and/or backups are sent to the flash recovery area.

DB_RECOVERY_FILE_DEST: Specifies the location to be used for the flash recovery area.

DB_WRITER_PROCESSES: Do not increase from the default value of 1 unless the server has more than 8 CPUs. See here for a related article.

STATISTICS_LEVEL: Should be set to TYPICAL, do not leave the parameter set to ALL at the system-wide level as it will significantly slow down performance as more performance data must be collected for each SQL statement executed. The performance hit when set to ALL is more significant on Oracle 10g than it is on Oracle 11g.

SGA_MAX_SIZE: Specifies the absolute maximum size of memory allocated to items in the system global area (SGA), defaults to the value of SGA_TARGET if not set. Requires bouncing the database to change the parameter’s value.

SGA_TARGET: Specifies the suggested maximum amount of memory to be allocated to items in the system global area. The value may be manually increased to the value of SGA_MAX_SIZE without bouncing the database.

SHARED_POOL_SIZE: When the SGA_TARGET is specifies, sets the minimum amount of memory available for caching items in the shared pool (SQL statements, packages, etc.).

TRACE_ENABLED: Allows the database to create extended trace and 10053 trace files when session request those trace files to be generated. Typically default to a value of TRUE.

MEMORY_MAX_TARGET: Part of the new memory management parameters in Oracle 11g, specifies the absolute maximum amount of memory that may be used by Oracle.

MEMORY_TARGET: Part of the new memory management parameters in Oracle 11g, by default 80% of this memory will be allocated to the SGA and 20% to the PGA.

DB_CACHE_SIZE: Specifies the minimum amount of memory in bytes for the DEFAULT block buffer cache (KEEP and RECYCLE buffer cache sizes do not subtract from this value).

LOG_BUFFER: Specifies the amount of memory to be allocated for buffering redo information before it is written to the redo logs. 512KB to 1MB is typically sufficient on older Oracle releases, 10g and above may automatically set this parameter’s value to a size close to the memory granule size, which may be 16MB.

WORKAREA_SIZE_POLICY: When set to AUTO, allows the automatic allocation of memory for work areas from the memory specified for the PGA_AGGREGATE_TARGET.

SORT_AREA_SIZE: Has no effect when WORKAREA_SIZE_POLICY is set to AUTO (assuming dedicated sessions), specifies the amount of memory that may be used during a sorting or hashing operation when executing a SQL statement. HASH_AREA_SIZE defaults to twice this value.

SORT_AREA_RETAINED_SIZE: Has no effect when WORKAREA_SIZE_POLICY is set to AUTO, specifies the amount of memory that may be used after a sorting operation when the client is retrieving the results of the SQL statement. If the server has sufficient memory, set this value to the same as the value of SORT_AREA_SIZE to avoid unnecessarily spilling the results to the temp tablespace after the sort, but before the client starts retrieving the results.

OPEN_CURSORS: Specifies the maximum number of cursors that may be simultaneously open for each client’s session. Depending on the application connecting to the database, a value between 300 and 1000 might be a safe target if there is sufficient memory on the server.

SESSION_CACHED_CURSORS: On older Oracle releases, this parameter defaults to 0, and on more recent releases the parameter defaults to either 20 or 50 (this parameter controls the number of cached cursors per session). If the value of this parameter is set to a non-zero value and the same SQL statement is submitted at least 3 times, the SQL statement is added to the session cached cursors and remains open even when the client explicitly closes the cursor. The helps reduce the performance hit caused by soft parses when the client repeatedly submits the same SQL statement to be executed – on the next parse request Oracle does not need to search the library cache as would be needed during a soft parse. A value of 50 to 100 probably would be a good target, and if server memory permits, consider setting this parameter to a higher value, possibly 200.

CURSOR_SHARING: Starting with Oracle 8i, it is possible for the database server to automatically convert constants (literals) submitted in SQL statements to bind variables in order to reduce the number of hard parses. There are problems in patched Oracle 10.2.0.2 and 10.2.0.3 when this parameter is set to anything except EXACT (the October 2006 CPU for Oracle 10.2.0.2, for example, introduces problems when the CURSOR_SHARING parameter is set to FORCE – the problem may appear a couple of hours after the database is used in production).

CURSOR_SPACE_FOR_TIME: This parameter will be removed from future releases of Oracle as it is often misused (removed from 11.2.0.1?). When set to TRUE, this parameter causes Oracle to assume that required SQL statements will not be prematurely aged out of the library cache.

OPTIMIZER_INDEX_CACHING: Tells Oracle the approximate percentage of index blocks that remain in the buffer cache – primarily has an effect during nested loop joins, affects costing of nested loop joins and in-lists.

OPTIMIZER_INDEX_COST_ADJ: Artificially lowers the calculated cost of an index access to the percentage of the original specified by this parameter. Due to rounding problems, may cause the wrong index to be used if this parameter is set to too low of a value. If index access costs are calculated too high compared to full table scans (and fast full index scans), use CPU (system) statistics, if available, to increase the cost of full table scans, rather than using this parameter to decrease the cost of index accesses.

OPTIMIZER_FEATURES_ENABLE: When adjusted, automatically changes the value of many hidden initialization parameters to permit the query optimizer to behave similar to the optimizer in an earlier release of Oracle.

OPTIMIZER_SECURE_VIEW_MERGING: Defaults to TRUE on 10g, and may cause performance problems when set to TRUE when a user accesses a view created by another user, while the performance problem is not present for the view owner.

DB_FILE_MULTIBLOCK_READ_COUNT: Controls the maximum number of blocks that may be fetched in a single read operation during a full table scan or fast full index scan. Oracle 10.2.0.x and above is able to auto-set the DB_FILE_MULTIBLOCK_READ_COUNT, which will likely set the parameter to permit multi-block reads of 1MB.

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: