Based on the top SQLs on AWR, one of our DBAs examined WWV_FLOW_DATA (which is one of the most heavily used tables in our Apex based system) in order to see if there are things to improve. They found the followings:
1. Below is the one of Top SQLs. (this is the actual top query)
--7taas77dsqh3z
SELECT ITEM_NAME, ITEM_VALUE_VC2, ITEM_VALUE_CLOB, ITEM_ID, NVL(ITEM_FILTER, 'N'), NVL(IS_ENCRYPTED, 'N')
FROM WWV_FLOW_DATA WHERE ( FLOW_ID = :B3 OR ITEM_ID < 0 ) AND FLOW_INSTANCE = :B2 AND ITEM_NAME IS NOT NULL AND ITEM_NAME NOT LIKE 'D:%' AND ITEM_NAME <> :B1
With the following index, the elapsed time of query above would be reduced:
CREATE INDEX APEX_050100.WWV_FLOW_DATA_IDX2 ON APEX_050100.WWV_FLOW_DATA
(FLOW_ID,FLOW_INSTANCE)
TABLESPACE APEX;
2. The following update/delete statements are also heavily used:
-- gfj1f8y1xwdj4
UPDATE WWV_FLOW_DATA SET ITEM_VALUE_VC2 = :B5 , ITEM_VALUE_CLOB = :B4 , SESSION_STATE_STATUS = DECODE(SESSION_STATE_STATUS,'R','I','U'), IS_ENCRYPTED = :B3 WHERE FLOW_INSTANCE = :B2 AND ITEM_ID = :B1 ;
-- 47s8s1zw21nff
DELETE FROM WWV_FLOW_DATA WHERE FLOW_INSTANCE = :B2 AND ITEM_ID = :B1
As index WWV_FLOW_DATA_PK (FLOW_INSTANCE, ITEM_ID) is used as PRIMARY KEY (PRIMARY KEY constraint enabled) but the index has been created without UNIQUE , index range scan is seen on the Execution Plan rather than index unique scan which, we believe, requires more effort.
SQL> select table_name,index_name,uniqueness from dba_indexes where index_name = 'WWV_FLOW_DATA_PK';
TABLE_NAME INDEX_NAME UNIQUENES
-------------------- -------------------- ---------
WWV_FLOW_DATA WWV_FLOW_DATA_PK NONUNIQUE
SQL> select constraint_name, constraint_type,table_name,status from dba_constraints where constraint_name = 'WWV_FLOW_DATA_PK'; 2
CONSTRAINT_NAME CONSTRAINT_TYPE TABLE_NAME STATUS
-------------------- -------------------- -------------------- --------
WWV_FLOW_DATA_PK P WWV_FLOW_DATA ENABLED
No duplicate rows found: (no row returned from the query below)
select FLOW_INSTANCE,ITEM_ID, count(*)
from WWV_FLOW_DATA
group by FLOW_INSTANCE,ITEM_ID
having count(*) > 1;
I guess the discussion is whether creating a new index...
CREATE INDEX APEX_050100.WWV_FLOW_DATA_IDX2
ON APEX_050100.WWV_FLOW_DATA(FLOW_ID,FLOW_INSTANCE)
TABLESPACE APEX;
... and recreating index 'WWV_FLOW_DATA_PK' is advisable.