Reading Material On Order 2

29 05 2011

May 29, 2011

A year ago I wrote an article that had a couple of topics, one of which described three books that I put on order – ordered with the intention of writing reviews for all three books.  Two of the books arrived in a timely fashion, while the third book has yet to arrive despite being ordered twice from Amazon (I had to rely on the Google Books viewer for the review).

I just recently purchased three electronic formatted books (otherwise known as eBooks), and I plan to put the Motorola Xoom to use while reading those books.  The books include: “Troubleshooting Oracle Performance“, “Beginning Oracle SQL“, and “Oracle Database 11g Performance Tuning Recipes: A Problem-Solution Approach”.  I will likely review the second and third of the above books, and if I find time I will try to improve the level of detail found in the review of the first of the above books so that the review matches the level of detail found in the book reviews that I wrote a couple of months later.  That said, here are a couple of quick observations about the books:

Troubleshooting Oracle Performance

I tested the PDF and ePUB (apparently used by the Nook) versions of this book.  While the PDF version of the book accurately reproduces the book contents, I recommend staying away from the ePUB version, if possible.  You can read about my adventures with the PDF version and ePUB version of that book at the end of one of my previous articles.  It has been almost three years since my first read through of this book, and while I found a couple of minor errors (yet somewhat obvious errors that the author quickly addressed before I had a chance to read those sections of the book), I did not find anything negative worth mentioning in the original review.  About a year ago I started re-reading the book, but only made it about 100 to 120 pages into the book before I had to break-away to something else.  I do not recall taking any notes in the first 100 to 120 pages, but I do recall taking fairly extensive notes in later parts of the book during the initial read.

Beginning Oracle SQL

This book is intended as an update to the “Mastering Oracle SQL and SQL*Plus” book that was originally written by Lex DeHaan, and was apparently intended both and as an introduction to SQL as well as a brief demonstration of more advanced techniques.  I was impressed with the quality and accuracy of the original book, and I was excited to see a follow-up book titled “Pro Oracle SQL“.  The original “Mastering Oracle SQL and SQL*Plus” book did have a couple of problems: most of the pages lacked page numbers, various formatting problems that resulted in dropped characters, and a missing Oracle Database 10.1.0.2 CD that was promised on the book’s cover.  I have not had a lot of time to examine the “Beginning Oracle SQL” book.  However, I did notice that every page that should have a page number now has a page number, I have not noticed any formatting problems that resulted in dropped characters, and the front cover no longer advertises that the book includes Oracle Database on CD.  I initially thought that all mention of analytic queries had been dropped from the “Beginning Oracle SQL” book due to the extended discussion of this topic in the “Pro Oracle SQL” book, and a search for the word analytic finds only a single page containing that word.  Thankfully, it does appear that a couple of the analytic functions are mentioned in the updated book.  It does not appear that this is just a simple reprint of the original book – the new book includes descriptions of Oracle Database 11.1 and possibly 11.2 features.  More information will follow if I have a chance to read the entire book.  The Google Books viewer application crashes when attempting to show book pages 33 and 34 side-by-side (pages 56 and 57 as displayed in Google Books), but the problem is not present when the Xoom is held in portrait orientation.

Oracle Database 11g Performance Tuning Recipes: A Problem-Solution Approach

This book is set to be released in the middle of August 2011, so I bought the alpha copy of the book that currently includes chapters 3, 5, 6, 7, and 14.  I bought this book because I was curious to see how the recipe format works for a book written on the topic of performance tuning.  That format worked very well for one of the books that I reviewed, and mostly/almost worked for another book that I reviewed (with the notable exception of the SQL statements that were Oracle Database performance related).  How well will the format work for a book that is specifically written about Oracle Database 11g performance tuning?  Let’s just say that I hope that there is a good technical reviewer involved in this book project, that the authors listen to the technical reviewer, and that the alpha copy of the chapters were captured before the technical reviewer had a chance to examine the chapters.  If the alpha copy of the book chapters actually shows the results after the technical reviewers provided recommendations, this will very likely be the first Apress title that I have read which will receive a 3 star, or very likely lower, rating on a 5 star scale when I write a review of the book.  I do not want to go into a lot of specifics after a very quick examination of a couple of alpha chapters of a book, but I will mention a couple of examples of problems that I identified:

  • The introduction to chapter 3 states that the results of SQL queries and PL/SQL functions may be stored in the shared pool when a results cache is used.  It might be a better idea to be much more specific, and state that Oracle Database is using the Result Cache Memory in the shared pool as indicated in the Oracle Database documentation, but maybe this is explained later.
  • In recipe 3-1 the authors mention that they would set the MEMORY_MAX_TARGET to 1500M, and then show a SQL statement that sets that parameter to 2G.  This particular inconsistency will likely be caught in a later review of the book material.  This recipe seems to be mirroring a page from the Oracle documentation library, only that the order of a couple commands were specified incorrectly in the book.  The comment regarding the _TARGET parameters does not seem to apply to the SGA_TARGET parameter.  This recipe states that “automatic memory management is the recommended approach to managing Oracle’s memory allocation,” but does not describe any of the potential pitfalls or operating system specific problem areas/limitations (reference1 reference2 reference3 reference4 reference5 reference6).  Beyond telling the reader to simply add the current PGA_AGGREGATE_TARGET and SGA_TARGET values to determine a starting point for MEMORY_MAX_TARGET, the recipe gives little insight into the “best” value for this parameter.  For now I will ignore the numerous spelling mistakes, because the book editors will likely find and address those issues.
  • In recipe 3-2 the authors state, “if you want a segment to be cached (or pinned) in the buffer pool, you must specify the KEEP buffer pool [when creating the segment].”  Something just doesn’t seem to be right (or complete) about this statement – for one, I wonder what the CACHE and  NOCACHE clauses might affect?  Secondly, this statement might be implying that blocks belonging to an object will only remain in the buffer cache if those objects are assigned to the KEEP buffer cache.  The recipe states that the approximate size of the KEEP buffer pool can be determined by “summing up the size of all candidate objects for this pool” – great, now what happens when Oracle Database creates up to six (and potentially many more) consistent read versions of the objects’ blocks (reference)?  The last two paragraphs of this recipe seem to indicate that it is the segments themselves (apparently all blocks) that are aged out of the buffer cache when space is needed to cache other blocks, rather than the segments’ blocks that are aged out of the cache – additional clarity is needed in these two paragraphs.  The second to last paragraph also makes a claim that I am not sure how to interpret: “Oracle figures that if a single segment takes up more than 10% of (nonsequential) physical reads, it’s a large segment, for the purpose of deciding if it needs to use the KEEP or RECYCLE buffer pools.”
  • (Skipping around a bit) In recipe 3-13 we learn how to tune the redo log buffer.  The book states, “since there’s no cost whatsoever to increasing the LOG_BUFFER size, feel free to set it to higher than the suggested maximum of 1MB for this parameter.”  Earlier the book showed how to set the LOG_BUFFER parameter to a value of 4,096,000.  I think that I recall that Oracle Database 11.1 was released after Oracle Database 10.2 ( :-) ), where the LOG_BUFFER parameter started being auto-set to a value slightly smaller than the granule size – the most common granule size for Oracle Database 10.2 databases was likely 16MB, while only databases with a SGA size less then about 1GB saw granule sizes of 4MB.  The granule size rules changed again with the release of Oracle Database 11.1 and again with the release of Oracle Database 11.2.  If you check the comments section of this blog article, one reader commented about the LOG_BUFFER being auto-tuned to nearly 512MB in size.  I wonder how useful the ratio of ‘redo entries’ statistic value divided by the the ‘redo log space requests’ statistic value might be when trying to find the ideal value for the LOG_BUFFER parameter?
  • In recipe 5-1, how does one see a “latch on a log file”?  The recipe also describes a view that has a name that ends with the suffix _HISTORY, but the book failed to mention the licensing requirements to access this view.  When describing the V$SESSION_EVENT view, the book states, “The data in this view are available only so long as a session is active.” – considering that one of the columns in V$SESSION is named ACTIVE, I can see where a statement like this one could lead to confusion.  The last paragraph of the recipe states, “Note that you can query the V$WAITSTAT view for the same information as well.” – that view was not previously introduced and in actuality, the V$WAITSTAT view does not produce information similar to the V$SYSTEM_EVENT view that was described a sentence or two earlier.
  • Recipe 5-2 queries V$ACTIVE_SESSION_HISTORY without first providing a warning regarding the licensing requirements to access this view.  I have not experimented enough with ASH data, but I wonder if SUM(WAIT_TIME + TIME_WAITED) when grouped by SQL_ID and USER_ID could lead to incorrect assumptions regarding the wait time per SQL statement for the last 30 minutes.  Is there a risk of double-counting, or counting incidental CPU usage immediately after a wait event completes, as part of the time spent in wait events for a specific SQL statement?  I am left wondering why the authors did not suggest checking the APPLICATION_WAIT_TIME, CONCURRENCY_WAIT_TIME, CLUSTER_WAIT_TIME, USER_IO_WAIT_TIME, PLSQL_EXEC_TIME, and JAVA_EXEC_TIME columns of V$SQL (or V$SQLAREA) periodically, and calculating the delta (change) values for specific SQL statements – those columns have existed as long as ASH (since the release of Oracle Database 10.1).

