Redo Log Buffer – What is Wrong with this Quote?

24 06 2010

June 24, 2010

I am in the process of reading the June 2010 printing (fourth printing) of the “Oracle Performance Firefighting” book.  To say that I have stumbled upon one or two gems in the book is probably an understatement.  The book is filled with interesting statements – this time I found a couple of interesting paragraphs that describe several initialization parameters and their effect on the redo log buffer prior to Oracle 9i R2, and the current behavior.

Redo Log Buffer Prior to Oracle Database 9i R2, page 297:

Having a single redo allocation latch makes enforcing redo serialization very straightforward. But as you can imagine, having a single redo allocation latch also can become a point of contention. To reduce the likelihood of this, server processes hold the allocation latch just long enough to allocate redo log buffer space. There is also the instance parameter _log_small_entry_max_size, which is used to shift allocation latch activity onto one of the redo copy latches, as discussed in the ‘Redo Allocation Latch Contention’ section later in this chapter. To further reduce the contention possibilities, Oracle allows for multiple redo copy latches. The instance parameter _log_simultaneous_copies is used to control the number of redo copy latches.

Redo Log Buffer Oracle Database 9i R2 and Later, page 298:

By default, the number of redo strands is dynamic, but it can be made static by setting the hidden instance parameter _log_parallelism_dynamic to false. When Oracle is dynamically controlling the number of redo strands, the maximum number of strands is controlled by the hidden instance parameter _log_parallelism_max. The DBA can specifically set the number of redo strands via the hidden parameter _log_parallelism. The default number of redo strands is surprisingly low—perhaps two.

What, if anything, is wrong with the above quotes from the book (it is possible that nothing is wrong)?  Keep in mind that these blog articles are intended to generate discussion – be sure to check any and all comments attached to the articles in this series.  The comment section is where the heart of the blog article material in this series will be found.

The point of blog articles like this one is not to insult authors who have spent thousands of hours carefully constructing an accurate and helpful book, but instead to suggest that readers investigate when something stated does not exactly match what one believes to be true.  It could be that the author “took a long walk down a short pier”, or that the author is revealing accurate information which simply cannot be found through other resources (and may in the process be directly contradicting information sources you have used in the past).  If you do not investigate in such cases, you may lose an important opportunity to learn something that could prove to be extremely valuable.





Undo Segments – What is Wrong with this Quote?

21 06 2010

June 21, 2010

I located another interesting section of a paragraph in the June 2010 printing of the “Oracle Performance Firefighting” book.  From page 231:

“By default, Oracle tries to assign only one active transaction per undo segment. If each undo segment has an active transaction and if there is space available in the undo tablespace, Oracle will automatically create an additional undo segment. This usually takes care of the buffer busy waits. However, if there is no more room in the undo segment tablespace, multiple transactions will be assigned to each undo segment, and eventually undo segment header contention will result. The solution is to add another database file to your undo segment tablespace, thus enabling Oracle to create additional undo segments. It’s that simple.”

What, if anything, is wrong with the above quote from the book (it is possible that nothing is wrong)?  Keep in mind that these blog articles are intended to generate discussion – be sure to check any and all comments attached to the articles in this series.  The comment section is where the heart of the blog article material in this series will be found.

The point of blog articles like this one is not to insult authors who have spent thousands of hours carefully constructing an accurate and helpful book, but instead to suggest that readers investigate when something stated does not exactly match what one believes to be true.  It could be that the author “took a long walk down a short pier”, or that the author is revealing accurate information which simply cannot be found through other resources (and may in the process be directly contradicting information sources you have used in the past).  If you do not investigate in such cases, you may lose an important opportunity to learn something that could prove to be extremely valuable.





Database Writer Parameters – What is Wrong with this Quote?

18 06 2010

June 18, 2010

While reading the June 2010 printing of the “Oracle Performance Firefighting” book I keep stumbling across interesting sections of the book.  Here is an interesting section – I hope that I did not exclude too much of the surrounding section of the book, causing the paragraph to lose contextual meaning (I am trying to balance how much typing I need to do with how much needs to be quoted to avoid losing the meaning of the material).  From page 219:

“From an Oracle perspective, I start thinking of any instance parameter that may increase Oracle’s IO writing efficiency. For example, I would investigate looking for a way to increase the database writer’s batch write size. As I mentioned, there are version-specific ways to alter the database writer’s batch size. Investigate the _db_block_write_batch and the _db_writer_max_writes parameters. Also considering increasing the instance parameter _db_writer_max_scan_pct (the default may be 40, for 40%) or _db_writer_max_scan_cnt, as they determine how many LRU buffer headers a server process will scan before it signals the database writer to start writing. Increasing these parameters provides more time for the write list to build up, and therefore results in more blocks written per database writer IO request.”

What, if anything, is wrong with the above quote from the book?  Keep in mind that these blog articles are intended to generate discussion – be sure to check any and all comments attached to the articles in this series.  The comment section is where the heart of the blog article material in this series will be found.

The point of blog articles like this one is not to insult authors who have spent thousands of hours carefully constructing an accurate and helpful book, but instead to suggest that readers investigate when something stated does not exactly match what one believes to be true.  It could be that the author “took a long walk down a short pier”, or that the author is revealing accurate information which simply cannot be found through other resources (and may in the process be directly contradicting information sources you have used in the past).  If you do not investigate in such cases, you may lose an important opportunity to learn something that could prove to be extremely valuable.





_SMALL_TABLE_THRESHOLD Parameter and Buffer Cache – What is Wrong with this Quote?

17 06 2010

June 17, 2010

Warning: this article is certain to bring tears of pain to your eyes while you attempt to determine what is right and what is wrong.

While reading the June 2010 printing of the “Oracle Performance Firefighting” book I found a couple of interesting lines about the buffer cache and the effects, or lack there-of, of the _SMALL_TABLE_THRESHOLD parameter.  This quote is from page 208 (this is a long quote, required to not lose the context of the discussion topic - please excuse any typos):

“The single most radical departure from the modified LRU algorithm [introduced in Oracle Database 6, per the book] is known as midpoint insertion [introduced in Oracle Database 8.1.5, per the book]. Each LRU chain is divided into a hot and cold region. When a buffer is read from disk and a free buffer has been found, the buffer and buffer header replace the previous buffer and buffer header contents, and then the buffer header is moved to the LRU chain midpoint. Single-block read, multiblock read, fast-full scan, or full-table scan—it makes no difference. The buffer header is not inserted at the MRU end of the LRU chain, but rather at the midpoint…

Because the window scheme used in the modified LRU algorithm is no longer used, the hidden instance parameter _small_table_threshold became deprecated. However, in Oracle Database 11g, it is being used again, but for a different purpose. Starting with this version, the _small_table_threshold parameter is the threshold for a server process to start issuing direct reads.”

Before deciding what about the above is correct or incorrect, take a look at Metalink Doc ID 787373.1.  A couple of words from that document to encourage you to take a look at the original material:

“Applies to Version: 9.0.1.0 to 11.2.0.1.0: When loading objects and data into the buffer cache… best way to load for full scans. This functionality is called smallness logic and is driven by a hidden parameter called: _small_table_threshold. … Oracle will load the object via the buffer cache as this is more efficient than doing a direct read operation.”

Before deciding what about the above Metalink document is correct or incorrect, take a look at this AskTom article.  A couple of words from that article:

“no, you are not correct on your guess about the [_SMALL_TABLE_THRESHOLD] parameter, it controls the caching of the blocks (whether they are cached like a single block IO or cached as we cache full scan blocks) – not the method of IO.”

Before deciding what about the above AskTom article is still correct, take a look at this blog article.  A couple of words from that article:

“When direct path reads starts to happen?  It is known that somewhat reliable figure is your _small_table_threshold multiplied by 5 (mentioned by Tanel Poder on oracle-l recently).”

One final blog article, from the comments section:

“A table is deemed ‘small’ by Oracle if it’s 2% or less of the buffer cache in size. A small table is ‘cached’ when read via a FTS and is not immediately loaded into the least recently used end of the LRU list as becomes a ‘large’ table read via a FTS…”

“When a block is read from a ‘large’ table via a FTS, the blocks are basically loaded into the LRU end of the LRU list, even though they’ve only just been loaded into the buffer cache. As such, the chances of them remaining in the buffer cache is minimal and will likely be immediately overwritten once processed.

However, when a block is read via an index, the blocks are loaded somewhere near the ‘middle’ of the LRU list, not at the LRU end of the list…”

What, if anything, is wrong with the above quote from the book?  Bonus question, what, if anything, is wrong with the Metalink document?  Maybe there is a problem with the other quotes? 

The point of blog articles like this one is not to insult authors who have spent thousands of hours carefully constructing an accurate and helpful book, but instead to suggest that readers investigate when something stated does not exactly match what one believes to be true.  It could be that the author “took a long walk down a short pier”, or that the author is revealing accurate information which simply cannot be found through other resources (and may in the process be directly contradicting information sources you have used in the past).  If you do not investigate in such cases, you may lose an important opportunity to learn something that could prove to be extremely valuable.





