Database version: 12.1.0.2.0
The below mentioned count query is fired once every minute by the application.
This query is generated by the application (and hence lot of unnecessary brackets)
PKT_FEEDBACK_MST table which is doing self-join in the query, is 1.6 GB in size. So, the full table scan is not very good.
The other table HRTB_PN_MASTER is very small. It has only 15 rows and few KBs in size. So, FTS in HRTB_PN_MASTER is fine.
Currently, the query takes 8 to 13 second seconds to execute.
Indexes which are currently in place :
PKT_FEEDBACK_MST's primary key is ID which is not part of a filter but used in join conditions.
There are single column indexes in the following columns
PKT_FEEDBACK_MST table's parent_id column (but optimizer ignores it because of the NULL, i guess)
PKT_FEEDBACK_MST table's pkuc_instc_id column
PKT_FEEDBACK_MST table's pkuc_defc_id column.
Business wants to speed up the execution of this query.
I have 2 questions:
Question1.
Is it worth creating an index on EVENT_FLAG, SKU_ID columns as shown below ? Or any other columns ?
--- If optimizer uses this index in a session with "optimizer_use_invisible_indexes=true",
---- then the index can be made visible
CREATE INDEX PKT_FEEDBACK_MST_idx3 ON PKT_FEEDBACK_MST (EVENT_FLAG, SKU_ID) invisible;
Question2.
Autotrace's 'Statistics' section showed the following. So, this means, Oracle had to do 184180 physical disk I/O reads and copy the needed blocks to DB buffer cache.
And from the DB buffer cache, it had to do 186992 memory reads (I don't know the correct terminology) to get the result set ?
186992 consistent gets
184180 physical reads
The query along with bind variables being executed with auto trace in SQL*Plus.
set timing on
variable v01 varchar2(55);
variable v02 varchar2(55);
variable v03 varchar2(55);
variable v04 varchar2(55);
variable v05 varchar2(55);
variable v06 varchar2(55);
variable v07 varchar2(55);
variable v08 varchar2(55);
variable v09 varchar2(55);
variable v10 varchar2(55);
variable v11 varchar2(55);
variable v12 varchar2(55);
variable v13 varchar2(55);
variable v14 varchar2(55);
exec :v01:= 'abcEfg' ;
exec :v02:= 'abcEfg' ;
exec :v03:= 'abcdefghijklmnopqrst' ;
exec :v04:= 'abcdefghijklmnopqrst' ;
exec :v05:= 'abcdefghijklmnopqrst' ;
exec :v06:= 'abcdefghijklmnopqrst' ;
exec :v07:= 'abcdefghijklmno' ;
exec :v08:= 'abcdefghijklmnopqrst' ;
exec :v09:= 'abcdefghijklm' ;
exec :v10:= 'abcdefgh' ;
exec :v11:= 'abcdefghijklmnop' ;
exec :v12:= 'abcdefghijklmnopqrst' ;
exec :v13:= 'abcdefghijklmno' ;
exec :v14:= 'abcdefgh' ;
alter session set current_Schema = APPSCHEMA;
set autotrace on
SELECT
COUNT(DISTINCT res.id)
FROM
PKT_FEEDBACK_MST res INNER JOIN PKT_FEEDBACK_MST exe ON res.id = exe.pkuc_instc_id
INNER JOIN HRTB_PN_MASTER p ON res.pkuc_defc_id = p.id
WHERE
( res.parent_id IS NULL
AND ( p.sbn = :v01
AND ( exe.EVENT_FLAG = 0
AND exe.SKU_ID IN ( :v02, :v03, :v04, :v05, :v06,
:v07, :v08, :v09, :v10, :v11,
:v12, :v13, :v14 ) ) ) );
COUNT(DISTINCTRES.id)
----------------------
32
Elapsed: 00:00:08.89
Execution Plan
----------------------------------------------------------
Plan hash value: 380577457
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 66 | | 86046 (1)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | 66 | | | |
| 2 | VIEW | VW_DAG_0 | 809 | 53394 | | 86046 (1)| 00:00:04 |
| 3 | HASH GROUP BY | | 809 | 362K| | 86046 (1)| 00:00:04 |
|* 4 | HASH JOIN SEMI | | 809 | 362K| 6064K| 86045 (1)| 00:00:04 |
|* 5 | HASH JOIN | | 16589 | 5864K| | 41191 (1)| 00:00:02 |
|* 6 | TABLE ACCESS FULL| HRTB_PN_MASTER | 15 | 1755 | | 3 (0)| 00:00:01 |
|* 7 | TABLE ACCESS FULL| PKT_FEEDBACK_MST | 16589 | 3969K| | 41188 (1)| 00:00:02 |
|* 8 | TABLE ACCESS FULL | PKT_FEEDBACK_MST | 583K| 53M| | 41546 (2)| 00:00:02 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("RES"."id"="EXE"."PKUC_INSTC_ID")
5 - access("RES"."PKUC_DEFC_ID"="P"."id")
6 - filter("P"."SBN"=SYS_OP_C2C(:V01))
7 - filter("RES"."PARENT_ID" IS NULL)
8 - filter(("EXE"."SKU_ID"=SYS_OP_C2C(:V02) OR "EXE"."SKU_ID"=SYS_OP_C2C(:V03) OR
"EXE"."SKU_ID"=SYS_OP_C2C(:V04) OR "EXE"."SKU_ID"=SYS_OP_C2C(:V05) OR
"EXE"."SKU_ID"=SYS_OP_C2C(:V06) OR "EXE"."SKU_ID"=SYS_OP_C2C(:V07) OR
"EXE"."SKU_ID"=SYS_OP_C2C(:V08) OR "EXE"."SKU_ID"=SYS_OP_C2C(:V09) OR
"EXE"."SKU_ID"=SYS_OP_C2C(:V10) OR "EXE"."SKU_ID"=SYS_OP_C2C(:V11) OR
"EXE"."SKU_ID"=SYS_OP_C2C(:V12) OR "EXE"."SKU_ID"=SYS_OP_C2C(:V13) OR
"EXE"."SKU_ID"=SYS_OP_C2C(:V14)) AND "EXE"."EVENT_FLAG"=0)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
186992 consistent gets
184180 physical reads
0 redo size
556 bytes sent via SQL*Net to client
552 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>