Oracle RDBMS version: 19c
OS : Oracle Linux 8.7
I have a production DB (PDB) which is busy almost 24X7. This PDB is hosted on a 2-node RAC DB.
I see occasional 'log file sync' waits on this PDB. ORLs of the multitenant CDB are sized appropriately (8GB) and hourly redo log switching rate is between 3 to 7.
In my PDB, I have a table like below.
The table size is 95 GB.
CREATE TABLE TABLE_XYZ
(
XYZID CHAR(24 BYTE) NOT NULL,
ATTRIBUTEID CHAR(24 BYTE) NOT NULL,
STRINGVALUE VARCHAR2(512 BYTE),
NUMBERVALUE FLOAT(126),
DATEVALUE TIMESTAMP(3)
);
During peak business hours, I noticed in the AWR reports, that this table has around 4 million INSERTs like below per hour
INSERT INTO TABLE_XYZ (XYZID, AttributeID, StringValue, NumberValue, DateValue) VALUES ( :v0 , :v1 , :v2 , :v3 , :v4 )
and around 140,000 DELETEs like below per hour
DELETE FROM TABLE_XYZ WHERE XYZID = :v0
I know it sounds absurd, but the above table has 5 indexes. Since this table has insanely high amount of INSERTs and relatively hight amount of DELETEs, I want to know if any/all of the indexes are fragmented due to leaf block splits.
Question1. Is there a query which I can use to determine if an index is fragmented (for 19c) ?
Yes, I can run analyze index ... validate structure; on the indexes. But, this may have to be done via a change process.
Question2. How long should I monitor the index for usage ?
I would like to know if any of the index/indexes on large tables that get heavy DMLs are actually used by the optimizer. If not, I would like to drop them.
So, for how long should I enable monitoring using the alter index <indexName> monitoring usage; and then query v$object_usage to study the usage ? Would 3 weekdays be enough ? Is there any convention for this duration ?