## SQL – RANK, MAX Analytical Functions, DECODE, SIGN

26 12 2009

December 26, 2009

A couple years ago the following question appeared on the comp.databases.oracle.misc Usenet group:

Table Structure:

```Table1
symbol  orders  ttime```

Requirement: Want to arrange all records, symbolwise, based on orders (asc order).
Among that, if a particular symbol have records in the range TTIME BETWEEN 9300 AND 1530, then I want to extract MIN(TTIME) within that range else MIN(TTIME) of whatever available records.

I want to achieve this using a single query.

Example:

```Table1
symbol  orders  ttime
A       2       9300
A       2       9450
A       2       1030
A       2       9451
A       2       1530
A       2       1600
A       2       1700
B       3       9300
B       4       1600
C       3       1600```

I want to get all records with maximum orders (in desc order) for each symbol.

```Output:
Symbol  Orders  ttime
A       2       9300
A       2       9450
A       2       9451
A       2       1030
A       2       1530
A       2       1600
A       1       9300
A       2       1700
B       4       9300
B       4       1600
C       3       1600```

Out of this subset I want to get all records with ttime falling between 9450 to 1530 to appear first in asc. if there is no record within this range then I want to go for normal asc order on ttime.

```Ouput:
Symbol  Orders  ttime
A       2       9450
A       2       1030
A       2       1530
A       2       1600
A       2       9300
B       4       9450
B       4       1030
B       4       1600
C       3       1600```

Finally I want to extract only first record

```Final output:
A       2       9450
B       4       9300
C       3       1600```

Are we dealing with time here?  When is 9300 less than 1530?  Why is 1 included in the above?

The set up:

```CREATE TABLE T1 (
SYMBOL CHAR(1),
ORDERS NUMBER(10),
TTIME NUMBER(10));

INSERT INTO T1 VALUES('A',2,9300);
INSERT INTO T1 VALUES('A',2,9450);
INSERT INTO T1 VALUES('A',2,10300);
INSERT INTO T1 VALUES('A',2,9451);
INSERT INTO T1 VALUES('A',2,15300);
INSERT INTO T1 VALUES('A',2,16000);
INSERT INTO T1 VALUES('A',2,17000);
INSERT INTO T1 VALUES('B',3,9300);
INSERT INTO T1 VALUES('B',4,16000);
INSERT INTO T1 VALUES('C',3,16000);```

First, let’s find the maximum value for ORDERS for each SYMBOL:

```SELECT
SYMBOL,
MAX(ORDERS) OVER (PARTITION BY SYMBOL) ORDERS,
TTIME TTIME
FROM
T1;

SYMBOL ORDERS  TTIME
A           2   9300
A           2   9450
A           2  10300
A           2   9451
A           2  15300
A           2  17000
A           2  16000
B           4  16000
B           4   9300
C           3  16000```

You stated that if TTIME is between 9450 and 1530 (should it be 15300?), that it should take priority over other values of TTIME.  The DECODE below determines if TTIME is between 9450 and 15300, if so it assigns a value of 10 to EXTRA_SORT, otherwise it assigns a value of 1 to EXTRA_SORT:

```SELECT
SYMBOL,
MAX(ORDERS) OVER (PARTITION BY SYMBOL) ORDERS,
TTIME TTIME,
DECODE(SIGN(TTIME-9449.999),1,DECODE(SIGN(TTIME-15300.0001),-1,10,1),1) EXTRA_SORT
FROM
T1;

SYMBOL ORDERS  TTIME  EXTRA_SORT
A           2   9300           1
A           2   9450          10
A           2  10300          10
A           2   9451          10
A           2  15300          10
A           2  17000           1
A           2  16000           1
B           4  16000           1
B           4   9300           1
C           3  16000           1```

If we slide the above into an inline view, we can then rank the rows when sorted first on EXTRA_SORT and then on TTIME:

```SELECT
SYMBOL,
ORDERS,
TTIME,
RANK() OVER (PARTITION BY SYMBOL ORDER BY EXTRA_SORT DESC,TTIME) POSITION
FROM
(SELECT
SYMBOL,
MAX(ORDERS) OVER (PARTITION BY SYMBOL) ORDERS,
TTIME TTIME,
DECODE(SIGN(TTIME-9449.999),1,DECODE(SIGN(TTIME-15300.0001),-1,10,1),1) EXTRA_SORT
FROM
T1);

SYMBOL ORDERS  TTIME  POSITION
A           2   9450         1
A           2   9451         2
A           2  10300         3
A           2  15300         4
A           2   9300         5
A           2  16000         6
A           2  17000         7
B           4   9300         1
B           4  16000         2
C           3  16000         1```

We can again slide the above into an inline view and extract only those with a POSITION value of 1:

```SELECT
SYMBOL,
ORDERS,
TTIME
FROM
(SELECT
SYMBOL,
ORDERS,
TTIME,
RANK() OVER (PARTITION BY SYMBOL ORDER BY EXTRA_SORT DESC,TTIME)
POSITION
FROM
(SELECT
SYMBOL,
MAX(ORDERS) OVER (PARTITION BY SYMBOL) ORDERS,
TTIME TTIME,
DECODE(SIGN(TTIME-9449.999),1,DECODE(SIGN(TTIME-15300.0001),-1,10,1),1) EXTRA_SORT
FROM
T1)
)
WHERE
POSITION=1;

SYMBOL ORDERS  TTIME
A           2   9450
B           4   9300
C           3  16000```

## SQL – DENSE_RANK, PERCENT_RANK, and COUNT Analytical Functions

26 12 2009

December 26, 2009

A couple years ago the following question appeared on the comp.databases.oracle.misc Usenet group:

I have to following problem:

I get X rows from a statement, these are sorted by a certain column, let’s say a numerical value.
Now I want to calculate the average of this numerical value, but the 10% with the lowest and the 10% with the highest value shall not be included in this calculation. So for example, if I get 20 rows, I need the average of the value in rows 3 to 18.

Currently I solved this with a very complicated statement, but I don’t know the built-in Oracle mathematical functions so I hope that there could be a way to do this with a better performance.

Let’s set up a short experiment:

```CREATE TABLE T1 (C1 NUMBER(4));

INSERT INTO T1
SELECT
ROWNUM
FROM
DUAL
CONNECT BY
LEVEL<=20;```

We now have a table with 20 rows with numbers between 1 and 20.

Assuming that you are running a version of Oracle that supports analytical functions, the following returns the twenty rows with the relative ranking of each row, if the rows are sorted by C1 in descending order:

```SELECT
C1,
DENSE_RANK() OVER (ORDER BY C1 DESC) DR,
COUNT(C1) OVER (PARTITION BY 1) R
FROM
T1;

C1         DR          R
---------- ---------- ----------
20          1         20
19          2         20
18          3         20
17          4         20
16          5         20
15          6         20
14          7         20
13          8         20
12          9         20
11         10         20
10         11         20
9         12         20
8         13         20
7         14         20
6         15         20
5         16         20
4         17         20
3         18         20
2         19         20
1         20         20```

A slight modification of the above, dividing the value of DENSE_RANK by the value of COUNT, and also including a PERCENT_RANK for comparison:

```SELECT
C1,
DENSE_RANK() OVER (ORDER BY C1 DESC) DR,
(DENSE_RANK() OVER (ORDER BY C1 DESC))/(COUNT(C1) OVER (PARTITION BY 1)) DRP,
PERCENT_RANK() OVER (ORDER BY C1 DESC) PR
FROM
T1;

C1         DR        DRP         PR
---------- ---------- ---------- ----------
20          1        .05          0
19          2         .1 .052631579
18          3        .15 .105263158
17          4         .2 .157894737
16          5        .25 .210526316
15          6         .3 .263157895
14          7        .35 .315789474
13          8         .4 .368421053
12          9        .45 .421052632
11         10         .5 .473684211
10         11        .55 .526315789
9         12         .6 .578947368
8         13        .65 .631578947
7         14         .7 .684210526
6         15        .75 .736842105
5         16         .8 .789473684
4         17        .85 .842105263
3         18         .9 .894736842
2         19        .95 .947368421
1         20          1          1```

The final cleanup is performed when the above is slid into an inline view, by using a WHERE clause:

```SELECT
SUM(T.C1) S
FROM
(SELECT
C1,
DENSE_RANK() OVER (ORDER BY C1 DESC) DR,
(DENSE_RANK() OVER (ORDER BY C1 DESC))/(COUNT(C1) OVER (PARTITION BY 1)) DRP,
PERCENT_RANK() OVER (ORDER BY C1 DESC) PR
FROM
T1) T
WHERE
T.DRP>0.1
AND T.DRP<=0.9;```

S
———-
168

A version that uses the PERCENT_RANK value:

```SELECT
SUM(T.C1) S
FROM
(SELECT
C1,
DENSE_RANK() OVER (ORDER BY C1 DESC) DR,
(DENSE_RANK() OVER (ORDER BY C1 DESC))/(COUNT(C1) OVER (PARTITION BY 1)) DRP,
PERCENT_RANK() OVER (ORDER BY C1 DESC) PR
FROM
T1) T
WHERE
T.PR BETWEEN 0.1 AND 0.9;```

S
———-
168

## SQL – COUNT Analytical Function, GROUP BY, HAVING

26 12 2009

December 26, 2009

I have a book table and in that table it has the book tile, publisher, and type of book it is. example mystery, scifi, etc…

I am trying to write a query that brings back a list of every pair of books that have the same publisher and same book type.  I have been able to get the following code to work:

```select publisher_code, type
from book
group by publisher_code, type
having count(*) > 1;```

which returns the following results:

```PU TYP
-- ---
JP MYS
LB FIC
PE FIC
PL FIC
ST SFI
VB FIC```

I can not figure out how to get the book title and book code for the books that this result list represents, everything I have tried throws out an error.

My initial response follows:

I see two possible methods:

1.  Slide the SQL statement that you have written into an inline view, join the inline view to your book table, and then use the publisher_code, type columns to drive back into your book table.  The join syntax may look like one of the following: (publisher_code, type) IN (SELECT…)   or   b.publisher_code=ib.publisher_code and b.type=ib.type
2.  Use analytical functions (COUNT() OVER…) to determine the number of matches for the same publisher_code, type columns.  Then slide this SQL statement into an inline view to retrieve only those records with the aliased COUNT() OVER greater than 1.  This has the benefit of retrieving the matching rows in a single pass.

The original poster then attempted to create a query to meet the requirements, but the query generated an error:

```SQL> select title
2  from book
3  where publisher_code, type in
4  (select publisher_code, type
5  from book
6  group by publisher_code, type
7  having count(*) > 1);
where publisher_code, type in
*
ERROR at line 3:
ORA-00920: invalid relational operator```

My reponse continues:

Very close to what you need.  However, Oracle expects the column names to be wrapped in () … like this:  where (publisher_code, type) in

The above uses a subquery, which may perform slow on some Oracle releases compared to the use of an inline view.  Assume that I have a table named PART, which has columns ID, DESCRIPITION, PRODUCT_CODE, and COMMODITY_CODE, with ID as the primary key.  I want to find ID, DESCRIPTION, and COMMODITY_CODE for all parts with the same DESCRIPTION and PRODUCT_CODE, where there are at least 3 matching parts in the group:

The starting point, which looks similar to your initial query:

```SELECT
DESCRIPTION,
PRODUCT_CODE,
COUNT(*) NUM_MATCHES
FROM
PART
GROUP BY
DESCRIPTION,
PRODUCT_CODE
HAVING
COUNT(*)>=3;```

When the original query is slid into an inline view and joined to the original table, it looks like this:

```SELECT
P.ID,
P.DESCRIPTION,
P.COMMODITY_CODE
FROM
(SELECT
DESCRIPTION,
PRODUCT_CODE,
COUNT(*) NUM_MATCHES
FROM
PART
GROUP BY
DESCRIPTION,
PRODUCT_CODE
HAVING
COUNT(*)>=3) IP,
PART P
WHERE
IP.DESCRIPTION=P.DESCRIPTION
AND IP.PRODUCT_CODE=P.PRODUCT_CODE;```

Here is the DBMS_XPLAN:

