Hi Tuning Experts,
To fix a performance issue I had created new index on table. Table size is 350 GB and size of index after creation was 90GB. The problem is after index creation current issue is resolved but other dayend job performance is impacted. The dayend job which usually takes 40mins is now running for more than 4 hours, the AWR reports shows new index as cause of issue below is snippet
Recommendation 1: SQL Tuning
Estimated benefit is .61 active sessions, 13.77% of total activity.
-------------------------------------------------------------------
Action
Run SQL Tuning Advisor on the INSERT statement with SQL_ID
"axmbr51fg0b3t".
Related Object
SQL statement with SQL_ID axmbr51fg0b3t.
INSERT INTO TABLE_NAME (...) VALUES ('T', :B1 , :B2 , :B14 , :B13 , :B12
, :B11 , :B10 , :B3 , :B4 , :B5 , :B9 , 0, :B6 , :B7 , :B8 )
Rationale
The SQL spent 100% of its database time on CPU, I/O and Cluster waits.
This part of database time may be improved by the SQL Tuning Advisor.
Rationale
Database time for this SQL was divided as follows: 100% for SQL
execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java
execution.
Rationale
SQL statement with SQL_ID "axmbr51fg0b3t" was executed 620 times and had
an average elapsed time of 1.8 seconds.
Rationale
I/O and Cluster wait for INDEX "PMM.TABLE_NAME_INDX" with object ID
915311 consumed 99% of the database time spent on this SQL statement.
Rationale
Top level calls to execute the PL/SQL statement with SQL_ID
"6s4g4vvu56zcv" are responsible for 100% of the database time spent on
the INSERT statement with SQL_ID "axmbr51fg0b3t".
Related Object
SQL statement with SQL_ID 6s4g4vvu56zcv.
BEGIN xxxxxxxx; END;
after the index above is disabled the performance if dayend job is running optimally as it usually does. I am not sure if just a index can impact insert statement performance so much?
can anyone help me understand issue, can creation of indexes have such huge impact on database? where do I start my analysis from?
DB Release: 12.2.0.1.0
Thanks in advance.
Imran.