hi I am using version 11.2.0.4.0 of oracle. below query is executing ~5000 times a day inside a Package code in looP and taking lotof time(~3hrs some days). Individual execution is taking ~1second so over all execution time increases and i am trying if it can be better, its having a Parallel hint in it. I have just removed the Parallel hint and see bit of fast resPonce time. but want to know, if this storage full scan can be made faster by any index? we already have index on few columns of the table, have mentioned the column stats details.
I see that the column ZE seems to be most distinct and may be function based index(involving nvl) on that would helP in this case, befoer that i tried executing the query by rePlacing "AND NVL (ZE, -999999) = -999999" with "AND "ZE = -999999" as we have already one index exist on ZE , but still the query oPts for full table scan. need suggestion for making this query fast.
Table ZZI is 19GB in size. and is not Partitioned.
column_name, data_tyPe, num_distinct,density, num_nulls, histogram
ESCC VARCHAR2 13 1.4834552021451E-8 15459614 FREQUENCY
ZE NUMBER 48970450 7.46653127356624E-6 58718 HEIGHT BALANCED
ZTZCD VARCHAR2 19 1.01690734558547E-8 0 FREQUENCY
ZZCD VARCHAR2 24 1.01706000173958E-8 0 FREQUENCY
EDA NUMBER 226348 0.000688231245698555 6 HEIGHT BALANCED
DRC VARCHAR2 2 1.01699388648363E-8 102 FREQUENCY
ZRC VARCHAR2 6 1.0177925195751E-8 36452 FREQUENCY
MT VARCHAR2 1354451 0.00104712041884817 4799276 HEIGHT BALANCED
DTU VARCHAR2 1 1 49155412 NONE
ZZIID is the Primary key.
SELECT /*+parallel(4) */
NVL (ZZIID, 0)
FROM ZZI
WHERE TRIM (SUBSTR (MT, 0, 50)) = TRIM (SUBSTR ( :B9, 0, 50))
AND ZTZCD = :B8
AND NVL ( :B7, ZZCD) = NVL ( :B7, :B6)
AND DRC = :B5
AND ESCC = :B4
AND EDA = :B3
AND ZRC = :B2
AND NVL (ZE, -999999) = -999999
AND NVL (DTU, 'N') = NVL ( :B1, 'N')
AND ROWNUM = 1;
----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:01.15 | 29 | | | |
|* 1 | COUNT STOPKEY | | 1 | | 0 |00:00:01.15 | 29 | | | |
| 2 | PX COORDINATOR | | 1 | | 0 |00:00:01.15 | 29 | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 4 | COUNT STOPKEY | | 0 | | 0 |00:00:00.01 | 0 | | | |
| 5 | PX BLOCK ITERATOR | | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 6 | TABLE ACCESS STORAGE FULL| ZZI | 0 | 1 | 0 |00:00:00.01 | 0 | 1025K| 1025K| |
----------------------------------------------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
17 recursive calls
0 db block gets
2456525 consistent gets
2453946 physical reads
0 redo size
400 bytes sent via SQL*Net to client
465 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=1)
4 - filter(ROWNUM=1)
6 - storage(:Z>=:Z AND :Z<=:Z AND ("ZZI"."EDA"=:B3 AND "ZZI"."ZRC"=:B2 AND "ZZI"."ZTZCD"=:B8 AND
"ZZI"."ESCC"=:B4 AND NVL(:B7,"ZZI"."ZZCD")=NVL(:B7,:B6) AND
TRIM(SUBSTR("ZZI"."MT",0,50))=TRIM(SUBSTR(:B9,0,50)) AND "ZZI"."DRC"=:B5 AND
NVL("ZZI"."DTU",'N')=NVL(:B1,'N')))
filter(("ZZI"."EDA"=:B3 AND "ZZI"."ZRC"=:B2 AND "ZZI"."ZTZCD"=:B8 AND
"ZZI"."ESCC"=:B4 AND NVL(:B7,"ZZI"."ZZCD")=NVL(:B7,:B6) AND
TRIM(SUBSTR("ZZI"."MT",0,50))=TRIM(SUBSTR(:B9,0,50)) AND "ZZI"."DRC"=:B5 AND
NVL("ZZI"."DTU",'N')=NVL(:B1,'N') AND (-999999)=NVL("ZZI"."ZE",(-999999))))
Note
-----
- Degree of Parallelism is 4 because of hint
-- by removing Paralle hint
---------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.54 | 2453K| 2453K| | | |
|* 1 | COUNT STOPKEY | | 1 | | 0 |00:00:00.54 | 2453K| 2453K| | | |
|* 2 | TABLE ACCESS STORAGE FULL| ZZI | 1 | 1 | 0 |00:00:00.54 | 2453K| 2453K| 1025K| 1025K| 7199K (0)|
---------------------------------------------------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2453972 consistent gets
2453946 physical reads
0 redo size
400 bytes sent via SQL*Net to client
465 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=1)
2 - storage(("ZZI"."EDA"=:B3 AND "ZZI"."ZRC"=:B2 AND "ZZI"."ZTZCD"=:B8 AND "ZZI"."ESCC"=:B4
AND NVL(:B7,"ZZI"."ZZCD")=NVL(:B7,:B6) AND TRIM(SUBSTR("ZZI"."MT",0,50))=TRIM(SUBSTR(:B9,0,50)) AND "ZZI"."DRC"=:B5 AND
NVL("ZZI"."DTU",'N')=NVL(:B1,'N')))
filter(("ZZI"."EDA"=:B3 AND "ZZI"."ZRC"=:B2 AND "ZZI"."ZTZCD"=:B8 AND "ZZI"."ESCC"=:B4 AND
NVL(:B7,"ZZI"."ZZCD")=NVL(:B7,:B6) AND TRIM(SUBSTR("ZZI"."MT",0,50))=TRIM(SUBSTR(:B9,0,50)) AND "ZZI"."DRC"=:B5 AND
NVL("ZZI"."DTU",'N')=NVL(:B1,'N') AND (-999999)=NVL("ZZI"."ZE",(-999999))))
32 rows selected.
---- by rePlacing "AND NVL (ZE, -999999) = -999999" with "AND "ZE = -999999"---------
----------------------------------------------------------
Plan hash value: 1096440065
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 86 | 56084 (9)| 00:04:45 |
|* 1 | COUNT STOPKEY | | | | | |
|* 2 | TABLE ACCESS STORAGE FULL| ZZI | 1 | 86 | 56084 (9)| 00:04:45 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=1)
2 - storage("ZZI"."ZTZCD"=:B8 AND "ZZI"."ESCC"=:B4 AND
"ZZI"."ZRC"=:B2 AND "ZZI"."DRC"=:B5 AND "ZZI"."EDA"=TO_NUMBER(:B3))
filter("ZZI"."ZTZCD"=:B8 AND "ZZI"."ESCC"=:B4 AND
"ZZI"."ZRC"=:B2 AND "ZZI"."DRC"=:B5 AND "ZZI"."EDA"=TO_NUMBER(:B3)
AND "ZZI"."ZE"=(-999999) AND NVL(:B7,"ZZI"."ZZCD")=NVL(:B7,:B6) AND
TRIM(SUBSTR("ZZI"."MT",0,50))=TRIM(SUBSTR(:B9,0,50)) AND
NVL("ZZI"."DTU",'N')=NVL(:B1,'N'))
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2453972 consistent gets
2453946 physical reads
0 redo size
400 bytes sent via SQL*Net to client
465 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed