Oracle 统计信息的相关操作

Oracle 统计信息的相关操作

SQL> select count(*) from t 
  2  /

  COUNT(*)
----------
    17

SQL> set autot  on 
SQL> /

  COUNT(*)
----------
    17


Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522

-------------------------------------------------------------------
| Id  | Operation      | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      | 1 | 3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      | 1 |        |      |
|   2 |   TABLE ACCESS FULL| T    |    17 | 3   (0)| 00:00:01 |
-------------------------------------------------------------------

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


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

SQL> exec dbms_stats.gather_table_stats('hdp','t')
/

PL/SQL procedure successfully completed.

然后执行计划的改变

Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522

-------------------------------------------------------------------
| Id  | Operation      | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      | 1 | 3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      | 1 |        |      |
|   2 |   TABLE ACCESS FULL| T    |    17 | 3   (0)| 00:00:01 |
-------------------------------------------------------------------


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

锁定统计信息

SQL>  exec dbms_stats.lock_table_stats('hdp','T');

PL/SQL procedure successfully completed.




Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522

-------------------------------------------------------------------
| Id  | Operation      | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      | 1 | 3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      | 1 |        |      |
|   2 |   TABLE ACCESS FULL| T    |    17 | 3   (0)| 00:00:01 |
-------------------------------------------------------------------


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

锁定统计信息并不会影响 统计信息的使用 应该是限制了它的更新

SQL>  exec dbms_stats.delete_table_stats('hdp','T');
BEGIN dbms_stats.delete_table_stats('hdp','T'); END;

*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 3784
ORA-06512: at "SYS.DBMS_STATS", line 4617
ORA-06512: at "SYS.DBMS_STATS", line 11872
ORA-06512: at line 1




SQL> exec dbms_stats.unlock_table_stats('hdp','T');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.delete_table_stats('hdp','T');

PL/SQL procedure successfully completed.

SQL> set autot on 
SQL> select count(*) from t 
  2  /

  COUNT(*)
----------
    17


Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522

-------------------------------------------------------------------
| Id  | Operation      | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      | 1 | 3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      | 1 |        |      |
|   2 |   TABLE ACCESS FULL| T    |    17 | 3   (0)| 00:00:01 |
-------------------------------------------------------------------

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


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

SQL> 

这样统计信息就被删除了。