Automatic SQL Tuning Adviser

 Automatic SQL Tuning Adviser:

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

Optimizer --- Will generate and pick execution plan

Suppose Stale Table or Wrong statistics of a table 

1. Statistical Analysis 
2. Accessing Path (Using Index or not)

 

High Level steps for SQL Tuning Adviser:

How to find the SQL ID:

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

select sql_id from v$sql where sql_text like 'select * from Ranesh';

 

Create Tuning Task:

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

DECLARE

l_sql_tune_task_id VARCHAR2(100);

BEGIN

l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (

sql_id => 'abc52qbabz2ac',

scope => DBMS_SQLTUNE.scope_comprehensive,

time_limit => 500,

task_name => 'my_tuning_task_3',

description => 'Tuning task1 for statement abc52qbabz2ac');

DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);

END;

/

 

Execute Tuning Task:

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

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'my_tuning_task_3');

 

Status of Tuning Task:

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

SELECT TASK_NAME, STATUS FROM DBA_ADVISOR_LOG WHERE TASK_NAME='my_tuning_task_3';

 

Display the Recommendation:

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

set long 65536

set longchunksize 65536

set linesize 100

select dbms_sqltune.report_tuning_task('my_tuning_task_3') from dual;

 

Drop the Tuning Task:

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

execute dbms_sqltune.drop_tuning_task('my_tuning_task_1');

 

Find out state Tables:

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

set lines 160 pages 2000

col owner format a15

col table_name format a35

col last_analyzed format a35

col num_rows for 999999999999

SELECT RPAD(owner,15,' ') Owner, RPAD(table_name,35,' ') Table_Name, num_rows, RPAD(TO_CHAR(last_analyzed,'DD-MON-YYYY HH24:MI:SS'),35,' ') last_analyzed

FROM dba_tab_statistics

WHERE owner IN ('RANESH')

AND stale_stats='YES'

ORDER BY owner;

 

Gather table state:

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

execute dbms_stats.gather_table_stats(ownname =>'RANESH',tabname =>'RANESH',estimate_percent =>100);

 

 

Execution logs from SQL Tuning Adviser:

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

1. Run SQL statement and capture the SQL ID:

 

[oracle@oraclelab3 ~]$ ps -ef|grep smon

oracle    4941     1  0 Nov12 ?        00:00:16 ora_smon_TESTDB

oracle    8631  5466  0 00:55 pts/1    00:00:00 grep --color=auto smon

[oracle@oraclelab3 ~]$ sqlplus Ranesh/Ranesh

 

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Nov 24 00:55:21 2021

Version 19.3.0.0.0

 

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

 

Last Successful login time: Wed Nov 24 2021 00:16:14 +05:30

 

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

 

SQL> select * from Ranesh;

 

      STNO STNAME

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

         1 Ranesh

         2 John

        10 AAA

        10 AAA

        10 AAA

        10 AAA

        10 AAA

        10 AAA

        10 AAA

 

9 rows selected.

 

2. Validate the table and make sure there table is not in stale state:

 

SQL> set lines 160 pages 2000

col owner format a15

col table_name format a35

col last_analyzed format a35

col num_rows for 999999999999

SELECT RPAD(owner,15,' ') Owner, RPAD(table_name,35,' ') Table_Name, num_rows, RPAD(TO_CHAR(last_analyzed,'DD-MON-YYYY HH24:MI:SS'),35,' ') last_analyzed

FROM dba_tab_statistics

WHERE owner IN ('RANESH')

AND stale_stats='YES'

ORDER BY owner;SQL> SQL> SQL> SQL> SQL>   2    3    4    5

 

no rows selected

 

SQL> select sql_id from v$sql where sql_text like 'select * from Ranesh';

 

SQL_ID

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

abc52qbabz2ac

 

SQL>

 

3. Create Tuning Task for the SQL ID:

 

[oracle@oraclelab3 ~]$ ps -ef|grep smon

oracle    4941     1  0 Nov12 ?        00:00:16 ora_smon_TESTDB

oracle    8049  5048  0 00:48 pts/0    00:00:00 grep --color=auto smon

[oracle@oraclelab3 ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Nov 24 00:58:04 2021

Version 19.3.0.0.0

 

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

 

SQL> DECLARE

  2  l_sql_tune_task_id VARCHAR2(100);

BEGIN

l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (

sql_id => 'abc52qbabz2ac',

scope => DBMS_SQLTUNE.scope_comprehensive,

time_limit => 500,

task_name => 'my_tuning_task_1',

  3    4    5    6    7    8    9  description => 'Tuning task1 for statement abc52qbabz2ac');

DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);

