Striving for Optimal Performance

When I started writing the series of posts about Exadata Storage Server and the query optimizer, I didn’t expect to write more than three posts (part 1, part 2, part 3). Of course, things change. Hence, here is part 4 to cover a couple of things that I learned in the next couple of months.

In part 2 I pointed out that Oracle Database is not able to offload the processing of all datetime functions. This fact, to my surprise, was also referenced by Netezza in a recent paper entitled Oracle Exadata and Netezza TwinFin Compared. The essential thing to understand is that this limitation is due to bug 9682721. The fix is expected to be part of 11.2.0.2. According to my test cases (that Greg Rahn was so kind to execute against an early release of 11.2.0.2), offloading works correctly for all datetime functions but for the following three predicates.

• months_between(d,sysdate) = 0
• months_between(d,current_date) = 0
• months_between(d,to_date(‘01-01-2010′,’DD-MM-YYYY’)) = 0

Note that the MONTHS_BETWEEN function can basically be offloaded. The problem in these cases is that the offloading does not work when, for example, SYSDATE is used as parameter.

To have a full list of the functions supporting offloading, the “official reference” is available through the V$SQLFN_METADATA view. Here is a simple query to summarize the current situation.

SQL> SELECT offloadable, count(DISTINCT name)
  2  FROM v$sqlfn_metadata
  3  GROUP BY offloadable;

OFF COUNT(DISTINCTNAME)
--- -------------------
NO                  511
YES                 319

Another thing I would like to point out about offloading is that the feature can be controlled through the CELL_OFFLOAD_PROCESSING initialization parameter. By default it is set to TRUE and, therefore, offloading is used whenever possible. It goes without saying that offloading is disabled when it is set to FALSE. Note that it can not only be set at the instance and session level, but also at the statement level. The following example illustrate this (notice that only the first query uses offloading).

SQL> ALTER SESSION SET cell_offload_plan_display = always;

SQL> ALTER SESSION SET cell_offload_processing = true;

SQL> EXPLAIN PLAN FOR SELECT * FROM t WHERE id = 101;

SQL> SELECT * FROM table(dbms_xplan.display(format=>'basic +predicate'));

PLAN_TABLE_OUTPUT
---------------------------------------------------
Plan hash value: 3557914527

-------------------------------------------
| Id  | Operation                  | Name |
-------------------------------------------
|   0 | SELECT STATEMENT           |      |
|   1 |  PARTITION RANGE ALL       |      |
|*  2 |   TABLE ACCESS STORAGE FULL| T    |
-------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - storage("ID"=101)
       filter("ID"=101)

SQL> EXPLAIN PLAN FOR SELECT /*+ opt_param('cell_offload_processing' 'false') */  * FROM t WHERE id = 101;

SQL> SELECT * FROM table(dbms_xplan.display(format=>'basic +predicate'));

PLAN_TABLE_OUTPUT
---------------------------------------------------
Plan hash value: 3557914527

-------------------------------------------
| Id  | Operation                  | Name |
-------------------------------------------
|   0 | SELECT STATEMENT           |      |
|   1 |  PARTITION RANGE ALL       |      |
|*  2 |   TABLE ACCESS STORAGE FULL| T    |
-------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("ID"=101)

Another initialization parameter that controls offloading is CELL_OFFLOAD_DECRYPTION. This parameter is relevant for encrypted tablespaces only. With it you can specify whether the keys necessary to decrypt the data can be shipped to the cells. By default it is set to TRUE and, therefore, the keys are shipped. For security reasons you might want to set it to FALSE and disable offloading for encrypted tablespaces. Note that this parameter can only be changed at the instance level.

The aim of this post is to describe a strange (buggy) situation that I observed recently. But before doing that, I shortly summarize what a parent cursor and a child cursor are as well as when they can be shared. By the way, I borrowed this description from the pages 20/21 of my book. Hence, if you are interested in more information about this topic refer to it…