I guess that the above is a bit more lengthy than I had originally intended.  Best of luck to the technical reviewers of the book.





Row Values to Comma Separated Lists, an Overly Complicated Use Case Example

26 05 2011

May 26, 2011

In a previous article I showed a simple method to convert values found in multiple table rows into a comma delimited list.  The method works very well, until the situation in which the approach is to be used becomes a bit more complex.

Assume for a moment that the following table structure exists where the approach must be implemented:

In the above diagram, the PURCHASE_ORDER table is the parent table, the PURC_ORDER_LINE table is the child table (the line-level detail), and the PURC_LINE_DEL table is the grand-child table (the sub-line level detail).  A fairly simple setup, and I suspect not a too terribly uncommon setup.

Now the complexity.  Assume that an ERP package using the above tables generates a report using the tables’ data, and as the ERP package is flexible, it allows additional information to be added to the reports – essentially, it is possible to execute a query that retrieves a single row for every detail-level row in the report.  Great, except that in this particular report, the detail-level row level is at the PURC_ORDER_LINE table.  If there were 18 rows, for instance, in the PURC_LINE_DEL table (the schedule that indicates when the parts should be delivered) for the first order line in the PURC_ORDER_LINE table for a particular order ID that is found in the PURCHASE_ORDER table, then we have a bit of a problem.  Fortunately, this ERP package’s report was constructed such that the DESIRED_RECV_DATE and USER_ORDER_QTY columns are represented in two report variables with CRLF (carriage return – line feed) delimited lists, making it very easy to list the delivery dates that the parts are due, and the quantities that are due for each of those dates.  Fantastic, so where is the complexity?  What if the purchase order must be revised after the supplier starts sending the parts on the agreed upon delivery dates, such that the first 9 of the 18 delivery schedules for line 1 of a purchase order have been satisfied, 1 of the 18 delivery schedule lines has been partially satisfied, and the remaining 8 delivery schedule lines have dates that are in the future?  This might be helpful information to provide to the supplier when presenting the updated purchase order.  Unfortunately, the ERP package’s report does not deliver the USER_RECEIVED_QTY column as a CRLF delimited list, as it does for the DESIRED_RECV_DATE and USER_ORDER_QTY columns. 

A complex problem, or not?  Well, the ERP package does permit a SQL statement returning a single row to be executed per detail row in the report (at the PURC_ORDER_LINE table level), so we have a possible solution.  However, if the SQL statement is too complex (having inline views is one such complication), the complex portion of the SQL statement must be isolated in a statically defined view, and then that view should be referenced by the report.  A complex problem, or not?  Let’s try building a solution starting in SQL*Plus:

SELECT
  PURC_ORDER_ID,
  PURC_ORDER_LINE_NO,
  USER_RECEIVED_QTY,
  ROW_NUMBER() OVER (PARTITION BY PURC_ORDER_ID,PURC_ORDER_LINE_NO ORDER BY DEL_SCHED_LINE_NO) RN
FROM
  PURC_LINE_DEL
WHERE
  PURC_ORDER_ID='146825'
  AND PURC_ORDER_LINE_NO=1
ORDER BY
  DEL_SCHED_LINE_NO;

PURC_ORDER_ID   PURC_ORDER_LINE_NO USER_RECEIVED_QTY         RN
--------------- ------------------ ----------------- ----------
146825                           1                 1          1
146825                           1                 1          2
146825                           1                 2          3
146825                           1                 2          4
146825                           1                 2          5
146825                           1                 3          6
146825                           1                 3          7
146825                           1                 1          8
146825                           1                 3          9
146825                           1                 2         10
146825                           1                 0         11
146825                           1                 0         12
146825                           1                 0         13
146825                           1                 0         14
146825                           1                 0         15
146825                           1                 0         16
146825                           1                 0         17
146825                           1                 0         18 

Simple so far, that line identified by RN 10 requests an order quantity of 3, but the supplier only delivered 2 of the 3 parts for that date.  Now let’s generate a comma separated list using the SYS_CONNECT_BY_PATH function:

SELECT
  SYS_CONNECT_BY_PATH(TO_CHAR(USER_RECEIVED_QTY),',') LISTING
FROM
(SELECT
  PURC_ORDER_ID,
  PURC_ORDER_LINE_NO,
  USER_RECEIVED_QTY,
  ROW_NUMBER() OVER (PARTITION BY PURC_ORDER_ID,PURC_ORDER_LINE_NO ORDER BY DEL_SCHED_LINE_NO) RN
FROM
  PURC_LINE_DEL
WHERE
  PURC_ORDER_ID='146825'
  AND PURC_ORDER_LINE_NO=1
ORDER BY
  DEL_SCHED_LINE_NO) V
CONNECT BY PRIOR
  RN = RN-1
START WITH
  RN = 1;

LISTING
------------------------------------

,1
,1,1
,1,1,2
,1,1,2,2
,1,1,2,2,2
,1,1,2,2,2,3
,1,1,2,2,2,3,3
,1,1,2,2,2,3,3,1
,1,1,2,2,2,3,3,1,3
,1,1,2,2,2,3,3,1,3,2
,1,1,2,2,2,3,3,1,3,2,0
,1,1,2,2,2,3,3,1,3,2,0,0
,1,1,2,2,2,3,3,1,3,2,0,0,0
,1,1,2,2,2,3,3,1,3,2,0,0,0,0
,1,1,2,2,2,3,3,1,3,2,0,0,0,0,0
,1,1,2,2,2,3,3,1,3,2,0,0,0,0,0,0
,1,1,2,2,2,3,3,1,3,2,0,0,0,0,0,0,0
,1,1,2,2,2,3,3,1,3,2,0,0,0,0,0,0,0,0 

