Vishal desai’s Oracle Blog

May 15, 2014

IO Waits, Average Wait Time and Histogram Heat maps using SQL Developer

Filed under: Tools, Troubleshooting — vishaldesai @ 11:00 pm

Jeff Smith pointed out David Mann’s blog in recent session on SQL Developer Reporting. Using David’s base code I have developed SQL developer heat map reports to visualize, troubleshoot and compare IO related waits.

 image

 

image

From above we can clearly visualize that on 11-MAY-2013 between 2:00 – 3:00 pm average wait time for log file parallel write event was 27.3 ms. The number of waits during same time slot was 10801 which is equivalent to 3 waits per second and that’s not significant. If intermittent slowness was reported by application team and if it was related to commit wait class, DBAs need to engage SA/SAN team to investigate this further.

Below are IO wait histogram reports for log file parallel write event on same database instance. For OLTP workload you want to see bright red boxes for log file parallel write between 1 and 16ms buckets for wait counts and average wait times. Such heat maps can also be used to do relative performance comparison by Day or Day/Hour.

image

 

image

 

Download (I will upload SQL developer report definition files early next week.)

Another example of vertical stacked bar chart.

IO Wait Histogram by Count – db file sequential read

image

 

IO Wait Histogram by Avg Wait Time(s) – db file sequential read

image

April 18, 2014

Database Time Viewer

Filed under: Tools — vishaldesai @ 2:31 pm

DBTimeViewer is a great standalone tool created by Dominic Giles to monitor Oracle Database Time. It requires simple configuration file update which includes database connection information such as username, password, hostname, port and service. In large enterprise environment with hundreds of thousands of databases it becomes little difficult to update database.xml file every time you have to connect to different database. I have created simple combination of batch and SQL script which will automatically create database.xml.

Scripts:

Create SQL and Batch scripts with proper path settings:

Script dbtimemonitor.sql

set head off
set feed off
set linesize 200
set pages 9999
set feedback off
set verify off
set pagesize 0


set term off

spool C:\<dbtime path>\databases.xml

select '<?xml version = ' || '''' || '1.0' || '''' || ' encoding = ' || '''' || 'UTF-8' || '''' || '?>'  from dual
union all
select '<WaitMonitor Title="Monitored Databases" xmlns="http://www.dominicgiles.com/waitmonitor">' from dual
union all
select  '<MonitoredDatabase><ConnectString>//' || a.host_name || '<domain>:<port>/' || b.name || '</ConnectString><Comment></Comment><Username>' || '&1' || '</Username><Password>' || '&2' || '</Password></MonitoredDatabase>'  from gv$instance a, v$database b
union all
select '</WaitMonitor>' from dual;

spool off
set term on
exit


Script dbtime.bat

@ECHO off
SET DBUser=%1
SET DBPass=%2
SET DBTNS=%3

sqlplus -s "%DBUser%/%DBPass%@%DBTNS%" @C:\<path>\dbtimemonitor.sql %1 %2

cd C:\<path>
dbtimemonitor.bat

Output:

C:\<path>>dbtime.bat <dbuser> <dbpassword> <dbtns>

Capture4

November 26, 2013

Compare IO outliers

Filed under: Performance Tuning, Tools, Troubleshooting — vishaldesai @ 9:47 pm

Oracle SQL Developer has nice reporting capability to plot charts. To compare IO outliers using db_hist_event_histogram between two periods, I have created couple of reports for quick comparison.

When you click on report, it will prompt you to select database and input event, period one start snap and end snap, period two start and end snap.

Compare IO wait count outliers by %

image

Compare IO wait count outliers by Value

image

Compare IO wait time outlier by %

image

IO latency for all snaps by Day

image

IO latency for last 24 hours

image

Download link for report definition file.

PS Current version of SQL developer supports only 5 different colors for pie charts so there is overlap of colors in first 3 charts.

October 7, 2013

SQL Developer Child Reports and Drill Down

