RAC环境相同的SQL语句在不同的节点可能产生不同的执行计划

为了好区分，分别在2个节点设置一下SQL提示符：

节点1：

SQL> set sqlprompt 'node1> '

节点2：

SQL>  set sqlprompt 'node2> '

1、在节点 1 创建测试表，并收集统计信息。

node1> ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD HH24:MI:SS';

Session altered.

node1> create table test.t as select * from all_objects where 1=0;

Table created.

node1> create index test.idx_t_01 on test.t(object_id);

Index created.

node1> exec dbms_stats.gather_table_stats('test','T',cascade=>TRUE);

PL/SQL procedure successfully completed.

node1> select table_name,last_analyzed,num_rows from dba_tables where table_name='T';

TABLE_NAME           LAST_ANALYZED         NUM_ROWS
-------------------- ------------------- ----------
T                    2011/02/10 13:10:32          0

node1> select index_name,last_analyzed,num_rows from dba_indexes where index_name='IDX_T_01';

INDEX_NAME           LAST_ANALYZED         NUM_ROWS
-------------------- ------------------- ----------
IDX_T_01             2011/02/10 13:10:32          0

看一下此时查询的执行计划：

node1> set autot traceonly exp
node1> select * from test.t where object_id=1024;

Execution Plan
----------------------------------------------------------
Plan hash value: 3240219059

----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |     1 |   128 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T        |     1 |   128 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_T_01 |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   2 - access("OBJECT_ID"=1024)

2、在节点1插入一批数据，只收集索引的统计信息

node1> insert into test.t select * from all_objects;

358864 rows created.

node1> commit;

Commit complete.

node1> exec dbms_stats.gather_index_stats('test','idx_t_01');

PL/SQL procedure successfully completed.

node1> select table_name,last_analyzed,num_rows from dba_tables where table_name='T';

TABLE_NAME           LAST_ANALYZED         NUM_ROWS
-------------------- ------------------- ----------
T                    2011/02/10 13:10:32          0

node1> select index_name,last_analyzed,num_rows from dba_indexes where index_name='IDX_T_01';

INDEX_NAME           LAST_ANALYZED         NUM_ROWS
-------------------- ------------------- ----------
IDX_T_01             2011/02/10 13:22:04     358864

由于只收集了索引的统计信息，表的统计信息没有收集，此时ORACLE CBO计算出来的走全表扫描的成本低于走索引扫描的成本。

执行计划如下：

node1> set autot traceonly exp
node1> select * from test.t where object_id=1024;

Execution Plan
----------------------------------------------------------
Plan hash value: 2153619298

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |   128 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |     1 |   128 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"=1024)

 

3、刷新共享池，将执行计划都淘汰出去

节点1操作：

node1> select inst_id,sql_id,sql_text from gv$sql where sql_text like '%select * from test.t where%';

   INST_ID SQL_ID               SQL_TEXT
---------- -------------------- ----------------------------------------------------------------------------------------------
         1 9fc8z6xz78qpw        select * from test.t where object_id=1024
         1 52176wkjg1msw        select inst_id,sql_id,sql_text from gv$sql where sql_text like '%select * from test.t where%'
         2 52176wkjg1msw        select inst_id,sql_id,sql_text from gv$sql where sql_text like '%select * from test.t where%'

node1> select inst_id,sql_id,sql_text from gv$sql where sql_id='9fc8z6xz78qpw';

   INST_ID SQL_ID               SQL_TEXT
---------- -------------------- --------------------------------------------------
         1 9fc8z6xz78qpw        select * from test.t where object_id=1024

node1> alter system flush shared_pool;

System altered.

node1> select inst_id,sql_id,sql_text from gv$sql where sql_id='9fc8z6xz78qpw';

no rows selected

节点2操作：
 
node2> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.16

4、在节点1执行SQL语句，观看执行计划，及其各个节点的硬解析次数