We only want the last row returned from the above result, and we need to change those commas to a CRLF character combination (after eliminating the initial comma):

SELECT
  MAX(REPLACE(SUBSTR(SYS_CONNECT_BY_PATH(TO_CHAR(USER_RECEIVED_QTY),','),2),',',CHR(13)||CHR(10))) LISTING
FROM
(SELECT
  PURC_ORDER_ID,
  PURC_ORDER_LINE_NO,
  USER_RECEIVED_QTY,
  ROW_NUMBER() OVER (PARTITION BY PURC_ORDER_ID,PURC_ORDER_LINE_NO ORDER BY DEL_SCHED_LINE_NO) RN
FROM
  PURC_LINE_DEL
WHERE
  PURC_ORDER_ID='146825'
  AND PURC_ORDER_LINE_NO=1
ORDER BY
  DEL_SCHED_LINE_NO) V
CONNECT BY PRIOR
  RN = RN-1
START WITH
  RN = 1;

LISTING
-------

1
1
2
2
2
3
3
1
3
2
0
0
0
0
0
0
0
0

Looks like mission accomplished, all of the previously received quantities are returned in a single query row… except now we have a complicated query that must be isolated in a statically defined view (silly rules…).

Before we head down the path of isolating the complex portion of the query, I thought that it might be entertaining to examine a couple of execution plan to see what happens when we isolate the complicated portion of the query in a statically defined view.  First, we will take a look at the last of the above SQL statements:

SET LINESIZE 140
SET TRIMSPOOL ON
SET PAGESIZE 1000
SET AUTOTRACE TRACEONLY EXPLAIN

SELECT
  MAX(REPLACE(SUBSTR(SYS_CONNECT_BY_PATH(TO_CHAR(USER_RECEIVED_QTY),','),2),',',CHR(13)||CHR(10))) LISTING
FROM
(SELECT
  PURC_ORDER_ID,
  PURC_ORDER_LINE_NO,
  USER_RECEIVED_QTY,
  ROW_NUMBER() OVER (PARTITION BY PURC_ORDER_ID,PURC_ORDER_LINE_NO ORDER BY DEL_SCHED_LINE_NO) RN
FROM
  PURC_LINE_DEL
WHERE
  PURC_ORDER_ID='146825'
  AND PURC_ORDER_LINE_NO=1
ORDER BY
  DEL_SCHED_LINE_NO) V
CONNECT BY PRIOR
  RN = RN-1
START WITH
  RN = 1;

Plan hash value: 929864805

----------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |               |     1 |    26 |     4  (25)| 00:00:01 |
|   1 |  SORT AGGREGATE                          |               |     1 |    26 |            |          |
|*  2 |   CONNECT BY NO FILTERING WITH START-WITH|               |       |       |            |          |
|   3 |    VIEW                                  |               |     4 |   104 |     3   (0)| 00:00:01 |
|   4 |     WINDOW NOSORT                        |               |     4 |    76 |     3   (0)| 00:00:01 |
|   5 |      TABLE ACCESS BY INDEX ROWID         | PURC_LINE_DEL |     4 |    76 |     3   (0)| 00:00:01 |
|*  6 |       INDEX RANGE SCAN                   | SYS_C0021790  |     4 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("RN"-1=PRIOR "RN")
       filter("RN"=1)
   6 - access("PURC_ORDER_ID"='146825' AND "PURC_ORDER_LINE_NO"=1) 

The above looks simple, and the query really does execute quickly.  For the above complex portion of the query to work in a statically defined view, we need to relocate the WHERE clause outside of the inline view, so that when the report runs, it is able to pass in the appropriate values for the PURC_ORDER_ID and PURC_ORDER_LINE_NO columns.  Here is the execution plan (generated on Oracle Database 11.2.0.2) when the WHERE clause is relocated:

SELECT
  MAX(REPLACE(SUBSTR(SYS_CONNECT_BY_PATH(TO_CHAR(USER_RECEIVED_QTY),','),2),',',CHR(13)||CHR(10))) LISTING
FROM
(SELECT
  PURC_ORDER_ID,
  PURC_ORDER_LINE_NO,
  USER_RECEIVED_QTY,
  ROW_NUMBER() OVER (PARTITION BY PURC_ORDER_ID,PURC_ORDER_LINE_NO ORDER BY DEL_SCHED_LINE_NO) RN
FROM
  PURC_LINE_DEL
ORDER BY
  DEL_SCHED_LINE_NO) V
WHERE
  PURC_ORDER_ID='146825'
  AND PURC_ORDER_LINE_NO=1
CONNECT BY PRIOR
  RN = RN-1
START WITH
  RN = 1;

Plan hash value: 2107147172

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name          | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |               |     1 |    48 |       |  1193  (50)| 00:00:05 |
|   1 |  SORT AGGREGATE                           |               |     1 |    48 |       |            |          |
|*  2 |   FILTER                                  |               |       |       |       |            |          |
|*  3 |    CONNECT BY NO FILTERING WITH START-WITH|               |       |       |       |            |          |
|   4 |     VIEW                                  |               | 53851 |  2524K|       |   625   (3)| 00:00:03 |
|   5 |      SORT ORDER BY                        |               | 53851 |   999K|  1704K|   625   (3)| 00:00:03 |
|   6 |       WINDOW SORT                         |               | 53851 |   999K|  1704K|   625   (3)| 00:00:03 |
|   7 |        TABLE ACCESS FULL                  | PURC_LINE_DEL | 53851 |   999K|       |    57   (8)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("PURC_ORDER_ID"='146825' AND "PURC_ORDER_LINE_NO"=1)
   3 - access("RN"-1=PRIOR "RN")
       filter("RN"=1) 

The above looks to be reasonably sane, although the predicted amount of time jumped from about 1 second to about 5 seconds.  The Predicate Information section of the execution plan indicates that the filter for the specified PURC_ORDER_ID and PURC_ORDER_LINE_NO was not pushed into the inline view as I had hoped.  The actual execution time for this simple query is much longer than 5 seconds, spending 100% of its time burning CPU time (I killed the query after waiting several minutes).  No problem, I will just use a hint to force the optimizer to push those predicates into the inline view:

SELECT /*+ PUSH_PRED(V) */
  MAX(REPLACE(SUBSTR(SYS_CONNECT_BY_PATH(TO_CHAR(USER_RECEIVED_QTY),','),2),',',CHR(13)||CHR(10))) LISTING
FROM
(SELECT
  PURC_ORDER_ID,
  PURC_ORDER_LINE_NO,
  USER_RECEIVED_QTY,
  ROW_NUMBER() OVER (PARTITION BY PURC_ORDER_ID,PURC_ORDER_LINE_NO ORDER BY DEL_SCHED_LINE_NO) RN
FROM
  PURC_LINE_DEL
ORDER BY
  DEL_SCHED_LINE_NO) V
WHERE
  PURC_ORDER_ID='146825'
  AND PURC_ORDER_LINE_NO=1
CONNECT BY PRIOR
  RN = RN-1
START WITH
  RN = 1;