The result of a parse operation is a parent cursor and a child cursor stored in the library cache.

The key information related to a parent cursor is the text of the SQL statement. Therefore, several SQL statements share the same parent cursor if their text is exactly the same (note that there is at least an exception to this, specifically when cursor sharing is used). In the following example, four SQL statements are executed. Two have the same text. Two others differ only because of lowercase and uppercase letters or blanks. Through the V$SQLAREA view, it is possible to confirm that three distinct parent cursors were created.

SQL> ALTER SYSTEM FLUSH SHARED_POOL;

SQL> SELECT * FROM t WHERE n = 1234;

SQL> select * from t where n = 1234;

SQL> SELECT * FROM t WHERE n=1234;

SQL> SELECT * FROM t WHERE n = 1234;

SQL> SELECT sql_id, sql_text, executions
  2  FROM v$sqlarea
  3  WHERE sql_text LIKE '%1234';

SQL_ID        SQL_TEXT                          EXECUTIONS
------------- --------------------------------- ----------
2254m1487jg50 select * from t where n = 1234             1
g9y3jtp6ru4cb SELECT * FROM t WHERE n = 1234             2
7n8p5s2udfdsn SELECT * FROM t WHERE n=1234               1

The key information related to a child cursor is the execution plan and the execution environment related to it. The execution environment is important because if it changes, the execution plan might change as well. As a result, several SQL statements are able to share the same child cursor only if they share the same parent cursor and their execution environments are compatible. To illustrate, the same SQL statement is executed with two different values of the initialization OPTIMIZER_MODE parameter. The result is that a single parent cursor and two child cursors are created.

SQL> ALTER SESSION SET optimizer_mode = all_rows;

SQL> SELECT count(*) FROM t;

COUNT(*)
----------
      1000

SQL> ALTER SESSION SET optimizer_mode = first_rows_10;

SQL> SELECT count(*) FROM t;

COUNT(*)
----------
      1000

SQL> SELECT sql_id, child_number, sql_text, optimizer_mode, plan_hash_value
  2  FROM v$sql
  3  WHERE sql_id = (SELECT prev_sql_id
  4  FROM v$session
  5  WHERE sid = sys_context('userenv','sid'));

SQL_ID        CHILD_NUMBER SQL_TEXT               OPTIMIZER_MODE PLAN_HASH_VALUE
------------- ------------ ---------------------- -------------- ---------------
5tjqf7sx5dzmj            0 SELECT count(*) FROM t ALL_ROWS            2966233522
5tjqf7sx5dzmj            1 SELECT count(*) FROM t FIRST_ROWS          2966233522

To know which mismatch led to several child cursors, you can query the V$SQL_SHARED_CURSOR view.

SQL> SELECT child_number, optimizer_mode_mismatch
  2  FROM v$sql_shared_cursor
  3  WHERE sql_id = '5tjqf7sx5dzmj';

CHILD_NUMBER OPTIMIZER_MODE_MISMATCH
------------ -----------------------
           0 N
           1 Y

So far, so good… Now, let’s see what’s strange…

The interesting thing to point out about the previous example is that while I set FIRST_ROWS_10 as optimizer mode, the V$SQL view displayed the value FIRST_ROWS. Mhmm… That’s strange… They are two different optimizer modes. They cannot be considered equivalent. What are the implications? It is just the view that provides the wrong information or the database engine is able to share the same child cursor even with two different values of the OPTIMIZER_MODE parameter? Let’s try it with FIRST_ROWS (i.e. without “_10”)…

 SQL> ALTER SESSION SET optimizer_mode = first_rows;

SQL> SELECT sql_id, child_number, sql_text, optimizer_mode, executions
  2  FROM v$sql
  3  WHERE sql_id = (SELECT prev_sql_id
  4                  FROM v$session
  5                  WHERE sid = sys_context('userenv','sid'));

