Recommendation on ORCL database slowness

 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