Book Review: Oracle Performance Firefighting (Fourth Printing)

June 25, 2010

Many Helpful Topics, However the Errors and Omissions are Unnecessary Distractions
http://resources.orapub.com/Oracle_Performance_Firefighting_Book_p/ff_book.htm

This review covers the June 2010 fourth printing of the “Oracle Performance Firefighting” book.  I bought a PDF copy of the book direct from the OraPub site.  The author’s excellent “Forecasting Oracle Performance” book was a factor in my decision to purchase this book, as was my desire to learn something new while trying to maintain previously acquired knowledge.  As stated in this book, the author has roughly 20 years of experience working with Oracle Database, worked with Cary Millsap at Oracle Corporation for a period of time, and conducts a successful consulting and training business.  I recognize the names of three of the six technical reviewers listed in the book as well known experts in the Oracle community.  The author’s website states that there are no known issues/errata starting with the third printing (November 2009) of the book (reference).  All of the components for the book’s success are in place, so how did the book perform?

There are definitely positive moments in the book, where the book successfully communicates complicated topics using very clear statements.  The book publisher selected a font size and margin size that packs a significant amount of detail into each of the book’s 387 pages.  There are several well-placed helpful diagrams with no space wasting cartoon drawings.  The author introduced his three circle analysis which involves identifying the intersection of the Oracle Database, the Application, and the Operating System to help pinpoint the source of performance problems.  Several examples were provided where the author used the Linux strace command to troubleshoot Oracle Database performance problems.  The book recommends against modifying the _SPIN_COUNT hidden initialization parameter, which is a wise advisement. Clearly states that when there is an operating system bottleneck, there are only 4 subsystems to investigate: CPU, memory, IO, and network.  Provides a good demonstration of using DBMS_MONITOR to enable 10046 traces.  Provides a nice description of in-memory undo and queuing theory in easily understood language.  The author’s website offers the OSM toolkit, which is a collection of scripts that can be freely downloaded – several of the scripts were used throughout the book.  It is obvious that the author invested a significant amount of time into the writing of this book.

There are also definitely negative moments in the book, sections filled with typing errors, logic errors, and omissions.  Signs of problems include a paragraph that includes four hidden initialization parameters that the author suggests to adjust, without the author indicating by how much the initialization parameters should be adjusted, and without the author explaining why three of the four hidden initialization parameters simply do not exist in Oracle Database 11.1.0.7.  Some sections of the book are written in a condescending tone, as if no one other than a DBA is intelligent enough to understand the challenges that a DBA or performance tuning consultant faces, even if the concepts are provided in simple terms to others.  Very few reproducible test cases are provided in the book, which makes it a little difficult for readers to practice or duplicate concepts introduced in the book.

This book is not a replacement for the “Troubleshooting Oracle Performance” book – the two books share very little in common.  This book seems to spend little time explaining how to understand the root cause of the performance problems through various trace file analyses (10046, 10053, 10032, etc.) or even analysis of execution plans, while the author does spend a lot of effort suggesting changes to initialization parameters.  The book’s author is in a somewhat unique position in that he has either a 3 out of 5 star book, or a 5 out of 5 star book depending on the corrections that make it into the fifth printing of the book.  The PDF version of the book cannot be printed, does not permit copying text, and has the purchaser’s name, address, and phone number at the top of each page.

Specific errors, omissions, and distractions in order:

