Buffer sort in Oracle execution plan

Source: Internet
Author: User

Buffer sort in Oracle execution plan

The buffer sort in the Oracle execution plan is not actually sorted. It only loads data to the memory and does not scan tables multiple times.

-- Manufacturing data

Drop table test purge;
Drop table test1 purge;
Create table test as select * from dba_objects where rownum <10;
Create table test1 as select * from dba_objects where rownum <10;
Create index ind_t_object_id on test (object_id );
Create index ind_t1_object_id on test1 (object_id );
Exec dbms_stats.gather_table_stats (user, 'test', cascade => true );
Exec dbms_stats.gather_table_stats (user, 'test1', cascade => true );
-- Execute SQL
Select/* + gg_test */* from test t, test1 t1 where t. object_name like't % ';

----------------------------------------------------------
Plan hash value: 702683263
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
------------------------------------------------------------------------------
| 0 | select statement | 9 | 1350 | 6 (0) | 00:00:01 |
| 1 | merge join cartesian | 9 | 1350 | 6 (0) | 00:00:01 |
| * 2 | table access full | TEST | 1 | 75 | 3 (0) | 00:00:01 |
| 3 | buffer sort | 9 | 675 | 3 (0) | 00:00:01 |
| 4 | table access full | TEST1 | 9 | 675 | 3 (0) | 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
2-filter ("T". "OBJECT_NAME" LIKE't % ')
Statistics
----------------------------------------------------------
1 recursive cballs
0 db block gets
3 consistent gets
0 physical reads
0 redo size
1991 bytes sent via SQL * Net to client
348 bytes encoded ed via SQL * Net from client
1 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

-- # Keys = 0 indicates no sorting. in Oracle, how can I determine whether the SQL is actually sorted?
Select projection
From v $ SQL _plan_statistics_all
Where SQL _id in (select SQL _id from v $ SQL where
SQL _text like 'select/* + gg_test */* from test t, test1 t1 where t. object_name like 't% ''')
And operation = 'buffer' and options = 'sort ';

(# Keys = 0) "T1 ". "OWNER" [VARCHAR2, 30], "T1 ". "OBJECT_NAME" [VARCHAR2, 128], "T1 ". "SUBOBJECT_NAME" [VARCHAR2, 30], "T1 ". "OBJECT_ID" [NUMBER, 22], "T1 ". "DATA_OBJECT_ID" [NUMBER, 22], "T1 ". "OBJECT_TYPE" [VARCHAR2, 19], "T1 ". "CREATED" [DATE, 7], "T1 ". "LAST_DDL_TIME" [DATE, 7], "T1 ". "TIMESTAMP" [VARCHAR2, 19], "T1 ". "STATUS" [VARCHAR2, 7], "T1 ". "TEMPORARY" [VARCHAR2, 1], "T1 ". "GENERATED" [VARCHAR2, 1], "T1 ". "SECONDARY" [VARCHAR2, 1], "T1 ". "NAMESPACE" [NUMBER, 22], "T1 ". "EDITION_NAME" [VARCHAR2, 30]

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.