RE: Buffer Sort explanation

From: Karen Morton <Karen.Morton_at_hotsos.com>
Date: Sun, 7 Aug 2005 14:49:38 -0500
Message-ID: <H000006f000ed581.1123444174.hotsos01.hotsos.com@MHS>

Here's a test I did to try and understand this BUFFER SORT behavior. In the end, I noticed differences in when the optimizer chose to use a BUFFER SORT operation primarily when it was operating under an IO cost model and when the filter condition in the predicate was against a column that did not have an index and when the filter condition cardinality was estimated at 1 row.

The behavior evidenced by the IO cost model is what I saw consistently in Oracle v9 but in v10, I'm not seeing the BUFFER SORT operations chosen as frequently. This may be an example of the fine-tuning of the algorithms Lex mentioned.

This example is a bit long, but it illustrates the behavior and seems to indicate that revisits to PGA (under certain circumstances) appears to be favored over shared memory accesses.

Karen Morton
Hotsos Enterprises, Ltd.
http://www.hotsos.com
Upcoming events at http://www.hotsos.com/education/schedule.html  

• Test executed using Oracle 10.1.0.2 on Windows XP.
• First, I verify the setting for the _optimizer_cost_model parameter.
• It is set at the default value of CHOOSE.

SQL> @hparam _optimizer_cost_model
More:

Parameter Name                                     Parameter Value 
_optimizer_cost_model                              CHOOSE   

1 row selected.

SQL>

• This is the first test query. Note the filter condition for class_desc = 'English 101'.

SQL> get test1
  1 select c.class_desc, c.credit_hrs, s.student_name, avg(g.grade_received) grade_avg   2 from class c, student1 s, grades g

  3   where g.student_id = s.student_id
  4     and c.class_desc = 'English 101'
  5     and c.class_id = g.class_id
  6   group by cube (c.class_desc, c.credit_hrs, s.student_name)

  7 having grouping(c.class_desc) = 0
  8* and grouping(c.credit_hrs) = 0
SQL>

• This shows that the CLASS table has only one index on CLASS_ID.

SQL> @hix class

Index                            Flags Height Column Name
------------------------------ ------- ------ ------------------------------
CLASS_ID_PK                          U      1 CLASS_ID

SQL>
SQL>

• Now, I do an explain plan for this query.
• Note how the 'English 101' filter was applied at step 6 within the NESTED LOOP.

SQL> @hxplan
Enter .sql file name (without extension) []: test1 Enter the display level (TYPICAL, ALL, BASIC, SERIAL) [TYPICAL] :

---

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

---

| 0 | SELECT STATEMENT | | 3 | 138 | 8 (25)| 00:00:01 |

|*  1 |  FILTER                        |           |       |       |            |          |


|   2 |   SORT GROUP BY ROLLUP         |           |     3 |   138 |     8  (25)| 00:00:01 |

|*  3 |    HASH JOIN                   |           |    12 |   552 |     7  (15)| 00:00:01 |


|   4 |     TABLE ACCESS BY INDEX ROWID| GRADES    |    12 |   108 |     2   (0)| 00:00:01 |

|   5 |      NESTED LOOPS              |           |    12 |   360 |     4   (0)| 00:00:01 |

|*  6 |       TABLE ACCESS FULL        | CLASS     |     1 |    21 |     2   (0)| 00:00:01 |
|*  7 |       INDEX RANGE SCAN         | GRADES_PK |    12 |       |     1   (0)| 00:00:01 |


|   8 |     TABLE ACCESS FULL          | STUDENT1  |    18 |   288 |     2   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

---

   1 - filter(GROUPING("C"."CLASS_DESC")=0 AND GROUPING("C"."CREDIT_HRS")=0)
   3 - access("G"."STUDENT_ID"="S"."STUDENT_ID")
   6 - filter("C"."CLASS_DESC"='English 101')
   7 - access("C"."CLASS_ID"="G"."CLASS_ID")

SQL>
SQL>

• This is the second test query.
• This time the test uses the CLASS_ID instead of description as a filter condition.
• Recall that the CLASS_ID column is indexed.