System/Kernel Mode CPU Usage – What is Wrong with this Quote?

16 06 2010

June 16, 2010

While reading the June 2010 printing of the “Oracle Performance Firefighting” book I found an interesting paragraph that left me wondering if something more, or perhaps something else should be stated.  This quote is from page 104 (this is a long quote, required to not lose the context of the discussion topic - please excuse any typos):

“System time: This is when a core is spending time processing operating system kernel code. Virtual memory management, process scheduling, power management, or essentially any activity not directly related to a user task is classified as system time. From an Oracle-centric perspective, system time is pure overhead. It’s like paying taxes. It must be done, and there are good reasons (usually) for doing it, but it’s not under the control of the business—it’s for the government. Normally, Oracle database CPU subsystems spend about 5% to 40% of their active time in what is called system mode. If you’re from a non-Unix background, you may be more familiar with the term kernel mode or privileged mode, which is, in essence, system time.”

What, if anything, is wrong with the above quote from the book? 

The point of blog articles like this one is not to insult authors who have spent thousands of hours carefully constructing an accurate and helpful book, but instead to suggest that readers investigate when something stated does not exactly match what one believes to be true.  It could be that the author “took a long walk down a short pier”, or that the author is revealing accurate information which simply cannot be found through other resources (and may in the process be directly contradicting information sources you have used in the past).  If you do not investigate in such cases, you may lose an important opportunity to learn something that could prove to be extremely valuable.





Log File Sync – What is Wrong with this Quote?

16 06 2010

June 16, 2010

While reading the June 2010 printing of the “Oracle Performance Firefighting” book I found an interesting paragraph that left me wondering if something is missing.  This quote is from page 61 (this is a long quote, required to not lose the context of the discussion topic - please excuse any typos):

“As another example, suppose that you receive a call about long commit times. The users are saying ‘submits’ or ‘saves’ are taking longer today than yesterday. Upon an examination of the average log file sync wait times (which indicate commit times from an Oracle perspective), you discover there is no change from the previous day. Therefore, you know the performance issue is not because of Oracle’s commit mechanism and most likely not related to the database.”  [This period marks the end of Part 3, "IO Wait Time Summary with Event Details"]

What, if anything, is wrong with the above quote from the book? 

The point of blog articles like this one is not to insult authors who have spent thousands of hours carefully constructing an accurate and helpful book, but instead to suggest that readers investigate when something stated does not exactly match what one believes to be true.  It could be that the author “took a long walk down a short pier”, or that the author is revealing accurate information which simply cannot be found through other resources (and may in the process be directly contradicting information sources you have used in the past).  If you do not investigate in such cases, you may lose an important opportunity to learn something that could prove to be extremely valuable.





Wait Classes – How Do You Use Them to Find the Root Cause?

15 06 2010

June 15, 2010

A couple of days ago a thread appeared in the OTN forums asking the following question:

“As per documents In general, the addition of wait classes helps direct the DBA more quickly toward the root cause of performance problems.

How could i trace the root cause of performence problems if it is related to wait class?”

—–

This is the answer that I provided:

I am not completely sure that I understand your question. The wait class gives you an approximate idea of where the performance problem will be found. You must then further investigate the wait events in that wait class. There are of course potential problems with starting at the wait class (some wait classes have 2 wait events, while others have many – that could throw off the search for the problem that is impacting performance the most), but at least it provides a starting point. To give you an idea of the wait events in each wait class, here is a SQL statement that was executed on Oracle Database 11.1.0.7:

SQL> DESC V$EVENT_NAME

 Name                                      Null?    Type
 ----------------------------------------- -------- ------------
 EVENT#                                             NUMBER
 EVENT_ID                                           NUMBER
 NAME                                               VARCHAR2(64)
 PARAMETER1                                         VARCHAR2(64)
 PARAMETER2                                         VARCHAR2(64)
 PARAMETER3                                         VARCHAR2(64)
 WAIT_CLASS_ID                                      NUMBER
 WAIT_CLASS#                                        NUMBER
 WAIT_CLASS                                         VARCHAR2(64)

(Note that I have trimmed the output of the SQL statement to show the first wait event in each wait class, followed by a couple other wait events in the same wait class.)

SELECT
  SUBSTR(NAME,1,30) EVENT_NAME,
  SUBSTR(WAIT_CLASS,1,20) WAIT_CLASS
FROM
  V$EVENT_NAME
ORDER BY
  SUBSTR(WAIT_CLASS,1,20),
  SUBSTR(NAME,1,30);

EVENT_NAME                     WAIT_CLASS
------------------------------ --------------------
ASM COD rollback operation com Administrative
ASM mount : wait for heartbeat Administrative
Backup: sbtbackup              Administrative
Backup: sbtbufinfo             Administrative
Backup: sbtclose               Administrative
Backup: sbtclose2              Administrative
...
OLAP DML Sleep                 Application
SQL*Net break/reset to client  Application
SQL*Net break/reset to dblink  Application
Streams capture: filter callba Application
Streams: apply reader waiting  Application
WCR: replay lock order         Application
Wait for Table Lock            Application
enq: KO - fast object checkpoi Application
enq: PW - flush prewarm buffer Application
enq: RC - Result Cache: Conten Application
enq: RO - contention           Application
enq: RO - fast object reuse    Application
enq: TM - contention           Application
enq: TX - row lock contention  Application
enq: UL - contention           Application
ASM PST query : wait for [PM][ Cluster
gc assume                      Cluster
gc block recovery request      Cluster
...
enq: BB - 2PC across RAC insta Commit
log file sync                  Commit
Shared IO Pool Memory          Concurrency
Streams apply: waiting for dep Concurrency
buffer busy waits              Concurrency
cursor: mutex S                Concurrency
cursor: mutex X                Concurrency
cursor: pin S wait on X        Concurrency
...
Global transaction acquire ins Configuration
Streams apply: waiting to comm Configuration
checkpoint completed           Configuration
enq: HW - contention           Configuration
enq: SQ - contention           Configuration
enq: SS - contention           Configuration
enq: ST - contention           Configuration
enq: TX - allocate ITL entry   Configuration
free buffer waits              Configuration
...
ASM background timer           Idle
DIAG idle wait                 Idle
EMON slave idle wait           Idle
HS message to agent            Idle
IORM Scheduler Slave Idle Wait Idle
JOX Jit Process Sleep          Idle
...
ARCH wait for flow-control     Network
ARCH wait for net re-connect   Network
ARCH wait for netserver detach Network
ARCH wait for netserver init 1 Network
ARCH wait for netserver init 2 Network
ARCH wait for netserver start  Network
ARCH wait on ATTACH            Network
ARCH wait on DETACH            Network
ARCH wait on SENDREQ           Network
LGWR wait on ATTACH            Network
LGWR wait on DETACH            Network
LGWR wait on LNS               Network
LGWR wait on SENDREQ           Network
LNS wait on ATTACH             Network
LNS wait on DETACH             Network
LNS wait on LGWR               Network
LNS wait on SENDREQ            Network
SQL*Net message from dblink    Network
SQL*Net message to client      Network
SQL*Net message to dblink      Network
SQL*Net more data from client  Network
SQL*Net more data from dblink  Network
...
AQ propagation connection      Other
ARCH wait for archivelog lock  Other
ARCH wait for process death 1  Other
ARCH wait for process death 2  Other
ARCH wait for process death 3  Other
ARCH wait for process death 4  Other
ARCH wait for process death 5  Other
ARCH wait for process start 1  Other
...
Streams AQ: enqueue blocked du Queueing
Streams AQ: enqueue blocked on Queueing
Streams capture: waiting for s Queueing
Streams: flow control          Queueing
Streams: resolve low memory co Queueing
resmgr:I/O prioritization      Scheduler
resmgr:become active           Scheduler
resmgr:cpu quantum             Scheduler
ARCH random i/o                System I/O
ARCH sequential i/o            System I/O
Archiver slave I/O             System I/O
DBWR slave I/O                 System I/O
LGWR random i/o                System I/O
...
BFILE read                     User I/O
DG Broker configuration file I User I/O
Data file init write           User I/O
Datapump dump file I/O         User I/O
Log file init write            User I/O
Shared IO Pool IO Completion   User I/O
buffer read retry              User I/O
cell multiblock physical read  User I/O
cell single block physical rea User I/O
cell smart file creation       User I/O
cell smart index scan          User I/O
cell smart table scan          User I/O
cell statistics gather         User I/O
db file parallel read          User I/O
db file scattered read         User I/O
db file sequential read        User I/O
db file single write           User I/O
...

So, if the User I/O wait class floats to the top of the wait classes between a known start time and end time, and the Commit wait class is at the bottom of the wait classes when comparing accumulated time, it probably would not make much sense to spend time investigating the wait events in the Commit class… until you realize that there is a single event in the Commit wait class that typically contributes wait time, while there are many in the User I/O wait class.

—-

It appears that my answer either missed the target or was ignored by the original poster.  It appeared that the OP was attempting to immediately jump from step 1 to the last step, without considering any of the intermediate steps.

How would you answer the above question?  Would you read the question differently and start writing about Statspack, AWR, ADDM, 10046 traces, or process state dumps?  Would you lead the OP step by step through the process?  Or would you take an entirely different approach?





CPU Run Queue – What is Wrong with this Quote?

14 06 2010

June 14, 2010

I found another interesting couple of lines in the June 2010 printing of the “Oracle Performance Firefighting” book.  This quote is from page 116:

“While it may seem strange, the run queue reported by the operating system includes processes waiting to be serviced by a CPU as well as processes currently being serviced by a CPU. This is why you may have heard it’s OK to have the run queue up to the number of CPUs or CPU cores.”

Also, this quote from page 123:

“Have you ever heard someone say, ‘Our CPUs are not balanced. We’ve got to get that fixed.’? You might have heard this if you’re an operating system kernel developer or work for Intel, but not as an Oracle DBA. This is because there is a single CPU run queue, and any available core can service the next transaction.”

Also, this quote from page 136:

“The other area of misrepresentation has to do with time in the CPU run queue. When Oracle reports that a process has consumed 10 ms of CPU time, Oracle does not know if the process actually consumed 10 ms of CPU time or if the process first waited in the CPU run queue for 5 ms and then received 5 ms of CPU time.”

Interesting… regarding the first quote - most of what I have read about the CPU run queue seemed to indicate that the process was removed from the run queue when the process is running on the CPU, and then re-inserted into the run queue when the process stops executing on the CPU (assuming that the process has not terminated and is not suspended).  The “Oracle Performance Firefighting” book lacks a test case to demonstrate that the above is true, so I put together a test case using the CPU load generators on page 197 of the “Expert Oracle Practices” book, the Linux sar command, and a slightly modified version (set to refresh every 30 seconds rather than every second) of the WMI script on pages 198-200 of the “Expert Oracle Practices” book.

For the test, I will use the following command on Linux:

sar -q 30 10

Immediately after the above command is started, a copy of the Linux version of the CPU load generator will be run (the load generator runs for 10 minutes and then exits):

#!/bin/bash
i=0
STime=`date +%s`

while [ `date +%s` -lt $(($STime+$((600)))) ]; do
  i=i+0.000001
done

Every time a new line is written by the sar utility another copy of the CPU load generator is started.  For the first test run I manually launched a new command line from the GUI and then started the script.  For the second test run I first opened as many command line windows as necessary, and prepared each to execute the script.  Here is the output (the runq-sz column shows the run queue):

[root@airforce-5 /]# sar -q 30 10
Linux 2.6.18-128.el5 (airforce-5.test.com)      06/13/2010

05:39:16 PM   runq-sz  plist-sz   ldavg-1   ldavg-5  ldavg-15
05:39:46 PM         1       228      0.76      0.37      0.16
05:40:16 PM         2       230      1.04      0.48      0.21
05:40:46 PM         3       232      1.31      0.59      0.25
05:41:16 PM         4       233      1.86      0.79      0.33
05:41:46 PM         6       237      2.81      1.11      0.45
05:42:16 PM         7       241      3.71      1.48      0.59
05:42:46 PM         9       244      5.56      2.14      0.84
05:43:16 PM        12       247      7.86      3.00      1.16
05:43:46 PM        16       250     10.29      4.04      1.56
05:44:16 PM        14       250     12.03      5.06      1.98
Average:            7       239      4.72      1.91      0.75

[root@airforce-5 /]# sar -q 30 10
Linux 2.6.18-128.el5 (airforce-5.test.com)      06/13/2010

05:50:53 PM   runq-sz  plist-sz   ldavg-1   ldavg-5  ldavg-15
05:51:23 PM         1       237      0.54      3.41      2.76
05:51:53 PM         3       239      1.44      3.35      2.76
05:52:23 PM         3       241      2.20      3.35      2.78
05:52:53 PM         5       242      3.31      3.51      2.85
05:53:23 PM         7       245      4.53      3.78      2.96
05:53:53 PM        10       247      6.40      4.31      3.16
05:54:23 PM        13       249      8.60      5.03      3.43
05:54:53 PM        13       249     10.71      5.87      3.76
05:55:23 PM        16       253     12.16      6.68      4.10
05:55:53 PM        14       251     13.02      7.41      4.42
Average:            8       245      6.29      4.67      3.30

For the sake of comparison, here is a CPU load generator script that executes on Windows that performs the same operation as the script which was executed on Linux:

Dim i
Dim dteStartTime

dteStartTime = Now

Do While DateDiff("n", dteStartTime, Now) < 10
  i = i + 0.000001
Loop

Let’s use the WMI script in place of the Linux sar command and repeat the test.  The WMI script will be started, the CPU load generator script will be started, and every time the WMI script outputs a line another copy of the CPU load generator script will be started.  Here is the output from the WMI script (the Q. Length column shows the run queue):

6/13/2010 6:29:27 PM Processes: 53 Threads: 825 C. Switches: 1757840 Q. Length: 0
6/13/2010 6:29:57 PM Processes: 54 Threads: 826 C. Switches: 32912 Q. Length: 0
6/13/2010 6:30:27 PM Processes: 56 Threads: 831 C. Switches: 71766 Q. Length: 0
6/13/2010 6:30:57 PM Processes: 58 Threads: 836 C. Switches: 39857 Q. Length: 0
6/13/2010 6:31:27 PM Processes: 59 Threads: 830 C. Switches: 33946 Q. Length: 0
6/13/2010 6:31:57 PM Processes: 59 Threads: 821 C. Switches: 27955 Q. Length: 1
6/13/2010 6:32:27 PM Processes: 61 Threads: 830 C. Switches: 32088 Q. Length: 0
6/13/2010 6:32:57 PM Processes: 63 Threads: 826 C. Switches: 27027 Q. Length: 0
6/13/2010 6:33:29 PM Processes: 64 Threads: 827 C. Switches: 22910 Q. Length: 3
6/13/2010 6:34:01 PM Processes: 66 Threads: 836 C. Switches: 22936 Q. Length: 4
6/13/2010 6:34:34 PM Processes: 68 Threads: 839 C. Switches: 34076 Q. Length: 5
6/13/2010 6:35:07 PM Processes: 70 Threads: 840 C. Switches: 25564 Q. Length: 8

What, if anything, is wrong with the above quotes from the book?  The comments in this article might be helpful.

The point of blog articles like this one is not to insult authors who have spent thousands of hours carefully constructing an accurate and helpful book, but instead to suggest that readers investigate when something stated does not exactly match what one believes to be true.  It could be that the author “took a long walk down a short pier”, or that the author is revealing accurate information which simply cannot be found through other resources (and may in the process be directly contradicting information sources you have used in the past).  If you do not investigate in such cases, you may lose an important opportunity to learn something that could prove to be extremely valuable.





DB File Scattered Read Wait Event – What is Wrong with this Quote?

13 06 2010

June 13, 2010 (Modified June 14, 2010)

I found an interesting couple of lines in the June 2010 printing of the “Oracle Performance Firefighting” book.  This quote is from page 43 – I added the text in the square brackets [ ] to provide context for the quote:

Digging a little deeper [into the results of the readv Linux OS call that completed in 0.6ms], we can assert that all the requested blocks must have resided in memory (but not Oracle’s buffer cache memory, since Oracle needed to request them from the operating system). We can make this assertion because a physical spinning IO device cannot return 16 nonsequential blocks (the blocks could be scattered over many physical devices) in less than a single millisecond!

[One sentence removed to cut down the amount of typing for this quote - sentence basically states that Oracle assigns the time consumed to an Oracle wait event.]  Regardless of the operating system or the actual system call, Oracle gives a multiblock IO call the special name db file scattered read, because these multiple blocks can be scattered over the IO subsystem.”

This book was written by Craig Shallahamer, had six technical reviewers (I recognize the names of three), and was printed by Good Catch Publishing.

What, if anything, is wrong with the above quote?

Edit June 14, 2010:
The point of blog articles like this one is not to insult authors who have spent thousands of hours carefully constructing an accurate and helpful book, but instead to suggest that readers investigate when something stated does not exactly match what one believes to be true.  It could be that the author “took a long walk down a short pier”, or that the author is revealing accurate information which simply cannot be found through other resources (and may in the process be directly contradicting information sources you have used in the past).  If you do not investigate in such cases, you may lose an important opportunity to learn something that could prove to be extremely valuable.





Reading Material On Order

10 06 2010

June 10, 2010 (Updated May 29, 2011)

I must admit that I am a bit surprised by the number of page views for the “Oracle SQL Recipes: A Problem – Solution Approach” book review that I posted 4 days ago.  That article is currently in 12th place for the number of page views on this blog in the last 90 days, and currently has half as many page views as the blog article in first place for the last 90 days (there are currently 202 blog articles on this blog).  You may have noticed that, unlike my other blog articles, I do not permit readers to post comments on blog articles that are book reviews.  I personally pay for each book that I review, and I try to post reviews of books with that criteria in mind – am I getting my money’s worth from the book, and what do I think about the book’s contents.

I missed the first 10 minutes of the Ultimate SQL Query Tune Off webinar today – the client-side version of the Live Meeting software kept crashing, simply stating that a problem happened, see your administrator.  The webinar started off with a roughly 50 minute long presentation by Jonathan Lewis.  Later, there were a lot of great questions asked after the formal portion of the webinar, and I am a bit surprised just how quickly extremely detailed (and more importantly correct) answers were provided for the questions.  Interestingly, some of the issues that I raised (or were related) about the Oracle SQL Recipes book were also asked as questions (I am sure that it was purely coincidental, but still interesting):

  • Why isn’t my index being used.
  • Is there any certain way to know if it is safe to drop an index (monitoring is not sufficient).
  • Rebuilding indexes and Metalink notes.

The question of what books should be read was also asked.  I have previously posted my recommendations for books.  As of Monday I had three new books on Order:


The first book was supposed to be in stock on June 1, but I received an email stating that Amazon is still trying to locate that book. I ordered that book simply because I was curious. I had bumped into that book’s author several times on Oracle’s OTN forums, usually with interesting results. Any book offered with the following warning must be interesting:

“This is not a book for beginners. Targeted at the senior Oracle DBA, this comprehensive book gives you all of the knowledge you need to be successful in tuning even the most complex Oracle database.”

I ordered the second book because I had previously purchased two copies of the 9i/10g edition of this book, and I purchased his “Expert One On One” book.

I ordered the third book because I had previously purchased that author’s “Forecasting Oracle Performance” book. The “Oracle Performance Firefighting” book (fourth printing) arrived by email on Tuesday. I am a bit disappointed that I cannot print the PDF file, nor copy sections of the PDF file – something that is permitted in the PDF copy of the “Expert Oracle Practices” book that I bought from Apress. I am also a bit surprised that my name, address, and phone number appear at the top of every page of the “Oracle Performance Firefighting” book, but I understand why. I am roughly 20 pages into the book, but it has not yet latched onto my undivided attention as has happened with some of the other books.

I plan to post detailed reviews of these three book, just as I did with the “Oracle SQL Recipes” book.

—————-
(Added May 5, 2011)
Reviews of the above books:
Oracle Tuning: The Definitive Reference Second Edition
Expert Oracle Database Architecture: Oracle Database Programming 9i, 10g, and 11g Techniques and Solutions, Second Edition
Oracle Performance Firefighting
—————-

On a related note, some people (myself included) have spent a significant amount of time and effort creating content that is posted in blogs, Usenet/OTN forums, various websites, and in some cases books. I posted the following today, somewhere:

You have a fundamental misunderstanding of U.S. Copyright law (I understand that you reside in the United States based on your profiles):
http://www.copyright.gov/circs/circ1.pdf

Copyright ownership is immediate, and does not need to be registered for entitlement.

It is absolutely not the responsibility of people to tell you not to post their work. It is your responsibility to obtain the permission of the copyright holder to post material that is owned by the copyright holders, whether it is pictures, large sections of text, or scripts.

Fair use laws do allow you, in some cases, to reproduce small sections of copyright work, but you must cite where the original material is found:
http://www.copyright.gov/fls/fl102.html

Consider this – you have spent tens of hours, hundreds of hours, or thousands of hours to produce something in written form, a book for instance. You have carefully specified every word on the page to state exactly what needs to be stated to clearly communicate a concept. You have spent hours building and testing scripts, verifying the results across different Oracle releases and different hardware platforms. A technical reviewer and an editor have reviewed your work, and you have made significant modifications to that work to more clearly communicate concepts that cannot be found through other resources. You assemble the scripts into an easily used format, and then you publish your work.

Now, someone finds all of your hard work, thinks it looks impressive, runs your test scripts through their system, captures a bit of the output, changes a couple words in the text, and then publishes the material (possibly for profit) as their own hard work. By reproducing the hard work of others and publishing it as their own original content, they are robbing the original producers of that content, not only of their entitlement under copyright law, but also of all of their hard work and potential monetary benefit that may be derived from that work.

That is exactly what you are doing by posting the original works of others with just a couple of changed words.

Kind of odd that I would post that message, or maybe not. For the record, it was not my material that was copied without attribution.





ORA-1410 Invalid ROWID Mystery

8 06 2010

June 8, 2010

An interesting thread appeared on the OTN forums today.  The original poster indicated that he was running Oracle Database 10.2.0.2 on Windows 2003 R2, and received an unexpected error message:

SQL> analyze table ap2dw.ist_perfaggr_r_7 validate structure cascade;
analyze table ap2dw.ist_perfaggr_r_7 validate structure cascade
*
ERROR at line 1:
ORA-01410: invalid ROWID

The OP stated that the table is truncated and reloaded every night and that there are no indexes on the table (the index was dropped and the error remains).  The OP bounced the server and the problem disappeared.  What was the cause of the problem?





True or False – Why Isn’t My Index Getting Used?

25 05 2010

May 25, 2010

(Back to the Previous Post in the Series

The book “Expert One-On-One Oracle” has been on the edge of my desk for the last couple of days.  I cannot recall why I originally pulled it off the bookshelf, but possibly it was needed to satisfy a question about how things used to work in Oracle Database.  I opened the book to a random page, and ended up somewhere around page 303.  Page 303 includes a section title of “Why isn’t my Index Getting Used?”.  This still seems to be a frequent question on various Oracle forums, and volumes have been written on the subject.  A quick Google search finds the following on the topic:

The “Expert One-On-One Oracle” book lists 6 of the cases why an index would not be used.  Some things in Oracle stay the same from one release to the next, while others change.  Which of the following are true or false for Oracle Database 11.1.0.6 or higher, and indicate why the answer is true or false, and if relevant, the Oracle release when the answer changed.  Note that I might intentionally state only part of the case identified in the book, in part to make things more interesting.  Maybe the question of when is it true and when is it not true would be a more realistic request?

Reasons why an index would not be used:

1. The predicate in the WHERE clause does not specify the column at the leading edge of the index.  For example, an index exists on T1(C1, C2) and the WHERE clause in the query is simply WHERE C2=1.

2. The query is attempting to count the number of rows in the table, for example: SELECT COUNT(*) FROM T1;.

3. The query is attempting to apply a function on the indexed columns, for example: SELECT * FROM T1 WHERE TRUNC(DATE_COL) = TRUNC(SYSDATE);.

4. The query is attempting to treat a VARCHAR2/CHAR column that contains only numbers as a numeric column, for example: SELECT * FROM T1 WHERE CHAR_COL = 10;.

5. The index, if used, would be slower, as determined by the calculated cost of the access path.

6. The tables have not been analyzed in a while.  The last time the tables referenced in the WHERE clause were analyzed, the tables were quite small.  Now the tables are significantly larger.  Without up-to-date statistics, the CBO cannot make the correct decisions.

Extra credit: list three other reasons why an index might not be used.

(10053 trace for second test of table T1 in the comments section: OR11_ora_4400_WhyFullTableScanTest - Edit: this is a PDF file, WordPress is not allowing the double extention trick nor Word formatted files for some reason.)





True or False – Optimizer Mode and Tuning Goals

13 05 2010

 May 13, 2010

(Back to the Previous Post in the Series) (Forward to the Next Post in the Series)

Continuing the true or false blog article series with more questions that seem to have conflicting answers.  Today’s quiz is on the topic of the optimizer mode and tuning goals, inspired by a recent thread that I found in a discussion forum.  A Google search found a couple of related articles.

When answering the questions, answer True or False, followed by a justification for the answer that you provided.

The Articles:

 The Quiz:

1.  As of Oracle 10g R1, there are three methods for joining tables or other row sources: star transformation join, nested loops join, and hash join.

2.  SQL execution speed will be fastest when the first join between two row sources produces the smallest possible result set from the specified row sources.

3.  Oracle Database 10g’s SQL Profiles and SQL Access Advisor help to change execution plans for queries without adding hints.

4. Oracle Database 10g offers three optimizer modes, controlled by the OPTIMIZER_MODE initialization parameter: RULE, COST-BASED, and CHOOSE.

5.  An effective approach for query tuning involves enabling the RULE based optimizer either through a RULE hint, or by changing the session-level optimizer goal with the following SQL statement:

    ALTER SESSION SET OPTIMIZER GOAL = RULE;

6.  The default optimizer mode in Oracle 10g R1 is CHOOSE, which means that the RULE based optimizer will be used if none of the objects referenced in the SQL statement have statistics.

7.  A SELECT statement requiring 10 seconds and performing 10,000 DB_BLOCK_GETS is automatically less efficient that than a transformed version of the SELECT statement that performs 2,000 DB_BLOCK_GETS.

8.  The FIRST_ROWS optimizer mode is appropriate for OLTP type systems because it allows the small resultsets that are exepected by the end users to be returned as quickly as possible.

9.  The RULE based optimizer does not support any features introduced in Oracle since the mid 1990s.

10. Specifying a value for the OPTIMIZER_INDEX_CACHING parameter is recommended, because the value is critical for costing nested loops joins.  Its value should be set to indicate the percentage of an index that is likely to remain in Oracle’s buffer cache.

11. When the PARALLEL_AUTOMATIC_TUNING parameter is set to ON, the cost-based optimizer will artifically increase the calculated cost of index access paths, which increases the frequency of full table scans that may be executed in parallel.

12. With the RULE based optimizer, the driving table is the last table listed in the FROM clause of the SQL statement.  When the Cost-Based optimizer is used, the driving table is the first table listed in the FROM clause of the SQL statement.

13. The Cost-Based optimizer often performs unnecessary full table scans when three or more tables are listed in the FROM clause of a SQL statement.

14. Setting the OPTIMIZER_MODE parameter at the session level to FIRST_ROWS_10 instructs the query optimizer that the SQL statement which will be submitted next will return 10 rows.

I fear that this blog series might be falsely inflating the Google ranking of the Oracle documentation library due to the number of times the documentation library has been referenced - sorry Google users.  :-)





True or False – Hash Joins

12 05 2010

May 12, 2010

(Back to the Previous Post in the Series) (Forward to the Next Post in the Series)

Continuing the true or false blog article series with more questions that seem to have conflicting answers.  Today’s quiz is on the topic of the hash joins, inspired by a recent thread that I found in a discussion forum.

When answering the questions, answer True or False, followed by a justification for the answer that you provided.

The Articles:

The Quiz:

1.  A hash join is ideal when joining a small table to a large table, and in such cases a hash join is typically faster than a nested loops join or a sort-merge join.

2.  When presented with very large tables in a production database, the Oracle query optimizer will attempt to avoid using hash joins unless the PGA_AGGREGATE_TARGET or HASH_AREA_SIZE (if PGA_AGGREGATE_TARGET is not used) is increased to a large value.

3.  When a PGA_AGGREGATE_TARGET is in use, one session cannot use all of the memory specified by PGA_AGGREGATE_TARGET nor all of the available memory in the server, whichever is the lower value.

4.  A hash join may use only 200MB of memory, or 5% of the PGA_AGGREGATE_TARGET, whichever is the lower value.

5.  The query optimizer will not select to use a hash join unless the hash join is able to complete in memory, as controlled by the PGA_AGGREGATE_TARGET and/or HASH_AREA_SIZE parameter values.

6.  A hash join is only faster than a nested loops join when the memory available for the hash join is at least 1.6 times the sum of bytes in the rows of the table to be hashed.

7.  A hash join’s performance is affected when the hash join overflows the memory established by the HASH_AREA_SIZE parameter, and the hash join spills to the TEMP tablespace.

8.  The degree of parallelism for a table (or individual SQL statement) must be adjusted when a full table scan is used to retrieve the rows during a hash join.  Hash joins typically require the parallel degree to be set to a value greater than 1 in order to improve performance over other join methods.

9.  The _PGA_MAX_SIZE parameter should be adjusted to improve performance when joining large tables with a hash join.

10. The Oracle query optimizer will commonly ignore USE_HASH hints.

11. The OPTIMIZER_INDEX_COST_ADJ parameter and OPTIMIZER_MAX_PERMUTATIONS parameters control the optimizer’s decision to use hash joins.

12. When the PGA_AGGREGATE_TARGET parameter is not used, the HASH_AREA_SIZE parameter should be set to 50% of the square root of the bytes in the smaller of the two row sources being joined, with a minimum value of 1MB.

13. The row source listed below the words HASH JOIN in an execution plan 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 row source listed below the words HASH JOIN in an execution plan is fully scanned, probing the generated hash table in search of a match.

When attempting to answer the true or false questions, think about under what conditions the answer to the question will be true and under what conditions the answer to the question must be false.





True or False – NVL

11 05 2010

May 11, 2010

(Back to the Previous Post in the Series) (Forward to the Next Post in the Series)

Continuing the true or false blog article series with more questions that seem to have conflicting answers, if the answers to those questions depend on the source of the information found through Internet searches.  Today’s quiz is on the topic of the NVL function, inspired by a recent thread that I found in a discussion forum.

When answering the questions, answer True or False, followed by a justification for the answer that you provided.

The Articles:

The Quiz:

1.  The most common use of the NVL function is to prevent division by zero errors in mathematical equations.

2.  The SQL statement:

SELECT NVL(CURRENT_STATUS, "Not disclosed") FROM T1;

   replaces NULL values found in the CURRENT_STATUS column of table T1 with the phrase: Not disclosed

3.  The NVL function replaces a NULL column value with any other value.

4.  The NVL function is the most efficient method for converting NULL values to a non-NULL value.

To help you with the questions, here is a test table:

CREATE TABLE T1(
  C1 NUMBER,
  C2 NUMBER,
  C3 NUMBER,
  C4 DATE,
  C5 DATE,
  C6 DATE,
  C7 VARCHAR2(20),
  C8 VARCHAR2(20),
  C9 VARCHAR2(20),
  PRIMARY KEY(C1));

INSERT /*+ APPEND */ INTO
  T1
SELECT
  ROWNUM,
  DECODE(MOD(ROWNUM,5),0,NULL,ROWNUM),
  DECODE(MOD(ROWNUM,5),0,NULL,ROWNUM),
  TRUNC(SYSDATE)+TRUNC(ROWNUM/100),
  DECODE(MOD(ROWNUM,5),0,NULL,TRUNC(SYSDATE)+TRUNC(ROWNUM/100)),
  DECODE(MOD(ROWNUM,5),0,NULL,TRUNC(SYSDATE)+TRUNC(ROWNUM/100)),
  RPAD(CHR(MOD(ROWNUM-1,26)+65)||
    CHR(MOD(ROWNUM,26)+65)||
    CHR(MOD(ROWNUM+1,26)+65)||
    CHR(MOD(ROWNUM+2,26)+65),20,'A'),
  DECODE(MOD(ROWNUM,5),0,NULL,
   RPAD(CHR(MOD(ROWNUM-1,26)+65)||
    CHR(MOD(ROWNUM,26)+65)||
    CHR(MOD(ROWNUM+1,26)+65)||
    CHR(MOD(ROWNUM+2,26)+65),20,'A')),
  DECODE(MOD(ROWNUM,5),0,NULL,
   RPAD(CHR(MOD(ROWNUM-1,26)+65)||
    CHR(MOD(ROWNUM,26)+65)||
    CHR(MOD(ROWNUM+1,26)+65)||
    CHR(MOD(ROWNUM+2,26)+65),20,'A'))
FROM
  DUAL
CONNECT BY
  LEVEL<=1000000;

COMMIT;

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1',CASCADE=>TRUE)

The above creates a table with 1,000,000 rows, where one of every 5 rows contains NULL values in columns C2, C3, C5, C6, C8, and C9.

For some of the quiz questions, the following script might be helpful:

SET ARRAYSIZE 1000
SET AUTOTRACE TRACEONLY STATISTICS
SET TIMING ON

ALTER SESSION SET TRACEFILE_IDENTIFIER = 'NVL_TEST';
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 8';

SELECT /*+ GATHER_PLAN_STATISTICS */
  COALESCE(C3,C2,C1) NUM,
  COALESCE(C6,C5,C4) DAT,
  COALESCE(C9,C8,C7) VCAR
FROM
  T1;

SELECT /*+ GATHER_PLAN_STATISTICS */
  NVL(C3,NVL(C2,C1)) NUM,
  NVL(C6,NVL(C5,C4)) DAT,
  NVL(C9,NVL(C8,C7)) VCAR
FROM
  T1;

SELECT /*+ GATHER_PLAN_STATISTICS */
  NVL(C3,C1) NUM,
  NVL(C6,C4) DAT,
  NVL(C9,C7) VCAR
FROM
  T1;

SELECT /*+ GATHER_PLAN_STATISTICS */
  DECODE(C3,NULL,DECODE(C2,NULL,C1,C2),C3) NUM,
  DECODE(C6,NULL,DECODE(C5,NULL,C4,C5),C6) DAT,
  DECODE(C9,NULL,DECODE(C8,NULL,C7,C8),C9) VCAR
FROM
  T1;

SELECT /*+ GATHER_PLAN_STATISTICS */
  NVL(C3,NVL(C2,C4)) COL1,
  NVL(C6,NVL(C5,C7)) COL2
FROM
  T1;

SELECT
  SYSDATE
FROM
  DUAL;

ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';

It is probably best to execute the above script several times to limit the effects of physical block reads.  You may see different performance results whether or not the 10046 trace is enabled, whether or not physical block reads are performed on every execution, and whether or not the test is executed directly on the database server.  The GATHER_PLAN_STATISTICS hint is used to permit DBMS_XPLAN.DISPLAY_CURSOR to retrieve the execution plan with the ALLSTATS LAST format parameter.





True or False – Undo

9 05 2010

May 9, 2010

(Back to the Previous Post in the Series) (Forward to the Next Post in the Series)

Continuing the true or false blog article series with more questions that seem to have conflicting answers, if the answers to those questions depend on the source of the information found through Internet searches.  Today’s quiz is on the topic of Oracle Database’s undo functionality, and in some cases you may need to consult several of the suggested articles in order to correctly answer each of the questions.

When answering the questions, answer True or False, followed by a justification for the answer that you provided.  Yes, there are a lot of questions, but part of the entertainment in these types of exercises is reading an article and identifying what is logically right or wrong.  Of course, as with the previous quizzes, any answers that attempt to utilize logical fallacies will be scored as incorrect.

The Articles:

The Quiz:

1.  Rollback segment shrinks cause the next session to use the rollback segment to wait while the rollback segment is reduced in size to its optimal size.

2.  An OPTIMAL size for rollback segments should never be set because when the rollback segments are correctly sized, they should never increase in size.

3.  The ideal rollback segment size is partially determined by the values of the DB_WRITER_PROCESSES, LOG_BUFFER, AND LOG_CHECKPOINT_INTERVAL initialization parameters, as well as the COMMIT frequency.

4.  Large, long running batch jobs should be manually set to use a single, large rollback segment to reduce the risk of ORA-01555 (snapshot too old) errors.

5.  Adding a large undo segment when manual undo management is in use will effectively reduce the chances of sessions receiving ORA-01555 (snapshot too old) errors.

6.  When automatic undo management is used, ORA-01555 (snapshot too old) errors are triggered by Oracle Database creating many small undo segments in an undo tablespace that is too small.

7.  In a database with a single session connected, it is not possible to trigger an ORA-01555 (snapshot too old) error when the session is not modifying the tables that are being queried.

8.  When manual undo management is in use, only UPDATE transactions are able to automatically cause an undo segment to grow; SELECT statements do not cause the undo segments to grow, thus increasing the risk of ORA-01555 (snapshot too old) errors when the undo segments are small.

9.  When manual undo management is in use, it is a common practice for DBAs to assign a transaction to a specific rollback segment using the SET TRANSACTION command, but this is unnecessary with automatic undo management.

10. The automatically created undo segments in the SYSTEM tablespace will only be used when modifying objects residing in the SYSTEM tablespace.

 11. The UNDO_TABLESPACE parameter must be set to an undo tablespace’s name when automatic undo management is used.

12. The V$WAITSTAT view includes a block class that indicates the number of waits on the system undo header blocks.

13. The SESSIONS parameter should be set to a value no larger than the expected maximum number of user sessions since the SESSIONS parameter affects the number of undo segments created when automatic undo management is used.

14. When automatic undo management is used, as the number of sessions connected to the database instance increases, the instance dynamically adds additional (offline) undo segments in response to the sessions merely connecting.  The TRANSACTIONS_PER_ROLLBACK_SEGMENT parameter determines the number of additional sessions that must be connected before an additional undo segment is added.

15. SELECT type SQL statements do not generate redo nor undo.

16. In most databases, DML or DDL is performed in only 10% of all transactions in the database.

17. After a session changes data and issues a COMMIT, all sessions with access permissions to view the data are able to immediately see those changes.

18. Read consistency in Oracle means that data included in the query results will always be as of the moment the query was submitted.

19. Undo segments may be used to support flashback type operations.

20. The redo logs contain undo records, which allows Oracle Database to optimize the process of recovering from a shutdown abort, and also means that a future release of Oracle Database will no longer need dedicated undo tablespaces.

Note that a new blog article category has been added to quickly locate the quiz articles.  When attempting to answer the true or false questions, think about under what conditions the answer to the question will be true and under what conditions the answer to the question must be false.





True or False – Data Dump Import and Indexes

6 05 2010

May 6, 2010

(Back to the Previous Post in the Series) (Forward to the Next Post in the Series)

While reading one of the OTN threads that asked whether Data Pump import will automatically rebuild indexes, I notice that someone posted an interesting response that appeared to be quoted from another source.  I searched the Internet and found the text that was copied into the OTN post, directly below a heading that read “Maximizing Oracle Import (impdp) Performance” – it was then that I decided it was time for another true or false quiz.

The reading material:

  1. http://www.dba-oracle.com/oracle_tips_load_speed.htm
  2. http://download.oracle.com/docs/cd/E14072_01/server.112/e10701/dp_import.htm
  3. http://download.oracle.com/docs/cd/E14072_01/server.112/e10701/dp_export.htm

Read through the above material and attempt to answer the following questions – as in the past, state why the answer is true or false.  Since the first article was last revised in March 2006, I probably should have referenced the documentation for Oracle Database 10g R2, but the Oracle Database 11g R2 documentation should be sufficient.

1.  The options used when data is exported with Data Pump Export (expdp) have no control over how the data may be imported with Data Pump Import (impdp).

2.  Data Pump Export supports enabling and disabling direct path for exports.

3.  Set the ANALYZE parameter of impdp to N to prevent statistics from being gathered on imported tables and indexes.

4.  Using solid state disks (SSD) will speed up import rates when the import job is otherwise fully tuned.

5.  Setting the COMMIT parameter of impdp to N suppresses the committing of the imported data until the load process ends.

6.  Setting the INDEXES parameter of impdp to N prevents indexes from being created until the import process completes.

7.  Setting the INDEXES parameter of impdp to N eliminates the index maintenance overhead when data is imported into a pre-existing table having pre-existing indexes.

8.  When the creation of indexes is postponed through the use of the INDEXES parameter of impdp, the resulting text file should be edited to set the parallel degree of each index to one less than the value of the CPU_COUNT initialization parameter’s value.

9.  Using the EXCLUDE=STATISTICS parameter value of impdp prevents the automatic of gathering of object statistics by impdp.  Setting this parameter value will greatly improve impdp performance.

10. When the _disable_logging initialization parameter is set to TRUE, either a SHUTDOWN ABORT or instance crash will disable/corrupt the database.  This corruption may be prevented by creating a backup of the database before modifying the value of that hidden initialization parameter.

Edit – May 6, 2010: The original title of this blog article was intended to be “True or False – Data Pump Import and Indexes” – a bit of unintentional humor was caused by the typo in the article title.  There are several definitions of the word “Dump“, some of which are more fitting than others.

SchedulerAutomation.zip





How Much Space is Required to Store a Whole Lot of Nothing?

29 04 2010

April 29, 2010

While looking for something completely unrelated I encountered one of the test cases that I posted to an OTN forum thread about a year ago.  There are a couple of interesting items in the test case, so I thought that I would reproduce it here, verifying the test case on Oracle 11.2.0.1.  The OTN forum thread asked if NULL values in table columns take up space, and if so does it depend on the data type of the column.  I answered the question like this:

It is my understanding that NULL values in the last columns (non-null values appear only in the first set of columns defined for the table) in of a row do not consume space, while NULL values in the first set of columns, if they are followed by columns containing non-NULL values will consume space. At this time, I am having trouble locating a reference which backs up my understanding of how NULLs affect storage space.

The test case was originally constructed to be executed against Oracle Database 10.2.0.4, and the output showed what was achieved with an ASSM autoallocate tablespace with an 8KB block size.  So, what happens when the test case is executed on Oracle Database 11.2.0.1 with an ASSM autoallocate tablespace with an 8KB block size?  The results may surprise you.

First, we need to create a table with a couple of different column data types, and then collect statistics for the table:

CREATE TABLE T1 (
  C1 VARCHAR2(300),
  C2 NUMBER(22,4),
  C3 DATE,
  C4 BLOB,
  C5 CLOB);

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1')

Surprised by the results yet?

SELECT
  NUM_ROWS,
  BLOCKS,
  EMPTY_BLOCKS,
  AVG_ROW_LEN,
  NUM_FREELIST_BLOCKS
FROM
  USER_TABLES
WHERE
  TABLE_NAME='T1';

  NUM_ROWS     BLOCKS EMPTY_BLOCKS AVG_ROW_LEN NUM_FREELIST_BLOCKS
---------- ---------- ------------ ----------- -------------------
         0          0            0           0                   0

SELECT
  EXTENT_ID,
  BYTES,
  BLOCKS
FROM
  USER_EXTENTS
WHERE
  SEGMENT_NAME='T1'
ORDER BY
  EXTENT_ID;

no rows selected

Probably not if you read about Oracle Database 11.2.0.1′s deferred segment creation feature.  Yes, the above results differed from that of Oracle Database 10.2.0.4, which showed the following for the second SQL statement:

 EXTENT_ID      BYTES     BLOCKS
---------- ---------- ----------
         0      65536          8

Next, insert 1,000,000 rows containing NULL values in all columns and re-gather the statistics:

INSERT INTO T1 (C1)
SELECT
  NULL
FROM
  DUAL
CONNECT BY
  LEVEL<=500000;

INSERT INTO T1 (C1)
SELECT
  NULL
FROM
  DUAL
CONNECT BY
  LEVEL<=500000;

COMMIT;

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1')

Now let’s check the table’s statistics:

SELECT
  NUM_ROWS,
  BLOCKS,
  EMPTY_BLOCKS,
  AVG_ROW_LEN,
  NUM_FREELIST_BLOCKS
FROM
  USER_TABLES
WHERE
  TABLE_NAME='T1';

  NUM_ROWS     BLOCKS EMPTY_BLOCKS AVG_ROW_LEN NUM_FREELIST_BLOCKS
---------- ---------- ------------ ----------- -------------------
   1000000       1630            0           1                   0

Oracle Database 10.2.0.4 was not quite as accurate when estimating the number of rows in the table:

  NUM_ROWS     BLOCKS EMPTY_BLOCKS AVG_ROW_LEN NUM_FREELIST_BLOCKS
---------- ---------- ------------ ----------- -------------------
    984619       1630            0           1                   0

Next let’s take a look at the extent allocation, which was the same for both releases of Oracle Database:

SELECT
  EXTENT_ID,
  BYTES,
  BLOCKS
FROM
  USER_EXTENTS
WHERE
  SEGMENT_NAME='T1'
ORDER BY
  EXTENT_ID;

 EXTENT_ID      BYTES     BLOCKS
---------- ---------- ----------
         0      65536          8
         1      65536          8
         2      65536          8
         3      65536          8
         4      65536          8
         5      65536          8
         6      65536          8
         7      65536          8
         8      65536          8
         9      65536          8
        10      65536          8
        11      65536          8
        12      65536          8
        13      65536          8
        14      65536          8
        15      65536          8
        16    1048576        128
        17    1048576        128
        18    1048576        128
        19    1048576        128
        20    1048576        128
        21    1048576        128
        22    1048576        128
        23    1048576        128
        24    1048576        128
        25    1048576        128
        26    1048576        128
        27    1048576        128

SELECT
  SUM(BYTES) BYTES,
  SUM(BLOCKS) BLOCKS
FROM
  USER_EXTENTS
WHERE
  SEGMENT_NAME='T1'
ORDER BY
  EXTENT_ID;

     BYTES     BLOCKS
---------- ----------
  13631488       1664

The above shows that the table is using 1630 blocks to store the rows with the completely NULL values, there are 1664 blocks allocated to extents used by the table, and 13,631,488 bytes used by the extents.  NULLs consuming space?

A little fun with the ROWID pseudocolumn, dissecting the components of that pseudocolumn (side note, I do not recall where I originally determined how to break apart the 18 byte long displayed ROWID, but it is found in the documentation):

SELECT
  SUBSTR(ROWID,1,6) OBJECT_ID,
  SUBSTR(ROWID,7,3) FILE_ID,
  SUBSTR(ROWID,10,6) BLOCK_ID,
  SUBSTR(ROWID,16,3) ROW_ID,
  LENGTHB(ROWID) LEN,
  COUNT(*) OVER (PARTITION BY SUBSTR(ROWID,10,6) ORDER BY ROWNUM) ROWN,
  SUM(LENGTH(ROWID)) OVER (PARTITION BY SUBSTR(ROWID,10,6) ORDER BY ROWNUM) BYTES
FROM
  T1
WHERE
  ROWNUM<=1400;

OBJECT FIL BLOCK_ ROW        LEN       ROWN      BYTES
------ --- ------ --- ---------- ---------- ----------
AAAULA AAH AAGLHT AAA         18          1         18
AAAULA AAH AAGLHT AAB         18          2         36
AAAULA AAH AAGLHT AAC         18          3         54
AAAULA AAH AAGLHT AAD         18          4         72
AAAULA AAH AAGLHT AAE         18          5         90
AAAULA AAH AAGLHT AAF         18          6        108
AAAULA AAH AAGLHT AAG         18          7        126
AAAULA AAH AAGLHT AAH         18          8        144
AAAULA AAH AAGLHT AAI         18          9        162
AAAULA AAH AAGLHT AAJ         18         10        180
AAAULA AAH AAGLHT AAK         18         11        198
AAAULA AAH AAGLHT AAL         18         12        216
AAAULA AAH AAGLHT AAM         18         13        234
AAAULA AAH AAGLHT AAN         18         14        252
AAAULA AAH AAGLHT AAO         18         15        270
AAAULA AAH AAGLHT AAP         18         16        288
AAAULA AAH AAGLHT AAQ         18         17        306
AAAULA AAH AAGLHT AAR         18         18        324
AAAULA AAH AAGLHT AAS         18         19        342
AAAULA AAH AAGLHT AAT         18         20        360
...
AAAULA AAH AAGLHT AKP         18        656      11808
AAAULA AAH AAGLHT AKQ         18        657      11826
AAAULA AAH AAGLHT AKR         18        658      11844
AAAULA AAH AAGLHT AKS         18        659      11862
AAAULA AAH AAGLHT AKT         18        660      11880
AAAULA AAH AAGLHU AAA         18          1         18
AAAULA AAH AAGLHU AAB         18          2         36
AAAULA AAH AAGLHU AAC         18          3         54
AAAULA AAH AAGLHU AAD         18          4         72
AAAULA AAH AAGLHU AAE         18          5         90
AAAULA AAH AAGLHU AAF         18          6        108
AAAULA AAH AAGLHU AAG         18          7        126
AAAULA AAH AAGLHU AAH         18          8        144
AAAULA AAH AAGLHU AAI         18          9        162
AAAULA AAH AAGLHU AAJ         18         10        180
AAAULA AAH AAGLHU AAK         18         11        198
AAAULA AAH AAGLHU AAL         18         12        216
AAAULA AAH AAGLHU AAM         18         13        234
AAAULA AAH AAGLHU AAN         18         14        252
...
AAAULA AAH AAGLHU AKP         18        656      11808
AAAULA AAH AAGLHU AKQ         18        657      11826
AAAULA AAH AAGLHU AKR         18        658      11844
AAAULA AAH AAGLHU AKS         18        659      11862
AAAULA AAH AAGLHU AKT         18        660      11880
AAAULA AAH AAGLHV AAA         18          1         18
AAAULA AAH AAGLHV AAB         18          2         36
AAAULA AAH AAGLHV AAC         18          3         54
AAAULA AAH AAGLHV AAD         18          4         72
AAAULA AAH AAGLHV AAE         18          5         90
AAAULA AAH AAGLHV AAF         18          6        108
AAAULA AAH AAGLHV AAG         18          7        126
AAAULA AAH AAGLHV AAH         18          8        144
AAAULA AAH AAGLHV AAI         18          9        162
AAAULA AAH AAGLHV AAJ         18         10        180
AAAULA AAH AAGLHV AAK         18         11        198
AAAULA AAH AAGLHV AAL         18         12        216
AAAULA AAH AAGLHV AAM         18         13        234
AAAULA AAH AAGLHV AAN         18         14        252
...
AAAULA AAH AAGLHV ABM         18         77       1386
AAAULA AAH AAGLHV ABN         18         78       1404
AAAULA AAH AAGLHV ABO         18         79       1422
AAAULA AAH AAGLHV ABP         18         80       1440

The above shows that Oracle was packing 660 of these completely NULL rows into each 8KB block with a default PCT_FREE of 10%, leaving about 7370 bytes of space for storing the 660 rows per block, indicating that each row was consuming about 11 bytes.  Obviously from the above, the actual ROWID for a row does not occupy a full 18 bytes of data storage per row.  It is actually 10 bytes (reference) that is externalized as a 18 byte (reference) base 64 encoded character string.  In retrospect, I probably should have done the following instead to decode the ROWID using the DBMS_ROWID package, and calculate the per row overhead plus the space required (1 byte) to store the data:

SELECT
  DBMS_ROWID.ROWID_OBJECT(ROWID) OBJECT_ID,
  DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) FILE_ID,
  DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) BLOCK_NUMBER,
  DBMS_ROWID.ROWID_ROW_NUMBER(ROWID) ROW_ID,
  COUNT(*) OVER (PARTITION BY SUBSTR(ROWID,10,6) ORDER BY ROWNUM) ROWN,
  SUM(11) OVER (PARTITION BY SUBSTR(ROWID,10,6) ORDER BY ROWNUM) BYTES