Filed under: Tools, Troubleshooting — vishaldesai @ 10:36 pm

SQL Developer has some cool reporting capability. Using these features and some of my favorite scripts, I can now quickly troubleshoot Oracle Database related issue.

Example: For given database session, I can run Tanel’s asqlmon, look at historical SQL performance using Kerry’s AWR scripts, and drill down into IO latency with just point and click.

 

September 3, 2013

Oracle IO latency monitoring

Filed under: Performance Tuning, Tools — vishaldesai @ 6:13 pm

I have converted Kyle Hailey’s oramon, to SQL format so that it can be run from client sqlplus window.

Sample Screenshot:

image

Usage:

oraiomon.sql <interval> <samples>

Download

July 5, 2013

v$event_histogram – buckets of time in Nth/Snap interval

Filed under: Performance Tuning, Tools, Troubleshooting — vishaldesai @ 6:22 pm

v$event_histogram stores cumulative data since instance startup. To monitor real time events and under what latency bucket they fall under, I have written script called wait_histogram_wc_pct.sql. Script will display percentage of wait counts that falls into individual latency bucket.

Example: As shown below in last line, 25% of direct path read events waited for >32ms and <64ms (307 out of 1228  events).

SQL> @wait_histogram_wc_pct.sql 'direct path read' 10 10
---------------------------------------------------------------Percent Wait Count--------------------------------------------------------------------------|
Event               |Time                |<1    |<2    |<4    |<8    |<16   |<32   |<64   |<128  |<256  |<512  |<1024 |<2048 |<4096 |<8192 |Tot Wait Count |
-----------------------------------------------------------------------------------------------------------------------------------------------------------|
direct path read    |07/05/13 11:49:51   |     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|              1|
direct path read    |07/05/13 11:50:01   |     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|              1|
direct path read    |07/05/13 11:50:11   |     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|              1|
direct path read    |07/05/13 11:50:21   |     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|              1|
direct path read    |07/05/13 11:50:31   |     1|     2|     3|     7|    17|    38|    21|     7|     3|     0|     0|     0|     0|     0|           1212|
direct path read    |07/05/13 11:50:41   |     2|     2|     6|    11|    20|    33|    20|     5|     1|     0|     0|     0|     0|     0|           2696|
direct path read    |07/05/13 11:50:51   |     4|     3|     6|    11|    20|    29|    19|     7|     1|     0|     0|     0|     0|     0|           2492|
direct path read    |07/05/13 11:51:01   |     4|     3|     6|    11|    18|    24|    25|     9|     1|     0|     0|     0|     0|     0|           2164|
direct path read    |07/05/13 11:51:12   |     2|     2|     4|     9|    16|    23|    25|    16|     3|     0|     0|     0|     0|     0|           1588|
direct path read    |07/05/13 11:51:22   |     1|     2|     3|     7|    13|    19|    27|    21|     6|     0|     0|     0|     0|     0|           1228|

Total wait counts may not be 100% accurate if v$event_histogram gets updated between last and first sample.

To monitor historical events from AWR in similar fashion you can use awr_wait_hist_wc_pct.sql

SQL> @awr_wait_hist_wc_pct.sql
Please enter start_date(mm/dd/yy)    :07/05/13
Please enter end_date  (mm/dd/yy)    :07/05/13


     14757 05-JUL-13 09.00.57.689 AM
     14758 05-JUL-13 09.10.58.136 AM
     14759 05-JUL-13 09.20.58.580 AM
     14760 05-JUL-13 09.30.59.208 AM
     14761 05-JUL-13 09.40.59.867 AM
     14762 05-JUL-13 09.50.00.963 AM
     14763 05-JUL-13 10.00.02.200 AM
     14764 05-JUL-13 10.10.03.637 AM
     14765 05-JUL-13 10.20.04.821 AM
     14766 05-JUL-13 10.30.05.240 AM
     14767 05-JUL-13 10.40.05.723 AM
     14768 05-JUL-13 10.50.06.094 AM
     14769 05-JUL-13 11.00.06.516 AM
     14770 05-JUL-13 11.10.07.016 AM
     14771 05-JUL-13 11.20.07.402 AM
     14772 05-JUL-13 11.30.07.838 AM
     14773 05-JUL-13 11.40.08.306 AM
