MySQL 5.6 执行计划错误案例分析

前提


Mysql 优化器本就是为了优化SQL语句的查找路径而存在，当优化器足够智能的时候，这是一件美事。但是，如果优化器犯二的时候呢？有的时候执行计划看上去非常好，但是慢的无可救药。有的时候执行计划看上去很差，却跑的很欢。 接下来我们一起来看一下下面的例子：

  • 表结构
CREATE TABLE `test_table` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `brokerid` int(10) NOT NULL COMMENT '',
  `groupid` int(10) NOT NULL COMMENT '',
  `cid` int(10) NOT NULL COMMENT '',
  `gid` int(10) NOT NULL COMMENT '',
  `fix_prop_num` mediumint(6) NOT NULL COMMENT '',
  `more_10hours_num` mediumint(6) NOT NULL COMMENT '',
  `new_add_num` mediumint(6) NOT NULL COMMENT '',
  `multi_map_num` mediumint(6) NOT NULL COMMENT '',
  `fix_clicks` mediumint(6) NOT NULL COMMENT '',
  `fix_consume` float(8,2) NOT NULL COMMENT '',
  `bid_prop_num` mediumint(6) NOT NULL COMMENT '',
  `bid_clicks` mediumint(6) NOT NULL COMMENT '',
  `bid_consume` float(8,2) NOT NULL COMMENT '',
  `report_date` int(6) NOT NULL COMMENT '',
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '',
  `cst_broker_company_ids` int(10) NOT NULL DEFAULT '0' COMMENT '',
  `new_fix_multi_num` mediumint(6) NOT NULL DEFAULT '0' COMMENT '',
  `new_bid_num` mediumint(6) NOT NULL DEFAULT '0' COMMENT '',
  `bid_multi_num` mediumint(6) NOT NULL DEFAULT '0' COMMENT '',
  `new_bid_multi_num` mediumint(6) NOT NULL DEFAULT '0' COMMENT '',
  PRIMARY KEY (`id`),
  KEY `cid` (`cid`,`report_date`),
  KEY `gid` (`gid`,`report_date`),
  KEY `report_date` (`report_date`),
  KEY `brokerid` (`brokerid`,`report_date`),
  KEY `cst_date` (`cst_broker_company_ids`,`report_date`)
) ENGINE=InnoDB AUTO_INCREMENT=57230309 DEFAULT CHARSET=utf8 COMMENT='test_table'
  • total rows
dbadmin:abc> select count(*) from test_table;
+----------+
| count(*) |
+----------+
| 52023757 |
+----------+
1 row in set (14.04 sec)
  • index
dbadmin:abc> show index from test_table;
+---------------------+------------+--------------+--------------+------------------------+-----------+-------------+----------+--------+------+------------+---------+--------------
-+
| Table               | Non_unique | Key_name     | Seq_in_index | Column_name            | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment
 |
+---------------------+------------+--------------+--------------+------------------------+-----------+-------------+----------+--------+------+------------+---------+--------------
-+
| test_table |          0 | PRIMARY      |            1 | id                     | A         |    51696652 |     NULL | NULL   |      | BTREE      |         |
 |
| test_table |          1 | cid          |            1 | cid                    | A         |       47341 |     NULL | NULL   |      | BTREE      |         |
 |
| test_table |          1 | cid          |            2 | report_date            | A         |     4308054 |     NULL | NULL   |      | BTREE      |         |
 |
| test_table |          1 | gid          |            1 | gid                    | A         |       39016 |     NULL | NULL   |      | BTREE      |         |
 |
| test_table |          1 | gid          |            2 | report_date            | A         |     6462081 |     NULL | NULL   |      | BTREE      |         |
 |
| test_table |          1 | report_date  |            1 | report_date            | A         |      106591 |     NULL | NULL   |      | BTREE      |         |
 |
| test_table |          1 | cst_date     |            1 | cst_broker_company_ids | A         |      181391 |     NULL | NULL   |      | BTREE      |         |
 |
| test_table |          1 | cst_date     |            2 | report_date            | A         |    25848326 |     NULL | NULL   |      | BTREE      |         |
 |