Plan hash value: 2107147172

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name          | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |               |     1 |    48 |       |  1193  (50)| 00:00:05 |
|   1 |  SORT AGGREGATE                           |               |     1 |    48 |       |            |          |
|*  2 |   FILTER                                  |               |       |       |       |            |          |
|*  3 |    CONNECT BY NO FILTERING WITH START-WITH|               |       |       |       |            |          |
|   4 |     VIEW                                  |               | 53851 |  2524K|       |   625   (3)| 00:00:03 |
|   5 |      SORT ORDER BY                        |               | 53851 |   999K|  1704K|   625   (3)| 00:00:03 |
|   6 |       WINDOW SORT                         |               | 53851 |   999K|  1704K|   625   (3)| 00:00:03 |
|   7 |        TABLE ACCESS FULL                  | PURC_LINE_DEL | 53851 |   999K|       |    57   (8)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("PURC_ORDER_ID"='146825' AND "PURC_ORDER_LINE_NO"=1)
   3 - access("RN"-1=PRIOR "RN")
       filter("RN"=1) 

Well, the above is disappointing.  I could dig through a 10053 trace to try to determine why the hint was “ignored“, or I could try another query that produces an equivalent result.  I choose the latter.  Let’s try using the COUNT analytic function to replace the ROW_NUMBER analytic function:

SELECT
  MAX(REPLACE(SUBSTR(SYS_CONNECT_BY_PATH(TO_CHAR(USER_RECEIVED_QTY),','),2),',',CHR(13)||CHR(10))) LISTING
FROM
(SELECT
  PURC_ORDER_ID,
  PURC_ORDER_LINE_NO,
  DEL_SCHED_LINE_NO,
  USER_RECEIVED_QTY,
  COUNT(*) OVER (PARTITION BY PURC_ORDER_ID,PURC_ORDER_LINE_NO ORDER BY DEL_SCHED_LINE_NO) RN
FROM
  PURC_LINE_DEL
WHERE
  PURC_ORDER_ID='146825'
  AND PURC_ORDER_LINE_NO=1
ORDER BY
  DEL_SCHED_LINE_NO) V
CONNECT BY PRIOR
  RN=RN-1
START WITH
  RN=1;

Plan hash value: 617003335

----------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |               |     1 |    26 |     4  (25)| 00:00:01 |
|   1 |  SORT AGGREGATE                          |               |     1 |    26 |            |          |
|*  2 |   CONNECT BY NO FILTERING WITH START-WITH|               |       |       |            |          |
|   3 |    VIEW                                  |               |     4 |   104 |     3   (0)| 00:00:01 |
|   4 |     WINDOW BUFFER                        |               |     4 |    76 |     3   (0)| 00:00:01 |
|   5 |      TABLE ACCESS BY INDEX ROWID         | PURC_LINE_DEL |     4 |    76 |     3   (0)| 00:00:01 |
|*  6 |       INDEX RANGE SCAN                   | SYS_C0021790  |     4 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("RN"-1=PRIOR "RN")
       filter("RN"=1)
   6 - access("PURC_ORDER_ID"='146825' AND "PURC_ORDER_LINE_NO"=1) 

The execution plan appears to be reasonable – the predicates on the PURC_ORDER_ID and PURC_ORDER_LINE_NO columns are applied early, so the query executes quickly.  Now to move the WHERE clause:

SELECT
  MAX(REPLACE(SUBSTR(SYS_CONNECT_BY_PATH(TO_CHAR(USER_RECEIVED_QTY),','),2),',',CHR(13)||CHR(10))) LISTING
FROM
(SELECT
  PURC_ORDER_ID,
  PURC_ORDER_LINE_NO,
  DEL_SCHED_LINE_NO,
  USER_RECEIVED_QTY,
  COUNT(*) OVER (PARTITION BY PURC_ORDER_ID,PURC_ORDER_LINE_NO ORDER BY DEL_SCHED_LINE_NO) RN
FROM
  PURC_LINE_DEL
ORDER BY
  DEL_SCHED_LINE_NO) V
WHERE
  PURC_ORDER_ID='146825'
  AND PURC_ORDER_LINE_NO=1
CONNECT BY PRIOR
  RN=RN-1
START WITH
  RN=1;

Plan hash value: 2107147172

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name          | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |               |     1 |    48 |       |  1193  (50)| 00:00:05 |
|   1 |  SORT AGGREGATE                           |               |     1 |    48 |       |            |          |
|*  2 |   FILTER                                  |               |       |       |       |            |          |
|*  3 |    CONNECT BY NO FILTERING WITH START-WITH|               |       |       |       |            |          |
|   4 |     VIEW                                  |               | 53851 |  2524K|       |   625   (3)| 00:00:03 |
|   5 |      SORT ORDER BY                        |               | 53851 |   999K|  1704K|   625   (3)| 00:00:03 |
|   6 |       WINDOW SORT                         |               | 53851 |   999K|  1704K|   625   (3)| 00:00:03 |
|   7 |        TABLE ACCESS FULL                  | PURC_LINE_DEL | 53851 |   999K|       |    57   (8)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("PURC_ORDER_ID"='146825' AND "PURC_ORDER_LINE_NO"=1)
   3 - access("RN"-1=PRIOR "RN")
       filter("RN"=1) 

As had happened before, the predicates for the PURC_ORDER_ID and PURC_ORDER_LINE_NO columns were not pushed into the inline view, so the query executes extremely slow.  Let’s try the PUSH_PRED hint again:

SELECT /*+ PUSH_PRED(V) */
  MAX(REPLACE(SUBSTR(SYS_CONNECT_BY_PATH(TO_CHAR(USER_RECEIVED_QTY),','),2),',',CHR(13)||CHR(10))) LISTING
FROM
(SELECT
  PURC_ORDER_ID,
  PURC_ORDER_LINE_NO,
  DEL_SCHED_LINE_NO,
  USER_RECEIVED_QTY,
  COUNT(*) OVER (PARTITION BY PURC_ORDER_ID,PURC_ORDER_LINE_NO ORDER BY DEL_SCHED_LINE_NO) RN
FROM
  PURC_LINE_DEL
ORDER BY
  DEL_SCHED_LINE_NO) V
WHERE
  PURC_ORDER_ID='146825'
  AND PURC_ORDER_LINE_NO=1
CONNECT BY PRIOR
  RN=RN-1
START WITH
  RN=1;

Plan hash value: 2107147172

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name          | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |               |     1 |    48 |       |  1193  (50)| 00:00:05 |
|   1 |  SORT AGGREGATE                           |               |     1 |    48 |       |            |          |
|*  2 |   FILTER                                  |               |       |       |       |            |          |
|*  3 |    CONNECT BY NO FILTERING WITH START-WITH|               |       |       |       |            |          |
|   4 |     VIEW                                  |               | 53851 |  2524K|       |   625   (3)| 00:00:03 |
|   5 |      SORT ORDER BY                        |               | 53851 |   999K|  1704K|   625   (3)| 00:00:03 |
|   6 |       WINDOW SORT                         |               | 53851 |   999K|  1704K|   625   (3)| 00:00:03 |
|   7 |        TABLE ACCESS FULL                  | PURC_LINE_DEL | 53851 |   999K|       |    57   (8)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("PURC_ORDER_ID"='146825' AND "PURC_ORDER_LINE_NO"=1)
   3 - access("RN"-1=PRIOR "RN")
       filter("RN"=1) 

The execution plan is unchanged.  Out of luck…