```-------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------
|*  1 |  HASH JOIN            |      |      1 |   1768 |  11525 |00:00:00.21 |    2748 |  1048K|  1048K| 1293K (0)|
|   2 |   VIEW                |      |      1 |   1768 |   1156 |00:00:00.11 |    1319 |       |       |          |
|*  3 |    FILTER             |      |      1 |        |   1156 |00:00:00.11 |    1319 |       |       |          |
|   4 |     HASH GROUP BY     |      |      1 |   1768 |  23276 |00:00:00.08 |    1319 |       |       |          |
|   5 |      TABLE ACCESS FULL| PART |      1 |  35344 |  35344 |00:00:00.04 |    1319 |       |       |          |
|   6 |   TABLE ACCESS FULL   | PART |      1 |  35344 |  35344 |00:00:00.04 |    1429 |       |       |          |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("IP"."DESCRIPTION"="P"."DESCRIPTION" AND "IP"."PRODUCT_CODE"="P"."PRODUCT_CODE")
3 - filter(COUNT(*)>=3)```

The query format using the subquery looks like this:

```SELECT
P.ID,
P.DESCRIPTION,
P.COMMODITY_CODE
FROM
PART P
WHERE
(DESCRIPTION,PRODUCT_CODE) IN
(SELECT
DESCRIPTION,
PRODUCT_CODE
FROM
PART
GROUP BY
DESCRIPTION,
PRODUCT_CODE
HAVING
COUNT(*)>=3);```

The DBMS_XPLAN, note that Oracle 10.2.0.2 transformed the query above:

```-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------
|*  1 |  HASH JOIN RIGHT SEMI |          |      1 |      1 |  11525 |00:00:00.21 |    2748 |  1048K|  1048K| 1214K (0)|
|   2 |   VIEW                | VW_NSO_1 |      1 |   1768 |   1156 |00:00:00.12 |    1319 |       |       |          |
|*  3 |    FILTER             |          |      1 |        |   1156 |00:00:00.12 |    1319 |       |       |          |
|   4 |     HASH GROUP BY     |          |      1 |   1768 |  23276 |00:00:00.09 |    1319 |       |       |          |
|   5 |      TABLE ACCESS FULL| PART     |      1 |  35344 |  35344 |00:00:00.04 |    1319 |       |       |          |
|   6 |   TABLE ACCESS FULL   | PART     |      1 |  35344 |  35344 |00:00:00.01 |    1429 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("DESCRIPTION"="\$nso_col_1" AND "PRODUCT_CODE"="\$nso_col_2")
3 - filter(COUNT(*)>=3)```

Without allowing the automatic transformations in Oracle 10.2.0.2, the query takes _much_ longer than 0.21 seconds to complete.

The method using analytical functions starts like this:

```SELECT
P.ID,
P.DESCRIPTION,
P.COMMODITY_CODE,
COUNT(*) OVER (PARTITION BY DESCRIPTION, PRODUCT_CODE) NUM_MATCHES
FROM
PART P;```

Then, sliding the above into an inline view:

```SELECT
ID,
DESCRIPTION,
COMMODITY_CODE
FROM
(SELECT
P.ID,
P.DESCRIPTION,
P.COMMODITY_CODE,
COUNT(*) OVER (PARTITION BY DESCRIPTION, PRODUCT_CODE) NUM_MATCHES
FROM
PART P)
WHERE
NUM_MATCHES>=3;```

The DBMS_XPLAN for the above looks like this:

```-----------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------
|*  1 |  VIEW               |      |      1 |  35344 |  11525 |00:00:00.31 |    1319 |       |       |          |
|   2 |   WINDOW SORT       |      |      1 |  35344 |  35344 |00:00:00.27 |    1319 |  2533K|   726K| 2251K (0)|
|   3 |    TABLE ACCESS FULL| PART |      1 |  35344 |  35344 |00:00:00.04 |    1319 |       |       |          |
-----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("NUM_MATCHES">=3)

Note that there is only one TABLE ACCESS FULL of the PART table in the above.  The execution time required 0.31 seconds to complete, which is greater than the first two approaches, but that is because the database server is concurrently still trying to resolve the query method using the subquery with no permitted transformations (5+ minutes later).```

Subquery method with no transformations permitted:

```---------------------------------------------------------------------------------------
| Id  | Operation            | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------
|*  1 |  FILTER              |      |      1 |        |  11525 |00:46:21.46 |      38M|
|   2 |   TABLE ACCESS FULL  | PART |      1 |  35344 |  35344 |00:00:00.25 |    1429 |
|*  3 |   FILTER             |      |  29474 |        |   6143 |00:46:06.52 |      38M|
|   4 |    HASH GROUP BY     |      |  29474 |      1 |    613M|00:33:24.30 |      38M|
|   5 |     TABLE ACCESS FULL| PART |  29474 |  35344 |   1041M|00:00:02.54 |      38M|
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( IS NOT NULL)
3 - filter(("DESCRIPTION"=:B1 AND "PRODUCT_CODE"=:B2 AND COUNT(*)>=3))```

Maxim Demenko provided another possible solution for the problem experienced by the original poster.

## Proving that 1=2, is Oracle Wrong to Short-Circuit an Execution Plan?

25 12 2009

December 25, 2009

Earlier this month I wrote a blog article that showed how Oracle behaves when the WHERE clause includes the predicate 1=2.  But is the shortcut implemented by Oracle wrong?  Will 1=2 never happen?

I attended a college course that covered just mathematical proofs (the name of the class escapes me at the moment – Discrete Mathematics?).  The mathematics professor for that course proved that 1=2.  That proof follows (I attempted to align the equal sign to make the proof easier to read):

```Assumption: Let a = b
1.  a^2               = a^2
2.  a^2               = b^2
3.  a^2               = b * b
4.  a^2               = a * b
5.  a^2 - b^2         = a * b - b^2
6.  (a + b) * (a - b) = b * (a - b)
7.  a + b             = b
8.  b + b             = b
9.  2b                = b
10. 2 = 1```

```Mathematical Explanation of the Above Steps:
1.  Truth Statement
2.  Substitution
3.  Factor
4.  Substitution
5.  Subtract b^2 from Both Sides of the Equation
6.  Factor
7.  Divide Both Sides of the Equation by (a - b)
8.  Substitution
9.  Simplification
10. Divide Both Sides of the Equation by b```

I think that there is a lesson in the above that may be applied to the understanding of Oracle databases.  Let me ponder the lesson while you review the Faulty Quotes series of blog articles.

A couple references for mathematical proofs follow – how might the techniques of mathematical proofs be applied to understanding the logic built into Oracle Database?http://www.eiu.edu/~mathcs/mat2345/index/Webview/Slides/handout-Week02-2×3.pdf
http://www.math.harvard.edu/archive/23b_spring_05/proofs.pdf
http://en.wikibooks.org/wiki/Category:Mathematical_Proof
http://wapedia.mobi/en/Inductive_proof

## Miscellaneous Metalink Performance Articles 2

24 12 2009

12/24/2009

It appears that my previous post of this topic was well received (thanks Doug).  My search through Metalink in July/August found quite a number of performance related articles – many more than I expected to find on the support site.  Below are a couple more of those articles – some of these articles are offered as suggested further reading items for the topics in the chapters that I co-authored.

• Doc ID 179668.1 “TROUBLESHOOTING: Tuning Slow Running Queries”
• Doc ID 296377.1 “Handling and resolving unshared cursors/large version_counts”
• Doc ID 43214.1 “AUTOTRACE Option in sqlplus” – lists all of the various SQL*Plus options for controlling the output of expected execution plans for SQL statements, and the runtime statistics.
• Doc ID 215187.1 “SQLT (SQLTXPLAIN) – Enhanced Explain Plan and related diagnostic information for one SQL”
• Doc ID 235530.1 “Methods for Obtaining a Formatted Explain Plan” – shows several ways to retrieve execution plans, including DBMS_XPLAN.DISPLAY_CURSOR.
• Doc ID 41634.1 “TKProf Basic Overview” – quick reference guide for enabling a basic SQL trace, processing the trace file with TKPROF and understanding the contents of the resulting report.
• Doc ID 39817.1 “Interpreting Raw SQL_TRACE and DBMS_SUPPORT.START_TRACE output” – shows how to decode a raw 10046 extended SQL trace file, includes keywords added in 11g.
• Doc ID 779226.1 “Troubleshooting Oracle Net”
• Doc ID 219968.1 “SQL*Net, Net8, Oracle Net Services – Tracing and Logging at a Glance”
• Doc ID 216912.1 “How to Perform Client-Side Tracing of Programmatic Interfaces on Windows Platforms”
• Doc ID 438452.1 “Performance Tools Quick Reference Guide”
• Doc ID 394937.1 “Statistics Package (STATSPACK) Guide” – the 26 page reference for creating and understanding Statspack reports in 9.2.0.1 through 11.1.0.6.
• Doc ID 215858.1 “Interpreting HANGANALYZE trace files to diagnose hanging and performance problems”
• Doc ID 423153.1 “Understanding and Reading Systemstates” – shows how to read the contents of systemstate dumps .
• Doc ID 370363.1 “CASE STUDY: Using Real-Time Diagnostic Tools to Diagnose Intermittent Database Hangs”
• Doc ID 362791.1 “STACKX Core / Stack Trace Extraction Tool” – Unix/Linux utility that pulls the call stack information from core dump files.
• Doc ID 352363.1 “LTOM – The On-Board Monitor User Guide” – tracing in the database and in the operating system.
• Doc ID 301137.1 OS Watcher for Unix/Linux, Doc ID 433472.1 OS Watcher for Windows.
• Doc ID 377152.1 “Best Practices for automatic statistics collection on Oracle 10g”
• Doc ID 149560.1 “System Statistics: Collect and Display System Statistics (CPU and IO)”

## SORT_AREA_SIZE Affecting Sorts to Disk with PGA_AGGREGATE_TARGET Set 2?

23 12 2009

December 23, 2009

This is a follow-up to my post from a couple days ago (back to the previous post).

A small confession may be found below…

The good news is that I was able to reproduce the behavior on 64 bit Oracle 11.1.0.7 with an initial PGA_AGGREGATE_TARGET of 1.8GB.  Below is the test script that I created – it took about 2 hours for SQL*Plus to scroll all of the returned data up the screen, regardless of how quickly the DBMS_XPLAN outputs indicate that the SQL statements executed.