Enter value for start snap_id   :14757
Enter value for end snap_id     :14773
Enter wait event                :direct path read
Enter instance number for RAC   :1
---------------------------------------------------------------Percent Wait Count--------------------------------------------------------------------------|
Event               |Time                |<1    |<2    |<4    |<8    |<16   |<32   |<64   |<128  |<256  |<512  |<1024 |<2048 |<4096 |<8192 |Tot Wait Count |
-----------------------------------------------------------------------------------------------------------------------------------------------------------|
direct path read    |07/05/13 09:00      |     0|     0|    33|    33|    33|     0|     0|     0|     0|     0|     0|     0|     0|     0|              3|
direct path read    |07/05/13 09:10      |    50|     0|    50|     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|              2|
direct path read    |07/05/13 09:20      |     2|     3|     6|    11|    20|    36|    15|     5|     1|     0|     0|     0|     0|     0|           6002|
direct path read    |07/05/13 09:30      |     3|     3|     6|    10|    19|    33|    18|     6|     1|     0|     0|     0|     0|     0|         158867|
direct path read    |07/05/13 09:40      |     3|     3|     6|    11|    20|    30|    20|     7|     1|     0|     0|     0|     0|     0|          82122|
direct path read    |07/05/13 09:50      |     2|     3|     5|     9|    15|    22|    25|    15|     5|     0|     0|     0|     0|     0|          27698|
direct path read    |07/05/13 10:00      |     2|     2|     5|     9|    15|    24|    24|    15|     4|     0|     0|     0|     0|     0|          59854|
direct path read    |07/05/13 10:10      |     3|     3|     5|    10|    16|    24|    25|    14|     2|     0|     0|     0|     0|     0|          51421|
direct path read    |07/05/13 10:20      |     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|              0|
direct path read    |07/05/13 10:30      |    20|    20|     0|    40|    20|     0|     0|     0|     0|     0|     0|     0|     0|     0|              5|
direct path read    |07/05/13 10:40      |     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|              0|
direct path read    |07/05/13 10:50      |    60|     0|     0|     0|     0|    40|     0|     0|     0|     0|     0|     0|     0|     0|              5|
direct path read    |07/05/13 11:00      |    33|    33|     0|    33|     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|              3|
direct path read    |07/05/13 11:10      |     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|              0|
direct path read    |07/05/13 11:20      |    50|     0|     0|    50|     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|              2|
direct path read    |07/05/13 11:30      |    50|     0|     0|    50|     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|              2|
direct path read    |07/05/13 11:40      |   100|     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|     0|              1|

If you make any enhancements, find any bug with scripts or have any other better way to monitor this, please let me know.

Download Scripts

July 1, 2013

Refresh SQL output every N seconds

Filed under: Tools, Troubleshooting — vishaldesai @ 3:27 pm

In Linux there is watch command to repeat Unix Commands or Shell-Scripts every N seconds. To monitor output of SQL script at regular interval of time from sqlplus you can use refresh.sql.

Example 1: To monitor output of session_longops every 5 seconds you can run following command:

SQL> @refresh.sql session_longops 5 5

SID             % Complete Time Now        ELAPSED_SECONDS MESSAGE
--------------- ---------- --------------- --------------- ---------------------------------------------------------------
1227.1457,@1         99.99 130701 10:10:17               1 RMAN: aggregate input: backup 33: 7831 out of 7832 Blocks done

Example 2: To monitor output of Instance wait every 5 seconds you can run following command:

SQL> @refresh.sql swact 5 5

  INST_ID     SID STATE   EVENT                                          SEQ# SEC_IN_WAIT P1              P2            P3                 P1TRANSL
