1. Responsibilities Listing
2. Menus Listing
3. Submenu and Function Listing
4. User and Assigned Responsibility Listing
5. Responsibility and assigned request group listing
6. Profile option with modification date and user
7. Forms personalization Listing
8. Patch Level Listing
9. Request attached to responsibility listing
10. Request listing application wise
11. Count Module Wise Reports
12. Request Status Listing
13. User and responsibility listing
14. Applied Patch Listing




//*
1. Responsibilities Listing
Purpose/Description:
Retrieve a list of all responsibilities.
Parameters
None
*//

SELECT
    (SELECT application_short_name
        FROM fnd_application fa
        WHERE fa.application_id = frt.application_id)
    application
,   frt.responsibility_id
,   frt.responsibility_name
FROM apps.fnd_responsibility_tl frt;



//*
2. Menus Listing
Purpose/Description:
To see the Menus associated with a given responsibility
Parameters
responsibility_id that you can retrieve from query nr 1 (Responsibilities Listing)
*//

SELECT DISTINCT
    a.responsibility_name
,   c.user_menu_name
FROM
    apps.fnd_responsibility_tl a
,   apps.fnd_responsibility b
,   apps.fnd_menus_tl c
,   apps.fnd_menus d
,   apps.fnd_application_tl e
,   apps.fnd_application f
WHERE
    a.responsibility_id(+) = b.responsibility_id
AND a.responsibility_id = 50103
AND b.menu_id = c.menu_id
AND b.menu_id = d.menu_id
AND e.application_id = f.application_id
AND f.application_id = b.application_id
AND a.LANGUAGE = ‘US’;



//*
3. Submenu And Function Listing
Purpose/Description:
By using this query you can check function and submenus attached to a specific menu
Parameters
User_menu_name that you can get by running query 2 (Menu Listing)
*//

SELECT
    c.prompt
,   c.description
FROM
    apps.fnd_menus_tl a
,   fnd_menu_entries_tl c
WHERE
    a.menu_id = c.menu_id
AND a.user_menu_name = ‘Navigator Menu - System Administrator GUI’;



//*
4.User and Assigned Responsibility Listing
Purpose/Description:
You can use this query to check responsibilities assigned to users.
Parameters
None
*//
   
SELECT UNIQUE
    u.user_id
,   SUBSTR (u.user_name, 1, 30) user_name
,   SUBSTR (r.responsibility_name, 1, 60) responsiblity
,   SUBSTR (a.application_name, 1, 50) application
FROM
    fnd_user u
,   fnd_user_resp_groups g
,   fnd_application_tl a
,   fnd_responsibility_tl r
WHERE
    g.user_id(+) = u.user_id
AND g.responsibility_application_id = a.application_id
AND a.application_id = r.application_id
AND g.responsibility_id = r.responsibility_id
ORDER BY
    SUBSTR (user_name, 1, 30)
,   SUBSTR (a.application_name, 1, 50)
,   SUBSTR (r.responsibility_name, 1, 60);



//*
5. Responsibility and assigned request group listing
Purpose/Description:
To find responsibility and assigned request groups.
Every responsibility contains a request group (The request group is basis of submitting requests)
Parameters
None
*//

SELECT
    responsibility_name responsibility
,   request_group_name
,   frg.description
FROM
    fnd_request_groups frg
,   fnd_responsibility_vl frv
WHERE
    frv.request_group_id = frg.request_group_id
ORDER BY responsibility_name



//*
6. Profile option with modification date and user
Purpose/Description:
Query that can be used to audit profile options.
Parameters
None
*//

SELECT
    t.user_profile_option_name
,   profile_option_value
,   v.creation_date
,   v.last_update_date
,   v.creation_date - v.last_update_date "Change Date"
,   (SELECT UNIQUE user_name
        FROM fnd_user
        WHERE user_id = v.created_by) "Created By"
,   (SELECT user_name
        FROM fnd_user
        WHERE user_id = v.last_updated_by) "Last Update By"
FROM
    fnd_profile_options o
,  fnd_profile_option_values v
,   fnd_profile_options_tl t
    WHERE
        o.profile_option_id = v.profile_option_id
    AND o.application_id = v.application_id
    AND start_date_active <= SYSDATE
    AND NVL (end_date_active, SYSDATE) >= SYSDATE
    AND o.profile_option_name = t.profile_option_name
    AND level_id = 10001
    AND t.LANGUAGE IN (SELECT language_code
    FROM fnd_languages
WHERE installed_flag = ‘B’
UNION
    SELECT nls_language
    FROM fnd_languages
    WHERE installed_flag = ‘B’)
ORDER BY user_profile_option_name;



//*
7. Forms personalization Listing
Purpose/Description:
To get modified profile options.
Personalization is a feature available in 11.5.10.X.
Parameters
None
*//

SELECT
    ffft.user_function_name "User Form Name"
,   ffcr.SEQUENCE
,   ffcr.description
,   ffcr.rule_type
,   ffcr.enabled
,   ffcr.trigger_event
,   ffcr.trigger_object
,   ffcr.condition
,   ffcr.fire_in_enter_query
,   (SELECT user_name
        FROM fnd_user fu
        WHERE fu.user_id = ffcr.created_by) "Created By”
FROM
    fnd_form_custom_rules ffcr
,   fnd_form_functions_vl ffft
WHERE ffcr.ID = ffft.function_id
ORDER BY 1;



//*
8. Patch Level Listing
Purpose/Description:
Query that can be used to view the patch level status of all modules
Parameters
None
*//

SELECT
    a.application_name
,   DECODE (b.status, ‘I’, ‘Installed’, ‘S’, ‘Shared’, ‘N/A’) status
,   patch_level
FROM
    apps.fnd_application_vl a
,   apps.fnd_product_installations b
WHERE
    a.application_id = b.application_id;
 



//*
9. Request attached to responsibility listing
Purpose/Description:
To see all requests attached to a responsibility
Parameters
None
*//

SELECT
    responsibility_name
,   frg.request_group_name
,   fcpv.user_concurrent_program_name
,  fcpv.description
FROM
    fnd_request_groups frg
,   fnd_request_group_units frgu
,   fnd_concurrent_programs_vl fcpv
,   fnd_responsibility_vl frv
WHERE
    frgu.request_unit_type = ‘P’
AND frgu.request_group_id = frg.request_group_id
AND frgu.request_unit_id = fcpv.concurrent_program_id
AND frv.request_group_id = frg.request_group_id
ORDER BY responsibility_name;



//*
10. Request listing application wise
Purpose/Description:
View all request types application wise
Parameters
None
*//

SELECT
    fa.application_short_name
,   fcpv.user_concurrent_program_name
,   description
,   DECODE (fcpv.execution_method_code
            ,’B', ‘Request Set Stage Function’
            ,’Q', ‘SQL*Plus’
            ,’H', ‘Host’
            ,’L', ‘SQL*Loader’
            ,’A', ‘Spawned’
            ,’I', ‘PL/SQL Stored Procedure’
            ,’P', ‘Oracle Reports’
            ,’S', ‘Immediate’
            ,fcpv.execution_method_code) exe_method
,   output_file_type
,   program_type
,   printer_name
,   minimum_width
,   minimum_length
,   concurrent_program_name
,   concurrent_program_id
FROM
    fnd_concurrent_programs_vl fcpv
,   fnd_application fa
WHERE
    fcpv.application_id = fa.application_id
ORDER BY description



//*
11. Count Reports per module
Purpose/Description:
To Count Reports
Parameters
None
*//

SELECT
    fa.application_short_name
,   DECODE (fcpv.execution_method_code
    ,’B', ‘Request Set Stage Function’
    ,’Q', ‘SQL*Plus’
    ,’H', ‘Host’
    ,’L', ‘SQL*Loader’
    ,’A', ‘Spawned’
    ,’I', ‘PL/SQL Stored Procedure’
    ,’P', ‘Oracle Reports’
    ,’S', ‘Immediate’
    ,fcpv.execution_method_code) exe_method
,   COUNT (concurrent_program_id) COUNT
FROM
    fnd_concurrent_programs_vl fcpv
,   fnd_application fa
WHERE
    fcpv.application_id = fa.application_id
GROUP BY
    fa.application_short_name
,   fcpv.execution_method_code
ORDER BY 1;