FROM
  T1
WHERE
  ROWNUM<=1400;

 OBJECT_ID    FILE_ID BLOCK_NUMBER     ROW_ID       ROWN      BYTES

---------- ---------- ------------ ---------- ---------- ----------
     82624          7      1618387          0          1         11
     82624          7      1618387          1          2         22
     82624          7      1618387          2          3         33
     82624          7      1618387          3          4         44
     82624          7      1618387          4          5         55
     82624          7      1618387          5          6         66
     82624          7      1618387          6          7         77
     82624          7      1618387          7          8         88
     82624          7      1618387          8          9         99
     82624          7      1618387          9         10        110
     82624          7      1618387         10         11        121
     82624          7      1618387         11         12        132
     82624          7      1618387         12         13        143
     82624          7      1618387         13         14        154
     82624          7      1618387         14         15        165
...
     82624          7      1618387        655        656       7216
     82624          7      1618387        656        657       7227
     82624          7      1618387        657        658       7238
     82624          7      1618387        658        659       7249
     82624          7      1618387        659        660       7260
     82624          7      1618388          0          1         11
     82624          7      1618388          1          2         22
     82624          7      1618388          2          3         33
     82624          7      1618388          3          4         44
     82624          7      1618388          4          5         55
     82624          7      1618388          5          6         66
     82624          7      1618388          6          7         77
     82624          7      1618388          7          8         88
     82624          7      1618388          8          9         99
     82624          7      1618388          9         10        110