--------- ------- ------- ---------------------------------------- ---------- ----------- ------------------ ------------------ ------------------ --
        1    1179 WAITING PX Deq: Execute Reply                           320           3 sleeptime/senderid passes= 2          0
                                                                                          = 200

        1     961 WAITING Streams AQ: waiting for messages in the       23225           2 queue id= 12603    process#=          wait time= 5
                                                                                                             0x0000000C910276C0

        1    1227 WAITING control file sequential read                  45873           0 file#= 0           block#= 1          blocks= 1
        1      52 WAITING direct path read                               4197           0 file number= 156   first dba= 730752  block cnt= 128
        1      99 WAITING direct path read                               4225           0 file number= 156   first dba= 2571008 block cnt= 128
        1      25 WAITING direct path read                               3933           0 file number= 157   first dba= 1747200 block cnt= 128
        1     123 WAITING direct path read                               4167           0 file number= 158   first dba= 621824  block cnt= 128
        1      74 WAITING direct path read                               4180           0 file number= 158   first dba= 2348160 block cnt= 128
        1     146 WAITING direct path read                               4297           0 file number= 159   first dba= 2502658 block cnt= 126
        1     314 WORKING On CPU / runqueue                              4393           0
        1       1 WORKING On CPU / runqueue                              4123           0
        1     749 WORKING On CPU / runqueue                               582           0

Script:

--Usage: refresh.sql "sql script name" interval sample
set feed off
set head off
set echo off
set term off
set linesize 120
set verify off
spool refresh_1.sql
set feedback off
set feed off
set serveroutput on 
select cmd from (
select '@' || '&1'  as cmd from dual
union all
select 'exec dbms_lock.sleep(&2);' as cmd from dual
union all
select 'clear scr' as cmd from dual
) , (select rownum from dual connect by level <=&3) ;
spool off
set term on
set serveroutput on
set head on
clear scr
@refresh_1.sql

June 4, 2013

Organizing your DBA scripts

Filed under: Tools — vishaldesai @ 10:46 pm

I have lot of scripts in my DBA scripts folder and at times its hard to find script that I use rarely or have added it to my library but never got chance to use it. To index my scripts and manage it, I started using ultraedit projects feature which allows me to organize scripts in tree view. On top of tree view, I also created help file which has script name and description for that script.

 

image

May 14, 2012

Datafile resizing

Filed under: Tools — vishaldesai @ 3:28 pm

One of our DW environment is very dynamic in terms of space utilization and as client is planning to move production environment to Exadata they do not want to want to invest anything in existing SAN infrastructure which is Netapp. Priorities of work done by Data Analysts and Product creation team changes every other day and Data analysts are given quota on USERS tablespace and all Product creation team uses DATA tablespace. Both USERS and DATA tablespaces are mapped to same ASM Disk group. I could have used one tablespace only but have not done it so as I have seen Data Anaysts writing cartesian join and filling up entire tablespace/mount points and security. To cut long story short, I have to do manual tablespace managment for USERS and DATA depending upon requirement. Below is the script that will display data files that can be resized to release free space from data files above High Water Mark (HVM).

 

 

VDESAI@db > @freespace

TABLESPACE_NAME              TOTAL_SPACE  FREE_SPACE   AUTO_GROW LARGEST_EXTENT  USED_SPACE PCTFULL PCTAUTOGROW
---------------------------- ----------- ----------- ----------- -------------- ----------- ------- -----------
....
USERS                            181,733      62,208     305,274         11,200     119,525      66         168
....


TOTAL_SPACE  FREE_SPACE  USED_SPACE
----------- ----------- -----------
  7,611,993   2,104,437   5,507,556

