oracle performance tuning效能優化學習系列(一)

oracle效能優化學習系列（一）

C:\Users\123\Desktop\每天工作明細\文件\oracle文件\oracle11g官方文件\server.112\e16638\perf_overview.htm

Understanding Scalability

?
What is Scalability?

?
System Scalability

?
Factors Preventing Scalability
-----------------------
What is Scalability?
可伸縮性即隨著系統資源使用率成比例增加時，系統可以處理或接受更多工作貝負荷的能力；
換句說講，在一個可伸縮性的系統中，如負荷加倍，系統資源使用率也同樣加倍；

如下列出一些導致降低伸縮性的示例或原因：
1，隨著使用者增加，導致併發訪問的提升；即多個使用者同時訪問應用系統；
2，鎖定資源的活動更多了，即保護資料的一致性；
3，保持資料一致性的工作增多了，即多會話多使用者同時訪問應用，如何保持期資料的一致性
4，作業系統工作量增加了
5，因為資料量增加，相對應的事務數量也增多了
6，編寫差的SQL導致邏輯IO及物理IO增加
7，維護資料庫物件要花費更多的時間

資源消耗的示例如下：
1，硬體消耗
2，大量事務產生的表掃描導致IO不足
3，過度的網路請求，
4，記憶體分配不合理，產生分頁和交換活動
5，過量程式及執行緒分配，讓作業系統CRASH

影響可伸縮性的一些因素
Factors Preventing Scalability
1,不合理的應用設計,實施和配置
2,應用對可伸縮性產生了極大的影響，如：
　　a,不合理的模式使用者設計導致ＳＱＬ不能有效伸縮
    b,不合理的事務設計導致產生鎖及序列化問題
    c,不合理的連線管理導致極差的響應時間及令系統不可靠

C:\Users\123\Desktop\每天工作明細\文件\oracle文件\oracle11g官方文件\server.112\e16638\design.htm

使用不同型別的索引
Using a Different Index Type

基於函式的索引
1,在使用此索引有些限制，但據我測試與b-tree index相同，未見區別

反向鍵索引
Reverse Key Indexes
1,防止insert出現熱點塊問題
2,在插入方面表現優異
3,但不能用於索引範圍掃描

附上測試示例
SQL> create table t_reverse(a int);

Table created

SQL> set time on
14:17:32 SQL> set timing on
14:17:35 SQL> create index idx_t_reverse on t_reverse(a);

Index created

Executed in 0.015 seconds

14:19:22 SQL> begin
           2 for i in 1..1000000 loop
           3 insert into t_reverse values(i);
           4 if mod(i,10000)=0 then
           5   commit;
           6 end if;
           7 end loop;
           8 end;
           9 /

PL/SQL procedure successfully completed

Executed in 122.414 seconds

14:26:15 SQL> create index idx_t_reverse on t_reverse(a) reverse;

Index created

Executed in 0.202 seconds

14:26:41 SQL> ed
14:26:50 SQL>
14:26:50 SQL> begin
           2 for i in 1..1000000 loop
           3 insert into t_reverse values(i);
           4 if mod(i,10000)=0 then
           5   commit;
           6 end if;
           7 end loop;
           8 end;
           9 /

PL/SQL procedure successfully completed

Executed in 130.073 seconds

反饋鍵索引如where條件為範圍式則不使用索引，一定要小心使用
14:37:05 SQL> explain plan for select count(a) from t_reverse;

Explained

Executed in 0.016 seconds

14:38:43 SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3632442583
--------------------------------------------------------------------------------
| Id | Operation          | Name      | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     1 |    13 |   292   (5)| 00:00:04 |
|   1 | SORT AGGREGATE    |           |     1 |    13 |            |          |
|   2 |   TABLE ACCESS FULL| T_REVERSE |   853K|    10M|   292   (5)| 00:00:04 |
--------------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement (level=2)

13 rows selected

Executed in 0.265 seconds

14:38:45 SQL> explain plan for select count(a) from t_reverse where a=3;

Explained

Executed in 0.016 seconds

14:39:12 SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 866930652
--------------------------------------------------------------------------------
| Id | Operation         | Name          | Rows | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |               |     1 |    13 |     1   (0)| 00:00:0
|   1 | SORT AGGREGATE   |               |     1 |    13 |            |
|* 2 |   INDEX RANGE SCAN| IDX_T_REVERSE |    18 |   234 |     1   (0)| 00:00:0
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("A"=3)
Note
-----
   - dynamic sampling used for this statement (level=2)

18 rows selected

Executed in 0.296 seconds

14:39:14 SQL> explain plan for select count(a) from t_reverse where a<3;

Explained

Executed in 0.015 seconds

14:39:22 SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3632442583
--------------------------------------------------------------------------------
| Id | Operation          | Name      | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     1 |    13 |   296   (6)| 00:00:04 |
|   1 | SORT AGGREGATE    |           |     1 |    13 |            |          |
|* 2 |   TABLE ACCESS FULL| T_REVERSE |    18 |   234 |   296   (6)| 00:00:04 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("A"<3)
Note
-----
   - dynamic sampling used for this statement (level=2)

18 rows selected

Executed in 0.297 seconds

索引的序列化問題：
１，如使用序列，timestamp作為產生主鍵的索引值；則會產生熱點塊；
　　可採用反向鍵解決或cycling sequence迴圈序列

--附上測試示例,cycle必須同時指定cache及maxvalue
14:45:10 SQL> create sequence seq_t1 start with 1 cycle;

create sequence seq_t1 start with 1 cycle

ORA-04015: ascending sequences that CYCLE must specify MAXVALUE

14:45:21 SQL> create sequence seq_t1 start with 1 maxvalue 10 cycle;

create sequence seq_t1 start with 1 maxvalue 10 cycle

ORA-04013: number to CACHE must be less than one cycle

14:45:44 SQL> create sequence seq_t1 start with 1 maxvalue 10 cycle cache 3;

Sequence created

Executed in 0.046 seconds

14:45:59 SQL> select seq_t1.nextval from dual;

   NEXTVAL
----------
         1

Executed in 0.047 seconds

14:46:15 SQL> select seq_t1.nextval from dual;

   NEXTVAL
----------
         2

Executed in 0.047 seconds

14:46:16 SQL> select seq_t1.nextval from dual;

   NEXTVAL
----------
         3

Executed in 0.078 seconds

14:46:17 SQL> select seq_t1.nextval from dual;

   NEXTVAL
----------
         4

Executed in 0.047 seconds

14:46:18 SQL> select seq_t1.nextval from dual;

   NEXTVAL
----------
         5

Executed in 0.047 seconds

14:46:19 SQL> select seq_t1.nextval from dual;

   NEXTVAL
----------
         6

Executed in 0.062 seconds

14:46:20 SQL> select seq_t1.nextval from dual;

   NEXTVAL
----------
         7

Executed in 0.187 seconds

14:46:21 SQL> select seq_t1.nextval from dual;

   NEXTVAL
----------
         8

Executed in 0.047 seconds

14:46:22 SQL> select seq_t1.nextval from dual;

   NEXTVAL
----------
         9

Executed in 0.063 seconds

14:46:28 SQL> select seq_t1.nextval from dual;

   NEXTVAL
----------
        10

Executed in 0.047 seconds

14:46:28 SQL> select seq_t1.nextval from dual;

   NEXTVAL
----------
         1

Executed in 0.063 seconds

14:46:29 SQL> select seq_t1.nextval from dual;

   NEXTVAL
----------
         2

Executed in 0.047 seconds

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

oracle performance tuning效能優化學習系列(一)

相關文章