系統優化之---分割槽表加速查詢速度和刪除速度

Aminiy發表於2013-06-05
優化
1、刪除表裡的資料最用下面語句
alter table t_name drop partition p_name;
alter table t_name truncate partition p_name;
delete 方式弊端：消耗大量的系統資源和無法釋放空間

SQL> create user test identified by test account unlock; 
User created.
SQL> grant dba to test;
Grant succeeded.
SQL> grant resource,connect to test;
Grant succeeded.
SQL> conn test/test as sysdba
Connected.
SQL>  create table t as select object_id,object_name from dba_objects;
create table t as select object_id,object_name from dba_objects
Table created.
SQL> exec dbms_stats.gather_table_stats(user,'t');

PL/SQL procedure successfully completed.

SQL> set autotrace trace exp stat;
SQL> delete from t where object_id <10000;

9708 rows deleted.


Execution Plan
----------------------------------------------------------
Plan hash value: 3335594643

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | DELETE STATEMENT   |      |  9715 | 48575 |    96   (2)| 00:00:02 |
|   1 |  DELETE            | T    |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| T    |  9715 | 48575 |    96   (2)| 00:00:02 |
---------------------------------------------------------------------------

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

   2 - filter("OBJECT_ID"<10000)


Statistics
----------------------------------------------------------
        200  recursive calls
      10106  db block gets
        373  consistent gets
          0  physical reads
    2609544  redo size
        678  bytes sent via SQL*Net to client
        608  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
       9708  rows processed
這種情況下，delete操作產生了10106+373條資料塊（db block gets+consistent gets）同是產生了2.6M的redo日誌。
即使用建立索引的方法也無法避免資源消耗
SQL> rollback;

Rollback complete.

SQL> create index ind_t on t(object_id);

Index created.
SQL> exec dbms_stats.gather_index_stats(user,'ind_t')

PL/SQL procedure successfully completed.

SQL>  delete from t where object_id <10000;

9708 rows deleted.


Execution Plan
----------------------------------------------------------
Plan hash value: 3974964266

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | DELETE STATEMENT  |       |  9715 | 48575 |    23   (0)| 00:00:01 |
|   1 |  DELETE           | T     |       |       |            |          |
|*  2 |   INDEX RANGE SCAN| IND_T |  9715 | 48575 |    23   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - access("OBJECT_ID"<10000)


Statistics
----------------------------------------------------------
        282  recursive calls
      10370  db block gets
         90  consistent gets
          0  physical reads
    2777076  redo size
        680  bytes sent via SQL*Net to client
        609  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          8  sorts (memory)
          0  sorts (disk)
       9708  rows processed
上面兩種情況消耗的資源差不多都是1000多個資料塊，相反使用truncate和drop 消耗的資源小的多。

  create table t1(object_id int,object_name varchar2(1000)) partition by range(object_id)
 (partition p1 values less than(10000),
partition p2 values less than(20000),
partition p3 values less than(30000),
partition p4 values less than(40000)
,partition pm values less than(maxvalue));

 create table t1(object_id int,object_name varchar2(1000)) partition by range(object_id) (partition p1 values less than(10000),partition p2 values less than(20000),partition p3 values less than(30000),partition p4 values less than(40000),partition pm values less than(maxvalue));
SQL> insert into t1 select * from t;

62751 rows created.


Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------------
-

| Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time
|

--------------------------------------------------------------------------------
-

|   0 | INSERT STATEMENT         |      | 72459 |  2052K|    95   (0)| 00:00:02
|

|   1 |  LOAD TABLE CONVENTIONAL | T1   |       |       |            |
|

|   2 |   TABLE ACCESS FULL      | T    | 72459 |  2052K|    95   (0)| 00:00:02
|

--------------------------------------------------------------------------------
-



Statistics
----------------------------------------------------------
       1170  recursive calls
      39860  db block gets
      38041  consistent gets
          0  physical reads
   10866864  redo size
        680  bytes sent via SQL*Net to client
        602  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      62751  rows processed


SQL> commit;
啟動SQL TRACE實用工具
對會話啟動SQL TRACE 
SQL> alter session set sql_trace=true;

Session altered.
SQL> alter table t1 truncate partition p1;

SQL> truncate table t1;

Table truncated.

SQL> insert into t1 select * from t;

62751 rows created.

SQL> commit;

Commit complete.

SQL> alter session set sql_trace=true;

Session altered.

SQL> alter table t1 drop partition p1;

Table altered.
關閉SQL TRACE實用工具
對會話關閉SQL TRACE 
SQL> alter session set sql_trace=false;

Session altered.
獲得程式資訊，選擇需要跟蹤的程式:
select s.USERNAME,s.SID,s.SERIAL#,s.COMMAND from v$session s where s.USERNAME='TEST';

USERNAME                              SID    SERIAL#    COMMAND
------------------------------ ---------- ---------- ----------
TEST                                   33         52          2

SQL> show user
USER is "SYS"
設定跟蹤:
SQL>  exec sys.dbms_system.set_sql_trace_in_session(33,52,true)

PL/SQL procedure successfully completed.

可以等候片刻，跟蹤session執行任務,捕獲sql操作…
停止跟蹤:
SQL> exec sys.dbms_system.set_sql_trace_in_session(33,52,false);

PL/SQL procedure successfully completed.

 tkorof工具 分析ORACLE跟蹤檔案並且產生一個更加人性化清晰的輸出結果的可執行工具
[oracle@haoxy trace]$ pwd
/u01/app/diag/rdbms/hxy/hxy/trace
[oracle@haoxy trace]$ tkprof hxy_ora_10283.trc tra.txt print=600 record=sql.txt sys=no
vi tra.txt

alter table t1 truncate partition p1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.06          0          4          1           0
Execute      1      0.03       0.11          5          1         82           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.04       0.18          5          5         83           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 91

alter table t1 drop partition p1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.02       0.12          0          0          1           0
Execute      1      0.04       0.11          1          1         35           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.06       0.23          1          1         36           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 91
由此實驗可以看出DDL消耗資源遠小於DML資源，DDl消耗的資源是對資料字典的修改，這個值基本是不變的，DML是隨著資料量的增大消耗的資源也隨之增大。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/24492954/viewspace-765656/，如需轉載，請註明出處，否則將追究法律責任。

相關文章