Running SQL tuning advisory

Running SQL tuning advisor
========================================================================
set long 65536
set longchunksize 65536
set linesize 100
set pages 255
set lines 1000

DECLARE
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          sql_id      => 'ftt3txfr7qxwu',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 600,
                          task_name   => 'ftt3txfr7qxwu_TASK_NAME_1',
                          description => 'Tuning task for statement ftt3txfr7qxwu');
END;

---------------------------------------------------------------------------
EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'ftt3txfr7qxwu_TASK_NAME_1');
----------------------------------------------------------------------------

SELECT DBMS_SQLTUNE.report_tuning_task('ftt3txfr7qxwu_TASK_NAME_1') AS recommendations FROM dual;

===========================

select table_name, stale_stats,last_analyzed from dba_tab_statistics where table_name='&1';
select owner, index_name, last_analyzed, global_stats from dba_indexes where index_name='&1';

====================

select * from table(dbms_xplan.display_awr('d6xu9cxq57ant'));

select plan_table_output from table(DBMS_XPLAN.display_cursor('7s78wpgr5wadp'));

EXPLAIN PLAN FOR

SELECT * FROM   emp e, dept d WHERE  e.deptno = d.deptno AND    e.ename  = 'SMITH';

SET LINESIZE 130

SET PAGESIZE 0

SELECT * FROM   TABLE(DBMS_XPLAN.DISPLAY);

==================

select INST_ID,SID from gv$session s,apps.fnd_concurrent_requests r where s.audsid=r.oracle_session_id and r.request_id=61825419;

select sid,event,last_call_et,SQL_ID,P1,P2 from gv$session where SID=1305;

select sid,event,last_call_et,SQL_ID,P1,P2 from gv$session where SID=&1;

V$SESSION_EVENT can show that many waits, but it does not show which file and block number. However, V$SESSION_WAIT shows the file number in P1, the block number read in P2, and the number of blocks read in P3 (P1 and P2 let you determine for which segments the wait event is occurring).

P1—The absolute file number for the data file involved in the wait.
P2—The block number within the data file referenced in P1 that is being waited upon.
P3—The reason code describing why the wait is occurring.

select module,sql_hash_value,event,action,last_call_et from v$session where sid ='&1';

select sid,SERIAL#,inst_id,status,SQL_ID,LAST_CALL_ET  from gv$session where SID in ('15654');

select SQL_TEXT from gv$sql where sql_id='&1';

select plan_table_output from table(DBMS_XPLAN.display_cursor('d6xu9cxq57ant'));

select sid,event,last_call_et,SQL_ID,P1,P2 from gv$session where SID=15654;

select SID,serial#,SQL_ID from v$session where sql_id in ('d6xu9cxq57ant');

================