• The book uses a lot of footnotes, some of which do not add value to the discussion of Oracle Database.
• Page 16 suggests to keep working to improve performance until time and budget are exhausted, or performance is acceptable.  I think that this is a pathway to compulsive tuning disorder.
• Page 22 suggests that when the CPU run queue is usually longer than the number of CPU cores, that means that processes are needing to wait for CPU resources.  This suggestion begs the question – isn’t it possible to reach the point of processes needing to wait for CPU resources without the CPU run queue reaching the length of the number of CPU cores?
• Chapters one and two seem to spend a lot of time trying to relate Oracle firefighting concepts to non-computer related activities: telephone operator, school report card, doctor checking a young child, clients requiring the author to keep a timecard to track time, etc.  Additionally, the early chapters excessively repeat the terms firefighting and firefight.
• Page 41: Missing letter “Oracle has never guaranteed it has completely instrumented it code.”
• Page 42: Missing letter “By tracing an Oracle process through the operating system, we can easily observe how Oracle has instrumented it code.”
• Page 43: Incorrectly defines the meaning of a db file scattered read wait event when attempting to correlate a Linux readv OS call 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.”  One of the problems with the quote is that a multiblock read from the operating system could correspond to several different Oracle wait events.  A second problem is that the readv call reads logically adjacent sections of a file (think multiple Oracle blocks) on disk into non-adjacent memory locations (reference).
• Page 47: While Table 2-1 states that only selected columns of V$SYSTEM_EVENT are displayed, the table probably should have included the TOTAL_TIMEOUTS column, which helps describe the true length of wait events that have, for instance, three second timeouts – this column has existed since Oracle Database 8.1.5.  The table also states that the WAIT_CLASS, WAIT_CLASS_ID, AND WAIT_CLASS# columns (the book incorrectly includes an underscore between CLASS and #) were introduced in 11g R1, while the Oracle documentation shows that those columns were introduced in 10g R2 (reference).
• Page 49: While Table 2-2 states that only selected columns of V$SESSION_WAIT are displayed, the table seems to discount the value of the SECONDS_IN_WAIT column by excluding that column.  The table also states that the WAIT_CLASS, WAIT_CLASS_ID, AND WAIT_CLASS# columns (the book incorrectly includes an underscore between CLASS and #) were introduced in 11g R1, while the Oracle documentation shows that those columns were introduced in 10g R2 (reference).  The table should provide a better description for the STATE column’s value of “WAITED KNOWN TIME: Previous wait completed; the session is currently not waiting.” – the book should describe what does this STATE value means: that the session is in an idle wait event, or that the session is currently burning CPU time (this is what it means).
• Page 55: States “Systems based on Oracle Database 10g and later can take advantage of the v$sys_time_model and v$ses_time_model views…” – the session level view is actually named V$SESS_TIME_MODEL.
• Page 61: The book strongly suggests that a user’s “Submit” or “Save” operation equates to a COMMIT, and that a COMMIT equates to a LOG FILE SYNC wait event, and therefore if the average LOG FILE SYNC wait time is identical to a previous day when performance was acceptable, the performance problem is “most likely not related to the database.”  A lot may be said about where this paragraph fails the reality check – just think of one other operation that may happen when the user selects the “Save” option, or one other cause for the LOG FILE SYNC wait, or how averages may distort the true slowness that is experienced by the user, or how the author is simply measuring the wrong thing when trying to determine if the problem is present in the database (reference).
• Page 68: Example of condescending tone: “When working on Oracle systems, you will eventually hear some poor whiner say something like, ‘Oracle is a resource hog!  It consumes every bit of resources it can get its despicable hands on!’”  More examples of condescending/confrontational tone with the server admin, storage admin, storage vendor, etc. are found on pages 128, 130, 132, 148, 156, 201, 264, 324.
• Page 74: Example of an odd footnote that does not add value to the book: “I am in no way implying Oracle sincerely cares how much memory its database system requires.”
• Page 94: The author indicated that one of his tests that modified the _KKS_USE_MUTEX_PIN parameter showed that the mutex implementation decreased the execution time by 11%, yet the author did not provide a SQL statement to create the test table, nor provide the PL/SQL script used in the test so that readers could reproduce the test results.
• Page 104: The book states: “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…”  The book fails to mention that activities in Oracle Database itself directly cause kernel mode CPU utilization.  Network I/O,  disk I/O (especially with buffered file systems), timer calls (typically gettimeofday calls),  memory access requests, memory management, and process creation and termination (think session creation and termination) all directly cause kernel mode CPU utilization.  The paragraph seems to suggest that the DBAs has no control over kernel mode CPU consumption (reference reference2).
• Page 116: States: “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 an operating system specific observation.  Linux appears to behave this way, Windows does not, and others have indicated that various Unix releases do not behave the same as Linux (reference).
• Page 136: States: “For example, if you ran a response time-focused report over a duration of 60 seconds, and if the single-CPU core subsystem were heavily overworked, Oracle could report that Oracle processes consumed 65 seconds of CPU time. Yet, we clearly know the CPU subsystem is limited to supplying up to 60 seconds of CPU time and no more.”  This appears to be an operating system specific CPU usage measurement problem.  Windows will exhibit this problem, where time spent in the run queue is accumulated with time spent actually running on the CPU, but I could not reproduce that behavior on Linux (reference).
• The book seems to use Oracle 10g R1 frequently for tests, rather than something more recent, such as 10g R2, in various tests.  Oracle 10g R1 was desupported quite a long time ago, while the 10.2.0.5 patch was only recently released.  In the span of a couple of sentences, the book frequently uses the term “Oracle” to mean both “Oracle Database” and “Oracle Corporation” – this might cause some degree of confusion.
• Page 175: The book seems to suggest that bind variable values are typically included in TKPROF summaries, except when multiple trace files are combined.  Bind variable values are never included in TKPROF summaries.
• Page 177: The book seems to recommend changing hidden initialization parameters without warning that those parameters should only be modified after consulting Oracle support.
• Page 182: The book suggests modifying the _ASH_SAMPLING_FREQUENCY hidden initialization parameter.  Unfortunately, this parameter does not exist in Oracle Database 11.1.0.7 (the only release that I checked).
• Page 183: The book suggests that changing the _ASH_SIZE hidden initialization parameter will modify the amount of memory used by Active Session History, yet the book mentions that Oracle does not always respect the setting.  This might be a sign that the parameter controls something other than what the author described.  The author also mentioned changing the  _ASH_ENABLE hidden initialization parameter.
• Page 200: The author suggests that the _DB_BOCK_MAX_CR_DBA hidden initialization parameter controls the number of clones per buffer, however that parameter does not exist in Oracle Database 11.1.0.7.  I believe that the author intended to write _DB_BLOCK_MAX_CR_DBA which attempts to control the maximum number of consistent read copies of a block.
• Page 203: The author suggests that changing the _DB_BLOCK_HASH_LATCHES hidden initialization parameter will provide some relief to cache buffer chains latch contention.  This is one of the few times that the author cautions, “Before you change this, be aware there may be support issues from both Oracle and your application vendor.”
• Page 208: The book states that the _SMALL_TABLE_THRESHOLD hidden initialization parameter was deprecated because of the modified LRU (Least Recently Used) algorithm that the book stated was introduced in Oracle Database 8.1.5, and the hidden initialization parameter assumed new life with the introduction of Oracle Database 11g where the “parameter is the threshold for a server process to start issuing direct reads.”  Metalink (My Oracle Support) Doc ID 787373.1 indicates that the parameter was never deprecated.  The actual threshold for selecting a direct path read for a serial operation in Oracle Database 11.1.0.6 and above is roughly five times the value specified for _SMALL_TABLE_THRESHOLD.  Various other articles describe the purpose of the _SMALL_TABLE_THRESHOLD parameter prior to Oracle Database 11.1.0.6. (reference reference2 reference3)
• Page 213: The book states: “Without physical blocks being read from disk, there will be no LRU chain latch contention, because there will be no need to find a free buffer or insert a buffer header into an LRU chain.”  This statement begs the question, what about consistent read copies of blocks, where Oracle must essentially roll back a block to a specific SCN to provide a consistent read for an in-process query?
• Page 219: The book states: “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…”  One problem with these two sentences is that the _DB_BLOCK_WRITE_BATCH, _DB_WRITER_MAX_SCAN_PCT, and _DB_WRITER_MAX_SCAN_CNT hidden initialization parameters do not exist in Oracle Database 11.1.0.7 (the only release checked) (reference).
• Page 221: The book states: “Now suppose you have scanned more than _db_writer_max_scan_pct buffer headers. If so, you would be very frustrated.”  Unfortunately, the _DB_WRITER_MAX_SCAN_PCT hidden initialization parameter does not exist in Oracle Database 11.1.0.7 (the only release checked).
• Page 222: The book states: “Increase _db_writer_max_scan_pct. This will give the database writer more time to flush its write list.”  Unfortunately, the _DB_WRITER_MAX_SCAN_PCT hidden initialization parameter does not exist in Oracle Database 11.1.0.7 (the only release checked).
• Page 223: The book states, “Strangely, in Oracle Database 11g, Oracle removed the reason code from the v$session_wait and v$session view’s p3 column!”  Column P3 was changed from the reason code to block class starting with Oracle Database 10g R1.
• Page 226: The author used block dumps to investigate locks, while it seems that it would be easier to query a couple of views.
• Page 230: The book states, “… Simply keep adding free lists until the contention subsides.” This begs the question of what side-effects may be encountered?
• Page 231: The book describes how active transactions are assigned to undo segments.  Limited testing indicates that the book is correct unless the objects are created in the SYSTEM tablespace (creating objects in that tablespace is not recommended).
• Page 253: The book used library_cache dumps to investigate child cursors – it seems that there are easier, less resource intensive ways to obtain this information.
• Page 256: The book states: “Oracle keeps track of these reloads in the reloads column from v$library_cache…” – the correct view name is V$LIBRARYCACHE, not V$LIBRARY_CACHE.
• Page 265: The book mentions the use of CURSOR_SHARING = SIMILAR and FORCE, but does not discuss the performance impact of the change other than stating that Mark Gury stated that CURSOR_SHARING = FORCE can result in incorrect results.  No other discussion of performance problems were provided.
• Page 284: The book describes the author’s under-powered Linux server used in a test.  The server was configured with 512MB of memory, 256MB shared pool, 4MB for the buffer cache and was running Oracle Database 11g Release 1.  The Oracle documentation (reference) states that 1GB of memory is the minimum for Oracle Database 11g Release 1, and that puts into question the validity of any tests performed with that server.  The details of the test case, which obtained a 21% decrease in CPU usage through the use of in-memory undo over the configuration with in-memory undo disabled, was largely omitted, making it impossible for readers to duplicate the test.
• Page 286: The book lists a parameter named COMPATIBILITY, however that parameter does not exist.  The actual parameter name is COMPATIBLE.
• Page 289: The book states: “Increase the number of IMU pools by increasing the _im_pools parameter…”  The _im_pools hidden initialization parameter does not exist in Oracle Database 11.1.0.7 (the only release checked) – the author probably intended to write _IMU_POOLS.
• Page 294: The test script shows that redo statistics for a session are recorded, the session sleeps for 5 seconds, the session queries a table, and then the redo statistics are recorded again.  The author attempted to explain that redo could be generated by a session merely by querying a table due to delayed block cleanout.  The test case is slightly unclear, making it difficult for readers to reproduce the results – there is no point in the session sleeping for 5 seconds, it would have been helpful if the author explained what changes were made to the CUSTOMERS that eventually resulted in the delayed block cleanouts, and because the statistic number for the redo size statistic is prone to change from one Oracle release to the next it would have made more sense to specify SN.NAME=’redo size’ rather than essentially specifying SN.STATISTIC#=133.
• Page 297 states: “… 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…”  The _LOG_SMALL_ENTRY_MAX_SIZE hidden initialization parameter does not exist in Oracle Database 11.1.0.7 (the only release checked).
• Page 298 states: “The DBA can specifically set the number of redo strands via the hidden parameter _log_parallelism.”  The _LOG_PARALLELISM hidden initialization parameter does not exist in Oracle Database 11.1.0.7 (the only release checked).
• Page 300 states: “Global temporary tables can be explicitly dropped. When this occurs, the associated indexes and views are also dropped.”  The associated indexes will be dropped assuming that the temporary table is also purged, however affected views will not be dropped and will have a status of INVALID.
• Page 301 states: “Object growth can be observed by repeatedly by querying v$sort_usage_view.  Global temporary tables create 50% less redo than standard tables.” This quote has a minor grammar error, and the view name is V$SORT_USAGE (should not include _VIEW in the name).  The question remains how the author determined that working with global temporary tables generates half as much redo as regular heap tables.
• Page 302: Another instance where the author correlates an application save with a LOG FILE SYNC wait and concludes that the problem is not in the database instance.
• Page 305: While it is mentioned on the next page in another section, it probably would have made sense to mention that the redo log buffer is also flushed every three seconds and repeated that the redo log buffer is also by default flushed after every COMMIT.
• Page 314: The footnote states: “When a process is serviced by one CPU and then is switched to another CPU, it undergoes a context switch… Exclusively binding a CPU and process together virtually eliminates context switching, because the CPU will always be there for that process and the process will never need to wait while the operating system switches its processing to another CPU.”  The author seems to have either redefined the meaning of context switch, or the footnote needs additional clarification (reference reference2).
• Page 315 states: “The redo log buffer is structured in relatively small operating system-size blocks (typically 512 bytes), not Oracle-size blocks, so it is normal for log writer activity to be performed with multiple blocks.”  The Oracle documentation (reference) states that the redo log file block size defaults to the physical sector size of the disk, which is typically 512 bytes.
• Page 322 states: “The heavy redo-generating SQL will obviously be some type of DML, like inserts, updates, deletes, or perhaps create statements. If you have ASH licensed, as Figure 8-9 demonstrated, you can easily find the SQL related to the log file parallel write event.” – The heavy redo generating SQL statement could also be a SELECT statement due to delayed block cleanout (as mentioned earlier in the book), a CREATE statement would likely be classified as DDL.  Since the LOG FILE PARALLEL WRITE wait event is posted by LGWR and not a user session, there probably will not be a SQL statement that is definitely associated with the LOG FILE PARALLEL WRITE wait event.
• Page 324: The book makes a claim that is difficult to verify: “Using global temporary tables can cut redo generation by 50%, substantially reducing Oracle’s overall IO requirements. This alone could solve the log file parallel write problem.”
• Page 326 states: “If the log writer encounters a nonarchived redo log, it will stop writing and post the wait event log file switch (archive incomplete).”  The actual wait event that will be posted is log file switch (archiving needed).
• Pages 327 – 387 seem to be related to the author’s “Forecasting Oracle Performance” book, with concepts written in an easier to understand, more fluid writing style.  This is not a problem, just worth mentioning.
• Page 330: The word “difference” should be replaced with “different”.
• Page 330 states: “For example, the Statspack facility typically collects data in 60-minute intervals and stores the data in tables starting with stats$ (the key table is stats$snap).”  There is no typical collection period for Statspack, while AWR snapshots default to 60 minutes in duration.
• Page 333 states: “In previous chapters, I have presented how to gather CPU requirements from the v$sesstat, v$sysstat, v$ses_time_mode, and v$sys_time_model views.”   V$SES_TIME_MODEL should be written as V$SESS_TIME_MODEL.
• Page 343 states: “Also, Oracle CPU consumption includes Oracle processes waiting for CPU and also queuing for CPU. As a result, in a CPU-saturated system, Oracle may report CPU consumption higher than actually occurred.”  The behavior mentioned by the author is operating system dependent.
• Page 344 states: “In other words, Oracle does not have the ability to split CPU time into service time and queue time. When we gather CPU consumption from either the instance statistics views or the system time model views, what we collect as CPU time and typically classify as CPU service time actually contains both CPU service time and CPU queue time. Oracle has no way of knowing the difference and reports the total, which is, in the truest sense, CPU response time.” This behavior is operating system dependent.
• Page 346 states: “The arrival rate in Figure 9-9, which is the horizontal axis, is simply the number of logical IOs (v$sysstat: buffer gets plus consistent gets) processed per millisecond.”  There is no statistic in V$SYSSTAT that is named buffer gets, the author probably should have stated db block gets instead. (reference).
• Page 347 states: “The instance statistic physical reads signifies the number of Oracle blocks that Oracle server processes had to request from the operating system because the blocks did not reside in Oracle’s buffer cache.”  The physical reads statistic includes block reads that will never hit the buffer cache, including blocks read into the PGA.  It appears that the author is describing the physical reads cache statistic instead.
• Page 349 states: “…when focusing on a particular session or group of sessions, use v$sesstat, v$ses_time_model, and v$session_event.” The correct view name is V$SESS_TIME_MODEL.

