Quick Oracle Database Tunning Tips without OEM(Enterprice Manager)


http://docs.oracle.com/cd/B28359_01/server.111/b28274/sql_tune.htm
http://www.pythian.com/news/584/wouldnt-automatic-sql-plan-management-be-the-11g-killer-app/

create a tunning task
---------------------
variable stmt_task VARCHAR2(64);
variable sts_task  VARCHAR2(64);
variable spa_tune_task VARCHAR2(64);
EXEC :stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK( sql_text => 'SELECT S.EMPLID ,S.EMPL_RCD , PDE.EFFDT ,INS.PER_ORG ,INS.ORIG_HIRE_DT ,PDE.SEX ,PRS.BIRTHDATE ,PRS.BIRTHPLACE ,PRS.BIRTHCOUNTRY ,PRS.BIRTHSTATE ,PRS.DT_OF_DEATH ,PDE.HIGHEST_EDUC_LVL ,PDE.FT_STUDENT ,PDE.LANG_CD ,PDE.ALTER_EMPLID ,PDE.MAR_STATUS ,ASG.HOME_HOST_CLASS ,ASG.CMPNY_SENIORITY_DT ,ASG.SERVICE_DT ,ASG.PROF_EXPERIENCE_DT ,ASG.LAST_VERIFICATN_DT ,ASG.OWN_5PERCENT_CO ,ASG.PROBATION_DT ,ASG.ORG_INSTANCE_ERN ,ASG.SENIORITY_PAY_DT FROM PS_PERSON PRS ,PS_PER_ORG_ASGN ASG ,PS_PER_ORG_INST INS ,PS_PERS_DATA_EFFDT PDE ,PS_GP_PYE_STAT_WRK S WHERE S.RUN_CNTL_ID ='ABS_JAN2012_IND' AND S.OPRID = 12345 AND S.EMPLID BETWEEN 1000 AND 19999 AND S.EMPLID = PRS.EMPLID AND S.EMPLID = PDE.EMPLID AND S.FRST_JOB_SEG_IND = 'Y' AND S.EMPLID = ASG.EMPLID AND S.EMPL_RCD = ASG.EMPL_RCD AND ASG.EMPLID = INS.EMPLID AND ASG.ORG_INSTANCE_ERN = INS.ORG_INSTANCE_ERN ORDER BY S.EMPLID, S.EMPL_RCD, PDE.EFFDT DESC ');
---or-----
EXEC :stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(task_name=>'hello1223',sql_id => '4qh14ksgd4dd8',scope='COMPREHENSIVE');
---or-----
DECLARE
sql_text clob;
sqlprofile_name varchar2(30);
task_name varchar2(100);
BEGIN
sql_text :=
task_name:=dbms_sqltune.create_tuning_task(sql_text,task_name=>'TASKREQ01',scope=>'COMPREHENSIVE');
END;
/
execute a tunning task
----------------------
EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK(:stmt_task);
display a tunning task
----------------------
SET LONG 1000
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
SELECT DBMS_SQLTUNE.report_tuning_task('TASK_4188') AS recommendations FROM dual
SELECT DBMS_SQLTUNE.report_tuning_task(:stmt_task) AS recommendations FROM dual
accept a recommendation sql profile
execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_4256',task_owner => 'MD86506', replace => TRUE);
disable sql profile
EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE('SYS_SQLPROF_013c237dab800000','STATUS','DISABLED');

view task information
--------------------
SELECT task_name, status FROM dba_advisor_log WHERE owner = 'MD86506';

primary_gl_tb 

SELECT DBMS_SQLTUNE.report_tuning_task('primary_gl_tb') AS recommendations FROM dual ;
execute dbms_sqltune.accept_sql_profile(task_name => 'primary_gl_tb',task_owner => 'MD86506', replace => TRUE);