execute dbms_stats.gather_table_stats(ownname => 'XX_DISCO', tabname => 'EXB_FA_DETAILS_MV', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE  AUTO', cascade => TRUE); 

execute dbms_sqltune.accept_sql_profile(task_name => 'cxt97vnjpys8r_tuning_task', task_owner => 'SYS', replace => TRUE);  

===============

Child request:

set lines 200

col USER_CONCURRENT_PROGRAM_NAME for a40

col PHASE_CODE for a10

col STATUS_CODE for a10

col COMPLETION_TEXT for a20

SELECT sum.request_id,req.PARENT_REQUEST_ID,sum.user_concurrent_program_name, DECODE

(sum.phase_code,'C','Completed',sum.phase_code) phase_code, DECODE(sum.status_code,'D',

'Cancelled' ,

'E', 'Error' , 'G', 'Warning', 'H','On Hold' , 'T', 'Terminating', 'M', 'No Manager' , 'X',

'Terminated', 'C', 'Normal', sum.status_code) status_code, sum.actual_start_date,

sum.actual_completion_date, sum.completion_text FROM apps.fnd_conc_req_summary_v sum,

apps.fnd_concurrent_requests req where req.request_id=sum.request_id and req.PARENT_REQUEST_ID =

'&parent_concurrent_request_id';

====================

alter session set current_schema=APPS;

==============

Related Documents:

Doc ID 215187.1 SQLT Diagnostic Tool

Document 1614107.1 SQLT Usage Instructions

Document 1454160.1 FAQ: SQLT (SQLTXPLAIN) Frequently Asked Questions

==================

Killing in RAC ::::::::::

SELECT  'ALTER SYSTEM KILL SESSION '||''''||sid||','||serial#||','|| '@' || inst_id ||''''|| ';' from gv$session where osuser = 'xxwwexp' and machine in ('ccixapp-prod1-128','ccixapp-prod2-155');

SELECT  'ALTER SYSTEM KILL SESSION '||''''||sid||','||serial#||','|| '@' || inst_id ||''''|| ';' from gv$session where sql_id in ('az41pugxbqdha','cmndhqbj6h5dj','d6xu9cxq57ant');

=================

Manager name:

select b.USER_CONCURRENT_QUEUE_NAME from apps.fnd_concurrent_processes a, 

apps.fnd_concurrent_queues_vl b, apps.fnd_concurrent_requests c

where  a.CONCURRENT_QUEUE_ID = b.CONCURRENT_QUEUE_ID

and    a.CONCURRENT_PROCESS_ID = c.controlling_manager

and    c.request_id ='&request_id';

==================

select s.snap_id,to_char(s.begin_interval_time,'DD-MON-YYYY DY HH24:MI') sample_end

, q.sql_id

, q.plan_hash_value

, q.EXECUTIONS_DELTA "executions"

, q.VERSION_COUNT "VersionCount"

, q.INVALIDATIONS_DELTA  "Invalidation"

, round(disk_reads_delta/executions_delta) "Phy IO/Exec"

, round(buffer_gets_delta/executions_delta) "LogIO/Exec"

, round(elapsed_time_delta*0.0000001/executions_delta,2) "Elapsed_Time_In_Sec/Exec"

, round(cpu_time_delta*0.0000001/executions_delta,2) "CPU_Time_In_Sec/Exec"

, round(rows_processed_delta/executions_delta) "Rows/Exec"

from dba_hist_sqlstat q, dba_hist_snapshot s

where q.SQL_ID=trim('&sql_id')

and s.snap_id = q.snap_id

and s.dbid = q.dbid

and s.instance_number = q.instance_number

and q.executions_delta > 0

and begin_interval_time BETWEEN (SYSDATE - nvl(to_number('&num_days'),1)) AND SYSDATE

order by q.sql_id,s.snap_id desc;

========================

Accept the profile generated by the tuning task using:

EXECUTE dbms_sqltune.accept_sql_profile(task_name => 'ftt3txfr7qxwu_TASK_NAME_1', description => 'Tuning test for sql_id ftt3txfr7qxwu');


Drop the tuning task with:

EXECUTE dbms_sqltune.drop_tuning_task('ftt3txfr7qxwu_TASK_NAME_1');

======================

ALTER SESSION SET nls_date_format='dd-mon-yyyy hh24:mi:ss';
col description FOR a40
SELECT task_name, description, advisor_name, execution_start, execution_end, status FROM dba_advisor_tasks WHERE owner='TEST' ORDER BY task_id DESC;

SELECT name, created, description, status FROM dba_sql_profiles;

Generate a Specific Schema Form through AD utility adadmin:

1. Start the adadmin Utility
2. Step Through the Initial adadmin Questions
Your default directory is '/oracle/prod/apps/apps_st/appl'.
Is this the correct APPL_TOP [Yes] ?
Note:-If the above is true, then hit the [Enter] key.
AD Administration records your AD Administration session in a text file
you specify.  Enter your AD Administration log file name or press [Return]
to accept the default file name shown in brackets.
Filename [adadmin.log] : POXBWVRP.log
Note:-Here you can record your adadmin session in a specific log file like above.
************* Start of AD Administration session *************
AD Administration version: 12.0.0
AD Administration started at: Tue May 07 2013 21:01:17
APPL_TOP is set to /oracle/prod/apps/apps_st/appl
You can be notified by email if a failure occurs.
Do you wish to activate this feature [No] ?
Note:-This option only works in UNIX and the purpose of this question is to notify the AD administrator by mail if any failure occurred while adadmin was running in unattended mode.
Please enter the batchsize [1000] :
Note:-This option asks for a specific batch size, to reserve memory when adadmin validates package and procedure information. This option only is meaningful when running database administrative related tasks. For the purpose of generating a form, you can leave the default value.
You are about to use or modify Oracle Applications product tables
in your ORACLE database 'ancprod'
using ORACLE executables in '/oracle/prod/apps/tech_st/10.1.2'.
Is this the correct database [Yes] ?
Note:-If correct, hit the [Enter] key. Otherwise, exit this utility by typing "abort" plus the [b] key and verify if your environment has been sourced correctly.
AD Administration needs the password for your 'SYSTEM' ORACLE schema
in order to determine your installation configuration.
Enter the password for your 'SYSTEM' ORACLE schema:
Note:-Here you must enter your SYSTEM database user password.
The ORACLE username specified below for Application Object Library
uniquely identifies your existing product group: APPLSYS
Enter the ORACLE password of Application Object Library [APPS] :
Note:-Here you must enter your SYSTEM database user password.
After finally reaching this point, the utility will try to connect to your database and get information regarding your system.

3. The next step which requires user interaction is the main menu:
            AD Administration Main Menu
   --------------------------------------------------
   1.    Generate Applications Files menu
   2.    Maintain Applications Files menu
   3.    Compile/Reload Applications Database Entities menu
   4.    Maintain Applications Database Entities menu
   5.    Change Maintenance Mode
   6.    Exit AD Administration

Enter your choice [6] : 1

         Generate Applications Files
   ----------------------------------------
   1.    Generate message files
   2.    Generate form files
   3.    Generate report files
   4.    Generate product JAR files
   5.    Return to Main Menu

Enter your choice [5] : 2

AD utilities can support a maximum of 999 workers. Your
current database configuration supports a maximum of 276 workers.
Oracle recommends that you use between 64 and 128 workers.
Enter the number of workers [64] : 8
Note:-In order to determine the optimal number of workers for your system, you should consider the following general rule:
Total of available workers for your system = (Number of processors) x 3
Your current character set is "UTF8".
Do you want to generate Oracle Forms objects
using this character set [Yes] ?
Note:-If this is your correct character set, hit [Enter].  Otherwise, please verify your environment.
Do you want to regenerate Oracle Forms PL/SQL library files [Yes] ?
Note:-In this case, we will generate PL/SQL library files, so you might reply 'y' to the above question.
Do you want to regenerate Oracle Forms menu files [Yes] ?
Note:-Same as above; reply 'y' to this question.
Do you want to regenerate Oracle Forms executable files [Yes] ?
Note:-Here, hit [Enter] to accept the default 'Yes'
Enter list of products ('all' for all products) [all] : PO
Note:-Now, we need to specify the Application short name. (po for Purchasing Order, gl, for General Ledger, ap for Payables, and so on).  In our example we will be using module fnd (Application Object Library).
In 11i, the entry must be typed using lowercase characters.
Generate specific forms objects for each selected product [No] ? Yes
Note:-Answer 'y' to this question, since it will regenerate all forms for the module selected previously if 'No' is answered here.
The current set of installed languages is: US
Please select languages for generating Oracle Forms files.
You may select all of the above languages, or just a subset.
Enter list of languages ('all' for all of the above) [all] :
Note:-In this case, reply 'all', since this will regenerate the form for all existing languages.
You selected the following languages: US
Is this the correct set of languages [Yes] ?
Reading product form information...
Selecting Oracle Forms PL/SQL library files and menu files to generate...
Selecting library and menu files for Purchasing...
List of libraries and menus in Purchasing :
   POASTDCM.pll   POXAPAPC.pll   POXAPINT.pll   POXBWVRP.pll   POXCOMSG.pll
    POXCORE.pll   POXCOSEU.pll   POXCPDOC.pll   POXDOCEC.pll   POXDOCON.pll
   POXDOPRE.pll   POXGMLCR.pll   POXGMLCT.pll   POXGMLPO.pll   POXGMLRQ.pll
   POXOPROC.pll   POXPIPLL.pll   POXPIPOH.pll   POXPIPOL.pll   POXPIPOS.pll
   POXPIRFV.pll   POXPIRQH.pll   POXPIRQL.pll    POXPOAH.pll   POXPOCTR.pll
   POXPODIS.pll   POXPODMC.pll   POXPOEAC.pll   POXPOEGA.pll   POXPOEPO.pll
   POXPOPOL.pll   POXPOPOS.pll   POXPORCV.pll   POXPOREL.pll   POXPORMC.pll
   POXPOVCT.pll   POXPOVP1.pll   POXPOVP2.pll   POXPRCDF.pll   POXPROJA.pll
   POXPROJM.pll   POXRIHDR.pll   POXRILNS.pll   POXRQDIS.pll   POXRQHDR.pll
   POXRQLNS.pll   POXRQMOD.pll   POXRQVR1.pll   POXRQVR2.pll   POXSCAPP.pll
   POXSCRFV.pll   POXSCSAQ.pll   POXSCSI2.pll   POXSCSIL.pll   POXSTIFT.pll
   RCVCOFND.pll   RCVCOTRX.pll   RCVCOUOM.pll   RCVGMLCR.pll   RCVGMLTX.pll
   RCVMRFND.pll   RCVMRMAT.pll   RCVRCCON.pll   RCVRCCUR.pll   RCVRCERH.pll
   RCVRCERL.pll   RCVRCMUR.pll   RCVRCVRC.pll   RCVSHESH.pll   RCVSTDRO.pll
   RCVTXECO.pll   RCVTXERE.pll   RCVTXERT.pll   RCVTXVTX.pll
Enter libraries and menus to generate, or enter 'all' [all] : POXBWVRP.pll
Selecting product forms to generate...
Selecting forms for Purchasing...
List of forms in Purchasing :
   POASTDCM.fmx   POASTDSR.fmx   POXBWVRP.fmx   POXCPDOC.fmx   POXDOAPP.fmx
   POXDOCEC.fmx   POXDOCON.fmx   POXDOFDO.fmx   POXDOPRE.fmx   POXDOREP.fmx
   POXGAORG.fmx   POXOPROC.fmx   POXPCATN.fmx    POXPOAH.fmx   POXPODMC.fmx
   POXPOEAC.fmx   POXPOEPO.fmx   POXPOERL.fmx   POXPORMC.fmx   POXPOVCT.fmx
   POXPOVPO.fmx   POXPRCDF.fmx   POXQUEMQ.fmx   POXRQARQ.fmx   POXRQERQ.fmx
   POXRQMOD.fmx   POXRQTMP.fmx   POXRQVRQ.fmx   POXSCAPP.fmx   POXSCASL.fmx
   POXSCERQ.fmx   POXSCSAQ.fmx   POXSCSIC.fmx   POXSTASL.fmx   POXSTDCG.fmx
   POXSTDCS.fmx   POXSTDLC.fmx   POXSTDPC.fmx   POXSTIFT.fmx   POXSTITS.fmx
   POXSTPRS.fmx   POXSTRLM.fmx   POXSVASL.fmx   POXTAXCT.fmx   POXTAXDT.fmx
   RCVCOFND.fmx   RCVCOSTA.fmx   RCVRCERC.fmx   RCVRCMUR.fmx   RCVRCVRC.fmx
   RCVSHESH.fmx   RCVSTAND.fmx   RCVSTDRO.fmx   RCVTXECO.fmx   RCVTXERE.fmx
   RCVTXERT.fmx   RCVTXVTX.fmx
Enter forms to generate, or enter 'all' [all] : POXBWVRP.fmx
Note:-Enter ‘POXBWVRP.fmx’ at the prompt. Once you do so, adadmin will invoke the appropriate forms compiler and will regenerate the forms.
Generating Oracle Forms objects...
Note:-In case of any error, review the adadmin and the adworker log file(s).  

Invalid Object Count

spool /tmp/invalid_obj_before.txt
set pages 10000 lines 150
col owner for a30
col object_name for a50
col object_type for a30
select owner, object_name, object_type,status
from dba_objects
where status like 'INVALID'
order by 1,2,3 asc ;
exit ;

Compile_jsp

compile_jsp
rm -fr $COMMON_TOP/_pages
mkdir $COMMON_TOP/_pages
perl $FND_TOP/patch/115/bin/ojspCompile.pl --compile --flush -p 20 -log /tmp/compile.log

CM was not coming up , even while taking down was returning error :

shutdown error:
ORACLE Password:
ORACLE error 904 in FDPRRC

Cause: FDPRRC failed due to ORA-00904: "NLS_SORT": invalid identifier
.

The SQL statement being executed at the time of the error was:  and was executed from the file .
Cannot submit your concurrent request

Once killed the process, on start also only ICM was coming up and rest manager remains down.
ICM log file was having below error :
use: insert_fcp failed due to ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
ORA-06512: at "APPS.FND_CP_FNDSM", line 134
ORA-06512: at line 1.

The following actions were performed in order to solve the issue but without success:

- cleaned the fnd_nodes
- ran autoconfig in db tier and apps tier
- executed $FND_TOP/patch/115/sql/afdcm037.sql
- ran autoconfig again

TO check fnd_node details:
set lines 180
set pages 50
col NODE_NAME for a15 WORD_WRAPPED
COL SUPPORT_DB for a12
COL SUPPORT_CP for a12
COL SUPPORT_ADMIN for a15
COL SUPPORT_FORMS for a15
COL SUPPORT_WEB for a12
COL HOST for a15 WORD_WRAPPED
Col STATUS for a10
col DOMAIN for a20 WORD_WRAPPED
select NODE_NAME,SUPPORT_DB,SUPPORT_CP,SUPPORT_ADMIN,SUPPORT_WEB,SUPPORT_FORMS,STATUS,HOST,DOMAIN
from apps.fnd_nodes;

select control_code from fnd_concurrent_queues where concurrent_queue_name = 'FNDSCH'

Main cause:

Checking the AFCPFSMB.pls file version on the files system and on the DB side, there is a mismatch:
- on the file system:
adident Header $FND_TOP/patch/115/sql/AFCPFSMB.pls

AFCPFSMB.pls 120.7.12010000.6

- while on Database:
select text from dba_source where name = 'FND_CP_FNDSM' and text like '%Header%';

AFCPFSMB.pls 120.7.12010000.2

Solution:

Reloading this file without doing anything else is not recommended.

Re-apply patch 15981176 by following the read-me and make sure it updates the file correctly.

Then re-test to see if all the concurrent managers start successfully.

Reference doc id for the same:
Concurrent Managers Fail to Start: "insert_fcp failed due to ORA-00054" (Doc ID 2213565.1)

Error: ORA-06598: insufficient INHERIT PRIVILEGES privilege" during Oracle 12c running below script:

After database upgrade to 12c Database on running script:

Script:
SQL> SET SERVEROUTPUT ON
BEGIN
  xx_tools.osce_db_passwd_reset_pkg.change_db_account_passwords(p_execute_mode => TRUE);
END;SQL>   2    3
  4  /
BEGIN
*

Error:
ERROR at line 1:
ORA-06598: insufficient INHERIT PRIVILEGES privilege
ORA-06512: at "XX_TOOLS.OSCE_DB_PASSWD_RESET_PKG", line 1
ORA-06512: at line 2

Cause
This is due to a new security feature in 12c:

Before Oracle Database 12c, an Invoker Rights (IR) unit always ran with the privileges of its invoker. If its invoker had higher privileges than its owner, then the IR unit might perform operations unintended by, or forbidden to, its owner. As of Oracle Database 12c, an IR unit can run with the privileges of its invoker only if its owner has either the INHERIT PRIVILEGES privilege on the invoker or the INHERIT ANY PRIVILEGES privilege.

Solution:

SQL>

SQL> GRANT INHERIT PRIVILEGES ON USER "SYSTEM" TO PUBLIC;

Grant succeeded.

SQL> conn system/******

Connected.

SQL>

and then run the script , it went successfully.....

this is a new privilege in Oracle 12c .By default the privilege INHERIT PRIVILEGES is granted to PUBLIC, and it secures a weakness where a malicious user may accidentally get access to a privilege that user shouldn't have.