| test_table |          1 | idx_brokerid |            1 | brokerid               | A         |      555877 |     NULL | NULL   |      | BTREE      |         |
 |
| test_table |          1 | idx_brokerid |            2 | report_date            | A         |    51696652 |     NULL | NULL   |      | BTREE      |         |
 |
+---------------------+------------+--------------+--------------+------------------------+-----------+-------------+----------+--------+------+------------+---------+--------------
-+
10 rows in set (0.00 sec)

问题1

  • SQL 1
dbadmin:abc> explain select distinct  `brokerid`  from `test_table`  where `cst_broker_company_ids` in ( '59494' , '59499' , '59502' , '59727' , '60119' , '93204' )  and `report_date` >= '20141101'  and `report_date` <= '20141126'  order by `brokerid` desc limit 15,15;
+----+-------------+---------------------+-------+-------------------------------+----------+---------+------+----------+-------------+
| id | select_type | table               | type  | possible_keys                 | key      | key_len | ref  | rows     | Extra       |
+----+-------------+---------------------+-------+-------------------------------+----------+---------+------+----------+-------------+
|  1 | SIMPLE      | test_table | index | report_date,brokerid,cst_date | brokerid | 8       | NULL | 51696652 | Using where |
+----+-------------+---------------------+-------+-------------------------------+----------+---------+------+----------+-------------+
1 row in set (0.00 sec)

dbadmin:abc> show status like 'Han%';
+----------------------------+----------+
| Variable_name              | Value    |
+----------------------------+----------+
| Handler_commit             | 1        |
| Handler_delete             | 0        |
| Handler_discover           | 0        |
| Handler_external_lock      | 2        |
| Handler_mrr_init           | 0        |
| Handler_prepare            | 0        |
| Handler_read_first         | 0        |
| Handler_read_key           | 1        |
| Handler_read_last          | 1        |
| Handler_read_next          | 0        |
| Handler_read_prev          | 45189200 |  --all index scan
| Handler_read_rnd           | 0        |
| Handler_read_rnd_next      | 1        |
| Handler_rollback           | 0        |
| Handler_savepoint          | 0        |
| Handler_savepoint_rollback | 0        |
| Handler_update             | 0        |
| Handler_write              | 0        |
+----------------------------+----------+
18 rows in set (0.00 sec)

执行时间：15 rows in set (5 min 36.12 sec)
  • SQL 2
dbadmin:abc> explain select distinct  `brokerid`  from `test_table` force index(brokerid) where `cst_broker_company_ids` in ( '59494' , '59499' , '59502' , '59727' , '60119' , '93204' )  and `report_date` >= '20141101'  and `report_date` <= '20141126'  order by `brokerid` desc limit 15,15
    -> ;
+----+-------------+---------------------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table               | type  | possible_keys | key      | key_len | ref  | rows | Extra       |
+----+-------------+---------------------+-------+---------------+----------+---------+------+------+-------------+
|  1 | SIMPLE      | test_table | index | brokerid      | brokerid | 8       | NULL | 3300 | Using where |
+----+-------------+---------------------+-------+---------------+----------+---------+------+------+-------------+
1 row in set (0.00 sec)

dbadmin:abc> show status like 'Han%';
+----------------------------+----------+
| Variable_name              | Value    |
+----------------------------+----------+
| Handler_commit             | 1        |
| Handler_delete             | 0        |
| Handler_discover           | 0        |
| Handler_external_lock      | 2        |
| Handler_mrr_init           | 0        |
| Handler_prepare            | 0        |
| Handler_read_first         | 0        |
| Handler_read_key           | 1        |
| Handler_read_last          | 1        |
| Handler_read_next          | 0        |
| Handler_read_prev          | 45189200 |
| Handler_read_rnd           | 0        |
| Handler_read_rnd_next      | 0        |
| Handler_rollback           | 0        |
| Handler_savepoint          | 0        |
| Handler_savepoint_rollback | 0        |
| Handler_update             | 0        |
| Handler_write              | 0        |
+----------------------------+----------+
18 rows in set (0.00 sec)

执行时间：15 rows in set (5 min 38.85 sec)
  • 总结
  1. 为什么explain中的rows不一样，最终的扫描的Handler_read_prev一样呢？

