In Oracle 11g, we can ensure that the same proven execution plans are used by the Optimizer regardless of any change which can effect the optimizer like a version change, index drop or recreate, init.ora parameter change etc. Once plans are marked as enabled and accepted, the optimizer will not use any new plans unless […]
The AWR tables contains a wealth of important performance data which can be very useful in performance tuning trend analysis and also when comparing performance between two seperate periods of time. AWR data is stored in the WRH$ and DBA_HIST tables in the SYSAUX tablespace. There could be performance implications if these tables were to […]
Checks to be performed at the machine level (note the example is Red Hat Linux specific) run queue should be ideally not more than the number of CPU’s on the machine At the maximum it should never be more than twice the number of CPU’s. This is denoted by the column ‘r’ in the vmstat […]
set linesize 120 col os_user format a10 col username format a15 col pid format 9999999999 PROMPT SESSIONS SORTED BY PHYSICAL READS PROMPT select OSUSER os_user,username, PROCESS pid, ses.SID sid, SERIAL#, PHYSICAL_READS, BLOCK_CHANGES from v$session ses, v$sess_io sio where ses.SID = sio.SID and username is not null and status=’ACTIVE’ order by PHYSICAL_READS;
This script will display the top 5 latches with the most sleeps. Script can be changed to even sort the display on misses instead. set linesize 120 col name format a30 select * from (select name, gets,misses, sleeps from v$latch order by sleeps desc) where rownum < 6;
This script will list the top 5 SQL statements sorted by the most number of physical reads set serverout on size 1000000 set feedback off declare top5 number; text1 varchar2(4000); x number; len1 number; Cursor c1 is select disk_reads,substr(sql_text,1,4000) from v$sqlarea order by disk_reads desc; begin dbms_output.put_line(‘Reads’||’ ‘||’ Text’); dbms_output.put_line (‘—–‘||’ ‘||’—————————————————-‘); dbms_output.put_line(‘ ‘); open […]
This script will list the top 5 SQL statements sorted by the most number of buffer gets or logical reads set serverout on size 1000000 declare top5 number; text1 varchar2(4000); x number; len1 number; Cursor c1 is select buffer_gets,substr(sql_text,1,4000) from v$sqlarea order by buffer_gets desc; begin dbms_output.put_line(‘Reads’||’ ‘||’ Text’); dbms_output.put_line (‘—–‘||’ ‘||’—————————————————‘); dbms_output.put_line(‘ ‘); open […]
This script will list the top 10 segments in the database that have the most number of physical reads against them. Script can also be changed to query on ‘physical writes’ instead. set pagesize 200 setlinesize 120 col segment_name format a20 col owner format a10 select segment_name,object_type,total_physical_reads from ( select owner||’.’||object_name as segment_name,object_type, value as […]
This note will discuss the various common Wait Events like db file sequential read, db file scattered read, log file sync etc , their possible causes and the likely actions which can be taken to resolve the problem. Download …
set linesize 120 col username format a10 col event format a30 select sid, serial#,username, event, seconds_in_wait, wait_time from v$session where state = ‘WAITING’ and wait_class != ‘Idle’ order by event ;
SELECT count(*), event FROM v$session_wait WHERE wait_time = 0 AND event NOT IN (‘smon timer’,’pipe get’,’wakeup time manager’, ‘pmon timer’,’rdbms ipc message’, ‘SQL*Net message from client’) GROUP BY event ORDER BY 1 DESC;
col name format a40 set linesize 140 select * from ( select name,phyrds, phywrts,readtim,writetim from v$filestat a, v$datafile b where a.file#=b.file# order by readtim desc) where rownum <6;
Easy way to export and import schema statistics – in case the optimizer is behaving strangely and you want to re-import old statistics when the optimizer behaved properly. SCHEMA NAME – TEST Create the table to store the statistics- EXEC DBMS_STATS.CREATE_STAT_TABLE(‘TEST’, ‘STATS_TABLE’); Export schema stats – will be stored in the ‘STATS_TABLE’ EXEC DBMS_STATS.export_schema_stats(‘TEST’,’STATS_TABLE’,NULL,’TEST’); If […]
If LOB data is less than 4000 bytes, then it can be stored in line or on the same database page as the rest of the row data. LOB data can be stored in line only when the block size is large enough to accommodate it. LOB data that is stored out of line, on […]
Oracle 10g has simplified one important aspect of tuning which is the wait event interface by broadly classifying wait events into 8-10 major classes This will give us a good overview of which area is contributing the most to a performance problem and enable us to concentrate our efforts in those areas. While […]
Purpose: Use the following procedure to generate the 10g SQL Tuning Advisor reports and recommendations from the command line when Database Control or Grid Control GUI is not available. CREATE A SQL TUNING TASK DECLARE my_task_name VARCHAR2 (30); my_sqltext CLOB; BEGIN my_sqltext := ‘INSERT INTO BWINT.PAYSLIP_CURRENT_RUN (ASSIGNMENT_ID, FULL_NAME, ASSIGNMENT_NUMBER, LOCATION_CODE, PAYROLL_ACTION_ID,PAY_DATE) […]
Very often, with packaged vendor applications we do not have access to the source code, but would like to customise the application to some specific requirements or in many cases would like an optimised piece of SQL code to be executed which is actually sub-optimal when run directly via the vendor application. We can use […]