SQL_ID        CHILD_NUMBER SQL_TEXT                          OPTIMIZER_MODE EXECUTIONS
------------- ------------ --------------------------------- -------------- ----------
5tjqf7sx5dzmj            0 SELECT count(*) FROM t            ALL_ROWS                1
5tjqf7sx5dzmj            1 SELECT count(*) FROM t            FIRST_ROWS              2

Oh, damn! Even though the OPTIMIZER MODE is set to a different value the same child cursor is used. Since in this particular situation the execution plans associated to both child cursors are the same (their hash value are equal), it’s not a real problem. But, in practice, it might be possible that two different optimizer modes lead to different execution plans. The following example illustrates this.

• Build a table for the test:

SQL> CREATE TABLE t AS
  2  SELECT rownum AS id, rpad('*',500,'*') AS pad
  3  FROM dual
  4  CONNECT BY level <= 1000;

SQL> CREATE UNIQUE INDEX i ON t (id);

SQL> execute dbms_stats.gather_table_stats(user, 'T')

• Show that different values of the OPTIMIZER_MODE parameter lead to different execution plans:

SQL> ALTER SESSION SET optimizer_mode = FIRST_ROWS_1;

SQL> EXPLAIN PLAN FOR SELECT * FROM t WHERE id <= 500;

SQL> SELECT * FROM table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
Plan hash value: 242607798

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

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"<=500)

SQL> ALTER SESSION SET optimizer_mode = FIRST_ROWS_1000;

SQL> EXPLAIN PLAN FOR SELECT * FROM t WHERE id <= 500;

SQL> SELECT * FROM table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   500 |   246K|    10   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |   500 |   246K|    10   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ID"<=500)

• Execute the test query with both values of the OPTIMIZER_MODE parameter:

SQL> ALTER SYSTEM FLUSH SHARED_POOL;

SQL> ALTER SESSION SET optimizer_mode = FIRST_ROWS_1;

SQL> SELECT * FROM t WHERE id <= 500;

        ID PAD
---------- ----------
         1 **********
         2 **********
…
       499 **********
       500 **********

SQL> ALTER SESSION SET optimizer_mode = FIRST_ROWS_1000;

SQL> SELECT * FROM t WHERE id <= 500;

        ID PAD
---------- ----------
         1 **********
         2 **********
…
       499 **********
       500 **********

• Show that a single execution plan was used for both executions:

SQL> SELECT * FROM table(dbms_xplan.display_cursor(NULL,NULL));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
SQL_ID  2vw03p929jzgz, child number 0
-------------------------------------
SELECT * FROM t WHERE id <= 500

Plan hash value: 242607798

------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |       |       |     3 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T    |     3 |  1515 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | I    |       |       |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"<=500)

SQL> SELECT sql_id, child_number, executions, optimizer_mode
  2  FROM v$sql
  3  WHERE sql_id = '2vw03p929jzgz';

SQL_ID        CHILD_NUMBER EXECUTIONS OPTIMIZER_MODE
------------- ------------ ---------- --------------
2vw03p929jzgz            0          2 FIRST_ROWS

Even though it is not very likely that this bug (yes, in my opinion something like this cannot be considered a restriction of the implementation…) has an impact on a production system, I really don’t understand why the developers didn’t implement it correctly. It should not be that difficult to manage a byte containing the information about the used optimizer mode! Note that this is not the only case where something like that happens with the first rows optimizer mode. For example, also in a trace file generated through SQL trace no difference is made between the old and the new first row optimizer. So, it seams that they really got it wrong.

According to the documentation the GET_COMPRESSION_RATIO procedure of the DBMS_COMPRESSION package can be used to assess the impact of different compression options for a given table. In other words, it allows us to find out the expected compression ratio for a given set of data without having to really create a compressed table. The question is: how good are the values it returns?

Before answering this question it is essential to point out two things. First, this package is available as of 11.2 only. Second, it can be used to find out (and compare) the expected compression ratio of OLTP compression as well as Exadata Hybrid Columnar Compression (EHCC). Note that for getting values about EHCC it is not required to actually have access to an Exadata Storage Server. How is that possible?

