user level export and import

expdp parfile=PLCT170.par oracle@uslp123sd7dfcvxsza > more PLCT050.par userid= "/ as sysdba" dumpfile=T050.dmp logfile=expdpT050.log schemas=WWSRPLawedxCT050 compression=all directory=SCRIPTS1 reuse_dumpfiles=yes impdp parfile=IMPPLCT170.par oracle@uslp123sd7dfcvxsza >more IMPPLCT050.par userid= "/ as sysdba" dumpfile=T050.dmp logfile=imp_T050.log schemas=WWSRzaswedcvbPLCT050 directory=SCRIPTS1 table_exists_action=REPLACE

top 10 cpu sql


select * from ( select p.spid "ospid",ss.INST_ID, (se.SID),ss.serial#,ss.SQL_ID,ss.username,substr(ss.program,1,30) "program", ss.module,ss.osuser,ss.MACHINE,ss.status, se.VALUE/100 cpu_usage_sec from gv$session ss,gv$sesstat se, gv$statname sn,gv$process p where se.STATISTIC# = sn.STATISTIC# and NAME like '%CPU used by this session%' and se.SID = ss.SID and ss.username !='SYS' and ss.status='ACTIVE' and ss.username is not null and ss.paddr=p.addr and value > 0 and ss.INST_ID=3 order by se.VALUE desc);



select rownum, a.* from ( SELECT v.sid,sess.Serial# ,v.INST_ID,sess.SQL_ID,program, v.value / (100 * 60) CPUMins FROM gv$statname s , gv$sesstat v, gv$session sess WHERE s.name = 'CPU used by this session' and sess.sid = v.sid and v.statistic#=s.statistic# and v.value>0 and v.INST_ID=3 ORDER BY v.value DESC) a where rownum < 11;



Memeory Usage



SELECT a.username, a.osuser,a.INST_ID, a.program, b.spid,b.pga_used_mem/1024/1024/1024, a.sid, a.serial# ,a.module,a.logon_time,a.terminal FROM gv$session a, gv$process b WHERE a.paddr = b.addr and a.USERNAME='XXXXXXXXX' order by b.pga_used_mem desc

Tables used for sqlid and its stats

SELECT a.object_owner, a.object_name, a.object_type, b.stale_stats FROM v$sql_plan a JOIN dba_tab_statistics b ON a.object_owner = b.owner AND a.object_name = b.TABLE_NAME WHERE b.object_type = 'TABLE' AND a.sql_id = TRIM ('&1') AND (stale_stats IS NULL OR stale_stats = 'YES') UNION ALL SELECT a.object_owner, a.object_name, a.object_type, b.stale_stats FROM v$sql_plan a JOIN dba_tab_statistics b ON a.object_owner = b.owner AND a.object_name = b.PARTITION_NAME WHERE b.object_type = 'PARTITION' AND a.sql_id = TRIM ('&1') AND (stale_stats IS NULL OR stale_stats = 'YES') UNION ALL SELECT a.object_owner, a.object_name, b.object_type, b.stale_stats FROM v$sql_plan a JOIN dba_ind_statistics b ON a.object_owner = b.owner AND a.object_name = b.index_name WHERE b.object_type!='SUBPARTITION' and a.sql_id = TRIM ('&1') AND (stale_stats IS NULL OR stale_stats = 'YES');

find out a SQL how many times its running in a min and avg response time/cpu time/cluster activity/io time

SET SERVEROUTPUT ON
DECLARE a INT := 0; a1 FLOAT := 0; a2 FLOAT := 0; a3 FLOAT := 0; b INT := 0; b1 FLOAT := 0; b2 FLOAT := 0; b3 FLOAT := 0; c INT := 0; d INT := 1; BEGIN SELECT SUM (executions), SUM (elapsed_time) / 1000, SUM (CONCURRENCY_WAIT_TIME) / 1000, SUM (USER_IO_WAIT_TIME) / 1000 INTO a, a1, a2, a3 FROM gv$sql WHERE sql_id = 'gnf46h863mwh3'; DBMS_LOCK.Sleep (60); SELECT SUM (executions), SUM (elapsed_time) / 1000, SUM (CONCURRENCY_WAIT_TIME) / 1000, SUM (USER_IO_WAIT_TIME) / 1000 INTO b, b1, b2, b3 FROM gv$sql WHERE sql_id = 'gnf46h863mwh3'; DBMS_OUTPUT.put_line ( 'gnf46h863mwh3 .. .. count=' || (b - a) || '..........elasped_time_ms=' || (b1 - a1) / (b - a) || '.......conc_time_ms' || (b2 - a2) / (b - a) || '....userwait_time_ms' || (b3 - a3) / (b - a)); end;

Daily scripts

To list long-running forms user sessions
select s.sid,s.process,p.spid,s.status ,s.action,s.module, (s.last_call_et/3600) from v$session s, v$process p where round(last_call_et/3600) >4 and action like ‘%FRM%’ and p.addr=s.paddr ;

To list inactive Sessions respective username
SELECT username,count(*) num_inv_sess FROM v$session where last_call_et > 3600 and username is not null AND STATUS=’INACTIVE’ group by username order by num_inv_sess DESC;

SELECT count(*) FROM v$session where last_call_et > 43200 and username is not null AND STATUS=’INACTIVE’;

SELECT count(*) FROM v$session where last_call_et > 3600 and username is not null AND STATUS=’INACTIVE’;
--- 3600 sec
To find session-id with a set of SPIDs

select sid from v$session, v$process where addr=paddr and spid in (‘11555′,’26265′,’11533’);

To find SQL Text given SQLHASH & SQLADDR

select piece,sql_text from v$sqltext where HASH_VALUE = &hash and ADDRESS =’&addr’ order by piece; select piece,sql_text from v$sqltext where ADDRESS =’&addr’ order by piece;

Checking for Active Transactions SID
select username,t.used_ublk,t.used_urec from v$transaction t,v$session s where t.addr=s.taddr;

Session details from Session longops
select inst_id,SID,SERIAL#,OPNAME,SOFAR,TOTALWORK,START_TIME,LAST_UPDATE_TIME, username from gv$session_longops;

Session details with SPID
select sid, serial#, USERNAME, STATUS, OSUSER, PROCESS, MACHINE, MODULE, ACTION, to_char(LOGON_TIME,’yyyy-mm-dd hh24:mi:ss’) from v$session where paddr in (select addr from v$process where spid = ‘&spid’)

To find Undo Generated For a given session
select username,t.used_ublk ,t.used_urec from gv$transaction t,gv$session s where t.addr=s.taddr and s.sid=’&sid’;

To list count of connections from other machines
select count(1),machine from gv$session where inst_id=’&inst_id’ group by machine;

To get the total count of sessions and processes

select count(*) from v$session;

select count(*) from v$process;

select (select count() from v$session) sessions, (select count() from v$process) processes from dual;

To find SQL text through SQL address
select sql_address from v$session where sid=11023;

select sql_text from v$sqltext where ADDRESS=’C00000027FF00AF0′ order by PIECE;

To find SQL text for different SQL hash value
select hash_value,sql_text from v$sql where hash_value in ('XXXXXXXXXXX','XXXXXXXXX')
Session details associated with SID and Event waiting for
select a.sid, a.serial#, a.status, a.program, b.event,to_char(a.logon_time, ‘dd-mon-yy hh24:mi’) LOGON_TIME,to_char(Sysdate, ‘dd-mon-yy-hh24:mi’) CURRENT_TIME, (a.last_call_et/3600) “Hrs connected” from v$session a,v$session_wait b where a.sid in(&SIDs) and a.sid=b.sid order by 8;

Active Sessions running for more than 1 hour
SELECT USERNAME,machine,inst_id,sid,serial#,PROGRAM, to_char(logon_time,’dd-mm-yy hh:mi:ss AM’)”Logon Time”, ROUND((SYSDATE-LOGON_TIME)(2460),1) as MINUTES_LOGGED_ON, ROUND(LAST_CALL_ET/60,1) as Minutes_FOR_CURRENT_SQL From gv$session WHERE STATUS=’ACTIVE’ AND USERNAME IS NOT NULL and ROUND((SYSDATE-LOGON_TIME)(2460),1) > 60 ORDER BY MINUTES_LOGGED_ON DESC;

SQLs Running from longtime
alter session set nls_date_format = ‘dd/mm/yyyy hh24:mi’; select sid ,opname ,target ,round(sofar/totalwork*100,2) as percent_done ,start_time ,last_update_time ,time_remaining from v$session_longops;

Last/Latest Running SQL
set pages 50000 lines 32767 select inst_id,sample_time,session_id,session_serial#,sql_id from gv$active_session_history where sql_id is not null order by 1 desc;
Current Running SQLs
select b.sid,b.status,b.last_call_et,b.program,c.sql_id,c.sql_text from v$session b,v$sqlarea c where b.sql_id=c.sql_id;
Current Running SQLs
select sid,serial#,a.sql_id,a.SQL_TEXT,S.USERNAME,i.host_name,machine,S.event,S.seconds_in_wait sec_wait, to_char(logon_time,’DD-MON-RR HH24:MI’) login from gv$session S,gV$SQLAREA A,gv$instance i where S.username is not null — and S.status=’ACTIVE’ AND S.sql_address=A.address and s.inst_id=a.inst_id and i.inst_id = a.inst_id and sql_text not like ‘select S.USERNAME,S.seconds_in_wait%’;

Sessions

select count(1) FROM DBA_HIST_ACTIVE_SESS_HISTORY where to_char(sample_time,'DD-MON-YYYY HH:MI:SS') >= '30-OCT-2021 09:00:00' and to_char(sample_time,'DD-MON-YYYY HH:MI:SS') <= '30-OCT-2021 22:00:00'

EmbeddedLDAPBackup.zip grows huge under weblogic

EmbeddedLDAPBackup.zip grows huge under weblogic

path : /apps/middleware/weblogic/XXXXXXX_domain/servers/AdminServer/data/ldap/backup

file size of EmbeddedLDAPBackup.zip some it might be in GB's
solution 1.


1- Stop the Admin/weblogic serivce


2- Take the backup of the ldap files -- path /apps/middleware/weblogic/base_domain/servers/AdminServer/data/ldap/


cp -r ldapfiles ldapfiles_bkp


3- copy the ldap files form othe machine of the same domin


example : /apps/middleware/weblogic/base_domain/servers/Machin1/data/ldap


cp -r ldapfiles /apps/middleware/weblogic/base_domain/servers/AdminServer/data/ldap/


in the admin ldap remeove .lok file


start the Admin/weblogic service solutoin 2:


1.make a copy of DOMAIN/bin/startWebLogic.sh file


2. add below line in DOMAIN/bin/startWebLogic.sh file :


JAVA_OPTIONS="${JAVA_OPTIONS} -Dweblogic.security.ldap.maxSize=31457280"


export JAVA_OPTIONS


NOTE: Add above lines before the following lines


SAVE_JAVA_OPTIONS=""


CLASSPATH="${SAVE_CLASSPATH}"


SAVE_CLASSPATH=""


3. stop & start Admin server

Disk space alert creation script

#!/bin/bash

###########################################################
# This script is to check the space on the Weblogic Mount

###########################################################


CURRENT=$(df /apps | grep / | awk '{ print $5}' | sed 's/%//g')

THRESHOLD=90

if [ "$CURRENT" -gt "$THRESHOLD" ] ; then

mail -s 'Disk Space Alert' XXXXXXXXXXXXX@YYYYYYYY.com << EOF

Your Servername disk remaining free space is critically low. Used: $CURRENT%

EOF

fi

coe_xfr_sql_profile

Example: SQL Tuning Task Options (Doc ID 2461848.1)



Good Plan Hash Value Not Showing in One of the RAC Node for Sqlid Even After Forcing Plan (Doc ID 2422536.1)


Encouraging CBO to Pickup a Better Execution Plan Using the COE XFR SQL Profile Script (Doc ID 1955195.1)

Is the Script CMCLEAN.SQL Supported in EBS R12?

(Doc ID 2183718.1)


The script CMCLEAN.SQL is NOT recommended or supported with E-Business Suite (EBS) Release 12.

The recommended and supported tools for EBS R12 and Oracle Applications Release 11i is the Concurrent Manager Recovery Wizard and/or the newly released CPADMIN utility.

Details of the "Concurrent Manager Recovery Wizard" is documented within the manual "Oracle E-Business Suite System Administrator's Guide - Maintenance Release 12.1" (Part No. E12894-04), Chapter 7, Diagnostic and Repair in Oracle Applications Manager: Concurrent Manager Recovery', page 7-4


Details of the CPADMIN utility is documented within the following notes:


- 'Note 2319585.1 R12 E-Business Suite Concurrent Processing Script Cpadmin.sh Command Line Utility Which Consolidates Existing CP Tools Into A Single Menu-Based Shell Script'


- 'Note 2084405.1 Oracle Application Object Library Release Notes For Release 12.1 Concurrent Processing RUP2 Patchset:


"What's New...Concurrent Processing Command-Line Utility - This command-line utility consolidates various existing utilities into a single menu-based utility called "cpadmin"...This adadmin-style utility can be used for multiple tasks, including:


* Manager Status: Use this option to display the statuses of all managers. * Clean CP Tables: Use this option to clean up the concurrent processing tables. This utility replaces cmclean.sql.


* Set Manager Diagnostics: Turn diagnostics on or off for individual managers with this option.
* Manager Control: Use this option to send a request such as start, stop, or verify to an individual manager.


* Rebuild Manager Views: Use this option to rebuild the FND_CONCURRENT_WORKER_REQUEST and FND_CONCURRENT_CRM_REQUESTS views.


* Move Request Files: Change request log and output file locations with this option.


* Analyze Request: Use this option to analyze a concurrent request."


As per Bug 17198256 CMCLEAN.SQL SHOULD TAKE CARE OF RESCHEDULING TERMINATED CONCURRENT REQUESTS, the script cmclean.sql was designed by Concurrent Processing (CP) Development for Oracle Applications Release 10.7 for internal use; however, it was unofficially distributed by Support--an effective script with Apps Release 10.7 and 11.0.


Oracle Applications 11i and E-Business Suite (EBS) R12 has evolved substantially and the script "cmclean.sql" is not any longer available, recommended, or supported.

R12.2 How to Enable Form FRD Trace Step by Step?

REF : (Doc ID 2567155.1)



R12.2 How to Enable Form FRD Trace Step by Step?

SOLUTION Steps is only for EBS R12.2 instance :

1.) Go to the specific concerned form window, click (menu) Help > About Oracle Applications, find out value to FORMS_TRACE_DIR and Forms Process ID:

FORMS_TRACE_DIR: scroll to section of ‘Forms Server Environment Variables’

Forms Process ID: scroll to section of ‘Forms Server’

NOTE: If the Forms Process ID is not visible, then make sure that the system profile 'FND: Diagnostics' is set to YES as documented in Note 438274.1

2.) Source the instance app tier environment file /…/EBSapps.env, and go to directory of FORMS_TRACE_DIR