VDESAI@db > @release_tbs_space.sql
Please enter tablespace name            : USERS
--alter database datafile '+DG_DATA_CSPROD/users_36.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_02.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_38.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_46.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_19.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_13.dbf' resize m--frag size0m;
alter database datafile '+DG_DATA_CSPROD/users_56.dbf' resize 3166m--total size4830m;
--alter database datafile '+DG_DATA_CSPROD/users_56.dbf' resize 3166m--frag size-1728m;
--alter database datafile '+DG_DATA_CSPROD/users_37.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_07.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_32.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_23.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_26.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_50.dbf' resize m--frag size0m;
alter database datafile '+DG_DATA_CSPROD/users_43.dbf' resize 3886m--total size4974m;
--alter database datafile '+DG_DATA_CSPROD/users_43.dbf' resize 3886m--frag size-1152m;
--alter database datafile '+DG_DATA_CSPROD/users_03.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_31.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_09.dbf' resize m--frag size0m;
alter database datafile '+DG_DATA_CSPROD/users_57.dbf' resize 427m--total size2411m;
--alter database datafile '+DG_DATA_CSPROD/users_57.dbf' resize 427m--frag size-2048m;
--alter database datafile '+DG_DATA_CSPROD/users_47.dbf' resize m--frag size0m;
alter database datafile '+DG_DATA_CSPROD/users_41.dbf' resize 59492m--total size65508m;
--alter database datafile '+DG_DATA_CSPROD/users_41.dbf' resize 59492m--frag size-6080m;
--alter database datafile '+DG_DATA_CSPROD/users_54.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_53.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_05.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_33.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_21.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_16.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_14.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_25.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_22.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_48.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_20.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_06.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_34.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_30.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_15.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_52.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_11.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_01.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_18.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_35.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_12.dbf' resize m--frag size0m;
alter database datafile '+DG_DATA_CSPROD/users_55.dbf' resize 3922m--total size4946m;
--alter database datafile '+DG_DATA_CSPROD/users_55.dbf' resize 3922m--frag size-1088m;
alter database datafile '+DG_DATA_CSPROD/users_42.dbf' resize 46718m--total size47166m;
--alter database datafile '+DG_DATA_CSPROD/users_42.dbf' resize 46718m--frag size-512m;
--alter database datafile '+DG_DATA_CSPROD/users_49.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_39.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_24.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_29.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_08.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_10.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_28.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_51.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_04.dbf' resize m--frag size0m;
alter database datafile '+DG_DATA_CSPROD/users_44.dbf' resize 3901m--total size5245m;
--alter database datafile '+DG_DATA_CSPROD/users_44.dbf' resize 3901m--frag size-1408m;
--alter database datafile '+DG_DATA_CSPROD/users_27.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_40.dbf' resize m--frag size0m;
--alter database datafile '+DG_DATA_CSPROD/users_17.dbf' resize m--frag size0m;
Total space reclaimation:13568MB
VDESAI@db > 


VDESAI@db > alter database datafile '+DG_DATA_CSPROD/users_56.dbf' resize 3166m--total size4830m;

Database altered.

VDESAI@db > alter database datafile '+DG_DATA_CSPROD/users_43.dbf' resize 3886m--total size4974m;

Database altered.

VDESAI@db > alter database datafile '+DG_DATA_CSPROD/users_57.dbf' resize 427m--total size2411m;

Database altered.

VDESAI@db > alter database datafile '+DG_DATA_CSPROD/users_41.dbf' resize 59492m--total size65508m;

Database altered.

VDESAI@db > alter database datafile '+DG_DATA_CSPROD/users_55.dbf' resize 3922m--total size4946m;

Database altered.

VDESAI@db > alter database datafile '+DG_DATA_CSPROD/users_42.dbf' resize 46718m--total size47166m;

Database altered.

VDESAI@db > alter database datafile '+DG_DATA_CSPROD/users_44.dbf' resize 3901m--total size5245m;

Database altered.

VDESAI@db > @freespace

TABLESPACE_NAME              TOTAL_SPACE  FREE_SPACE   AUTO_GROW LARGEST_EXTENT  USED_SPACE PCTFULL PCTAUTOGROW
---------------------------- ----------- ----------- ----------- -------------- ----------- ------- -----------
....
USERS                            168,166      48,640     312,378         11,200     119,526      71         186
....