To answer this second question, let’s describe how such an analysis is performed…

The following PL/SQL block shows how to start an analysis for all uncompressed tables of the current user.

DECLARE
  l_blkcnt_cmp       BINARY_INTEGER;
  l_blkcnt_uncmp     BINARY_INTEGER;
  l_row_cmp          BINARY_INTEGER;
  l_row_uncmp        BINARY_INTEGER;
  l_cmp_ratio        NUMBER;
  l_comptype_str     VARCHAR2(100);
BEGIN
  FOR i IN (SELECT table_name
            FROM user_tables
            WHERE compression = 'DISABLED'
            ORDER BY table_name)
  LOOP
    FOR j IN 1..5
    LOOP
      dbms_compression.get_compression_ratio(
        -- input parameters
        scratchtbsname   => 'SCRATCH',       -- scratch tablespace
        ownname          => user,            -- owner of the table
        tabname          => i.table_name,    -- table name
        partname         => NULL,            -- partition name
        comptype         => power(2,j),      -- compression algorithm
        -- output parameters
        blkcnt_cmp       => l_blkcnt_cmp,    -- number of compressed blocks
        blkcnt_uncmp     => l_blkcnt_uncmp,  -- number of uncompressed blocks
        row_cmp          => l_row_cmp,       -- number of rows in a compressed block
        row_uncmp        => l_row_uncmp,     -- number of rows in an uncompressed block
        cmp_ratio        => l_cmp_ratio,     -- compression ratio
        comptype_str     => l_comptype_str   -- compression type
      );
      dbms_output.put_line(i.table_name||' - '||'type: '||l_comptype_str||' ratio: '||to_char(l_cmp_ratio,'99.999'));
    END LOOP;
  END LOOP;
END;

As you can see the idea is that we provide to the package a table and, based on its data, the package estimates the compression ratio that can be achieved with the specified algorithm. Notice that as input parameter a scratch tablespace is also specified. This is necessary because the package, to estimate the output parameters, actually creates a compressed table. If you trace an execution you would see two CREATE TABLE statements.

• The first one creates a scratch table based on a sample of the content of the input table (the sampling percentage is chosen based on the size of the table; simply put the sampling percentage is inversely proportional to the table size):

CREATE TABLE dbms_tabcomp_temp_uncmp
TABLESPACE <tablespace name>
AS
SELECT * FROM <table name> SAMPLE BLOCK( <sampling percentage> )

• The second one creates another scratch table where the data is compressed according to the specified compression algorithm (in this case COMPRESS FOR QUERY HIGH):

CREATE TABLE dbms_tabcomp_temp_uncmp
ORGANIZATION HEAP
TABLESPACE <tablespace name>
COMPRESS FOR QUERY HIGH
AS
SELECT * FROM dbms_tabcomp_temp_uncmp

In other words the estimations are based on data that is actually compressed. Not on some heuristics…

The essential thing to note is that the second CREATE TABLE statement cannot be directly executed without an Exadata Storage Server. In fact, if you try to run it, the database engine raises an ORA-64307 (hybrid columnar compression is only supported in tablespaces residing on Exadata storage). Based on that observation it is sensible to say that in the current implementation there is a software lock that prevents us from using EHCC without an Exadata Storage Server. But, as just described, that lock can be overridden by the DBMS_COMPRESSION package.

Now, back to the initial question: how good are the values it returns?

To answer this question I installed a TPC-H schema (scale factor 10) and compared the estimations of the package with the actual values. For that purpose I created one table with each type of compression.

• The compression ratios based on the BLOCKS column of the USER_TABLES view are the following:

SQL> SELECT comp.table_name, round(uncomp.blocks/comp.blocks,3) AS ratio
  2  FROM user_tables comp, user_tables uncomp
  3  WHERE comp.compression = 'ENABLED'
  4  AND uncomp.compression = 'DISABLED'
  5  AND comp.table_name LIKE uncomp.table_name||'/_%' ESCAPE '/'
  6  ORDER BY uncomp.table_name, nullif(comp.compress_for,'OLTP') DESC;