I remembered reading about a new analytic function introduced in Oracle Database 11.2.0.1, what was that new function… LISTAGG?  I am not a fan of that function name, Oracle should have named the function __CREATE_A_DARN_COMMA_SEPARATED_LIST_QUICKLY (a double underscore function, making it a challenge to find the function).  The LISTAGG function is described in the Oracle documentation library for 11.2.  Let’s give the LISTAGG function a try (the SQL statement is also now formatting the USER_RECEIVED_QTY so that the column values consistently show 2 decimal places):

SELECT
  LISTING
FROM
(SELECT
  LISTAGG(TO_CHAR(USER_RECEIVED_QTY,'999,990.00'), CHR(13)||CHR(10)) WITHIN GROUP (ORDER BY DEL_SCHED_LINE_NO) LISTING
FROM
  PURC_LINE_DEL
WHERE
  PURC_ORDER_ID='146825'
  AND PURC_ORDER_LINE_NO=1
GROUP BY
  PURC_ORDER_ID,
  PURC_ORDER_LINE_NO);

Plan hash value: 3148992471

----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |               |     1 |    19 |     3   (0)| 00:00:01 |
|   1 |  SORT GROUP BY               |               |     1 |    19 |     3   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| PURC_LINE_DEL |     4 |    76 |     3   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | SYS_C0021790  |     4 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("PURC_ORDER_ID"='146825' AND "PURC_ORDER_LINE_NO"=1) 

The calculated cost for the query dropped by 1 and the CPU component of that cost dropped to 0%, so that must be a good thing (the calculated cost can be misleading, just as we saw earlier that the estimated time can be misleading).  Without AUTOTRACE TRACEONLY EXPLAIN enabled, the query executes very quickly and produces output that looks similar to this:

LISTING
-----------

       1.00
       1.00
       2.00
       2.00
       2.00
       3.00
       3.00
       1.00
       3.00
       2.00
       0.00
       0.00
       0.00
       0.00
       0.00
       0.00
       0.00
       0.00
 

OK, let’s re-enable AUTOTRACE TRACEONLY EXPLAIN and try moving the WHERE clause so that we are able to package the complex portion of this query into a statically defined view:

SELECT
  LISTING
FROM
(SELECT
  PURC_ORDER_ID,
  PURC_ORDER_LINE_NO,
  LISTAGG(TO_CHAR(USER_RECEIVED_QTY,'999,990.00'), CHR(13)||CHR(10)) WITHIN GROUP (ORDER BY DEL_SCHED_LINE_NO) LISTING
FROM
  PURC_LINE_DEL
GROUP BY
  PURC_ORDER_ID,
  PURC_ORDER_LINE_NO) V
WHERE
  PURC_ORDER_ID='146825'
  AND PURC_ORDER_LINE_NO=1;

Plan hash value: 3148992471

----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |               |     1 |    19 |     3   (0)| 00:00:01 |
|   1 |  SORT GROUP BY               |               |     1 |    19 |     3   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| PURC_LINE_DEL |     4 |    76 |     3   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | SYS_C0021790  |     4 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("PURC_ORDER_ID"='146825' AND "PURC_ORDER_LINE_NO"=1) 

In the above, notice that the  predicates for the PURC_ORDER_ID and PURC_ORDER_LINE_NO columns were pushed into the inline view, or more correctly, the inline view was completely eliminated by the query optimizer (notice that there is no line in the execution plan showing VIEW as we saw in the previous examples above).

OK, so Oracle’s query optimizer worked as I had hoped with the inline view, but what about a statically defined view – would it also execute the SQL statement efficiently?  Let’s create the view:

CREATE VIEW TEST_LA AS
SELECT
  PURC_ORDER_ID,
  PURC_ORDER_LINE_NO,
  LISTAGG(TO_CHAR(USER_RECEIVED_QTY,'999,990.00'), CHR(13)||CHR(10)) WITHIN GROUP (ORDER BY DEL_SCHED_LINE_NO) LISTING
FROM
  PURC_LINE_DEL
GROUP BY
  PURC_ORDER_ID,
  PURC_ORDER_LINE_NO; 

Now let’s try the query with AUTOTRACE TRACEONLY EXPLAIN enabled:

SELECT
  LISTING
FROM
  TEST_LA
WHERE
  PURC_ORDER_ID='146825'
  AND PURC_ORDER_LINE_NO=1;

Plan hash value: 3148992471

----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |               |     1 |    19 |     3   (0)| 00:00:01 |
|   1 |  SORT GROUP BY               |               |     1 |    19 |     3   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| PURC_LINE_DEL |     4 |    76 |     3   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | SYS_C0021790  |     4 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("PURC_ORDER_ID"='146825' AND "PURC_ORDER_LINE_NO"=1) 

The execution plan above shows the same cost (3) that we saw earlier, and once again the view was eliminated by Oracle’s query optimizer (as indicated by the absence of a VIEW line in the execution plan), allowing the query to execute quickly since the predicates would be applied early in the query execution.

So, Oracle Database 11.2.0.1 and above provide an efficient way to retrieve the information that we need in a comma (or CRLF) delimited list for the report.  When the SQL statement is added to the report (defined as an “extended query” in the ERP package), the SQL statement looks like the following (the reporting package does not permit line breaks in the SQL statement):

SELECT (LISTAGG(TO_CHAR(USER_RECEIVED_QTY,'999,990.00'), CHR(13)||CHR(10)) WITHIN GROUP (ORDER BY DEL_SCHED_LINE_NO)) FROM PURC_LINE_DEL WHERE PURC_ORDER_ID= : PO_ID AND PURC_ORDER_LINE_NO= :LN_LINE_NO GROUP BY PURC_ORDER_ID , PURC_ORDER_LINE_NO

Oddly enough, by enclosing the LISTAGG analytic funtion in the SQL statement with ( ), the reporting package did not require me to use a statically defined view for the complex portion of the query.  But even if the statically defined view was required, as shown above, the query would still have executed efficiently.





On the Topic of Technology… 3

24 05 2011

May 24, 2011 (Updated May 28, 2011)

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

In the previous article in this series I described some of the features of the Motorola Xoom tablet, and some of the reasons why I purchased that tablet rather than the Apple iPad 2.  When I wrote the previous article I could not identify a lot of productive uses for the Xoom – most of the uses seemed to be specific to media consumption (something that is quite expensive when you must pay per megabyte of usage on a wireless plan).

After a bit of thought, I added a BlueTooth keyboard to the Xoom and fired up Oracle Database 11.2.0.2:

The method by which the above works is actually quite simple.  More on that later.  The Honeycomb 3.1 operating system upgrade that was pushed out on May 14, 2011 seems to have corrected the lagging keypresses (and missing keypresses) problem with the on-screen keyboard.

It is quite easy to fill an entire Xoom screen with applications, some of which are free, and others that cost just a couple of dollars.  Fortunately, there are four more screens waiting to be filled with more applications and widgets:

A quick search of the Android MarketPlace for the word Oracle currently finds 94 applications and 246 books, the number of which is slightly surprising, yet might be pointing at a positive that the Xoom can be useful for productive tasks too:

To test the built-in book reader application, I decided I needed to add an Oracle Database related book.  The results are impressive, with line drawings, color graphics, and text just like the original book.  Slick special effects are also a part of the book viewer, most noticeable when changing from one page to another.  The book viewer did crash when viewing one set of pages in this book, however I am not sure if that is still a problem after the rollout of the Honeycomb 3.1 operating system:

