Hi All,
after database upgrade we have seen databse wait time is increased for all the sql statements, after upgrade there is a differences in sql execution, most of tables are going FULL Tablescan. An average before upgrade FULL table scan count is 35 tables, and after upgrade the count increased to 2000 to 3000.
I am taking a one example. I am having a table (ESLL) ,, after upgrade ESLL table record processing is almost same but it increased physical reads, I/O load and CPU load.
pls help me to know the issue.
After Upgrade (12.1.0.2)
Load Statistics Total for ESLL table
Analysis Date | Total Executions | Total Physical Reads | Elapsed Time (ms) | Total Buffer Gets | Records Processed |
23.07.2018 255.65 64.096.456.685 747.280.416 4.480.613.775 2.593.564.848
| Object Name | CPU Load [%] | I/O Load [%] | Elapsed Time [%] | Total Executions | Records Processed |
| ESLL | 329 | 4051 | 800 | 255656 | 2.593.564.848 |
SELECT
"PACKNO","INTROW","SRVPOS","KTEXT1"
FROM
"ESLL"
WHERE
"MANDT"=:A0
Execution Plan From: V$SQL_PLAN sql_id: 9s27w6gacbdsr
SELECT STATEMENT Estimated Costs= 4.563 Estimated Rows= 0
Optimizer: ALL_ROWS
1 TABLE ACCESS FULL ESLL
Estimated Costs= 4.563 Estimated Rows= 1.185.935
Filter predicates: "MANDT"=:A0
Estim. Bytes: 65.226.425
Estim. CPU-Costs = 940.173.627 Estim. IO-Costs = 4.502
Before Upgrade(11.2.0.4)
Load Statistics Total for ESLL table
| Analysis Date | Total Executions | Total Physical Reads | Elapsed Time (ms) | Total Buffer Gets | Records Processed |
28.05.2018 471.704 17.862 342.969.570 3.229.153.429 2.428.629.378
| Object Name | CPU Load [%] | I/O Load [%] | Elapsed Time [%] | Total Executions | Records Processed |
| ESLL | 125 | 000 | 300 | 471704
| 2.428.629.378 |
SELECT
"PACKNO","INTROW","SRVPOS","KTEXT1"
FROM
"ESLL"
WHERE
"MANDT"=:A0
Execution Plan From: V$SQL_PLAN sql_id: 9s27w6gacbdsr
SELECT STATEMENT Estimated Costs= 5.278 Estimated Rows= 0
Optimizer: ALL_ROWS
1 TABLE ACCESS FULL ESLL
Estimated Costs= 5.278 Estimated Rows= 1.089.399
Filter predicates: "MANDT"=:A0
Estim. Bytes: 59.916.945
Estim. CPU-Costs = 861.716.866 Estim. IO-Costs = 5.222