oracle 相同的sql执行两次 执行计划会不一样吗,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;将会采用索引扫描。