RE: Buffer Sort explanation
- From: "Karen Morton" <Karen.Morton@xxxxxxxxxx>
- To: lambu999@xxxxxxxxx, lex.de.haan@xxxxxxxxxxxxxx
- Date: Sun, 7 Aug 2005 14:49:38 -0500
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@xxxxxxxxxxxxxx]
Sent: Sunday, August 07, 2005 11:07 AM
To: lambu999@xxxxxxxxx
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@xxxxxxxxxxxxxx> 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 ...
--
//www.freelists.org/webpage/oracle-l
Other related posts:
