Oracle R12 DBA scripts for troubleshooting - part 1

SQL Sript to view database sessions:

  SELECT s.Inst_id,
    s.sid,
    s.module,
    s.action,
    s.program,
    s.process,
    sa.executions,
    s.event,
    sa.sql_text,
    sa.buffer_gets,
    sa.disk_reads,
    sa.optimizer_cost,
    sa.optimizer_mode,
    s.blocking_session,
    ROUND(s.last_call_et/60,2) "Elapsed Time"
  FROM gv$session s,
    gv$sqlarea sa,
    gv$process p
  WHERE s.sql_address=sa.address(+)
  AND p.addr         =s.paddr
  AND s.inst_id      =p.inst_id
  AND s.inst_id      =sa.inst_id(+) ;

If there is a performance issue, you can look at columns like sql_text,optimizer_cost, event etc to figure out cause for slowness.


SQL script to list scheduled concurrent requests:

SELECT fcrsv.REQUEST_ID,
    fcrsv.program,
    fcrsv.REQUESTED_START_DATE,
    ROUND(MIN_RUN_TIME,2) min_run_time,
    ROUND(MAX_RUN_TIME,2) max_run_time,
    ROUND(AVG_RUN_TIME,2) avg_run_time,
    fu.description user_description,
    fcrsv.argument_text,
    fcrsv.program_short_name,
    fcrsv.requestor,
    fcrsv.printer,
    fcr.resubmit_interval
    ||' '
    || fcr.resubmit_interval_unit_code
    ||', '
    ||DECODE(fcr.resubmit_interval_type_code,'START','From the start of prior run','END','from the end of prior run',fcr.resubmit_interval_type_code) Interval_type
  FROM FND_CONC_REQ_SUMMARY_V fcrsv,
    fnd_concurrent_requests fcr,
    FND_CONC_PROG_ONSITE_INFO fcpoi,
    fnd_user fu
  WHERE fcrsv.PROGRAM_APPLICATION_ID=fcpoi.PROGRAM_APPLICATION_ID
  AND fcrsv.CONCURRENT_PROGRAM_ID   =fcpoi.CONCURRENT_PROGRAM_ID
  AND fcr.request_id                =fcrsv.request_id
  AND fcrsv.PHASE_CODE              ='P'
  AND fcrsv.STATUS_CODE             ='I'
  AND fcrsv.requested_start_date    >sysdate -1
    --  AND requested_start_date         <= sysdate+1/24
  AND fcrsv.hold_flag ='N'
  AND fu.user_id      =fcrsv.requested_by
  ORDER BY 3 ;

SQL script view the details of database sessions associated with concurrent request:

  SELECT fcr.request_id,
    s.Inst_id,
    s.sid,
    s.module,
    s.program,
    sa.executions,
    s.event,
    sa.sql_text,
    sa.buffer_gets,
    sa.disk_reads,
    sa.optimizer_cost,
    sa.optimizer_mode,
    ROUND(s.last_call_et/60,2) "Elapsed Time"
  FROM gv$session s,
    gv$sqlarea sa,
    gv$process p,
    fnd_concurrent_requests fcr
  WHERE s.sql_address=sa.address(+)
  AND p.addr         =s.paddr
  AND s.inst_id      =p.inst_id
  AND s.inst_id      =sa.inst_id(+)
  AND p.spid         =Fcr.oracle_process_id
  AND s.process      =fcr.os_process_id ;

SQL script to view list of active application users:

  SELECT s.INST_ID,
    S.SID,
    S.SERIAL#,
    L.PROCESS_SPID,
    S.machine,
    s.event,
    s.action,
    s.module,
    s.blocking_session,
    s.last_call_et,
    -- NVL(F.START_TIME, NVL(R.START_TIME, L.START_TIME)) TIME,
    USR.USER_NAME,
    RSP.RESPONSIBILITY_NAME,
    FRM.USER_FORM_NAME,
    FLOOR(SYSDATE -s.logon_time)
    || ':'
    || mod(FLOOR((SYSDATE - s.logon_time)*24), 24)
    || ':'
    ||LTRIM(TO_CHAR(TRUNC(((SYSDATE - s.logon_time)*24- FLOOR((SYSDATE - s.logon_time)*24))*60), '09')) TIME
  FROM FND_RESPONSIBILITY_TL RSP,
    FND_FORM_TL FRM,
    FND_USER USR,
    FND_LOGINS L,
    FND_LOGIN_RESPONSIBILITIES R,
    FND_LOGIN_RESP_FORMS F,
    GV$SESSION S
  WHERE R.LOGIN_ID        = F.LOGIN_ID
  AND R.LOGIN_RESP_ID     = F.LOGIN_RESP_ID
  AND L.LOGIN_ID          = R.LOGIN_ID
  AND L.END_TIME         IS NULL
  AND R.END_TIME         IS NULL
  AND F.END_TIME         IS NULL
  AND L.USER_ID           = USR.USER_ID
  AND R.RESPONSIBILITY_ID = RSP.RESPONSIBILITY_ID
  AND R.RESP_APPL_ID      = RSP.APPLICATION_ID
  AND RSP.LANGUAGE        = userenv('LANG')
  AND FRM.FORM_ID         = F.FORM_ID
  AND FRM.APPLICATION_ID  = F.FORM_APPL_ID
  AND FRM.LANGUAGE        = userenv('LANG')
  AND S.AUDSID            = F.AUDSID
  UNION
  SELECT s.INST_ID,
    S.SID,
    S.SERIAL#,
    L.PROCESS_SPID,
    s.machine,
    s.event,
    s.action,
    s.module,
    s.blocking_session,
    s.last_call_et,
    -- NVL(R.START_TIME, L.START_TIME) TIME,
    USR.USER_NAME,
    RSP.RESPONSIBILITY_NAME,
    'FNDSCSGN',
    FLOOR(SYSDATE -s.logon_time)
    || ':'
    || mod(FLOOR((SYSDATE - s.logon_time)*24), 24)
    || ':'
    ||LTRIM(TO_CHAR(TRUNC(((SYSDATE - s.logon_time)*24- FLOOR((SYSDATE - s.logon_time)*24))*60), '09')) TIME
  FROM FND_RESPONSIBILITY_TL RSP,
    FND_USER USR,
    FND_LOGINS L,
    FND_LOGIN_RESPONSIBILITIES R ,
    GV$SESSION S
  WHERE L.LOGIN_ID        = R.LOGIN_ID
  AND L.END_TIME         IS NULL
  AND R.END_TIME         IS NULL
  AND L.USER_ID           = USR.USER_ID
  AND R.RESPONSIBILITY_ID = RSP.RESPONSIBILITY_ID
  AND R.RESP_APPL_ID      = RSP.APPLICATION_ID
  AND RSP.LANGUAGE        = userenv('LANG')
  AND S.AUDSID            = R.AUDSID
  ORDER BY 6 ;