TABLE_NAME                          RATIO
------------------------------ ----------
CUSTOMER_OLTP                       1.041
CUSTOMER_QUERY_LOW                  2.220
CUSTOMER_QUERY_HIGH                 3.708
CUSTOMER_ARCHIVE_LOW                3.837
CUSTOMER_ARCHIVE_HIGH               4.432
LINEITEM_OLTP                       1.487
LINEITEM_QUERY_LOW                  3.034
LINEITEM_QUERY_HIGH                 4.912
LINEITEM_ARCHIVE_LOW                5.144
LINEITEM_ARCHIVE_HIGH               6.704
ORDERS_OLTP                         1.149
ORDERS_QUERY_LOW                    2.887
ORDERS_QUERY_HIGH                   5.038
ORDERS_ARCHIVE_LOW                  5.305
ORDERS_ARCHIVE_HIGH                 6.704
PART_OLTP                           1.328
PART_QUERY_LOW                      3.307
PART_QUERY_HIGH                     6.718
PART_ARCHIVE_LOW                    7.296
PART_ARCHIVE_HIGH                  11.049
PARTSUPP_OLTP                       0.991
PARTSUPP_QUERY_LOW                  2.892
PARTSUPP_QUERY_HIGH                 5.428
PARTSUPP_ARCHIVE_LOW                5.659
PARTSUPP_ARCHIVE_HIGH               8.071

• The output of the PL/SQL block shown above is the following (be careful that the order is slightly different):

CUSTOMER - type: "Compress For OLTP" ratio:   1.041
CUSTOMER - type: "Compress For Query High" ratio:   3.742
CUSTOMER - type: "Compress For Query Low" ratio:   2.228
CUSTOMER - type: "Compress For Archive High" ratio:   4.460
CUSTOMER - type: "Compress For Archive Low" ratio:   3.894
ORDERS - type: "Compress For OLTP" ratio:   1.149
ORDERS - type: "Compress For Query High" ratio:   5.048
ORDERS - type: "Compress For Query Low" ratio:   2.886
ORDERS - type: "Compress For Archive High" ratio:   6.651
ORDERS - type: "Compress For Archive Low" ratio:   5.325
PART - type: "Compress For OLTP" ratio:   1.328
PART - type: "Compress For Query High" ratio:   6.778
PART - type: "Compress For Query Low" ratio:   3.338
PART - type: "Compress For Archive High" ratio:  11.126
PART - type: "Compress For Archive Low" ratio:   7.343
PARTSUPP - type: "Compress For OLTP" ratio:    .990
PARTSUPP - type: "Compress For Query High" ratio:   5.451
PARTSUPP - type: "Compress For Query Low" ratio:   2.893
PARTSUPP - type: "Compress For Archive High" ratio:   8.051
PARTSUPP - type: "Compress For Archive Low" ratio:   5.657

By comparing the estimations with the actual values it is possible to say that the value returned by the DBMS_COMPRESSION package are very accurate. Having so accurate estimations is a good thing because you probably want to know how much space you can save before doing a major reorganization of large tables as well as before buying an Exadata Storage Server just to test how effective the compression of EHCC is.

You might ask why the second output does not provide information about the LINEITEM table. The problem is that the package was not able to process it. In fact, the following error was raised:

ORA-00942: table or view does not exist
ORA-06512: at "SYS.PRVT_COMPRESSION", line 459
ORA-30562: SAMPLE percentage must be in the range [0.000001,100)
ORA-06512: at "SYS.DBMS_COMPRESSION", line 214
ORA-06512: at line 16

The problem is (probably) due to a rounding performed during the selection of the sampling percentage. In fact, for the LINEITEM table (which is the bigger one), the following CREATE TABLE statement was generated (notice that the sampling percentage is set to 0):