//*
12. Request Status Listing
Purpose/Description:
This query returns report/request processing time
Parameters
None
*//

SELECT
    f.request_id
,   pt.user_concurrent_program_name user_concurrent_program_name
,   f.actual_start_date actual_start_date
,   f.actual_completion_date actual_completion_date
,   floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)
    || ‘ HOURS ‘ ||
    floor((((f.actual_completion_date-f.actual_start_date)*24*60*60) -
    floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600)/60)
    || ‘ MINUTES ‘ ||
    round((((f.actual_completion_date-f.actual_start_date)*24*60*60) -
    floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600 -
    (floor((((f.actual_completion_date-f.actual_start_date)*24*60*60) -
    floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600)/60)*60) ))
    || ‘ SECS ‘ time_difference
,   DECODE(p.concurrent_program_name
    ,’ALECDC’
    ,p.concurrent_program_name||’['||
     f.description||']‘
    ,p.concurrent_program_name) concurrent_program_name
,   decode(f.phase_code
    ,’R',’Running’
    ,’C',’Complete’
    ,f.phase_code) Phase
, f.status_code
FROM
    apps.fnd_concurrent_programs p
,   apps.fnd_concurrent_programs_tl pt
,   apps.fnd_concurrent_requests f
WHERE
    f.concurrent_program_id = p.concurrent_program_id
AND f.program_application_id = p.application_id
AND f.concurrent_program_id = pt.concurrent_program_id
AND f.program_application_id = pt.application_id
AND pt.language = USERENV(’Lang’)
AND f.actual_start_date is not null
ORDER by f.actual_completion_date-f.actual_start_date desc;



//*
13. User and responsibility listing
Purpose/Description:
Check responsibilities assigned to users
Parameters
None
*//

SELECT UNIQUE
    u.user_id
,   SUBSTR (u.user_name, 1, 30) user_name
,   SUBSTR (r.responsibility_name, 1, 60) responsiblity
,   SUBSTR (a.application_name, 1, 50) application
FROM
    fnd_user u
,   fnd_user_resp_groups g
,   fnd_application_tl a
,   fnd_responsibility_tl r
WHERE g.user_id(+) = u.user_id
AND g.responsibility_application_id = a.application_id
AND a.application_id = r.application_id
AND g.responsibility_id = r.responsibility_id
–AND a.application_name like ‘%Order Man%’
ORDER BY SUBSTR (user_name, 1, 30),
SUBSTR (a.application_name, 1, 50),
SUBSTR (r.responsibility_name, 1, 60)



//*
14. Applied Patch Listing
Purpose/Description:
Check Current Applied Patches
Parameters
None
*//

SELECT
    patch_name
,   patch_type
,   maint_pack_level
,   creation_date
FROM applsys.ad_applied_patches
ORDER BY creation_date DESC



=================================
CM: jvm size of concurrent queues
=================================
select DEVELOPER_PARAMETERS from FND_CP_SERVICES
where SERVICE_ID = (select MANAGER_TYPE from FND_CONCURRENT_QUEUES
where CONCURRENT_QUEUE_NAME = 'FNDCPOPP');
============
to increase
============
update FND_CP_SERVICES
set DEVELOPER_PARAMETERS =
'J:oracle.apps.fnd.cp.gsf.GSMServiceController:-mx1024m'
where SERVICE_ID = (select MANAGER_TYPE from FND_CONCURRENT_QUEUES
where CONCURRENT_QUEUE_NAME = 'FNDCPOPP');



===========================================
Workflow: To see failed, open notifications
===========================================
SELECT message_type, COUNT(1)
FROM apps.wf_notifications
WHERE 1 = 1 AND mail_status = 'FAILED' AND status = 'OPEN'
GROUP BY message_type;


================
SQL: patch level
================
set linesize 155;
set pagesize 200;
set verify off;
select b.bug_number bug, b.creation_date credate, b.last_update_date ldate,
decode(bug_number,
2728236, 'OWF.G',
3031977, 'POST OWF.G ROLLUP 1 - 11.5.9.1',
3061871, 'POST OWF.G ROLLUP 2 - 11.5.9.2',
3124460, 'POST OWF.G ROLLUP 3 - 11.5.9.3',
3316333, 'POST OWF.G ROLLUP 4 - 11.5.9.4.1',
3314376, 'POST OWF.G ROLLUP 5 - 11.5.9.5',
3409889, 'POST OWF.G ROLLUP 5 Consolidated Fixes',
3492743, 'POST OWF.G ROLLUP 6 - 11.5.9.6',
3672076, 'POST OWF.G ROLLUP 6.1',
3868138, 'POST OWF.G ROLLUP 7',
3258819, 'OWF.H',
3262159, 'FND.H',
3140000, '11.5.10',
3240000, '11.5.10 CU1',
4017300, '11.5.10 Consolidated Update (CU1) for ATG Product Family',
3460000, '11.5.10 CU2',
4125550, '11.5.10 Consolidated Update (CU2) for ATG Product Family',
4605136, 'WFDS 4.5 rollup FOR 11.5.10, 11.5.10.1, 11.5.10.2',
4645579, 'WFDS 4.5 v2 (replaces 4605136) FOR FND.H',
4684377, 'ONE OFF MISSING RESPONSIBILITIES PATCH',
4334965, '11.5.10 Consolidated Update (CU3) for ATG Product Family') Patch
from APPS.AD_BUGS b
where b.BUG_NUMBER in
('2728236','3031977','3061871','3124460','3316333',
'3314376','3409889','3492743','3672076','3868138',
'3258819','3262159','3140000','3240000','4017300',
'3460000','4125550','4605136','4645579','4684377',
'4334965')
order by bug_number desc;

=================
Workflow: version
=================
$FND_TOP/sql/wfver.sql


========================
Enable low level logging
========================
How to turn on the debugging:
Set the following profiles:
 FND: Debug Log Enabled = YES
 FND: Debug Log Filename = NULL
 FND: Debug Log Level = STATEMENT (most detailed log)
 FND: Debug Log Module = % or ar% or even ar.arp_auto_rule%

If you have applied Patch 3140000 - 11.5.10 MAINTENANCE PACK, the profile names have changed:
'FND: Debug Log Enabled' been replaced by 'FND: Log Enabled'
and 'FND: Debug Log Level' has been replaced by 'FND: Log Level

If FND: Debug Log Enabled profile is not set, then rest of the others won't matter.


============================================
To check the what is installed on what nodes
===========================================
select node_name, status,  support_cp "cp", support_web "web", support_forms "frm", support_db "db" from apps.fnd_nodes;

============================================
Summary of how many users -- 10g As oacore
============================================

REM
    REM        START OF SQL
    REM
    set feedback on
    set timing on
    set echo on
    set feedback on
    set pagesize 132
    set linesize 80
    col user_name format a15
    col first_connect format a18
    col last_connect format a18
    col How_many_user_sessions format 9999999999
    col How_many_sessions format 9999999999
    REM
    REM SQL 1
    REM Summary of how many users
    REM
    select 'Number of user sessions : ' || count( distinct session_id) How_many_user_sessions
    from icx_sessions icx
    where last_connect > sysdate - 1
    and disabled_flag != 'Y'
    and PSEUDO_FLAG = 'N'


===========================================
check JOC patch's
===========================================
select bug_number, last_update_date from ad_bugs where bug_number in ('5726582','5639951','5455628','5468797','5215394');


=====================
Check current sysdate
=====================
SELECT TO_CHAR(SYSDATE,'MM/DD/YYYY HH:MM:SS AM')
       "Today's Date and Time" from DUAL;

===============================
Enable Apache and Jserv logging
===============================


Jserv
 Modify $ORACLE_HOME/Apache/Jserv/etc/jserv.properties
 Make sure the log switch is set to true. Can also specify the location
 of the jserv log file if so desired:

      log=true
      log.timestamp=true
      log.file=<path to jserv log file>/jserv.log
      log.channel.servletException=true
      log.channel.jservException=true
      log.channel.warning=true
      log.channel.servletLog=true
      log.channel.critical=true

Apache
# LogLevel: Control the number of messages logged to the error_log.
   # Possible values: debug,info,notice,warn,error,crit,alert,emerg
     Loglevel warn


=================================================================
Session info: high cpu consumption spid session info
=================================================================
set verify off
set echo off
set head off
set pages 1000
PROMPT Enter SPID :
ACCEPT 1
  select 'SID............ : ' || a.sid
|| chr(10) ||
         'SERIAL#........ : ' || a.serial#
|| chr(10) ||
         'USERNAME....... : ' || a.username
|| chr(10) ||
         'COMMAND........ : ' || a.command
|| chr(10) ||
         'STATUS......... : ' || a.status
|| chr(10) ||
         'Machine........ : ' || a.machine
|| chr(10) ||
         'Terminal....... : ' || a.terminal
|| chr(10) ||
         'Program........ : ' || a.program
|| chr(10) ||
         'Module........ : ' || a.module
|| chr(10) ||
         'SQL Hash Value. : ' || a.sql_hash_value
|| chr(10) ||
         'Logon Time..... : ' || to_char(a.logon_time,'DD-Mon-YYYY HH:MI:SS')
|| chr(10) ||
         'Last Call Et... : ' || a.last_call_et
|| chr(10) ||
         'Process ID..... : ' || a.process
|| chr(10) ||
         'SPID........... : ' || b.spid
  from   v$session a, v$process b
  where  a.paddr=b.addr     and b.spid='&1';
  PROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  PROMPT  Process ID is  process col. in v$session table.
  PROMPT  SPID       is  spid    col. in v$process table.
  PROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  select 'Session Waiting for event...: ' || event
  from    v$session_wait
  where   sid=(select sid from v$session a, v$process b where  a.paddr=b.addr
  and b.spid='&1');
  PROMPT  SQL STATEMENT :
  PROMPT  ===============
  select sql_text
  from   v$sqltext
  where  hash_value=(select sql_hash_value from v$session a, v$process b where
a.paddr=b.addr  and b.spid='&1')
  order  by piece;
==============================================

Solaris Command Reference
HD info(vendor, RPM, capacity)

iostat -E

sd0     Soft Errors: 0 Hard Errors: 3 Transport Errors: 0
Vendor: SEAGATE  Product: ST34371W SUN4.2G Revision: 7462 Serial No: 9742K71685
RPM: 7200 Heads: 16 Size: 4.29GB <4292075520 bytes>
Media Error: 0 Device Not Ready: 0 No Device: 3 Recoverable: 0
Illegal Request: 0 Predictive Failure Analysis: 0

sd1     Soft Errors: 0 Hard Errors: 3 Transport Errors: 0
Vendor: SEAGATE  Product: ST32171W SUN2.1G Revision: 7462 Serial No: 9736T74649
RPM: 5400 Heads: 19 Size: 2.13GB <2127708160 bytes>
Media Error: 0 Device Not Ready: 0 No Device: 3 Recoverable: 0
Illegal Request: 0 Predictive Failure Analysis: 0

sd6     Soft Errors: 0 Hard Errors: 3 Transport Errors: 0
Vendor: TOSHIBA  Product: XM5701TASUN12XCD Revision: 0997 Serial No: 04/09/97
RPM: 0 Heads: 0 Size: 18446744073.71GB <-8589934591 bytes>
Media Error: 0 Device Not Ready: 3 No Device: 0 Recoverable: 0
Illegal Request: 0 Predictive Failure Analysis: 0

Display the number of used and free i-nodes

df -F ufs -o i

Filesystem             iused   ifree  %iused  Mounted on
/dev/dsk/c0t3d0s0      38555  403045     9%   /
/dev/dsk/c0t1d0s0     160761  345607    32%   /export/home
/dev/md/dsk/d20       149826 1905214     7%   /usr/local
impulse:/home/dxy[4:07pm] /usr/ucb/df -i
Filesystem             iused   ifree  %iused  Mounted on
/dev/dsk/c0t3d0s0      38555  403045     9%   /
/dev/dsk/c0t1d0s0     160761  345607    32%   /export/home
/dev/md/dsk/d20       149826 1905214     7%   /usr/local
impulse:/home/dxy[4:07pm]

Display processes with the highest CPU utilization

ps -eo pid,pcpu,args | sort +1n

Display processes with the highest memory usage

ps -eo pid,vsz,args | sort +1n

Printing disk geometry and partition info

 prtvtoc /dev/rdsk/c0t0d0s0

* /dev/rdsk/c0t0d0s0 partition map
*
* Dimensions:
*     512 bytes/sector
*     135 sectors/track
*      16 tracks/cylinder
*    2160 sectors/cylinder
*    3882 cylinders
*    3880 accessible cylinders
*
* Flags:
*   1: unmountable
*  10: read-only
*
*                          First     Sector    Last
* Partition  Tag  Flags    Sector     Count    Sector  Mount Directory
       0      2    00          0   7855920   7855919   /usr/local
       1      3    01    7855920    524880   8380799
       2      5    00          0   8380800   8380799

Checking whether it's running in 32-bit mode or 64-bit mode

64-bit mode

% isalist -v 
sparcv9+vis sparcv9 sparcv8plus+vis sparcv8plus sparcv8 sparcv8-fsmuld sparcv7 sparc

% isainfo -v
64-bit sparcv9 applications
32-bit sparc applications

32-bit mode

% isalist -v
sparcv8plus+vis sparcv8plus sparcv8 sparcv8-fsmuld sparcv7 sparc

% isainfo -v
32-bit sparc applications

Verifying a route to a specified network

# route -n get xxx.yyy.zzz.0
   route to: xxx.yyy.zzz.0
destination: default
       mask: default
    gateway: xxx.yyy.aaa.254
  interface: hme0
      flags:
 recvpipe  sendpipe  ssthresh  rtt,msec    rttvar  hopcount      mtu     expire
       0         0         0         0         0         0      1500         0
#

print the version of OBP

% prtconf -V
OBP 3.3.2 1996/06/28 08:43

% /usr/platform/`uname -i`/sbin/prtdiag -v | grep OBP
  OBP 3.11.1 1997/12/03 15:53   POST 3.11.4 1997/05/27 02:26
%

{2} ok .version
Release 3.23 Version 1 created 1999/07/16 12:08
OBP 3.23.1 1999/07/16 12:08
POST 2.0.2 1998/10/19 10:46
{2} ok

print the version of Open Windows

% showrev -w

OpenWindows version:
OpenWindows Version 3.6.1 25 January 1999

%

To determine which monitor resolution is available

% /usr/sbin/ffbconfig -res \?
Valid values for -res option are:
        1024x768x60 [1]
        1024x768x70 [1]
        1024x768x75 [1] [2]
        1024x768x77
        1024x800x84
        1152x900x66
        1152x900x76
        1280x800x76 [1] [2]
        1280x1024x60 [1] [2]
        1280x1024x67
        1280x1024x76
        1280x1024x85 [1] [2]
        960x680x112s
        960x680x108s
        640x480x60 [1] [2]
        640x480x60i [1]
        768x575x50i [1]
        1440x900x76 [1] [2]
        1600x1000x66 [1] [2]
        1600x1000x76 [1] [2]
        1600x1280x76 [1] [2]
        1920x1080x72 [1] [2]
        1920x1080x76 [1] [2]
        1920x1200x70 [1] [2]
        1920x1200x75 [1] [2]
        svga [1]
        1152
        1280
        stereo
        vga [1] [2]
        ntsc [1]
        pal [1]
        none
Notes:
[1] monitor does not support this resolution.
[2] this version of FFB (FFB1) does not support this resolution.
%

system configuration

% sysdef
Display the device list (and drivers attached to devices)

% prtconf -D
System Configuration:  Sun Microsystems  sun4u
Memory size: 256 Megabytes
System Peripherals (Software Nodes):

SUNW,Ultra-1
    packages
        terminal-emulator
        deblocker
        obp-tftp
        disk-label
        ufs-file-system
    chosen
    openprom
        client-services
    options, instance #0 (driver name: options)
    aliases
    memory
    virtual-memory
    counter-timer
    sbus, instance #0 (driver name: sbus)
        SUNW,CS4231 (driver name: audiocs)
        auxio
        flashprom
        SUNW,fdtwo, instance #0 (driver name: fd)
        eeprom (driver name: eeprom)
        zs, instance #0 (driver name: zs)
        zs, instance #1 (driver name: zs)
        sc
        SUNW,pll
        SUNW,fas, instance #0 (driver name: fas)
            sd (driver name: sd)
            st (driver name: st)
            sd, instance #0 (driver name: sd)
            sd, instance #1 (driver name: sd)
            sd, instance #2 (driver name: sd)
            sd, instance #3 (driver name: sd)
            sd, instance #4 (driver name: sd)
            sd, instance #5 (driver name: sd)
            sd, instance #6 (driver name: sd)
            sd, instance #7 (driver name: sd)
            sd, instance #8 (driver name: sd)
            sd, instance #9 (driver name: sd)
            sd, instance #10 (driver name: sd)
            sd, instance #11 (driver name: sd)
            sd, instance #12 (driver name: sd)
            sd, instance #13 (driver name: sd)
            sd, instance #14 (driver name: sd)
        SUNW,hme, instance #0 (driver name: hme)
        SUNW,bpp (driver name: bpp)
    SUNW,UltraSPARC
    SUNW,ffb, instance #0 (driver name: ffb)
    pseudo, instance #0 (driver name: pseudo)

processor type, speed

% psrinfo -v
Status of processor 0 as of: 06/16/99 12:38:51
  Processor has been on-line since 02/07/99 01:47:11.
  The sparcv9 processor operates at 200 MHz,
        and has a sparcv9 floating point processor.

patch applied on the system
% showrev -p

exported file system on NFS server
% showmount -e NFS_SERVER

display current run level
% who -r

Find out a package which a file belongs to
% pkgchk -l -p /usr/lib/sendmail
Pathname: /usr/lib/sendmail
Type: regular file
Expected mode: 4555
Expected owner: root
Expected group: bin
Expected file size (bytes): 650720
Expected sum(1) of contents: 22626
Expected last modification: Apr 07 04:13:53 1999
Referenced by the following packages:
        SUNWsndmu
Current status: installed

%

Examining gcc behavior
% gcc -v -x c /dev/null

Display the version of CDE
% /usr/ccs/bin/what /usr/dt/bin/dtmail
/usr/dt/bin/dtmail:
        CDE Version 1.3.4
        CDEVersion1.3.4

Display the version of BIND
% nslookup -class=chaos -q=txt version.bind ns0.optix.org
Server:  impulse.optix.org
Address:  210.164.85.210
Aliases:  210.85.164.210.in-addr.arpa

VERSION.BIND    text = "8.2.2-P5"
% dig @ns-tk021.ocn.ad.jp version.bind chaos txt
; <<>> DiG 8.2 <<>> @ns-tk021.ocn.ad.jp version.bind chaos txt
; (1 server found)
;; res options: init recurs defnam dnsrch
;; got answer:
;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 6
;; flags: qr aa rd ra; QUERY: 1, ANSWER: 1, AUTHORITY: 0, ADDITIONAL: 0
;; QUERY SECTION:
;;      version.bind, type = TXT, class = CHAOS

;; ANSWER SECTION:
VERSION.BIND.           0S CHAOS TXT    "4.9.7-REL"

;; Total query time: 81 msec
;; FROM: velocity to SERVER: ns-tk021.ocn.ad.jp  203.139.160.103
;; WHEN: Tue May  9 17:26:23 2000
;; MSG SIZE  sent: 30  rcvd: 64

%

system configuration

% /usr/platform/`uname -i`/sbin/prtdiag
System Configuration:  Sun Microsystems  sun4u 8-slot Sun Enterprise 4000/5000
System clock frequency: 82 MHz
Memory size:  512Mb

========================= CPUs =========================

                    Run   Ecache   CPU    CPU
Brd  CPU   Module   MHz     MB    Impl.   Mask
---  ---  -------  -----  ------  ------  ----
 0     0     0      248     2.0   US-II    1.1
 0     1     1      248     2.0   US-II    1.1
 2     4     0      248     2.0   US-II    1.1
 2     5     1      248     2.0   US-II    1.1


========================= Memory =========================

                                              Intrlv.  Intrlv.
Brd   Bank   MB    Status   Condition  Speed   Factor   With
---  -----  ----  -------  ----------  -----  -------  -------
 0     0     256   Active      OK       60ns    2-way     A
 2     0     256   Active      OK       60ns    2-way     A

========================= IO Cards =========================

     Bus   Freq
Brd  Type  MHz   Slot  Name                              Model
---  ----  ----  ----  --------------------------------  ----------------------
 1   SBus   25     3   SUNW,hme                                              
 1   SBus   25     3   SUNW,fas/sd (block)                                    
 1   SBus   25    13   SUNW,soc/SUNW,pln                 501-2069            
 5   SBus   25     3   SUNW,hme                                              
 5   SBus   25     3   SUNW,fas/sd (block)                                    
 5   SBus   25    13   SUNW,soc/SUNW,pln                 501-2069            

Detached Boards
===============
  Slot  State       Type           Info
  ----  ---------   ------         -----------------------------------------
    3    disabled   disk           Disk 0: Target: 10   Disk 1: Target: 11  
    7    disabled   disk           Disk 0: Target: 14   Disk 1: Target: 15  

No failures found in System
===========================

No System Faults found
======================

Regards
Manoj

AIX Vs Linux Basic Commands

        LINUX                   AIX
===================================================
Text:
        cat                       "
        more                    "
        pg                        "
        cut                       "
        sort                      "
        grep                     "
        head                     "
        tail                        "
        join                       "
        paste                     "
        split                      "
        csplit                    "
        *awk                    "
        *sed                    "

Files:
        ls                      "
        cd                      "
        cp                      "
        mv                      "
        find                    "
        tr                      "
        df                      "
        du                      "
        cpio                    "
        file                    "
        diff                    "
        which                   "
        whatis                  "
        tar                     "
        gzip                    "
        bzip2                   "
        compress                "

Processes:
        ps                      "
        kill                    "
        nice                    "
        renice                  "
        nohup                   "
        &                       "
        ctrl+z                  "
        fg                      "
        bg                      "

Administrative stuff:
        su                          "
        passwd                  "
        chown                    "
        chmod                   "
        last                        "
        who (am i)              "
        whoami                  "
        useradd                 mkuser (better use smit)
        groupadd                mkgroup (better use smit)
        id                          "
        at                          "
        crontab                 "
        mount                   "
        -                       oslevel
        uname                   "
        ulimit                     "
        free                    bootinfo -r
        host                    "
        ifconfig                "
        netstat                 "
        ipcs                    "
        ipcrm                   "
        mail                    "
        dmesg                   alog -f /var/adm/ras/bootlog -o
        /var/log/messages       errpt -a | more
        -                             lsattr
        -                            chdev
        lsmod                   -
        modprobe                -
        insmod                  -

Printers:
        lpstat                  lpstat
        lp                      lp; enq



Performance/Monitoring:
        vmstat                  "
        sar                     "
        iostat                  "
        top; ntop               topas; monitor; nmon
        ethereal                (has to be compiled maybe)
        tcpdump                 "
        -                       filemon
        -                       svmon

LVM:
        pvdisplay               lspv
        vgdisplay               lsvg
        lvdisplay                lslv
        -                            lsfs
        pvcreate                "
        vgcreate                mkvg
        lvcreate                 mklv

Paket management:
        rpm                     installp
        rpm -qa               lslpp
        dpkg                    -
        apt-cache             -
        apt-get                 -
        -                          instfix

Shell:
        env                     "
        set                     "
        if; fi                  "
        else                    "
        elif                    "
        case; esac              "
        while; do; done         "
        for; do; done           "
        let                     "
        (())                    "
        eval                    "

Miscellaneous:
        ftp                     "
        rsh                     "
        rcp                     "
        ssh                     "
        scp                     "
        rsync                   "
        wget                    -
        yast                    smit(ty)

Regards
Manoj

• Windows RUN Command Shotcuts

• Accessibility Controls  :  access.cpl   
• Add Hardware Wizard : hdwwiz.cpl 
• Add/Remove Programs : appwiz.cpl 
• Administrative Tools : control admintools 
• Automatic Updates :  wuaucpl.cpl 
• Bluetooth Transfer Wizard : fsquirt 
• Calculator : calc 
• Certificate Manager : certmgr.msc 
• Character Map : charmap 
• Check Disk Utility : chkdsk 
• Clipboard Viewer : clipbrd 
• Command Prompt : cmd 
• Component Services : dcomcnfg 
• Computer Management : compmgmt.msc 
• Date and Time Properties : timedate.cpl 
• DDE Shares : ddeshare 
• Device Manager : devmgmt.msc 
• Direct X Control Panel (If Installed)* : directx.cpl 
• Direct X Troubleshooter : dxdiag 
• Disk Cleanup Utility : cleanmgr 
• Disk Defragment : dfrg.msc 
• Disk Management : diskmgmt.msc 
• Disk Partition Manager : diskpart 
• Display Properties : control desktop 
• Display Properties : desk.cpl 
• Display Properties (w/Appearance Tab Preselected) : control color 
• Dr. Watson System Troubleshooting Utility : drwtsn32 
• Driver Verifier Utility : verifier 
• Event Viewer : eventvwr.msc 
• File Signature Verification Tool : sigverif 
• Findfast : findfast.cpl 
• Folders Properties : control folders 
• Fonts : control fonts 
• Fonts Folder : fonts 
• Free Cell Card Game : freecell 
• Game Controllers : joy.cpl 
• Group Policy Editor (XP Prof) : gpedit.msc 
• Hearts Card Game : mshearts 
• Iexpress Wizard : iexpress 
• Indexing Service : ciadv.msc 
• Internet Properties : inetcpl.cpl 
• IP Configuration (Display Connection Configuration) : ipconfig /all 
• IP Configuration (Display DNS Cache Contents) : ipconfig /displaydns 
• IP Configuration (Delete DNS Cache Contents) : ipconfig /flushdns 
• IP Configuration (Release All Connections) : ipconfig /release 
• IP Configuration (Renew All Connections) : ipconfig /renew  
• IP Configuration (Refreshes DHCP & Re-Registers DNS) : ipconfig /registerdns 
• IP Configuration (Display DHCP Class ID) : ipconfig /showclassid 
• IP Configuration (Modifies DHCP Class ID) : ipconfig /setclassid 
• Java Control Panel (If Installed) : jpicpl32.cpl 
• Java Control Panel (If Installed) : javaws 
• Keyboard Properties : control keyboard 
• Local Security Settings : secpol.msc 
• Local Users and Groups : lusrmgr.msc 
• Logs You Out Of Windows : logoff 
• Microsoft Chat : winchat 
• Minesweeper Game : winmine 
• Mouse Properties : control mouse  
• Mouse Properties : main.cpl 
• Network Connections : control netconnections 
• Network Connections : ncpa.cpl 
• Network Setup Wizard : netsetup.cpl 
• Notepad : notepad 
• Nview Desktop Manager (If Installed) : nvtuicpl.cpl 
• Object Packager : packager 
• ODBC Data Source Administrator : odbccp32.cpl 
• On Screen Keyboard : osk 
• Opens AC3 Filter (If Installed) : ac3filter.cpl 
• Password Properties : password.cpl 
• Performance Monitor : perfmon.msc 
• Performance Monitor : perfmon 
• Phone and Modem Options : telephon.cpl 
• Power Configuration : powercfg.cpl 
• Printers and Faxes : control printers 
• Printers Folder : printers 
• Private Character Editor : eudcedit 
• Quicktime (If Installed) : QuickTime.cpl 
• Regional Settings : intl.cpl 
• Registry Editor : regedit 
• Registry Editor : regedit32 
• Remote Desktop : mstsc 
• Removable Storage : ntmsmgr.msc 
• Removable Storage Operator Requests : ntmsoprq.msc 
• Resultant Set of Policy (XP Prof) : rsop.msc 
• Scanners and Cameras : sticpl.cpl 
• Scheduled Tasks : control schedtasks 
• Security Center : wscui.cpl 
• Services : services.msc 
• Shared Folders : fsmgmt.msc 
• Shuts Down Windows : shutdown 
• Sounds and Audio : mmsys.cpl 
• Spider Solitare Card Game : spider 
• SQL Client Configuration : cliconfg 
• System Configuration Editor : sysedit 
• System Configuration Utility : msconfig 
• System File Checker Utility (Scan Immediately) : sfc /scannow 
• System File Checker Utility (Scan Once At Next Boot) : sfc /scanonce 
• System File Checker Utility (Scan On Every Boot) : sfc /scanboot 
• System File Checker Utility (Return to Default Setting) : sfc /revert 
• System File Checker Utility (Purge File Cache) : sfc /purgecache  
• System File Checker Utility (Set Cache Size to size x) : sfc /cachesize=x 
• System Properties : sysdm.cpl 
• Task Manager : taskmgr 
• Telnet Client : telnet 
• User Account Management : nusrmgr.cpl 
• Utility Manager : utilman 
• Windows Firewall : firewall.cpl 
• Windows Magnifier : magnify 
• Windows Management Infrastructure : wmimgmt.msc 
• Windows System Security Tool : syskey 
• Windows Update Launches : wupdmgr 
• Windows XP Tour Wizard : tourstart 
• Wordpad : write

    Regards 

    Manoj

Direct NFS Client – Performance, Scalability, and High Availability

Direct NFS Client – Performance, Scalability, and High Availability

Direct NFS Client includes two fundamental I/O optimizations to increase throughput and overall performance. First, Direct NFS Client is capable of performing concurrent direct I/O, which bypasses any operating system level caches and eliminates any operating system write-ordering locks. This decreases memory consumption by eliminating scenarios where Oracle data is cached both in the SGA and in the operating system cache and eliminates the kernel mode CPU cost of copying data from the operating system cache into the SGA. Second, Direct NFS Client performs asynchronous I/O, which allows processing to continue while the I/O request is submitted and processed. Direct NFS Client, therefore, leverages the tight integration with the Oracle Database software to provide unparalleled performance when compared to the operating system kernel NFS clients. Not only does Direct NFS Client outperform traditional NFS, it does so while consuming fewer system resources. The results of a detailed performance analysis are discussed later in this paper.

Oracle Direct NFS Client currently supports up to 4 parallel network paths to provide scalability and high availability. Direct NFS Client delivers optimized performance by automatically load balancing requests across all specified paths. If one network path fails, then Direct NFS Client will reissue commands over any remaining paths – ensuring fault tolerance and high availability.

Regards

Manoj

Understanding AWR (Automatic Workload Repository)

Understanding AWR

Viewing AWR Reports awrrpt.sql and awrrpti.sql displays statistics ,  awrinfo.sql displays general information.

Notes:

565812.1

274496.1

396940.1 -- Troubleshooting and Diagnosing ORA-4031 Erro

The following list includes the meanings of particular hit ratios:

Buffer Hit Ratio: Measures how many times a required block was found in memory rather than having to execute an expensive read operation on disk to get the block.

Buffer Nowait %: Shows the percentage of times when data buffers were accessed directly without any wait time.

Library Hit %: Shows the percentage of times when SQL statements and PL/SQL packages were found in the shared pool.

Execute to Parse %: Shows how often parsed SQL statements are reused without reparsing.

Parse CPU to Parse Elapsd %: Gives the ratio of CPU time spent to parse SQL statements.

Redo NoWait %: Shows whether the redo log buffer has sufficient size.

In-memory Sort %: Shows the percentage of times when sorts are performed in memory instead of using temporary tablespaces.

Soft Parse %: Shows how often sessions issued a SQL statement that is already in the shared pool and how it can use an existing version of that statement.

Latch Hit %: Shows how often latches were acquired without having to wait.

% Non-Parse CPU: Shows the percentage of how much CPU resources were spent on the actual SQL execution.

In the above list of statistics, special attention should be paid to parse-related statistics. The Instance Efficiency Percentage report provided previously shows that about 95 percent of the parses are soft as indicated by the Soft Parse %. This is good enough, indicating that the SQL statements are actively reused by Oracle.

The next interesting item to review is the Parse CPU to Parse Elapsd % statistic. In this case, it is about three percent, which is very low. This fact reveals that Oracle waits for some resources during parsing of SQL statements. This should be investigated further to find the cause.

In this case, % Non-Parse CPU statistic is about 97 percent, which is quite high.  This indicates Oracle utilizes the CPU mostly for statement execution but not for parsing.

As a rule of thumb, one should always minimize the number of hard parses in your production database. This reduction yields the benefit of minimizing CPU overhead spent performing costly parse work.

This following sample report section shows shared pool related statistics:

Shared Pool Statistics        Begin    End

                              ------  ------

             Memory Usage %:   92.70   92.49

    % SQL with executions>1:   86.73   84.20

  % Memory for SQL w/exec>1:   84.12   71.86

In this example, the Memory Usage % statistic shows that almost all, approximately 92 percent, of the shared pool memory is consumed. This could indicate that the system experiences some overhead while aging out old shared memory structures like cursors, PL/SQL programs, and so on. This places additional overhead on the CPU to perform reparsing aging-out. The size of the shared pool should be increased appropriately to eliminate such overhead. In general, this statistic should be near 70 percent after the database has been running a long time. If it is quite low, memory is being wasted.

The % SQL with executions>1 statistic indicates how many SQL statements are executed more than one time. This measures how well production applications are tuned and how well they make use of bind variables.

Regards
Manoj

Oracle Database Granules

The DB_CACHE_SIZE parameter, rather than DB_BLOCK_BUFFERS, is used to define the size of the buffer cache for the standard block size. This can be set to any size, but it will be rounded to the nearest whole granule. If the total SGA is less then 128M the granule size is 4M, greater than 128M and it becomes 16M. The DB_CACHE_SIZE must be at least 1 granule in size and defaults to 48M.

Regards

Manoj

Diagnose performance problems, using the wait interface in Oracle 10g.

Source: http://www.oracle.com/technology/oramag/oracle/04-jul/o44tech_perf.html

=======================
Perform Without Waiting
By Arup Nanda
========================

Diagnose performance problems, using the wait interface in Oracle 10g.
----------------------------------------------------------------------


John, the DBA at Acme Bank, is on the phone with an irate user, Bill, who complains that his database session is hanging, a complaint not unfamiliar to most DBAs. What can John do to address Bill's complaint?

Acme Bank's database is Oracle Database 10g, so John has many options. Automatic Database Diagnostic Manager (ADDM), new in Oracle Database 10g, can tell John about the current overall status and performance of the database, so John starts with ADDM to determine whether what Bill's session is experiencing is the result of a databasewide issue. The ADDM report identifies no databasewide issues that could have this impact on Bill's session, so John moves on to the next option.

One way to diagnose session-level events such as Bill's is to determine whether the session is waiting for anything, such as the reading of a block of a file, a lock on a table row, or a latch. Oracle has provided mechanisms to display the waits happening inside the database since Oracle7, and during the last several years, the model has been steadily perfected, with more and more diagnostic information added to it. In Oracle Database 10g, which makes significantly improved wait event information available, diagnosing a session slowdown has become even easier. This article shows you how to use the wait events in Oracle Database 10g to identify bottlenecks.

Session Waits

How can John the DBA determine what's causing Bill's session to hang? Actually, the session is not hanging; it's waiting for an event to happen, and that's exactly what John checks for.

To continue his investigation, John could use Oracle Enterprise Manager or he could directly access V$ views from the command line. John has a set of scripts he uses to diagnose these types of problems, so he uses the command line.

============================================================================================================
John queries the V$SESSION view to see what Bill's session is waiting for. (Note that John filters out all idle events.)

select sid, username, event, blocking_session,
   seconds_in_wait, wait_time
from v$session where state in ('WAITING')
and wait_class != 'Idle';

The output follows, in vertical format.

SID              : 270
USERNAME         : BILL
EVENT            : enq: TX - row lock
                 contention
BLOCKING_SESSION : 254
SECONDS_IN_WAIT  : 83
WAIT_TIME        : 0

============================================================================================================
Looking at this information, John immediately concludes that Bill's session with SID 270 is waiting for a lock on a table and that that lock is held by session 254 (BLOCKING_SESSION).

=====================================================================================================================
But John wants to know which SQL statement is causing this lock. He can find out easily, by issuing the following query joining the V$SESSION and V$SQL views:

select sid, sql_text
from v$session s, v$sql q
where sid in (254,270)
and (
   q.sql_id = s.sql_id or
   q.sql_id = s.prev_sql_id);

=====================================================================================================================
Listing 1 shows the result of the query. And there (in Listing 1) John sees it—both sessions are trying to update the same row. Unless session 254 commits or rolls back, session 270 will continue to wait for the lock. He explains this to Bill, who, considerably less irate now, decides that something in the application has gone awry and asks John to kill session 254 and release the locks.

Wait Classes

After John kills the blocking session, Bill's session continues but is very slow. John decides to check for other problems in the session. Again, he checks for any other wait events, but this time he specifically checks Bill's session.

=====================================================================================================================
In Oracle Database 10g, wait events are divided into various wait classes, based on their type. The grouping of events lets you focus on specific classes and exclude nonessential ones such as idle events. John issues the following against the V$SESSION_WAIT_CLASS view:

select wait_class_id, wait_class,
   total_waits, time_waited
from v$session_wait_class
where sid = 270;
=====================================================================================================================
The output, shown in Listing 2, shows the wait classes and how many times the session has waited for events in each class. It tells John that application-related waits such as those due to row locks have occurred 17,760 times, for a total of 281,654 centiseconds (cs)—hundredths of a second—since the instance started. John thinks that this TIME_WAITED value is high for this session. He decides to explore the cause of these waits in the application wait class. The times for individual waits are available in the V$SYSTEM_EVENT view. He issues the following query to identify individual waits in the application wait class (class id 4217450380):
=====================================================================================================================
select event, total_waits, time_waited
from v$system_event e, v$event_name n
where n.event_id = e.event_id
and wait_class_id = 4217450380;
=====================================================================================================================
Listing 3 shows the output of this query. It shows that lock contentions (indicated by the event enq: TX - row lock contention) constitute the major part of the waiting time in the application wait class. This concerns John. Is it possible that a badly written application made its way through to the production database, causing these lock contention problems?

Being the experienced DBA that he is, however, John does not immediately draw that conclusion. The data in Listing 3 merely indicates that the users have experienced lock-contention-related waits a total of 2,275 times, for 280,856 cs. It is possible that mostly 1- or 2-cs waits and only one large wait account for the total wait time, and in that case, the application isn't faulty. A single large wait may be some freak occurrence skewing the data and not representative of the workload on the system. How can John determine whether a single wait is skewing the data?
=====================================================================================================================
Oracle 10g provides a new view, V$EVENT_HISTOGRAM, that shows the wait time periods and how often sessions have waited for a specific time period. He issues the following against V$EVENT_HISTOGRAM:

select wait_time_milli bucket, wait_count
 from v$event_histogram
 where event =
    'enq: TX - row lock contention';

The output looks like this:

    BUCKET     WAIT_COUNT
-----------    ----------
         1          252
         2             0
         4             0
         8             0
        16             1
        32             0
        64            4
       128            52
       256          706
       512           392
      1024           18
      2048            7
      4096           843
=====================================================================================================================
The V$EVENT_HISTOGRAM view shows the buckets of wait times and how many times the sessions waited for a particular event—in this case, a row lock contention—for that duration. For example, sessions waited 252 times for less than 1 millisecond (ms), once less than 16 ms but more than 1 ms, and so on. The sum of the values of the WAIT_COUNT column is 2,275, the same as the value shown in the event enq: TX - row lock contention, shown in Listing 3. The V$EVENT_HISTOGRAM view shows that the most waits occurred in the ranges of 256 ms, 512 ms, and 4,096 ms, which is sufficient evidence that the applications are experiencing locking issues and that this locking is the cause of the slowness in Bill's session. Had the view showed numerous waits in the 1-ms range, John wouldn't have been as concerned, because the waits would have seemed normal.

Time Models

Just after John explains his preliminary findings to Bill, Lora walks in with a similar complaint: Her session SID 355 is very slow. Once again, John looks for the events the session is waiting for, by issuing the following query against the V$SESSION_WAIT view:
=====================================================================================================================
select event, seconds_in_wait,
   wait_time
from v$session_wait
where sid = 355;
=====================================================================================================================
The output, shown in Listing 4, shows a variety of wait events in Lora's session, including latch contention, which may be indicative of an application design problem. But before he sends Lora off with a prescription for an application change, John must support his theory that bad application design is the cause of the poor performance in Lora's session. To test this theory, he decides to determine whether the resource utilization of Lora's session is extraordinarily high and whether it slows not only itself but other sessions too.

In the Time Model interface of Oracle Database 10g, John can easily view details of time spent by a session in various activities. He issues the following against the V$SESS_TIME_MODEL view:
=====================================================================================================================
select stat_name, value
from v$sess_time_model
where sid = 355;
 =====================================================================================================================

ADDM Advises

Oracle Diagnostics Pack 10g includes the Automatic Database Diagnostic Monitor (ADDM)—a self-diagnostic engine built right into the Oracle Database 10g kernel. This is a revolutionary performance self-diagnostic solution that enables Oracle Database 10g to automatically diagnose its performance problems.

ADDM periodically examines the state of the database, automatically identifies potential database performance bottlenecks, and recommends corrective actions. Each ADDM finding has an associated impact and benefit measure to enable prioritized handling of the most critical issues. In addition to reporting potential performance issues, ADDM also documents the nonproblem areas of the system.
The output, shown in Listing 5, displays the time (in microseconds) spent by the session in various places. From this output, John sees that the session spent 503,996,336 microseconds parsing (parse time elapsed), out of a total of 878,088,366 microseconds on all SQL execution (sql execute elapsed time), or 57 percent of the SQL execution time, which indicates that a cause of this slowness is high parsing. John gives Lora this information, and she follows up with the application design team.

OS Statistics

While going over users' performance problems, John also wants to rule out the possibility of the host system's being a bottleneck. Before Oracle 10g, he could use operating system (OS) utilities such as sar and vmstat and extrapolate the metrics to determine contention. In Oracle 10g, the metrics at the OS level are collected automatically in the database. To see potential host contention, John issues the following query against the V$OSSTAT view:

select * from v$osstat;

The output in Listing 6 shows the various elements of the OS-level metrics collected. All time elements are in cs. From the results in Listing 6, John sees that the single CPU of the system has been idle for 51,025,805 cs (IDLE_TICKS) and busy for 2,389,857 cs (BUSY_TICKS), indicating a CPU that is about 4 percent busy. From this he concludes that the CPU is not a bottleneck on this host. Note that if the host system had more than one CPU, the columns whose headings had the prefix AVG_, such as AVG_IDLE_TICKS, would show the average of these metrics over all the CPUs.

Active Session History

So far the users have consulted John exactly when each problem occurred, enabling him to peek into the performance views in real time. This good fortune doesn't last long—Janice comes to John complaining about a recent performance problem. When John queries the V$SESSION view, the session is idle, with no events being waited for. How can John check which events Janice's session was waiting for when the problem occurred?

Oracle 10g collects the information on active sessions in a memory buffer every second. This buffer, called Active Session History (ASH), which can be viewed in the V$ACTIVE_SESSION_HISTORY dynamic performance view, holds data for about 30 minutes before being overwritten with new data in a circular fashion. John gets the SID and SERIAL# of Janice's session and issues this query against the V$ACTIVE_SESSION_HISTORY view to find out the wait events for which this session waited in the past.
=====================================================================================================================
select sample_time, event, wait_time
from v$active_session_history
where session_id = 271
and session_serial# = 5;
=====================================================================================================================
Next Steps

READ more about wait events
Oracle Database Reference Guide

Oracle Database Performance Tuning Guide

about AWR

about ADDM
Oracle Database Performance Tuning Guide
The output, excerpted in Listing 7, shows several important pieces of information. First it shows SAMPLE_TIME—the time stamp showing when the statistics were collected—which lets John tie the occurrence of the performance problems to the wait events. Using the data in the V$ACTIVE_SESSION_HISTORY view, John sees that at around 3:17 p.m., the session waited several times for the log buffer space event, indicating that there was some problem with redo log buffers. To further aid the diagnosis, John identifies the exact SQL statement executed by the session at that time, using the following query of the V$SQL view:
=====================================================================================================================
select sql_text, application_wait_time
from v$sql
where sql_id in (
  select sql_id
  from v$active_session_history
  where sample_time =
   '22-FEB-04 03.17.31.188 PM'
  and session_id = 271
  and session_serial# = 5
);
=====================================================================================================================
The output is shown in Listing 8.

The column APPLICATION_WAIT_TIME shows how long the sessions executing that SQL waited for the application wait class. In addition to the SQL_ID, the V$ACTIVE_SESSION_HISTORY view also lets John see specific rows being waited for (in case of lock contentions), client identifiers, and much more.

What if a user comes to John a little late, after the data is overwritten in this view? When purged from this dynamic performance view, the data is flushed to the Active Workload Repository (AWR), a disk-based repository. The purged ASH data can be seen in the DBA_HIST_ACTIVE_SESSION_HIST view, enabling John to see the wait events of a past session. The data in the AWR is purged by default after seven days.

Conclusion

Oracle Database 10g introduces a number of enhancements designed to automate and simplify the performance diagnostic process. Wait event information is more elaborate in Oracle Database 10g and provides deeper insight into the cause of problems, making the diagnosis of performance problems a breeze in most cases, especially in proactive performance tuning.

Regards
Manoj

SQL Queries for Database Locks

SQL to check lock held by sessions

select substr(to_char(l.session_id)||','||to_char(s.serial#),1,12) sid_ser,
substr(l.os_user_name||'/'||l.oracle_username,1,12) username,
l.process,
p.spid,
substr(o.owner||'.'||o.object_name,1,35) owner_object,
decode(l.locked_mode,
1,'No Lock',
2,'Row Share',
3,'Row Exclusive',
4,'Share',
5,'Share Row Excl',
6,'Exclusive',null) locked_mode,
substr(s.status,1,8) status
from
v$locked_object l,
all_objects o,
v$session s,
v$process p
where
l.object_id = o.object_id
and l.session_id  = s.sid
and s.paddr = p.addr
and s.status != 'KILLED';

SQL to check lock held by particular session 

select substr(to_char(l.session_id)||','||to_char(s.serial#),1,12) sid_ser,
    substr(l.os_user_name||'/'||l.oracle_username,1,12) username,
    l.process,
  p.spid,
    substr(o.owner||'.'||o.object_name,1,35) owner_object,
    decode(l.locked_mode,
    1,'No Lock',
    2,'Row Share',
    3,'Row Exclusive',
    4,'Share',
    5,'Share Row Excl',
    6,'Exclusive',null) locked_mode,
    substr(s.status,1,8) status
    from
  v$locked_object l,
    all_objects o,
    v$session s,
    v$process p
        where
    l.object_id = o.object_id
    and l.session_id = s.sid
    and s.paddr = p.addr
    and s.sid=978;

-------------
sample output
-------------

SID_SER      USERNAME    PROCESS      SPID        OWNER_OBJECT                      LOCKED_MODE    STATUS
------------ ------------ ------------ ------------ ----------------------------------- -------------- --------
978,60077    appmyfai/APP 6275        15225        ONT.OE_HEADERS_IFACE_ALL            Row Exclusive  KILLED
978,60077    appmyfai/APP 6275        15225        ONT.OE_ACTIONS_IFACE_ALL            Row Exclusive  KILLED
978,60077    appmyfai/APP 6275        15225        ONT.OE_PRICE_ADJS_IFACE_ALL        Row Exclusive  KILLED
978,60077    appmyfai/APP 6275        15225        ONT.OE_LINES_IFACE_ALL            Row Exclusive  KILLED
978,60077    appmyfai/APP 6275        15225      BOLINF.XXMF_TX_IN_ORDER_HEADERS    Row Exclusive  KILLED
978,60077    appmyfai/APP 6275        15225        BOLINF.XXMF_TX_IN_COUPONS          Row Exclusive  KILLED
978,60077    appmyfai/APP 6275        15225        BOLINF.XXMF_TX_IN_ORDER_LINES      Row Exclusive  KILLED
978,60077    appmyfai/APP 6275        15225        BOLINF.XXMF_ORDER_HEADERS          Row Exclusive  KILLED
978,60077    appmyfai/APP 6275        15225        BOLINF.XXMF_ORDER_LINES            Row Exclusive  KILLED

Regards
Manoj

Find sessions consuming high memory

Find sessions consuming high memory

SELECT  ss.SID,se.status,
        se.command,
        ss.VALUE CPU,
        se.username,
        se.program,
      se.module,
se.SQL_HASH_VALUE,
        se.logon_time
FROM v$sesstat ss, v$session se
WHERE ss.statistic# IN
(SELECT statistic#
FROM v$statname
WHERE NAME = 'session uga memory') AND se.SID=ss.SID AND ss.SID>6 AND se.username IS  NOT NULL AND se.username<>'SYS'
ORDER BY 4 DESC

Regards

Manoj

Database locks In Detail

Database locks in Detail

Create a blocking lock

To begin, create a situation where one user is actively blocking another. Open two sessions. Issue the following commands in Session 1 to build the test table:

SQL> create table tstlock (foo varchar2(1), bar varchar2(1));

Table created.

SQL> insert into tstlock values (1,'a');

1 row created.

SQL> insert into tstlock values (2, 'b');

1 row created.

SQL> select * from tstlock ;

FOO BAR
--- ---
1   a
2   b

2 rows selected.

SQL> commit ;

Commit complete.

Now grab a lock on the whole table, still in Session 1:

SQL> select * from tstlock for update ;

And in Session 2, try to update a row:

SQL> update tstlock set bar=
  2  'a' where bar='a' ;

This statement will hang, blocked by the lock that Session 1 is holding on the entire table.

Identify the blocking session

Oracle provides a view, DBA_BLOCKERS, which lists the SIDs of all blocking sessions. But this view is often, in my experience, a good bit slower than simply querying V$LOCK, and it doesn't offer any information beyond the SIDs of any sessions that are blocking other sessions. The V$LOCK view is faster to query, makes it easy to identify the blocking session, and has a lot more information.

SQL> select * from v$lock ;

ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
AF9E2C4C AF9E2C60        479 TX     131078      16739          0          6        685          0
ADDF7EC8 ADDF7EE0        422 TM      88519          0          3          0        697          0
ADDF7F74 ADDF7F8C        479 TM      88519          0          3          0        685          0
ADEBEA20 ADEBEB3C        422 TX     131078      16739          6          0        697          1
....     ....            ... ...      ....       ....       ....       ....        ....      ....

Note the BLOCK column. If a session holds a lock that's blocking another session, BLOCK=1. Further, you can tell which session is being blocked by comparing the values in ID1 and ID2. The blocked session will have the same values in ID1 and ID2 as the blocking session, and, since it is requesting a lock it's unable to get, it will have REQUEST > 0.

In the query above, we can see that SID 422 is blocking SID 479. SID 422 corresponds to Session 1 in our example, and SID 479 is our blocked Session 2.

To avoid having to stare at the table and cross-compare ID1's and ID2's, put this in a query:

SQL> select l1.sid, ' IS BLOCKING ', l2.sid
  2  from v$lock l1, v$lock l2
  3  where l1.block =1 and l2.request > 0
  4  and l1.id1=l2.id1
  5  and l1.id2=l2.id2
SQL> /

       SID 'ISBLOCKING'         SID
---------- ------------- ----------
       422  IS BLOCKING         479

1 row selected.

Even better, if we throw a little v$session into the mix, the results are highly readable:

SQL> select s1.username || '@' || s1.machine
  2  || ' ( SID=' || s1.sid || ' )  is blocking '
  3  || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
  4  from v$lock l1, v$session s1, v$lock l2, v$session s2
  5  where s1.sid=l1.sid and s2.sid=l2.sid
  6  and l1.BLOCK=1 and l2.request > 0
  7  and l1.id1 = l2.id1
  8  and l2.id2 = l2.id2 ;


BLOCKING_STATUS
----------------------------------------------------------------------------------------------------
BULKLOAD@yttrium ( SID=422 )  is blocking BULKLOAD@yttrium ( SID=479 )

1 row selected.

There's still more information in the v$lock table, but in order to read that information, we need to understand a bit more about lock types and the cryptically-named ID1 and ID2 columns.

Lock type and the ID1 / ID2 columns

In this case, we already know that the blocking lock is an exclusive DML lock, since we're the ones who issued the locking statement. But most of the time, you won't be so lucky. Fortunately, you can read this information from the v$lock table with little effort.

The first place to look is the TYPE column. There are dozens of lock types, but the vast majority are system types. System locks are normally only held for a very brief amount of time, and it's not generally helpful to try to tune your library cache, undo logs, etc. by looking in v$lock! (See the V$LOCK chapter in the Oracle Database Reference for a list of system lock types.)

There are only three types of user locks, TX, TM and UL. UL is a user-defined lock -- a lock defined with the DBMS_LOCK package. The TX lock is a row transaction lock; it's acquired once for every transaction that changes data, no matter how many objects you change in that transaction. The ID1 and ID2 columns point to the rollback segment and transaction table entries for that transaction.

The TM lock is a DML lock. It's acquired once for each object that's being changed. The ID1 column identifies the object being modified.

Lock Modes

You can see more information on TM and TX locks just by looking at the lock modes. The LMODE and REQUEST columns both use the same numbering for lock modes, in order of increasing exclusivity: from 0 for no lock, to 6 for exclusive lock. A session must obtain an exclusive TX lock in order to change data; LMODE will be 6. If it can't obtain an exclusive lock because some of the rows it wants to change are locked by another session, then it will request a TX in exclusive mode; LMODE will be 0 since it does not have the lock, and REQUEST will be 6. You can see this interaction in the rows we selected earlier from v$lock:

ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
AF9E2C4C AF9E2C60        479 TX     131078      16739          0          6        685          0
ADEBEA20 ADEBEB3C        422 TX     131078      16739          6          0        697          1

Note that ID1 and ID2 in Session 2, which is requesting the TX lock (LMODE=0, REQUEST=6), point back to the rollback and transaction entries for Session 1. That's what lets us determine the blocking session for Session 2.

You may also see TX locks in mode 4, Shared mode. If a block containing rows to be changed doesn't have any interested transaction list (ITL) entries left, then the session acquires a TX lock in mode 4 while waiting for an ITL entry. If you see contention for TX-4 locks on an object, you probably need to increase INITRANS for the object.

TM locks are generally requested and acquired in modes 3, aka Shared-Row Exclusive, and 6. DDL requires a TM Exclusive lock. (Note that CREATE TABLE doesn't require a TM lock -- it doesn't need to lock any objects, because the object in question doesn't exist yet!) DML requires a Shared-Row Exclusive lock. So, in the rows we selected earlier from v$lock, you can see from the TM locking levels that these are DML locks:

ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
ADDF7EC8 ADDF7EE0        422 TM      88519          0          3          0        697          0
ADDF7F74 ADDF7F8C        479 TM      88519          0          3          0        685          0

Identifying the locked object

Now that we know that each TM row points to a locked object, we can use ID1 to identify the object.

SQL> select object_name from dba_objects where object_id=88519 ;

OBJECT_NAME
--------------
TSTLOCK

Sometimes just knowing the object is enough information; but we can dig even deeper. We can identify not just the object, but the block and even the row in the block that Session 2 is waiting on.

Identifying the locked row

We can get this information from v$session by looking at the v$session entry for the blocked session:

SQL> select row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#
  2* from v$session where sid=479 ;

ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
------------- -------------- --------------- -------------
        88519             16          171309             0

This gives us the object ID, the relative file number, the block in the datafile, and the row in the block that the session is waiting on. If that list of data sounds familiar, it's because those are the four components of an extended ROWID. We can build the row's actual extended ROWID from these components using the DBMS_ROWID package. The ROWID_CREATE function takes these arguments and returns the ROWID:

SQL> select do.object_name,
  2  row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#,
  3  dbms_rowid.rowid_create ( 1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# )
  4  from v$session s, dba_objects do
  5  where sid=543
  6  and s.ROW_WAIT_OBJ# = do.OBJECT_ID ;

OBJECT_NAME     ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# DBMS_ROWID.ROWID_C
--------------- ------------- -------------- --------------- ------------- ------------------
TSTLOCK                 88519             16          171309             0 AAAVnHAAQAAAp0tAAA

And, of course, this lets us inspect the row directly.

SQL> select * from tstlock where rowid='AAAVnHAAQAAAp0tAAA' ;

FOO BAR
--- ---
1   a

Conclusion

We've seen how to identify a blocking session, and how to inspect the very row that the waiting session is waiting for. And, I hope, learned a bit about v$lock in the process.

About the author
Source: http://toolkit.rdbms-insight.com/ .

Regards
Manoj