...
     82624          7      1618388        656        657       7227
     82624          7      1618388        657        658       7238
     82624          7      1618388        658        659       7249
     82624          7      1618388        659        660       7260
     82624          7      1618389          0          1         11
     82624          7      1618389          1          2         22
     82624          7      1618389          2          3         33
     82624          7      1618389          3          4         44
     82624          7      1618389          4          5         55
     82624          7      1618389          5          6         66
     82624          7      1618389          6          7         77
     82624          7      1618389          7          8         88
     82624          7      1618389          8          9         99
     82624          7      1618389          9         10        110
...
     82624          7      1618389         77         78        858
     82624          7      1618389         78         79        869
     82624          7      1618389         79         80        880

A comparison test with a second table containing a single column:

CREATE TABLE T2 (
  C1 VARCHAR2(1));

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T2')

INSERT INTO T2 (C1)
SELECT
  NULL
FROM
  DUAL
CONNECT BY
  LEVEL<=500000;

INSERT INTO T2 (C1)
SELECT
  NULL
FROM
  DUAL
CONNECT BY
  LEVEL<=500000;

COMMIT;

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T2')

If the columns containing NULL values required storage space in table T1, then we should expect that table T2 should require less space than that for table T1.

SELECT
  SUM(BYTES) BYTES,
  SUM(BLOCKS) BLOCKS