END;

/ 10   11   12

 

PL/SQL procedure successfully completed.

 

SQL> 

 

4. Run the Tuning Task and check the status:

 

SQL> EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'my_tuning_task_1');

 

PL/SQL procedure successfully completed.

 

SQL>

SQL> SELECT TASK_NAME, STATUS FROM DBA_ADVISOR_LOG WHERE TASK_NAME='my_tuning_task_1';

 

TASK_NAME

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

STATUS

-----------

my_tuning_task_1

COMPLETED

 

SQL> 

 

5. Review the recommendation provided by Tuning Task:

 

SQL> set long 65536

set longchunksize 65536

set linesize 100

select dbms_sqltune.report_tuning_task('my_tuning_task_1') from dual;

SQL> SQL> SQL>

DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_TUNING_TASK_1')

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

GENERAL INFORMATION SECTION

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

Tuning Task Name   : my_tuning_task_1

Tuning Task Owner  : SYS

Workload Type      : Single SQL Statement

Scope              : COMPREHENSIVE

Time Limit(seconds): 500

Completion Status  : COMPLETED

Started at         : 11/24/2021 00:58:28

Completed at       : 11/24/2021 00:58:28

 

 

DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_TUNING_TASK_1')

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

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

Schema Name: RANESH

SQL ID     : abc52qbabz2ac

SQL Text   : select * from Ranesh

 

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

There are no recommendations to improve the statement.

 

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

 

SQL>

 

6. Delete Some rows from table and table will become in stale state:

 

SQL> select * from Ranesh;

 

      STNO STNAME

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

         1 Ranesh

         2 John

        10 AAA

        10 AAA

        10 AAA

        10 AAA

        10 AAA

        10 AAA

        10 AAA

 

9 rows selected.

 

SQL>

 

SQL> delete from Ranesh where STNO=10;

 

7 rows deleted.

 

SQL> commit;

 

Commit complete.

 

SQL> select * from Ranesh;

 

      STNO STNAME

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

         1 Ranesh

         2 John

 

SQL> set lines 160 pages 2000

col owner format a15

SQL> SQL> col table_name format a35

col last_analyzed format a35

col num_rows for 999999999999

SELECT RPAD(owner,15,' ') Owner, RPAD(table_name,35,' ') Table_Name, num_rows, RPAD(TO_CHAR(last_analyzed,'DD-MON-YYYY HH24:MI:SS'),35,' ') last_analyzed

FROM dba_tab_statistics

WHERE owner IN ('RANESH')

AND stale_stats='YES'

ORDER BY owner;SQL> SQL> SQL>   2    3    4    5

 

OWNER           TABLE_NAME                               NUM_ROWS LAST_ANALYZED

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

RANESH          RANESH                                         9 24-NOV-2021 00:28:55

 

SQL>

 

7. Now you can generate the new Tuning Task and see the recommendation from it:

 

SQL> DECLARE

l_sql_tune_task_id VARCHAR2(100);

BEGIN

l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (

sql_id => 'abc52qbabz2ac',

scope => DBMS_SQLTUNE.scope_comprehensive,

time_limit => 500,

task_name => 'my_tuning_task_2',

description => 'Tuning task1 for statement abc52qbabz2ac');

DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);

END;

/  2    3    4    5    6    7    8    9   10   11   12

 

PL/SQL procedure successfully completed.

 

SQL> EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'my_tuning_task_2');

 

PL/SQL procedure successfully completed.

 

SQL> SELECT TASK_NAME, STATUS FROM DBA_ADVISOR_LOG WHERE TASK_NAME='my_tuning_task_2';

 

TASK_NAME

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

STATUS

-----------

my_tuning_task_2

COMPLETED

 

 

SQL> set long 65536

set longchunksize 65536

set linesize 100

select dbms_sqltune.report_tuning_task('my_tuning_task_2') from dual;SQL> SQL> SQL>

 

DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_TUNING_TASK_2')

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

GENERAL INFORMATION SECTION

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

Tuning Task Name   : my_tuning_task_2

Tuning Task Owner  : SYS

Workload Type      : Single SQL Statement

Scope              : COMPREHENSIVE

Time Limit(seconds): 500

Completion Status  : COMPLETED

Started at         : 11/24/2021 01:02:18

Completed at       : 11/24/2021 01:02:18

 

 

DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_TUNING_TASK_2')

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

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

Schema Name: RANESH

SQL ID     : abc52qbabz2ac

SQL Text   : select * from Ranesh

 

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

FINDINGS SECTION (1 finding)

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

 

1- Statistics Finding

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

 

DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_TUNING_TASK_2')

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

  Optimizer statistics for table "RANESH"."RANESH" are stale.

 

  Recommendation

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

  - Consider collecting optimizer statistics for this table.

    execute dbms_stats.gather_table_stats(ownname => 'RANESH', tabname =>

            'RANESH', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,

            method_opt => 'FOR ALL COLUMNS SIZE AUTO');

 

  Rationale

  ---------

 

DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_TUNING_TASK_2')

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

    The optimizer requires up-to-date statistics for the table in order to

    select a good execution plan.

 

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

EXPLAIN PLANS SECTION

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

 

1- Original

-----------

Plan hash value: 2356778634

 

 

DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_TUNING_TASK_2')

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

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

| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT  |         |     9 |    63 |     3   (0)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| RANESH |     9 |    63 |     3   (0)| 00:00:01 |

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

 

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

 

SQL>

 

8. In this above Tuning task has given recommendation that table is in state stat and gather stats on the table

 

SQL> execute dbms_stats.gather_table_stats(ownname => 'RANESH', tabname =>'RANESH', estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt => 'FOR ALL COLUMNS SIZE AUTO');

 

PL/SQL procedure successfully completed.

 

SQL>

 

9. Now the table is not in stale state then you can generate the new tuning task which will not give any recommendation since table is upto date.

 

SQL> DECLARE

  2  l_sql_tune_task_id VARCHAR2(100);

BEGIN

l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (

  3    4    5  sql_id => 'abc52qbabz2ac',

scope => DBMS_SQLTUNE.scope_comprehensive,

time_limit => 500,

task_name => 'my_tuning_task_3',

description => 'Tuning task1 for statement abc52qbabz2ac');

DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);

  6  END;

/  7    8    9   10   11   12

 

PL/SQL procedure successfully completed.

 

SQL> EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'my_tuning_task_3');

 

PL/SQL procedure successfully completed.

 

SQL> SELECT TASK_NAME, STATUS FROM DBA_ADVISOR_LOG WHERE TASK_NAME='my_tuning_task_3';

 

TASK_NAME

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

STATUS

-----------

my_tuning_task_3

COMPLETED

 

 

SQL> set long 65536

set longchunksize 65536

set linesize 100

select dbms_sqltune.report_tuning_task('my_tuning_task_3') from dual;

SQL> SQL> SQL>

DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_TUNING_TASK_3')

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

GENERAL INFORMATION SECTION

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

Tuning Task Name   : my_tuning_task_3

Tuning Task Owner  : SYS

Workload Type      : Single SQL Statement

Scope              : COMPREHENSIVE

Time Limit(seconds): 500

Completion Status  : COMPLETED

Started at         : 11/24/2021 01:06:05

Completed at       : 11/24/2021 01:06:05

 

 

DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_TUNING_TASK_3')

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

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

Schema Name: RANESH

SQL ID     : abc52qbabz2ac

SQL Text   : select * from Ranesh

 

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

There are no recommendations to improve the statement.

 

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

 

SQL>

 

10. Drop those tuning tasks:

 

SQL> execute dbms_sqltune.drop_tuning_task('my_tuning_task_1');

 

PL/SQL procedure successfully completed.

 

SQL> execute dbms_sqltune.drop_tuning_task('my_tuning_task_2');

 

PL/SQL procedure successfully completed.

 

SQL> execute dbms_sqltune.drop_tuning_task('my_tuning_task_3');

 

PL/SQL procedure successfully completed.

 

SQL>

 

Query to check patches applied correctly with the expected sequence.

1.1.Run this sql statement: select * from ad_adop_session_patches order by end_date desc; 1.2. Run this piece of sql code: set pagesize 200; set linesize 160; column adop_session_id format 999999999999; column bug_number format a15; column status format a15; column applied_file_system_base format a23; column patch_file_system_base format a23; column adpatch_options format a15; column node_name format a15; column end_date format a15; column clone_status format a15; select ADOP_SESSION_ID, BUG_NUMBER, STATUS, APPLIED_FILE_SYSTEM_BASE, PATCH_FILE_SYS TEM_BASE, ADPATCH_OPTIONS, NODE_NAME, END_DATE, CLONE_STATUS from ad_adop_session_patches order by end_date desc; Below are possible values of STATUS column: N - Not Applied In the current node but applied in other nodes R - Patch Application is going on. H - Patch failed in the middle. (Hard Failure) F - Patch failed in the middle but user tried to skip some failures. S - Patch Application succeeded after skipping the failed jobs. Y - Patch Application succeeded. C - Reserved for clone and config_clone. Indicates clone completed