SQL> get test2
  1 select c.class_desc, c.credit_hrs, s.student_name, avg(g.grade_received) grade_avg   2 from class c, student1 s, grades g

  3   where g.student_id = s.student_id
  4     and c.class_id = 2
  5     and c.class_id = g.class_id
  6   group by cube (c.class_desc, c.credit_hrs, s.student_name)

  7 having grouping(c.class_desc) = 0
  8* and grouping(c.credit_hrs) = 0
SQL>

• This time the explain plan shows the class_id index being used.

SQL> @hxplan
Enter .sql file name (without extension) [TEST1]: test2 Enter the display level (TYPICAL, ALL, BASIC, SERIAL) [TYPICAL] :

---

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

---

| 0 | SELECT STATEMENT | | 3 | 138 | 7 (29)| 00:00:01 |

|*  1 |  FILTER                         |             |       |       |            |          |


|   2 |   SORT GROUP BY ROLLUP          |             |     3 |   138 |     7  (29)| 00:00:01 |

|*  3 |    HASH JOIN                    |             |    12 |   552 |     6  (17)| 00:00:01 |


|   4 |     NESTED LOOPS                |             |    12 |   360 |     3   (0)| 00:00:01 |

|   5 |      TABLE ACCESS BY INDEX ROWID| CLASS       |     1 |    21 |     1   (0)| 00:00:01 |

|*  6 |       INDEX UNIQUE SCAN         | CLASS_ID_PK |     1 |       |     0   (0)| 00:00:01 |


|   7 |      TABLE ACCESS BY INDEX ROWID| GRADES      |    12 |   108 |     2   (0)| 00:00:01 |

|*  8 |       INDEX RANGE SCAN          | GRADES_PK   |    12 |       |     1   (0)| 00:00:01 |


|   9 |     TABLE ACCESS FULL           | STUDENT1    |    18 |   288 |     2   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

---

   1 - filter(GROUPING("C"."CLASS_DESC")=0 AND GROUPING("C"."CREDIT_HRS")=0)
   3 - access("G"."STUDENT_ID"="S"."STUDENT_ID")
   6 - access("C"."CLASS_ID"=2)
   8 - access("G"."CLASS_ID"=2)

SQL>
SQL>

• Now, let's switch the optimizer cost model to IO and execute the same steps again and watch
• out for the appearance of a BUFFER SORT operation.

SQL> alter session set "_optimizer_cost_model" = io ;

Session altered.

SQL> @hparam _optimizer_cost_model
More:

Parameter Name                                     Parameter Value                                    
_optimizer_cost_model                              IO  

1 row selected.

SQL>
SQL> get test1
  1 select c.class_desc, c.credit_hrs, s.student_name, avg(g.grade_received) grade_avg   2 from class c, student1 s, grades g

  3   where g.student_id = s.student_id
  4     and c.class_desc = 'English 101'
  5     and c.class_id = g.class_id
  6   group by cube (c.class_desc, c.credit_hrs, s.student_name)

  7 having grouping(c.class_desc) = 0
  8* and grouping(c.credit_hrs) = 0
SQL>

• Notice the CARTESIAN operation being chosen this time instead of a NESTED LOOP as
• when the cost model was set to CHOOSE.

SQL> @hxplan
Enter .sql file name (without extension) [TEST2]: test1 Enter the display level (TYPICAL, ALL, BASIC, SERIAL) [TYPICAL] :

---

| Id | Operation | Name | Rows | Bytes | Cost |

---

| 0 | SELECT STATEMENT | | 3 | 138 | 9 |

|*  1 |  FILTER                 |          |       |       |       |


|   2 |   SORT GROUP BY ROLLUP  |          |     3 |   138 |     9 |

|*  3 |    HASH JOIN            |          |    12 |   552 |     7 |


|   4 |     MERGE JOIN CARTESIAN|          |    18 |   666 |     4 |

|*  5 |      TABLE ACCESS FULL  | CLASS    |     1 |    21 |     2 |


|   6 |      BUFFER SORT        |          |    18 |   288 |     2 |

|   7 |       TABLE ACCESS FULL | STUDENT1 |    18 |   288 |     2 |

|   8 |     TABLE ACCESS FULL   | GRADES   |   119 |  1071 |     2 |

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

Predicate Information (identified by operation id):

