????????????Oracle?????????????????????????????????????????SQL?????м???е??????裬????????????????????????????????????????????????????????????????????????????Ч??????????????????????????????????????б?????????????????????????????????????????????????????????裬???????????Ч??

????1???????????裿

?????????????????????е??У?????????????????е?????飬???Oracle??С??洢??λ??Oracle block??

??????????е????????????λ???????????飬???????????????б??????????????????????I/O?????

??????????????????????￡???????Щ?????????鱻?????(sequentially)????????????????I/O?????????????顣

??????ζ???????????????????????????????I/O???????????????鱻?????????DB_FILE_MULTIBLOCK_READ_COUNT??

????2??????????????裿

????a????????????Ч???????????????(???ν????ú?????????NULL?????????????????????)

????b?????????????????????????????

????c?????????з???????

????d?????full ???

????e??????????????Oracle????????????????????Ч

????f????????????С??DB_FILE_MULTIBLOCK_READ_COUNT??????ο????????????

????3????????????????

a????????????
scott@ORA11G> select * from v$version where rownum<2;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

--??????t
scott@ORA11G> CREATE TABLE t
  2  AS
  3  SELECT rownum AS n?? rpad('*'??100??'*') AS pad
  4  FROM dual
  5  CONNECT BY level <= 1000;

Table created.

--???????
scott@ORA11G> create unique index t_pk on t(n);

Index created.

scott@ORA11G> alter table t add constraint t_pk primary key(n) using index t_pk;

Table altered.

--?????????
scott@ORA11G> execute dbms_stats.gather_table_stats('SCOTT'??'T'??cascade=>true);

PL/SQL procedure successfully completed.

scott@ORA11G> set autot trace exp;
scott@ORA11G> select count(*) from t;   --->count(*)?????????????????????

Execution Plan
----------------------------------------------------------
Plan hash value: 454320086
----------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Cost (%CPU)| Time     |
----------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |     1 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |      |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| T_PK |  1000 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------

scott@ORA11G> set autot off;
scott@ORA11G> alter table t move;  --->????move table

Table altered.

-->move ????????Ч?????????
scott@ORA11G> @idx_info         
Enter value for owner: scott
Enter value for table_name: t

Table Name    INDEX_NAME     CL_NAM               CL_POS STATUS   IDX_TYP         DSCD
------------- -------------- -------------------- ------ -------- --------------- ----
T             T_PK           N                         1 UNUSABLE NORMAL          ASC


b???????Ч??????????
scott@ORA11G> set autot trace exp;
scott@ORA11G> select count(*) from t;

Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     7   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T    |  1000 |     7   (0)| 00:00:01 |
-------------------------------------------------------------------

scott@ORA11G> set autot off;
scott@ORA11G> alter index t_pk rebuild;   -->???????

Index altered.

scott@ORA11G> @idx_info
Enter value for owner: scott
Enter value for table_name: t

Table Name     INDEX_NAME       CL_NAM               CL_POS STATUS   IDX_TYP         DSCD
-------------- ---------------- -------------------- ------ -------- --------------- ----
T              T_PK             N                         1 VALID    NORMAL          ASC


c?????????????????????????????????
scott@ORA11G> select count(pad) from t where n<=990;

Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |   105 |     7   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |   105 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |   991 |   101K|     7   (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("N"<=990)

--????С????????????????????????
scott@ORA11G> select count(pad) from t where n<=10;

Execution Plan
----------------------------------------------------------
Plan hash value: 4270555908
-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |     1 |   105 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE              |      |     1 |   105 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T    |    10 |  1050 |     3   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T_PK |    10 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("N"<=10)


d????ò??з???????????????????
scott@ORA11G> select /*+ parallel(3) */ count(pad) from t where n<=10;

Execution Plan
----------------------------------------------------------
Plan hash value: 3126468333
----------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |     1 |   105 |     3   (0)| 00:00:01 |        |      |            |
|   1 |  SORT AGGREGATE        |          |     1 |   105 |            |          |        |      |            |
|   2 |   PX COORDINATOR       |          |       |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM) | :TQ10000 |     1 |   105 |            |          |  Q1??00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE     |          |     1 |   105 |            |          |  Q1??00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR |          |    10 |  1050 |     3   (0)| 00:00:01 |  Q1??00 | PCWC |            |
|*  6 |       TABLE ACCESS FULL| T        |    10 |  1050 |     3   (0)| 00:00:01 |  Q1??00 | PCWP |            |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   6 - filter("N"<=10)
Note
-----
   - Degree of Parallelism is 3 because of hint
--Author : Robinson
--Blog   :http://blog.csdn.net/robinson_0612


e?????full???????????????
scott@ORA11G> select /*+ full(t) */ count(pad) from t where n<=10;

Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |   105 |     7   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |   105 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |    10 |  1050 |     7   (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("N"<=10)       


f?????????????????????????
scott@ORA11G> exec dbms_stats.delete_table_stats('SCOTT'??'T');

PL/SQL procedure successfully completed.

scott@ORA11G> select count(pad) from t where n<=10;

Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    65 |     7   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |    65 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |    10 |   650 |     7   (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("N"<=10)
Note
-----
   - dynamic sampling used for this statement (level=2)

--???????м????????????裬????????????????????????????????
--??????????С??DB_FILE_MULTIBLOCK_READ_COUNT??????ο????????????????β????