3.) Run command : kill –USR1

--this will generate a file with name like forms_.trc

4.) Go back to the Oracle Applications and complete the steps to reproduce the issue.

5.) Once the issue is reproduced, please download $FORMS_TRACE_DIR / forms_.trc for R12 to your client computer, and upload it to SR.

6.) To Disable FRD trace with command: kill –HUP

Notes: Attached please find the doc with screen shot of each step.

PT

 --- how to find SQL plan changes 

select to_char(min(s.end_interval_time),'YYYY-MM-DD HH24:MI') sample_end

--, s.snap_id

     , q.sql_id

     , q.plan_hash_value

     , q.instance_number inst_id

     , sum(q.EXECUTIONS_DELTA) execs9

     , round((sum(ELAPSED_TIME_delta)/greatest(sum(executions_delta),1)/1000/1000),3) ela_sec

     , round((sum(CPU_TIME_delta)/greatest(sum(executions_delta),1)/1000/1000),3) cpu_sec

     , round((sum(rows_processed_delta)/greatest(sum(executions_delta),1)),1) avg_rows

     , round((sum(fetches_delta)/greatest(sum(executions_delta),1)),1) avg_fetches

     , round(sum(BUFFER_GETS_delta)/greatest(sum(executions_delta),1)) lio_per_exec

     , round(sum(DISK_READS_delta)/greatest(sum(executions_delta),1)) pio_per_exec

     , round((sum(iowait_delta)/greatest(sum(executions_delta),1)/1000/1000),1) iowait_sec