FROM
  USER_EXTENTS
WHERE
  SEGMENT_NAME='T2'
ORDER BY
  EXTENT_ID;

     BYTES     BLOCKS
---------- ----------
  13631488       1664

The 13,631,488 bytes and 1,664 blocks appears to be the same values as displayed for table T1.

For extra credit we might try something like this:

UPDATE
  T1
SET
  C2=1;

COMMIT;

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1')

SELECT
  NUM_ROWS,
  BLOCKS,
  EMPTY_BLOCKS,
  AVG_ROW_LEN,
  NUM_FREELIST_BLOCKS
FROM
  USER_TABLES
WHERE
  TABLE_NAME='T1';

  NUM_ROWS     BLOCKS EMPTY_BLOCKS AVG_ROW_LEN NUM_FREELIST_BLOCKS
---------- ---------- ------------ ----------- -------------------
   1000000       1630            0           4                   0

The average row length increased from 1 to 4 when the second column of the table was set to a value of 1 for all rows in the table – now the NULL values in column 1 require a single byte of storage space.

Feel free to experiment with this example.





True or False – Direct Path Reads

21 04 2010

 April 21, 2010

(Back to the Previous Post in the Series) (Forward to the Next Post in the Series)

Time for another true or false pop quiz.  Based on the number of views the previous true or false quizzes have experienced, this is a popular series.  However, the questions might be a little too hard to answer correctly - just what is correct is not always easy to determine?  Today’s quiz is on the topic of direct path reads, sparked by one of the test cases that we set up for the Expert Oracle Practices book.