哈哈，只能说explain 中的limit 欺骗了你。。。 limit optimization

问题二


针对以上案例，为什么Mysql 会选择brokerid 作为索引呢？为什么不用其他的索引呢？我们来强制指定看看

dbadmin:abc> explain select distinct  `brokerid`  from `test_table` force index(cst_date) where `cst_broker_company_ids` in ( '59494' , '59499' , '59502' , '59727' , '60119' , '93204' )  and `report_date` >= '20141101'  and `report_date` <= '20141126'  order by `brokerid` desc limit 15,15
    -> ;
+----+-------------+---------------------+------+-----------------------+------+---------+------+----------+----------------------------------------------+
| id | select_type | table               | type | possible_keys         | key  | key_len | ref  | rows     | Extra                                        |
+----+-------------+---------------------+------+-----------------------+------+---------+------+----------+----------------------------------------------+
|  1 | SIMPLE      | test_table | ALL  | cst_date,idx_brokerid | NULL | NULL    | NULL | 51696652 | Using where; Using temporary; Using filesort |
+----+-------------+---------------------+------+-----------------------+------+---------+------+----------+----------------------------------------------+
1 row in set (0.00 sec)

看样子，还是不行？ 强制索引无效。。。怎么办？那我们就应该去看看Mysql到底是如何一步一步选择执行计划的，还好Mysql 5.6 提供了另外一种追踪途径 optimizer_trace

mysql> SET optimizer_trace="enabled=on";

SQL1:
select distinct  `brokerid`  from `test_table`  where `cst_broker_company_ids` in ( '59494' , '59499' , '59502' , '59727' , '60119' , '93204' )  and `report_date` >= '20141101'  and `report_date` <= '20141126'  order by `brokerid` desc limit 15,15;

mysql> SELECT trace FROM information_schema.OPTIMIZER_TRACE INTO outfile 'trace.json';