CREATE TABLE dbms_tabcomp_temp_uncmp
TABLESPACE scratch
AS
SELECT *
FROM lineitem SAMPLE BLOCK( 0)

I was not able to find a bug in MOS, but it is definitely one.

Today’s post is dedicated to the Metalink SR identified by the number 6468252.994. I know, this number says nothing to you. For me, however, it’s a very well known number. The reason is quite simple… Even if I open this SR almost two years ago (to be precise, September 5, 2007), it was closed few days ago. By far the most long-lasting SR I even experienced.

Let me explain why I opened it.

When assessing execution plans I like to use DBMS_XPLAN or, when necessary, to directly look at views like V$SQL_PLAN_STATISTICS and V$SQL_PLAN_STATISTICS_ALL. I like them because they provide a lot of information about what’s going on. In other words, they help me avoiding guesswork as much as possible. One of the most interesting information they provide is the number of rows returned by a given operation. DBMS_XPLAN provides this information in the column “A-Rows”. Be careful to not confuse it with the columns “Rows” and “E-Rows”. While “A-Rows” shows the actual number of rows, the other two shows the estimated number of rows. So far, so good.

What I don’t like about the column “A-Rows” (or the underlying columns LAST_OUTPUT_ROWS in the V$ views), is that for the operations modifying a table 0 is shown. By the way, according to the documentation it is not a bug. In my book I point out this behavior at page 233. For example, as the following SQL statements show, even if 14 rows are modified the value of “A-Rows” for the operation UPDATE (Id=1) is 0:

SQL> UPDATE /*+ gather_plan_statistics */ scott.emp SET sal = sal * 1.15;

14 rows updated.

SQL> SELECT * FROM table(dbms_xplan.display_cursor(format=>'iostats last'));

SQL_ID  4cs72g2hp6j67, child number 0
-------------------------------------
UPDATE /*+ gather_plan_statistics */ scott.emp SET sal = sal * 1.15

Plan hash value: 1494045816

-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |      |      1 |        |      0 |00:00:00.01 |      25 |
|   1 |  UPDATE            | EMP  |      1 |        |      0 |00:00:00.01 |      25 |
|   2 |   TABLE ACCESS FULL| EMP  |      1 |     14 |     14 |00:00:00.01 |       7 |
-------------------------------------------------------------------------------------

Back to the SR. What I tried to do with the SR is to convince Oracle that it would be much better to have the information about the number of modified rows. Unfortunately, they confirmed that the current behavior is the best one. My guess is that doing such a modification is not trivial and, therefore, they decided not doing it. In fact, also the SQL trace files have a similar behavior. For example, via SQL trace, the execution plan for the UPDATE statement shown before is the following:

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  UPDATE  EMP (cr=7 pr=0 pw=0 time=0 us)
     14   TABLE ACCESS FULL EMP (cr=7 pr=0 pw=0 time=0 us cost=3 size=56 card=14)

It is also interesting to note that while analyzing the code (probably for checking whether the change I proposed was doable) a developer discovered a bug. Based on the information I received (see bug number 6410147) it seems that in some situation the value of “A-Rows” is different than 0. But, since it is a bug, they fixed it.

Update 2009-08-03: part 2 of this post is available here.

As you can read in the documentation, the columns MAX_TEMPSEG_SIZE and LAST_TEMPSEG_SIZE in the dynamic performance view V$SQL_WORKAREA provide information about the size of the temporary segment used for a specific workarea. The values are given in bytes. Let’s perform a test to check this information…

• Create a test table that contains about 1MB of data:

SQL> CREATE TABLE t AS
  2  SELECT rownum AS id, dbms_random.string('p',1000) AS pad
  3  FROM dual
  4  CONNECT BY level <= 1000;

SQL> execute dbms_stats.gather_table_stats(user, 't')

• Setup the session to force the user process to spill into a temporary segment:

SQL> ALTER SESSION SET workarea_size_policy = manual;
SQL> ALTER SESSION SET sort_area_size = 524288;