SQL script view list database sessions with top usage of CPU:

  SELECT "INST_ID",
    "SID",
    "SPID",
    "CPU",
    "EVENT",
    "PROGRAM",
    "MODULE",
    "ACTION",
    "LAST_CALL_ET",
    "SQL_TEXT"
  FROM
    (SELECT s.inst_id,
      s.sid,
      p.spid,
      (SELECT value
      FROM v$sesstat
      WHERE v$sesstat.sid = s.sid
      AND statistic#      =
        (SELECT statistic# FROM v$statname WHERE name = 'CPU used by this session'
        )
      ) cpu,
      s.event,
      s.program,
      s.module,
      s.action,
      s.last_call_et,
      sa.sql_text--,p.pga_user_mem,p.pga_alloc_mem
    FROM gv$session s,
      gv$process p,
      gv$sqlarea sa
    WHERE s.paddr    =p.addr
    AND s.sql_address=sa.address
    AND s.status     ='ACTIVE'
    )
  WHERE cpu  IS NOT NULL
  AND module IS NOT NULL
  AND sql_text NOT LIKE 'begin fndcp%'
  AND sql_text NOT LIKE 'BEGIN FND_CP_GSM%'
  AND sql_text NOT LIKE 'BEGIN FND_CP_OPP%'
  AND rownum < 21
  ORDER BY 4 DESC ;

Note: This blog has been moved to http://www.oamonitor.com/wordpress/blog

Oracle Applications on Amazon's Elasitc Cloud Computing (EC2)

New Post: Start your own Oracle Vision (Demo) instance R12.2.4 using Amazon AWS services

Amazon Elastic Compute Cloud (Amazon EC2) is a web service that provides resizable compute capacity in the cloud.  The EC2 web service interface allows users to create linux / windows instance with different configurations of CPU and memory, in minutes.  Using the web services interface, users can also scale the capacity of instances both up and down.  For more information on Amazon EC2 service and functionality, please visit http://aws.amazon.com/ec2/#functionality .

Subscribing to EC2 service

Subscribing to EC2 service is easy.  Just go to aws.amazon.com and sign up.  You will have to do the following steps, before you can start an instance.  These are high level steps based on my experience.

1. Subscribe for EC2 service. 
2. Log in to "AWS management console".
3. Click on VPC tab and click on Get started creating a VPC to create VPC.  You are creating a Virtual Private Cloud in this step which will provide you a subnet private to your account.
4. In left side bar, under "Network & Security", click on Security Groups to create a VPC based security group.
5. In left side bar, under "Network & Security", click on Key Pairs to create a new key pair.  Key pair will be used to access Linux instance via ssh.

Starting up Oracle E-Business Vision Instance

1. Click on EC2 tab
2. In left side bar, under IMAGES, Click on  AMI.
3. Change the viewing filter to "All Images"  and "All Platforms"
4. Search for image id ami-57c1013e (Oracle E-Business Suite 12.1.3 Database 64-bit - OVM" .
5. Right click on the row and select "Launch Instance"
6. Go through the wizard for creating an instance. 
7. Once the instance is created, create another instance by selecting image id ami-227a8c4b (Oracle-E-Business-Suite-12.1.3-App-Tier).
8. Here is the link from Amazon's AWS website on how to connect to linux instance using SSH : http://docs.amazonwebservices.com/AWSEC2/latest/UserGuide/AccessingInstancesLinux.html
9. Once you are able to connect to both linux instances,
10. Visit the link https://aws.amazon.com/amis/oracle-e-business-suite-12-1-3-app-tier-64-bit on documentation of oracle images which provides "quick start" guide and other information on starting the vision instance.

If you need more information or help on starting up the vision instance using Oracle image, please let me know via email: naresh.awasthi@gmail.com

Thanks
Naresh