```SET PAGESIZE 2000
SET LINESIZE 140
SET ARRAYSIZE 100

SPOOL SortToDiskTest11.1.0.7-1.txt

DROP TABLE T1 PURGE;

CREATE TABLE T1 (
C1 VARCHAR2(20),
C2 NUMBER(12),
C3 NUMBER(12));

/* Fill the test table to 1,000,000 rows: */
DECLARE
X NUMBER;
BEGIN
FOR X IN 1 .. 1000000
LOOP
INSERT INTO T1 VALUES (
TO_CHAR(MOD( X ,100))||'-'||TO_CHAR(MOD( X ,100)),
MOD( X ,500),
X);
END LOOP;
END;
/

COMMIT;

/* Gather statistics on the table: */
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1');

/*+ Let's see where the sort statistics are right now: */
SELECT
NAME,
VALUE
FROM
V\$SYSSTAT
WHERE
UPPER(NAME) LIKE '%SORT%'
ORDER BY
NAME;

/*+ Let's turn on a 10053 trace so that we can see what the cost-based optimizer is willing to report: */
ALTER SESSION SET TRACEFILE_IDENTIFIER = 'Default_Opt';
ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1';

SELECT 'STARTING POINT' FROM DUAL;

SPOOL OFF

/*+ Run a simple SQL statement to generate a sort, and gather statistics for DBMS_XPLAN: */
SELECT /*+ GATHER_PLAN_STATISTICS */
*
FROM
T1
ORDER BY
C2,
C3;

SPOOL SortToDiskTest11.1.0.7-1.txt APPEND

/*+ Determine the DBMS_XPLAN for the query: */
SELECT
*
FROM
TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

/* Let's check the sort statistics: */
SELECT
NAME,
VALUE
FROM
V\$SYSSTAT
WHERE
UPPER(NAME) LIKE '%SORT%'
ORDER BY
NAME;

/* Let's look at the plan statistics for the SQL statement: */
SELECT
SP.LAST_OUTPUT_ROWS,
SP.TEMP_SPACE,
SP.ESTIMATED_OPTIMAL_SIZE,
SP.ESTIMATED_ONEPASS_SIZE,
SP.LAST_MEMORY_USED,
SP.LAST_EXECUTION,
SP.LAST_TEMPSEG_SIZE
FROM
V\$SQL S,
V\$SQL_PLAN_STATISTICS_ALL SP
WHERE
S.SQL_TEXT LIKE 'SELECT /*+ GATHER_PLAN_STATISTICS */%'
AND S.SQL_ID=SP.SQL_ID;

/* Let's try again, this time with a 200MB PGA_AGGREGATE_TARGET rather than a 150MB: */
ALTER SYSTEM SET PGA_AGGREGATE_TARGET=200M;
ALTER SYSTEM FLUSH SHARED_POOL;

SELECT 'PGA 200' FROM DUAL;

SPOOL OFF

SELECT /*+ GATHER_PLAN_STATISTICS */
*
FROM
T1
ORDER BY
C2,
C3;

SPOOL SortToDiskTest11.1.0.7-1.txt APPEND

SELECT
*
FROM
TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

/*+ Let's check the sort statistics: */
SELECT
NAME,
VALUE
FROM
V\$SYSSTAT
WHERE
UPPER(NAME) LIKE '%SORT%'
ORDER BY
NAME;

/*+ Let's look at the plan statistics for the SQL statement: */
SELECT
SP.LAST_OUTPUT_ROWS,
SP.TEMP_SPACE,
SP.ESTIMATED_OPTIMAL_SIZE,
SP.ESTIMATED_ONEPASS_SIZE,
SP.LAST_MEMORY_USED,
SP.LAST_EXECUTION,
SP.LAST_TEMPSEG_SIZE
FROM
V\$SQL S,
V\$SQL_PLAN_STATISTICS_ALL SP
WHERE
S.SQL_TEXT LIKE 'SELECT /*+ GATHER_PLAN_STATISTICS */%'
AND S.SQL_ID=SP.SQL_ID;

/* Since we are still sorting to disk, let's increase the PGA_AGGREGATE_TARGET again and repeat the test: */
ALTER SYSTEM SET PGA_AGGREGATE_TARGET=300M;
ALTER SYSTEM FLUSH SHARED_POOL;

SELECT 'PGA 300' FROM DUAL;

SPOOL OFF

SELECT /*+ GATHER_PLAN_STATISTICS */
*
FROM
T1
ORDER BY
C2,
C3;

SPOOL SortToDiskTest11.1.0.7-1.txt APPEND

SELECT
*
FROM
TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

SELECT
NAME,
VALUE
FROM
V\$SYSSTAT
WHERE
UPPER(NAME) LIKE '%SORT%'
ORDER BY
NAME;

/* Let's continue the test, dropping PGA_AGGREGATE_TARGET back to 200MB: */
ALTER SYSTEM SET PGA_AGGREGATE_TARGET=200M;
ALTER SYSTEM FLUSH SHARED_POOL;

SELECT 'PGA 200' FROM DUAL;

SPOOL OFF

SELECT /*+ GATHER_PLAN_STATISTICS */
*
FROM
T1
ORDER BY
C2,
C3;
SPOOL SortToDiskTest11.1.0.7-1.txt APPEND

SELECT
*
FROM
TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

SELECT
NAME,
VALUE
FROM
V\$SYSSTAT
WHERE
UPPER(NAME) LIKE '%SORT%'
ORDER BY
NAME;

SELECT
SP.LAST_OUTPUT_ROWS,
SP.TEMP_SPACE,
SP.ESTIMATED_OPTIMAL_SIZE,
SP.ESTIMATED_ONEPASS_SIZE,
SP.LAST_MEMORY_USED,
SP.LAST_EXECUTION,
SP.LAST_TEMPSEG_SIZE
FROM
V\$SQL S,
V\$SQL_PLAN_STATISTICS_ALL SP
WHERE
S.SQL_TEXT LIKE 'SELECT /*+ GATHER_PLAN_STATISTICS */%'
AND S.SQL_ID=SP.SQL_ID;

/* Let's drop the PGA_AGGREGATE_TARGET to 150MB and see if we have an optimal sort (no sort to disk): */
ALTER SYSTEM SET PGA_AGGREGATE_TARGET=150M;
ALTER SYSTEM FLUSH SHARED_POOL;

SELECT 'PGA 150' FROM DUAL;

SPOOL OFF

SELECT /*+ GATHER_PLAN_STATISTICS */
*
FROM
T1
ORDER BY
C2,
C3;

SPOOL SortToDiskTest11.1.0.7-1.txt APPEND

SELECT
*
FROM
TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

SELECT
NAME,
VALUE
FROM
V\$SYSSTAT
WHERE
UPPER(NAME) LIKE '%SORT%'
ORDER BY
NAME;

SELECT
SP.LAST_OUTPUT_ROWS,
SP.TEMP_SPACE,
SP.ESTIMATED_OPTIMAL_SIZE,
SP.ESTIMATED_ONEPASS_SIZE,
SP.LAST_MEMORY_USED,
SP.LAST_EXECUTION,
SP.LAST_TEMPSEG_SIZE
FROM
V\$SQL S,
V\$SQL_PLAN_STATISTICS_ALL SP
WHERE
S.SQL_TEXT LIKE 'SELECT /*+ GATHER_PLAN_STATISTICS */%'
AND S.SQL_ID=SP.SQL_ID;

/*+ Let's again increase the PGA_AGGREGATE_TARGET to 200MB and see if we have an optimal sort (no sort to disk): */
ALTER SYSTEM SET PGA_AGGREGATE_TARGET=200M;
ALTER SYSTEM FLUSH SHARED_POOL;

SELECT 'PGA 200' FROM DUAL;

SPOOL OFF

SELECT /*+ GATHER_PLAN_STATISTICS */
*
FROM
T1
ORDER BY
C2,
C3;

SPOOL SortToDiskTest11.1.0.7-1.txt APPEND

SELECT
*
FROM
TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

SELECT
NAME,
VALUE
FROM
V\$SYSSTAT
WHERE
UPPER(NAME) LIKE '%SORT%'
ORDER BY
NAME;

SELECT
SP.LAST_OUTPUT_ROWS,
SP.TEMP_SPACE,
SP.ESTIMATED_OPTIMAL_SIZE,
SP.ESTIMATED_ONEPASS_SIZE,
SP.LAST_MEMORY_USED,
SP.LAST_EXECUTION,
SP.LAST_TEMPSEG_SIZE
FROM
V\$SQL S,
V\$SQL_PLAN_STATISTICS_ALL SP
WHERE
S.SQL_TEXT LIKE 'SELECT /*+ GATHER_PLAN_STATISTICS */%'
AND S.SQL_ID=SP.SQL_ID;

/* Now, let's see if changing the SORT_AREA_SIZE helps remove the sort to disk: */
ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SESSION SET SORT_AREA_SIZE=41943040;
ALTER SESSION SET SORT_AREA_RETAINED_SIZE=41943040;

SPOOL OFF

SELECT /*+ GATHER_PLAN_STATISTICS */
*
FROM
T1
ORDER BY
C2,
C3;

SPOOL SortToDiskTest11.1.0.7-1.txt APPEND

SELECT
*
FROM
TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

SELECT
NAME,
VALUE
FROM
V\$SYSSTAT
WHERE
UPPER(NAME) LIKE '%SORT%'
ORDER BY
NAME;

SELECT
SP.LAST_OUTPUT_ROWS,
SP.TEMP_SPACE,
SP.ESTIMATED_OPTIMAL_SIZE,
SP.ESTIMATED_ONEPASS_SIZE,
SP.LAST_MEMORY_USED,
SP.LAST_EXECUTION,
SP.LAST_TEMPSEG_SIZE
FROM
V\$SQL S,
V\$SQL_PLAN_STATISTICS_ALL SP
WHERE
S.SQL_TEXT LIKE 'SELECT /*+ GATHER_PLAN_STATISTICS */%'
AND S.SQL_ID=SP.SQL_ID;

/* Now, let's try a similar test with optimizer_features_enable set to 10.2.0.3 */
ALTER SESSION SET TRACEFILE_IDENTIFIER = 'OFE_10.2.0.3';
ALTER SYSTEM SET optimizer_features_enable='10.2.0.3';

ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1';

SELECT 'OFE_10.2.0.3' FROM DUAL;

SPOOL OFF

/* Let's first dump a DBMS_XPLAN with the estimated statistics. */
SELECT /*+ GATHER_PLAN_STATISTICS */
*
FROM
T1
ORDER BY
C2,
C3;

SPOOL SortToDiskTest11.1.0.7-1.txt APPEND

SELECT
*
FROM
TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,NULL));

SPOOL OFF

/* Let's repeat, this time requesting the additional statistics from DBMS XPLAN: */
SELECT /*+ GATHER_PLAN_STATISTICS */
*
FROM
T1
ORDER BY
C2,
C3;

SPOOL SortToDiskTest11.1.0.7-1.txt APPEND

SELECT
*
FROM
TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

SELECT
NAME,
VALUE
FROM
V\$SYSSTAT
WHERE
UPPER(NAME) LIKE '%SORT%'
ORDER BY
NAME;

/* Repeat with pga_aggregate_target at 200MB: */
ALTER SYSTEM SET PGA_AGGREGATE_TARGET=200M;
ALTER SYSTEM FLUSH SHARED_POOL;

SELECT 'PGA 200' FROM DUAL;

SPOOL OFF

SELECT /*+ GATHER_PLAN_STATISTICS */
*
FROM
T1
ORDER BY
C2,
C3;

SPOOL SortToDiskTest11.1.0.7-1.txt APPEND

SELECT
*
FROM
TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

SELECT
NAME,
VALUE
FROM
V\$SYSSTAT
WHERE
UPPER(NAME) LIKE '%SORT%'
ORDER BY
NAME;

/* Repeat with pga_aggregate_target at 100MB: */
ALTER SYSTEM SET PGA_AGGREGATE_TARGET=100M;
ALTER SYSTEM FLUSH SHARED_POOL;

SELECT
NAME,
VALUE
FROM
V\$SYSSTAT
WHERE
UPPER(NAME) LIKE '%SORT%'
ORDER BY
NAME;

/* Let's try again, this time bumping the SORT_AREA_SIZE to roughly 40MB: */
ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SESSION SET SORT_AREA_SIZE=41943040;
ALTER SESSION SET SORT_AREA_RETAINED_SIZE=41943040;

SELECT 'SAS' FROM DUAL;

SPOOL OFF

SELECT /*+ GATHER_PLAN_STATISTICS */
*
FROM
T1
ORDER BY
C2,
C3;

SPOOL SortToDiskTest11.1.0.7-1.txt APPEND

SELECT
*
FROM
TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

SELECT
NAME,
VALUE
FROM
V\$SYSSTAT
WHERE
UPPER(NAME) LIKE '%SORT%'
ORDER BY
NAME;

/* Let's try again, this time using the optimizer setting for Oracle 10.2.0.2: */
ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SESSION SET TRACEFILE_IDENTIFIER = 'OFE_10.2.0.2';
ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE='10.2.0.2';

SELECT 'OFE_10.2.0.2' FROM DUAL;

SPOOL OFF

SELECT /*+ GATHER_PLAN_STATISTICS */
*
FROM
T1
ORDER BY
C2,
C3;

SPOOL SortToDiskTest11.1.0.7-1.txt APPEND

SELECT
*
FROM
TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

SELECT
NAME,
VALUE
FROM
V\$SYSSTAT
WHERE
UPPER(NAME) LIKE '%SORT%'
ORDER BY
NAME;

/* Let's try again, this time using the optimizer setting for Oracle 10.1.0.4: */
ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SESSION SET TRACEFILE_IDENTIFIER = 'OFE_10.1.0.4';
ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE='10.1.0.4';

SELECT 'OFE_10.1.0.4' FROM DUAL;

SPOOL OFF

SELECT /*+ GATHER_PLAN_STATISTICS */
*
FROM
T1
ORDER BY
C2,
C3;

SPOOL SortToDiskTest11.1.0.7-1.txt APPEND

SELECT
*
FROM
TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

SELECT
NAME,
VALUE
FROM
V\$SYSSTAT
WHERE
UPPER(NAME) LIKE '%SORT%'
ORDER BY
NAME;

/* Let's try again with pga_aggregate_target at 300MB: */
ALTER SYSTEM SET PGA_AGGREGATE_TARGET=300M;
ALTER SYSTEM FLUSH SHARED_POOL;

SELECT 'PGA 300' FROM DUAL;

SPOOL OFF

SELECT /*+ GATHER_PLAN_STATISTICS */
*
FROM
T1
ORDER BY
C2,
C3;

SPOOL SortToDiskTest11.1.0.7-1.txt APPEND

SELECT
*
FROM
TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

SELECT
NAME,
VALUE
FROM
V\$SYSSTAT
WHERE
UPPER(NAME) LIKE '%SORT%'
ORDER BY
NAME;

/* Try again, reseting SORT_AREA_SIZE: */
ALTER SESSION SET SORT_AREA_SIZE=65536;
ALTER SESSION SET SORT_AREA_RETAINED_SIZE=0;
ALTER SYSTEM FLUSH SHARED_POOL;

SELECT 'SAS 64KB' FROM DUAL;

SPOOL OFF

SELECT /*+ GATHER_PLAN_STATISTICS */
*
FROM
T1
ORDER BY
C2,
C3;

SPOOL SortToDiskTest11.1.0.7-1.txt APPEND

SELECT
*
FROM
TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

SELECT
NAME,
VALUE
FROM
V\$SYSSTAT
WHERE
UPPER(NAME) LIKE '%SORT%'
ORDER BY
NAME;

/* Just to confirm, one more time: */
ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE='10.1.0.4';
ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1';

SELECT 'OFE_10.1.0.4' FROM DUAL;

SPOOL OFF

SELECT /*+ GATHER_PLAN_STATISTICS */
*
FROM
T1
ORDER BY
C2,
C3;

SPOOL SortToDiskTest11.1.0.7-1.txt APPEND

SELECT
*
FROM
TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

SELECT
NAME,
VALUE
FROM
V\$SYSSTAT
WHERE
UPPER(NAME) LIKE '%SORT%'
ORDER BY
NAME;

/* Try again, setting PGA_AGGREGATE_TARGET back to 200MB: */
ALTER SYSTEM SET PGA_AGGREGATE_TARGET=200M;
ALTER SYSTEM FLUSH SHARED_POOL;

SELECT 'PGA 200' FROM DUAL;

SPOOL OFF

SELECT /*+ GATHER_PLAN_STATISTICS */
*
FROM
T1
ORDER BY
C2,
C3;

SPOOL SortToDiskTest11.1.0.7-1.txt APPEND

SELECT
*
FROM
TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

SELECT
NAME,
VALUE
FROM
V\$SYSSTAT
WHERE
UPPER(NAME) LIKE '%SORT%'
ORDER BY
NAME;

SELECT
SP.LAST_OUTPUT_ROWS,
SP.TEMP_SPACE,
SP.ESTIMATED_OPTIMAL_SIZE,
SP.ESTIMATED_ONEPASS_SIZE,
SP.LAST_MEMORY_USED,
SP.LAST_EXECUTION,
SP.LAST_TEMPSEG_SIZE
FROM
V\$SQL S,
V\$SQL_PLAN_STATISTICS_ALL SP
WHERE
S.SQL_TEXT LIKE 'SELECT /*+ GATHER_PLAN_STATISTICS */%'
AND S.SQL_ID=SP.SQL_ID;

/* Trying again with PGA_AGGREGATE_TARGET at 100MB: */
ALTER SYSTEM SET PGA_AGGREGATE_TARGET=100M;
ALTER SYSTEM FLUSH SHARED_POOL;

SELECT 'PGA 100' FROM DUAL;

SPOOL OFF

SELECT /*+ GATHER_PLAN_STATISTICS */
*
FROM
T1
ORDER BY
C2,
C3;

SPOOL SortToDiskTest11.1.0.7-1.txt APPEND

SELECT
*
FROM
TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

SELECT
NAME,
VALUE
FROM
V\$SYSSTAT
WHERE
UPPER(NAME) LIKE '%SORT%'
ORDER BY
NAME;

SELECT
SP.LAST_OUTPUT_ROWS,
SP.TEMP_SPACE,
SP.ESTIMATED_OPTIMAL_SIZE,
SP.ESTIMATED_ONEPASS_SIZE,
SP.LAST_MEMORY_USED,
SP.LAST_EXECUTION,
SP.LAST_TEMPSEG_SIZE
FROM
V\$SQL S,
V\$SQL_PLAN_STATISTICS_ALL SP
WHERE
S.SQL_TEXT LIKE 'SELECT /*+ GATHER_PLAN_STATISTICS */%'
AND S.SQL_ID=SP.SQL_ID;

/* Let's be cruel to Oracle to see what happens with PGA_AGGREGATE_TARGET at 20MB: */
ALTER SYSTEM SET PGA_AGGREGATE_TARGET=20M;
ALTER SYSTEM FLUSH SHARED_POOL;

SELECT 'PGA 20' FROM DUAL;

SPOOL OFF

SELECT /*+ GATHER_PLAN_STATISTICS */
*
FROM
T1
ORDER BY
C2,
C3;

SPOOL SortToDiskTest11.1.0.7-1.txt APPEND

SELECT
*
FROM
TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

CONNECT /@OR11 AS SYSDBA

SELECT
SP.LAST_OUTPUT_ROWS,
SP.TEMP_SPACE,
SP.ESTIMATED_OPTIMAL_SIZE,
SP.ESTIMATED_ONEPASS_SIZE,
SP.LAST_MEMORY_USED,
SP.LAST_EXECUTION,
SP.LAST_TEMPSEG_SIZE
FROM
V\$SQL S,
V\$SQL_PLAN_STATISTICS_ALL SP
WHERE
S.SQL_TEXT LIKE 'SELECT /*+ GATHER_PLAN_STATISTICS */%'
AND S.SQL_ID=SP.SQL_ID;

/* Let's see the hidden parameters (adapted from a script on Jonathan Lewis' website): */
SELECT
UPPER(NAM.KSPPINM) NAME,
VAL.KSPPSTVL VALUE,
VAL.KSPPSTDF ISDEFAULT,
DECODE(BITAND(VAL.KSPPSTVF,7),
1,'MODIFIED',
4,'SYSTEM MODIFIED',
'FALSE') ISMODIFIED
FROM
X\$KSPPI NAM,
X\$KSPPSV VAL
WHERE
NAM.INDX = VAL.INDX
AND UPPER(NAM.KSPPINM) IN ('_SMM_MIN_SIZE','_SMM_MAX_SIZE')
ORDER BY
UPPER(NAM.KSPPINM);

SPOOL OFF```