• Run test query including a sort operation (that spills to the temporary tablespace):

SQL> SELECT id FROM t ORDER BY pad;

• Check the amount of used temporary space by querying V$SQL_WORKAREA:

SQL> SELECT max_tempseg_size, last_tempseg_size
  2  FROM v$sql_workarea
  3  WHERE (sql_id, child_number) IN (SELECT prev_sql_id, prev_child_number
  4                                   FROM v$session
  5                                   WHERE sid = sys_context('userenv','sid'));

MAX_TEMPSEG_SIZE LAST_TEMPSEG_SIZE
---------------- -----------------
         2097152           2097152

According to this information the size of the temporary space used to execute the query was 2MB. So far, so good.

Always according to the documentation another dynamic performance view, V$SQL_PLAN_STATISTICS_ALL, should provide the same information (remember, V$SQL_PLAN_STATISTICS_ALL shows in a single view all the information provided by the views V$SQL_PLAN, V$SQL_PLAN_STATISTICS, and V$SQL_WORKAREA). Let’s check it…

• Run the same test query as before:

SQL> SELECT id FROM t ORDER BY pad;

• Check the amount of used memory by querying V$SQL_PLAN_STATISTICS_ALL:

SQL> SELECT max_tempseg_size, last_tempseg_size
  2  FROM v$sql_plan_statistics_all
  3  WHERE (sql_id, child_number) IN (SELECT prev_sql_id, prev_child_number
  4                                   FROM v$session
  5                                   WHERE sid = sys_context('userenv','sid'))
  6  AND max_tempseg_size IS NOT NULL;

MAX_TEMPSEG_SIZE LAST_TEMPSEG_SIZE
---------------- -----------------
            2048              2048

Ups! According to this information the size of the temporary space used to execute the query was 2KB. Mhmm, something is not good… For this reason, at the end of 2007 I opened a service request about this issue. The support guy recognized the problem and opened a bug. Fine. For some unknown reasons (?) yesterday I was checking the status of few bugs. While doing so I noticed that this specific bug was closed few months ago with the status “Could Not Reproduce”! I don’t know you, but on my 64-bit Linux server I can reproduce it with at least 11.1.0.7.0, 11.1.0.6.0, 10.2.0.4.0, 10.2.0.3.0, 10.2.0.2.0, 10.2.0.1.0, 10.1.0.5.0, 10.1.0.4.0 and 10.1.0.3.0. Geez!

It is essential to note that also the package DBMS_XPLAN shows wrong information (here an example for the same query as before):

SQL> SELECT * FROM table(dbms_xplan.display_cursor(null,null,'memstats last'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
SQL_ID  ftb71b6926dtn, child number 0
-------------------------------------
SELECT id FROM t ORDER BY pad

Plan hash value: 961378228

---------------------------------------------------------------------------------
| Id  | Operation          | Name | E-Rows |  OMem |  1Mem | Used-Mem | Used-Tmp|
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |        |       |       |          |         |
|   1 |  SORT ORDER BY     |      |   1000 |  1152K|   562K|  529K (1)|    2048 |
|   2 |   TABLE ACCESS FULL| T    |   1000 |       |       |          |         |
---------------------------------------------------------------------------------

The only good thing about the fact that Oracle is not willing to fix the bug is that my book, Troubleshooting Oracle Performance, does not need to be updated. In fact, at page 210, while describing the output of the package DBMS_XPLAN I wrote the following information:

• Used-Tmp: The amount of temporary space used by the operation during the last execution. This value must be multiplied by 1,024 to be consistent with the other memory utilization columns (for example, 32K means 32MB).
• Max-Tmp: The maximum amount of temporary space used by the operation. This value has to be multiplied by 1,024 to be consistent with the other memory utilization columns (for example, 32K means 32MB).

ADDENDA (Mai 6, 2009): This post was noticed by an Oracle employee and, as a result, the bug was reopened. Thank you Greg!