#performance issue

Use of this parameter may increase your performance of the Oracle OLTP database The following are the parameters that may cause the Performance issue in Oracle Database: AUDIT_TRAIL is used to enable or disable database auditing. It consumer resources if you do not need them then please disabled them. --Default Enable setting: ALTER SYSTEM SET AUDIT_TRAIL=DB scope=spfile; --Disable the default…

Db file sequential read wait event in Oracle

How to tune the DB file sequential read wait event in AWR What is sequential Read?A sequential read is usually a single-block read typically used when reading indexes, although it is possible to see sequential reads for more than one block. For reduce the db file sequential read wait event various tuning aspects will need to lookup like tuning the SQL statements, checking indexes selectivity…

Script for move partition stats of previous to next month automatic in Oracle

Script for move stats of previous to next month partition in Oracle

In one of our customer is facing problems on first of every month due to no data present on new partition that why its stats calculated in zero rows. To fixed this issue we copy the previous month stats to the next month stats manually so that it work properly on 1st of month.

Following script will calculate the stats of…

Hard parse elapsed time wait in Oracle

Hard Parses wait in Oracle

On checking the statspack report of one of the DB, we are getting the high parse rate which consuming CPU resources. Hard parsing causing performance issue in Oracle Database.

Statistic Time (s) % DB time ----------------------------------- -------------------- --------- DB CPU 66.2 57.8

Connection management call elapsed wait in Oracle

Connection management call elapsed wait in Oracle

Error One of the DB server is facing performance issue, on checking the statspack report, it show the connection management call elapsed wait event. Connection management wait is due to high connect/disconnect of application session with the database.

Statistic Time (s) % DB time ----------------------------------- --------------------…

Disable rowsets enabled parameter in Oracle Database 12c

Disable rowsets enabled parameter in Oracle Database 12c

In Oracle 12.1.0.1 version introduced this feature of rowset with hash join. When you execute the query and its make execution plan with hash join with rowset.The rowset value in that is coming wrong in that case which caused the performance issue in Oracle 12c environment.

Example show the use of rowsets enabled You can analysed this with…

Check changed parameter history in oracle

Check changed parameter history in oracle

Check the history of parameter is changed in past in Oracle Database. If we are facing any specific issue like performance and we want to check any parameter is changed at database level in past few days then following ways help in it.

Following are the few ways to check the parameter changed in past:

1. Check the alert log file. Search for the alter…

TOP wait event “Acknowledge Over PGA Limit” in Oracle

TOP wait event “Acknowledge Over PGA Limit” Performance issue in Oracle

Acknowledge Over PGA limit is a new wait event introduced with PGA_AGGREGATE_LIMIT in 12.1 version of Oracle Database. If process need more PGA then process has to wait if instance is using PGA near to its limit defined in PGA_AGGREGATE_LIMIT parameter. Note: This wait event can also be encountered when PGA allocation is less…