Articles:

  1. http://www.dba-oracle.com/t_direct_path_read_waits.htm (dated June 5, 2009)
  2. http://oracledoug.com/serendipity/index.php?/archives/774-Direct-Path-Reads.html (dated May 22, 2006)
  3. http://oracledoug.com/serendipity/index.php?/archives/1321-11g-and-direct-path-reads.html (dated September 24, 2007)
  4. http://dioncho.wordpress.com/2009/07/21/disabling-direct-path-read-for-the-serial-full-table-scan-11g/ (dated July 21, 2009)
  5. http://antognini.ch/2009/07/impact-of-direct-reads-on-delayed-block-cleanouts/ (dated July 23, 2009)
  6. http://books.google.com/books?id=14OmJzfCfXMC&pg=PA121#v=onepage&q&f=false (dated 2004)

Answering the true or false questions probably requires examining the publication date of the article, if a specific Oracle release is not indicated in the article.  I have had a little bit of difficulty finding the exact (or even approximate) release dates of Oracle’s Database products in the past, so I performed a couple of searches and posted the results below.  The release dates listed below are from the following articles:
http://www.oracle.com/support/library/brochure/lifetime-support-technology.pdf
Metalink Doc ID 742060.1
http://www.orafaq.com/wiki/Oracle_8
http://www.orafaq.com/wiki/Oracle_8i