USER_ADVISOR_TASKS <-- table which have task information
•DBA_ADVISOR_TASKS
•DBA_ADVISOR_FINDINGS
•DBA_ADVISOR_RECOMMENDATIONS
•DBA_ADVISOR_RATIONALE
•DBA_SQLTUNE_STATISTICS
•DBA_SQLTUNE_BINDS
•DBA_SQLTUNE_PLANS
•DBA_SQLSET
•DBA_SQLSET_BINDS
•DBA_SQLSET_STATEMENTS
•DBA_SQLSET_REFERENCES
•DBA_SQL_PROFILES
•V$SQL
•V$SQLAREA
•V$ACTIVE_SESSION_HISTORY
enable/dislabe/delete sql profile
=================================

SELECT *
  FROM DBA_SQL_PROFILES prof,
       DBMSHSXP_SQL_PROFILE_ATTR attr
  WHERE prof.NAME=attr.PROFILE_NAME
  ORDER BY prof.name,
           attr.attr_number;
Enable/Disable
Locate the name of the SQL Profile you would like to disable and plug it in to the following statement:
EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE('PROFILE_NAME','STATUS','DISABLED');
Dropping a SQL Profile
EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('PROFILE_NAME');

 

SQL for Tunning task

Ref:
http://docs.oracle.com/cd/E11882_01/server.112/e25523/parallel006.htm
http://docs.oracle.com/cd/E11882_01/server.112/e16638/hintsref.htm
http://docs.oracle.com/cd/E14072_01/server.112/e10837/parallel.htm
http://docs.oracle.com/cd/E11882_01/server.112/e25523/parallel002.htm
Query to check parallel executions on instance:
----------------------------------------------
SELECT QCSID, SID, INST_ID "Inst", SERVER_GROUP "Group", SERVER_SET "Set",
  DEGREE "Degree", REQ_DEGREE "Req Degree"
FROM GV$PX_SESSION ORDER BY QCSID, QCINST_ID, SERVER_GROUP, SERVER_SET;

SELECT NAME, VALUE FROM GV$SYSSTAT
WHERE UPPER (NAME) LIKE '%PARALLEL OPERATIONS%'
OR UPPER (NAME) LIKE '%PARALLELIZED%' OR UPPER (NAME) LIKE '%PX%';

SELECT px.SID "SID", p.PID, p.SPID "SPID", px.INST_ID "Inst",
       px.SERVER_GROUP "Group", px.SERVER_SET "Set",
       px.DEGREE "Degree", px.REQ_DEGREE "Req Degree", w.event "Wait Event"
FROM GV$SESSION s, GV$PX_SESSION px, GV$PROCESS p, GV$SESSION_WAIT w
WHERE s.sid (+) = px.sid AND s.inst_id (+) = px.inst_id AND
      s.sid = w.sid (+) AND s.inst_id = w.inst_id (+) AND
      s.paddr = p.addr (+) AND s.inst_id = p.inst_id (+)
ORDER BY DECODE(px.QCINST_ID,  NULL, px.INST_ID,  px.QCINST_ID), px.QCSID,
DECODE(px.SERVER_GROUP, NULL, 0, px.SERVER_GROUP), px.SERVER_SET, px.INST_ID;

SELECT * from v$pq_sysstat;
SELECT * FROM v$px_process;
SELECT * FROM v$px_sesstat;
SELECT * FROM v$px_process_sysstat;


Examples
========
ALTER TABLE table_name PARALLEL (DEGREE 8);
SELECT /*+PARALLEL(table_alias, degree, nodes)*/ *
  FROM table_name ...
OR
SELECT /*+PARALLEL */ * FROM table_name/View name ...


 

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;
==============================================

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

Gather stats for PeopleSoft application database

Gather stats for PeopleSoft application database

BEGIN

DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS('SYSADM',DBMS_STATS.AUTO_SAMPLE_SIZE);

END;

Regards

Manoj

Delete archive logs of all instances running on the machine

one line command to delete archive log older than  10 days for all instance currently running on the machine.