The “Oracle Performance Firefighting” book has a lot of potential for being helpful, however the errors and omissions found in the fourth printing of the book give the book a decidedly dangerous edge.  If flipping initialization parameter values fits your style, this book will be a good fit.

Pages 352 though 387 are currently not included in this book review.

(Section added December 13, 2010): Related blog articles that discuss the contents of this book:

• DB File Scattered Read Wait Event – What is Wrong with this Quote?
• CPU Run Queue – What is Wrong with this Quote?
• Log File Sync – What is Wrong with this Quote?
• System/Kernel Mode CPU Usage – What is Wrong with this Quote?
• _SMALL_TABLE_THRESHOLD Parameter and Buffer Cache – What is Wrong with this Quote?
• Database Writer Parameters – What is Wrong with this Quote?
• Undo Segments – What is Wrong with this Quote?
• Redo Log Buffer – What is Wrong with this Quote?

Redo Log Buffer – What is Wrong with this Quote?

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?

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?

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?

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?

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?

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?

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?

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?

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

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

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?

Book Review: Oracle SQL Recipes: A Problem – Solution Approach

June 6, 2010 (References Last Updated June 7, 2010)

Answering the Question – What can I do with SQL? A Handful of Errors Tarnish a Great Book
http://www.amazon.com/Oracle-SQL-Recipes-Problem-Solution-Approach/dp/1430225092

Last November I pre-ordered this book, hoping that it would pick up where the “Mastering Oracle SQL and SQL*Plus” book ended (at roughly Oracle Database release 10.1.0.3).  The expectations were very high, and this book delivers for the most part.  Some parts of the book left me scratching my head, thinking that this SQL statement or paragraph just cannot be correct.  Even when taking those sections into account, this book conveys a significant amount of practical, useful information.

The book’s format of problem statement, solution, and how/why the solution works is the key to the success of this book.  You will not find page after page of SQL statement syntax.  The range of problem statements covered in this compact 500 page book is impressive, touching on a wide range of topics:

• Basic SELECT statements
• Manipulating numbers, dates, and character data
• Transaction management
• XML data
• Partitioning
• Analytic functions
• Regular expressions
• Performance tuning
• Database troubleshooting
• Creating databases, tablespaces, tables, indexes, and constraints

Positives:

• Thoroughly answers the question of why it is important to learn Oracle’s dialect of SQL.  The book provides 233 problem situations, most with more than one variation of the problem along with the SQL statement for each variation.
• Includes interesting side stories that are not necessarily related to SQL coding, but help to keep the reader interested in the book contents.  For example, a side story titled “Beer and Statistics Do Mix” that describes the invention of T-Tests.
• Typically states when a feature requires the Enterprise Edition or an additional cost license beyond the Enterprise Edition (Tuning Pack, for example).
• Most, if not all of the code samples from the book may be downloaded from the Apress website.
• Describes the LEAD, LAG, RANK, DENSE_RANK, ROW_NUMBER, SUM, MIN, MAX, NTILE and a couple of the other common analytic functions.  Does not cover all of the analytic functions, for example: RATIO_TO_REPORT, FIRST_VALUE, LAST_VALUE, PERCENTILE_DISC, etc.
• Recipe 5-8 shows how to create a PL/SQL function that converts from base 10 to binary, octal, and hex.
• Recipe 11-9 shows an impressive example that creates a HTML chart from a SQL statement.  It might be interesting to see this example extended to use floating objects with absolute positioning rather than HTML tables.
• Recipes 12-3 and 12-4 include PL/SQL functions that help to determine if a VARCHAR2 value can be safely converted to a NUMBER or DATE.

Problems (in order, not all code samples tested):