Google’s speech to text engine in the Xoom is very impressive, even if it did drop the word “the” in the phrase that I recited (this happened multiple times).  There is a free Google Translate application for the Xoom that accepts speech, converts the speech to text, and then translates the text to another language.  In theory it will also recite the translated text in the foreign language, but that did not seem to work properly when tested:

After the Honeycomb 3.1 upgrade, the Xoom now supports movie rental though the Marketplace application, the tasklist (third button from the left at the bottom of the screen) shows more than the last 5 or so recently started applications, and a couple of other enhancements made their way into the operating system.  Google Goggles and Google Voice Actions are interesting applications for the Xoom, and just might lead to far too many people mindlessly talking to their digital gadgets.  The PocketCloud application was used to VNC into a laptop computer for the first of the pictures in this article – without the external keyboard it was nearly impossible to type into the SQL*Plus window on the Xoom, because the on-screen keyboard did not appear automatically (the on-screen keyboard can be manually displayed with the PocketCloud application).

Prior to the release of the Honeycomb 3.1 operating system for the Xoom I was becoming upset with the number of crashes (force close in Android terminology) and the on-screen keyboard issues, and I was also growing a bit bored trying to find useful applications for the Xoom that did not pose a risk of exceeding my monthly Internet data cap.  So, I wandered into an electronics store.  Nothing interesting here (I later learned that the manufacturer would recall 1,000 of these units from this particular chain electonics store), so I drove to another store located about a block away.  I wandered out with one of these:

The above is a picture of a BlackBerry PlayBook.  The unit has 16GB of memory (larger memory capacities were available), a smaller screen than the Xoom, a good quality video and still picture camera, and an impressive task-switcher that shows off the multitasking capabilities of the unit (shown above).  The PlayBook also shipped with a built-in word processor and spreadsheet application, while I had to pay extra for those features on the Xoom.  The on-screen keyboard is fantastic compared to that found on the Xoom.

In this article I did not include a picture of the search, but searching for the term Oracle in the PlayBook’s App World returned a single result… and that result had nothing to do with Oracle Databases.

So, why did I buy a BlackBerry Playbook?  There is not even a stand-alone email application for the PlayBook, unlike the built-in Gmail application on the Xoom.  Well, if you have a BlackBerry phone that is connected to a BlackBerry Enterprise Server, you can look at and respond to your corporate email:

The above uses the BlackBerry Bridge application on the PlayBook to talk to the BlackBerry phone.  The catch here is that if the group that is responsible for administering the IT policy for the BlackBerry phones has disabled installation of third party applications in the IT Policy for the phone, it is not possible to install the BlackBerry application on the phone that works with the BlackBerry Bridge application on the PlayBook.

The BlackBerry Bridge also permits browsing websites using the phone’s data plan, without incurring an additional monthly cost.  The catch is that content rich websites simply do not display (the limit seems to be around 100KB).  The same websites will display correctly when using the PlayBook’s regular web browser, even if websites insist on sending the mobile version of their web pages.  The same data limit also seems to be present when viewing large email attachments (those that are larger than 100KB do not seem to display).

The iSpeech Translator is a free application found in the BlackBerry App World.  This free application features a speech to text converter, as well as translation to other languages both in text form and as speech.  The speech to text converter is very slow compared to the Xoom, and I think that I might observe a funny response if I actually used the translated text shown below (this is the same phrase that was supplied to the Xoom):

Unlike the Xoom, where it appears to be impossible to perform a screen capture without “rooting” the Xoom or using a cumbersome process that involves installing the Xoom developer kit, it is very easy to perform a screen capture on the PlayBook by holding the volume up and volume down buttons.  This screen capture capability is disabled when the BlackBerry Bridge is active, so shut off your BlackBerry phone if you have set up the BlackBerry Bridge:

The music player in the PlayBook works well.  Using the Xoom I bought several CDs worth of DRM free MP3s from Amazon’s Android portal.  Through a connected USB cable I was able to easily transfer the MP3s to a laptop using Windows Explorer, and then transfer the MP3s to the PlayBook also using Windows Explorer:

Incidentally, the USB and HDMI cables that work with the Xoom also work without problem with the PlayBook.

Unlike the Xoom, with its many applications, the applications for the PlayBook are currently very limited in number (and in some cases depth).  The PocketCloud application, with both VNC and Remote Desktop capability, does not exist for the PlayBook.  However, there is an interesting application that will talk to a VNC server on a Windows computer, and that application is capable of remotely controlling the Windows Media Center application on the Windows computer:

Simply add Internet TV to Windows Media Center, and you can use a BlackBerry PlayBook to change the channel to something more interesting (I might be implying something here).

One final interesting detail.  The Xoom is able to broadcast its MP3s to car receivers using BlueTooth – in this case the car is equiped with Microsoft Sync.  It is a slick feature, and will work even if the Xoom is turned off.  I have not tested to see if the BlackBerry PlayBook supports a similar feature.  A fun way to add a little Xoom to your vehicle without excessive visits to the fuel station:

—————————————————————————————————————-

(Section Added May 28, 2011)

Electronic Book Testing on the Xoom and PlayBook:

Acrobat Reader on the Xoom and PlayBook when showing the PDF eBook version of “Troubleshooting Oracle Performance” both accurately reproduced the printed version of the book.  However, on both tablets, when the pages were viewed in portrait orientation, the screen capture picture at the bottom of page 288 was hard to read due to antialiasing problems where the application simply removed lines of resolution.  The picture was readable in landscape orientation, however in that orientation only about 40% of the page was viewable without scrolling.  Only one page is visible on the screen at a time, although the pages can be stacked vertically end to end to produce a smoothly scrolling chain of pages (similar to the appearance on a desktop PC).

ezPDF Reader on the Xoom when showing the PDF eBook version of “Troubleshooting Oracle Performance” accurately reproduced the printed version of the book.  There were no antialiasing problems with the screen capture picture at the bottom of page 288 – the contents of that picture were easily readable in portrait orientation.  Only one page is visible on the screen at a time.

QuickOffice Pro HD on the Xoom when showing the PDF eBook version of “Troubleshooting Oracle Performance” accurately reproduced the printed version of the book.  There were no antialiasing problems with the screen capture picture at the bottom of page 288 – the contents of that picture were easily readable in portrait orientation.  Only one page is visible on the screen at a time, with pages stacked vertically end to end to produce a smoothly scrolling chain of pages (similar to the appearance on a desktop PC).

The ePUB format of the “Troubleshooting Oracle Performance” book has a file size roughly half as large as the PDF version of the same book.  I tested the Aldiko, FBReader, and CoolReader ePUB readers on the Xoom.  While all three of the ePUB viewers worked (Aldiko force closed a couple of times, usually when rotating the screen 90 degrees) the format reminded me of early HTML web pages with plain text between pictures.  None of the three ePUB viewers retained the original book page numbers, although it was still possible to jump from the index to section headings in the book.  All three viewers had difficulty with the monospaced Courier text areas, most noticeable in execution plans.  The Aldiko program came close when attempting to align the monospaced text correctly, although some of the execution plans still had formatting/alignment issues (alignment spaces were sometimes missing, and wide execution plans often line-wrapped).  The other two viewers used fonts that were not monospaced in those sections of the book, so the formatting/alignment issues often made the code sections impossible to read.

Acrobat Reader on the Xoom seems to lower the quality of images that are embedded in the text.  This was an obvious problem when looking at pages 112 and 114 of the “Expert Oracle Practices” book.

