Posts Tagged ‘Oracle Tuning’

Performance Tuning of Oracle Database Backup

July 30, 2020

It is suggested to routine Oracle data source copy at non-pick hours. This thing cannot be done stringently to those companies or sites, which are running 24/7 and available worldwide.  During the copy, the efficiency of data sources might lower. Performance effect will depend on size data source and number of contingency clauses.

If the hot copy is taken during non-pick hours then it definitely will depend on dimension data source as well as copy location. If you take back-up in the area and put the copy in the same disk where the production data source located then during the copy, the efficiency of data source surely changed. If you take back-up at NAS then the efficiency of copy might degrade because copy definitely will depend on network bandwidth. If the dimension data source is large then the active of bandwidth will be using and the operation of copy can become slower.

If you are backing up data source using upload application then efficiency badly affected of server because upload application can consume more resources of CPU and memory. If during upload, sessions are trying to get report or efficiency multiple deals then the efficiency of data source surely affected.

RMAN is a good copy application of Oracle. Using RMAN you can take copy similar but during this similar function high source of the host will be employing. If the data sources used by classes worldwide and at the time of RMAN function, classes are trying to access data source then efficiency horribly affected.

In 24/7 operating data sources, it is not easy to maintain copy easily because the data source is being utilized by multiple sessions worldwide at any time from anywhere. Duplicating data source for support up data source function is totally depending on dimension computer file and dimension data source. If the data source is larger in dimension then it would take more time to finish as a well the source of host would take effect. During support up a data source, if any deal is occurring or any long term query is implemented then data source badly impacts of efficiency bottleneck. During Hot copy process, high numbers of store files have generated this function also functions host read in the area. If more than a single store destination designed in a data source then store function also takes so much system source. Finally, the result would be the efficiency degrading of the host.

In the new data pump function, we can perform upload similar. This similar function will eat high sources at the host level and efficiency can be impacting. I have seen so many accidents about the copy process either trapped or operating long and very next day also operating at choose hours.

There are lots of Oracle DBA Tutorials existing on the Internet. You can find out best single resource to get everything like DBA tips, performance tuning tricks and techniques, career-related job guidance, and the latest database-related news. Expert dba team club is one of this blog. You can gain more knowledge if you are interested in database domain skills.

tuning of latch free wait event

March 31, 2010


Today We will try to see how to tune LATCH FREE wait event?

Today I have checked statspack report of one of my client. In Top 5 Wait event as follows in statspack report.

Top 5 Wait Events
~~~~~~~~~~~~~~~~~                                            Wait     % Total
Event                                               Waits  Time (s)   Wt Time
——————————————– ———— ———– ——-
control file sequential read                           43           1   48.89
latch free                                             88           1   43.73
control file parallel write                            43           0    2.33
db file parallel write                                  2           0    2.25
db file scattered read                                  4           0    1.32

After that I have seen other statspack reports and found that “latch free” wait event was consuming more CPU than other wait events.

After I have checked latch statistics and found there were problem in shared pool because there were no free space available in shared_pool_size and no bind variables were used.

Generally Latch free wait event is increasing due to session has to wait for getting latch which is being held by another process. Latch contention occurs due to this problem.

Heavy misses are being reflected in shared pool and library cache statistics reflects Hard parsing problem and bind variable usage.

How to tune Latch free wait event:

Check hard parse and soft parse. Also check execute to parse ratio. If hard parsing is more then check the root cause of problem. use bind variable instead of static.

Check free space in shared pool if you find out free space then increase following parameter of cursor


Now monitor cached cursor usage. Increase appropriate value of this parameter for tuning of hard parsing if you have free space in shared_pool.

Monitor usage of session_cached_cursors using following query.

SQL> select max(value) from v$sesstat
where statistic# in (select statistic# from v$statname where name=’session cursor cache count’);

We can use HINT as SESSION_CACHE in our SQL statement for cursor caching (Oracle 10g) as follows.

SQL>select /*+ session_cache */ * from emp;

Increase size of shared pool for providing more room for new cursors and pl/sql blocks.May be cursors are being aged out due to lack of free space available to make room for new cursors in shared pool.


Tune SQL if found more consuming blocks and less executions found in shared pool.

All the best,

Gitesh Trivedi