• In recipe 1-1, retrieving data from a table, the authors mentioned “currently set schema,” a concept which probably would have been much better to introduce in a later recipe.  Especially in the early sections of the book, it seems that the authors struggled with what concepts to introduce early and what concepts required a more establish foundation.  As such, people just learning SQL, or even just Oracle’s dialect, will struggle with the early sections of the book.
• Several recipes (1-4, 2-11, 2-14, 2-15, 2-16, 6-5, 6-14) use implicit date conversions, or explicit varchar to date conversions without specifying the format of the date contained in the varchar.  This is a very poor practice to teach to people who are just beginning the process of learning SQL.  A change in the NLS parameters, or even a change in the connection method from Oracle’s ODBC driver to Oracle’s OLEDB driver is sufficient to cause such SQL statements to fail.  Unfortunately, the TO_DATE function was not introduced until chapter 6.
• Page 26 incorrectly stated, “for performance reasons, Oracle will implicitly sort the data to match the grouping desired.”  As of Oracle Database 10.1 [edit: probably should state 10.2 for hash group by, previous releases did not necessarily need to perform a sort], more often than not a hashing algorithm will be used to perform GROUP BY operations, which means that there will be no implicit sort of the data. [Reference/Test Case] [Reference/Test Case]
• Recipe 3-5 attempted to explain when to use three different syntaxes to delete rows from a table (IN, EXISTS, and SELECT with USING clause).  As may be confirmed with a 10053 trace file, the optimizer will often automatically re-write SQL statements from one form to another, for example from an IN clause to a standard join, or an EXISTS clause to a standard join.  The authors probably should have mentioned the automatic query re-write capability of the cost-based optimizer, rather than stating something like this about an EXISTS clause example “this is not as elegant as the first solution, but might be more efficient.” [Reference/Test Case – Check the Comments Section Also]
• Recipe 5-11 demonstrates how to use the FOR UPDATE clause of a SELECT statement and demonstrates that the second session will be blocked until the first session commits or issues a rollback.  At this stage of the book the NOWAIT clause is not introduced.  Unfortunately, such a demonstration might be an invitation to “lost” updates.
• The query output for recipe 6-6 does not match the expected output, which may lead to confusion.  The book shows that the result of the timezone conversion is “13-AUG-09 06.00.00.000000 PM AMERICA/LOS_ANGELES” when it should be “13-AUG-09 06.25.00.000000 PM AMERICA/LOS_ANGELES”
• Recipe 6-12, which uses the EXTRACT function, will produce incorrect/unexpected results, resulting in the MONTHS calculation increasing by one up to 15 days earlier than should be expected if used to determine the length of time an employee has worked.  That recipe would have been the perfect time to introduce the MONTHS_BETWEEN SQL function (not included in the book). [Reference/Test Case – Check the Comments Section Also]
• Recipe 7-1 states that Oracle will never use a non-function based index on a column when that column appears inside the INSTR function that is present in a WHERE clause.  That statement is incorrect, a hint, covered in recipe 19-12, is sufficient to allow Oracle to perform an index full scan operation on that column when the only predicate in the WHERE clause includes that index’s column wrapped in an INSTR function (tested on Oracle releases 10.2.0.4 through 11.2.0.1). [Reference/Test Case – Check the Comments Section Also]
• Recipe 8-2 states, “With the value one-third stored in each column (BINARY_DOUBLE and NUMBER datatypes), we can use the VSIZE function to show it was much more complicated to store this with decimal precision, taking nearly three times the space.”  There are a couple of problems with this statement.  First, a BINARY_DOUBLE column will always require eight bytes to store any number, while it takes a variable amount of space to store a number in a NUMBER column (for instance, the number 1,000,000 requires fewer bytes to store than the number 333).  Second, the BINARY_DOUBLE column only maintained 16 significant digits to the right of the decimal point, while the NUMBER column maintained 40 significant digits to the right of the decimal point (the bytes required can be decreased by specifying a smaller number of significant digits to the right of the decimal point). [Reference/Test Case – Check the Comments Section Also]
• Recipe 8-6 states “The final two implicit defaults (of a CREATE SEQUENCE clause) cause Oracle to cache 20 values and not force strict first-come, first served ordering on those who call NEXTVAL.”  This statement is either misleading or incorrect.  The default will force the sequence numbers to be assigned to calling sessions in the sequential order specified by CREATE SEQUENCE – Oracle will not provide the numbers out of sequential order.  There can be some degree of out of sequential order assignment in a RAC environment.
• Recipe 9-2, which includes a SQL statement that is intended to show the objects which are locked that are blocking other sessions, has a number of problems.  1) It is not RAC aware – it should be selecting from the GV$ views, rather than the V$ views.  2) The SQL statement explicitly looks for the BLOCK column to be equal to 1 – in a RAC environment it could potentially show a value of 2.  3) When performing a self-join on V$LOCK, the query is only joining on the ID1 column of V$LOCK, while the documentation shows a self-join on that view using the ID1, ID2, and TYPE columns.  4) There appears to be a missing join condition between V$LOCK and V$LOCKED_OBJECT that results in objects being identified as contributing to the blocking of the second session, when in fact that may not be the case.  5) The query seems to assume that all locks acquired are full table locks. [Reference/Test Case – Check the Comments Section Also]
• Page 436 suggests checking Metalink (My Oracle Support) Doc ID 555284.1 for a script to identify indexes that should be rebuilt.  I suspect that that document looked very different prior to the last modification date of May 13, 2009.  The script in the document does not detect indexes that are candidates for a rebuild – the script simply outputs SQL statements to rebuild every index that is not owned by SYS or SYSTEM (and a small number of other users) into an INDX tablespace with a specified initial, next, and maxextents clause.
• Recipe 18-13, in the Note section, states that when a primary key constraint is created Oracle will create a unique index with the same name as the constraint.  That statement is true unless a unique or non-unique index already exists for the column or a composite index that has that column as its leading column exists.  Later in the recipe it was stated that the DBA could pre-create the index that will be used to help enforce the primary key constraint, but the Note section might cause a little confusion. [Reference/Test Case – Check the Comments Section Also]
• Recipe 19-1, the join between V$SQL_MONITOR and V$SQL is incomplete and will result in repeated rows if there are multiple child cursors for a SQL_ID, which seems to happen frequently starting with Oracle Database 11.1.0.6 due to adaptive cursor sharing, and 11.2.0.1 due to cardinality feedback.  Additionally, V$SQL_MONITOR will only be populated if the CONTROL_MANAGEMENT_PACK_ACCESS initialization parameter is set to DIAGNOSTIC+TUNING to enable the extra cost licenses (Tuning Pack, for example). [Reference/Test Case]
• Recipe 19-3 states “the V$SESSION_LONGOPS view gives you a rough idea of how long a currently running query has left to finish.”  Technically, this statement is both correct and incorrect.  The view provides a rough idea, based on the execution plan estimates, of how much time is left for a specific stage (step/line) in the execution plan.  Entries in the V$SESSION_LONGOPS view will appear and disappear as the different stages in the execution plan execute.  Most index type accesses will likely complete in less than six seconds per access, and probably will not appear in this view. [Reference/Test Case – Check the Comments Section Also]
• Recipe 19-4, the query may produce misleading results as the query is not time-scoped.  The query does not consider that the database may have been up for several years – a query that on average consumes one second of CPU time, but was executed 100 million times since the library cache was last purged will likely be flagged as a problem, while a query that on average consumes 1,000 seconds of CPU time but has only executed 1,000 times in the last 24 hours probably will not appear in the SQL results.
• Recipe 19-7 states that a PLAN_TABLE must exist, and that if it does not exist the utlxplan.sql script should be run to create a PLAN_TABLE in the user’s schema. As of Oracle Database 10.1.0.1 [Edit: it appears that this change happened prior to 10.1.0.1] user schemas should no longer have a PLAN_TABLE in their schema – a global temporary table owned by the SYS user is used instead. [Reference/Test Case – Check the Comments Section Also]
• Chapter 19 demonstrates the use of EXPLAIN PLAN, AUTOTRACE, and the EXPLAIN option of TKPROF, but the chapter does not mention that using those methods for displaying the execution plan could show the wrong execution plan. [Reference/Test Case – Check the Comments Section Also]
• Recipes 19-10 and 19-12 suggest that a goal of tuning is reducing the calculated COST value that appears in the execution plan, and this was accomplished in the recipes by adding an index or using an index hint.  This is not a good approach to tuning; it is much better to rely on actual response time rather than the calculated cost estimates. [Reference/Test Case – Check the Comments Section Also] [Reference/Test Case – Check the Comments Section Also]
• Recipe 20-1, the SQL statements are not time-scoped, and even if the database has only been open for a short period of time, the SQL statement could yield incorrect results because the “CPU used by this session” statistic is not updated in real-time, only when a SQL statement completes, or a fetch completes. [Reference/Test Case – Check the Comments Section Also]
• Page 500 suggests inspecting V$SESSION to determine the type of block contention that causes buffer busy waits.  It would have been nice to see the authors provide a demonstration of this technique because from what I am able to determine, the chances of a success are extremely small using V$SESSION (I assume that the authors might be suggesting to look at either the combination of P1, P2, and P3 or the combination of ROW_WAIT_OBJ#, ROW_WAIT_FILE#, and ROW_WAIT_BLOCK#).
• Page 500 states that the “SQL*Net message from client” wait event indicates possible network or middle-tier bottlenecks.  Client-side “think time” is probably the most significant contributor to this wait event, and I think that it would be very difficult to demonstrate that this wait event indicates network bottlenecks. [Reference/Test Case] [Reference/Test Case] [Reference/Test Case – Check the Comments Section Also]
• Recipe 20-8 suggests an approach to identify those indexes that are safe to drop by enabling monitoring of the indexes.  One of the problems with this approach is that the index may still be used by the cost-based optimizer even when monitoring reveals that the index is never used. [Reference/Test Case – Check the Comments Section Also] [Reference/Test Case – Check the Comments Section Also]
• Very few cross-recipe (forward and backward) references were provided, which makes the sections of the book appear to be disconnected and stand-alone.

The book attempts to cover a lot of ground.  While it probably requires the authors between one and eight hours per page to research, write, proof-read, test, and format a typical page in an Apress book, the authors probably could have controlled the scope of the book a little better and spent a little more time to test and expand some of the sections of the book – the sections that discuss analytic functions, for example.  I did not spend a lot of time reading through the chapters “Working with XML Data”, “Partitioning”, or “LOBS”, so I did not comment on those sections of the book.  The majority of the book is exceptional – far better than any book that is strictly a SQL reference type book, and I would not hesitate to recommend the book to others who are trying to figure out: “What do I do with SQL.”

I will probably update this review at a later time to supply links to test cases or articles for most of the problems that were identified with the book.  I might also take a closer look at the “Working with XML Data”, “Partitioning”, or “LOBS” chapters, and provide feedback for those sections of the book.

(Section added December 13, 2010): Related blog articles that discuss the contents of this book:

•  The INSTR Function will Never Use an Index? I will give You a Hint
• Date Delta SQL – What is Wrong with this SQL Statement?
• Lock Watching – What is Wrong with this SQL Statement?
• Measuring Numbers – Is this a Valid Comparison?

SQL – Experimenting with Case Insensitive Searches

June 4, 2010

Have you ever read about something, or heard about something, and wanted to be able to reproduce it?  Have you ever been warned that doing something is not a good idea because a specific problem is certain to happen, yet you do it anyway just to see if the problem can be avoided?  I recently read (again) about performing case insensitive searches in Oracle… as is the default behavior on SQL Server.  So, let’s try a couple of experiments.

First, we need a test table with a primary key index:

CREATE TABLE T9 (
  C1 VARCHAR2(20),
  C2 VARCHAR2(200),
  PRIMARY KEY (C1));

INSERT INTO
  T9
SELECT
  CHR(65+MOD(ROWNUM-1,26))||
    CHR(65+MOD(CEIL(ROWNUM/26)-1,26))||
    CHR(65+MOD(CEIL(ROWNUM/676)-1,26)),
  RPAD('A',200,'A')
FROM
  DUAL
CONNECT BY
  LEVEL<=17576
UNION ALL
SELECT
  CHR(97+MOD(ROWNUM-1,26))||
    CHR(97+MOD(CEIL(ROWNUM/26)-1,26))||
    CHR(97+MOD(CEIL(ROWNUM/676)-1,26)),
  RPAD('A',200,'A')
FROM
  DUAL
CONNECT BY
  LEVEL<=17576
UNION ALL
SELECT
  CHR(65+MOD(ROWNUM-1,26))||
    CHR(65+MOD(CEIL(ROWNUM/26)-1,26))||
    CHR(97+MOD(CEIL(ROWNUM/676)-1,26)),
  RPAD('A',200,'A')
FROM
  DUAL
CONNECT BY
  LEVEL<=17576
UNION ALL
SELECT
  CHR(65+MOD(ROWNUM-1,26))||
    CHR(97+MOD(CEIL(ROWNUM/26)-1,26))||
    CHR(97+MOD(CEIL(ROWNUM/676)-1,26)),
  RPAD('A',200,'A')
FROM
  DUAL
CONNECT BY
  LEVEL<=17576
UNION ALL
SELECT
  CHR(97+MOD(ROWNUM-1,26))||
    CHR(97+MOD(CEIL(ROWNUM/26)-1,26))||
    CHR(65+MOD(CEIL(ROWNUM/676)-1,26)),
  RPAD('A',200,'A')
FROM
  DUAL
CONNECT BY
  LEVEL<=17576
UNION ALL
SELECT
  CHR(97+MOD(ROWNUM-1,26))||
    CHR(65+MOD(CEIL(ROWNUM/26)-1,26))||
    CHR(65+MOD(CEIL(ROWNUM/676)-1,26)),
  RPAD('A',200,'A')
FROM
  DUAL
CONNECT BY
  LEVEL<=17576;

COMMIT;

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

The above test table is filled with one column having a three letter sequence (AAA, BAA, CAA,… ABA, BBA,… AZZ, … ) with a couple variations of upper and lowercase letters.  The other column is padded to 200 characters to intentionally discourage Oracle’s optimizer from using a full table scan when a suitable index is available.  Now the test (on Oracle Database 11.1.0.7).

To begin, we will explicitly list the upper and lowercase versions of the letters that are of interest:

SET AUTOTRACE TRACEONLY EXPLAIN

SELECT
  C1,
  C2
FROM
  T9
WHERE
  C1 IN ('ABC','abc','ABc','Abc','abC','aBC');

Plan hash value: 2861409042

---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |     6 |  1230 |    13   (0)| 00:00:01 |
|   1 |  INLIST ITERATOR             |              |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T9           |     6 |  1230 |    13   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | SYS_C0016172 |     6 |       |     7   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("C1"='ABC' OR "C1"='ABc' OR "C1"='Abc' OR "C1"='aBC' OR "C1"='abC' OR
              "C1"='abc')

As can be seen, an index unique scan was performed for each of the values in the IN list.  The calculated cost is 13, and the optimizer is correctly predicting that 6 rows will be returned.

Next, we will try the brute force method, using the UPPER function on the C1 column:

SELECT
  C1,
  C2
FROM
  T9
WHERE
  UPPER(C1) = 'ABC';

Execution Plan
----------------------------------------------------------
Plan hash value: 3973213776

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  1055 |   211K|   380   (2)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T9   |  1055 |   211K|   380   (2)| 00:00:02 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(UPPER("C1")='ABC')

The calculated cost is now 380, compared to the earlier value of 13, and Oracle is predicting that 1,055 rows will be returned – the increased cost likely explains why the optimizer did not use a full table scan for the IN list version of the SQL statement.

Next, let’s try something that is not supposed to work, using an index when a function is applied to that index’s column in the WHERE clause:

SELECT /*+ INDEX(T9) */
  C1,
  C2
FROM
  T9
WHERE
  UPPER(C1) = 'ABC';

Plan hash value: 1084614729

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |  1055 |   211K|  1332   (1)| 00:00:06 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T9           |  1055 |   211K|  1332   (1)| 00:00:06 |
|*  2 |   INDEX FULL SCAN           | SYS_C0016172 |  1055 |       |   277   (2)| 00:00:02 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(UPPER("C1")='ABC')

The primary key index was used, but note that this access is an index full scan where every block in the index is accessed (using single block reads), rather than an index unique scan as happened with the first SQL statement.  The calculated cost also increased again, this time to 1,332.

Now what?  Well, we can tell Oracle to perform case insensitive matches:

ALTER SESSION SET NLS_SORT=BINARY_CI;
ALTER SESSION SET NLS_COMP=LINGUISTIC;

SELECT
  C1,
  C2
FROM
  T9
WHERE
  C1 = 'ABC';

Plan hash value: 3973213776

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |   205 |   382   (3)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T9   |     1 |   205 |   382   (3)| 00:00:02 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(NLSSORT("C1",'nls_sort=''BINARY_CI''')=HEXTORAW('61626300') )

But now we are back to a full table scan and the calculated cost of that full table scan is 382, rather than the 380 that we saw earlier.  However, Oracle is expecting to retrieve only a single row now, not the actual 6 rows nor the 1,055 rows when a full table scan appeared earlier in this article.

So let’s force an index access path with a hint just to see what happens:

SELECT /*+ INDEX(T9) */
  C1,
  C2
FROM
  T9
WHERE
  C1 = 'ABC';

Plan hash value: 1084614729

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |     1 |   205 |   279   (3)| 00:00:02 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T9           |     1 |   205 |   279   (3)| 00:00:02 |
|*  2 |   INDEX FULL SCAN           | SYS_C0016172 |     1 |       |   278   (3)| 00:00:02 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(NLSSORT("C1",'nls_sort=''BINARY_CI''')=HEXTORAW('61626300') )

There is the index access path, and notice that the calculated cost for this access path is less than that of the full table scan, yet Oracle did not automatically select that access path.  We saw this behavior in an earlier article too.  The predicted cardinality still shows only a single row is expected to be returned.

We still have not tried a function based index, so we will switch back to case sensitive matches and try again:

ALTER SESSION SET NLS_COMP=BINARY;

CREATE INDEX IND_T9_C1_UPPER ON T9(UPPER(C1));
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T9',CASCADE=>TRUE)

SELECT
  C1,
  C2
FROM
  T9
WHERE
  UPPER(C1) = 'ABC';

Execution Plan
----------------------------------------------------------
Plan hash value: 1260941705

-----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |     6 |  1254 |     7   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T9              |     6 |  1254 |     7   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_T9_C1_UPPER |     6 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access(UPPER("C1")='ABC')

The function based index access path produced a lower cost plan than the IN list plan at the start of this article, and the cardinality estimate is correct.

Now let’s change back to a case insensitive search of column C1 to see what happens:

ALTER SESSION SET NLS_SORT=BINARY_CI;
ALTER SESSION SET NLS_COMP=LINGUISTIC;

SELECT
  C1,
  C2
FROM
  T9
WHERE
  UPPER(C1) = 'ABC';

Execution Plan
----------------------------------------------------------
Plan hash value: 3973213776

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     6 |  1254 |   383   (3)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T9   |     6 |  1254 |   383   (3)| 00:00:02 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(NLSSORT(UPPER("C1"),'nls_sort=''BINARY_CI''')=HEXTORAW('61
              626300') )

The optimizer did not use our function based index, so a full table scan was performed.

Trying again:

SELECT
  C1,
  C2
FROM
  T9
WHERE
  C1 = 'ABC';

Plan hash value: 3973213776

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |   205 |   382   (3)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T9   |     1 |   205 |   382   (3)| 00:00:02 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(NLSSORT("C1",'nls_sort=''BINARY_CI''')=HEXTORAW('61626300') )

Well, the WHERE clause did not match the function based index definition, so of course that index was not used.

One more time:

SELECT /*+ INDEX(T9) */
  C1,
  C2
FROM
  T9
WHERE
  C1 = 'ABC';

Plan hash value: 1084614729

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |     1 |   205 |   279   (3)| 00:00:02 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T9           |     1 |   205 |   279   (3)| 00:00:02 |
|*  2 |   INDEX FULL SCAN           | SYS_C0016172 |     1 |       |   278   (3)| 00:00:02 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(NLSSORT("C1",'nls_sort=''BINARY_CI''')=HEXTORAW('61626300') )

I think that we saw that plan earlier in this article.

Now who wanted Oracle to behave like SQL Server anyway?

Measuring Numbers – Is this a Valid Comparison?

June 4, 2010

I encountered an interesting test case in the “Oracle SQL Recipes” book, but I fear that my degree in mathematics is causing me to fail to fully comprehend the test case.  I developed a parallel test case that possibly answers the questions that are left unanswered.  Here is my test case:

CREATE TABLE T8 (
  NUMBER_DIV NUMBER,
  BIN_DBL_DIV BINARY_DOUBLE,
  NUMBER_VALUE NUMBER,
  BIN_DBL_VALUE BINARY_DOUBLE,
  NUMBER_VALUE2 NUMBER(7,2));

INSERT INTO
  T8
SELECT
  ROWNUM,
  ROWNUM,
  1000/ROWNUM,
  1000/ROWNUM,
  1000/ROWNUM
FROM
  DUAL
CONNECT BY
  LEVEL<=10000;

COMMIT;

COLUMN NUMBER_DIV FORMAT 99999
COLUMN BIN_DBL_DIV FORMAT 99999
COLUMN NUMBER_VALUE FORMAT 99990.00000000000000000000
COLUMN BIN_DBL_VALUE FORMAT 99990.00000000000000000000
COLUMN NUMBER_VALUE2 FORMAT 99990.000
COLUMN VND FORMAT 999
COLUMN VBDD FORMAT 9999
COLUMN VNV FORMAT 999
COLUMN VBDV FORMAT 9999
COLUMN VNV2 FORMAT 9999

SET LINESIZE 140
SET TRIMSPOOL ON
SET PAGESIZE 1000
SPOOL NUMBERTEST.TXT

SELECT
  NUMBER_DIV,
  BIN_DBL_DIV,
  NUMBER_VALUE,
  BIN_DBL_VALUE,
  NUMBER_VALUE2,
  VSIZE(NUMBER_DIV) VND,
  VSIZE(BIN_DBL_DIV) VBDD,
  VSIZE(NUMBER_VALUE) VNV,
  VSIZE(BIN_DBL_VALUE) VBDV,
  VSIZE(NUMBER_VALUE2) VNV2
FROM
  T8
ORDER BY
  NUMBER_DIV;

SPOOL OFF

Quoting from page 190 of the book:

With the value one-third stored in each column, we can use the VSIZE function to show it was much more complicated to store this [the value of 1/3] with  decimal precision [using the NUMBER datatype], taking nearly three times the space [when compared to the BINARY_DOUBLE datatype].

Here is the output from my script for the row containing the value one-third:

NUMBER_DIV BIN_DBL_DIV            NUMBER_VALUE           BIN_DBL_VALUE NUMBER_VALUE2  VND  VBDD  VNV  VBDV  VNV2
---------- ----------- ----------------------- ----------------------- ------------- ---- ----- ---- ----- -----
      3000        3000      0.3333333333333333      0.3333333333333333         0.330    2     8   21     8     2

As the book states, the column with the NUMBER datatype requires 21 bytes, while the column with the BINARY_DOUBLE datatype requires just 8 bytes to store the value one-third in the table.  What, if anything,  is wrong with the comparison?

Hint: To conserve space, the column format for the NUMBER_VALUE and BIN_DBL_VALUE columns in the above output was changed from:

99990.00000000000000000000
to:
99990.0000000000000000

There is an interesting description of the NUMBER and BINARY_DOUBLE (or other similar datatypes) datatypes in the book “Troubleshooting Oracle Performance“.

Lock Watching – What is Wrong with this SQL Statement?

June 3, 2010

I came across an interesting SQL statement that is described as identifying blocking transactions:

select s1.username blkg_user, s1.machine blkg_ws, s1.sid blkg_sid,
       s2.username wait_user, s2.machine wait_ws, s2.sid wait_sid,
       lo.object_id blkd_obj_id, do.owner, do.object_name
from v$lock l1, v$session s1, v$lock l2, v$session s2,
     v$locked_object lo, dba_objects do
where s1.sid = l1.sid
  and s2.sid = l2.sid
  and l1.id1 = l2.id1
  and s1.sid = lo.session_id
  and lo.object_id = do.object_id
  and l1.block = 1
  and l2.request > 0;

The SQL statement is a bit different from the one that I typically use for determining enqueues.  The documentation also includes a SQL statement for determining enqueues.

What, if anything, is wrong with the above SQL statement?  If you need a test case, try the one found in this article.  I suspect that there may be more than one answer.

Date Delta SQL – What is Wrong with this SQL Statement?

June 2, 2010

I found this interesting example a couple of months ago in a book, and something just did not seem right with the example.  What is the problem with the example?

select employee_id, first_name, last_name,
(sysdate - hire_date)*86400 Emp_Length_Seconds,
extract(year from (sysdate - hire_date) year to month) || ' years, ' ||
extract(month from (sysdate - hire_date) year to month) || ' months. '
Emp_Length_Readable
from hr.employees;

I have not used the EXTRACT SQL function much, if at all, prior to seeing this example, but I sensed that something was wrong.

Here is a test script to possibly help you find the problem with the above:

SET PAGESIZE 2000
COLUMN T FORMAT A20
SET TRIMSPOOL ON
SPOOL datedelta.txt

WITH DATES AS (
SELECT
  TO_DATE('01-JAN-2010','DD-MON-YYYY') + (ROWNUM-1) D
FROM
  DUAL
CONNECT BY
  LEVEL<=365)
SELECT
  D1.D D1,
  D2.D D2,
  D2.D-D1.D DATE_DELTA,
  extract(year from (D2.D-D1.D) year to month) || ' years, ' ||
  extract(month from (D2.D-D1.D) year to month) || ' months. ' T
FROM
  DATES D1,
  DATES D2
WHERE
  D1.D<D2.D;

SPOOL OFF

The above script scolls through every start and end date combination for this year, writing a modified version of the calculation from the original SQL statement to a text file.  The output should look something like this:

D1        D2        DATE_DELTA T
--------- --------- ---------- --------------------
01-JAN-10 02-JAN-10          1 0 years, 0 months.
01-JAN-10 03-JAN-10          2 0 years, 0 months.
01-JAN-10 04-JAN-10          3 0 years, 0 months.
01-JAN-10 05-JAN-10          4 0 years, 0 months.
01-JAN-10 06-JAN-10          5 0 years, 0 months.
01-JAN-10 07-JAN-10          6 0 years, 0 months.
01-JAN-10 08-JAN-10          7 0 years, 0 months.
01-JAN-10 09-JAN-10          8 0 years, 0 months.
01-JAN-10 10-JAN-10          9 0 years, 0 months.
...
05-MAR-10 16-JUN-10        103 0 years, 3 months.
05-MAR-10 17-JUN-10        104 0 years, 3 months.
05-MAR-10 18-JUN-10        105 0 years, 3 months.
05-MAR-10 19-JUN-10        106 0 years, 3 months.
05-MAR-10 20-JUN-10        107 0 years, 4 months.
05-MAR-10 21-JUN-10        108 0 years, 4 months.
05-MAR-10 22-JUN-10        109 0 years, 4 months.
...

What is wrong with this SQL statement, and more importantly, how do we fix it?

The INSTR Function will Never Use an Index? I will give You a Hint

June 1, 2010

As regular readers probably know, I frequently read books on various computer related topics.  Last winter I pre-ordered the book “Oracle SQL Recipes”, and have been reading the book on and off since that time.  Some parts of the book are absolutely fantastic, and others leave me scratching my head – did I read that right?  Page 169 of the book describes the INSTR and LIKE functions.  The book makes the following statement:

LIKE can be more readable, and may even use an index if there are no pattern-matching characters at the beginning of the search string. INSTR will never use an existing index unless you have created a function-based index containing the exact INSTR clause that you used in the WHERE clause.

The above paragraph conveys a lot of good information, and certainly the authors have tested that the above paragraph is correct.  Repeat after me: The INSTR function will never use a non-function based index!  The INSTR function will never use a non-function based index.  The INSTR function will never use a non-function based index?

I sense that some people are not sure that the above is true.  Let’s put together a quick test table for a test:

CREATE TABLE T5 (
  CHAR_COL VARCHAR2(10),
  C2 VARCHAR2(100),
  PRIMARY KEY (CHAR_COL));

INSERT INTO
  T5
SELECT
  TO_CHAR(ROWNUM),
  RPAD('A',100,'A')
FROM
  DUAL
CONNECT BY
  LEVEL <=1000000;

COMMIT;

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

Now that we have a test table with a primary key index, we need a test script:

SET AUTOTRACE TRACEONLY EXPLAIN

SELECT
  *
FROM
  T5
WHERE
  CHAR_COL LIKE '99999%';

SELECT
  *
FROM
  T5
WHERE
  INSTR(CHAR_COL,'99999') = 1;

SELECT
  *
FROM
  T5
WHERE
  SUBSTR(CHAR_COL,1,5) = '99999';

SELECT /*+ INDEX(T5) */
  *
FROM
  T5
WHERE
  INSTR(CHAR_COL,'99999') = 1;

SELECT /*+ INDEX(T5) */
  *
FROM
  T5
WHERE
  SUBSTR(CHAR_COL,1,5) = '99999';

SELECT /*+ INDEX(T5) CARDINALITY(T5 11) */
  *
FROM
  T5
WHERE
  INSTR(CHAR_COL,'99999') = 1;

SELECT /*+ INDEX(T5) CARDINALITY(T5 11) */
  *
FROM
  T5
WHERE
  SUBSTR(CHAR_COL,1,5) = '99999';

If the above script is executed on Oracle Database 11.2.0.1, which of the above seven SQL statements will use the primary key index?

Before you cry fowl, think about the question and the expected execution plans.

Wild turkeys, probably not what you would expect to find on a blog that is about Oracle Databases.  Yet seemingly appropriate.  I found this group of turkeys wandering in my field a year or two ago.  It is amazing how close I was able to get to the birds – with a 20x zoom camera.

Now that you have had a chance to think about it, and you have probably seen this blog article, which, if any, of the execution plans will show that the primary key index was used?  Here is the output of the script:

SQL> SELECT
  2    *
  3  FROM
  4    T5
  5  WHERE
  6    CHAR_COL LIKE '99999%'; 

Execution Plan
----------------------------------------------------------
Plan hash value: 260357324

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |     1 |   108 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T5           |     1 |   108 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | SYS_C0023201 |     1 |       |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("CHAR_COL" LIKE '99999%')
       filter("CHAR_COL" LIKE '99999%')

---

SQL> SELECT
  2    *
  3  FROM
  4    T5
  5  WHERE
  6    INSTR(CHAR_COL,'99999') = 1;

Execution Plan
----------------------------------------------------------
Plan hash value: 2002323537

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 10000 |  1054K|  4407   (1)| 00:00:53 |
|*  1 |  TABLE ACCESS FULL| T5   | 10000 |  1054K|  4407   (1)| 00:00:53 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(INSTR("CHAR_COL",'99999')=1)

---

SQL> SELECT
  2    *
  3  FROM
  4    T5
  5  WHERE
  6    SUBSTR(CHAR_COL,1,5) = '99999';

Execution Plan
----------------------------------------------------------
Plan hash value: 2002323537

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 10000 |  1054K|  4422   (1)| 00:00:54 |
|*  1 |  TABLE ACCESS FULL| T5   | 10000 |  1054K|  4422   (1)| 00:00:54 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(SUBSTR("CHAR_COL",1,5)='99999')

---

SQL> SELECT /*+ INDEX(T5) */
  2    *
  3  FROM
  4    T5
  5  WHERE
  6    INSTR(CHAR_COL,'99999') = 1;

Execution Plan
----------------------------------------------------------
Plan hash value: 1277447555

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              | 10000 |  1054K|  5935   (1)| 00:01:12 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T5           | 10000 |  1054K|  5935   (1)| 00:01:12 |
|*  2 |   INDEX FULL SCAN           | SYS_C0023201 | 10000 |       |  3922   (1)| 00:00:48 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(INSTR("CHAR_COL",'99999')=1)

---

SQL> SELECT /*+ INDEX(T5) */
  2    *
  3  FROM
  4    T5
  5  WHERE
  6    SUBSTR(CHAR_COL,1,5) = '99999';

Execution Plan
----------------------------------------------------------
Plan hash value: 1277447555

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              | 10000 |  1054K|  5950   (1)| 00:01:12 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T5           | 10000 |  1054K|  5950   (1)| 00:01:12 |
|*  2 |   INDEX FULL SCAN           | SYS_C0023201 | 10000 |       |  3937   (1)| 00:00:48 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(SUBSTR("CHAR_COL",1,5)='99999')

---

SQL> SELECT /*+ INDEX(T5) CARDINALITY(T5 11) */
  2    *
  3  FROM
  4    T5
  5  WHERE
  6    INSTR(CHAR_COL,'99999') = 1;

Execution Plan
----------------------------------------------------------
Plan hash value: 1277447555

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |    11 |  1188 |  5935   (1)| 00:01:12 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T5           |    11 |  1188 |  5935   (1)| 00:01:12 |
|*  2 |   INDEX FULL SCAN           | SYS_C0023201 | 10000 |       |  3922   (1)| 00:00:48 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(INSTR("CHAR_COL",'99999')=1)

---

SQL> SELECT /*+ INDEX(T5) CARDINALITY(T5 11) */
  2    *
  3  FROM
  4    T5
  5  WHERE
  6    SUBSTR(CHAR_COL,1,5) = '99999';

Execution Plan
----------------------------------------------------------
Plan hash value: 1277447555

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |    11 |  1188 |  5950   (1)| 00:01:12 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T5           |    11 |  1188 |  5950   (1)| 00:01:12 |
|*  2 |   INDEX FULL SCAN           | SYS_C0023201 | 10000 |       |  3937   (1)| 00:00:48 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(SUBSTR("CHAR_COL",1,5)='99999')

So, the answer to the question, “the INSTR function will never use a non-function based index?”  False, if an index use is hinted.  When an index hint is provided an index full scan is used to read the index, which reads the index blocks one at a time.  This could be a time consuming operation compared to a full table scan if all block accesses result in physical reads and the average row length for the table is not terribly long.  Consider with an 8KB block size, Oracle should be able to read up to 128 blocks (depending on extent size, OS limits, blocks already in the buffer cache, and DB_FILE_MULTIBLOCK_READ_COUNT value) in a single read request during the full table scan in about the same amount of time as would be required to read a single index block from disk during the index full scan operation (unless index pre-fetch kicks in to read multiple index blocks in a single read request).

So, yes we can use an index with the INSTR function, but would we really want to do that?  Maybe on rare occasions, but not as a regular practice.