里面的中文是网页翻译的

准备

create table bricks (

  colour   varchar2(10),

  shape    varchar2(10)

);

create table colours (

  colour        varchar2(10),

  rgb_hex_value varchar2(6)

);

create table cuddly_toys (

  toy_name varchar2(20),

  colour   varchar2(10)

);

create table pens (

  colour   varchar2(10),

  pen_type varchar2(10)

);

insert into cuddly_toys values ( 'Miss Snuggles', 'pink' ) ;

insert into cuddly_toys values ( 'Cuteasaurus', 'blue' ) ;

insert into cuddly_toys values ( 'Baby Turtle', 'green' ) ;

insert into cuddly_toys values ( 'Green Rabbit', 'green' ) ;

insert into cuddly_toys values ( 'White Rabbit', 'white' ) ;

 

insert into colours values ( 'red' , 'FF0000' );

insert into colours values ( 'blue' , '0000FF' );

insert into colours values ( 'green' , '00FF00' );

insert into bricks values ( 'red', 'cylinder' );

insert into bricks values ( 'blue', 'cube' );

insert into bricks values ( 'green', 'cube' );

 

insert into bricks

  select * from bricks;

 

insert into bricks

  select * from bricks;

 

insert into bricks

select * from bricks

insert into pens values ( 'black', 'ball point' );

insert into pens values ( 'black', 'permanent' );

insert into pens values ( 'blue', 'ball point' );

insert into pens values ( 'green', 'permanent' );

insert into pens values ( 'green', 'dry-wipe' );

insert into pens values ( 'red', 'permanent' );

insert into pens values ( 'red', 'dry-wipe' );

insert into pens values ( 'blue', 'permanent' );

insert into pens values ( 'blue', 'dry-wipe' );

 

exec dbms_stats.gather_table_stats ( null, 'pens' ) ;

exec dbms_stats.gather_table_stats ( null, 'colours' ) ;

exec dbms_stats.gather_table_stats ( null, 'bricks' ) ;

exec dbms_stats.gather_table_stats ( null, 'cuddly_toys' ) ;

 

 

使用 DBMS_XPlan 获取基本计划

您可以使用 DBMS_XPlan 获取查询的执行计划。表函数 DISPLAY_CURSOR 从内存中获取请求的 SQL 语句的计划。

 select *

from   bricks b

join   colours c

on     b.colour = c.colour;

select * from table(dbms_xplan.display_cursor);

 

查找语句的 SQL ID

要获取语句的 SQL ID，请在 v$sql 中搜索它：

 select sql_id, sql_text

from   v$sql

where  sql_text like 'select *%bricks b%'

/* exclude this query */

and    sql_text not like '%not this%';

如果您知道语句的 SQL ID，则可以将其直接传递给 DBMS_XPlan 

 

如果您不知道 SQL ID，但有语句的文本，您可以查找它的 SQL ID 并将其传递给 DBMS_XPlan 在一个语句中使用以下查询

select /* colours query */* from colours;

 

select p.* 

from   v$sql s, table ( 

  dbms_xplan.display_cursor ( 

    s.sql_id, s.child_number, 'BASIC' 

  ) 

) p 

where s.sql_text like '%colours query%'  /* enter text from the target statement here */

and   s.sql_text not like '%not this%';

阅读执行计划 

 

 从顶部 ( SELECT STATEMENT ) 开始，沿着树向下到达第一片叶子。这是TABLE ACCESS FULL的的颜色表。

将此表中的行向上传递到第一个叶子的父级HASH JOIN。

查找第一步。这样的下一个未访问过的孩子是TABLE ACCESS FULL了的砖台。

将此表中的行向上传递给其父级HASH JOIN。

步骤 1 的所有子项都已被访问，因此将连接后幸存下来的行传递给SELECT STATEMENT并返回给客户端。

运行这个四表连接来获得它的计划 

select c.*, pen_type, shape, toy_name

from   colours c

join   pens p

on     c.colour = p.colour

join   cuddly_toys t

on     c.colour = t.colour

join   bricks b

on     c.colour = b.colour;

本计划中的操作顺序是

从计划的顶部 ( SELECT STATEMENT ) 开始，向下到第一片叶子。这是TABLE ACCESS FULL所述的COLORS在执行计划步骤4表。

将此表中的行向上传递到第一个叶的父级，即步骤 3 中的HASH JOIN。

查找下一个未访问过的孩子，这是TABLE ACCESS FULL了的CUDDLY_TOYS表中第5步。

将行传递给第 3 步中的HASH JOIN。第 3 步没有更多的孩子，因此将在第 3 步中的HASH JOIN中幸存下来的行返回到第 2步中的HASH JOIN。

搜索步骤2这下一个孩子是TABLE ACCESS FULL了的PENS步骤6台。

将这些行传递给第 2 步中的HASH JOIN。第 2 步没有更多子行，因此将在第 2 步中的HASH JOIN中幸存下来的行返回到第 1步中的HASH JOIN。

重复该过程，直到您运行完所有操作。因此，访问执行计划步骤 ID 的完整顺序是：4、3、5、3、2、6、2、1、7、1 和 0。

阅读复杂的查询计划

select c.colour, count(*)

from   colours c

join   (

  select colour, shape from bricks

  union all

  select colour, toy_name from cuddly_toys

  union all

  select colour, pen_type from pens

) t

on     t.colour = c.colour

group  by c.colour;

 

 

沿着计划向下移动到第一片叶子。这是TABLE ACCESS FULL所述的COLORS在步骤3表中。

将此表中的行向上传递到第一个叶子的父级，即步骤 2 中的HASH JOIN。

找到的下叶，它是TABLE ACCESS FULL所述的砖表在步骤6。

它的父级是一个多子操作——UNION- ALL——所以数据库接下来将执行步骤 7 和 8。（有一个优化——UNION-ALL的并发执行——这意味着数据库可以同时运行所有这些表扫描并行查询中的时间。）

将步骤 6、7 和 8 中表中的行传递到步骤 5 中的UNION-ALL。此步骤将这些行合并为一个数据集。

将树备份到步骤 2 中的HASH JOIN。

连接第 3 步和第 5 步中的行，将幸存的行传递给第 1 步中的HASH GROUP BY。

最后，将数据返回给客户端。

select /*+ gather_plan_statistics */*

from   bricks b

join   colours c

on     b.colour = c.colour;

select * from table(dbms_xplan.display_cursor(format => 'ROWSTATS LAST'));

 

在到目前为止的查询中，数据库在执行期间读取每个表一次。在某些查询中，数据库会多次读取同一个表。

要检查这一点，请查看行统计信息中包含的 Starts 列。这说明在查询运行时操作开始的次数。

此查询使用标量子查询。数据库可以为颜色表中的每一行运行一次。通过查看开始列来验证这一点

select /*+ gather_plan_statistics */c.rgb_hex_value,

       ( select count (*)

         from   bricks b

         where  b.colour = c.colour

       ) brick#

from   colours c;

 

select * from table(dbms_xplan.display_cursor(format => 'ROWSTATS LAST'));