--     , round((sum(clwait_delta)/greatest(sum(executions_delta),1)/1000),1) clwait_sec

--     , round((sum(apwait_delta)/greatest(sum(executions_delta),1)/1000),1) apwait_sec

--     , round((sum(ccwait_delta)/greatest(sum(executions_delta),1)/1000),1) ccnwait_sec

 from dba_hist_sqlstat q, dba_hist_snapshot s

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

  and s.snap_id = q.snap_id

  and s.dbid = q.dbid

  and s.instance_number = q.instance_number

  and s.end_interval_time between ( sysdate - &2 ) and ( sysdate - &2 + &3 )

group by s.snap_id, q.sql_id, q.plan_hash_value, q.instance_number

order by s.snap_id, q.sql_id, q.plan_hash_value, q.instance_number







-- if you find a bad plan then try purging it 




select inst_id,plan_hash_value,'exec sys.DBMS_SHARED_POOL.PURGE ('''||ADDRESS||', '||HASH_VALUE||''', ''C'');' from gV$SQL where sql_id in ('1pfb281d5f37c') order by plan_hash_value;







-- To check stats and collect stats if stale

SELECT a.object_owner,

       a.object_name,

       a.object_type,

       b.stale_stats

  FROM v$sql_plan  a

       JOIN dba_tab_statistics b

           ON a.object_owner = b.owner AND a.object_name = b.TABLE_NAME

 WHERE     b.object_type = 'TABLE'

       AND a.sql_id = TRIM ('&1')

       AND (stale_stats IS NULL OR stale_stats = 'YES')

UNION ALL

SELECT a.object_owner,

       a.object_name,

       a.object_type,

       b.stale_stats

  FROM v$sql_plan  a

       JOIN dba_tab_statistics b

           ON a.object_owner = b.owner AND a.object_name = b.PARTITION_NAME

 WHERE     b.object_type = 'PARTITION'

       AND a.sql_id = TRIM ('&1')

       AND (stale_stats IS NULL OR stale_stats = 'YES')

UNION ALL

SELECT a.object_owner,

       a.object_name,

       b.object_type,

       b.stale_stats

  FROM v$sql_plan  a

       JOIN dba_ind_statistics b

           ON a.object_owner = b.owner AND a.object_name = b.index_name

 WHERE     b.object_type!='SUBPARTITION' and a.sql_id = TRIM ('&1')

       AND (stale_stats IS NULL OR stale_stats = 'YES');







-- if you decide to create a SQL baseline after all analysis 




SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR('17n0jry7qhtdx'));

 

 

-- look up AWR history to find SQL detailed statistics. Pick up the snapshots in which the SQL has had a good or better plan.

 

/* Formatted on 1/23/2018 10:06:30 AM (QP5 v5.300) */

  SELECT ss.snap_id,

         ss.instance_number,

         sn.end_interval_time,

         ss.plan_hash_value,

         ss.elapsed_time_delta / 1000 / 1000 / ss.executions_delta

             avg_time_per_run,

         elapsed_time_total / 1000 / 1000      total_elapsed_time,

         ss.executions_delta                   exe_times,

         ss.fetches_delta,

         ss.rows_processed_delta               processed_rows,

         iowait_delta / ss.executions_delta    per_IO_wait,

         ss.cpu_time_delta / ss.executions_delta Per_CPU_time,

         disk_reads_delta / ss.executions_delta Per_disk_R,

         buffer_gets_delta / ss.executions_delta Per_buffer_R

    FROM dba_hist_sqlstat ss, dba_hist_snapshot sn

   WHERE     ss.snap_id = sn.snap_id

         AND ss.instance_number = sn.instance_number

         AND ss.sql_id = '&sql_id'

         AND ss.executions_delta > 0

ORDER BY ss.snap_id DESC, 1;

 

-- snap_id= 121097

 

-- create a sql tuning set. You can name it to whatever that is easy to find its purpose.

 

set serveroutput on

exec dbms_sqltune.create_sqlset(sqlset_name => '&SQL_set_name',description => '&SQL_set_name');

 

-- load SQLs that you want to tune in the above created SQL tuning set. Enter the snapshot ID in which the target sql had a good plan. Enter the ID of the SQL.

 

set serveroutput on

declare

baseline_ref_cur DBMS_SQLTUNE.SQLSET_CURSOR;

begin

open baseline_ref_cur for

select VALUE(p) from table(

DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(&snapshot_start, &snapshot_end,'sql_id='||CHR(39)||'&sql_id'||CHR(39)||'',NULL,NULL,NULL,NULL,NULL,NULL,'ALL')) p;

DBMS_SQLTUNE.LOAD_SQLSET('&SQL_set_name', baseline_ref_cur);

end;

/

 

 

 

declare

my_int pls_integer;

begin

my_int := dbms_spm.load_plans_from_sqlset (

sqlset_name => '&SQL_set_name',

basic_filter => 'sql_id=''&sql_id''',