This is useful on machines that run non-production instance and there is no specific naming convention for the SID's. 

ps -ef | grep ora_pmon | grep -v grep | awk '{print $8}' | awk '{gsub(/ora_pmon_/,"export ORACLE_SID=");}1' | grep -v awk | awk '{print $0,"; echo \"delete noprompt archivelog until time \x27 sysdate - 7 \x27 ;\" | rman target /;";} > del_arch.sh ; sh del_arch.sh




Regards
Manoj

Datafile high water mark
How lower can you resize datafiles ?
define blksize=8192 –set  your db block size

select /*+rule*/ 'alter database datafile '''||file_name||''' resize '||ceil( (nvl(hwm,1)*&blksize)/1024/1024)||'m;',
ceil( (nvl(hwm,1)*&blksize)/1024/1024 ) smallest,
ceil( blocks*&blksize/1024/1024) currsize,
ceil( blocks*&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&blksize)/1024/1024 ) savings
from
   dba_data_files a,
   (select file_id, max(block_id+blocks-1) hwm
   from
      dba_extents
   group by file_id ) b
where a.file_id = b.file_id(+);


source: dba-oracle.com

Regards
Manoj

select Query to view long running sql's

select Query to view long running sql's

set linesize 160
set pagesize 100

col machine for a15

select
machine,
ELAPSED_SECONDS,
TIME_REMAINING,
100 - (TIME_REMAINING*100/ELAPSED_SECONDS),
OPNAME  ,
b.TARGET,
b.sid
from
v$session a,
v$session_longops b
where a.sid=b.sid and
time_remaining != 0 ;

Given a username, gives his roles and privileges

Query to return roles and privileges in tabular format. Usefull for auditing.

select
  lpad(' ', 2*level) || granted_role "User, his roles and privileges"
from
  (
  /* THE USERS */
    select
      null     grantee,
      username granted_role
    from
      dba_users
    where
      username like upper('%&enter_username%')
  /* THE ROLES TO ROLES RELATIONS */
  union
    select
      grantee,
      granted_role
    from
      dba_role_privs
  /* THE ROLES TO PRIVILEGE RELATIONS */
  union
    select
      grantee,
      privilege
    from
      dba_sys_privs
  )
start with grantee is null
connect by grantee = prior granted_role

Allocated Tablespace quota for each oracle database user

select
username,
tablespace_name,
trunc(bytes/1024/1024,0) MB,
decode(max_bytes, '-1', 'UNLIMITED',MAX_BYTES/1024/1024) "QUOTA (MB)"
from
dba_ts_quotas

List of Database users about to expire in 'n' days.

select * from dba_users where expiry_date  > sysdate - 10 and expiry_date < sysdate + 10

Database size with breakdown of datafiles, tempfiles, redologs and archivelogs

select
a.bytes "Data Files GB",
b.bytes "Temp Files GB",
c.bytes "Log Files GB",
d.bytes "Archive Logfiles 30 Days",
(a.bytes + b.bytes + c.bytes + d.bytes) "Total GB"
from
(select sum(bytes/1024/1024/1024) bytes from dba_data_files) a ,
(select sum(bytes/1024/1024/1024) bytes from dba_temp_files) b,
(select count(1) * 0.05859375 bytes from v$logfile) c,
(SELECT count(1) * 0.05859375 bytes from v$log_history
where to_date(first_time) > sysdate - 30) d

Tablespace Free size for E-business suite


I have included only those tablespaces which frequently become full

select
  a.tablespace_name,
  round(sum(a.bytes/1024/1024),2) TOTAL_MB,  
  round(sum
  (
    ( case
    when autoextensible = 'YES' then
    (a.maxbytes - a.user_bytes) + nvl(b.bytes,0)
    when autoextensible = 'NO' then
    nvl(b.bytes,0)
    end
    )/1024/1024
  ),2) FREE_MB
from
  dba_data_files a,
  (
    select file_id,
      sum(bytes) bytes
    from dba_free_space
    group by file_id
  ) b
where
  a.file_id = b.file_id(+)
and a.tablespace_name in
(
'SYSTEM',
'APPLSYSD',
'APPLSYSX',
'GLX',
'GLD',
'APD',
'APX',
'ARD',
'ARX',
'UNDOTS1'
)
group by a.tablespace_name

Temp Tablespace error's and resolution

ora-01652

Troubleshooting Details
Error explanation
There is no enough space for a temporary segment. The size reported in the error message is the number of contiguous free Oracle blocks that cannot be found in the listed tablespace.

How to approach the ORA-1652 error
There are two views in the database that help keep track of the free space in the temporary tablespace: v$sort_usage and v$tempseg_usage (from 9i onwards).

In order for the approach to be relevant, the following investigation steps should be followed immediately after the error occurs : Check the status of the sort segment utilization :
select TABLESPACE_NAME,TOTAL_BLOCKS,USED_BLOCKS,FREE_BLOCKS from v$sort_segment;
If USED_BLOCKS = TOTAL_BLOCKS, find which user and statement is using the temporary sort segment by following: (Note 317441.1 - How Do You Find Who And What SQL Is Using Temp Segments) In some cases, you may find that the ORA-1652 is not reported for a temporary tablespace, but a permanent one. This is not an abnormal behaviour and it can occur for example while creating or dropping objects like tables and indexes in permanent tablespaces. (Reference : Note 19047.1 - OERR: ORA 1652 unable to extend temp segment by %s in tablespace %s) In such cases the following note will be of use :
( Note 100492.1 - ORA-01652 : Estimate Space Needed to CREATE INDEX)

In the process of diagnostic and tuning, the resumable statement feature can be useful. It allows the DBA, once having applied the appropriate solution to the space allocation issue, to resume the suspended transaction which does not loose all the work done previously. By querying dba_resumable one can find the statement that is executed when ORA-1652 occurs. See Note 136941.1 - Using RESUMABLE Session to Avoid Transaction Abort Due to Space Errors.

There are two ways of solving this error

Add more tempfiles, increase the size of the current ones or enable auto extend and the most important:
Tune the queries/statements so that the sort operations are done in memory and not on the disk.
Note that the extents allocated for a user's sort segment are NOT deallocated but are marked as FREE from performance reasons. The FREE extents can be further used by other users that are executing sort
operations.  After the database restart the allocated extens are NOT released either but are FREE to be reused.
Hence, seeing  the physical space of the temporary tablespace fully allocated is not a reason to be concerned per se. The above query of V$SORT_SEGMENT should be used to establish the free space inside the temporary tablespace.

Known issues
Note 463819.1 - Database HANG After Migrating to 10.2 : ORA-1652
Note 164850.1 - ORA-01652 in Resumable Statements Prevents any SELECT on DBA_RESUMABLE View

SQL Queries

Temp Tablespace usage Report

SELECT   A.tablespace_name tablespace, D.mb_total,
         SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
         D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM     v$sort_segment A,
         (
         SELECT   B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
         FROM     v$tablespace B, v$tempfile C
         WHERE    B.ts#= C.ts#
         GROUP BY B.name, C.block_size
         ) D
WHERE    A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total

Temp Space usage session wise

SELECT   S.sid || ',' || S.serial# sid_serial, S.username,
         T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
         T.sqladdr address, Q.hash_value, Q.sql_text
FROM     v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS
WHERE    T.session_addr = S.saddr
AND      T.sqladdr = Q.address (+)
AND      T.tablespace = TBS.tablespace_name
--AND      T.blocks * TBS.block_size / 1024 / 1024  > 5024
ORDER BY S.sid

Regards
Manoj

Check Database Memory Usage SGA+PGA

UNIX tools which look at UNIX process memory are unable to distinguish between private and shared memory and thus over-report memory usage for Oracle processes.

Check memory usage from Oracle Database

Total memory usage
select sum(bytes)/1024/1024 mb from (select bytes from v$sgastat union all select value bytes from v$sesstat s, v$statname n where n.STATISTIC# = s.STATISTIC# and  n.name = 'session pga memory' );

Process wise listing
select a.sid,value/(1024*1024) "MB",program from v$session a, v$sesstat b where a.sid=b.sid and b.statistic#=(select statistic# from v$statname where name='session pga memory') order by value/(1024*1024) desc ;


source: oracle communities

Script to find free space in tablesapces

Script to find free space in tablesapces



select
  a.tablespace_name,
  round(sum(a.bytes/1024/1024),2) TOTAL_MB, 
  round(sum
  (
    ( case
    when autoextensible = 'YES' then
    (a.maxbytes - a.user_bytes) + nvl(b.bytes,0)
    when autoextensible = 'NO' then
    nvl(b.bytes,0)
    end
    )/1024/1024
  ),2) FREE_MB
from
  dba_data_files a,
  (
    select file_id,
      sum(bytes) bytes
    from dba_free_space
    group by file_id
  ) b
where
  a.file_id = b.file_id(+)
group by a.tablespace_name;

Find current SCN number in Database

select current_scn from v$database;
select dbms_flashback.get_system_change_number() from dual;

-- find where current SCN lives in SGA:

SQL> select ksmfsadr from x$ksmfsv where ksmfsnam = 'kcsgscn_';

KSMFSADR
--------
20009104

-- on 32bit environments this is the most significant half word of 8 byte SCN. To get the other, least significant half, you have to add 4 to the address and combine the results:

SQL> l
 1  select dbms_flashback.get_system_change_number flashback_scn,
 2         current_scn,
 3         (select to_number(ksmmmval,'XXXXXXXX')
 4         from x$ksmmem where addr = hextoraw('20009104')) * power(2,32) +
 5         (select to_number(ksmmmval,'XXXXXXXX')
 6         from x$ksmmem where addr = hextoraw('20009108')) direct_scn
 7* from v$database
SQL> /

FLASHBACK_SCN CURRENT_SCN DIRECT_SCN
------------- ----------- ----------
     2633692     2633692    2633692

The SCN lives in fixed part of SGA, thus its location doesn't change over instance bounces or SGA size changes. It might change only if you relink Oracle binary or change SGA mapped base address.


source:freelists.org 
Regards
Manoj 

Database Size Limits

Item	Type of Limit	Limit Value
Database Block Size	Minimum	2048 bytes; must be a multiple of operating system physical block size
Database Block Size	Maximum	Operating system dependent; never more than 32 KB
Database Blocks	Minimum in initial extent of a segment	2 blocks
Database Blocks	Maximum per datafile	Platform dependent; typically 222 - 1 blocks
Controlfiles	Number of control files	1 minimum; 2 or more (on separate devices) strongly recommended
Controlfiles	Size of a control file	Dependent on operating system and database creation options; maximum of 20,000 x (database block size)
Database files	Maximum per tablespace	Operating system dependent; usually 1022
Database files	Maximum per database	65533 May be less on some operating systems Limited also by size of database blocks and by the DB_FILES initialization parameter for a particular instance
Database extents	Maximum per dictionary managed tablespace	4 GB * physical block size (with K/M modifier); 4 GB (without K/M modifier)
Database extents	Maximum per locally managed (uniform) tablespace	2 GB * physical block size (with K/M modifier); 2 GB (without K/M modifier)
Database file size	Maximum	Operating system dependent. Limited by maximum operating system file size; typically 222 or 4 MB blocks
MAXEXTENTS	Default value	Derived from tablespace default storage or DB_BLOCK_SIZE initialization parameter
MAXEXTENTS	Maximum	Unlimited
Redo Log Files	Maximum number of logfiles	Limited by value of MAXLOGFILES parameter in the CREATE DATABASE statement Control file can be resized to allow more entries; ultimately an operating system limit
Redo Log Files	Maximum number of logfiles per group	Unlimited
Redo Log File Size	Minimum size	4 MB
Redo Log File Size	Maximum Size	Operating system limit; typically 2 GB
Tablespaces	Maximum number per database	64 K Number of tablespaces cannot exceed the number of database files because each tablespace must include at least one file
Bigfile Tablespaces	Number of blocks	A bigfile tablespace contains only one datafile or tempfile, which can contain up to approximately 4 billion ( 232 ) blocks. The maximum size of the single datafile or tempfile is 128 terabytes (TB) for a tablespace with 32 K blocks and 32 TB for a tablespace with 8 K blocks.
Smallfile (traditional) Tablespaces	Number of blocks	A smallfile tablespace is a traditional Oracle tablespace, which can contain 1022 datafiles or tempfiles, each of which can contain up to approximately 4 million (222) blocks.
External Tables file	Maximum size	Dependent on the operating system. An external table can be composed of multiple files.

Maximum database size is 8Pb in Oracle9i & 10g (Small file Tablespaces).

Block Sz   Max Datafile Sz (Gb)   Max DB Sz (Tb)
   2,048                      8              512 

   4,096                     16            1,024

   8,192                     32            2,048

  16,384                     64            4,096

  32,768                    128            8,192

The maximum database size is 8Eb in Oracle 10g (Big file tablespaces).

 Block Sz   Max Datafile Sz (Gb)   Max DB Sz (Tb)

2,048                  8,192          524,264 

   4,096                 16,384        1,048,528

   8,192                 32,768        2,097,056

  16,384                 65,536        4,194,112

  32,768                131,072        8,388,224

Logical Database limit

Logical Database Limits

Item	Type of Limit	Limit Value
GROUP BY clause	Maximum length	The GROUP BY expression and all of the nondistinct aggregate functions (for example, SUM, AVG) must fit within a single database block.
Indexes	Maximum per table	Unlimited
Indexes	Total size of indexed column	75% of the database block size minus some overhead
Columns	Per table	1000 columns maximum
Columns	Per index (or clustered index)	32 columns maximum
Columns	Per bitmapped index	30 columns maximum
Constraints	Maximum per column	Unlimited
Subqueries	Maximum levels of subqueries in a SQL statement	Unlimited in the FROM clause of the top-level query 255 subqueries in the WHERE clause
Partitions	Maximum length of linear partitioning key	4 KB - overhead
Partitions	Maximum number of columns in partition key	16 columns
Partitions	Maximum number of partitions allowed per table or index	1024K - 1
Rows	Maximum number per table	Unlimited
Stored Packages	Maximum size	PL/SQL and Developer/2000 may have limits on the size of stored procedures they can call. The limits typically range from 2000 to 3000 lines of code. See Also: Your PL/SQL or Developer/2000 documentation for details
Trigger Cascade Limit	Maximum value	Operating system-dependent, typically 32
Users and Roles	Maximum	2,147,483,638
Tables	Maximum per clustered table	32 tables
Tables	Maximum per database	Unlimited

Process and Runtime Limits

Item	Type of Limit	Limit Value
Instances per database	Maximum number of cluster database instances per database	Operating system-dependent
Locks	Row-level	Unlimited
Locks	Distributed Lock Manager	Operating system dependent
SGA size	Maximum value	Operating system-dependent; typically 2 to 4 GB for 32-bit operating systems, and > 4 GB for 64-bit operating systems
Advanced Queuing Processes	Maximum per instance	10
Job Queue Processes	Maximum per instance	1000
I/O Slave Processes	Maximum per background process (DBWR, LGWR, etc.)	15
I/O Slave Processes	Maximum per Backup session	15
Sessions	Maximum per instance	32 KB; limited by the PROCESSES and SESSIONS initialization parameters
Global Cache Service Processes	Maximum per instance	10
Shared Servers	Maximum per instance	Unlimited within constraints set by the PROCESSES and SESSIONS initialization parameters, for instance
Dispatchers	Maximum per instance	Unlimited within constraints set by PROCESSES and SESSIONS initialization parameters, for instance
Parallel Execution Slaves	Maximum per instance	Unlimited within constraints set by PROCESSES and SESSIONS initialization parameters, for instance
Backup Sessions	Maximum per instance	Unlimited within constraints set by PROCESSES and SESSIONS initialization parameters, for instance

Datatype Limits

Datatypes	Limit	Comments
BFILE	Maximum size: 4 GB Maximum size of a file name: 255 characters Maximum size of a directory name: 30 characters Maximum number of open BFILEs: see Comments	The maximum number of BFILEs is limited by the value of the SESSION_MAX_OPEN_FILES initialization parameter, which is itself limited by the maximum number of open files the operating system will allow.
BLOB	Maximum size: (4 GB - 1) * DB_BLOCK_SIZE initialization parameter (8 TB to 128 TB)	The number of LOB columns per table is limited only by the maximum number of columns per table (that is, 1000Foot 1 ).
CHAR	Maximum size: 2000 bytes	None
CHAR VARYING	Maximum size: 4000 bytes	None
CLOB	Maximum size: (4 GB - 1) * DB_BLOCK_SIZE initialization parameter (8 TB to 128 TB)	The number of LOB columns per table is limited only by the maximum number of columns per table (that is, 1000Footref 1).
Literals (characters or numbers in SQL or PL/SQL)	Maximum size: 4000 characters	None
LONG	Maximum size: 2 GB - 1	Only one LONG column is allowed per table.
NCHAR	Maximum size: 2000 bytes	None
NCHAR VARYING	Maximum size: 4000 bytes	None
NCLOB	Maximum size: (4 GB - 1) * DB_BLOCK_SIZE initialization parameter (8 TB to 128 TB)	The number of LOB columns per table is limited only by the maximum number of columns per table (that is, 1000Footref 1).
NUMBER	999...(38 9's) x10125 maximum value -999...(38 9's) x10125 minimum value	Can be represented to full 38-digit precision (the mantissa) Can be represented to full 38-digit precision (the mantissa)
Precision	38 significant digits	None
RAW	Maximum size: 2000 bytes	None
VARCHAR	Maximum size: 4000 bytes	None
VARCHAR2	Maximum size: 4000 bytes	None

*Source : OTN
 

Regards

Manoj

How to Find out available (current & old) log file names of concurrent Manager.

How to Find out available (current & old) log file names of concurrent Manager.

Script provided by oracle

afcmcreq.sql
Displays the concurrent manager and the name of its log file that processed a request.



Tables:

fnd_concurrent_processes (LOGFILE_NAME --> logfile name)
fnd_concurrent_queues (CONCURRENT_QUEUE_NAME --> short name of manager)
fnd_concurrent_queues_tl (USER_CONCURRENT_QUEUE_NAME --> Name of manager, as seen on front end screen)

Foreign Key: CONCURRENT_QUEUE_ID.

Query
-----

set linesize 350
set pagesize 100
col Name for a30
col "Short Name" for a10
col Host for a13
col "Log File" for a40

select b.USER_CONCURRENT_QUEUE_NAME "Name", b.CONCURRENT_QUEUE_NAME "Short Name", a.PROCESS_START_DATE "Start Date", a.NODE_NAME "Host", a.LOGFILE_NAME "Log File" from fnd_concurrent_processes a, fnd_concurrent_queues_tl b where a.CONCURRENT_QUEUE_ID = b.CONCURRENT_QUEUE_ID and b.CONCURRENT_QUEUE_NAME like '%&Short_Name%';

Note: Change b.CONCURRENT_QUEUE_NAME to b.USER_CONCURRENT_QUEUE_NAME if you want to query through concurrent manager name. CONCURRENT_QUEUE_NAME is manager short name

Regards
Manoj