SQL优化核心思想 (1).epub

在Oracle数据库中，执行计划是树形结构，因此我们可以利用树形查询来定制执行计划。
我们打开PLSQL dev SQL窗口，登录示例账户Scott并且运行如下SQL。

explain plan for select /*+ use_hash(a,dept) */ *
  from emp a, dept
 where a.deptno = dept.deptno
   and a.sal > 3000;

然后执行下面的脚本，结果如图3-2所示。

select case
         when (filter_predicates is not null or
              access_predicates is not null) then
          '*'
         else
          ' '
       end || id as "Id",
       lpad(' ', level) || operation || ' ' || options "Operation",
       object_name "Name",
       cardinality as "Rows",
       filter_predicates "Filter",
       access_predicates "Access"
  from plan_table 
 start with id = 0
connect by prior id = parent_id;

Picture 1
图3-2
我们曾在1.2节中提到，只有大表才会产生性能问题，因此可以将表的段大小添加到定制执行计划中，这样我们在用定制执行计划优化SQL的时候，可以很方便地知道表大小，从而更快地判断该步骤是否可能是性能瓶颈。下面脚本添加表的段大小以及索引段大小到定制执行计划中，结果如图3-3所示。

select case
         when (filter_predicates is not null or
              access_predicates is not null) then
          '*'
         else
          ' '
       end || id as "Id",
       lpad(' ', level) || operation || ' ' || options "Operation",
       object_name "Name",
       cardinality as "Rows",
       b.size_mb "Size_Mb",
       filter_predicates "Filter",
       access_predicates "Access"
  from plan_table a,
       (select owner, segment_name, sum(bytes / 1024 / 1024) size_mb
          from dba_segments
         group by owner, segment_name) b
 where a.object_owner = b.owner(+)
   and a.object_name = b.segment_name(+)
 start with id = 0
connect by prior id = parent_id;

如图3-3所示，Size_Mb显示表的段大小，单位是MB。
Picture 1
图3-3
我们曾在1.4节中提到建立组合索引避免回表或者建立合适的组合索引减少回表次数。如果一个SQL只访问了某个表的极少部分列，那么我们可以将这些被访问的列联合在一起，从而建立组合索引。下面脚本将添加表的总字段数以及被访问字段数量到定制执行计划中，结果如图3-4所示。

select case
         when access_predicates is not null or filter_predicates is not null then
          '*' || id
         else
          ' ' || id
       end as "Id",
       lpad(' ', level) || operation || ' ' || options "Operation",
       object_name "Name",
       cardinality "Rows",
       b.size_mb "Mb",
       case
         when object_type like '%TABLE%' then
          REGEXP_COUNT(a.projection, ']') || '/' || c.column_cnt
       end as "Column",
       access_predicates "Access",
       filter_predicates "Filter",
       case
         when object_type like '%TABLE%' then
          projection
       end as "Projection"
  from plan_table a,
       (select owner, segment_name, sum(bytes / 1024 / 1024) size_mb
          from dba_segments
         group by owner, segment_name) b,
       (select owner, table_name, count(*) column_cnt
          from dba_tab_cols
         group by owner, table_name) c
 where a.object_owner = b.owner(+)
   and a.object_name = b.segment_name(+)
   and a.object_owner = c.owner(+)
   and a.object_name = c.table_name(+)
 start with id = 0
connect by prior id = parent_id;

Picture 4
图3-4
如图3-4中所示，Column表示访问了表多少列/表一共有多少列。Projection显示了具体的访问列信息，限于书本宽度，图中没有显示Projection列信息。
限于书本限制，定制执行计划本书不做进一步讨论，有兴趣的读者请自行添加其余定制信息到定制执行计划中。

有时需要抓取正在运行的SQL的执行计划，这时我们需要获取SQL的SQL_ID以及SQL的CHILD_NUMEBR，然后将其代入下面SQL，就能获取正在运行的SQL的执行计划。

select * from table(dbms_xplan.display_cursor('sql_id',child_number));

在一个会话中执行如下SQL。

select count(*) from a,b where a.owner=b.owner;

在另外一个会话中执行如下SQL。

select a.sid, a.event, a.sql_id, a.sql_child_number, b.sql_text
  from v$session a, v$sql b
 where a.sql_address = b.address
   and a.sql_hash_value = b.hash_value
   and a.sql_child_number = b.child_number
 order by 1 desc;

接下来我们将SQL_ID和CHILD_NUMBER代入以下SQL。

SQL> select * from table(dbms_xplan.display_cursor('czr9jwxv0xra6',0));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------

SQL_ID  czr9jwxv0xra6, child number 0
-------------------------------------
select count(*) from a,b where a.owner=b.owner

Plan hash value: 319234518

------------------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |       |       |       |  2556 (100)|          |
|   1 |  SORT AGGREGATE     |      |     1 |    34 |       |            |          |
|*  2 |   HASH JOIN         |      |   400M|    12G|  1920K|  2556  (78)| 00:00:31 |
|   3 |    TABLE ACCESS FULL| B    | 67547 |  1121K|       |   187   (1)| 00:00:03 |
|   4 |    TABLE ACCESS FULL| A    | 77054 |  1279K|       |   187   (1)| 00:00:03 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("A"."OWNER"="B"."OWNER")

Note
-----
   - dynamic sampling used for this statement (level=2)

转载：https://www.cnblogs.com/ddzj01/p/11365541.html

一、前言

生产中偶尔会碰到一些sql，有多种执行计划，其中部分情况是统计信息过旧造成的，重新收集下统计信息就行了。但是有些时候重新收集统计信息也解决不了问题，而开发又在嗷嗷叫，没时间让你去慢慢分析原因的时候，这时临时的解决办法是通过spm去固定一个正确的执行计划，等找到真正原因后再解除该spm。

- 阅读剩余部分 -