sqlset_owner => 'AIUDKC3',

fixed => 'NO',

enabled => 'YES');

DBMS_OUTPUT.PUT_line(my_int);

end;

/

 

 

select * from dba_sql_plan_baselines order by created desc;










-- Below is mostly not needed but if you came to a conclusion and looking to copy SQL profile from one env. to other env.




-- Run it in theta from where you want to export sql profile

EXEC DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF (table_name => 'PROFILE_STGTAB_86dxp1c32fd42');

 

EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (profile_name => 'SYS_SQLPROF_036f7c7ee4560000', staging_table_name => 'PROFILE_STGTAB_86dxp1c32fd42');

 

select * from PROFILE_STGTAB;

 

-- export and improt above table

expdp tables=PROFILE_STGTAB_86dxp1c32fd42 dumpfile=PROFILE_STGTAB_86dxp1c32fd42.dmp directory=scripts;

 

impdp tables=PROFILE_STGTAB_86dxp1c32fd42 dumpfile=PROFILE_STGTAB_86dxp1c32fd42.dmp directory=conv_dir;

 

 

-- ON target database

 

 

 EXEC DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF (table_name => 'PROFILE_STGTAB');

  

 select * from PROFILE_STGTAB; -- PROFILE_6zhhcaxya0z18

  

 EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(replace=>true,staging_table_name => 'PROFILE_STGTAB');







