Loading....

Script – Sessions with high physical reads

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; Please follow and like […]

Script – Top SQL (Physical Reads)

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 […]

Script – Top SQL (Buffer Gets)

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 […]

Script – Segments with highest I/O activity

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 […]

Export and Import schema statistics

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 […]

How to manually submit a 10g SQL Tuning Advisor task

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) […]

Social media & sharing icons powered by UltimatelySocial
Back To Top

Add A Knowledge Base Question !

You will receive an email when your question will be answered.

+ = Verify Human or Spambot ?