SQL query to get the version of a file

select sub.filename, sub.version , sub.last_update_date, sub.app_short_name, sub.subdir from ( select adf.filename filename, afv.version version,afv.LAST_UPDATE_DATE,adf.app_short_name, adf.subdir, rank()over(partition by adf.filename order by afv.version_segment1 desc, afv.version_segment2 desc,afv.version_segment3 desc, afv.version_segment4 desc,afv.version_segment5 desc, afv.version_segment6 desc,afv.version_segment7 desc, afv.version_segment8 desc,afv.version_segment9 desc, afv.version_segment10 desc, afv.translation_level desc) as rank1 from ad_file_versions afv, ( select filename, app_short_name, subdir, file_id from ad_files where upper(filename) like upper('%&filename%') ) adf where adf.file_id = afv.file_id ) sub where rank1 = 1 order by 1;

Query to find Plan Hash Values for a SQLID in Oracle

Query to find Plan Hash Values for a SQLID in Oracle SELECT DISTINCT sql_id, plan_hash_value FROM dba_hist_sqlstat dhs, ( SELECT /*+ NO_MERGE */ MIN(snap_id) min_snap, MAX(snap_id) max_snap FROM dba_hist_snapshot ss WHERE ss.begin_interval_time BETWEEN (SYSDATE - &No_Days) AND SYSDATE ) s WHERE dhs.snap_id BETWEEN s.min_snap AND s.max_snap AND dhs.sql_id IN ( '&SQLID')

Long-running sessions in Oracle

 Long-running sessions in Oracle using below queries:

You can check long running sessions using v$session_longops script which will show you, % completed, remaining time, sofar completed and much more detailed information. You can also use below queries to kill long running sessions in Oracle.

 

Query1:

SELECT SID, SERIAL#,OPNAME, CONTEXT, SOFAR, TOTALWORK,ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE" FROM V$SESSION_LONGOPS WHERE OPNAME NOT LIKE '%aggregate%' AND TOTALWORK != 0 AND SOFAR <> TOTALWORK;

Query2:

set lines 300

col TARGET for a40

col SQL_ID for a20

select SID,TARGET||OPNAME TARGET, TOTALWORK, SOFAR,TIME_REMAINING/60 Mins_Remaining,ELAPSED_SECONDS,SQL_ID from v$session_longops where TIME_REMAINING>0 order by TIME_REMAINING;

 

 

TO find out sql_id for the above sid:

SQL> select sql_id from v$session where sid='&SID';



To find sql text for the above sql_id:

SQL> select sql_fulltext from V$sql where sql_id='bgf07y9xn8grx';



To find wait event of the query for which it is waiting for:

SQL>select sql_id, state, last_call_et, event, program, osuser from v$session where sql_id='&sql_id';

 

 

How to Kill Long Running Concurrent Job in R12

 

How to Kill Long Running Concurrent Job in R12

Step 1) Find SID and Serial Number for Concurrent Job

Step 2) Kill the long running session

Step 3) Update fnd_concurrent_requests table to mark the request in error and completed

STEP1:

SELECT a.request_id, d.sid, d.serial# ,d.osuser,

d.process , c.SPID ,d.inst_id

FROM apps.fnd_concurrent_requests a,

apps.fnd_concurrent_processes b,

gv$process c,

gv$session d

WHERE a.controlling_manager = b.concurrent_process_id

AND c.pid = b.oracle_process_id

AND b.session_id=d.audsid

AND a.request_id = 344625347--------------req id

AND a.phase_code = 'R';

STEP2:

select ORACLE_PROCESS_ID from apps.fnd_concurrent_requests where REQUEST_ID=&REQUEST_ID;

select ORACLE_PROCESS_ID from apps.fnd_concurrent_requests where REQUEST_ID=&REQUEST_ID;

alter system kill session ‘sid,serial#’ immediate:

STEP3:

 UPDATE fnd_concurrent_requests

SET phase_code = 'C', status_code = 'D'

WHERE Request_id=&request_id;

If this does not solve your problem then try restarting Standard Concurrent Manager and Output Post Processor Concurrent Manager.