node1> select inst_id,name,value from gv$sysstat where name ='parse count (hard)' order by inst_id;

   INST_ID NAME                                          VALUE
---------- ---------------------------------------- ----------
         1 parse count (hard)                             7521
         2 parse count (hard)                            21704

node1> select inst_id,name,value from gv$sysstat where name ='parse count (hard)' order by inst_id;

   INST_ID NAME                                          VALUE
---------- ---------------------------------------- ----------
         1 parse count (hard)                             7521
         2 parse count (hard)                            21704

node1> select inst_id,name,value from gv$sysstat where name ='parse count (hard)' order by inst_id;

   INST_ID NAME                                          VALUE
---------- ---------------------------------------- ----------
         1 parse count (hard)                             7521
         2 parse count (hard)                            21704

node1>  set autot traceonly exp
node1> select * from test.t where object_id=1024;

Execution Plan
----------------------------------------------------------
Plan hash value: 2153619298

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |   128 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |     1 |   128 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"=1024)

node1>  select inst_id,name,value from gv$sysstat where name ='parse count (hard)' order by inst_id;

   INST_ID NAME                                          VALUE
---------- ---------------------------------------- ----------
         1 parse count (hard)                             7523
         2 parse count (hard)                            21705

node1> select inst_id,sql_id,sql_text from gv$sql where sql_id='9fc8z6xz78qpw';

   INST_ID SQL_ID               SQL_TEXT
---------- -------------------- --------------------------------------------------
         1 9fc8z6xz78qpw        select * from test.t where object_id=1024

node1>

可以看到执行计划是全表扫描，硬解析次数增加。并且这条SQL语句值存在节点1。

5、在节点1收集表的统计信息

 

node1> exec dbms_stats.gather_table_stats('test','t',cascade=>true);

PL/SQL procedure successfully completed.

node1>  select table_name,last_analyzed,num_rows from dba_tables where table_name='T';

TABLE_NAME           LAST_ANALYZED         NUM_ROWS
-------------------- ------------------- ----------
T                    2011/02/10 13:48:05     361565

node1> select index_name,last_analyzed,num_rows from dba_indexes where index_name='IDX_T_01';

INDEX_NAME           LAST_ANALYZED         NUM_ROWS
-------------------- ------------------- ----------
IDX_T_01             2011/02/10 13:48:07     358864

node1> select inst_id,sql_id,sql_text from gv$sql where sql_id='9fc8z6xz78qpw';

   INST_ID SQL_ID               SQL_TEXT
---------- -------------------- --------------------------------------------------
         1 9fc8z6xz78qpw        select * from test.t where object_id=1024

可以看到，节点1原来缓存的SQL语句的执行计划并没有被淘汰出去。

此时如果在节点1执行这个SQL语句，Oracle会共享原来sql的执行计划，从统计信息也可以看出：

node1> set autot traceonly stat
node1> select * from test.t where object_id=1024;

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       5834  consistent gets
          0  physical reads
          0  redo size
       1404  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

逻辑读是5834。

node1> select /*+index(t)*/ * from test.t where object_id=1024;

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
       1404  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

如果强制走索引，逻辑读只有5。

到目前为止，select * from test.t where object_id=1024值在节点1执行过，并且节点1缓存的SQL语句的执行计划是全表扫描。

下面看看在节点2执行的情况：

6、在节点2执行查询，观看情况

node2> select inst_id,sql_id,sql_text from gv$sql where sql_id='9fc8z6xz78qpw';

   INST_ID SQL_ID                     SQL_TEXT
---------- -------------------------- ----------------------------------------
         1 9fc8z6xz78qpw              select * from test.t where object_id=1
                                      024

Elapsed: 00:00:00.01

node2> select inst_id,name,value from gv$sysstat where name ='parse count (hard)' order by inst_id;

   INST_ID NAME                                          VALUE