ezPDF Reader on the Xoom does well with the PDF version of the “Expert Oracle Practices” book.  The picture on page 112 is nearly as clear as it is in Acrobat reader on the PC when Acrobat Reader is zoomed to 134% – the picture in both cases is inferior to the printed copy of the picture as found in the book.  The picture on page 114 is very blurry on the PC and in ezPDF, but it is much more clear than it was in Acrobat Reader on the Xoom.

QuickOffice Pro HD failed to render all pictures that were more complicated than simple line drawings in the “Expert Oracle Practices” book.  The pictures on the front cover, pages 112, 114, 115, and several other pages were replaced with simple rectangles.  The line drawing on pages 30, 81, 82 appeared as expected.

Acrobat Reader on the PlayBook could not open the PDF version of the “Expert Oracle Practices” book because the PDF document is password protected as purchased from Apress.

Acrobat Reader, ezPDF Reader, and QuickOffice Pro HD on the Xoom all do well with the PDF version of the book “Expert One-On-One Oracle”.   The start of chapter picture on page 196, and the line drawings on pages 199 and 203 showed as clearly as is found in the printed book.  The code sections that had a light gray background in the book had a nearly invisible gray background in all three PDF viewing applications on the Xoom.  Acrobat Reader on the PlayBook also did well with this book, although the code sections had a much more visible light gray background that duplicated the effect found in the printed book (Acrobat Reader on the PC also produced the more visible light gray background).

Acrobat Reader displayed jagged edges on the circles found on page 18 of the “Oracle Performance Firefighting” book.  Zooming in on the circles greatly reduces the jagged edges, almost as if that forced the application to re-render the line drawing in a higher resolution.  The line drawings on pages 28, 30, 32 also exhibit jagged edges.

ezPDF Reader, and QuickOffice Pro HD on the Xoom exhibited no problems with the “Oracle Performance Firefighting” book – the line drawings were very smooth.

The “Oracle Performance Firefighting” PDF is password protected so it would not open in Acrobat Reader on the PlayBook.

The Google Books reader on the Xoom is very good, and seems to accurately reproduce printed books.  The application crashes when viewing the book “Beginning Oracle SQL” while attempting to display pages 56 and 57 side-by-side – of course the application tries to reopen these two pages when the book is selected, so an endless loop of crashes is experienced every time the book is opened until the tablet is rotated 90 degrees.





Large Page Support or Not – ORA_LPENABLE, Granule Size

17 05 2011

May 17, 2011

Have you ever tried to bounce a database (maybe to adjust a parameter such as PROCESSES, which can only be altered in the spfile/init.ora) and encountered something like this (note that this is on a 64 bit Windows server):

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-27102: out of memory
OSD-00022: additional error information
O/S-Error: (OS 1450) Insufficient system resources exist to complete the requested service. 

Interesting, 14,256MB of free memory, the database instance was just running with an SGA_TARGET set to 10,016M, and now the database will not OPEN.  The SGA_TARGET need not be more than 10,000M in size, the same problem can happen for small databases with the SGA_TARGET set to just 600M.  If you check the alert log, you might find something like this for the database instance with the small SGA_TARGET (note that the server has Intel Xeon X5687 CPUs, Oracle Database 11.2.0.2):

Starting ORACLE instance (normal)
Large page enabled         : 1
Large page size            : 2097152
Large page request size    : 4194304 

The database instance with the 10,016M SGA_TARGET might have an entry similar to the following:

Starting ORACLE instance (normal)
Large page enabled         : 1
Large page size            : 2097152
Large page request size    : 33554432

Straying a bit from the main topic of this blog article, I was a little curious about the change in the Large page request size that was reported for the two database instances.  So, I performed a couple of tests with different SGA_TARGET values:

SGA_TARGET Large Page Request Size
256M 4,194,304
512M 4,194,304
1G 4,194,304
1G + 1 byte 16,777,216
2G 16,777,216
4G 16,777,216
8G 16,777,216
8G + 1 byte 33,554,432
16G 33,554,432
32G 67,108,864
64G 134,217,728
64G + 1 byte 268,435,456

So, what determines the Large page request size?  Checking the Oracle Database 11.2 documentation:

“Memory for the shared pool, large pool, java pool, and buffer cache is allocated in units of granules. The granule size is 4MB if the SGA size is less than 1GB. If the SGA size is greater than 1GB, the granule size changes to 16MB. The granule size is calculated and fixed when the instance starts up. The size does not change during the lifetime of the instance.”

The observation of 4MB up to a SGA_TARGET of 1GB matches the documentation that describes the granual size, as does the observation of 16MB for an SGA_TARGET of 8GB.  However, notice that at a SGA_TARGET of 1 byte beyond 8GB, the Large page request size jumped to 32MB, which is a value greater than what the documentation describes for the granule size.  So, is the granule size still 16MB when the SGA_TARGET is set to a value just larger than 8GB, as indicated it would be in the 11.2 documentation?

You might recall reading in the past that as of Oracle Database 10.2 the LOG_BUFFER parameter is set by default to a value of just less than the granule size, typically being set to a value around 14MB with SGA values larger than 1GB – see Metalink (MOS) Doc ID 351857.1, “The Log_buffer Default Size Cannot Be Reduced In 10g R2″.  Let’s take a look at the size of the LOG_BUFFER in the database instance with the 10,016M SGA_TARGET:

SQL> SHOW PARAMETER LOG_BUFFER
NAME                                 TYPE        VALUE
------------------------------------ ----------- --------
log_buffer                           integer     18202624

The LOG_BUFFER was auto-set to a value of 17.36MB, which possibly indicates that the granule size in this database instance is not 16MB.  If we check Metalink (MOS) Doc ID 947152.1, “How to determine granule size”, we find that the calculation for the granule size has changed from Oracle Database 9.2 to 10.2, again with the release of 11.1, and again with the release of 11.2.  With the discovery of this new bit of information, the values reported for the Large page request size exactly mirrors that of the granule size.

Back to the main topic of this article, large page support.  Here is a quote from the Oracle Database 10.2 documentation:

“Large page support is a feature of Oracle Database 10g Release 1 (10.1) or later. It provides a performance boost for memory-intensive database instances. By taking advantage of newly introduced operating system support, Oracle Database 10g Release 1 (10.1) or later can now make more efficient use of processor memory addressing resources. Specifically, when large page support is enabled, the CPUs in the system will be able to access the Oracle Database buffers in RAM more quickly. Instead of addressing the buffers in 4KB increments, the CPUs are told to use 4MB page sizes when addressing the database buffers.”

A quote from the same section of the Oracle Database 11.2 documentation, with one extra line copied at the end:

“Large page support is a feature of Oracle Database 10g Release 1 (10.1) or later. It provides a performance boost for memory-intensive database instances on Windows Server 2003. By taking advantage of newly introduced operating system support, Oracle Database 10g Release 1 (10.1) or later can now make more efficient use of processor memory addressing resources. Specifically, when large page support is enabled, the CPUs in the system will be able to access the Oracle Database buffers in RAM more quickly. Instead of addressing the buffers in 4KB increments, the CPUs are told to use 2 MB page sizes in Physical Address Extension (PAE) mode and 4MB page sizes in non-PAE mode when addressing the database buffers.”

“By default Oracle allocates the minimum available large page size when using large pages. The minimum available large page size, 16 MB, is obtained by using the GetLargePageMinumum function.”

