Oracle 执行计划中的buffer sort

Oracle SQL优化 专栏收录该内容
130 篇文章 4 订阅

  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]

  • 0
    点赞
  • 0
    评论
  • 0
    收藏
  • 打赏
    打赏
  • 扫一扫,分享海报

©️2021 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页

打赏作者

深圳gg

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值