Category: SQL scripts

This script will be useful for tracking progress of GBS copied to Target system. Need to execute it on Target system.

This will provide prompt with session details.

This PL/SQL block identifies recently active SQL queries from a specific application server and provides details on their performance and execution plans.  Here is a breakdown of what the script does:

Find the SPID from Oracle’s SID using below SQL. REM **** This is used to get SPID from SID.col username format a30col machine format a20col program format a40accept _sid prompt ‘Enter Oracle Session ID ->’select a.sid, b.pid, b.spid, a.username, a.program,a.machinefrom v$session a,V$process bwhere a.paddr = b.addrand   a.sid = &_sid/ Use ORADEBUG command as below. oradebug…

Read More “Trace a SQL session from another session using ORADEBUG” »

set serveroutput onaccept v_sqlid prompt ‘SQLID:’accept v_phv prompt ‘PHV:’ var pvm numberbegin:pvm := dbms_spm.load_plans_from_cursor_cache(sql_id=>’&v_sqlid’, plan_hash_value=>&v_phv, fixed =>’NO’, enabled=>’YES’);end;/ EXEC dbms_output.put_line(‘Number of plans loaded: ‘ || :pvm);

declaremyplan pls_integer;beginmyplan:=DBMS_SPM.drop_sql_plan_baseline (sql_handle => ‘&sql_handle’);end;/

accept v_sqlid prompt ‘Enter sqlid ‘accept v_phv prompt ‘Enter PHV ‘set serveroutput on DECLAREv_first_snapid number;v_last_snapid number;v_sqlset varchar2(1000);loaded_plans number; BEGINselect max(ss.snap_id)-3, max(ss.snap_id)into v_first_snapid, v_last_snapidfrom DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SSwhere sql_id = ‘&v_sqlid’and plan_hash_value = ‘&v_phv’and ss.snap_id = S.snap_idand ss.instance_number = S.instance_numberand executions_delta > 0 ; loaded_plans := dbms_spm.load_plans_from_awr( begin_snap=>v_first_snapid,end_snap=>v_last_snapid,basic_filter=>q’# sql_id=’&v_sqlid’ and plan_hash_value=’&v_phv’ #’ ); dbms_output.put_line(‘loaded-plans= ‘ ||…

Read More “Load SPM baseline from AWR” »

set serveroutput onvar res numberexec :res :=DBMS_SPM.DROP_SQL_PLAN_BASELINE (‘&original_sql_handle’,’&original_plan_name’);exec dbms_output.put_line(‘Number of plans dropped: ‘ || :res);

set lines 120 pages 200col dd format a20col inst_id format 99col metric_name format a30col value format 99.99 select x.dd, x.inst_id, x.metric_name, x.value “%CPU”, y.sessfrom(select to_char(sysdate, ‘DD-MON-RRRR:HH24:MI’) DD, inst_id, metric_name, valuefrom gv$sysmetricwhere metric_name like ‘Host CPU Utilization%’ and group_id=2 ) x,(select inst_id, count(1) sess from gv$sessionwhere status = ‘ACTIVE’ and osuser != ‘oracle’group by inst_id) ywhere…

Read More “findinfo.sql (SQL for getting CPU and Active session info)” »

col SAMPLE_TIME format a25col program format a20col SQL_EXEC_START format a25col machine format a15set lines 140 pages 200alter session set nls_date_format=’DD-MON-RRRR:HH24:MI:SS’; select SAMPLE_TIME, SESSION_ID, SESSION_SERIAL#, USER_ID, sql_ID, Machine, program, SQL_EXEC_STARTfrom gv$active_session_historywhere session_id = &v_session_idand sample_time > sysdate – 1/24order by sample_time/