How to put concurrent requests are on Hold and to Release.

 

During some scheduled maintenance activities, oracle apps database administrator would require to keep the Pending Jobs on Hold before bounce and release them after the scheduled activity.

This process help to bring down Concurrent Manager quickly and the pending jobs are preserved for running after the maintenance is complete.


1) Create table apps.conc_req_on_hold as select * from fnd_Concurrent_requests where PHASE_CODE='P' and hold_flag='N';

2) select count(*) from apps.conc_req_on_hold

3) update fnd_Concurrent_requests set hold_flag='Y' where PHASE_CODE='P' and hold_flag='N' and request_id in (select request_id from apps.conc_req_on_hold);

Or

3)update apps.fnd_concurrent_requests set hold_flag = 'Y', last_update_date = sysdate, last_updated_by = -1 where phase_code = 'P';

NOTE: You have to commit if select & update are same number of records. Otherwise rollback and try gain till the numbers are same

4) Commit;

To Release hold on Concurrent Requests patching, run the below sql :

5) update fnd_Concurrent_requests set hold_flag='N' where request_id in (select request_id from apps.conc_req_on_hold);

Or

5)update apps.fnd_concurrent_requests a set a.hold_flag = 'N' , last_update_date = sysdate, last_updated_by = -1 where phase_code = 'P' and a.hold_flag = 'Y' and last_updated_by = -1and last_update_date > sysdate-1;

6)Commit the changes

 commit;

 

Enable and Disable the SQL Profiles in Oracle

Check the present SQL Profiles present in Oracle Database.

SELECT NAME,STATUS,SQL_TEXT FROM DBA_SQL_PROFILES;

Name: Got from the first query which profile you want to enable or disable

Enable the SQL Profile in Oracle Database:

EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE(name =>'name_value', attribute_name=>'STATUS', value=>'ENABLED');

Disable the SQL Profile in Oracle Database:

EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE(name =>'name_value', attribute_name=>'STATUS', value=>'DISABLED');

Drop the SQL Profile if not needed:

Exec dbms_sqltune.drop_sql_profile(name=>'profile_name_value');

DATAPUMP Schema Level EXPORT & IMPORT.--- for remapping the schema with another.

DATAPUMP Schema Level EXPORT & IMPORT.--- for remapping the schema with another.

 

 create an directory at OS level dp_17_10_2023; automatically log file and dmp files will be created in this directory

 SQL> create directory datapump_schema as 'D:\dp_17_10_2023';

 Directory created.

 Grant permission to schema:

 SQL> grant read,write on directory datapump_schema to varshini;

Grant succeeded.

 SQL> grant read,write on directory datapump_schema to Ranesh;

Grant succeeded.

 

EXPORT and SCHEMA.

expdp directory=datapump_schema dumpfile=varshini.dmp logfile=varshini_bkp.log schemas=varshini

 Export: Release 19.0.0.0.0 - Production on Tue Oct 17 09:14:05 2023 Version 19.19.0.0.0

 Copyright (c) 1982, 2023, Oracle and/or its affiliates. All rights reserved.

 Username: / as sysdba

 

Export another schema  

expdp directory=datapump_schema dumpfile=Ranesh.dmp logfile=Ranesh_bkp.log schemas=Ranesh

 

 IMPORT and SCHEMA for remapping. before import need to remove that Schema - Ranesh

 

impdp directory=datapump_schema dumpfile=varshini.dmp logfile=IMP_varshini.log remap_schema=varshini:Ranesh

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>