The output of the above script follows, slightly cleaned up:

```NAME             VALUE
-------------- -------
sorts (disk)      0
sorts (memory) 3746
sorts (rows)  33118

'STARTINGPOINT
--------------
STARTING POINT

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------
SQL_ID  auy15crnrmkc3, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */   * FROM   T1 ORDER BY   C2,   C3

Plan hash value: 2148421099

----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |   1000K|00:00:00.28 |    2712 |       |       |          |
|   1 |  SORT ORDER BY     |      |      1 |   1000K|   1000K|00:00:00.28 |    2712 |    38M|  2211K|   34M (0)|
|   2 |   TABLE ACCESS FULL| T1   |      1 |   1000K|   1000K|00:00:00.01 |    2712 |       |       |          |
----------------------------------------------------------------------------------------------------------------

NAME             VALUE
-------------- -------
sorts (disk)         0
sorts (memory)    6291
sorts (rows)   1196353

LAST_OUTPUT_ROWS TEMP_SPACE ESTIMATED_OPTIMAL_SIZE ESTIMATED_ONEPASS_SIZE LAST_MEMORY_USED LAST_EXECU LAST_TEMPSEG_SIZE
---------------- ---------- ---------------------- ---------------------- ---------------- ---------- -----------------
1000000
1000000   24110000               40624128                2264064         36109312 OPTIMAL
1000000

'PGA200
-------
PGA 200

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  auy15crnrmkc3, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */   * FROM   T1 ORDER BY   C2,   C3

Plan hash value: 2148421099

----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |   1000K|00:00:00.28 |    2712 |       |       |          |
|   1 |  SORT ORDER BY     |      |      1 |   1000K|   1000K|00:00:00.28 |    2712 |    38M|  2211K|   34M (0)|
|   2 |   TABLE ACCESS FULL| T1   |      1 |   1000K|   1000K|00:00:00.01 |    2712 |       |       |          |
----------------------------------------------------------------------------------------------------------------

NAME             VALUE
-------------- -------
sorts (disk)         0
sorts (memory)    8948
sorts (rows)   2217360

LAST_OUTPUT_ROWS TEMP_SPACE ESTIMATED_OPTIMAL_SIZE ESTIMATED_ONEPASS_SIZE LAST_MEMORY_USED LAST_EXECU LAST_TEMPSEG_SIZE
---------------- ---------- ---------------------- ---------------------- ---------------- ---------- -----------------
1000000
1000000   24110000               40624128                2264064         36109312 OPTIMAL
1000000

'PGA300
-------
PGA 300

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  auy15crnrmkc3, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */   * FROM   T1 ORDER BY   C2,   C3

Plan hash value: 2148421099

----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |   1000K|00:00:03.28 |    2712 |       |       |          |
|   1 |  SORT ORDER BY     |      |      1 |   1000K|   1000K|00:00:03.28 |    2712 |    38M|  2211K|   34M (0)|
|   2 |   TABLE ACCESS FULL| T1   |      1 |   1000K|   1000K|00:00:00.01 |    2712 |       |       |          |
----------------------------------------------------------------------------------------------------------------

NAME             VALUE
-------------- -------
sorts (disk)         0
sorts (memory)   11357
sorts (rows)   3367784

'PGA200
-------
PGA 200

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  auy15crnrmkc3, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */   * FROM   T1 ORDER BY   C2,   C3

Plan hash value: 2148421099

----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |   1000K|00:00:00.28 |    2712 |       |       |          |
|   1 |  SORT ORDER BY     |      |      1 |   1000K|   1000K|00:00:00.28 |    2712 |    38M|  2211K|   34M (0)|
|   2 |   TABLE ACCESS FULL| T1   |      1 |   1000K|   1000K|00:00:00.01 |    2712 |       |       |          |
----------------------------------------------------------------------------------------------------------------

NAME             VALUE
-------------- -------
sorts (disk)         0
sorts (memory)   13757
sorts (rows)   4388065

LAST_OUTPUT_ROWS TEMP_SPACE ESTIMATED_OPTIMAL_SIZE ESTIMATED_ONEPASS_SIZE LAST_MEMORY_USED LAST_EXECU LAST_TEMPSEG_SIZE
---------------- ---------- ---------------------- ---------------------- ---------------- ---------- -----------------
1000000
1000000   24110000               40624128                2264064         36109312 OPTIMAL
1000000

'PGA150
-------
PGA 150

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  auy15crnrmkc3, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */   * FROM   T1 ORDER BY   C2,   C3

Plan hash value: 2148421099

--------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|
--------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |   1000K|00:00:00.62 |    2715 |   2927 |   2927 |       |       |          |         |
|   1 |  SORT ORDER BY     |      |      1 |   1000K|   1000K|00:00:00.62 |    2715 |   2927 |   2927 |    25M|  1845K|   30M (1)|   23552 |
|   2 |   TABLE ACCESS FULL| T1   |      1 |   1000K|   1000K|00:00:00.01 |    2712 |      0 |      0 |       |       |          |         |
--------------------------------------------------------------------------------------------------------------------------------------------

NAME             VALUE
-------------- -------
sorts (disk)         1
sorts (memory)   17889
sorts (rows)   5558136

LAST_OUTPUT_ROWS TEMP_SPACE ESTIMATED_OPTIMAL_SIZE ESTIMATED_ONEPASS_SIZE LAST_MEMORY_USED LAST_EXECU LAST_TEMPSEG_SIZE
---------------- ---------- ---------------------- ---------------------- ---------------- ---------- -----------------
1000000
1000000   24110000               27058176                1889280         31464448 1 PASS                 23552
1000000

'PGA200
-------
PGA 200

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  auy15crnrmkc3, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */   * FROM   T1 ORDER BY   C2,   C3

Plan hash value: 2148421099

--------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|
--------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |   1000K|00:00:00.44 |    2715 |   2927 |   2927 |       |       |          |         |
|   1 |  SORT ORDER BY     |      |      1 |   1000K|   1000K|00:00:00.44 |    2715 |   2927 |   2927 |    25M|  1843K|   30M (1)|   23552 |
|   2 |   TABLE ACCESS FULL| T1   |      1 |   1000K|   1000K|00:00:00.01 |    2712 |      0 |      0 |       |       |          |         |
--------------------------------------------------------------------------------------------------------------------------------------------

NAME             VALUE
-------------- -------
sorts (disk)         2
sorts (memory)   20280
sorts (rows)   6578105

LAST_OUTPUT_ROWS TEMP_SPACE ESTIMATED_OPTIMAL_SIZE ESTIMATED_ONEPASS_SIZE LAST_MEMORY_USED LAST_EXECU LAST_TEMPSEG_SIZE
---------------- ---------- ---------------------- ---------------------- ---------------- ---------- -----------------
1000000
1000000   24110000               26975232                1887232         31464448 1 PASS                 23552
1000000

----------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  auy15crnrmkc3, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */   * FROM   T1 ORDER BY   C2,   C3

Plan hash value: 2148421099

----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |   1000K|00:00:00.28 |    2712 |       |       |          |
|   1 |  SORT ORDER BY     |      |      1 |   1000K|   1000K|00:00:00.28 |    2712 |    38M|  2211K|   34M (0)|
|   2 |   TABLE ACCESS FULL| T1   |      1 |   1000K|   1000K|00:00:00.01 |    2712 |       |       |          |
----------------------------------------------------------------------------------------------------------------

NAME             VALUE
-------------- -------
sorts (disk)         2
sorts (memory)   22832
sorts (rows)   7729292

LAST_OUTPUT_ROWS TEMP_SPACE ESTIMATED_OPTIMAL_SIZE ESTIMATED_ONEPASS_SIZE LAST_MEMORY_USED LAST_EXECU LAST_TEMPSEG_SIZE
---------------- ---------- ---------------------- ---------------------- ---------------- ---------- -----------------
1000000
1000000   24110000               40624128                2264064         36109312 OPTIMAL
1000000

'OFE_10.2.0.
------------
OFE_10.2.0.3

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  auy15crnrmkc3, child number 1
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */   * FROM   T1 ORDER BY   C2,   C3

Plan hash value: 2148421099

-----------------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |       |  5700 (100)|          |
|   1 |  SORT ORDER BY     |      |  1000K|    13M|    45M|  5700   (1)| 00:01:09 |
|   2 |   TABLE ACCESS FULL| T1   |  1000K|    13M|       |   757   (1)| 00:00:10 |
-----------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  auy15crnrmkc3, child number 1
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */   * FROM   T1 ORDER BY   C2,   C3

Plan hash value: 2148421099

----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |   1000K|00:00:00.28 |    2712 |       |       |          |
|   1 |  SORT ORDER BY     |      |      1 |   1000K|   1000K|00:00:00.28 |    2712 |    38M|  2211K|   34M (0)|
|   2 |   TABLE ACCESS FULL| T1   |      1 |   1000K|   1000K|00:00:00.01 |    2712 |       |       |          |
----------------------------------------------------------------------------------------------------------------

NAME             VALUE
-------------- -------
sorts (disk)         2
sorts (memory)   23828
sorts (rows)   9864585

'PGA200
-------
PGA 200

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  auy15crnrmkc3, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */   * FROM   T1 ORDER BY   C2,   C3

Plan hash value: 2148421099

----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |   1000K|00:00:00.28 |    2712 |       |       |          |
|   1 |  SORT ORDER BY     |      |      1 |   1000K|   1000K|00:00:00.28 |    2712 |    38M|  2211K|   34M (0)|
|   2 |   TABLE ACCESS FULL| T1   |      1 |   1000K|   1000K|00:00:00.01 |    2712 |       |       |          |
----------------------------------------------------------------------------------------------------------------

NAME             VALUE
-------------- -------
sorts (disk)         2
sorts (memory)   26015
sorts (rows)  10884097

'SA
---
SAS

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  auy15crnrmkc3, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */   * FROM   T1 ORDER BY   C2,   C3

Plan hash value: 2148421099

--------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|
--------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |   1000K|00:00:00.50 |    2717 |   2928 |   2928 |       |       |          |         |
|   1 |  SORT ORDER BY     |      |      1 |   1000K|   1000K|00:00:00.50 |    2717 |   2928 |   2928 |    25M|  1845K|   20M (1)|   23552 |
|   2 |   TABLE ACCESS FULL| T1   |      1 |   1000K|   1000K|00:00:00.01 |    2712 |      0 |      0 |       |       |          |         |
--------------------------------------------------------------------------------------------------------------------------------------------

NAME             VALUE
-------------- -------
sorts (disk)         3
sorts (memory)   29925
sorts (rows)  11926332

'OFE_10.2.0.
------------
OFE_10.2.0.2

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  auy15crnrmkc3, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */   * FROM   T1 ORDER BY   C2,   C3

Plan hash value: 2148421099

--------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|
--------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |   1000K|00:00:00.41 |    2717 |   2928 |   2928 |       |       |          |         |
|   1 |  SORT ORDER BY     |      |      1 |   1000K|   1000K|00:00:00.41 |    2717 |   2928 |   2928 |    25M|  1843K|   20M (1)|   23552 |
|   2 |   TABLE ACCESS FULL| T1   |      1 |   1000K|   1000K|00:00:00.01 |    2712 |      0 |      0 |       |       |          |         |
--------------------------------------------------------------------------------------------------------------------------------------------

NAME             VALUE
-------------- -------
sorts (disk)         4
sorts (memory)   32075
sorts (rows)  13076448

'OFE_10.1.0.
------------
OFE_10.1.0.4

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  auy15crnrmkc3, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */   * FROM   T1 ORDER BY   C2,   C3

Plan hash value: 2148421099

--------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|
--------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |   1000K|00:00:00.50 |    2717 |   2928 |   2928 |       |       |          |         |
|   1 |  SORT ORDER BY     |      |      1 |   1000K|   1000K|00:00:00.50 |    2717 |   2928 |   2928 |    25M|  1845K|   20M (1)|   23552 |
|   2 |   TABLE ACCESS FULL| T1   |      1 |   1000K|   1000K|00:00:00.01 |    2712 |      0 |      0 |       |       |          |         |
--------------------------------------------------------------------------------------------------------------------------------------------

NAME             VALUE
-------------- -------
sorts (disk)         5
sorts (memory)   34911
sorts (rows)  14099400

'PGA300
-------
PGA 300

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  auy15crnrmkc3, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */   * FROM   T1 ORDER BY   C2,   C3

Plan hash value: 2148421099

--------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|
--------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |   1000K|00:00:00.41 |    2717 |   2928 |   2928 |       |       |          |         |
|   1 |  SORT ORDER BY     |      |      1 |   1000K|   1000K|00:00:00.41 |    2717 |   2928 |   2928 |    25M|  1843K|   20M (1)|   23552 |
|   2 |   TABLE ACCESS FULL| T1   |      1 |   1000K|   1000K|00:00:00.01 |    2712 |      0 |      0 |       |       |          |         |
--------------------------------------------------------------------------------------------------------------------------------------------

NAME             VALUE
-------------- -------
sorts (disk)         6
sorts (memory)   40285
sorts (rows)  15276005

'SAS64KB
--------
SAS 64KB

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  auy15crnrmkc3, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */   * FROM   T1 ORDER BY   C2,   C3

Plan hash value: 2148421099

----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |   1000K|00:00:00.31 |    2712 |       |       |          |
|   1 |  SORT ORDER BY     |      |      1 |   1000K|   1000K|00:00:00.31 |    2712 |    38M|  2211K|   34M (0)|
|   2 |   TABLE ACCESS FULL| T1   |      1 |   1000K|   1000K|00:00:00.01 |    2712 |       |       |          |
----------------------------------------------------------------------------------------------------------------

NAME             VALUE
-------------- -------
sorts (disk)         6
sorts (memory)   42443
sorts (rows)  16295460

'OFE_10.1.0.
------------
OFE_10.1.0.4

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  auy15crnrmkc3, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */   * FROM   T1 ORDER BY   C2,   C3

Plan hash value: 2148421099

----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |   1000K|00:00:00.31 |    2712 |       |       |          |
|   1 |  SORT ORDER BY     |      |      1 |   1000K|   1000K|00:00:00.31 |    2712 |    38M|  2211K|   34M (0)|
|   2 |   TABLE ACCESS FULL| T1   |      1 |   1000K|   1000K|00:00:00.01 |    2712 |       |       |          |
----------------------------------------------------------------------------------------------------------------

NAME             VALUE
-------------- -------
sorts (disk)         6
sorts (memory)   44626
sorts (rows)  17315395

'PGA200
-------
PGA 200

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  auy15crnrmkc3, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */   * FROM   T1 ORDER BY   C2,   C3

Plan hash value: 2148421099

----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |   1000K|00:00:00.28 |    2712 |       |       |          |
|   1 |  SORT ORDER BY     |      |      1 |   1000K|   1000K|00:00:00.28 |    2712 |    38M|  2211K|   34M (0)|
|   2 |   TABLE ACCESS FULL| T1   |      1 |   1000K|   1000K|00:00:00.01 |    2712 |       |       |          |
----------------------------------------------------------------------------------------------------------------

NAME             VALUE
-------------- -------
sorts (disk)         6
sorts (memory)   48666
sorts (rows)  18487624

LAST_OUTPUT_ROWS TEMP_SPACE ESTIMATED_OPTIMAL_SIZE ESTIMATED_ONEPASS_SIZE LAST_MEMORY_USED LAST_EXECU LAST_TEMPSEG_SIZE
---------------- ---------- ---------------------- ---------------------- ---------------- ---------- -----------------
1000000
1000000   48210000               40624128                2264064         36109312 OPTIMAL
1000000

'PGA100
-------
PGA 100

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  auy15crnrmkc3, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */   * FROM   T1 ORDER BY   C2,   C3

Plan hash value: 2148421099

--------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|
--------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |   1000K|00:00:00.56 |    2717 |   2928 |   2928 |       |       |          |         |
|   1 |  SORT ORDER BY     |      |      1 |   1000K|   1000K|00:00:00.56 |    2717 |   2928 |   2928 |    25M|  1844K|   20M (1)|   23552 |
|   2 |   TABLE ACCESS FULL| T1   |      1 |   1000K|   1000K|00:00:00.01 |    2712 |      0 |      0 |       |       |          |         |
--------------------------------------------------------------------------------------------------------------------------------------------

NAME             VALUE
-------------- -------
sorts (disk)         7
sorts (memory)   58650
sorts (rows)  22038592

LAST_OUTPUT_ROWS TEMP_SPACE ESTIMATED_OPTIMAL_SIZE ESTIMATED_ONEPASS_SIZE LAST_MEMORY_USED LAST_EXECU LAST_TEMPSEG_SIZE
---------------- ---------- ---------------------- ---------------------- ---------------- ---------- -----------------
1000000
1000000   48210000               27012096                1888256         21013504 1 PASS                 23552
1000000

'PGA20
------
PGA 20

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  auy15crnrmkc3, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */   * FROM   T1 ORDER BY   C2,   C3

Plan hash value: 2148421099

--------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|
--------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |   1000K|00:00:00.90 |    2746 |   3816 |   3816 |       |       |          |         |
|   1 |  SORT ORDER BY     |      |      1 |   1000K|   1000K|00:00:00.90 |    2746 |   3816 |   3816 |    25M|  1848K| 4141K (3)|   26624 |
|   2 |   TABLE ACCESS FULL| T1   |      1 |   1000K|   1000K|00:00:00.01 |    2712 |      0 |      0 |       |       |          |         |
--------------------------------------------------------------------------------------------------------------------------------------------

LAST_OUTPUT_ROWS TEMP_SPACE ESTIMATED_OPTIMAL_SIZE ESTIMATED_ONEPASS_SIZE LAST_MEMORY_USED LAST_EXECU LAST_TEMPSEG_SIZE
---------------- ---------- ---------------------- ---------------------- ---------------- ---------- -----------------
1000000
1000000   48210000               27150336                1892352          4240384 3 PASSES               26624
1000000

NAME            VALUE ISDEFAULT ISMODIFIED
--------------- ----- --------- ----------
_SMM_MAX_SIZE    4096 TRUE      FALSE
_SMM_MIN_SIZE     128 TRUE      FALSE            ```

