We have performance issue. here i am describing the scenario.
1) There are 2 different p_methods 'A' and 'B'.
2) There are criterias while selecting the records as below.
a) After selecting a record - 's_datetime'
b) if 'A' is in progress another 'A' should not be selected for same 'code'
c) if 'B' is in progress another 'B' should not be selected for same 'code, src_value'
Total records in table t_phdr is *530000*
not processed records as below:
*999 records having [ p_method = 'A', c_datetime as NULL ]*
*999 records having [ p_method = 'B', c_datetime as NULL ]*
previously we found full table scan for nullable fields, to avoid we added decode index.
now this query is giving the correct result, but performance is slow. if more number of non processed records then its taking more than 5 sec.
Please help to improve the performance.
Version : 10.2.0.1
This is the statement:
TABLE: t_phdr
CREATE TABLE t_phdr(
p_num NUMBER(10, 0) NOT NULL,
p_method CHAR(1) NOT NULL,
code CHAR(6) NOT NULL,
src_value NUMBER(10, 0),
s_datetime CHAR(17),
c_datetime CHAR(17),
CONSTRAINT pk_t_phdr PRIMARY KEY (p_num)
);
INDEX
CREATE INDEX ix_t_phdr_1 ON T_phdr (DECODE(c_datetime,NULL,0,NULL));
CREATE INDEX ix_t_phdr_2 ON T_phdr (DECODE(s_datetime,NULL,0,NULL));
SELECT QUERY
SELECT * FROM p_hdr
WHERE p_num =
(
SELECT MIN(p_num) FROM p_hdr
WHERE p_num IN
(
(SELECT MIN(p_num) FROM p_hdr
WHERE p_method = 'A'
AND decode(c_datetime, NULL, 0, NULL) = 0
GROUP BY code
)
UNION ALL
(SELECT MIN(p_num) FROM p_hdr
WHERE p_method = 'B'
AND decode(c_datetime, NULL, 0, NULL) = 0
GROUP BY code,
src_value
)
)
AND decode(s_datetime, NULL, 0, NULL) = 0
);
The Version of the database is 10.2.0.1
These are the parameters relevant to the optimizer:
SQL>
SQL> show parameter optimizer
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling integer 2
optimizer_features_enable string 10.2.0.1
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_mode string ALL_ROWS
optimizer_secure_view_merging boolean TRUE
SQL>
SQL> show parameter db_file_multi
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count integer 16
SQL>
SQL> show parameter db_block_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192
SQL>
SQL>
SQL> show parameter cursor_sharing
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing string EXACT
SQL>
SQL>
SQL> column sname format a20
SQL> column pname format a20
SQL> column pval2 format a20
SQL> select
2 sname
3 , pname
4 , pval1
5 , pval2
6 from
7 sys.aux_stats$
8 ;
SNAME PNAME PVAL1 PVAL2
-------------------- -------------------- ---------- --------------------
SYSSTATS_INFO STATUS COMPLETED
SYSSTATS_INFO DSTART 11-12-2008 09:27
SYSSTATS_INFO DSTOP 11-12-2008 09:27
SYSSTATS_INFO FLAGS 1
SYSSTATS_MAIN CPUSPEEDNW 1072.94552
SYSSTATS_MAIN IOSEEKTIM 10
SYSSTATS_MAIN IOTFRSPEED 4096
SYSSTATS_MAIN SREADTIM
SYSSTATS_MAIN MREADTIM
SYSSTATS_MAIN CPUSPEED
SYSSTATS_MAIN MBRC
SNAME PNAME PVAL1 PVAL2
-------------------- -------------------- ---------- --------------------
SYSSTATS_MAIN MAXTHR
SYSSTATS_MAIN SLAVETHR
13 rows selected.
Here is the output of EXPLAIN PLAN:
SQL> explain plan for SELECT * FROM p_hdr
2 WHERE p_num =
3 (
4 SELECT MIN(p_num) FROM p_hdr
5 WHERE p_num IN
6 (
7 (SELECT MIN(p_num) FROM p_hdr
8 WHERE p_method = 'A'
9 AND decode(c_datetime, NULL, 0, NULL) = 0 10
10 GROUP BY code
11 )
12 UNION ALL
13 (SELECT MIN(p_num) FROM p_hdr
14 WHERE p_method = 'B'
15 AND decode(c_datetime, NULL, 0, NULL) = 0 17
16 GROUP BY code,
17 src_value
18 )
19 ) 23
20 AND decode(s_datetime, NULL, 0, NULL) = 0
21 );
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 357 | 7 (29)|
| 1 | TABLE ACCESS BY INDEX ROWID | p_hdr | 1 | 357 | 3 (0)|
| 2 | INDEX UNIQUE SCAN | PK_p_hdr | 1 | | 2 (0)|
| 3 | SORT AGGREGATE | | 1 | 92 | |
| 4 | NESTED LOOPS | | 1 | 92 | 4 (50)|
| 5 | VIEW | VW_NSO_1 | 2 | 26 | 4 (50)|
| 6 | UNION-ALL | | | | |
| 7 | SORT GROUP BY | | 1 | 115 | 2 (50)|
| 8 | TABLE ACCESS BY INDEX ROWID| p_hdr | 1 | 115 | 1 (0)|
| 9 | INDEX RANGE SCAN | ix_t_phdr_1 | 1 | | 1 (0)|
| 10 | SORT GROUP BY | | 1 | 117 | 2 (50)|
| 11 | TABLE ACCESS BY INDEX ROWID| p_hdr | 1 | 117 | 1 (0)|
| 12 | INDEX RANGE SCAN | ix_t_phdr_1 | 1 | | 1 (0)|
| 13 | TABLE ACCESS BY INDEX ROWID | p_hdr | 1 | 79 | 0 (0)|
| 14 | INDEX RANGE SCAN | ix_t_phdr_2 | 1 | | 0 (0)|
Note
-----
- 'PLAN_TABLE' is old version
24 rows selected.
Here is the output of SQL*Plus AUTOTRACE
SQL> set autotrace traceonly arraysize 200
SQL> alter session set tracefile_identifier = 'sdc_trace';
Session altered.
SQL> SELECT * FROM p_hdr
2 WHERE p_num =
3 (
4 SELECT MIN(p_num) FROM p_hdr
5 WHERE p_num IN
6 (
7 (SELECT MIN(p_num) FROM p_hdr
8 WHERE p_method = 'A'
9 AND decode(c_datetime, NULL, 0, NULL) = 0
10 GROUP BY code
11 )
12 UNION ALL
13 (SELECT MIN(p_num) FROM p_hdr
14 WHERE p_method = 'B'
15 AND decode(c_datetime, NULL, 0, NULL) = 0
16 GROUP BY code,
17 src_value
18 )
19 )
20 AND decode(s_datetime, NULL, 0, NULL) = 0
21 );
Execution Plan
----------------------------------------------------------
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 357 | 7 (29)|
| 1 | TABLE ACCESS BY INDEX ROWID | p_hdr | 1 | 357 | 3 (0)|
| 2 | INDEX UNIQUE SCAN | PK_p_hdr | 1 | | 2 (0)|
| 3 | SORT AGGREGATE | | 1 | 92 | |
| 4 | NESTED LOOPS | | 1 | 92 | 4 (50)|
| 5 | VIEW | VW_NSO_1 | 2 | 26 | 4 (50)|
| 6 | UNION-ALL | | | | |
| 7 | SORT GROUP BY | | 1 | 115 | 2 (50)|
| 8 | TABLE ACCESS BY INDEX ROWID| p_hdr | 1 | 115 | 1 (0)|
| 9 | INDEX RANGE SCAN | ix_t_phdr_1 | 1 | | 1 (0)|
| 10 | SORT GROUP BY | | 1 | 117 | 2 (50)|
| 11 | TABLE ACCESS BY INDEX ROWID| p_hdr | 1 | 117 | 1 (0)|
| 12 | INDEX RANGE SCAN | ix_t_phdr_1 | 1 | | 1 (0)|
| 13 | TABLE ACCESS BY INDEX ROWID | p_hdr | 1 | 79 | 0 (0)|
| 14 | INDEX RANGE SCAN | ix_t_phdr_2 | 1 | | 0 (0)|
-------------------------------------------------------------------------------------------
Note
-----
- 'PLAN_TABLE' is old version
Statistics
----------------------------------------------------------
57 recursive calls
0 db block gets
543123 consistent gets
0 physical reads
0 redo size
1687 bytes sent via SQL*Net to client
469 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed