1) sqlid:a9ct4d8thary1 : Check logic high number of execution of a9ct4d8thary1 20cr per hours, which seems to be quite high.
SELECT CASE WHEN :B1 ='PASRS' THEN 'AAA' WHEN :B1 ='BBBB' THEN 'DWH' WHEN :B1 ='FFF' THEN 'CWH' WHEN :B1 ='FMBAS' THEN 'EWH' WHEN :B1 ='RES00' THEN 'BWH' WHEN :B1 ='USBAS' THEN 'FWH' ELSE 'STN' END FROM DUAL
2.1) SQL_ID 7g8d62n3p5hsp
SELECT COUNT(*) FROM SAPLOGDETAIL WHERE SLD_SLID=UPPER(:B1 );
BUFFER_GETS/EXECUTIONS EXECUTIONS ELAPSED_TIME/EXECUTIONS/1000000
94741.5607 18961 5.01561606
Plan hash value: 3876476421
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 15228 (100)| |
| 1 | SORT AGGREGATE | | 1 | 16 | | |
|* 2 | TABLE ACCESS FULL| SAPLOGDETAIL | 4 | 64 | 15228 (1)| 00:03:03 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
2 filter("SLD_SLID"=UPPER(:B1))
Recommendation: create index GPCPRD.SAPLOGDETAIL_idx on GPCPRD.SAPLOGDETAIL(SLD_SLID);
Post creation index:
BUFFER_GETS/EXECUTIONS EXECUTIONS ELAPSED_TIME/EXECUTIONS/1000000
3.06666667 90 .000200589
2.2) SQL_ID 6k569u8nuh456, child number 0
-------------------------------------
SELECT LC_AR FROM LOCATION WHERE LC_UN=:B1 AND LC_STATUS='01'
Plan hash value: 570049103
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 166 (100)| |
|* 1 | TABLE ACCESS BY INDEX ROWID| LOCATION | 1 | 11 | 166 (0)| 00:00:02 |
|* 2 | INDEX SKIP SCAN | INX_LC_LARSTS | 3483 | | 109 (0)| 00:00:02 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
Previous:
BUFFER_GETS/EXECUTIONS EXECUTIONS ELAPSED_TIME/EXECUTIONS/1000000
344.032431 5548831 .015886226
SQL> create index GPCPRD.LOCATION_idx on gpcprd.LOCATION(LC_UN,LC_STATUS);
Post:
11:36:10 SQL> @bg
Enter value for sqlid: 6k569u8nuh456
BUFFER_GETS/EXECUTIONS EXECUTIONS ELAPSED_TIME/EXECUTIONS/1000000
2.05360825 485 .000029847
2.3) SQL_id: 83dd4m0km7ft5
Enter value for child: 0
old 1: select plan_table_output from table(dbms_xplan.display_cursor('&sql_id',&child))
new 1: select plan_table_output from table(dbms_xplan.display_cursor('83dd4m0km7ft5',0))
PLAN_TABLE_OUTPUT
SQL_ID 83dd4m0km7ft5, child number 0
SELECT COUNT(*) FROM PACKINGHISTORY WHERE PKH_LINENO=:B2 AND
PKH_ISPOSTED=1 AND PKH_LOADED='N' AND PKH_STATUS NOT IN('00','03') AND
PKH_NO<>:B1
Plan hash value: 4153909901
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 15 (100)| |
| 1 | SORT AGGREGATE | | 1 | 30 | | |
|* 2 | TABLE ACCESS FULL| PACKINGHISTORY | 1 | 30 | 15 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
2 filter(("PKH_LINENO"=:B2 AND "PKH_ISPOSTED"=1 AND "PKH_LOADED"='N'
AND "PKH_STATUS"<>'00' AND "PKH_STATUS"<>'03' AND "PKH_NO"<>:B1))
SQL> create index GPCPRD.PACKINGHISTORY_idx on GPCPRD.PACKINGHISTORY(PKH_LINENO);
Post Index creation:
BUFFER_GETS/EXECUTIONS EXECUTIONS ELAPSED_TIME/EXECUTIONS/1000000
35.0522876 306 .000486461
3) Increase session_cached_cursors 200
4) Increase db_writer_processes to 2
No comments:
Post a Comment