--- if there is no good plan in AWR snaps and you found a good baseline in some other env. and decide to copy here 




select * from dba_sql_plan_baselines where plan_name='SQL_PLAN_7mduu8xwt7wzx8c7c867d';

 

 

-- create staging table on source

 

BEGIN

    DBMS_SPM.CREATE_STGTAB_BASELINE (table_name        => 'SPB_86dxp1c32fd42',

                                     table_owner       => 'AIUDKC3',

                                     tablespace_name   => 'USERS');

END;

 

-- packing SQL baseline to staging table

DECLARE

    my_plans   NUMBER;

BEGIN

    my_plans :=

        DBMS_SPM.PACK_STGTAB_BASELINE (

            table_name    => 'SPB_86dxp1c32fd42',

            enabled       => 'yes',

            table_owner   => 'AIUDKC3',

            plan_name     => 'SQL_PLAN_7mduu8xwt7wzx8c7c867d',

            sql_handle    => 'SQL_79b75a477993f3fd');

END;

/

 

select * from SPB_86dxp1c32fd42;

 

 -- export and improt above table

  

 select * from dba_directories where directory_name='EXPDP_DUMP';

  

 expdp tables=SPB_86dxp1c32fd42 dumpfile=SPB_86dxp1c32fd42.dmp directory=EXPDP_DUMP;

 

  impdp tables=SPB_86dxp1c32fd42 dumpfile=SPB_86dxp1c32fd42.dmp directory=EXPDP_DUMP;

   

-- unpack sql baseline

/* Formatted on 4/2/2020 10:24:44 AM (QP5 v5.326) */

SET SERVEROUTPUT ON

 

DECLARE

    l_plans_unpacked   PLS_INTEGER;

BEGIN

    l_plans_unpacked :=

        DBMS_SPM.unpack_stgtab_baseline (table_name    => 'SPB_86dxp1c32fd42',

                                         table_owner   => 'AIUDKC3');

 

    DBMS_OUTPUT.put_line ('Plans Unpacked: ' || l_plans_unpacked);

END;

/

   

 

select inst_id,plan_hash_value,'exec sys.DBMS_SHARED_POOL.PURGE ('''||ADDRESS||', '||HASH_VALUE||''', ''C'');' from gV$SQL where sql_id in ('86dxp1c32fd42') order by plan_hash_value;