最终看到的jason时这样的(截取部分)：
            "clause_processing": {\
              "clause": "GROUP BY",\
              "original_clause": "`test_table`.`brokerid` desc",\
              "items": [\
                {\
                  "item": "`test_table`.`brokerid`"\
                }\
              ],\
              "resulting_clause_is_simple": true,\
              "resulting_clause": "`test_table`.`brokerid` desc"\
            }\
          },\
          {\
            "refine_plan": [\
              {\
                "table": "`test_table`",\
                "access_type": "table_scan"\
              }\
            ]\
          },\
          {\
            "reconsidering_access_paths_for_index_ordering": {\
              "clause": "GROUP BY",\
              "index_order_summary": {\
                "table": "`test_table`",\
                "index_provides_order": true,\
                "order_direction": "desc",\
                "index": "brokerid",\
                "plan_changed": true,\
                "access_type": "index_scan"\

大家可以很清晰的看到，Mysql在之前还是有很多可以选择的索引，但是最后
reconsidering_access_paths_for_index_ordering 中却选择了brokerid，访问路径为index_scan.
奇了个怪了，为啥？google了一把后，发现之前有类似的bug [Bug #70245](http://bugs.mysql.com/
bug.php?id=70245),里面说eq_range_index_dive_limit 会影响range查询计划，官方文档确实也是这
么说的。But，无论我怎么设置eq_range_index_dive_limit的值，丝毫不会影响执行计划

dbadmin:abc> select @@session.eq_range_index_dive_limit;
+-------------------------------------+
| @@session.eq_range_index_dive_limit |
+-------------------------------------+
|                                  10 |
+-------------------------------------+
1 row in set (0.00 sec)
以上SQL测试均来自 @@session.eq_range_index_dive_limit。

设置成200（>in(N)）：set @@session.eq_range_index_dive_limit=200;

设置成0(<in(N))，set @@session.eq_range_index_dive_limit=0；

设置成与IN列表中的个数(=in(N))： set @@session.eq_range_index_dive_limit=6；

以上执行计划没有任何变化，跑出来的时间，和上面一样。

那怎么办呢？

  • 首先

既然brokerid干扰其优化器的选择，如果我将其drop掉,优化器是否能够选择正确的索引呢？

dbadmin:abc> alter table test_table drop index brokerid;
Query OK, 0 rows affected (0.22 sec)
Records: 0  Duplicates: 0  Warnings: 0

dbadmin:abc> explain select distinct  `brokerid`  from `test_table`  where `cst_broker_company_ids` in ( '59494' , '59499' , '59502' , '59727' , '60119' , '93204' )  and `report_date` >= '20141101'  and `report_date` <= '20141126'  order by `brokerid` desc limit 15,15;
+----+-------------+---------------------+-------+----------------------+----------+---------+------+------+--------------------------------------------------------+
| id | select_type | table               | type  | possible_keys        | key      | key_len | ref  | rows | Extra                                                  |
+----+-------------+---------------------+-------+----------------------+----------+---------+------+------+--------------------------------------------------------+
|  1 | SIMPLE      | test_table | range | report_date,cst_date | cst_date | 8       | NULL |  780 | Using index condition; Using temporary; Using filesort |
+----+-------------+---------------------+-------+----------------------+----------+---------+------+------+--------------------------------------------------------+
1 row in set (0.00 sec)

dbadmin:abc> flush status;
Query OK, 0 rows affected (0.00 sec)

dbadmin:abc> select distinct  `brokerid`  from `test_table`  where `cst_broker_company_ids` in ( '59494' , '59499' , '59502' , '59727' , '60119' , '93204' )  and `report_date` >= '20141101'  and `report_date` <= '20141126'  order by `brokerid` desc limit 15,15;
+----------+
| brokerid |
+----------+
|  2112641 |
|  2111870 |
|  2076429 |
|  2072897 |
|  1988209 |
|  1897956 |
|  1816767 |
|  1767494 |
|  1754405 |
|  1709879 |
|  1628017 |
|  1587473 |
|  1582185 |
|  1574712 |
|  1562055 |
+----------+
15 rows in set (0.11 sec)

dbadmin:abc> show status like 'Hand%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 1     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_external_lock      | 2     |
| Handler_mrr_init           | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 0     |
| Handler_read_key           | 6     |
| Handler_read_last          | 0     |
| Handler_read_next          | 781   |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 30    |
| Handler_read_rnd_next      | 36    |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             | 0     |
| Handler_write              | 781   |
+----------------------------+-------+
18 rows in set (0.00 sec)

果然，Mysql选择了正确的索引，跑起来还不错。但是那个索引要经常被用到，不能被删除，结果这条道路是走不通的。

  • 其次

再回头看看trace的选择，里面有关于"clause": "GROUP BY"？ 我就再想，是不是由于Group by的原因呢？不清楚，那就试试呗，于是将distinct去掉，试试看

dbadmin:abc> explain select   `brokerid`  from `test_table` force index(cst_date) where `cst_broker_company_ids` in ( '59494' , '59499' , '59502' , '59727' , '60119' , '93204' )  and `report_date` >= '20141101'  and `report_date` <= '20141126'  order by `brokerid` desc limit 15,15;
+----+-------------+---------------------+-------+---------------+----------+---------+------+------+---------------------------------------+
| id | select_type | table               | type  | possible_keys | key      | key_len | ref  | rows | Extra                                 |
+----+-------------+---------------------+-------+---------------+----------+---------+------+------+---------------------------------------+
|  1 | SIMPLE      | test_table | range | cst_date      | cst_date | 8       | NULL |  780 | Using index condition; Using filesort |
+----+-------------+---------------------+-------+---------------+----------+---------+------+------+---------------------------------------+
1 row in set (0.00 sec)

dbadmin:abc> show status like 'Hand%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 1     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_external_lock      | 2     |
| Handler_mrr_init           | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 0     |
| Handler_read_key           | 6     |
| Handler_read_last          | 0     |
| Handler_read_next          | 781   |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_next      | 0     |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             | 0     |
| Handler_write              | 0     |
+----------------------------+-------+
18 rows in set (0.00 sec)

情况貌似好转了，但是这样子是不满足业务逻辑的呀。。。。
于是，再仔细看看SQL语句的，发现order by 和 group by 重合了，，，为啥不利用group by来排序呢？
so，SQL语句这样修改一下

dbadmin:abc> explain select   `brokerid`    from     `test_table`  where `cst_broker_company_ids` in ( '59494' , '59499' , '59502' , '59727' , '60119' , '93204' )  and `report_date` >= '20141101'  and `report_date` <= '20141126'   group  by `brokerid` desc limit 15,15;
+----+-------------+---------------------+-------+-----------------------------------+----------+---------+------+------+--------------------------------------------------------+
| id | select_type | table               | type  | possible_keys                     | key      | key_len | ref  | rows | Extra                                                  |
+----+-------------+---------------------+-------+-----------------------------------+----------+---------+------+------+--------------------------------------------------------+
|  1 | SIMPLE      | test_table | range | report_date,cst_date,idx_brokerid | cst_date | 8       | NULL |  780 | Using index condition; Using temporary; Using filesort |
+----+-------------+---------------------+-------+-----------------------------------+----------+---------+------+------+--------------------------------------------------------+
1 row in set (0.01 sec)

dbadmin:abc> show status like 'Hand%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 1     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_external_lock      | 2     |
| Handler_mrr_init           | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 0     |
| Handler_read_key           | 6     |
| Handler_read_last          | 0     |
| Handler_read_next          | 781   |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 15    |
| Handler_read_rnd_next      | 36    |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             | 0     |
| Handler_write              | 781   |
+----------------------------+-------+
18 rows in set (0.00 sec)
  • 从性能上看
优化前的SQL：
dbadmin:abc> select distinct  `brokerid`  from `test_table`  where `cst_broker_company_ids` in ( '59494' , '59499' , '59502' , '59727' , '60119' , '93204' )  and `report_date` >= '20141101'  and `report_date` <= '20141126'  order by `brokerid` desc limit 15,15;

+----------+
| brokerid |
+----------+
|  2112641 |
|  2111870 |
|  2076429 |
|  2072897 |
|  1988209 |
|  1897956 |
|  1816767 |
|  1767494 |
|  1754405 |
|  1709879 |
|  1628017 |
|  1587473 |
|  1582185 |
|  1574712 |
|  1562055 |
+----------+
15 rows in set (5 min 42.10 sec)

优化后的SQL：
dbadmin:abc> select  `brokerid`  from `test_table`  where `cst_broker_company_ids` in ( '59494' , '59499' , '59502' , '59727' , '60119' , '93204' )  and `report_date` >= '20141101'  and `report_date` <= '20141126' group by `brokerid` desc limit 15,15;
+----------+
| brokerid |
+----------+
|  2112641 |
|  2111870 |
|  2076429 |
|  2072897 |
|  1988209 |
|  1897956 |
|  1816767 |
|  1767494 |
|  1754405 |
|  1709879 |
|  1628017 |
|  1587473 |
|  1582185 |
|  1574712 |
|  1562055 |
+----------+
15 rows in set (0.01 sec)

PS：为了保证SQL的效率的准确性，以上SQL均重启后第一次跑的时间为准。
  • 总结

    1. distinct，orderby ，group by，limit 这几个条件放在一起，会给Mysql 优化器带来很大的负担，建议尽量不要这样使用。
时间： 2024-09-20 08:49:22

MySQL 5.6 执行计划错误案例分析的相关文章

执行计划错误—索引统计信息的不准确

mysql在生成执行计划的时候,需要根据索引的统计信息进行一个估算,计算出成本最低的索引: 但是mysql索引统计信息的采集默认8个page: root@test 08:48:52>show global variables like '%samp%'; +---------+---+ | Variable_name             | Value | +---------+---+ | innodb_stats_sample_pages | 8     | +---------+---

关于SQL执行计划错误导致临时表空间不足的问题_oracle

故障现象:临时表空间不足的问题已经报错过3次,客户也烦了,前两次都是同事添加5G的数据文件,目前已经达到40G,占用临时表空间主要是distinct 和group by 以及Union all 表数据量在200W左右,也不至于把40G的临时表空间撑爆. 原因分析:既然排序用不了这么多临时表空间应该是别的原因造成. 从包含故障时间段的AWR报告中可以看出这一阶段DBtime蛮高的,并且sql execute elapsed time 竟然占到了99.43%,可以断定是SQL语句引起的. 通过TOP

什么是MySQL的explain执行计划

Explain语法 EXPLAIN  SELECT -- 变体: 1. EXPLAIN EXTENDED SELECT -- 将执行计划"反编译"成SELECT语句,运行SHOW WARNINGS 可得到被MySQL优化器优化后的查询语句 2. EXPLAIN PARTITIONS SELECT -- 用于分区表的EXPLAIN 执行计划包含的信息 id 包含一组数字,表示查询中执行select子句或操作表的顺序 id相同,执行顺序由上至下 如果是子查询,id的序号会递增,id值越大优

MySQL远程代码执行／提取的分析与实践

0x00 背景 2016年9月12日,国外安全研究人员Dawid Golunski发布安全公告发现了MySQL的一个可被远程代码执行/权限提升的漏洞(CVE-2016-6662).笔者在研究了原报告后,做了如下分析和实践. 0x01 分析 漏洞披露原址: http://legalhackers.com/advisories/MySQL-Exploit-Remote-Root-Code-Execution-Privesc-CVE-2016-6662.html 影响范围 (漏洞作者9月16日的最新更

MySQL · 最佳实战 · 审计日志实用案例分析

审计日志是RDS安全策略中非常重要的一环,它采集了数据库中所有的访问请求,包括常见的insert,update,delete,select,alter,drop,create语句, 还有一些比如set,commit,rollback命令语句.有了这些日志后可以帮助我们进行问题回溯,分析问题.下面这则案例讲述如何使用审计日志来分析只读实例延迟问题,如果没有审计日志我们很难想象该问题该如何解决. 问题描述: 一客户使用了2个RDS只读节点来承担业务的读流量,两个RDS的资源规格和业务流量完全一样,但

MySQL的远程连接出现错误提示分析

技巧问题 Mysql的远程连接出现"Lost connection to MySQL server during query" 安装完了RH 8.0用自带的mysql以后直接在本机上可以正常使用mysql登陆,用 mysql -u root -p 添加了一些用户,如* beginner pass 即beginner可以从任何地方通过密码可以访问这台Mysql服务器(假设我的mysql服务器为mysql.test.com)但当我运行 mysql -h mysql.test.com -u

MySQL执行计划extra中的using index 和 using where using index 的区别

原文:MySQL执行计划extra中的using index 和 using where using index 的区别   本文出处:http://www.cnblogs.com/wy123/p/7366486.html (保留出处并非什么原创作品权利,本人拙作还远远达不到,仅仅是为了链接到原文,因为后续对可能存在的一些错误进行修正或补充,无他)   mysql执行计划中的extra列中表明了执行计划的每一步中的实现细节,其中包含了与索引相关的一些细节信息其中跟索引有关的using index

通过分析SQL语句的执行计划优化SQL(二)

优化|语句|执行 第5章 ORACLE的执行计划 背景知识:        为了更好的进行下面的内容我们必须了解一些概念性的术语: 共享sql语句    为了不重复解析相同的SQL语句(因为解析操作比较费资源,会导致性能下降),在第一次解析之后,ORACLE将SQL语句及解析后得到的执行计划存放在内存中.这块位于系统全局区域SGA(system global area)的共享池(shared buffer pool)中的内存可以被所有的数据库用户共享.因此,当你执行一个SQL语句(有时被称为一个

ORACLE执行计划的执行步骤

有了这些背景知识后就开始介绍执行计划.为了执行语句,Oracle可能必须实现许多步骤.这些步骤中的每一步可能是从数据库中物理检索数据行,或者用某种方法准备数据行,供发出语句的用户使用.Oracle用来执行语句的这些步骤的组合被称之为执行计划.执行计划是SQL优化中最为复杂也是最为关键的部分,只有知道了ORACLE在内部到底是如何执行该SQL语句后,我们才能知道优化器选择的执行计划是否为最优的.执行计划对于DBA来说,就象财务报表对于财务人员一样重要.所以我们面临的问题主要是:如何得到执行计划:如