oracle 统计信息 sort,Oracle 执行计划中的buffer sort

Oracle 执行计划中的buffer sort实际上没有排序，只是把数据加载到内存，不扫描多次表。

Oracle 执行计划中的buffer sort实际上没有排序，只是把数据加载到内存，，不扫描多次表。

--制造数据

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);

--执行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%')

统计信息

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

1 recursive calls

0 db block gets

3 consistent gets

0 physical reads

0 redo size

1991 bytes sent via SQL*Net to client

348 bytes received via SQL*Net from client

1 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

0 rows processed

--#keys=0表示没有排序，Oracle中如何判断SQL是否真正做了排序

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]

本文永久更新链接地址：

本文原创发布php中文网，转载请注明出处，感谢您的尊重！