---

   1 - filter(GROUPING("C"."CLASS_DESC")=0 AND

              GROUPING("C"."CREDIT_HRS")=0)

   3 - access("G"."STUDENT_ID"="S"."STUDENT_ID" AND
              "C"."CLASS_ID"="G"."CLASS_ID")
   5 - filter("C"."CLASS_DESC"='English 101')

Note

---

• cpu costing is off (consider enabling it) SQL> SQL>
  • Notice how for the second test, the plan doesn't change (it's the same as under cost model CHOOSE).

SQL> get test2
  1 select c.class_desc, c.credit_hrs, s.student_name, avg(g.grade_received) grade_avg   2 from class c, student1 s, grades g

  3   where g.student_id = s.student_id
  4     and c.class_id = 2
  5     and c.class_id = g.class_id
  6   group by cube (c.class_desc, c.credit_hrs, s.student_name)

  7 having grouping(c.class_desc) = 0
  8* and grouping(c.credit_hrs) = 0
SQL>
SQL> @hxplan
Enter .sql file name (without extension) [TEST1]: test2 Enter the display level (TYPICAL, ALL, BASIC, SERIAL) [TYPICAL] :

---

| Id | Operation | Name | Rows | Bytes | Cost |

---

| 0 | SELECT STATEMENT | | 3 | 138 | 8 |

|*  1 |  FILTER                         |             |       |       |       |


|   2 |   SORT GROUP BY ROLLUP          |             |     3 |   138 |     8 |

|*  3 |    HASH JOIN                    |             |    12 |   552 |     6 |


|   4 |     NESTED LOOPS                |             |    12 |   360 |     3 |

|   5 |      TABLE ACCESS BY INDEX ROWID| CLASS       |     1 |    21 |     1 |

|*  6 |       INDEX UNIQUE SCAN         | CLASS_ID_PK |     1 |       |       |


|   7 |      TABLE ACCESS BY INDEX ROWID| GRADES      |    12 |   108 |     2 |

|*  8 |       INDEX RANGE SCAN          | GRADES_PK   |    12 |       |     1 |


|   9 |     TABLE ACCESS FULL           | STUDENT1    |    18 |   288 |     2 |

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

Predicate Information (identified by operation id):

---

   1 - filter(GROUPING("C"."CLASS_DESC")=0 AND

              GROUPING("C"."CREDIT_HRS")=0)

   3 - access("G"."STUDENT_ID"="S"."STUDENT_ID")
   6 - access("C"."CLASS_ID"=2)
   8 - access("G"."CLASS_ID"=2)

Note

---

• cpu costing is off (consider enabling it) SQL> SQL> SQL> SQL> alter session set "_optimizer_cost_model" = choose ;

Session altered.  

-----Original Message-----
From: Lex de Haan [mailto:lex.de.haan_at_naturaljoin.nl] Sent: Sunday, August 07, 2005 11:07 AM
To: lambu999_at_gmail.com
Cc: 'oracle-l'
Subject: RE: Buffer Sort explanation

that's indeed how I understand it. obviously, there must be some cut off number or threshold value -- and obviously, it is highly undocumented; I don't have a clue :-)

by the way, even if I would have a clue, these algorithms are typically fine-tuned by Oracle development with every release, without letting us know...

kind regards,

Lex.  

---

Steve Adams Seminar http://www.naturaljoin.nl/events/seminars.html

---

-----Original Message-----

Hi Lex,

If Oracle determines that if a block will be accessed multiple times by the _same_ SQL, then it moves it to PGA. If the same can be accessed multiple times by _different_ SQL statements it ends up in SGA? Is there a cut off number for accessing the data block above which Oracle places it to PGA?

On 8/4/05, Lex de Haan <lex.de.haan_at_naturaljoin.nl> wrote:

> a BUFFER SORT typically means that Oracle reads data blocks into 
> private memory, because the block will be accessed multiple times in 
> the context of the SQL statement execution. in other words, Oracle 
> sacrifies some extra memory to reduce the overhead of accessing blocks 
> multiple times in shared memory. this has nothing to do with sorting ...

--
http://www.freelists.org/webpage/oracle-l

Received on Sun Aug 07 2005 - 14:54:14 CDT