As you can see by the above, a little additional clarity was added to the 11.2 documentation, but where are the 2MB, 4MB, and 16MB numbers coming from?  Let’s check Metalink (MOS) Doc ID 422844.1, “Using Large Memory Pages on 64-Bit Windows Systems” – a very brief quote:

“… instead of addressing the buffers in 4KB increments, the CPUs are told to use 2 MB page sizes or larger. The default page size depends on the chipset, and the total amount of memory in the system.”

From the above we learn that the default page size depends on the chipset and the amount of memory in the server, with 2MB page sizes being the minimum (that 2MB figure does match the Large page size figure that appears above).  Is it really the chipset, or is it the CPU architecture (Intel vs. AMD, NUMA vs. non-NUMA) that is a potential source of the change?  Is it really the total amount of memory in the system, or is it the total amount of memory allocated to the SGA that is a potential source of the change?

Let’s take a look at a Microsoft document to see if it provides a little more insight into why the ORA-27102: out of memory error might have appeared when there was 14,256MB of free memory in the server:

    • “Large-page memory regions may be difficult to obtain after the system has been running for a long time because the physical space for each large page must be contiguous, but the memory may have become fragmented. Allocating large pages under these conditions can significantly affect system performance…”

The memory must be contiguous… in this case, I wonder if Oracle Database is requesting multiple (313) 32MB allocations of memory without physical gaps in each memory region, or a single 10,016MB contiguous memory region at start up – the difference is significant.

Incidentally, the quick work-around for this problem is to disable large page support by changing the ORA_LPENABLE Windows registry key from a value of 1 to a value of 0 (this parameter is set for the Oracle home, but can also be set for each database instance running from the Oracle home).  There is also a mention of the ORA_LPSIZE registry key in Metalink (MOS) for controlling the size of the large page request size, but not enough information is provided in the articles to determine its exact usage.

On a slight tangent, the ORA_LPENABLE and ORA_LPSIZE registry keys, which are quite clearly intended to store numbers, must be defined as string (REG_SZ) datatypes in the Windows registry.  A bit of irony for a database company to use a string field to store a number, when the same action in the company’s database may have undesirable consequences.





How to Eliminate Wait Events in Oracle Database?

13 05 2011

May 13, 2011

In the last couple of days I have been busy with a couple of non-Oracle Database specific tasks, and I started wondering what types of wait events I might be posting during this time period if I had to report what I was doing.  Waiting on the server to reboot for the 20th time during a software deployment, waiting for replacement hard drives to be shipped, waiting for the darn technology gadgets to download a file or recover from the latest force close (a nice way of saying CRASH!), waiting for a software vendor to return a call, waiting for thoughts to magically appear on PowerPoint slides, etc.

While I was processing all of these wait events, I noticed a couple of recent search keywords being used to find pages on my blog.  One of the sets of search keywords included:
How to Eliminate Wait Events Oracle

I know that I covered that topic in an earlier blog article, but I thought that I would open this topic again for discussion.

While we are at it, let’s try to answer a couple of additional sets of search keywords that lead people to blog articles on this site:
How to Determine Why a CPU is Busy
How to Stop Dead Locks in Oracle

Just a fair warning – the first result returned by a search engine is not necessarily the result that you want in a production environment.





How to Collect Statistics

4 05 2011

May 4, 2011

In a previous article I shared a couple of the slides that I put together for an Oracle Database presentation for a specific ERP group – a presentation that as designed was far too long to fit into the permitted time slot.  I thought that in today’s blog article I would share a couple of “light-weight” slides from the presentation on the topic of statistics collection.

The notes that go along with the above slide are as follows:

Starting with Oracle Database 10.1 the query optimizer uses system statistics by default.  These statistics tell the optimizer characteristics about the server’s performance.  If you do not collect system statistics, Oracle will automatically use standardized system statistics, which are called NOWORKLOAD statistics.  When your server is under a typical to heavy load, you should gather the system statistics, which may be accomplished using a SQL*Plus command similar to the command at the top of this slide – this command will collect the statistics for a 60 minute time period and then set the statistic values. You can check the current system statistics using the SQL statement at the left.  If you see statistics like those at the right, that means your database is still using NOWORKLOAD statistics.

There is a bug in Oracle Database 11.2.0.1 and 11.2.0.2 related to the calculation of the SREADTIM – the average single-block read time expressed in milliseconds, and the MREADTIM – the average multi-block read time expressed in milliseconds.

The notes that go along with the above slide are as follows:

This slide shows an example of the statistics collection bug in Oracle Database 11.2.0.1 and Oracle Database 11.2.0.2.  Notice the large values for SREADTIM and MREADTIM.  These values are typically in the range of 4 to 12 milliseconds, and would normally appear as one or two digit values.  Watch out for cases where the SREADTIM value is greater than the MREADTIM value – that is almost certainly an error in the statistics collecting process and could be due to SAN read-ahead caching.

The calculated value of 78 for the MBRC, which is the average number of blocks read from disk during multi-block reads, might be a little high.  A high value for this parameter could cause the optimizer to use full table scans a little too frequently, and could lead to excessive CPU consumption problems – an example of this problem appears when Visual no longer uses the index on the PART_ID column when querying the INVENTORY_TRANS table when the PART_ID column is specified in the WHERE clause of a SQL statement.

The notes that go along with the above slide are as follows:

If you find that the System statistics were collected incorrectly, or if you need to set the system statistics of a test server to match the collected statistics of the production server, you can manually set the statistic values as shown at the top of this slide.

The notes that go along with the above slide are as follows:

It is important to make certain that the fixed object statistics are collected.  If the statistics are not collected, there is a strong chance that some queries of Oracle Database views will be slow, or even result in the crashing of the user’s session – see the link at the bottom of this slide for an example that shows what might happen if these statistics are not collected.  You can verify that the statistics were collected by executing the SQL statement at the left – if the SQL statement returns no rows, then the statistics have not been collected.

The notes that go along with the above slide are as follows:

Once you have collected the fixed object statistics, the query at the left will return output like you see on the right side of this slide.

The notes that go along with the above slide are as follows:

You should collect statistics weekly for the objects in the SYSADM schema (where the ERP data is located), typically when the database experiences little activity.  You can collect those statistics using the SQL*Plus command shown at the top of this slide.  Starting with Oracle Database 10.1 it is also a good idea to collect statistics for the objects in the SYS schema, and that can be done using the second command. 

Starting with Oracle Database 10.1 a stale statistics collecting process collects missing and out of date statistics for objects, typically starting around 10PM.  Even though the statistics collection is now partially automated in recent Oracle Database releases, it is still a good practice to collect statistics weekly.  You can verify that statistics were recently collected using the SQL statements at the bottom of this slide.

The notes that go along with the above slide are as follows:

I have been mentioning this warning for the last couple of years in the ERP mailing list – do not use the Update Database Statistics menu item in Visual to collect statistics.  Even in Visual 7.0, this menu command issues ANALYZE commands to collect the statistics, rather than the DBMS_STATS package which became the correct approach when Oracle 8i was introduced a decade ago.  The ANALYZE command does not collect all of the statistics needed by the Oracle query optimizer, and collects some statistics that potentially cause problems for the query optimizer.  You can execute the SQL statement shown on this slide to see if any tables in the SYSADM schema contain remnants from ANALYZE that need to be fixed.  If the query returns any rows, execute the SQL statements that are returned.

—–

Any comments about the above?  How would you do things differently?








Follow

Get every new post delivered to your Inbox.

Join 141 other followers