SQL Grouping – Generating Comma Separated Lists

30 11 2009

The following question appeared on the OTN forums (http://forums.oracle.com/forums/thread.jspa?messageID=3901658):

I want to select Multiple rows into a single line in ‘Single Column Table’ .

For ex:

Employee table has only one column , named as empname . it has three rows

Select empname from emp;



My expected result: thambi,peter,antony

i did see some post regarding pivot query but did not get righ post for Single Column table.. Can someone help me.

One way to do this is with analytic functions.  The set up:






 DEPT EMPNAME            RN
----- ---------- ----------
   10 THAMBI              1
   10 PETER               2
   10 ANTHONY             3
   20 MICHAEL             1
   20 GEORGE              2 

Now that the employees are separated by department, SYS_CONNECT_BY_PATH is used to draw the related employees into a single row:



----- ------------------------------

Toy Project for Performance Tuning

30 11 2009

Over the years I have experimented with creating a custom performance tuning tool.  Below are a couple screen shots of the program.

Part 2:
https://hoopercharles.wordpress.com/2009/12/13/toy-project-for-performance-tuning-2/ Statspack Report Contents

30 11 2009

The following had to be cut from the “Expert Oracle Practices: Oracle Database Administration from the Oak Table” book, as it was about 4-5 pages of bulleted text.  Someone might find this useful:

 A level 7 Statspack report created by Oracle includes the following sections (see the file spcpkg.sql in the rdbms/admin directory of the Oracle home).
• Database and host information – instance name, start up time, and Oracle release.
• Snapshot summary – starting and ending times for the statistics, number of sessions, elapsed time, CPU time used by the instance, and total waits with CPU time experienced by the sessions.
• Cache sizes – buffer cache size, shared pool size, standard block size, and log buffer size.
• Load profile – rates per second and per transaction for redo generation, physical reads/writes, parses, executes, rollbacks, DB time, and a handful of other statistics.
• Instance efficiency indicators – several ratios including execute to parse %, parse CPU to parse elapsed %, % non-parse CPU, and latch hit %.
• Top timed events – top five elapsed time wait events unioned to the CPU time.
• Host CPU – user mode CPU time, kernel mode CPU time, and unused CPU time accumulated across all processes running on the server.
• Instance CPU – available CPU time, busy system-wide CPU time, and CPU time used by the instance.
• Memory statistics – system memory, SGA, and PGA used in the start and end periods.
• Time model statistics – allocation of CPU time and elapsed time used by the instance (V$SYS_TIME_MODEL).
• Foreground wait events – wait time experienced by the end-user sessions.
• Background wait events – wait time encountered by Oracle’s background processes (DBWn, LGWR, PMON, SMON, etc.)
• Wait events (fg and bg) – wait time for all foreground and background processes.
• Wait event histogram – indicates the percentage of each wait on a wait event which completed in a time range (less than 1ms, between 1ms and 1.999ms, between 2ms and 3.999ms, etc), listing the delta values from V$EVENT_HISTOGRAM.
• SQL ordered by CPU – lists the top SQL statements consuming the greatest number of CPU seconds in the time period, with a default minimum of 1% of the DB CPU statistic. Statistics for SQL statements are grouped on the OLD_HASH_VALUE and ADDRESS columns, which allows the same SQL statement to appear multiple times in the Statspack report if the plan for the SQL statement changed between the selected start and end Statspack snapshots (this is true for the remaining SQL sections as well).
• SQL ordered by gets – lists the top SQL statements requiring the largest number of consistent gets in the time period, with a default minimum of 10,000 consistent gets.
• SQL ordered by reads – lists the top SQL statements requiring the largest number of physical block reads in the time period, with a default minimum of 1,000 physical block reads.
• SQL ordered by executions – lists the top SQL statements with the greatest number of executions in the time period, with a default minimum of 100 executions.
• SQL ordered by parse calls – lists the top SQL statements with the greatest number of hard or soft parse calls in the time period, with a default minimum of 1,000 parse calls.
• SQL ordered by sharable memory – lists the top SQL statements with the greatest SHARABLE_MEM summed over the OLD_HASH_VALUE and ADDRESS columns (V$SQL), with a default minimum of 1MB of sharable memory.
• SQL ordered by version count – lists the SQL statements with the greatest number of child cursors in the library cache, with a default minimum of 20 child cursors.
• Instance activity statistics – lists the delta values of changed system-wide statistics (V$SYSSTAT), showing the delta value over the time period, the average delta value per second, and the average delta value per transaction (defined as user commits + user rollbacks).
• Instance activity statistics (absolute values) – number of sessions connected to the instance, number of open cursors, number of cursors held open in the session cursor cache, and the approximate number of redo log switches.
• OS statistics – CPU usage and memory statistics for the server (V$OSSTAT).
• OS statistics detail – lists the server CPU usage statistics for intermediate Statspack snapshots between the start and end snap IDs.
• IO statistics by function – lists the delta values of the view V$IOSTAT_FUNCTION, which indicates disk I/O characteristics for various read and write type operations (ARCH, Buffer Cache Reads, Data Pump, DBWR, Direct Reads, Direct Writes, LGWR, RMAN, Recovery, Smart Scan, Streams AQ, XDB, and Others).
• Tablespace IO statistics – number of block reads and writes, and average performance by tablespace.
• File IO statistics – number of block reads and writes, and average performance per datafile and temp file, showing the delta values from V$FILESTAT and V$TEMPSTAT.
• File read histogram statistics – indicates the number of single block reads per datafile which completed in a time range (less than 2ms, between 2ms and 3.999ms, between 4ms and 7.999ms, etc), showing the delta values from V$FILE_HISTOGRAM.
• Instance recovery statistics – estimated time to recover from a shutdown abort.
• Memory dynamic components – amount of SGA memory used by components (buffer cache, shared pool, java pool, large pool) and the PGA_AGGREGATE_TARGET at the start and end snap IDs.
• Memory resize operations – indicates dynamic resizing events of objects in the SGA when the SGA_TARGET initialization parameter is set (V$MEMORY_RESIZE_OPS).
• Buffer pool advisory – provides estimated changes in the number of physical reads and read times with smaller and larger memory allocations for the DEFAULT, KEEP, and RECYCLE buffer pools (V$DB_CACHE_ADVICE).
• Buffer pool statistics – delta values from the view V$BUFFER_POOL_STATISTICS, showing the number of consistent gets, physical block reads, physical block writes, buffer busy waits, free buffer waits, and write complete waits by buffer pool (DEFAULT, KEEP, RECYCLE, etc.).
• Buffer wait statistics – delta values from the view V$WAITSTAT, showing by block class (data block, extent map, file header block, free list, segment header, undo block, etc.) the number of buffer busy waits and wait time.
• PGA aggregate target statistics – shows the amount of memory specified for the PGA_AGGREGATE_TARGET as well as the amount of memory actually used.
• PGA aggregate target histogram – number of in-memory optimal memory executions, as well as one pass, and multi-pass temp tablespace executions for various memory size ranges.
• PGA memory advisory – provides estimated changes in the amount of megabytes written to the temp tablespace with smaller and larger allocations for the PGA_AGGREGATE_TARGET (V$PGA_TARGET_ADVICE).
• Process memory summary statistics, and by component – PGA memory allocations for SQL processing, PL/SQL processing, and other activities for the beginning and ending snap IDs.
• Enqueue activity – lists enqueues (such as row lock contention, sequence cache, etc.) which impacted database instance performance in the collection period.
• Undo Segment Statistics – lists the number of transactions, maximum query execution time, and number of undo blocks for each of the intermediate snap IDs between the start and end snap ID.
• Latch activity – get requests, percentage of misses, and wait time for latches, showing delta values from V$LATCH (buffer pool, cache buffers chains, redo allocation, shared pool, etc.).
• Latch sleep breakdown – lists latches which could not be immediately acquired after spinning while waiting for thelatch to become available.
• Latch miss sources – number of sleeps and misses for latches, shows the delta values from V$LATCH_MISSES.
• Mutex sleep – lists sleeps and wait time for mutexes (Cursor Pin – kkslce, Cursor Pin – kksfbc, Cursor Pin – kksSetBindType, Library Cache – kglkhfs1 52, etc.), shows the delta values from V$MUTEX_SLEEP.
• Segments by logical reads – ranks the tables, indexes, and other objects by the number of logical reads experienced in the delta time period (V$SEGSTAT or V$SEGMENT_STATISTICS), with a default minimum of 10,000 consistent gets.
• Segments by physical reads – ranks the tables, indexes, and other objects by the number of physical block reads experienced in the delta time period (V$SEGSTAT or V$SEGMENT_STATISTICS), with a default minimum of 1,000 physical block reads.
• Segments by row lock waits – ranks the tables, indexes, and other objects by the number of row lock enqueues experienced in the delta time period (V$SEGSTAT or V$SEGMENT_STATISTICS), with a default minimum of 100 row lock waits.
• Segments by buffer busy waits – ranks the tables, indexes, and other objects by the number of buffer busy wait experienced for blocks in the segment during the delta time period (V$SEGSTAT or V$SEGMENT_STATISTICS), with a default minimum of 100 buffer busy waits.
• Dictionary cache statistics – delta values from V$ROWCACHE, showing statistics for types including dc_awr_control, dc_constraints, dc_database_links, dc_files, dc_free_extents, etc.
• Library cache activity – delta values from V$LIBRARYCACHE, showing summarized BODY, INDEX, OBJECT, SQL AREA, TRIGGER, etc. requests, misses, invalidations, and reloads.
• Shared pool advisory – provides estimated changes in the number of library cache hits and load times with smaller and larger memory allocations for the shared pool (V$SHARED_POOL_ADVICE).
• Cache size changes – lists the default buffer cache size and the shared pool size at each intermediate Statspack snapshot, which indicates the effectiveness of the value specified for the SGA_TARGET and the minimum values specified for the DB_CACHE_SIZE and SHARED_POOL_SIZE.
• SGA target advisory – provides estimated changes in the number of physical reads and DB time with smaller and larger memory allocations for the shared pool (V$SGA_TARGET_ADVICE).
• SGA memory summary – lists the starting and ending memory sizes for memory regions in the SGA including Database Buffers, Fixed Size, Redo Buffers, and Variable Size (V$SGA).
• SGA breakdown difference – lists the starting and ending memory sizes for the first 35 sub-memory regions sorted by pool and then sub-pool in the SGA including shared pool – row cache, shared pool – sql area, and buffer_cache (V$SGASTAT).
• SQL memory statistics – average memory utilization per cursor in the library cache, total number of unique SQL_IDs, and total number of SQL_IDs including child cursors (V$SQLSTATS).
• init.ora parameters – shows all non-default initialization parameters.


To understand how to read Statspack reports, review the following links:

Book Review: Misc. Oracle Related Books

30 11 2009

Oracle Performance Tuning 101 (Oracle Press) by Gaja Krishna Vaidyanatha
This is the book which introduced me to correct methods for Oracle performance tuning through system and session level wait events. Throwing out the old methods of performance tuning (mostly cache-hit ratios), and focusing in on the real problem made significant differences in the production database (3.25 hour Scheduler run time dropped to roughly 29 minutes with the same hardware). But the book does have a couple errors. Page 44: “One of the classic myths about CPU utilization is that a system with 0 percent idle is categorized as a system undergoing CPU bottlenecks. The real issue here is how many processes are waiting for the CPU? It is perfectly okay to have a system with 0 percent idle, so long as the average runnable queue for the CPU is less than (2 x number of CPUs).” Queuing Theory, Metalink note:148176.1, Cary Millsap, Jonathan Lewis, Craig Shallahamer, and others have suggested that 100% utilization is not desirable for various reasons. But the book was written in 2001, so there are bound to be a couple mistakes which were found later.

Optimizing Oracle Performance (O’Reilly) by Cary Millsap
This is the book which introduced me to focused performance tuning methods through the use of 10046 traces, rather than just flipping a series of knobs and switches in the hope of reducing the system-wide wait events related to the knobs and switches. The book drives to the heart of the problem – what is the important process, is that important process too slow, if it is too slow exactly what is causing it to be too slow? Is it a bad execution plan, too many logical IOs, large sorts to disk, contention with other sessions, high server CPU utilization, slow redo log writing, etc. Page 264: ”But be careful: pegging CPU utilization at 100% over long periods often causes OS scheduler thrashing, which can reduce throughput. On interactive-only application systems, CPU utilization that stays to the right of the knee (queuing theory reference) over long periods of time is bad. When CPU utilization exceeds the knee in the response time curve, response time fluctuations become unbearable.”

Oracle Database Performance Tuning Guide by Oracle Corporation
The version for 11g R1/R2 for this title is much improved over previous versions. Covers wait events, execution plans, tracing, and more.

Expert One on One Oracle (Wrox, now available from Apress) by Thomas Kyte
Describes the problems related to database independent applications, differences between Oracle and other database platforms, internal architecture of Oracle, reading 10046 trace files, writing PL/SQL, and more. The most memorable part of the book for me was the Oracle war stories described in the book, and how to overcome the problems.

Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions (Apress) by Thomas Kyte
I was initially disappointed with the book as the first chapter or two was nearly identical to those chapters in “Expert One on One Oracle”, but that disappointment faded quickly once the third chapter was reached. While the book lacked many of the Oracle war stories from the earlier title, the rest of the contents were interesting enough that I recently bought a second copy of the book. I have actually purchased two copies of “Expert Oracle Database Architecture”, and have read both book titles cover to cover. His books are excellent, highly detailed, and include a number of test cases which allow the reader to understand concepts, while reproducing the output using their databases. The test cases also offer a method to determine if something was true in Oracle 9i R1 or in 10g R1 is no longer true in 11g R2. I do not know if it is a fair comparison, but you might be able to consider his books as the Oracle “Concepts Guide” written so that it is easy to understand, with real-world examples, and real-world “war stories” (quite a number of these appear in “Expert One on One”). I consider Tom Kyte’s “Expert Oracle Database Architecture” as the critical starting point, conveying the foundation knowledge needed to understand the material presented in other books including “Optimizing Oracle Performance”, “Troubleshooting Oracle Performance”, “Cost-Based Oracle Fundamentals”, “Forecasting Oracle Performance”, and the “Expert Oracle Practices” book.

Expert Oracle Database 10g Administration (Apress) by Sam R. Alapati
This book served as the final reference for my conversion from Oracle 8i ( to Oracle 10g R2 ( The book is quite literally a dictionary for Oracle – just about any task that needs to be completed is described very well in this book. There are, however, a couple errors in the book. In addition to a couple cases where information was simply omitted, there is this quote from page 202: [i]“Indexes often become too large if there are many deletions, because the space used by the deleted values is not reused automatically by the index.” That statement is simply not true.

Oracle Insights Tales of the Oak Table (Apress) by several members of the Oak Table
Oracle war stories, performance tuning and more from the world’s most respected database tuning experts – what more could any one want except a follow up to the original book?

Oracle Database 2 Day DBA by Oracle Corporation
The basics of Oracle database administration, helpful when upgrading from a prior release, or if suddenly pushed into a DBA role.

Oracle Database Administrator’s Guide 10g by Oracle Corporation
Essentially, covers the foundation knowledge required to properly administer Oracle 10g R2. Much of the contents of the book may be found in the “Expert Oracle Database 10g Administration” and “Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions” books, but sometimes it is nice to read the official documentation.

Recovery Manager Reference and Backup and Recovery Advanced User’s Guide by Oracle Corporation
I read the versions of these books for Oracle 8i and was impressed with the depth of coverage in the books. At the time I felt that no book could be purchased which exceeded the depth, accuracy, nor easy of locating information as was contained in these two books. Things change.

Forecasting Oracle Performance (Apress) by Craig Shallahamer
This is a great book, with a lot of queuing theory and mathematics. Despite the review by Donald Burleson, there is no coverage of Statspack or AWR in the book. Paraphrased from the book: Once the CPU utilization reaches around 75%, queuing becomes significant, leading to performance problems. The book also describes why a 16 CPU server might not yield 8 or even 4 times the performance of a 2 CPU server. Great book, but a little too much theory.

Oracle Database 10g Insider Solutions (SAMS) by Arun Kumar
Of the Oracle books that I have read, this is the first that I read in about a week. Every page seemed to say “Hey, look at this neat feature” without a sufficient amount of supporting details to determine if the solution applies to servers costing less than ¾ of a million dollars with every available Oracle option, if the solutions are helpful or harmful, etc. This is one of the few books I read which I have never considered taking off the shelf to help implement an Oracle feature.

Oracle SQL High Performance Tuning (Prentice Hall) by Guy Harrison
I was very impressed with this book when I initially read the book in 2002. Since that time I have learned that some of the book contents are just plain wrong. What is wrong? A couple quotes come to mind: “On Windows NT/Windows 2000, the maximum number of blocks that can be read [in a multiblock read] cannot exceed 128K (for 32 bit versions).” “The size of the Oracle block can have an influence on the efficiency of full-table scans. Larger block sizes can often improve scan performance.” “Improving the hit rate in the buffer cache” section title.

Applied Mathematics for Database Professionals (Apress) by Lex de Haan and Toon Koppelaars
If ever I needed a book to remind me how much mathematics I have forgotten (my major was mathematics), this is the book. I started reading this book a year ago, but had to put it down to read more exciting titles once I made it half way through the book. I will finish this book, eventually.

Special Edition Using SQL by Que
A fairly thorough book (inline views are not covered), but also a bit old. Not specific to Oracle, but does include Oracle specific content.

Joe Celko’s SQL for Smarties: Advanced SQL Programming
Contains advanced set operations performed in SQL.  Not specific to Oracle.

Oracle Database SQL Language Reference
Primarily useful to cover the items missed by the above SQL books, such as using Oracle analytical functions.

Edit December 16, 2009: Fixed a couple of non-functional links.

Book Review: Expert Oracle Database 11g Administration

30 11 2009

Great Reference, Even with a Couple Accuracy Problems, September 5, 2009

This is a huge book, which could possibly best be described as the condensed encyclopedia of Oracle databases. The book covers everything from administration to performance tuning to using SQL*Plus to installing Oracle on Linux to using RMAN. While I did not read this book cover to cover, I did page through the book looking for interesting topics to read. I did read the author’s “Expert Oracle Database 10g Administration” book cover to cover a couple years ago and was at the time very impressed with that book. There were a couple small errors in the 10g book, repeated a couple times, but I commented to a couple people that the 10g book is by far the best and most thorough Oracle reference book that I had run across. The appendix at the back of the 10g book was very effective in helping me find exactly the right topic, and usually the right syntax for just about any task. The appendix in the 11g version of the book is just about as good. It appears that the author may have rushed the job of updating the 10g book for 11g R1 as quite a few screen captures still show Oracle versions such as and a couple other sections of the book also seem to be more specific to 10g R1 than 11g R1 (or 11g R2).

This book contains a lot of great and/or very helpful information, but while paging through the book I found a couple problems. Overlooking the problems, I would still recommend this book as a reference for Oracle 11g R1. The section on performance tuning is not my first choice for performance tuning information.

Problems found when paging through the book (I know that I probably missed several issues):

  • Page 90 mentions RAID 0+1 but not the more robust RAID 10.
  • Page 92 states “RAID 5 offers many advantages over the other levels of RAID. The traditional complaint about the `write penalty’ should be discounted because of sophisticated advances in write caches and other strategies that make RAID 5 more efficient than in the past.” Visit http://www.baarf.com/ to see the opinions of other DBAs.
  • Page 166 states “if you use an Oracle block size of 64KB (65,536 bytes)…” The maximum supported block size is 32KB, not 64KB, and some platforms support a maximum of a 16KB block size.
  • Page 171 states when suggesting the use of multiple block sizes in a single database “if you have large indexes in your database, you will need a large block size for their tablespaces.” “Oracle provides separate pools for the various block sizes, and this leads to better use of Oracle memory.” For those who have followed the multiple block size discussions over the years, it should be fairly clear that it is not a good idea to use multiple block sizes in a single database. Oracle’s documentation states that multiple block sizes are intended to be used only to support transportable tablespaces.
  • Page 181 states “The database writer process writes dirty blocks to disk under the following conditions… Every 3 seconds.” A check of the Oracle documentation will quickly confirm that this is not the case.
  • Page 182 states “Oracle further recommends that you first ensure that your system is using asynchronous I/O before deploying additional database writer processes beyond the default number – you might not need multiple database writer processes if so.” I think that I misread this several times as saying “do not enable multiple database writers unless you also plan to enable asynchronous I/O,” which would be an incorrect statement.
  • Page 190 states “this is why the buffer cache hit ratio, which measures the percentage of time users accessed the data they needed from the buffer cache (rather than requiring a disk read), is such an important indicator of performance of the Oracle instance.” The author provides a link on page 1161 to an article authored by Cary Millsap which discusses why a higher buffer cache hit ratio may not be ideal. This is definitely a step in the right direction regarding the buffer cache hit ratio, but it might be better to simply ignore the statistic.
  • Page 190 when describing the buffer cache hit ratio states that the formula for calculating the hit ratio is “hit rate = (1 – (physical reads)/(logical reads))*100”. The Oracle Database Performance Tuning Guide 11g Release 1 manual states that the correct formula is “1 – ((‘physical reads cache’)/(‘consistent gets from cache’+’db block gets from cache’))”
  • Page 395 states “11.1.0 is an alternative name for Oracle Database 11g Release 2.” Oracle 11g R2 was just released on September 1, 2009 and its version is
  • Page 402 correctly (according to the documentation) states that Oracle Enterprise Linux 4 and 5, as well as Red Hat Enterprise Linux are supported platforms for Oracle Database 11g, and correctly (according to the documentation) does not list Red Hat Enterprise Linux 3. Page 403 lists the required RPM packages for Red Hat Enterprise Linux 3, but ignores the supported Linux platforms.
  • Page 405 shows parameters that need to be set on Linux. This appears to be a direct copy of the parameters in the Oracle documentation, but the author did not include the net.core.wmem-max parameter. Note that Oracle will require different parameters than those specified in this book, but that is not the fault of the author.
  • Page 452 states that the COMPATIBLE parameter may be set to “10.2 so the untested features of the new Oracle version won’t hurt your application.” I think that this is misleading at best.
  • Page 466 states “If you’re supporting data warehouse applications, it makes sense to have a very large DB_BLOCK_SIZE – something between 8KB and 32KB. This will improve database performance when reading huge chunks from disk.” This is not quite a correct statement, especially if the DB_FILE_MULTIBLOCK_READ_COUNT is set correctly, or not set in the case Oracle or above is in use. 8KB is the standard block size, so I am not sure why the author groups it with the other block sizes in the very large block size group.
  • Page 477 states “the default value for the STATISTICS_LEVEL initialization parameter is TYPICAL. You need to use this setting if you want to use several of Oracle’s features, including Automatic Shared Memory Management.” This is an incomplete statement as a setting of ALL will also work.
  • Page 1055 shows the use of both CASCADE>=YES and CASCADE=>’TRUE’ with DBMS_STATS. I believe that TRUE is the correct syntax, but it should not be specified in single quotes.
  • Page 1067 states “subqueries perform better when you use IN rather than EXISTS.” The reality is that Oracle may very well automatically transform queries using IN syntax into queries using EXISTS syntax (or vice-versa), and may even transform both IN syntax queries and EXISTS syntax queries into standard join syntax.
  • Page 1074 stated that “inline stored functions can help improve the performance of your SQL statement.” The author then demonstrated this concept by converting a SQL statement with a three table equijoin accessing apparent primary and foreign keys of the tables into a SQL statement which accessed a single table and called two PL/SQL functions (per row), each of which queried one of the other two tables which were included in the original equijoin SQL statement. This approach does not seem to be a good idea given the number of additional context switches which will result, as well as the additional number of parse calls. In short, do not solve something in PL/SQL when it may be easily solved in SQL alone.
  • Page 1075 states “you should avoid the use of inequality and the greater than or equal to predicates, as they may also bypass indexes.” Something does not look right about that statement.
  • Page 1089 states “the indexes could become unbalanced in a database with a great deal of DML. It is important to rebuild such indexes regularly so queries can run faster.” Interesting suggestion – I believe that standard SELECT statements are classified as DML as they are definitely not DDL. An index cannot become unbalanced, and indexes rarely need to be rebuilt – see Richard Foote’s blog for the more details.
  • Page 1089 states “when you rebuild the indexes, include the COMPUTE STATISTICS statement so you don’t have to gather statistics after the rebuild.” Oracle 10g and above automatically collect statistics when building indexes, and I would assume that the same is true when rebuilding indexes.
  • Page 1108 states that “cpu_time is the total parse and execute time” when describing the columns found in V$SQL. It is actually the time measure in centiseconds (100th of a second) of the CPU utilization when executing the SQL statement, and probably also includes the CPU time for parsing the SQL statement, but I have not verified this.
  • Page 1154 provides a demonstration of finding sessions consuming a lot of CPU time. The example used the `CPU used by this session’ statistic rather than drilling through V$SYS_TIME_MODEL into V$SESS_TIME_MODEL. The example used the `CPU used by this session’ as a reason for examining parse CPU usage and recursive CPU usage. While it is good to check CPU usage of sessions, there are a couple problems with this approach. First, the `CPU used by this session’ statistic is not updated until the first fetch from a SQL statement is returned to the client. If the client was waiting for the last hour for the first row to be returned, the CPU utilization will show close to 0 seconds difference between the start of the SQL statement and the check of the `CPU used by this session’ statistic in V$SESSTAT – this is not the case for the statistics in V$SESS_TIME_MODEL, which are updated in near real-time. Second, the change (delta) in the statistic for the sessions was not determined – if one session had been connected for 120 days, it probably should have consumed more CPU time than a session connected for 30 minutes, and probably should not be investigated.

Even with the above issues, I would still recommend this book as a reference. For specific information on performance tuning, using RMAN, or installing on Linux, it might be a good idea to use a second book for deeper understanding of the process.

Book Review: Secrets of the Oracle Database

30 11 2009

Useful Book Even without a Lot of Real Undocumented Secrets, July 1, 2009

The “Secrets of the Oracle Database” book with the subtitle of “Advanced administration, tuning, and troubleshooting using undocumented features” follows the same pattern as most of the other Apress books, with very well researched and verified information with careful references to well regarded books and external resources. Many code samples and demonstrations are provided throughout the book in the format of: tell me about something, and then show me/prove to me that it actually works. The topics in this book reminded me a bit of the topics discussed in the book “Oracle 10g Insider Solutions,” except for the fact that the “Secrets of the Oracle Database” book actually makes an effort (a very thorough effort) at indicating which features are available with each version of Oracle (through, which features require additional cost licenses (such as the use of AWR), and provides a good enough example of the features so that the feature may be utilized with an understanding of why the feature should be used. The “Secrets of the Oracle Database” book even does a thorough job indicating the permissions and/or roles needed to leverage the various features. Specific items that I found to be helpful:

  • In most cases, commands are provided for Linux/Unix and the equivalents for Windows.
  • Good description of SYS.AUX_STATS and the various functions to view and populate the CPU stats are described in the book.
  • Very detailed description of raw 10046 trace files.
  • Detailed listing of the purpose of the various database tables related to Statspack.
  • Good summary of ORADEBUG functionality.
  • Provides a warning not to adjust the hidden (underscore) parameters unless under the supervision of Oracle support.

With the above in mind, why not give the book a 5 out of 5 rating?

Several Oracle features/behavior which are described as undocumented are in fact fairly well documented and/or discussed in Metalink notes, on various Oracle related blogs, and various Internet forums:

  • Page 143 describes ALTER USER IDENTIFIED BY VALUES as being undocumented while it is documented in Metalink (279355.1 last modified 27-OCT-2008 and 1051962.101 last modified 16-OCT-2008) and on several websites.
  • Page 136 states that the TRACE=TRUE parameter for the exp/imp and expdp/impdp utilities is undocumented while it is documented in a couple Metalink notes (271782.1 last modified 17-JAN-2005 and 286496.1 last modified 21-APR-2009) .
  • Page 337 states that DBMS_SYSTEM is undocumented while it is documented in a couple Metalink notes (286496.1 last modified 21-APR-2009 and 103267.1 last modified 20-NOV-2002 and 436036.1 last modified 09-MAR-2009 and DBMS_SUPPORT is described in 62294.1 last modified 25-OCT-2002), several books, and several websites.
  • Pages 271 and 371 state that the 10046 trace file format is undocumented while it is documented in a couple Metalink notes (39817.1 last modified 09-DEC-2008 and 376442.1 last modified 25-JUN-2009), two books referenced by this book (“Optimizing Oracle Performance” and “Troubleshooting Oracle Performance“), and several websites.
  • Page 299 states that “it is undocumented which parameter changes force the optimizer to consider a new plan,” after showing how changing OPTIMIZER_INDEX_COST_ADJ forced a change in the execution plan – but the book never went on to suggest checking V$SES_OPTIMIZER_ENV, V$SQL.OPTIMIZER_ENV_HASH_VALUE/V$SQL.OPTIMIZER_ENV, or a 10053 trace file.

There also appear to be a couple errors, or at least exceptions to some of the broad rules discussed in the book:

  • Page 29 states that “V$PARAMETER is built in such a way that it lists documented (non-hidden) parameters only,” with documented parameters being those which do not begin with one or two underscore characters. This is a correct statement, until one of the hidden parameters is modified, with a command such as the following: ALTER SYSTEM SET “_OPTIMIZER_UNDO_COST_CHANGE”=’′; (_OPTIMIZER_UNDO_COST_CHANGE is one of those parameters which are adjusted automatically when OPTIMIZER_FEATURES_ENABLE is set to a different value). Once the _OPTIMIZER_UNDO_COST_CHANGE parameter (or likely any _ parameter) is modified, it will then be listed along with the documented parameters in V$PARAMETER (tested on Oracle and
  • Page 45 states “Since SELECT statements don’t benefit from unused indexes… it may be worthwhile to drop unused indexes.” Richard Foote’s blog provides evidence that those indexes which appear to be unused indexes may actually provide the cost based optimizer statistical information that it would not otherwise have. Also, not every use of an index is recorded as a use of that index.

Given the title and subtitle of the book, I expected much more insight into the internals of Oracle databases. For instance on page 277 when describing the content of a 10046 trace file, the book stated that cr is defined simply as “consistent reads”, while cu is defined simply as “current blocks processed”. Page 357 gives a little more detail on the cu statistic “call parameter cu (current read) corresponds to the statistic db block gets.” This limited description is disappointing as a book described as providing advanced administration, tuning, and troubleshooting techniques should be able to tell much more about the cr and cu statistics. The same might also be stated about the coverage of V$SYS_TIME_MODEL, V$LOCK, V$SQL_SHARED_CURSOR and several other features discussed in the book.

Several of the chapters (5, 6, 8, 13, 14, 15, etc.) are very short, ranging from two to five pages in length. It might have been a better idea to combine several of these small chapters and/or provide more knowledge of Oracle’s internal behavior in those chapters. The author seems to favor the word “contrary” as it appears many times throughout the book. The back cover of the book indicates that this book should be read after “Troubleshooting Oracle Performance” and/or “Expert Oracle Database 11g Administration”. Reading either of these two books before reading the book “Secrets of the Oracle Database” might make some of the secrets disclosed in the book seem quite ordinary.

In summary, this book’s contents would likely seem as “secrets” for those DBA who have worked with Oracle for only a couple years and for those DBAs whose last book read was an Oracle 8.0.5 administrator’s guide. This book contains a great collection of disconnected information about Oracle (no building process from one item to the next), but it sometimes stops short of providing undocumented secrets to those DBAs who have read a couple good books recently, read a couple good blogs, and followed along on the discussions of a couple Oracle related forums. Much of the information may be obtained from other sources, but it is helpful that so much of the information is contained in a single book.

Book Review: Oracle Wait Interface: A Practical Guide to Performance Diagnostics & Tuning

30 11 2009

Even Five Years After Publication, this Book Still Belongs on the Bookshelf Next to the Other Worthy Books, June 16, 2009

For the last couple years I debated whether or not to buy this book, or for that matter any additional book, on the topic of Oracle’s wait event interface. I felt that between my copy of “Oracle Performance Tuning 101”, the enhanced wait event documentation in the free Oracle 11g Performance Tuning Guide, and the notes that I have collected from various web sites, that there was no need for another book in my collection which solely described wait events. To put it lightly, I was surprised by the depth of detail, the range of topics, the clarity of concepts, and the level of accuracy in this Oracle Press book, something I have come to expect usually only of the books offered by Apress.

This book, now five years old and covering through (apparently) Oracle, makes a very good attempt at covering a large percentage of the most frequently occurring of the 800+ wait events in Oracle 10g (there are nearly 1,000 in Oracle Not only does the book provide a typically detailed explanation of what each wait event means, but it also describes the related system/session statistics, related system parameters, and what the background processes might wait on while the foreground process waits on a different wait event (for example LOG FILE PARALLEL WRITE and LOG FILE SYNC, respectively).

Utilizing a wide range of system views while identifying performance problems and methods for correcting those performance problems, the book describes views including: v$system_event, v$session_event, v$session_wait, v$session_wait_history, v$system_wait_class, v$session_wait_class, v$sql_workarea, v$sql_workarea_active, v$latch_misses, v$sess_time_model, v$sql_shared_cursor, v$rowcache, x$kcqrs, v$resource, v$lock, v$session, v$segment_statistics, x$kcbfwait, x$kcbwds, x$ksolsfts, and several others.

Many system parameters, both normal and hidden, were described throughout the book. Better definitions of the system parameters are hard to find from other sources, especially in the case of the hidden parameters. The hidden parameters (those beginning with an _) should not be altered without first consulting Oracle support – I believe that the book only mentioned this fact once near the end. Having a good description of the meaning of the hidden parameters might help determine if another DBA working with the same database instance attempted to implement some sort of `silver bullet’ fix by adjusting one of the hidden parameters, but it might also be helpful when trying to troubleshoot a situation where the Oracle instance is behaving in an unexpected way. The book describes the following parameters: hash_area_size, sort_area_size, db_writer_max_writes, db_writer_processes, session_cached_cursors, _db_writer_chunk_writes, _log_io_size, _max_exponential_sleep, _max_sleep_holding_latch, _spin_count, _kghdsidx_count, _kgl_latch_count, _optim_peek_user_binds, _db_block_hash_latches, _db_block_hash_buckets, _wait_for_sync, _log_io_size, fast_start_mttr_target, _lm_dynamic_remastering, _db_block_max_cr_dba, _db_aging_freeze_cr, _db_aging_touch_time, _fairness_threshold, _bump_highwater_mark_count, _ash_enable, _ash_disk_write_enable, _ash_size, and several others.

There are a few sections of the book which may benefit from a re-write to update the information for Oracle 10g R2, 11g R1 or 11g R2 (currently in beta) versions of Oracle. There are also a few sections of the book which could use a bit more supporting information, and a few more sections which contain information which might not be 100% correct based on information made available on the Internet and in various books in the last five years. Regardless of any small problems which might be present in the book, it is an excellent companion to the books “Oracle Performance Tuning 101”, “Optimizing Oracle Performance”, “Cost-Based Oracle Fundamentals”, “Troubleshooting Oracle Performance”, and to some extent “Forecasting Oracle Performance”.

Possibly interesting comments that I noted as I read the book:

  • The discussion on page 28 and 29 of the V$SESSION_WAIT view is limited, and does not answer the question of when the times reported should be ignored. For example, it would have been helpful if the book stated something to the effect of: if the STATE column indicates that the session is Waiting, Waited Unknown Time, Waited Short Time, then the WAIT_TIME column is mostly meaningless. If the entry is reporting a STATE of Waited Known Time, then the WAIT_TIME is the number of 1/100th of a second that the session waited on the event. If the STATE is Waiting, then the SECONDS_IN_WAIT column is the actual number of seconds that the session has been in the wait, otherwise that column shows the approximate number of seconds since the last wait started.
  • A SQL statement is provided to decode the P1 value of an ENQUEUE type wait.
  • The SQL*Net message to client description on page 65 seems to be incorrect – the wait actually ends as soon as the data is handed off to the server’s network stack – the event cannot be used to detect network latency as stated: “… or the network latency delays may be causing the message delivery to take longer.”
  • Pages 143-144 contain a very nice summary of the differences between latches and locks.
  • The book frequently uses the adjective “high” when stating the boundary values between OK and problem conditions for wait events, but the book usually does not indicate the contextual meaning of “high” – for example on pages 151 and 157.
  • On page 184, the book states “Unindexed foreign keys are no longer an issue starting in Oracle 9i Database.” This does not appear to be 100% correct as sessions will still block other sessions with TM enqueues on the child table without the foreign key index if the primary key (or a column defined as unique) on the parent table, to which the foreign key on the child table points, is modified (even when modified to the same value).
  • Page 190 suggests creating all data segments with a minimum of 2 freelist groups, even in a single-instance database. It would have been helpful if the authors discussed some of the possible side effects related to multiple freelist groups, although a nice table was provided to show some of the performance benefits.
  • It might have been helpful if the book discussed how to correlate the average time spent on the LOG FILE SYNC wait event with the average time spent on the LOG FILE PARALLEL WRITE wait event – what does it mean if the average times are roughly the same, what does it mean if the average times are significantly different. What happens to the average LOG FILE SYNC wait time when the CPUs in the server become very busy?
  • Pages 202-203 state “There are many things that you and your system administrator can do to provide some relief to the log file sync wait by increasing the I/O throughput to the log files… placing your log files on raw device and binding the LUN in RAID 0 or 0+1 instead of RAID 5.” This advice comes a couple dozen pages after the book warned to make certain that the redo logs need to be on very reliable mount points. With that in mind, avoid RAID 0, and use RAID 10 rather than RAID 0+1.
  • The book states that a log buffer larger than 1MB is likely oversized, yet on recent versions of Oracle the log buffer size is controlled by the granule size, meaning that a 14MB log buffer is not out of the ordinary for database instances with large SGA sizes (resulting in a 16MB granule size).
  • The book suggests several times to increase the number of DBWR processes even on systems which support asynchronous writes. For example, on page 210 the book suggests modifying the number of DBWR processes before increasing the buffer cache size when encountering FREE BUFFER WAITS. It would have been helpful if the book explored some of the potential problems related to increasing the number of DBWR processes.
  • Very well written section on delayed block cleanout.
  • The section describing wait events in a RAC environment contains helpful information that is useful in non-RAC environments, so don’t skip this section of the book if RAC is not used in your environment.
  • The indented time model statistics section in the book looks nearly identical to the indented time model in the Oracle documentation, yet that section of the book lacks the descriptions of the time model statistics which are now present in the Oracle documentation.
  • The _ASH_ENABLED parameter mentioned on page 254 is not the Oracle approved method of disabling ASH collection. Oracle has since released an approved method of disabling ASH collection, see Metalink for the DBMS_AWR.DISABLE_AWR function.
  • Aside from the brief hint on page 259, the book fails to mention that the use of AWR/ADDM requires an additional cost license.
  • Appendix B contains a list of Oracle 10g enqueue wait events and a brief description of each wait event. Any description of the more obscure 10g enqueue waits is difficult to locate from other sources, including Oracle documentation, Metalink, and searches of the Internet.
  • Appendix C shows how to create a variety of dumps using both ALTER SYSTEM/SESSION and ORADEBUG. Dumps include data block dumps, file headers, control files, library cache, processstate, systemstate, and more.
  • The book often lists in which Oracle version a specific feature or change in behavior takes place.

Book Review: Practical Oracle8i: Building Efficient Databases

30 11 2009

No Expiration Date for Useful, Accurate Information, March 3, 2009

I bought this book a couple weeks ago, after reading a couple reviews from different sources stating that while this book is a bit old, much of the information contained within is still useful. But, why spend the money buying a book that is eight years old, when the last database maintained in-house was moved to Oracle 10g R2 three years ago, and Oracle 11g R2 is likely set to be released in just a couple months? As fast as computer technology, and computer software for that matter, changes, is there much value in eight year old information? I was very pleased with the author’s “Cost-Based Oracle Fundamentals” book, so I thought that it was wise to buy a copy of this book before it disappears from the retail market. Was it a waste of money? After all, there is no coverage of ASSM tablespaces, MEMORY_TARGET parameter, SGA_TARGET parameter, PGA_AGGREGATE_TARGET parameter, AWR, Enterprise Manager Database Control, or any other feature introduced after Oracle 8.1.6.

If one ignores what likely amounted to ten pages of bug descriptions related to problems which must be avoided in Oracle 8.1.5 and 8.1.6, 95% of the book information is still relevant to people administering Oracle 9i through Oracle 11g. If one ignores this quote from page 28, “Of course the buyers may decide that they can get a really good deal by buying 4 35GB disks instead of the 16 9GB disks you requested… and maybe 4 600-MHz CPUs would be cheaper than the 12 220-MHz CPUs you wanted,” the relevance and accuracy of the advice dispensed in the book does not diminish with time, and that cannot be said for many Oracle related books of the same vintage. It might even be an interesting mental exercise to read the book with a critical eye, attempting to identify limitations outlined within the text which no longer apply to more recent releases of Oracle – one would hope that the bug related limitations have long since been exterminated.

Despite having read a couple books by Thomas Kyte a couple years ago, as well as several others written by various respected authors, I still managed to record eight pages of typewritten notes while reading the Practical Oracle 8i book. Topics of the notes seemed to fall into the categories of “I remember that, kind of”, “*There* is the missing link that connects the facts of the seemingly unrelated events”, “Wow, I never thought of that as a possibility”, and “That fact, or a question related to that fact, was just mentioned on one of the Oracle forums, or on a technical Oracle blog”.

Paraphrased sections of the book, the case against rebuilding indexes on a scheduled basis, circa 2001 (and probably just as accurate today): “When an index block becomes completely empty, it is placed in the index’s freelist, but is not removed from the structure until the block is reused. The index block, when reused, may be used in a different part of the index structure.” Formulas are floating about on the Internet purporting to indicate when an index should be rebuilt based on criteria which is only updated after analyzing an index. “Analyzing an index will take out a mode 4 (shared) lock on a table, in the process preventing inserts, updates, and deletes within the index. An online rebuild of an index requires two periods in which the parent table is locked – once at the start of the rebuild and once at the end of the rebuild. Waits caused by the locks will not show in V$LOCK, and V$SESSION_WAIT will show waits on a NULL event. It is not possible to perform an online rebuild of bitmap indexes, reversed indexes, LOB indexes, secondary indexes on index organized tables, or on function based indexes. During an online index rebuild, space will be needed for the original index, the rebuilt index, and a logging segment named SYS.JOURNAL_nnnnn. Unlike the rebuild command, coalesce does not demand extra space to maintain two copies of the index, nor does it need to lock the table, but using coalesce does increase the chances of other sessions hitting a snapshot too old error.”

Paraphrased from the book, the case against and for using the SYS user for an export (CONISTENT=Y cannot be used when the SYS user is performing an export, per the “Oracle Database Utilities 10g Release 2, Original Export and Import” documentation): “Oracle 8i introduces row level security, which uses a PL/SQL function to apply an additional WHERE clause predicate to a table – row level security does not apply to the SYS user. It is important to use CONSISTENT=Y when exporting partitioned tables. When CONSISTENT=N is specified, the export of each partition in a table is treated as a separate transaction, and may be exported at a different SCN number (incremented when any session commits). When tables are exported which contain nested tables, the two physical segments are exported in separate transactions, potentially resulting in inconsistent data during the import if the export was performed with the default CONSISTENT=N.” So, what to do if the database is using partitioning, row level security, and nested tables? (Read the book to find the answer).

The book is written in a style which seems to mimic the type of conversation one might expect if an Oracle guru casually pulls up a chair in the library and starts telling the story of the little Oracle database engine that grew up to become the big database engine with occasional sharp teeth. The author’s casual language in the text is not what one would expect from a technical computer book, but the writing style does flow well. When I reached the last page of the final appendix, I thought to myself “is that all there is to the book, what happened next?” Reviewing my notes, I think that I am able to answer that question.

Struggling to Find a Couple Negatives:
* A couple scripts are missing obvious characters, such as a closing parenthesis.
* Features requiring an Enterprise Edition license were not identified as such until Appendix A (an important section of the book which briefly describes various features mentioned in the book, along with associated benefits and negatives).
* Appendix A failed to mention that analytic functions and function based indexes were not available to users on the Standard Edition of Oracle 8.1.x

Book Review: RMAN Recipes for Oracle Database 11g : A Problem-Solution Approach

30 11 2009

Useful for Oracle 10g and Above, with Carefully Constructed, Easily Followed Solutions, February 14, 2009

About six months ago I read the book “Oracle Database 10g RMAN Backup and Recovery”. While I enjoyed reading that book, I found that the book was difficult to use when practicing various backup, recovery, and cloning procedures which were outlined in the book. The reward for flipping through what must have been 50 to 100 pages in order to find everything needed for a practice procedure seemed to be the appearance of unexplained ORA errors, but maybe it was just my inability to follow the directions. In a panic database down situation, should it occur, the “Oracle Database 10g RMAN Backup and Recovery” book will remain on the shelf. So, I started looking for a different resource.

I initially discounted the “RMAN Recipes for Oracle Database 11g” book as irrelevant primarily as I currently use Oracle 10g R2 in production, and did not wish to be confused by a book using 11g specific RMAN syntax without acknowledging that a slightly different syntax is required for Oracle 10g (or 9i, for that matter). In addition to output captures showing that Oracle 11.1.0.x (beta) had been used, other output indicated that Oracle 10.2.0.x was used to demonstrate various commands in the book. The authors made a significant effort to indicate which features are available, and when necessary the alternate syntax in 11g, 10g, and versions prior to 10g. Paraphrasing a couple sections of the book: “Prior to Oracle 10g, it was necessary to set ARCHIVE_LOG_START ([SIC] – LOG_ARCHIVE_START) to TRUE in order to enable automatic archiving of redo logs.” “RMAN does not back up locally managed temporary tablespace tempfiles – starting with Oracle 10g Oracle will recreate the tempfiles when the database is opened.” “Beginning with Oracle database 10g, it is possible to restore a backup from a previous incarnation and recover through a resetlogs command.” “An archived redo log deletion policy is only available starting in Oracle 11g.” “In 11g it is possible to use Active Database Duplication to duplicate a database without any prior backups of the source database.” The extra effort to identify version specific features allows the book to be used successfully with Oracle 11g, 10g, and possibly 9i.

The title of this book is the second reason why I initially discounted the book as unusable. The title implied that the book’s layout was one of “Perform steps X, Y, and Z in order, and don’t bother wondering why it works – essentially, do as I say or your database will not rise when placed into the oven.” That perception of the book’s contents was far from reality. The basic book structure is: Chapter introduction, very brief problem description, well organized step by step problem solution (typically contained on a single page, with relevant related information needed by the solution within 5 pages of the solution), detailed description of how and why each step should be performed as well as side notes and cautionary advice, second very brief problem description, etc. The first half of the book describes how to backup the database, and the second half describes how to recover the database as well as how to set up for special configurations. Roughly 35 pages near the end of the book describe how to configure three different media management products. Most of the book focuses on the command line interface for RMAN, which in my opinion is the correct approach as extensive coverage of backups performed with the Enterprise Manager Database Control would have been a waste of book pages – the nagging question of what to do when the GUI breaks was avoided in this book. The “RMAN Recipes for Oracle Database 11g” book, while not perfect, is organized such that it is not necessary to dig through 50 to 100 pages in order to implement a solution listed in the table of contents, should the book be needed for future reference purposes.

As I did with the “Oracle Database 10g RMAN Backup and Recovery” book, I tested a couple of the backup, recovery, and cloning solutions outlined in this book using Oracle Standard Edition on a Windows platform.

  • Repeated backups with and without a recovery catalog, with and without backing up archived redo logs, with and without automatic control file and spfile auto backups, all with a flash recovery area were performed. All worked as described in the book without issue.
  • Restore of the control file without a recovery catalog – worked as described in the book, the fact that ALTER DATABASE OPEN RESETLOGS; must be executed when a control file is restored from backup was repeatedly emphasized throughout the book.
  • Restore and recovery of a deleted datafile – worked as described in the book.
  • FLASHBACK DATABASE did not work as it is not supported on the Standard Edition of Oracle. Block level recovery also would have failed as it is not supported on Standard Edition. FLASHBACK TABLE TO BEFORE DROP did work without problem. See the Oracle documentation “Oracle Database Licensing Information – Oracle Database Editions” for the list of unsupported features.
  • Recovering to a previous incarnation of the database did not work as expected (it is possible that I missed a step). When testing, be careful with issuing RESET DATABASE INCARNATION 1; as this could lead to complications when later testing other restore and recovery scenarios – dealing with “ORA-00600: internal error code, arguments: [kcvsor_current_inc_rdfail]” is not covered, and may be an indication that the database incarnation needs to be reset to a different value after recovering the control file, but before attempting to recover.
  • Dropping a table and recovering to a past time using the UNTIL TIME syntax worked as described in the book.
  • Cloning a database worked on the second try, after modifying the author’s init.ora to include a line for SGA_TARGET, and there was no sense of frustration trying to locate all of the items to be set as was the case with the “Oracle Database 10g RMAN Backup and Recovery” book.
  • Setting up the production database to permit scheduled cloning of the database worked without problem, although it might have been helpful to stress to the readers that the later addition of data files to the production database will likely require re-executing the RUN block with CONFIGURE AUXNAME so that auxnames are specified for the new datafiles.

General comments about the book, as it was read from cover to cover:

  • The book’s errata page on Apress’ website identifies a number of typos in the book, which may or may not cause problems for people using the book in a high stress situation.
  • The first couple chapters contain many paragraphs with odd sentence structure and strange synonyms, making it difficult in some cases to easily understand the authors’ advice.
  • Page 41 states “Remember to recycle the database after editing the [parameter] file for the changes to take effect.” This is after page 29 states “Stopping and restarting your database in quick succession is known colloquially in the DBA world as bouncing your database.” I have heard of bouncing the database, but I am guessing that recycling the database is what one would do after corrupting the spfile after editing it with a text editor, or what one might do when the database crashes and RMAN cannot recover the database.
  • Page 55 advises against placing the archived redo logs in the flash recovery area, yet page 17, under the heading “Best Practices” states that Oracle recommends using the flash recovery area to store the entire set of backup and recovery related files. “Oracle recommends” is stated a few too many times in the first couple chapters.
  • Paraphrasing: “A backup taken while the database is online or in the mount state (???) is considered to be an inconsistent backup. An inconsistent backup always needs recovery after a restore. A noarchive redo log mode database when backed up with RMAN must be mounted.” This begs the question, how does one create a consistent backup of a database in noarchive redo log mode when the database must be mounted – I think that “mount” is a typo in the book.
  • People reading the book cover to cover will find that the book frequently repeats information throughout the book, but that repetition proves to be quite valuable when using the book as a reference during a backup or recovery situation, as in most cases all need information will be within five pages, or a reference will be made to see Recipe x-y should problem z occur.
  • The book does not indicate what features described in the book require an Enterprise Edition license, nor what features require additional licenses beyond an Enterprise Edition license (this seems to happen in several books by different authors). For example, in Recipe 7-10 Performing Incremental Backups, the authors fail to mention how incremental backups in the Standard Edition differs from incremental backups in the Enterprise Edition.
  • The method of freeing space in the flash recovery area on page 96 appears to be a bit extreme (like launching a grenade at an ant hill), although better approaches are provided later. Page 246 seems to indicate that the method on page 96 might not be as extreme as it first appears.
  • The book shows how to schedule a backup script using either Linux/Unix cron or the Windows task scheduler, with included scripts for both Unix and Windows.
  • The recipe showing how to duplicate (clone) a production database on a single host is very well constructed, showing how to create the backup of the production database, what to enter in the listener.ora, the initialization parameters to specify in the clone database’s init.ora file, starting the clone in NOMOUNT mode using SQL*Plus, and performing the duplication in RMAN. Unfortunately, the authors failed to specify a value for SGA_TARGET in the suggested init.ora file parameters, resulting in “ORA-04031: unable to allocate 100 bytes of shared memory” when RMAN attempted to start cloning the production database. Even with this minor omission, the directions are far easier to follow than directions in other resources describing how to accomplish the same task. It would have been helpful had the directions for cloning the database included a side note stating “On the Windows platform, use ORADIM -NEW -SID TEST1 to create a service for the clone instance.”
  • The book provides a recipe describing how to set up the production database to permit cloning the database on a scheduled basis. A portion of the script closely resembled a similar script in the Oracle 10g R2 documentation “Backup and Recover Advanced User’s Guide”.
  • The book provides a helpful section describing how to respond to various RMAN error messages.
  • The book demonstrates flashback versions queries, flashback table, flashback drop, flashback database.

Overall, I recommend this book to people using Oracle 10g or 11g in production environments which require backup and recovery to be accomplished correctly on the first attempt.

Book Review: Oracle Database 10g RMAN Backup & Recovery

30 11 2009

A Comprehensive RMAN Book which will Remain on the Shelf in an Emergency, February 6, 2009

I finished reading this book about six months ago and delayed writing a review of the book until I had a chance to contrast the contents of the book with the Oracle 10g R2 RMAN documentation and other books on the same subject. I bought the book after reading recommendations by several individuals, and thought that this book was the perfect opportunity to refresh by memory of the Oracle 8i documentation of RMAN and bring me up to speed with the command line interface of RMAN in Oracle 10g R2. The Enterprise Manager Database Control in Oracle 10g R2 almost over simplifies the process of setting up backups, but there is always that nagging question… what if the GUI interface is down, underscoring why it is important to be familiar and comfortable with the command line interface when restore and recovery are needed. I was also preparing to give a presentation related to Oracle administration and performance tuning. This book did not meet the criteria for inclusion in the recommended resources for the presentation, and for that matter neither did any other RMAN book, with the possible exception of the Oracle documentation.

While the book excels at explaining what makes RMAN tick, I personally found that the book is actually a bit difficult to use when trying to implement a change to the Oracle configuration. The section on database cloning, for instance, is more difficult to follow than Oracle’s own “Backup and Recovery Advanced User’s Guide” documentation on the same topic. Maybe it is just me (and one or two other people). Flipping between several pages (seemed like a hundred or more) is not confidence inspiring had this been a real emergency.

I then decided to try a couple recovery scenarios. I set up an Oracle database instance on a new box, used impdp to load a copy of the data from a production database, enabled archived redo log mode, and then scheduled a backup using the Enterprise Manager Database Control. The first test involved shutting down the Oracle instance, and renaming both copies of the control files. The first attempt in following the “Oracle Database 10g RMAN Backup & Recovery” book to recover from this problem resulted in “ORA-07445: exception encountered: core dump [ACCESS_VIOLATION] [unable_to_trans_pc] [PC:0x7c910CB0] [ADDR:0x4] [UNABLE_TO_READ] []”. The book did not cover how to recover from the ORA-07445, which essentially means that Oracle Metalink support must be contacted – the database is down. Not too reassuring. The second attempt (after recreating the instance and database) of following the book to perform this procedure, after realizing that I should have specified RESETLOGS (if I recall correctly, it seems that information was located somewhere else in the book) resulted in “ORA-19698: …\redo01.log is from different datadase: id=0, db_name=” following the execution of ALTER DATABASE OPEN RESETLOGS. Once again, scanning through this book I did not find the solution to this particular problem. Had this been a real emergency, this book would not have been the only object to hit the recycle bin.

Other miscellaneous notes about the book, in no particular order:

  • 100+ pages of the book are devoted to setting up RMAN to work with various storage/media managers.
  • RMAN workshops list steps to accomplish various tasks, often spread over 5+ pages in the book. Not all required information is located in close proximity to the RMAN workshops.
  • Covers command line and Enterprise Manager GUI commands for RMAN.
  • Demonstrates flashback versions queries, flashback table, flashback drop, flashback database
  • Many screen shots, diagrams, RMAN Workshop break outs, large font – space filling section headings, seemingly wide margins.
  • Some steps which should be straight forward, such as duplicating a database using RMAN, appear to be very simple when reading the book, yet when attempting to put those simple procedures into practice using the book as a guide, are in fact quite difficult due to all of the alternate syntax which is introduced to cover many different set ups. It might be necessary to flip between 50 different pages to successfully duplicate a database on the same server using this book as a guide.
  • The book does not indicate what features described in the book require an Enterprise Edition license, nor what features require additional licenses beyond an Enterprise Edition license (this seems to happen in several books by different authors).
  • In a crisis, the Recovery Case Studies in chapter 21, which could prove to be the most helpful section of the book, but that section only covers 13 scenarios, all with generally a long description to describe the recovery case study problem.

This book is spared a three star recommendation only by the fact that I enjoyed reading the book cover to cover.

Recently, I have been reading “RMAN Recipes for Oracle Database 11g: A Problem-
Solution Approach” and have found that the book is better organized, locating the necessary information in the right places for accomplishing a task, although there is a bit of over repeating in the book (the same sentence appears in two adjacent paragraphs, for example). While the RMAN Recipes book states that it is designed for Oracle 11g, there are output captures which show that some of the sections of the book were written using Oracle 10.2.0.x. If you are considering the purchase of “Oracle Database 10g RMAN Backup & Recovery”, you might first want to examine “RMAN Recipes for Oracle Database 11g: A Problem-Solution Approach” through the Google books search to determine if it is a better fit for your anticipated backup and recovery scenarios.

(The author indicates that the next release will address the clarity of the Cloning section.)

Book Review: Cost-Based Oracle Fundamentals

30 11 2009

A Fantastic Book with a Very Deceptive Name, January 25, 2009

I waited nearly three years after the first cover to cover read through of this book, and two years after the second cover to cover read through of this book to write this review – just in case my opinion of the book changed. I enjoyed the first pass through the book, but became increasingly concerned as just weeks earlier I had migrated the most critical database at the company from Oracle to It seemed every turn of the page brought new understanding of what could have gone wrong during and after the migration, not only what might happen, but also most importantly WHY it might happen. Appendix A of the book quickly summarizes what one might expect when moving from one database release to another – that section of the book, for me, was worth several times the price of the book. I might also add that this book provided the tools and techniques necessary to troubleshoot and resolve a session crashing issue triggered by the application of the October 2006 patch for, as well as another odd problem which appeared when OPTIMIZER_FEATURES_ENABLE was set to a value greater than

During the second read through of the book I found a number of extremely useful pieces of information which were somehow missed during the previous pass through the book (I recorded much more detailed notes the second time). A brief sampling of some of the more personally useful pieces of information from the book:

  • The filter lines in 10053 traces and XPLANS can show implicit conversions from varchar to number. (It also shows when a commercially developed program repeatedly defines bind variables as VARCHAR2 when those bind variables should have been defined as numeric.)
  • Bind variable peeking is not used with explain plan or autotrace. (Believing explain plans for SQL statements with bind variables seems to be a common problem on Oracle related forums.)
  • In an execution plan, the access predicates show the predicates used to generate the start and stop keys for the index, but the filter predicates show the predicates that cannot be used until the leaf blocks have been reached. (That’s why my query is running slow…, slapping hand to forehead.)
  • OPTIMIZER_INDEX_COST_ADJ reduces the cost of single block reads, which tends to reduce the tendency of excessive tablescans. The downside is that due to rounding errors, the optimizer may decide to use the wrong index. (I have seen this happen, Oracle decides to use an index with just two distinct values, rather than a high precision index with many distinct values – the primary key index.)
  • Constraints on tables can generate additional predicates due to transitive closure. (That’s were those access and filter predicates came from…)
  • Bind variable peeking can introduce poor execution plans if a typical value is not specified for bind variables on the first hard parse. (Such as a commercial product submitting NULL values for the bind variables values during the initial hard parse, followed by extremely unpopular values, followed by somewhat popular values in a tight loop, causing performance problems.)
  • With the evolution of the optimizer, Oracle is increasingly likely to eliminate subqueries by using various transformations. (Hence the reason why code that ran slowly against an Oracle database might run quickly against a database, and why other code that ran quickly on Oracle takes orders of magnitude longer on, without some assistance.)
  • Setting the value of SORT_AREA_RETAINED_SIZE to a value other than that of SORT_AREA_SIZE (or 0) may cause Oracle to spill the sort results to the temp tablespace, even if the sort completed in memory, and this will not be reported as a sort to disk. (This flies somewhat counter to a recommendation of my favorite Oracle wait event book from 2001 which suggested that as SORT_AREA_SIZE is increased to allow sorts to complete in memory, that SORT_AREA_RETAINED_SIZE should be decreased.)
  • Extended coverage of 10053 trace file capturing and analysis.
  • Description of several hints which might be used to better control the execution plans.
  • Extensive descriptions of several of the hidden (underscored) parameters which control the optimizer’s behavior and how the value for OPTIMIZER_FEATURES_ENABLE automatically adjusts some of these hidden parameters.
  • Mathematical logic behind the cost calculations used by the optimizer, and how the calculated cost influences the execution plans generated by the optimizer.

The author maintains an extremely helpful Oracle blog which seems to be a direct extension of his book, helping to extend the analysis performed in the book to more recent versions of Oracle, and explore other areas of potential concern to database administrators. On his website the author also maintains a complete list of book errata, additional information learned since the publication date, and how the book’s contents need to be reworded to be applicable to more recent versions of Oracle – in all cases giving credit to the person who identified the potential rewording. Directly from the author’s errata pages: “I spotted the first error in Practical Oracle 8i a few seconds after the first copy had landed on my front door-step. I spotted the first error in Cost Based Oracle before the printer had even finished printing it – I woke up at three a.m. a few days before the book came out, realizing that I had introduced an error in the last pass of proof-reading the galley pages.” As infrequently as this author makes mistakes, it is good to see that the author is humble about the mistakes, without attempting to sweep those mistakes into a dark corner while scolding the person who identified the mistakes.

You will not find page after page of wide margin, 14 point font sized text interspersed between cartoon drawings in the book. You will not find wild assumptions in this book. You will not find cases where the author must have been hand waiving (with a third hand) while composing the book. You will not find untested scripts scattered through the pages. You will find, with a little bit of effort, the reason why the Oracle database instance did not behave as expected.

Prior to purchasing this author’s book, I performed a fairly extensive search on the Internet to make certain that the author’s book would not do more damage than good to my comprehension of what makes an Oracle database function. I found that the author created Usenet group postings in Oracle related forums, helping other users, dating back to 1994; and in checking the accuracy of those post contents, what was stated is just about as accurate now as it was in 1994. I also found that the author had participated in several technical discussions on the Oracle-L list serve. Following the purchase of the book I noticed that the author set up one of the most technical Oracle related blogs on the Internet, and started participating in Oracle’s OTN forums. Since that time I have had the opportunity to converse directly with the author a couple times on the OTN forums and Usenet, where I have always found him to be helpful, knowledgeable, and humble if he missed/overlooked a small detail (and humble when pointing out a small or significant detail which I missed).

In short, my opinion of the book has not changed since the first read through nearly three years ago. “Cost-Based Oracle Fundamentals” was, and still is, the most useful Oracle performance related book which I have found. The book, however, is closely followed in usefulness, in no particular order, by “Troubleshooting Oracle Performance”, “Optimizing Oracle Performance”, “Expert Oracle Database Architecture” and “Oracle Performance Tuning 101”.

Book Review: Beginning PL/SQL: From Novice to Professional

30 11 2009

Very Well Written, with a Lot of Advice – Some will Force the DBA to Cringe, January 25, 2009

This book is very well written, with a couple minor problems which kept it from receiving a five star rating. The book takes the approach of here is how something works (with a detailed code example), here is a problem which needs to be solved using something similar to what you have just learned, and here is how I would write the solution for the problem. The book is easily read from cover to cover through the use of the author’s humor, which seemed to dry up a bit half of the way through the book.

Comments about the book which I recorded as I read through, in no particular order:

  • Page 282 suggests sticking to a standard set of VARCHAR2 column lengths, such as 2000 for comments (4000 for international). However, doing so may lead to excessive memory consumption problems as variable anchors are used in PL/SQL modules to declare variables.
  • Testing and documentation are both demonstrated and stressed as necessary for the developer. The author states that roughly twice as much time should be spent testing a solution as the time required to code the solution.
  • The author provides a brief description of basic SQL, just in case the author’s advice of being comfortable with SQL was ignored.
  • The book provides updated content for developers using Oracle 10g.
  • Chapter titles appearing at the top of each page probably should have been labeled a little better to describe the contents of the chapter, rather than attempting to use a bit of witty humor for the chapter titles. This change would have made it easier to find a specific syntax example, although the index at the back of the book eliminates much of this being an issue.
  • The author created a PL function in one of the early chapters of the book as a shorthand method of calling DBMS_OUTPUT.PUT_LINE, and this PL function was used in many of the later chapters of the book without indicating that PL is not a built-in PL/SQL function. There is a chance that this might cause some confusion for people who attempt to use the book for reference purposes, rather than as a book which should be read cover to cover.
  • The author casually demonstrates a lot of good programming practices with Oracle databases, without drawing much attention to some of those good programming practices.
  • The author stresses modularization of program processes, if the code will be used in more than one place, that code should be stored in a PL/SQL function or procedure. Unfortunately, without much caution, there is a chance that a developer will take that message to an extreme, coding all kinds of black box type procedures which then might be called in a for loop, repeatedly sending the same query to the database rather than sending the query once and storing the result (yes, this does happen with production code).
  • Page 420 suggests creating a temporary table to store an intermediate result, using an example that if the table’s average rowsize is 297 bytes, and only 13 bytes per row are needed, that the results should be placed into a temporary table to improve performance. The author stopped short of stating that the temporary tables should be created on the fly as needed, which would definitely not be a good suggestion.

Submitted to the publisher as errata:

  • The script on page 43, despite the description, the TO_NUMBER_OR_NULL function error handler does not catch errors when non-numeric values are passed into the function (such as the letter A). The author’s code is only attempting to catch the exception INVALID_NUMBER, which is apparently insufficient on Oracle – the author later indicated that he did not know why the code did not work when placed into a package, and modified the code to catch the PL/SQL error.
  • The script on page 94 references the column WORKER_TYPE_T.WORKER_TYPE_ID, but no such column exists when the tables are created using the scripts in the script library. The downloadable script library also contains the same error. The column listed in the script does not match the ERD diagram on page 27, which shows that the script should have referenced WORKER_TYPE_T.ID.
  • The script on page 119 references the column GENDER_T.GENDER_ID, but no such column exists when the tables are created using the scripts in the script library. The downloadable script library also contains the same error. The column listed in the script does not match the ERD diagram on page 27, which shows that the script should have referenced GENDER_T.ID.
  • The scripts which create the tables assume that the tablespace “USERS” exists in the database, but the book does not mention that such a tablespace needs to exist. Some of the columns listed in the tables created by the scripts are Oracle reserved words (SELECT * FROM V$RESERVED_WORDS ORDER BY KEYWORD;), which generally should be avoided as column names. The ERD diagram on page 27 shows a couple such reserved words (ID, NAME).
  • The scripts to create the tables/indexes in the database create the objects with odd extent sizes which will contribute to fragmentation in dictionary managed tablespaces, and poor full tablescan/fast full index scan performance. The scripts on pages 29-36 create objects with 10KB extent sizes, the scripts on pages 188-190 also create objects with 10KB extent sizes, another script created one or more objects with a 100KB extent size, while the script on pages 237-238 creates a table and an index with a 1MB extent size. All objects in a single tablespace should have the same extent size (unless they are in an Oracle controlled ASSM tablespace with auto controlled extent sizes), and the extent size should be a power of 2 – the extent size controls the maximum multi-block read size, so this needs to be considered as well.
  • The script on pages 159-160 is described as a plain old SQL solution, which was created in an effort to show that an all SQL solution (no PL/SQL) would out perform a PL/SQL solution. It did that, but just barely. The problem is that the plain old SQL solution calls 2 PL/SQL functions for each row inserted into the WORKER_T table. A pure SQL solution could have been constructed for this particular insert which would have better demonstrated the author’s point.
  • On pages 266-268 the author attempts to force the Oracle optimizer to use an index access for a SQL statement containing “WHERE NAME LIKE ‘%DOE%’. The author stated that “the Optimizer is wrong” for selecting to use a full tablescan rather than an index type access, citing that the index type access would require the retrieval of about 1,080 4KB blocks to determine which table blocks to access, compared to the 5,500 4KB blocks during a full tablescan. On page 266 the author stated “Instead, it resorted to the worst of all options: a full table scan. Ouch!”. On page 268 the author stated “On the other hand, you’re an intelligent programmer who is much more knowledgeable and can therefore consider things like physics.” The author fails to recognize that if the table’s extent size were set at 1MB, with a 4KB block size, Oracle 10g R2 would have auto-set the db_file_multiblock_read_count to 256 (on earlier versions of Oracle the DBA could have set the same value). While the author’s forced INDEX FULL SCAN was reading the 4KB blocks one at a time, the full tablescan would have been reading up to 256 blocks at a time, in roughly the same amount of time that it would have taken to fetch a couple blocks of the index from disk. The author’s advice could put the developer reading this book at odds with a DBA who has read a couple performance tuning books and articles written by Oaktable Network members.

Book Review: Mastering Oracle SQL and SQL*Plus

30 11 2009

Another Fantastic Oracle Book from Apress, January 12, 2009

First the negatives of the book:
* Page numbers are missing from all pages except the first page of each chapter. This is not a critical problem until one attempts to use the index at the back of the book to locate a specific topic.
* The initial word in a couple of paragraphs is missing.
* The left-most column of table 2-5 “SQL Arithmetic Operators” is blank. The left-most column of table 4-2 “SQL Comparison Operators” is blank.
* Between listing 9-14 and 9-15 (approximately page 244) the book states that an ORDER BY is executed before a ROWNUM filter in the WHERE clause is applied, when in fact the ORDER BY is applied after the ROWNUM filter is evaluated (based on the pre-existing order of the rows in the table blocks) – meaning that the desired rows from the query would not be returned, and that is why the inline view is required.
* The book states that GROUP BY requires a sort operation, but that is no longer the case with more recent releases of Oracle (it was a correct statement as of the original publication date).
* The front cover of the book states that the book includes a CD with a developer’s license for Oracle 10g (version No CD is included with the book, however a version of Oracle with a developer license may be downloaded from Oracle’s OTN website.
* The book has limited coverage of analytical functions.

Like many of the Apress books, this book is extremely well written with a thoughtful layout and a careful progression of topics. This approach will help to advance the skills of readers who are just beginning to understand how to write Oracle SQL statements, and there is sufficient coverage of “newer” (or little known) syntax to assist long-term intermediate and advanced users of Oracle SQL in finding better ways of attacking old coding problems (using UPDATE against an inline view, instead-of-triggers, outputting HTML from SQL*Plus, regular expressions, INSERT ALL, SYS_CONNECT_BY_PATH, etc.).

Syntax of SQL*Plus commands and Oracle specific SQL is interspersed throughout the book’s chapters, introduced as those commands are needed by the author’s examples. I initially felt a bit disappointed by this approach until I noticed the thoroughness and organization of appendix A “Quick Reference to SQL and SQL*Plus”. For an intermediate to advanced user of Oracle SQL and SQL*Plus, that section of the book will prove invaluable when trying to determine if the correct linking syntax for a SQL statement is “ON”, “IN”, “OVER”, “FROM”, “TO”, or “OF”. For a beginner, that section of the book while help to quickly determine if a SQL command exists for a specific task.

Even with the negatives outlined at the beginning of this review, most of which appear to be typesetting issues, this book is still the best Oracle SQL reference that I have found so far, providing just enough detail to help a person to become productive when interacting with Oracle databases, without overwhelming the reader with 20 variations of different SQL statements which accomplish the same basic task. I just wish that I had found a book of this caliber a decade earlier.


Follow-up February 15, 2010:
An updated version of the book, now called “Beginning Oracle SQL”, is available here.

Book Review: Troubleshooting Oracle Performance

30 11 2009

Most Thorough, Yet Compact Performance Tuning Book 9i-11g, September 11, 2008

(Forward to the Next Post in the Series)

“Troubleshooting Oracle Performance” is the most thorough, yet physically compact book covering performance tuning with Oracle 9i R2 through 11g R1 on the market. It is quite clear that a great deal of effort was made by the author to carefully verify the tips and test results contained in the book and to organize the material in a logical progression, thus building a bridge between the reader’s current understanding to the understanding of complex tuning approaches.

What this book accomplishes, which most other performance tuning books seem to miss, is to indicate which performance tuning features are available in each Oracle release (and which are available at no additional licensing cost) as the various performance tuning approaches are discussed. Not only does the book indicate when a feature would be appropriate, but also potential problems (“Pitfalls and Fallacies”) associated with each feature.

The depth of coverage of Oracle 11g R1 features is surprisingly thorough given the short amount of time which that version has been on the market. A little more detail in a couple areas, such as hacking stored outlines, would have been helpful; at the same time, everyone who reads the book might have a different opinion of what needed additional detail, the book could have grown to 2500 or more pages, and likely would have been obsolete by the time it was published.

The Apress Roadmap on the back cover of the book indicates that this book should be read before “Forecasting Oracle Performance”, “Expert Oracle Database Architecture”, and “Cost-Based Oracle Fundamentals”. The “Troubleshooting Oracle Performance” book seems to assume that the Oracle database concepts are well understood. As such, it is probably wise to read “Expert Oracle Database Architecture” first, followed by this book, “Cost-Based Oracle Fundamentals”, and finally “Forecasting Oracle Performance”.

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

Using Histograms to Fix Bind Peeking Problems?

30 11 2009

November 30, 2009

The following question appeared on the OTN forums (http://forums.oracle.com/forums/thread.jspa?threadID=993929):

What is the solution to [bind] variable peeking without going to 11g?
i got answer before as stored outline , but i don’t think this will fix it as stored outlines will stablise the plan which we don’t want , i think histogram is a better solution?

Consider the following test case, which might leave you wondering if creating a histogram on a column used by bind variables is a good idea.
The set up:


NAME                                 TYPE        VALUE
------------------------------------ ----------- --------
optimizer_dynamic_sampling           integer     2
optimizer_features_enable            string
optimizer_index_caching              integer     0
optimizer_index_cost_adj             integer     100
optimizer_mode                       string      ALL_ROWS
optimizer_secure_view_merging        boolean     TRUE

  DECODE(MOD(ROWNUM,1000),1,1,2,2,3,3,DECODE(MOD(ROWNUM,25),10,10,11,11,25)) COL2,
  LPAD('A',255,'A') COL3
    LEVEL<=10000) V1,
    LEVEL<=1000) V2;



  COUNT(*)/10000000*100 PERCENT

COL2        NUM    PERCENT
---- ---------- ----------
   1      10000         .1
   2      10000         .1
   3      10000         .1
  10     400000          4
  11     400000          4
  25    9170000       91.7 

The above created a 10,000,000 row table with 6 distinct values in COL2. 0.1% of the rows have a value of 1 in COL2, and 91.7% of the rows have a value of 25 in COL2. There is an index with a histogram on COL2. Obviously (or not) if we have only COL2=1 in the WHERE clause, we probably would want to use the index on the COL2 column to retrieve rows. Obviously (or not) if we have only COL2=25 in the WHERE clause, we probably would want to use a full table scan to retrieve rows. So, what happens when bind variable peeking takes place when a histogram is present on COL2? Ignore for a moment the elapsed time that is output in the following (note that I flush the buffer cache to force physical reads for consistency – direct I/O is enabled):

EXEC :N1:=1



  COL2= :N1

      COL2         C1
---------- ----------
         1      10000

Elapsed: 00:00:42.72


| Id  | Operation                    | Name      | Starts | E-Rows | A-Rows |A-Time      | Buffers | Reads  |
|   1 |  SORT GROUP BY NOSORT        |           |      1 |      1 |      1 |00:00:42.29 |   10022 |  10022 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T10       |      1 |   8856 |  10000 |00:00:39.03 |   10022 |  10022 |
|*  3 |    INDEX RANGE SCAN          | IND_T10_2 |      1 |   8856 |  10000 |00:00:00.06 |      22 |     22 |

Predicate Information (identified by operation id):
   3 - access("COL2"=:N1)

EXEC :N1:=25


  COL2= :N1

      COL2         C1
---------- ----------
        25    9170000

Elapsed: 00:00:32.37


| Id  | Operation                    | Name      | Starts | E-Rows | A-Rows |A-Time      | Buffers | Reads  |
|   1 |  SORT GROUP BY NOSORT        |           |      1 |      1 |      1 |00:00:32.35 |     402K|    402K|
|   2 |   TABLE ACCESS BY INDEX ROWID| T10       |      1 |   8856 |   9170K|00:00:27.57 |     402K|    402K|
|*  3 |    INDEX RANGE SCAN          | IND_T10_2 |      1 |   8856 |   9170K|00:00:09.22 |   17879 |  17879 |

Predicate Information (identified by operation id):
   3 - access("COL2"=:N1)

EXEC :N1:=25


  COL2= :N1

      COL2         C1
---------- ----------
        25    9170000

Elapsed: 00:00:20.76


| Id  | Operation            | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
|   1 |  SORT GROUP BY NOSORT|      |      1 |      1 |      1 |00:00:20.57 |     384K|    384K|
|*  2 |   TABLE ACCESS FULL  | T10  |      1 |   9234K|   9170K|00:00:27.54 |     384K|    384K|

Predicate Information (identified by operation id):
   2 - filter("COL2"=:N1)

EXEC :N1:=1


  COL2= :N1

      COL2         C1
---------- ----------
         1      10000

Elapsed: 00:00:20.20


| Id  | Operation            | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
|   1 |  SORT GROUP BY NOSORT|      |      1 |      1 |      1 |00:00:20.19 |     384K|    384K|
|*  2 |   TABLE ACCESS FULL  | T10  |      1 |   9234K|  10000 |00:00:28.73 |     384K|    384K|

Predicate Information (identified by operation id):
   2 - filter("COL2"=:N1)

The above shows that the first time the SQL statement is hard parsed, a bind variable value of 1 is set, which causes an index range scan regardless if the query will select 0.1% of the rows or 91.7% of the rows. OK, if we then flush the shared pool and first set the bind variable value to 25, a full table scan is used regardless if we select 91.7% of the rows or 0.1% of the rows. You will note that when the full table scan is used when the bind variable was set to 25 the query completed in 20.76 seconds, and when an index range scan was used with the same bind variable value the query completed in 32.37 seconds.

OK so far, now the potentially confusing part. When an index range scan was used for both bind variable values, Oracle counted the 0.1% of the matching rows (10000) in 42.72 seconds, while counting 91.7% of the rows (9,170,000) in just 32.37 seconds. You might be wondering why Oracle is able to return the result of counting 91.7% of the rows by the index range scan faster than it is able to count 0.1% of the rows – I will leave that for your investigation.

Now, reviewing the above, what is better?:
* Allow the bind variable values submitted during the hard parse to determine the execution plan.
* Use a stored outline to lock the execution plan to always use an index range scan.
* Use a stored outline to lock the execution plan to always use a full table scan.
* Disable bind variable peeking.
* Not enough information is available.