oracle performance tuning性能优化学习系列(一)

最新推荐文章于 2024-09-07 21:55:07 发布

clg10051

最新推荐文章于 2024-09-07 21:55:07 发布

阅读量130

点赞数

文章标签：操作系统数据库

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9240380/viewspace-755437/

clg10051

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
oracle performance tuning性能优化学习系列(一)

oracle性能优化学习系列（一）C:\Users\123\Desktop\每天工作明细\文档\oracle文档\oracle11g官方文档\server.112\e16638\perf_overview.htmUnde...
复制链接

扫一扫