VDESAI@db > l
  1  declare
  2  cursor c1 is select tablespace_name,file_id,max(block_id) mbid from dba_extents where tablespace_name='&&tbs'
  3  group by tablespace_name,file_id;
  4  fs number;
  5  fn varchar2(100);
  6  ts number;
  7  x number;
  8  gtot number:=0;
  9  tffs number:=0;
 10  ffrags number:= 0;
 11  begin
 12  for v1 in c1 loop
 13     select sum(bytes)/1024/1024 into fs from dba_free_space where tablespace_name = v1.tablespace_name and
 14          file_id = v1.file_id and block_id>v1.mbid;
 15          select file_name,bytes/1024/1024 into fn,ts from dba_data_files where file_id=v1.file_id and tablespace_name=v1.tablespace_name;
 16          select sum(bytes/1024/1024) into tffs from
 17          (select a.bytes/1024/1024 as bytes from dba_free_space a where file_id=v1.file_id and tablespace_name=v1.tablespace_name
 18           union all
 19           select 0.000001 as bytes from dual);
 20  x:=(ts-fs)+64;
 21  if ts>x then
 22  dbms_output.put_line('alter database datafile ' || '''' || fn || '''' || ' resize ' ||  round(x) || 'm' || '--total size' || round(ts) || 'm;' );
 23
 24  gtot :=  gtot + (ts-x);
 25  end if;
 26
 27  ffrags := nvl(tffs - fs,0);
 28  dbms_output.put_line('--alter database datafile ' || '''' || fn || '''' || ' resize ' ||  round(x) || 'm' || '--frag size' || round(ffrags) || 'm;');
 29
 30  end loop;
 31
 32  dbms_output.put_line('Total space reclaimation:' || gtot || 'MB');
 33* end;
VDESAI@db > 

January 6, 2011

Blocking Lock script

Filed under: Performance Tuning, Tools, Uncategorized — vishaldesai @ 10:08 pm

Tool

I just learned from AOT training that v$session is a gold mine and has lot of information about blocking session and final blocking session. So it was time for me to modify my old blocking lock script.

If the script is run on 10g it will only display blocking session. If it is run on 11g it will display blocking and final blocking session.

Sample Output

                                                                                          final
                                                          blocking        blocking        blocking        final
                                                          instance        lockholder      instance        lockholder
Inst                           P2              P3         sid             name            sid             name
  Id  SID SERIAL# P1           ID1             ID2        status          mode            status          mode
---- ---- ------- ------------ --------------- ---------- --------------- --------------- --------------- ---------------
   1    9    6001 Name=TX mode usn<<16 | slot= sequence=3 1 10 VALID      Name=TX mode=6  1 591 VALID
                  =6 X         1245251         60                         X id1=1245251 i
                                                                          d2=360

   1   10    4925 Name=TX mode usn<<16 | slot= sequence=3 1 591 VALID     Name=TX mode=6  1 591 VALID     Name=TX mode=6
                  =6 X         1310780         56                         X id1=1310780 i                 X id1=1310780 i
                                                                          d2=356                          d2=356

SQL> @bl

                                                                                          final
                                                          blocking        blocking        blocking        final
                                                          instance        lockholder      instance        lockholder
Inst                           P2              P3         sid             name            sid             name
  Id  SID SERIAL# P1           ID1             ID2        status          mode            status          mode
---- ---- ------- ------------ --------------- ---------- --------------- --------------- --------------- ---------------
   1   10    4925 Name=TM mode object #=77657  table/part 1 591 VALID     Name=TM mode=3  1 591 VALID     Name=TM mode=3
                  =4 share                     ition=0                    row-X id1=77657                 row-X id1=77657
                                                                           id2=0                           id2=0

Download script

Older Posts »

The Silver is the New Black Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.