8         June 1997
8.1.5     February 1999
8.1.7     September 2000
9.0.1     June 2001
9.2.0.1   July 2002
10.1.0.1  January 2004
10.1.0.5  January 2006
10.2.0.1  July 2005
10.2.0.4  February 2008
11.1.0.6  August 2007
11.1.0.7  September 2008
11.2.0.1  September 2009

When answering the quiz, please provide justification for your answers – answer the question “why is the question true or false”.

The quiz:

1.  Direct path read waits will only appear when parallel query is used, for example, a full table scan executed in parallel. 

2.  Parallel query is enabled at the session and instance level by setting a table’s parallel degree to 32.

3.  Setting a parallel degree for a table or index forces the cost-based optimizer to calculate the cost of a full scan of the table (or index) with a lower value than the cost of the corresponding index’s range scan access path.

4.  Physical block reads performed during parallel query operations are always brought directly into the program global area (PGA), completely avoiding the buffer cache. *

5.  The hidden parameter _SERIAL_DIRECT_READ should be set to FALSE to prevent problems caused by excessive direct path reads.

6.  The hidden parameter _SMALL_TABLE_THRESHOLD affects Oracle’s decision to perform direct path reads.





True or False – Buffer Busy Waits

19 04 2010

April 19, 2010

(Back to the Previous Post in the Series) (Forward to the Next Post in the Series)

An earlier blog article provided a couple of links that I found to be helpful when encountering buffer busy waits.  There are certainly other helpful web pages within reach of the nearest search engine.  I thought for today’s True or False quiz I would reference some of the pages found by the Google search engine.

Please read the following articles, and try to answer the questions that follow with either a True or False answer, followed by justification for your answer:

  1. http://dba-oracle.com/art_builder_bbw.htm (updated February 2008)
  2. rafioracledba.blogspot.com/2010/03/buffer-busy-waits-and-remedies.html
  3. yanggq.itpub.net/post/1594/111558
  4. remote-dba.net/oracle_10g_tuning/t_buffer_busy_waits.htm
  5. http://www.jlcomp.demon.co.uk/bbw.html
  6. http://books.google.com/books?id=bxHDtttb0ZAC&pg=PA284
  7. http://books.google.com/books?id=tdRes4IdLiIC&pg=PA1176
  8. http://books.google.com/books?id=TmPoYfpeJAUC&pg=PA434
  9. http://books.google.com/books?id=b3DIkYO2gBQC&pg=PA539

The quiz:

1.  Referencing the Top 5 Timed Events at the top of article #1, a Statspack report that indicates 2,154 waits for the CPU is a possible sign of a problem with buffer busy waits.

2. Referencing the Top 5 Timed Events at the top of article #1, a Statspack report that indicates 2,598 waits on the ‘db file sequential read’ wait event and 25,519 waits on the ‘db file scattered read’ wait event provides significant evidence of buffer busy waits when 7,146 seconds and 3,246 seconds, respectively, were reported for those wait events.

3.  I/O bound Oracle database instances frequently experience buffer busy waits.

4.  ‘db file sequential read’ waits and ‘db file scattered read’ waits appearing in the top 5 list in a Statspack report indicate that the database instance is I/O bound.

5.  Eliminating buffer busy waits in the database instance will also reduce the I/O load in the instance.

6.  To reduce the frequency of buffer busy waits, missing indexes should be created, the freelists for the affected tables and indexes should be increased, and then those objects should be moved to an ASSM tablespace.

7.  Referencing article #1, assume that you query V$SYSTEM_EVENT and find that there were a total of 636,528 buffer busy waits.  The reported number of waits on this event indicate that there were 636,528 distinct waits for a block, and this number of buffer busy waits is an indication of a severe problem.

8.  As of February 2008, a block that is in the process of being read into the buffer cache will trigger a buffer busy wait in another session if that other session requires access to that same block.

9.  As of February 2008, the P3 column of V$SESSION_WAIT indicates the reason code for a buffer busy wait, with a value of 0 indicating that a block is in the process of being read into the buffer cache.

10. Freelist groups should be used to reduce segment header contention.

Tip: Think carefully about the questions – some of the questions might not have obvious answers.








Follow

Get every new post delivered to your Inbox.

Join 139 other followers