---------- ---------------------------------------- ----------
         1 parse count (hard)                             7763
         2 parse count (hard)                            21719

Elapsed: 00:00:00.06
node2> select inst_id,name,value from gv$sysstat where name ='parse count (hard)' order by inst_id;

   INST_ID NAME                                          VALUE
---------- ---------------------------------------- ----------
         1 parse count (hard)                             7763
         2 parse count (hard)                            21719

Elapsed: 00:00:00.06
node2> select inst_id,name,value from gv$sysstat where name ='parse count (hard)' order by inst_id;

   INST_ID NAME                                          VALUE
---------- ---------------------------------------- ----------
         1 parse count (hard)                             7763
         2 parse count (hard)                            21719

Elapsed: 00:00:00.05

node2> set autot traceonly stat
node2> select * from test.t where object_id=1024;

Elapsed: 00:00:00.05

Statistics
----------------------------------------------------------
        607  recursive calls
          0  db block gets
        133  consistent gets
          0  physical reads
          0  redo size
       1404  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
          1  rows processed

node2> set autot traceonly stat
node2> select * from test.t where object_id=1024;

Elapsed: 00:00:00.00

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
       1404  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

node2> set autot off

node2> select inst_id,sql_id,sql_text from gv$sql where sql_id='9fc8z6xz78qpw';

   INST_ID SQL_ID                     SQL_TEXT
---------- -------------------------- ----------------------------------------
         2 9fc8z6xz78qpw              select * from test.t where object_id=1
                                      024

         1 9fc8z6xz78qpw              select * from test.t where object_id=1
                                      024

Elapsed: 00:00:00.00
node2> select inst_id,name,value from gv$sysstat where name ='parse count (hard)' order by inst_id;

   INST_ID NAME                                          VALUE
---------- ---------------------------------------- ----------
         1 parse count (hard)                             7763
         2 parse count (hard)                            21750

Elapsed: 00:00:00.06

由于数据库后台递归SQL也可能导致硬解析，因此通过parse count (hard)，并不能说明什么问题。

但是通过比较2个SQL语句在2个节点上的逻辑读也可以看出问题来：

节点 1查询：

node1> select * from test.t where object_id=1024;

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       5834  consistent gets
          0  physical reads
          0  redo size
       1404  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

节点2 查询：

node2> select * from test.t where object_id=1024;

Elapsed: 00:00:00.01

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
       1404  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

如果在节点2执行全表扫描，那么逻辑读应该也是5834。

node2> select /*+full(t)*/ * from test.t where object_id=1024;

Elapsed: 00:00:00.82

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       5834  consistent gets
          0  physical reads
          0  redo size
       1404  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

我们还可以通过GV$SQL_PLAN视图 查看SQL语句的执行计划。

node2> SELECT INST_ID,SQL_ID,CHILD_NUMBER,OPERATION,OPTIONS,OBJECT_NAME FROM GV$SQL_PLAN WHERE SQL_ID='9fc8z6xz78qpw' ORDER BY 1;

   INST_ID SQL_ID             CHILD_NUMBER OPERATION            OPTIONS              OBJECT_NAME
---------- ------------------ ------------ -------------------- -------------------- --------------------
         1 9fc8z6xz78qpw                 0 SELECT STATEMENT
         1 9fc8z6xz78qpw                 0 TABLE ACCESS         FULL                 T
         2 9fc8z6xz78qpw                 0 TABLE ACCESS         BY INDEX ROWID       T
         2 9fc8z6xz78qpw                 0 SELECT STATEMENT
         2 9fc8z6xz78qpw                 0 INDEX                RANGE SCAN           IDX_T_01

Elapsed: 00:00:00.01

 

可以看到，同一个SQL语句，在2个节点确实缓存了2个不同的执行计划。

此时在节点1执行select * from test.t where object_id=1024 将会采用全表扫描。

在节点2执行select * from test.t where object_id=1024;将会采用索引扫描。