-- Create/Recreate indexes
create unique index KLNL_DKKHMX_IDX1 on KLNL_DKKHMX (MINGXIBH, DKJIEJUH, FARENDMA)
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
create index KLNL_DKKHMX_IDX3 on KLNL_DKKHMX (DKJIEJUH, FARENDMA)
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
1.
select nvl(max(to_number(mingxibh)), 0) as lMaxXuhao
from klnl_dkkhmx
where dkjiejuh = '20151020000935'
and farendma = '9999'
and trim(translate(mingxibh, '0123456789', ' ')) is null;
通过ash找到正在执行的慢sql
select a.session_id,
a.sql_id,
a.machine,
a.blocking_session,
a.sample_time,
a.module,
a.PROGRAM,
a.event,
b.SQL_TEXT
from v$active_session_history a, v$sqlarea b
where a.sql_id = b.sql_id;
select sql_id,
sql_text,
version_count,
loads,
hash_value,
address,
plan_hash_value,
old_hash_value,
last_active_child_address
from v$sqlarea a
where a.sql_id='7cvwmagyjx7zt'
SQL_ID 7cvwmagyjx7zt
SQL_TEXT select nvl(max(to_number(mingxibh)), 0) as lMaxXuhao from klnl_dkkhmx where dkjiejuh = '20151020000935' and farendma = '9999' and trim(translate(mingxibh, '0123456789', ' ')) is null
VERSION_COUNT 1
LOADS 1
HASH_VALUE 4246642681
ADDRESS 00000000F242BB00
PLAN_HASH_VALUE 3928807798
OLD_HASH_VALUE 696269501
LAST_ACTIVE_CHILD_ADDRESS 00000000F28CBED8
select sql_id,
sql_text,
a.loaded_versions,
hash_value,
address,
a.old_hash_value,
a.plan_hash_value,
a.child_number,
a.child_address
from v$sql a
where a.sql_id='7cvwmagyjx7zt'
SQL_ID 7cvwmagyjx7zt
SQL_TEXT select nvl(max(to_number(mingxibh)), 0) as lMaxXuhao from klnl_dkkhmx where dkjiejuh = '20151020000935' and farendma = '9999' and trim(translate(mingxibh, '0123456789', ' ')) is null
LOADED_VERSIONS 1
HASH_VALUE 4246642681
ADDRESS 00000000F242BB00
OLD_HASH_VALUE 696269501
PLAN_HASH_VALUE 3928807798
CHILD_NUMBER 0
CHILD_ADDRESS 00000000F28CBED8
--根据sql_id 查看执行计划
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('SQL_ID',SQL_CHILD_NUMBER));
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('7cvwmagyjx7zt',0));
PLAN_TABLE_OUTPUT
SQL_ID 7cvwmagyjx7zt, child number 0
-------------------------------------
select nvl(max(to_number(mingxibh)), 0) as lMaxXuhao from klnl_dkkhmx
where dkjiejuh = '20151020000935' and farendma = '9999' and
trim(translate(mingxibh, '0123456789', ' ')) is null
Plan hash value: 3928807798
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 88646 (100)| |
| 1 | SORT AGGREGATE | | 1 | 46 | | |
|* 2 | TABLE ACCESS FULL| KLNL_DKKHMX | 3 | 138 | 88646 (1)| 00:17:44 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("DKJIEJUH"='20151020000935' AND
TRIM(TRANSLATE("MINGXIBH",'0123456789',' ')) IS NULL AND
"FARENDMA"='9999'))
等价于
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('7cvwmagyjx7zt'));
PLAN_TABLE_OUTPUT
SQL_ID 7cvwmagyjx7zt, child number 0
-------------------------------------
select nvl(max(to_number(mingxibh)), 0) as lMaxXuhao from klnl_dkkhmx
where dkjiejuh = '20151020000935' and farendma = '9999' and
trim(translate(mingxibh, '0123456789', ' ')) is null
Plan hash value: 3928807798
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 88646 (100)| |
| 1 | SORT AGGREGATE | | 1 | 46 | | |
|* 2 | TABLE ACCESS FULL| KLNL_DKKHMX | 3 | 138 | 88646 (1)| 00:17:44 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("DKJIEJUH"='20151020000935' AND
TRIM(TRANSLATE("MINGXIBH",'0123456789',' ')) IS NULL AND
"FARENDMA"='9999'))
2.
create index KLNL_DKKHMX_IDX1 on KLNL_DKKHMX (MINGXIBH, DKJIEJUH, FARENDMA)
select sql_id,
sql_text,
version_count,
loads,
hash_value,
address,
plan_hash_value,
old_hash_value,
last_active_child_address
from v$sqlarea a
where a.sql_id='7cvwmagyjx7zt'
SQL_ID 7cvwmagyjx7zt
SQL_TEXT select nvl(max(to_number(mingxibh)), 0) as lMaxXuhao from klnl_dkkhmx where dkjiejuh = '20151020000935' and farendma = '9999' and trim(translate(mingxibh, '0123456789', ' ')) is null
VERSION_COUNT 2
LOADS 3
HASH_VALUE 4246642681
ADDRESS 00000000F242BB00
PLAN_HASH_VALUE 2367693466
OLD_HASH_VALUE 696269501
LAST_ACTIVE_CHILD_ADDRESS 00000000F28389F0
select sql_id,
sql_text,
a.loaded_versions,
hash_value,
address,
a.old_hash_value,
a.plan_hash_value,
a.child_number,
a.child_address
from v$sql a
where a.sql_id='7cvwmagyjx7zt';
SQL_ID SQL_TEXT LOADED_VERSIONS HASH_VALUE ADDRESS OLD_HASH_VALUE PLAN_HASH_VALUE CHILD_NUMBER CHILD_ADDRESS
1 7cvwmagyjx7zt select nvl(max(to_number(mingxibh)), 0) as lMaxXuhao from klnl_dkkhmx where dkjiejuh = '20151020000935' and farendma = '9999' and trim(translate(mingxibh, '0123456789', ' ')) is null 1 4246642681 00000000F242BB00 696269501 2367693466 0 00000000F28CBED8
2 7cvwmagyjx7zt select nvl(max(to_number(mingxibh)), 0) as lMaxXuhao from klnl_dkkhmx where dkjiejuh = '20151020000935' and farendma = '9999' and trim(translate(mingxibh, '0123456789', ' ')) is null 1 4246642681 00000000F242BB00 696269501 2367693466 1 00000000F28389F0
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('7cvwmagyjx7zt'));
PLAN_TABLE_OUTPUT
SQL_ID 7cvwmagyjx7zt, child number 0
-------------------------------------
select nvl(max(to_number(mingxibh)), 0) as lMaxXuhao from klnl_dkkhmx
where dkjiejuh = '20151020000935' and farendma = '9999' and
trim(translate(mingxibh, '0123456789', ' ')) is null
Plan hash value: 2367693466
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 15578 (100)| |
| 1 | SORT AGGREGATE | | 1 | 46 | | |
|* 2 | INDEX FAST FULL SCAN| KLNL_DKKHMX_IDX1 | 3 | 138 | 15578 (1)| 00:03:07 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("DKJIEJUH"='20151020000935' AND
TRIM(TRANSLATE("MINGXIBH",'0123456789',' ')) IS NULL AND "FARENDMA"='9999'))
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('7cvwmagyjx7zt',0));
PLAN_TABLE_OUTPUT
SQL_ID 7cvwmagyjx7zt, child number 0
-------------------------------------
select nvl(max(to_number(mingxibh)), 0) as lMaxXuhao from klnl_dkkhmx
where dkjiejuh = '20151020000935' and farendma = '9999' and
trim(translate(mingxibh, '0123456789', ' ')) is null
Plan hash value: 2367693466
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 15578 (100)| |
| 1 | SORT AGGREGATE | | 1 | 46 | | |
|* 2 | INDEX FAST FULL SCAN| KLNL_DKKHMX_IDX1 | 3 | 138 | 15578 (1)| 00:03:07 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("DKJIEJUH"='20151020000935' AND
TRIM(TRANSLATE("MINGXIBH",'0123456789',' ')) IS NULL AND "FARENDMA"='9999'))
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('7cvwmagyjx7zt',1));
PLAN_TABLE_OUTPUT
SQL_ID 7cvwmagyjx7zt, child number 1
-------------------------------------
select nvl(max(to_number(mingxibh)), 0) as lMaxXuhao from klnl_dkkhmx
where dkjiejuh = '20151020000935' and farendma = '9999' and
trim(translate(mingxibh, '0123456789', ' ')) is null
Plan hash value: 2367693466
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 15578 (100)| |
| 1 | SORT AGGREGATE | | 1 | 46 | | |
|* 2 | INDEX FAST FULL SCAN| KLNL_DKKHMX_IDX1 | 3 | 138 | 15578 (1)| 00:03:07 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("DKJIEJUH"='20151020000935' AND
TRIM(TRANSLATE("MINGXIBH",'0123456789',' ')) IS NULL AND "FARENDMA"='9999'))
Note
-----
- cardinality feedback used for this statement
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('7cvwmagyjx7zt',2));
SQL_ID: 7cvwmagyjx7zt, child number: 2 cannot be found
3.create index KLNL_DKKHMX_IDX2 on KLNL_DKKHMX (DKJIEJUH, FARENDMA);
select sql_id,
sql_text,
version_count,
loads,
hash_value,
address,
plan_hash_value,
old_hash_value,
last_active_child_address
from v$sqlarea a
where a.sql_id='7cvwmagyjx7zt'
SQL_ID 7cvwmagyjx7zt
SQL_TEXT select nvl(max(to_number(mingxibh)), 0) as lMaxXuhao from klnl_dkkhmx where dkjiejuh = '20151020000935' and farendma = '9999' and trim(translate(mingxibh, '0123456789', ' ')) is null
VERSION_COUNT 3
LOADS 5
HASH_VALUE 4246642681
ADDRESS 00000000F242BB00
PLAN_HASH_VALUE 992735451
OLD_HASH_VALUE 696269501
LAST_ACTIVE_CHILD_ADDRESS 00000000E7BE4F98
select sql_id,
sql_text,
a.loaded_versions,
hash_value,
address,
a.old_hash_value,
a.plan_hash_value,
a.child_number,
a.child_address
from v$sql a
where a.sql_id='7cvwmagyjx7zt';
SQL_ID SQL_TEXT LOADED_VERSIONS HASH_VALUE ADDRESS OLD_HASH_VALUE PLAN_HASH_VALUE CHILD_NUMBER CHILD_ADDRESS
1 7cvwmagyjx7zt select nvl(max(to_number(mingxibh)), 0) as lMaxXuhao from klnl_dkkhmx where dkjiejuh = '20151020000935' and farendma = '9999' and trim(translate(mingxibh, '0123456789', ' ')) is null 1 4246642681 00000000F242BB00 696269501 2367693466 0 00000000F28CBED8
2 7cvwmagyjx7zt select nvl(max(to_number(mingxibh)), 0) as lMaxXuhao from klnl_dkkhmx where dkjiejuh = '20151020000935' and farendma = '9999' and trim(translate(mingxibh, '0123456789', ' ')) is null 1 4246642681 00000000F242BB00 696269501 992735451 1 00000000F28389F0
3 7cvwmagyjx7zt select nvl(max(to_number(mingxibh)), 0) as lMaxXuhao from klnl_dkkhmx where dkjiejuh = '20151020000935' and farendma = '9999' and trim(translate(mingxibh, '0123456789', ' ')) is null 1 4246642681 00000000F242BB00 696269501 992735451 2 00000000E7BE4F98
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('7cvwmagyjx7zt'));
PLAN_TABLE_OUTPUT
1 SQL_ID 7cvwmagyjx7zt, child number 0
2 -------------------------------------
3 select nvl(max(to_number(mingxibh)), 0) as lMaxXuhao from klnl_dkkhmx
4 where dkjiejuh = '20151020000935' and farendma = '9999' and
5 trim(translate(mingxibh, '0123456789', ' ')) is null
6
7 Plan hash value: 2367693466
8
9 ------------------------------------------------------------------------------------------
10 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
11 ------------------------------------------------------------------------------------------
12 | 0 | SELECT STATEMENT | | | | 15578 (100)| |
13 | 1 | SORT AGGREGATE | | 1 | 46 | | |
14 |* 2 | INDEX FAST FULL SCAN| KLNL_DKKHMX_IDX1 | 3 | 138 | 15578 (1)| 00:03:07 |
15 ------------------------------------------------------------------------------------------
16
17 Predicate Information (identified by operation id):
18 ---------------------------------------------------
19
20 2 - filter(("DKJIEJUH"='20151020000935' AND
21 TRIM(TRANSLATE("MINGXIBH",'0123456789',' ')) IS NULL AND "FARENDMA"='9999'))
22
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('7cvwmagyjx7zt',0));
PLAN_TABLE_OUTPUT
SQL_ID 7cvwmagyjx7zt, child number 0
-------------------------------------
select nvl(max(to_number(mingxibh)), 0) as lMaxXuhao from klnl_dkkhmx
where dkjiejuh = '20151020000935' and farendma = '9999' and
trim(translate(mingxibh, '0123456789', ' ')) is null
Plan hash value: 2367693466
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 15578 (100)| |
| 1 | SORT AGGREGATE | | 1 | 46 | | |
|* 2 | INDEX FAST FULL SCAN| KLNL_DKKHMX_IDX1 | 3 | 138 | 15578 (1)| 00:03:07 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("DKJIEJUH"='20151020000935' AND
TRIM(TRANSLATE("MINGXIBH",'0123456789',' ')) IS NULL AND "FARENDMA"='9999'))
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('7cvwmagyjx7zt',1));
PLAN_TABLE_OUTPUT
SQL_ID 7cvwmagyjx7zt, child number 1
-------------------------------------
select nvl(max(to_number(mingxibh)), 0) as lMaxXuhao from klnl_dkkhmx
where dkjiejuh = '20151020000935' and farendma = '9999' and
trim(translate(mingxibh, '0123456789', ' ')) is null
Plan hash value: 992735451
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 54 (100)| |
| 1 | SORT AGGREGATE | | 1 | 46 | | |
|* 2 | TABLE ACCESS BY INDEX ROWID| KLNL_DKKHMX | 3 | 138 | 54 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | KLNL_DKKHMX_IDX2 | 55 | | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TRIM(TRANSLATE("MINGXIBH",'0123456789',' ')) IS NULL)
3 - access("DKJIEJUH"='20151020000935' AND "FARENDMA"='9999')
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('7cvwmagyjx7zt',2));
PLAN_TABLE_OUTPUT
SQL_ID 7cvwmagyjx7zt, child number 2
-------------------------------------
select nvl(max(to_number(mingxibh)), 0) as lMaxXuhao from klnl_dkkhmx
where dkjiejuh = '20151020000935' and farendma = '9999' and
trim(translate(mingxibh, '0123456789', ' ')) is null
Plan hash value: 992735451
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 180 (100)| |
| 1 | SORT AGGREGATE | | 1 | 46 | | |
|* 2 | TABLE ACCESS BY INDEX ROWID| KLNL_DKKHMX | 64 | 2944 | 180 (0)| 00:00:03 |
|* 3 | INDEX RANGE SCAN | KLNL_DKKHMX_IDX2 | 192 | | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TRIM(TRANSLATE("MINGXIBH",'0123456789',' ')) IS NULL)
3 - access("DKJIEJUH"='20151020000935' AND "FARENDMA"='9999')
Note
-----
- cardinality feedback used for this statement