曾有一次某应用系统，速度很慢
[root@ADSL ~]# top
Tasks: 187 total, 1 running, 186 sleeping, 0 stopped, 0 zombie
Cpu(s): 13.7% us, 2.2% sy, 0.0% ni, 64.0% id, 19.9% wa, 0.2% hi, 0.0% si
Mem: 4148848k total, 4131796k used, 17052k free, 3160k buffers
Swap: 8385920k total, 38396k used, 8347524k free, 3810740k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
837 oracle 15 0 1277m 568m 566m S 11 14.0 0:19.92 oracle
1215 oracle 15 0 1277m 565m 563m D 11 14.0 0:19.54 oracle
1305 oracle 15 0 1277m 564m 563m S 11 13.9 0:19.87 oracle
1039 oracle 15 0 1277m 565m 564m D 10 14.0 0:19.28 oracle
[root@ADSL ~]# sar -u 2 10
Linux 2.6.9-42.ELsmp (ADSL) 07/02/2006
01:08:04 PM CPU %user %nice %system %iowait %idle
01:08:06 PM all 5.00 0.00 0.62 43.88 50.50
通过上面的信息发现主要问题是I/O的争用比较厉害
SQL> select sid,event from v$session_wait;
SID EVENT
---------- ----------------------------------------------------------------
89 read by other session
93 read by other session
94 read by other session
.
.
.
说明有大表全表扫描
通过下面sql查得disk较多的性能差sql语句
SQL> SELECT * FROM (select sql_text FROM v$sqlarea
3 order BY disk_reads DESC )where ROWNUM<2;
SQL_TEXT
--------------------------------------------------------------------------------
select a.id,a.content,b.name from article a,col_type b where a.type_id=b.id and
a.ARTICLE_ID=25632458
接下来查看上面这条sql的执行方案
SQL> set autotrace on;
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report
运行 /ORACLE_HOME/rdbms/admin/utlxplan.sql;ORACLE_HOME/sqlplus/admin/plustrce.sql
SQL> set autotrace on;
SQL> set autotrace traceonly;
SQL> set timing on;
SQL> select a.id,a.content,b.name from article a,col_type b where a.type_id=b.id and a.ARTICLE_ID=256324;
Elapsed: 00:04:00.52
Execution Plan
----------------------------------------------------------
Plan hash value: 3360883512
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 62 | 143K (4)| 00:28:45 |
|* 1 | HASH JOIN | | 1 | 62 | 143K (4)| 00:28:45 |
|* 2 | TABLE ACCESS FULL| ARTICLE | 1 | 54 | 143K (4)| 00:28:45 |
| 3 | TABLE ACCESS FULL| COL_TYPE | 8 | 64 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("B"."ID"=TO_NUMBER("A"."TYPE_ID"))
2 - filter(TO_NUMBER("A"."ARTICLE_ID")=256324)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
636255 consistent gets
595176 physical reads
0 redo size
554 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
通过上面的信息可以清楚的看到
用时4分钟
有两个全表扫描（TABLE ACCESS FULL| ARTICLE ）
TABLE ACCESS FULL| COL_TYPE
两个转换1 - access("B"."ID"=TO_NUMBER("A"."TYPE_ID"))
2 - filter(TO_NUMBER("A"."ARTICLE_ID")=256324)
查看表结构
SQL> desc article
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER
CONTENT VARCHAR2(500)
ARTICLE_ID VARCHAR2(10)
TYPE_ID VARCHAR2(10)
上表有80000000条记录
SQL> desc col_type
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
NAME VARCHAR2(50)
果然存在数据类型转换的问题
问题处理(优化)
因独立的存储设备有限，所以采取以下措施
1 create index
SQL> create index index_col_type_id on col_type (id);
Index created.
SQL> create index index_article_article_id on article (article_id);
Index created.
2 修改sql语句为
select a.id,a.content,b.name from article a,col_type b where a.type_id=b.id and a.ARTICLE_ID='256987';
3 查看优化效果
SQL> select a.id,a.content,b.name from article a,col_type b where a.type_id=b.id and a.ARTICLE_ID='256324';
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
Plan hash value: 3740428324
--------------------------------------------------------------------------------
--------------------------
| Id | Operation | Name | Rows | Bytes
| Cost (%CPU)| Time |
--------------------------------------------------------------------------------
--------------------------
| 0 | SELECT STATEMENT | | 1 | 62
| 5 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | COL_TYPE | 1 | 8
| 1 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 62
| 5 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| ARTICLE | 1 | 54
| 4 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | INDEX_ARTICLE_ARTICLE_ID | 1 |
| 3 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | INDEX_COL_TYPE_ID | 1 |
| 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------
--------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A"."ARTICLE_ID"='256324')
5 - access("B"."ID"=TO_NUMBER("A"."TYPE_ID"))
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
8 consistent gets
3 physical reads
0 redo size
554 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
用时0.05秒，比用4.00.52提高了4800倍
