3. 看V$Session_longops，超过6秒的SQL
select * from V$Session_longopsorder BY last_update_time DESC；
只要是涉及两张表的全表查询都会超过6秒。
4. 去掉Hint基于规则优先/*rule/看几次语句执行后的执行计划及消耗的内存/CPU量
SELECT JB.RYID, JB.GMSFHM,JB.XM, JB.XB, JB.CSRQ, ZP.ZP
FROM HZCZRK_JBXXB JB,HZCZRK_ZPXXB ZP
WHERE JB.RYID = ZP.RYID;
执行计划是：
执行计划
----------------------------------------------------------
Plan hashvalue: 993606438
--------------------------------------------------------------------------------
---
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
---
| 0 | SELECT STATEMENT | | 1 | 49 | 48728 (1)| 00:09:4
5 |
|* 1 | HASH JOIN | | 1 | 49 | 48728 (1)| 00:09:4
5 |
| 2 | TABLE ACCESS FULL| HZCZRK_JBXXB | 1 | 41 | 2 (0)| 00:00:0
1 |
| 3 | TABLE ACCESS FULL| HZCZRK_ZPXXB | 61692 | 481K| 48725 (1)| 00:09:4
5 |
--------------------------------------------------------------------------------
---
PredicateInformation (identified by operation id):
---------------------------------------------------
1 -access("JB"."RYID"="ZP"."RYID")
统计信息
----------------------------------------------------------
8 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
830 bytes sent via SQL*Net to client
756 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
连接关系由Merge join和Sort join转变为Hash Join。
5. 尝试在RYID、身份证号码、姓名这些字段建复合索引，然后看执行计划及消耗的内存/CPU量
CREATE INDEX inx_ryid_HZCZRK_JBXXB onHZCZRK_JBXXB(RYID,XM,CSRQ);
CREATE INDEX inx_ryid_HZCZRK_ZPXXB onHZCZRK_ZPXXB(RYID,ZPID);
之后执行计划为：
执行计划
----------------------------------------------------------
Plan hashvalue: 993606438
--------------------------------------------------------------------------------
---
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
---
| 0 | SELECT STATEMENT | | 1 | 49 | 48728 (1)| 00:09:4
5 |
|* 1 | HASH JOIN | | 1 | 49 | 48728 (1)| 00:09:4
5 |
| 2 | TABLE ACCESS FULL| HZCZRK_JBXXB | 1 | 41 | 2 (0)| 00:00:0
1 |
| 3 | TABLE ACCESS FULL| HZCZRK_ZPXXB | 61692 | 481K| 48725 (1)| 00:09:4
5 |
--------------------------------------------------------------------------------
---
PredicateInformation (identified by operation id):
---------------------------------------------------
1 -access("JB"."RYID"="ZP"."RYID")
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
830 bytes sent via SQL*Net to client
756 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
可以看到内存消耗量更加小了。
6. 尝试复合的Global Partition索引，然后看执行计划及消耗的内存/CPU量
以出生年月来进行范围的划分，在表HZCZRK_JBXXB上创建索引CSRQ_IX_RANGE
CREATE INDEXCSRQ_IX_RANGE ON HZCZRK_JBXXB(CSRQ)
GLOBALPARTITION BY RANGE(CSRQ)
(
PARTITIONP_19021912 VALUES LESS THAN (19130101),
PARTITIONP_19131923 VALUES LESS THAN (19240101),
PARTITIONP_19241934 VALUES LESS THAN (19350101),
PARTITIONP_19351945 VALUES LESS THAN (19460101),
PARTITIONP_19561966 VALUES LESS THAN (19670101),
PARTITIONP_19671977 VALUES LESS THAN (19780101),
PARTITIONP_19781988 VALUES LESS THAN (19890101),
PARTITIONP_1989