Category Archives: Performance and Tuning

Find a session which are generating more archive logs

To find sessions generating lots of redo, you can use either of the following methods. Both methods examine the amount of undo generated. When a transaction generates undo, it will automatically generate redo as well. The methods are: 1) Query … Continue reading

Posted in Performance and Tuning, scripts | Leave a comment

Fragmentation in table

Deleted records not freeing up space   We have deleted records from big tables in database. But each table still holds the same blocks and extents. It means they have not released the space logically. How to use this space. … Continue reading

Posted in Performance and Tuning, Uncategorized | Leave a comment

Logon table is locking oftenly

We have one table master table is getting locked very often , Master table which containing user credentials to logon the application. Troubleshooting: SQL> analyze table scott.master validate structure cascade; Table analyzed. SQL> EXEC dbms_stats.gather_table_stats(‘SCOTT’,’MASTER’,cascade=>TRUE); PL/SQL procedure successfully completed. SQL> … Continue reading

Posted in Performance and Tuning | Leave a comment

How to Move From ANALYZE DBMS_STATS

How to Move From ANALYZE/DBMS_UTILITY.ANALYZE_* to DBMS_STATS ============================================================= Cost-Based optimization relies on accurate statistics to determine the relative cost of execution plans. In Oracle 7, there was two methods to gather statistics: o ANALYZE o DBMS_UTILITY Since the Oracle Version … Continue reading

Posted in statistics | Leave a comment

How To Run DBMS_STATS.GATHER_TABLE_STATS Using Dynamic Query

Connect as scott, scott has DBA and ANALYSE ANY privileges create or replace procedure getstattest IS str1 varchar2(1000); cnt NUMBER; CURSOR c_C1 IS select TABLE_NAME, OWNER from dba_tables where OWNER IN (‘SCOTT’) and TABLE_NAME IN (‘EMP’, ‘DEPT’,’BONUS’,’SALGRADE’); begin FOR tab_rec … Continue reading

Posted in statistics | Leave a comment

How to understand AWR report

The AWR/Statspack report should be taken for the interval not more than 60 minutes during problem. Please dont take AWR / Statspack report for duration of like five or six hours as that would not be reliable. The AWR report … Continue reading

Posted in Performance and Tuning | 1 Comment

How to Backup and Restore Statistics

Backup Optimizer Statistics Step Checklist: 1. Create a statistics table in the ¬†User schema 2. Transfer the statistics to this table Step detail: ———— 1. Create a statistics table in the user schema : User is the owner of the … Continue reading

Posted in statistics | Leave a comment