Note in the above that the sorts to disk did not happen when PGA_AGGREGATE_TARGET was adjusted to 200MB, then to 300MB, and then to 200MB.  The first sort to disk happened when the parameter was set to 150MB, and the second sort to disk happened when the parameter was bumped back up to 200MB.

Prior to posting the original blog article “SORT_AREA_SIZE Affecting Sorts to Disk with PGA_AGGREGATE_TARGET Set?”, I read a document titled “Advanced Management of Working Areas In Oracle 9I/10G” that was written by Joze Senegacnik in 2004 (http://joze-senegacnik.blogspot.com/).  When I originally created the test case a couple years ago, I suspected that there was a delayed reaction when the PGA_AGGREGATE_TARGET parameter is adjusted, but I did not know what caused that delayed reaction.  Joze’s article seems to indicate that the delay is caused by the CKPT process publishing the memory bounds only every three seconds.  So, I re-ran the test, adding the following command after each adjustment of the PGA_AGGREGATE_TARGET parameter:

`host sleep 30`

The above command executes the operating system’s sleep command, causing SQL*Plus to pause for 30 seconds.  The results (just displaying the 200MB to 150MB to 200MB section of the output)?

```'PGA200
-------
PGA 200

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------
SQL_ID  auy15crnrmkc3, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */   * FROM   T1 ORDER BY   C2,   C3

Plan hash value: 2148421099

----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |   1000K|00:00:00.28 |    2712 |       |       |          |
|   1 |  SORT ORDER BY     |      |      1 |   1000K|   1000K|00:00:00.28 |    2712 |    38M|  2211K|   34M (0)|
|   2 |   TABLE ACCESS FULL| T1   |      1 |   1000K|   1000K|00:00:00.01 |    2712 |       |       |          |
----------------------------------------------------------------------------------------------------------------

NAME             VALUE
-------------- -------
sorts (disk)         0
sorts (memory)   14062
sorts (rows)   4390586

LAST_OUTPUT_ROWS TEMP_SPACE ESTIMATED_OPTIMAL_SIZE ESTIMATED_ONEPASS_SIZE LAST_MEMORY_USED LAST_EXECU LAST_TEMPSEG_SIZE
---------------- ---------- ---------------------- ---------------------- ---------------- ---------- -----------------
1000000
1000000   24110000               40624128                2264064         36109312 OPTIMAL
1000000

'PGA150
-------
PGA 150

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  auy15crnrmkc3, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */   * FROM   T1 ORDER BY   C2,   C3

Plan hash value: 2148421099

--------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|
--------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |   1000K|00:00:00.47 |    2715 |   2927 |   2927 |       |       |          |         |
|   1 |  SORT ORDER BY     |      |      1 |   1000K|   1000K|00:00:00.47 |    2715 |   2927 |   2927 |    25M|  1846K|   30M (1)|   23552 |
|   2 |   TABLE ACCESS FULL| T1   |      1 |   1000K|   1000K|00:00:00.01 |    2712 |      0 |      0 |       |       |          |         |
--------------------------------------------------------------------------------------------------------------------------------------------

NAME             VALUE
-------------- -------
sorts (disk)      1
sorts (memory)16553
sorts (rows)5541481

LAST_OUTPUT_ROWS TEMP_SPACE ESTIMATED_OPTIMAL_SIZE ESTIMATED_ONEPASS_SIZE LAST_MEMORY_USED LAST_EXECU LAST_TEMPSEG_SIZE
---------------- ---------- ---------------------- ---------------------- ---------------- ---------- -----------------
1000000
1000000   24110000               27076608                1890304         31464448 1 PASS                 23552
1000000

'PGA200
-------
PGA 200

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  auy15crnrmkc3, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */   * FROM   T1 ORDER BY   C2,   C3

Plan hash value: 2148421099

----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |   1000K|00:00:00.31 |    2712 |       |       |          |
|   1 |  SORT ORDER BY     |      |      1 |   1000K|   1000K|00:00:00.31 |    2712 |    38M|  2211K|   34M (0)|
|   2 |   TABLE ACCESS FULL| T1   |      1 |   1000K|   1000K|00:00:00.01 |    2712 |       |       |          |
----------------------------------------------------------------------------------------------------------------

NAME             VALUE
-------------- -------
sorts (disk)      1
sorts (memory)18956
sorts (rows)6561447

LAST_OUTPUT_ROWS TEMP_SPACE ESTIMATED_OPTIMAL_SIZE ESTIMATED_ONEPASS_SIZE LAST_MEMORY_USED LAST_EXECU LAST_TEMPSEG_SIZE
---------------- ---------- ---------------------- ---------------------- ---------------- ---------- -----------------
1000000
1000000   24110000               40624128                2264064         36109312 OPTIMAL
1000000  ```

As can be seen from the above, pausing 30 seconds after each adjustment of the PGA_AGGREGATE_TARGET parameter provides CKPT enough time to perform its processing (3 seconds likely would have been sufficient).

I recall reading an article on Jonathan Lewis’ site about a SORT_AREA_SIZE bug (search for SAS Bug on his site).  I then wondered if adjusting the SORT_AREA_SIZE parameter when WORKAREA_SIZE_POLICY = MANUAL at the session level is also subject to the roughly 3 second delay.  I put together the following test case, which differs quite a bit from the one referenced on the asktom.oracle.com site:

```SET PAGESIZE 2000
SET LINESIZE 140
SET ARRAYSIZE 100

SPOOL SortToDiskTest11.1.0.7-3.txt

DROP TABLE T1 PURGE;

CREATE TABLE T1 (
C1 VARCHAR2(20),
C2 NUMBER(12),
C3 NUMBER(12));

/* Fill the test table to 1,000,000 rows: */
DECLARE
X NUMBER;
BEGIN
FOR X IN 1 .. 1000000
LOOP
INSERT INTO T1 VALUES (
TO_CHAR(MOD( X ,100))||'-'||TO_CHAR(MOD( X ,100)),
MOD( X ,500),
X);
END LOOP;
END;
/

COMMIT;

/* Gather statistics on the table: */
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1');

/*+ Let's see where the sort statistics are right now: */
SELECT
NAME,
VALUE
FROM
V\$SYSSTAT
WHERE
UPPER(NAME) LIKE '%SORT%'
ORDER BY
NAME;

/*+ Let's turn on a 10053 trace so that we can see what the cost-based optimizer is willing to report: */
ALTER SESSION SET TRACEFILE_IDENTIFIER = 'SORT_AREA';
ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1';

SELECT 'STARTING POINT' FROM DUAL;

SPOOL OFF

/*+ Run a simple SQL statement to generate a sort, and gather statistics for DBMS_XPLAN: */
SELECT /*+ GATHER_PLAN_STATISTICS */
*
FROM
T1
ORDER BY
C2,
C3;

SPOOL SortToDiskTest11.1.0.7-3.txt APPEND

/*+ Determine the DBMS_XPLAN for the query: */
SELECT
*
FROM
TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

/* Let's check the sort statistics: */
SELECT
NAME,
VALUE
FROM
V\$SYSSTAT
WHERE
UPPER(NAME) LIKE '%SORT%'
ORDER BY
NAME;

/* Let's look at the plan statistics for the SQL statement: */
SELECT
SP.LAST_OUTPUT_ROWS,
SP.TEMP_SPACE,
SP.ESTIMATED_OPTIMAL_SIZE,
SP.ESTIMATED_ONEPASS_SIZE,
SP.LAST_MEMORY_USED,
SP.LAST_EXECUTION,
SP.LAST_TEMPSEG_SIZE
FROM
V\$SQL S,
V\$SQL_PLAN_STATISTICS_ALL SP
WHERE
S.SQL_TEXT LIKE 'SELECT /*+ GATHER_PLAN_STATISTICS */%'
AND S.SQL_ID=SP.SQL_ID;

ALTER SESSION SET WORKAREA_SIZE_POLICY = MANUAL;

/* Let's try again, this time with a 20MB SORT_AREA_SIZE: */
ALTER SESSION SET SORT_AREA_SIZE=20971520;
ALTER SYSTEM FLUSH SHARED_POOL;
host sleep 30

SELECT 'SORT_AREA_SIZE=20971520' FROM DUAL;

SPOOL OFF

SELECT /*+ GATHER_PLAN_STATISTICS */
*
FROM
T1
ORDER BY
C2,
C3;

SPOOL SortToDiskTest11.1.0.7-3.txt APPEND

SELECT
*
FROM
TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

/*+ Let's check the sort statistics: */
SELECT
NAME,
VALUE
FROM
V\$SYSSTAT
WHERE
UPPER(NAME) LIKE '%SORT%'
ORDER BY
NAME;

/*+ Let's look at the plan statistics for the SQL statement: */
SELECT
SP.LAST_OUTPUT_ROWS,
SP.TEMP_SPACE,
SP.ESTIMATED_OPTIMAL_SIZE,
SP.ESTIMATED_ONEPASS_SIZE,
SP.LAST_MEMORY_USED,
SP.LAST_EXECUTION,
SP.LAST_TEMPSEG_SIZE
FROM
V\$SQL S,
V\$SQL_PLAN_STATISTICS_ALL SP
WHERE
S.SQL_TEXT LIKE 'SELECT /*+ GATHER_PLAN_STATISTICS */%'
AND S.SQL_ID=SP.SQL_ID;

/* Since we are still sorting to disk, let's increase the SORT_AREA_SIZE to 25MB again and repeat the test: */
ALTER SESSION SET SORT_AREA_SIZE=26214400;
ALTER SYSTEM FLUSH SHARED_POOL;

SELECT 'SORT_AREA_SIZE=26214400' FROM DUAL;

SPOOL OFF

SELECT /*+ GATHER_PLAN_STATISTICS */
*
FROM
T1
ORDER BY
C2,
C3;

SPOOL SortToDiskTest11.1.0.7-3.txt APPEND

SELECT
*
FROM
TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

SELECT
NAME,
VALUE
FROM
V\$SYSSTAT
WHERE
UPPER(NAME) LIKE '%SORT%'
ORDER BY
NAME;

/* Since we are still sorting to disk, let's increase the SORT_AREA_SIZE to 30MB again and repeat the test: */
ALTER SESSION SET SORT_AREA_SIZE=31457280;
ALTER SYSTEM FLUSH SHARED_POOL;

SELECT 'SORT_AREA_SIZE=31457280' FROM DUAL;

SPOOL OFF

SELECT /*+ GATHER_PLAN_STATISTICS */
*
FROM
T1
ORDER BY
C2,
C3;

SPOOL SortToDiskTest11.1.0.7-3.txt APPEND

SELECT
*
FROM
TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

SELECT
NAME,
VALUE
FROM
V\$SYSSTAT
WHERE
UPPER(NAME) LIKE '%SORT%'
ORDER BY
NAME;

/* Let's increase the SORT_AREA_SIZE to 40MB again and repeat the test: */
ALTER SESSION SET SORT_AREA_SIZE=41943040;
ALTER SYSTEM FLUSH SHARED_POOL;

SELECT 'SORT_AREA_SIZE=41943040' FROM DUAL;

SPOOL OFF

SELECT /*+ GATHER_PLAN_STATISTICS */
*
FROM
T1
ORDER BY
C2,
C3;

SPOOL SortToDiskTest11.1.0.7-3.txt APPEND

SELECT
*
FROM
TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

SELECT
NAME,
VALUE
FROM
V\$SYSSTAT
WHERE
UPPER(NAME) LIKE '%SORT%'
ORDER BY
NAME;

/* Let's change the SORT_AREA_SIZE to 20MB again and repeat the test: */
ALTER SESSION SET SORT_AREA_SIZE=20971520;
ALTER SYSTEM FLUSH SHARED_POOL;

SELECT 'SORT_AREA_SIZE=20971520 #2' FROM DUAL;

SPOOL OFF

SELECT /*+ GATHER_PLAN_STATISTICS */
*
FROM
T1
ORDER BY
C2,
C3;

SPOOL SortToDiskTest11.1.0.7-3.txt APPEND

SELECT
*
FROM
TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

SELECT
NAME,
VALUE
FROM
V\$SYSSTAT
WHERE
UPPER(NAME) LIKE '%SORT%'
ORDER BY
NAME;

/* Let's change the SORT_AREA_SIZE to 40MB again and repeat the test: */
ALTER SESSION SET SORT_AREA_SIZE=41943040;
ALTER SYSTEM FLUSH SHARED_POOL;

SELECT 'SORT_AREA_SIZE=41943040 #2' FROM DUAL;

SPOOL OFF

SELECT /*+ GATHER_PLAN_STATISTICS */
*
FROM
T1
ORDER BY
C2,
C3;

SPOOL SortToDiskTest11.1.0.7-3.txt APPEND

SELECT
*
FROM
TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

SELECT
NAME,
VALUE
FROM
V\$SYSSTAT
WHERE
UPPER(NAME) LIKE '%SORT%'
ORDER BY
NAME;

/* Let's change the SORT_AREA_SIZE to 20MB again, sleep, and repeat the test: */
ALTER SESSION SET SORT_AREA_SIZE=20971520;
ALTER SYSTEM FLUSH SHARED_POOL;
host sleep 30
SELECT 'SORT_AREA_SIZE=20971520 #3' FROM DUAL;

SPOOL OFF

SELECT /*+ GATHER_PLAN_STATISTICS */
*
FROM
T1
ORDER BY
C2,
C3;

SPOOL SortToDiskTest11.1.0.7-3.txt APPEND

SELECT
*
FROM
TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

SELECT
NAME,
VALUE
FROM
V\$SYSSTAT
WHERE
UPPER(NAME) LIKE '%SORT%'
ORDER BY
NAME;

/* Let's change the SORT_AREA_SIZE to 40MB again, sleep, and repeat the test: */
ALTER SESSION SET SORT_AREA_SIZE=41943040;
ALTER SYSTEM FLUSH SHARED_POOL;
host sleep 30

SELECT 'SORT_AREA_SIZE=41943040 #3' FROM DUAL;

SPOOL OFF
SELECT /*+ GATHER_PLAN_STATISTICS */
*
FROM
T1
ORDER BY
C2,
C3;

SPOOL SortToDiskTest11.1.0.7-3.txt APPEND

SELECT
*
FROM
TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

SELECT
NAME,
VALUE
FROM
V\$SYSSTAT
WHERE
UPPER(NAME) LIKE '%SORT%'
ORDER BY
NAME;

SPOOL OFF```

The output of the above script follows:

```NAME              VALUE
--------------- -------
sorts (disk)          0
sorts (memory)     3591
sorts (rows)      33734

'STARTINGPOINT
--------------
STARTING POINT

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  auy15crnrmkc3, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */   * FROM   T1 ORDER BY   C2,   C3

Plan hash value: 2148421099

-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |   1000K|00:00:00.50 |    2712 |   2707 |       |       |          |
|   1 |  SORT ORDER BY     |      |      1 |   1000K|   1000K|00:00:00.50 |    2712 |   2707 |    38M|  2211K|   34M (0)|
|   2 |   TABLE ACCESS FULL| T1   |      1 |   1000K|   1000K|00:00:00.03 |    2712 |   2707 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------

NAME              VALUE
--------------- -------
sorts (disk)          0
sorts (memory)     6019
sorts (rows)    1066679

LAST_OUTPUT_ROWS TEMP_SPACE ESTIMATED_OPTIMAL_SIZE ESTIMATED_ONEPASS_SIZE LAST_MEMORY_USED LAST_EXECU LAST_TEMPSEG_SIZE
---------------- ---------- ---------------------- ---------------------- ---------------- ---------- -----------------
1000000
1000000   24110000               40624128                2264064         36109312 OPTIMAL
1000000

'SORT_AREA_SIZE=2097152
-----------------------
SORT_AREA_SIZE=20971520

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  auy15crnrmkc3, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */   * FROM   T1 ORDER BY   C2,   C3

Plan hash value: 2148421099

--------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|
--------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |   1000K|00:00:00.50 |    2715 |   2926 |   2926 |       |       |          |         |
|   1 |  SORT ORDER BY     |      |      1 |   1000K|   1000K|00:00:00.50 |    2715 |   2926 |   2926 |    25M|  1844K|   18M (1)|   23552 |
|   2 |   TABLE ACCESS FULL| T1   |      1 |   1000K|   1000K|00:00:00.01 |    2712 |      0 |      0 |       |       |          |         |
--------------------------------------------------------------------------------------------------------------------------------------------

NAME              VALUE
--------------- -------
sorts (disk)          1
sorts (memory)     8435
sorts (rows)    2086785

LAST_OUTPUT_ROWS TEMP_SPACE ESTIMATED_OPTIMAL_SIZE ESTIMATED_ONEPASS_SIZE LAST_MEMORY_USED LAST_EXECU LAST_TEMPSEG_SIZE
---------------- ---------- ---------------------- ---------------------- ---------------- ---------- -----------------
1000000
1000000   24110000               27002880                1888256         18884608 1 PASS                 23552
1000000

'SORT_AREA_SIZE=2621440
-----------------------
SORT_AREA_SIZE=26214400

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  auy15crnrmkc3, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */   * FROM   T1 ORDER BY   C2,   C3

Plan hash value: 2148421099

--------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|
--------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |   1000K|00:00:00.44 |    2715 |   2926 |   2926 |       |       |          |         |
|   1 |  SORT ORDER BY     |      |      1 |   1000K|   1000K|00:00:00.44 |    2715 |   2926 |   2926 |    25M|  1843K|   23M (1)|   23552 |
|   2 |   TABLE ACCESS FULL| T1   |      1 |   1000K|   1000K|00:00:00.01 |    2712 |      0 |      0 |       |       |          |         |
--------------------------------------------------------------------------------------------------------------------------------------------

NAME              VALUE
--------------- -------
sorts (disk)          2
sorts (memory)    10870
sorts (rows)    3107315

'SORT_AREA_SIZE=3145728
-----------------------
SORT_AREA_SIZE=31457280

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  auy15crnrmkc3, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */   * FROM   T1 ORDER BY   C2,   C3

Plan hash value: 2148421099

--------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|
--------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |   1000K|00:00:00.47 |    2715 |   2926 |   2926 |       |       |          |         |
|   1 |  SORT ORDER BY     |      |      1 |   1000K|   1000K|00:00:00.47 |    2715 |   2926 |   2926 |    25M|  1843K|   27M (1)|   23552 |
|   2 |   TABLE ACCESS FULL| T1   |      1 |   1000K|   1000K|00:00:00.01 |    2712 |      0 |      0 |       |       |          |         |
--------------------------------------------------------------------------------------------------------------------------------------------

NAME              VALUE
--------------- -------
sorts (disk)          3
sorts (memory)    13542
sorts (rows)    4127908

'SORT_AREA_SIZE=4194304
-----------------------
SORT_AREA_SIZE=41943040

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  auy15crnrmkc3, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */   * FROM   T1 ORDER BY   C2,   C3

Plan hash value: 2148421099

----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |   1000K|00:00:00.28 |    2712 |       |       |          |
|   1 |  SORT ORDER BY     |      |      1 |   1000K|   1000K|00:00:00.28 |    2712 |    38M|  2211K|   34M (0)|
|   2 |   TABLE ACCESS FULL| T1   |      1 |   1000K|   1000K|00:00:00.01 |    2712 |       |       |          |
----------------------------------------------------------------------------------------------------------------

NAME              VALUE
--------------- -------
sorts (disk)          3
sorts (memory)    16027
sorts (rows)    5148428

'SORT_AREA_SIZE=20971520#2
--------------------------
SORT_AREA_SIZE=20971520 #2

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  auy15crnrmkc3, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */   * FROM   T1 ORDER BY   C2,   C3

Plan hash value: 2148421099

--------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|
--------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |   1000K|00:00:00.44 |    2715 |   2926 |   2926 |       |       |          |         |
|   1 |  SORT ORDER BY     |      |      1 |   1000K|   1000K|00:00:00.44 |    2715 |   2926 |   2926 |    25M|  1843K|   18M (1)|   23552 |
|   2 |   TABLE ACCESS FULL| T1   |      1 |   1000K|   1000K|00:00:00.01 |    2712 |      0 |      0 |       |       |          |         |
--------------------------------------------------------------------------------------------------------------------------------------------

NAME              VALUE
--------------- -------
sorts (disk)          4
sorts (memory)    18474
sorts (rows)    6168736

'SORT_AREA_SIZE=41943040#2
--------------------------
SORT_AREA_SIZE=41943040 #2

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  auy15crnrmkc3, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */   * FROM   T1 ORDER BY   C2,   C3

Plan hash value: 2148421099

----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |   1000K|00:00:00.28 |    2712 |       |       |          |
|   1 |  SORT ORDER BY     |      |      1 |   1000K|   1000K|00:00:00.28 |    2712 |    38M|  2211K|   34M (0)|
|   2 |   TABLE ACCESS FULL| T1   |      1 |   1000K|   1000K|00:00:00.01 |    2712 |       |       |          |
----------------------------------------------------------------------------------------------------------------

NAME              VALUE
--------------- -------
sorts (disk)          4
sorts (memory)    22413
sorts (rows)    7206482

'SORT_AREA_SIZE=20971520#3
--------------------------
SORT_AREA_SIZE=20971520 #3

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  auy15crnrmkc3, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */   * FROM   T1 ORDER BY   C2,   C3

Plan hash value: 2148421099

--------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|
--------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |   1000K|00:00:00.53 |    2715 |   2926 |   2926 |       |       |          |         |
|   1 |  SORT ORDER BY     |      |      1 |   1000K|   1000K|00:00:00.53 |    2715 |   2926 |   2926 |    25M|  1843K|   18M (1)|   23552 |
|   2 |   TABLE ACCESS FULL| T1   |      1 |   1000K|   1000K|00:00:00.01 |    2712 |      0 |      0 |       |       |          |         |
--------------------------------------------------------------------------------------------------------------------------------------------

NAME              VALUE
--------------- -------
sorts (disk)          5
sorts (memory)    24788
sorts (rows)    8226615

'SORT_AREA_SIZE=41943040#3
--------------------------
SORT_AREA_SIZE=41943040 #3

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  auy15crnrmkc3, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */   * FROM   T1 ORDER BY   C2,   C3

Plan hash value: 2148421099

----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |   1000K|00:00:00.28 |    2712 |       |       |          |
|   1 |  SORT ORDER BY     |      |      1 |   1000K|   1000K|00:00:00.28 |    2712 |    38M|  2211K|   34M (0)|
|   2 |   TABLE ACCESS FULL| T1   |      1 |   1000K|   1000K|00:00:00.01 |    2712 |       |       |          |
----------------------------------------------------------------------------------------------------------------

NAME              VALUE
--------------- -------
sorts (disk)          5
sorts (memory)    27252
sorts (rows)    9247027

LAST_OUTPUT_ROWS TEMP_SPACE ESTIMATED_OPTIMAL_SIZE ESTIMATED_ONEPASS_SIZE LAST_MEMORY_USED LAST_EXECU LAST_TEMPSEG_SIZE
---------------- ---------- ---------------------- ---------------------- ---------------- ---------- -----------------
1000000
1000000                          40624128                2264064         36109312 OPTIMAL
1000000                   ```

The output shows that at least in Oracle 11.1.0.7 there is no delay in the implementation of the SORT_AREA_SIZE at the session level when WORKAREA_SIZE_POLICY = MANUAL is set at the session level and the SORT_AREA_SIZE parameter is adjusted.  The test query continued to perform a sort to disk with SORT_AREA_SIZEs OF 30MB or smaller.  Every time the SORT_AREA_SIZE was set to 40MB without a delay, Oracle switched to an in-memory optimal sort.  I might need to repeat the final test with an older release of Oracle to see if the same behavior is present (I will update this post if I see a change).

Back on the original topic, I now wonder if the occasional sorts to disk that I saw when experiementing with the SORT_AREA_SIZE parameter with PGA_AGGREGATE_TARGET set (and not being modified) in a production environment could have been a side-effect of the “CKPT process publishing the memory bounds only every three seconds” as was described in Joze’s paper. In short, the behavior was an unrelated cause and effect.

## Faulty Quotes 4 – Buffer Cache Hit Ratio (BCHR)

22 12 2009

December 22, 2009

Over the years a lot of conflicting advice for maintaining Oracle databases has surfaced in books, magazine articles, websites, Usenet (and other discussion forums), and presentations.  Even as the year 2009 draws to a close there are Oracle forum posts asking for help in improving the buffer cache hit ratio (BCHR).

Here is an interesting history of recommendations for the buffer cache hit ratio (BCHR), as presented in user conferences for a particular ERP platform:

1999:

Find the statistics in V\$SYSSTAT or REPORT.TXT

```  Logical reads = db block gets + consistent gets

Ratio should be above 90%, if not increase the shared_pool_size

——————–

2001:

```HIT_RATIO=((DB BLOCK GETS + CONSISTENT GETS - PHYSICAL READS)/
(DB BLOCK GETS + CONSISTENT GETS))*100```

90 – 100 %  Few physical reads. Current size is optimal if not a bit high.  OK to remove some buffers if memory needed elsewhere.
70 – 89 % Buffer cache has low to moderate number of physical reads.  Consider resizing if there is a serious problem with memory on the Oracle database.
0 – 69 % Buffer cache is experiencing moderate to high number of physical reads.  Consider adding more buffers to the buffer cache.

——————–

2004:

Be aware that the Cache Hit Ratio isn’t necessarily the definitive answer; although it can be a good indication that something is up.

——————–

2006:

Bad performance indicators – Poor cache hit ratios

——————–

2008:

The buffer cache hit ratio was never mentioned.

——————–
If the above is any indication, recommending the use of the buffer cache hit ratio as a performance metric is on the decline.  This seems to be confirmed by the helpful replies that typically follow requests for improving the buffer cache hit ratio in various forum threads.  But, there is a twist.  Do we know what the buffer cache hit ratio is supposed to measure?  A search of the Internet, including Metalink, finds a number of formulas for calculating the buffer cache hit ratio.  Some of those forumulas follow:

`(logical reads – physical reads)/logical reads`

——

`1 – (physical reads)/(consistent gets + db block gets)`

——

`(logical reads)/(logical reads + physical reads)`

——

`1 – (physical reads – physical reads direct – physical reads direct (lob))/(consistent gets + db block gets)`

——

`1 – (physical reads – (physical reads direct + physical reads direct (lob)))/(db block gets + consistent gets – (physical reads direct + physical reads direct (lob)))`

So many formulas, and then we have this one from the 11g R2 Performance Tuning Guide:

`1 - (('physical reads cache') / ('consistent gets from cache' + 'db block gets from cache')`

Nice… and a bit confusing.  Pick a formula, any formula.  But, what is it measuring?

A search of the Internet finds a large number of articles discussing the benefits of using the buffer cache hit ratio as a performance metric.  For example:

www.cryer.co.uk/brian/oracle/tuning_bchr.htm

“If the cache-hit ratio goes below 90% then:
* For Oracle 8 and earlier: increase the initialisation parameter DB_BLOCK_BUFFERS.
* For Oracle 9 onwards: increate the initialisation parameter DB_CACHE_SIZE.”

——————–

publib.boulder.ibm.com/tividd/td/oracle2/GC32-0454-00/en_US/HTML/oracle20rgf2k81.htm

“In general, if the hit ratio is below 90%, and the dictionary cache has been tuned, increase the init.ora parameter DB_BLOCK_BUFFERS to increase the hit ratio.”

——————–

oracle-training.cc/teas_elite_troub_5.htm

“One must use delta statistics over time to come up with a meaningful value for the ratio, and high logical I/O values can definitely be a leading cause of bad execution times.  However, when properly computed, the buffer cache hit ratio  is an excellent indicator of how often the data requested by users is found in RAM instead of disk, a fact of no small importance.”

——————–

www.iselfschooling.com/mcpfcd1/iscdpfh05vrl.htm

“As a DBA, you are responsible for monitoring and calculating the Buffer Cache Hit Ratio in the SGA memory in case of performance problems…

— If the Buffer Cache Hit Ratio is more than 90% then there is no problem.
— If the Buffer Cache Hit Ratio is between 70% and 90% then there could be a problem.
— And if the Buffer Cache Hit Ratio is less than 70%, there is definitely a problem and the Buffer Cache size needs to be increased.”

——————–

praetoriate.com/t_%20tuning_data_buffer_hit_ratio.htm

“To summarize, our goal as the Oracle DBA is to allocate as much RAM as possible to the data buffers without causing the database server to page-in RAM. Whenever the hourly data buffer hit ratio falls below 90 percent, we should add buffers to the block buffers.”

——————–

dba-oracle.com/art_dbazine_bust_ora_myth_bust.htm

“New myth – Ratio-based Oracle tuning is meaningless…
However, most OLTP systems and systems in which the working set of frequently-referenced data are not cached will greatly benefit from ratio-based tuning. Oracle Corporation recognizes that monitoring SGA usage ratios and adjusting the size of the SGA regions can have a dramatic impact on system performance, and this is the foundation of Oracle10g Automatic Memory Management (AMM) in which Oracle calculates the point of diminishing marginal return from adding data buffers”

——————–

oracle-training.cc/t_viewing_sga_performance.htm

“The data buffer hit ratio can provide data similar to v\$db_cache_advice , and most Oracle tuning professionals use both tools to monitor the effectiveness of data buffers and monitor how AMM adjusts the sizes of the buffer pools.”

——————–

oracle-training.cc/t_allocating_oracle_buffer_caches.htm

“The DBHR is a common metric used by Oracle tuning experts to measure the propensity of a row to be in the data buffer.  For example, a hit ratio of 95 percent means that 95 percent of row requests were already present in the data buffer, thereby avoiding an expensive disk I/O.  In general, as the size of the data buffers increases, the DBHR will also increase and approach 100 percent.”

——————–

oracle-training.cc/phys_55.htm

“In order for the DBA to determine how well the buffer pools are performing, it is necessary to measure the hit ratio at more frequent intervals.  Calculating the DBHR for Oracle8 and beyond is more complicated than earlier versions, but the results enable the administrator to achieve a higher level of tuning than was previously possible.”

——————–

dba-oracle.com/t_buffer_cache_hit_ratio_value.htm

“But the question remains about the value of this metric to the DBA.

Once I’ve tuned and stabilized my systems, I notice that the metrics create repeatable ‘signatures’, patterns of usage that form the baselines for the exception alerts.

First, we establish a ‘exception threshold’ for the BCHR, (e.g. +- 20%), and compare that deviation to the historical average, normalized by the historical average per hour and the day-of-the-week.”

——————–

“Many folks misunderstand that bit about ‘setting your own BHR’, and falsely conclude that it’s a useless metric. It’s not useless.

Of course, doing lots pre-buffered I/O (consistent gets) will increase the BHR, that’s the whole point, right?

That does not mean that the BHR is useless, it just means that it’s not a panacea.

The BHR remains very useful for detecting ‘undersized’ data buffers, where the working-set is not cached and Oracle is forced to do extra physical reads. . . .

If the BHR was totally useless, why does Oracle continue to include it in OEM alert thresholds, and STATSPACK and AWR reports?

The BHR is just like any other Oracle metric, you must understand its limitations and proper usage. It’s just one of many tools…”

——————–

“BCHR can be manipulated. That is nothing new. All stats can be inflated in similar manners. But that doesn’t make them all meaningless. Given everything else being equal, high BCHR is always better than low BHCR…  BCHR alone is not meant to tell performance. If it does, we would not have to look at anything else…  BCHR alone does not tell you about overall performance. It simply tell you the disk I/O percentage. It is an indicator, a very meaningful one.”

A number of examples advocating the use of the buffer cache hit ratio also exist in Oracle books, many of which may be viewed, in part, through Google searches:

Creating a self-tuning Oracle database: automating Oracle9i Dynamic SGA

“The data buffer hit ratio (DBHR) measures the propensity for a block to be cached in the buffer pool, and the goal of the DBA is to keep as many of the frequently used Oracle blocks in buffer memory as possible…”

——————–

Oracle9i High-Performance Tuning with STATSPACK

“From this chart we can clearly see that the DBHR dropped below the recommended value of 90 percent at 3:00 A.M., 4:00 A.M., and 10:00 A.M. each day…  The problem here is that the DBHR is dropping low at 10:00 A.M., a prime-time online period.”

——————–

Oracle Tuning: The Definitive Reference

“A query like the one that follows can be used to see a metric’s behavior for the recent time period.  For example, the following query shows data buffer hit ratio history for the last hour.”

——————–

Expert Oracle Database 11g Administration” – Page 190

“this is why the buffer cache hit ratio, which measures the percentage of time users accessed the data they needed from the buffer cache (rather than requiring a disk read), is such an important indicator of performance of the Oracle instance.”

The author provides a link on page 1161 to an article authored by Cary Millsap which discusses why a higher buffer cache hit ratio may not be ideal. This is definitely a step in the right direction regarding the buffer cache hit ratio, but it might be better to simply ignore the statistic.

——————–

Oracle Database 10g Performance Tuning Tips & Techniques

“Some DBAs (usually those trying to sell you a tuning product) minimize the importance of hit ratios (proactive tuning) and focus completely on waits (reactive tuning), since focusing on waits is a great way to quickly solve the current burning problems. By monitoring the Instance Efficiency section (and using all of STATSPACK and Enterprise Manager), the DBA will combine reactive and proactive tuning and will find some problems before the users scream or wait events hit the top 5 list. Hit ratios are one important piece of the puzzle (so are waits).”

“Hit ratios are a great barometer of the health of your system. A large increase or drop from day to day is an indicator of a major change that needs to be investigated.”

“Hit Ratio = (Logical Reads – Physical Reads) / Logical Reads” “The buffer hit ratio should be above 95 percent. If it is less than 95 percent, you should consider increasing the size of the data cache by increasing the DB_CACHE_SIZE initialization parameter (given that physical memory is available to do this).”

——————–

There are, of course, very strong counter-points to using the buffer cache hit ratio (BCHR) as a tuning metric.  The first two are very well written, detailed articles:

http://jonathanlewis.wordpress.com/2007/09/05/hit-ratios-2/  (a very detailed blog entry)

“Ratios are highly suspect for monitoring purposes. If you think a ratio is helpful, think carefully about whether you should be monitoring the two underlying values instead.

The buffer cache hit ratio (BCHR) is a particularly bad example of the genre as there are so many events that make the attempt to correlate BCHR and performance meaningless.”

——————–

http://richardfoote.wordpress.com/2007/12/16/buffer-cache-hit-ratios-useful-or-not/ (a very detailed blog entry)

“The biggest problem of all with the BCHR is that regardless of it’s values, or whether it goes up or down or remains unchanged, we need to perform precisely the same database checks regardless as it doesn’t tell us whether the ‘health’ of the database has improved, got worse or remains unchanged.”

——————–

http://www.orafaq.com/wiki/Improving_Buffer_Cache_Hit_Ratio

“Many DBAs do their best to get a 99% or better buffer cache hit ratio, but quickly discover that the performance of their database isn’t improving as the hit ratio gets better.”

——————–

“The evidence that hit ratios are unreliable is overwhelming, and similar ratio fallacies occurring in other industries are well documented.”

——————–

Oracle Performance Troubleshooting: With Dictionary Internals SQL & Tuning

“For example, there have been a number of benchmarks done to prove that a 99-100% buffer cache hit ratio does not mean that a database is running well.  A high cache hit ratio can be observed while the database is literally at a standstill.”

——————–

Cary Millsap’s “Why a 99%+ Database Buffer Cache Hit Ratio is Not Ok”

“Many tuning professionals and textbook authors sell advice encouraging their customers to enjoy the performance virtues of Oracle database buffer cache hit ratios that approach 100%. However, database buffer cache hit ratio is not a reliable system performance metric. Buffer cache hit ratios above 99% usually indicate particularly serious SQL inefficiencies”

——————–

“The data buiffer hit ratio has limited value”

——————–

“This ratio should not be used as an indicator of database performance health.  Much Oracle software documentation touts the database buffer cache hit ratio as being one of the most important tuning metrics.  In my opinion and that of many others this statement is often a complete and absolute falsity.”

——————–

“So if someone tells you that the buffer cache hit ratio must be a good thing because Oracle has based their v\$db_cache_advice technology on it, then they are displaying a lack of understanding about the deficiencies of the buffer cache hit ratio in particular, and how LRU (least recently used) caching mechanisms work in general.”

——————–

“Who cares?

If not, then your hit ratio is just fine and dandy, whatever it happens to be.

The more general point here is that, *** and *** notwithstanding, the buffer cache hit ratio is an absolutely abysmal way of tuning anything. It can sometimes offer a useful corollary to other statistics; to allow you to distinguish between two otherwise equally plausible causes for, for example, free buffer waits. But as a performance tuning goal in its own right? Furgedaboudit.”

——————–

“And it only take *one* piece of what I technically define as ‘Crap’ code to both inflate the BHR to incredibly high levels whilst at the same time killing or impacting *database* performance.

I’m probably more sympathetic to BHRs than many. However, it provides only one very small piece in the tuning puzzle, one that needs to be put into perspective. It can be used as an indicator of whether the buffer cache is set way to low/high and nothing more. And what it’s actual *value* is of little consequence, there is no such thing as an ideal value x.

Does a 99.9% BHR mean the database/buffer cache/sql is well tuned. Possibly.

Does a 99.9% BHR mean the database/buffer cache/sql is poorly tuned. Possibly.

So what does a 99.9% BHR actually mean and represent? Without being able to answer this question in it’s fullness, the figure is meaningless.

You get the point.”

——————–

Tuning Oracle Without Cache-Hit Ratios
http://www.quest.com/whitepapers/TuningOracleWithoutCacheHit_new.pdf

“From time immemorial, Oracle performance tuning has the infamous label of witchcraft, secretly practiced by an elite group of individuals, who allegedly use voodoo to cure the performance problems of an Oracle system. To compound this misperception there exists many thousands of pages of published material that propagate the idea of tuning Oracle with cache-hit ratios.”

Bottom line, Oracle tuning efforts need to be based on isolating and pinpointing bottlenecks (the disease) not cache-hit ratios (the symptoms).”

——————–

Of course, tools are available to help correct a low buffer cache hit ratio:

From the book “Optimizing Oracle Performance