Create an Oracle SQL TUNING TASK manually

 Create an Oracle SQL TUNING TASK manually with the sql_id 

You can create an SQL TUNING TASK manually ad hoc with the following simple steps. ALTER SESSION SET NLS_LANGUAGE='AMERICAN'; 0. Find the sql_id of the oracle session you would like to analyze. Usually the AWR has the top sql_ids.

 In case this is a current sql running use the v$session.

 select sql_id from v$session where sid = :x

 1. Login as SYSTEM (or any other user) at sqlplus and create the tuning task: ===========================================================================

SET SERVEROUTPUT ON
declare 
stmt_task VARCHAR2(40); 
begin stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => '5tru8vxmktswq'); DBMS_OUTPUT.put_line('task_id: ' || stmt_task ); 
end; / =========================================================================== task_id: TASK_69287 

2. Run the SQL TUNING TASK =========================================================================== begin 
DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'TASK_69287'); 
end; 
/ =========================================================================== == 3. You can monitor the processing of the tuning task with the statement

 SELECT TASK_NAME, STATUS FROM DBA_ADVISOR_LOG WHERE TASK_NAME = 'TASK_ 69287';

 4. When the task has a status=COMPLETED, then run: SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_69287') AS recommendations FROM dual; 

5. Examine the recommendations from Oracle, in case you agree, then accept the best SQL profile. begin DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(task_name => 'TASK_69287', task_owner => 'SYSTEM', replace => TRUE); end; / 

6. You can check the database sql profiles with the statement:

 select * from dba_sql_profiles; 

In case you want to disable an sql profile use the statement:

 begin
 DBMS_SQLTUNE.ALTER_SQL_PROFILE('SQL_PROFILE','STATUS','DISABLED'); 
end; 
/

Query is running slow for long time for SQL_ID 
Query is running slow for long time for SQL_ID captured 

Run SQL Tuning Adviosor for the sql_id
------------------------------------- 

Statement with SQL_ID captured is taking long time, we need to set best Execution Plan for the SQL_ID

 So, we need to submit to Oracle Tuning Advisor(sqltrpt.sql) and then check the FINDINGS SECTION for Findings and Recommendations for the SQL_ID. 

Recommendations from sqltrpt.sql will be providing the best Explain Plan. 

We can implement these profiles/index rebuild/... suggested after checking with the SME of the database. 

Location: $ORACLE_HOME/rdbms/admin/sqltrpt.sql 

$sqlplus "/as sysdba" 

Query to see current running sqls 

set pages 50000 lines 32767
col program format a40
col sql_text format a130
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 
/ 

Run SQL Tuning Advisor for the SQL_ID

SQL> @?/rdbms/admin/sqltrpt.sql

In case the recommendation is for creation of SQL PROFILE, sqltrpt.sql will provide the command too as below.

Command to Create and Implement SQL Profile in Oracle for the SQL_ID:

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

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

If successful, you should see the following:

PL/SQL procedure successfully completed.

SQL> SELECT name, created, LAST_MODIFIED FROM dba_sql_profiles ORDER BY created DESC;

Command to Drop SQL Profile in Oracle for the SQL_ID:

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

SQL> execute dbms_sqltune.drop_sql_profile(''); 

If successful, you should see the following: PL/SQL procedure successfully completed.

SQL> SELECT name, created FROM dba_sql_profiles ORDER BY created DESC; 

Command to Alter SQL Profile in Oracle for the SQL_ID:-

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

SQL> EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE ('','STATUS','DISABLED');

 If successful, you should see the following: PL/SQL procedure successfully completed. 

SQL> SELECT name, created FROM dba_sql_profiles ORDER BY created DESC; If you don't know the name of the SQL Profile then use the below query 

SQL> select NAME,SQL_TEXT from DBA_SQL_PROFILES where SQL_TEXT like '%SELECT% TABLE%NAME%';

 Query
---- 

The SQL_ID is not stored with the profiles. You can see if a statement is using a profile by querying v$sql where sql_profile is not null.

select sql_id, child_number, plan_hash_value plan_hash, sql_profile, executions execs, (elapsed_time/1000000)/decode(nvl(executions,0),0,1,executions) avg_etime, buffer_gets/decode(nvl(executions,0),0,1,executions) avg_lio, sql_text from v$sql s where upper(sql_text) like upper(nvl('&sql_text',sql_text)) and sql_text not like '%from v$sql where sql_text like nvl(%' and sql_id like nvl('&sql_id',sql_id) and sql_profile like nvl('&sql_profile_name',sql_profile) and sql_profile is not null order by 1, 2, 3 /

How to create sql profile

 @Sqlh
 
Select sqlid from @longops and get plan hash value from @sqlh or @sqlver for particular sqlid
 
Not in red sqlid after that Plan Hash value change sqlid and plan hash value bellow pl/sql procedure
 
Copy it in notepad and change sqlid and plan hash value
 
declare
     ar_hint_table    sys.dbms_debug_vc2coll;
     ar_profile_hints sys.sqlprof_attr := sys.sqlprof_attr();
     cl_sql_text      clob;
     i                pls_integer;
   begin
     with a as (
                select
                     rownum as r_no
                     , a.*
                From table( dbms_xplan.display_awr('913uapvkwtmav',   3122673927, null, 'OUTLINE' )
              ) a
     ),
     b as (
     select
              min(r_no) as start_r_no
     from
              a
     where
              a.plan_table_output = 'Outline Data'
     ),
     c as (
     select
              min(r_no) as end_r_no
     from
              a
           , b
    where
             a.r_no > b.start_r_no
    and      a.plan_table_output = '  */'
    ),
    d as (
    select
             instr(a.plan_table_output, 'BEGIN_OUTLINE_DATA') as start_col
     from
              a
            , b
     where
              r_no = b.start_r_no + 4
     )
     select
              substr(a.plan_table_output, d.start_col) as outline_hints
     bulk collect
     into
              ar_hint_table
     from
              a
            , b
            , c
            , d
     where
         a.r_no >= b.start_r_no + 4
    54    and      a.r_no <= c.end_r_no - 1
     order by
              a.r_no;
 
     select
              sql_text
     into
              cl_sql_text
     from
              sys.dba_hist_sqltext
     where
              sql_id = '913uapvkwtmav';
 
     -- this is only required
     -- to concatenate hints
     -- splitted across several lines
     -- and could be done in SQL, too
     i := ar_hint_table.first;
     while i is not null
     loop
       if ar_hint_table.exists(i + 1) then
         if substr(ar_hint_table(i + 1), 1, 1) = ' ' then
           ar_hint_table(i) := ar_hint_table(i) || trim(ar_hint_table(i + 1));
           ar_hint_table.delete(i + 1);
         end if;
       end if;
       i := ar_hint_table.next(i);
     end loop;
 
     i := ar_hint_table.first;
     while i is not null
     loop
       ar_profile_hints.extend;
       ar_profile_hints(ar_profile_hints.count) := ar_hint_table(i);
       i := ar_hint_table.next(i);
     end loop;
 
     dbms_sqltune.import_sql_profile(
       sql_text    => cl_sql_text
     , profile     => ar_profile_hints
     , name        => 'SQLP_913uapvkwtmav_3122673927'
     -- use force_match => true
     -- to use CURSOR_SHARING=SIMILAR
 -- behaviour, i.e. match even with
    98    -- differing literals
     , force_match => false
   );
  End;
 
  /
 
 
 
Check sql profile is create or not
 
@sql_profiles          Press Enter
Ask profile name     if don’t know sql profile name Press Enter it will display all prifile
 
 
After creating profile Gather stats of all tables which is used in that sqlid
 
EXEC DBMS_STATS.GATHER_TABLE_STATS('OLAP', '&table_name', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', degree => 32, cascade => TRUE);
 
SQL> !cat sql_profiles.sql
 
set pages 300 lines 300;
col name for a40 ;
col created for a50 ;
col last_modified for a50 ;
 
select name , CREATED,LAST_MODIFIED,STATUS from dba_sql_profiles where name like '%&name%'
order by CREATED;
 
 
 
SQL>
SQL>
SQL> !cat disable_profile.sql
EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE (name=>'&Profile_name',attribute_name =>'STATUS',value => 'DISABLED');
 
 
 
SQL>
SQL>
SQL> !cat enable_profile.sql
EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE (name=>'&Profile_name',attribute_name =>'STATUS',value => 'ENABLED');
 
 
SQL>
SQL>
SQL> !cat drop_profile.sql
EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('&PROFILE_NAME');
 
SQL>
 
x
x

Sql queries to check ACTIVE / INACTIVE Sessions

 --Total Count of sessions

select count(s.status) TOTAL_SESSIONS

from gv$session s;

--Total Count of Inactive sessions

select count(s.status) INACTIVE_SESSIONS

from gv$session s, v$process p

where

p.addr=s.paddr and

s.status='INACTIVE';

SESSIONS WHICH ARE IN INACTIVE STATUS FROM MORE THAN 1HOUR

select count(s.status) "INACTIVE SESSIONS > 1HOUR "

from gv$session s, v$process p

where

p.addr=s.paddr and

s.last_call_et > 3600 and

s.status='INACTIVE';

--COUNT OF ACTIVE SESSIONS

select count(s.status) ACTIVE_SESSIONS

from gv$session s, v$process p

where

p.addr=s.paddr and

s.status='ACTIVE';

--TOTAL SESSIONS COUNT ORDERED BY PROGRAM

col program for a30

select s.program,count(s.program) Total_Sessions

from gv$session s, v$process p

where  p.addr=s.paddr

group by s.program;

--TOTAL COUNT OF SESSIONS ORDERED BY MODULE

col module  for a30

prompt TOTAL SESSIONS

select s.module,count(s.sid) Total_Sessions

from gv$session s, v$process p

where  p.addr=s.paddr

group by s.module;

--TOTAL COUNT OF SESSIONS ORDERED BY ACTION

col action for a30

prompt TOTAL SESSIONS

select s.action,count(s.sid) Total_Sessions

from gv$session s, v$process p

where  p.addr=s.paddr

group by s.action;

--INACTIVE SESSIONS

prompt INACTIVE SESSIONS

select p.spid, s.sid,s.last_call_et/3600 last_call_et ,s.status,s.action,s.module,s.program

from gv$session s, v$process p

where

p.addr=s.paddr and

s.status='INACTIVE';

--INACTIVE

prompt INACTIVE SESSIONS

select count(s.status) INACTIVE

from gv$session s, gv$sqlarea t,v$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr and

s.status='INACTIVE';

--INACTIVE PROGRAMS

col module for a40             

prompt INACTIVE SESSIONS

col INACTIVE_PROGRAMS FOR A40

select distinct (s.program) INACTIVE_PROGRAMS,s.module

from gv$session s, v$process p

where  p.addr=s.paddr and

s.status='INACTIVE';

--INACTIVE PROGRAMS with disk reads

prompt INACTIVE SESSIONS

select distinct (s.program) INACTIVE_PROGRAMS,SUM(T.DISK_READS)

from gv$session s, gv$sqlarea t,v$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr and

s.status='INACTIVE'

GROUP BY S.PROGRAM;

--INACTIVE SESSIONS COUNT WITH PROGRAM

col program for a30

prompt TOTAL INACTIVE SESSIONS

col INACTIVE_PROGRAMS FOR A40

select s.program,count(s.program) Total_Inactive_Sessions

from gv$session s,v$process p

where     p.addr=s.paddr  AND

s.status='INACTIVE'

group by s.program

order by 2 desc;

--TOTAL INACTIVE SESSIONS MORE THAN 1HOUR

col program for a30

col INACTIVE_PROGRAMS FOR A40

select s.program,count(s.program) Inactive_Sessions_from_1Hour

from gv$session s,v$process p

where     p.addr=s.paddr  AND

s.status='INACTIVE'

and s.last_call_et > (3600)

group by s.program

order by 2 desc;

--TOTAL INACTIVE SESSIONS GROUP BY  MODULE

col program for a60

COL MODULE FOR A30

prompt TOTAL SESSIONS

col INACTIVE_PROGRAMS FOR A40

select s.module,count(s.module) Total_Inactive_Sessions

from gv$session s,v$process p

where     p.addr=s.paddr  AND

s.status='INACTIVE'

group by s.module;

--INACTIVE SESSION DETAILS MORE THAN 1 HOUR

set pagesize 40

col INST_ID for 99

col spid for a10

set linesize 150

col PROGRAM for a10

col action format a10

col logon_time format a16

col module format a13

col cli_process format a7

col cli_mach for a15

col status format a10

col username format a10

col last_call_et_Hrs for 9999.99

col sql_hash_value for 9999999999999col username for a10

set linesize 152

set pagesize 80

col "Last SQL" for a60

col elapsed_time for 999999999999

select p.spid, s.sid,s.last_call_et/3600 last_call_et_Hrs ,s.status,s.action,s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"

from gv$session s, gv$sqlarea t,gv$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr and

s.status='INACTIVE'

and s.last_call_et > (3600)

order by last_call_et;

--INACTIVE PROGRAM  --ANY--

select p.spid, s.sid,s.last_call_et/3600 last_call_et_Hrs ,s.status,s.action,s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"

from gv$session s, gv$sqlarea t,gv$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr and

s.status='INACTIVE'

And s.program='&PROGRAM_NAME'

order by last_call_et;

--INACTIVE MODULES  --ANY--

select p.spid, s.sid,s.last_call_et/3600 last_call_et_Hrs ,s.status,s.action,s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"

from gv$session s, gv$sqlarea t,gv$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr

And s.module like '%order_cleanup_hazmat_v3.sql'

order by last_call_et;

--INACTIVE JDBC SESSIONS

set pagesize 40

col INST_ID for 99

col spid for a10

set linesize 150

col PROGRAM for a10

col action format a10

col logon_time format a16

col module format a13

col cli_process format a7

col cli_mach for a15

col status format a10

col username format a10

col last_call_et for 9999.99

col sql_hash_value for 9999999999999col username for a10

set linesize 152

set pagesize 80

col "Last SQL" for a60

col elapsed_time for 999999999999

select p.spid, s.sid,s.last_call_et/3600 last_call_et ,s.status,s.action,

s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"

from gv$session s, gv$sqlarea t,gv$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr and

s.status='INACTIVE'

and s.program='JDBC Thin Client'

and s.last_call_et > 3600

order by last_call_et;

--COUNT OF INACTIVE SESSIONS MORE THAN ONE HOUR

SELECT COUNT(P.SPID)

from gv$session s, gv$sqlarea t,gv$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr and

s.status='INACTIVE'

and s.program='JDBC Thin Client'

and s.last_call_et > 3600

order by last_call_et;

FORMS

--TOTAL FORM SESSIONS

SELECT COUNT(S.SID) INACTIVE_FORM_SESSIONS FROM V$SESSION S

WHERE S.STATUS='INACTIVE' and

s.action like ('%FRM%');

--FORMS SESSIONS DETAILS

col "Last SQL" for a30

select p.spid,s.sid,s.status,s.last_call_et/3600 last_call_et_hrs ,

s.sid,t.disk_reads, t.elapsed_time,lpad(t.sql_text,30) "Last SQL"

from gv$session s, gv$sqlarea t,gv$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr and

s.action like ('FRM%') and

s.last_call_et > 3600

order by spid;                      

col machine for a15

col "Last SQL" for a30

select p.spid,s.sid,s.status,s.last_call_et/3600 last_call_et_hrs ,

S.ACTION,s.process Client_Process,s.machine

from gv$session s, gv$sqlarea t,gv$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr and

s.action like ('FRM%') and

s.last_call_et > 3600;         

order by 4;                           

--INACTIVE FORMS SESSIONS DETAILS

col program for a15

col last_call_et for 999.99

select p.spid, s.sid, s.process,s.last_call_et/3600 last_call_et ,s.status,s.action,s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"

from gv$session s, gv$sqlarea t,gv$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr and

s.status='INACTIVE'

and s.action like 'FRM:%'

and s.last_call_et > 3600

order by last_call_et desc;

--UNIQUE SPID

select unique(p.spid)

from gv$session s, gv$sqlarea t,gv$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr and

s.status='INACTIVE'

and s.action like 'FRM:%'

and s.last_call_et > 3600;

--COUNT FORMS

select COUNT(p.spid)

from gv$session s, gv$sqlarea t,gv$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr and

s.status='INACTIVE'

and s.action like 'FRM:%'

and s.last_call_et > 3600;

--ZERO HASH VALUE

select COUNT(p.spid)

from gv$session s,gv$process p

where

p.addr=s.paddr and

s.status='INACTIVE'

and s.action like 'FRM:%'

and s.last_call_et > 3600

AND S.SQL_HASH_VALUE=0;

--INACTIVE FORM BY NAME

select count(s.sid) from v$session S

where s.action like ('%&ACTION%')

AND S.STATUS='INACTIVE';

GROUP BY ACTION

SELECT S.ACTION,COUNT(S.SID) FROM V$SESSION S

WHERE S.STATUS='INACTIVE' and

s.action like ('%FRM%')

group by s.action;

FROM A SPECIFIC USERNAME

SET LINSIZE 152

col spid for a10

col process_spid for a10

col user_name for a20

col form_name for a20

select a.pid,a.spid,a.process_spid, c.user_name,to_char(a.start_time,'DD-MON-YYYY HH24:MI:SS') "START_TIME" ,

d.user_form_name "FORM_NAME"

from apps.fnd_logins a, apps.fnd_login_resp_forms b, apps.fnd_user c,

apps.fnd_form_tl d

where

a.login_id=b.login_id

and c.user_name like 'JROMO'

and a.user_id=c.user_id

and trunc(b.start_time) >trunc(sysdate -11)

and trunc(b.end_time) is null

and b.form_id=d.form_id

and d.language='US';

INACTIVE FORM

set pagesize 40

col INST_ID for 99

col spid for a10

set linesize 150

col PROGRAM for a10

col action format a10

col logon_time format a16

col module format a13

col cli_process format a7

col cli_mach for a15

col status format a10

col username format a10

col last_call_et for 9999.99

col sql_hash_value for 9999999999999col username for a10

set linesize 152

set pagesize 80

col "Last SQL" for a30

col elapsed_time for 999999999999

select p.spid, s.sid,s.process cli_process,s.last_call_et/3600 last_call_et ,

s.status,s.action,s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"

from gv$session s, gv$sqlarea t,gv$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr and

s.status='INACTIVE'

and s.action like ('FRM%')

and s.last_call_et > (3600*3)

order by last_call_et;

--INACTIVE FORM SESSIONS 

col cli_proc for a9

COL AUDSID FOR A6

COL PID FOR A6

COL SID FOR A5

COL FORM_NAME FOR A25

COL USER_NAME FOR A15

col last_call_et for 9999.99

SELECT

-- /*+ ORDERED FULL(fl) FULL(vp) USE_HASH(fl vp) */

( SELECT SUBSTR ( fu.user_name, 1, 20 )

FROM apps.fnd_user fu

WHERE fu.user_id = fl.user_id

) user_name,vs.status,

TO_CHAR ( fl.start_time, 'DD-MON-YYYY HH24:MI' ) login_start_time,

TO_CHAR ( fl.end_time, 'DD-MON-YYYY HH24:MI' ) login_end_time,

vs.last_call_et/3600 last_call_et,

SUBSTR ( fl.process_spid, 1, 6 ) spid,

SUBSTR ( vs.process, 1, 8 ) cli_proc,

SUBSTR ( TO_CHAR ( vs.sid ), 1, 3 ) sid,

SUBSTR ( TO_CHAR ( vs.serial#), 1, 7 ) serial#,

SUBSTR ( TO_CHAR ( rf.audsid ), 1, 6 ) audsid,

SUBSTR ( TO_CHAR ( fl.pid ), 1, 3 ) pid,

SUBSTR ( vs.module || ' - ' ||

( SELECT SUBSTR ( ft.user_form_name, 1, 40 )

FROM apps.fnd_form_tl ft

WHERE ft.application_id = rf.form_appl_id

AND ft.form_id        = rf.form_id

AND ft.language       = USERENV('LANG')

), 1, 40 ) form_name

FROM apps.fnd_logins           fl,

gv$process            vp,

apps.fnd_login_resp_forms rf,

gv$session            vs

WHERE fl.start_time   > sysdate - 7 /* login within last 7 days */

AND fl.login_type   = 'FORM'

AND fl.process_spid = vp.spid

AND fl.pid          = vp.pid

AND fl.login_id     = rf.login_id

AND rf.end_time    IS NULL

AND rf.audsid       = vs.audsid

and vs.status='INACTIVE'

ORDER BY

vs.process,

fl.process_spid;

--ACTIVE

prompt ACTIVE SESSIONS

select count(s.status) ACTIVE

from gv$session s, gv$sqlarea t,v$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr and

s.status='ACTIVE';

--MODULE

set pagesize 40

col INST_ID for 99

col spid for a10

set linesize 150

col PROGRAM for a10

col action format a10

col logon_time format a16

col module format a13

col cli_process format a7

col cli_mach for a15

col status format a10

col username format a10

col last_call_et for 9999.99

col sql_hash_value for 9999999999999col username for a10

set linesize 152

set pagesize 80

col "Last SQL" for a30

col elapsed_time for 999999999999

select p.spid, s.sid,s.process cli_process,s.last_call_et/3600 last_call_et ,

s.status,s.action,s.module,s.program,t.disk_reads,lpad(t.sql_text,30) "Last SQL"

from gv$session s, gv$sqlarea t,gv$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr 

and s.MODULE like ('&MODULE_NAME_1HR%')

and s.last_call_et > ('&TIME_HRS' * 3600)

order by last_call_et;

select p.spid, s.sid,s.process cli_process,s.last_call_et/3600 last_call_et ,

s.status,s.action,s.module,s.program

from gv$session s, gv$sqlarea t,gv$process p

where s.sql_address =t.address and

p.addr=s.paddr 

and s.MODULE like ('%TOAD%')

Order by last_call_et;

--TOAD SESSIONS

select p.spid, s.sid,s.process cli_process,s.last_call_et/3600 last_call_et ,

s.status,s.action,s.module,s.program

from gv$session s, gv$process p

where

p.addr=s.paddr 

and s.MODULE like ('%TOAD%')

Order by last_call_et;

--CLIENT MACHINE SESSIONS COUNT

select count(s.process) TOTAL from v$session S

where s.machine like ('%&CLIENT_MACHINE%');

select count(s.process) INACTIVE from v$session S

where s.machine like ('%&CLIENT_MACHINE%')

and s.status='INACTIVE';

hash value=0

select count(s.process) from v$session S

where s.machine like ('%&CLIENT_MACHINE%')

AND S.SQL_HASH_VALUE=0;

select count(s.process) from v$session S

where s.machine like ('%&CLIENT_MACHINE%')

AND S.SQL_HASH_VALUE=0

AND S.LAST_CALL_ET > 3600;

--Unique Actions

col module for a40             

prompt INACTIVE SESSIONS

col INACTIVE_PROGRAMS FOR A40

select distinct (s.program) INACTIVE_PROGRAMS,s.module

from gv$session s, gv$sqlarea t,v$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

s.machine like ('%&CLIENT_MACHINE%') AND

p.addr=s.paddr and

s.status='INACTIVE';

GROUP BY  program

col program for a60

prompt TOTAL SESSIONS

col INACTIVE_PROGRAMS FOR A40

select s.program,count(s.program) Total_Inactive_Sessions

from gv$session s, gv$sqlarea t,v$process p

where s.sql_address =t.address and

s.sql_hash_value =t.hash_value and

p.addr=s.paddr  AND

s.machine like ('%&CLIENT_MACHINE%') AND

s.status='INACTIVE'

group by s.program;

Load data using SQL loader via shell or equivalent python

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

Shell script:

==========run_sqlldr.sh==============

#!/bin/bash
# Oracle Environment
export ORACLE_HOME=/u01/app/oracle/product/19.0.0/db_1
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=oradb
# Variables
USERID=ashwani/ashwani@pdb1
CONTROL_FILE=/home/oracle/emp.ctl
LOG_FILE=/home/oracle/emp.log
BAD_FILE=/home/oracle/emp.bad
DATA_FILE=/home/oracle/emp.csv
echo "Starting SQL Loader..."
sqlldr userid=$USERID \
control=$CONTROL_FILE \
data=$DATA_FILE \
log=$LOG_FILE \
bad=$BAD_FILE
if [ $? -eq 0 ]
then
   echo "Data loaded successfully."
else
   echo "SQL Loader failed."
fi

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

Python script:

=============run_sqlldr.py============================================

#!/usr/bin/env python3

import argparse

import os

import subprocess

import sys

from datetime import datetime

# ---------------------------------------------------------------------------

# Oracle environment

# ---------------------------------------------------------------------------

ORACLE_HOME = "/u01/app/oracle/product/19.0.0/db_1"

ORACLE_SID  = "oradb"

# ---------------------------------------------------------------------------

# Default SQL*Loader parameters

# ---------------------------------------------------------------------------

DEFAULT_USERID   = "ashwani/ashwani@pdb1"

DEFAULT_CONTROL  = "/home/oracle/emp.ctl"

DEFAULT_LOG      = "/home/oracle/emp.log"

DEFAULT_BAD      = "/home/oracle/emp.bad"

DEFAULT_DATA     = "/home/oracle/emp.csv"

def parse_args():

    p = argparse.ArgumentParser(description="Python wrapper for SQL*Loader (sqlldr).")

    p.add_argument("--oracle-home", default=ORACLE_HOME)

    p.add_argument("--oracle-sid",  default=ORACLE_SID)

    p.add_argument("--userid",      default=DEFAULT_USERID)

    p.add_argument("--control",     default=DEFAULT_CONTROL)

    p.add_argument("--data",        default=DEFAULT_DATA)

    p.add_argument("--log",         default=DEFAULT_LOG)

    p.add_argument("--bad",         default=DEFAULT_BAD)

    return p.parse_args()

def build_env(oracle_home, oracle_sid):

    env = os.environ.copy()

    env["ORACLE_HOME"] = oracle_home

    env["ORACLE_SID"]  = oracle_sid

    env["PATH"]        = os.path.join(oracle_home, "bin") + os.pathsep + env.get("PATH", "")

    lib = os.path.join(oracle_home, "lib")

    env["LD_LIBRARY_PATH"] = lib + os.pathsep + env.get("LD_LIBRARY_PATH", "")

    return env

def main():

    args = parse_args()

    env  = build_env(args.oracle_home, args.oracle_sid)

    sqlldr = os.path.join(args.oracle_home, "bin", "sqlldr")

    cmd = [

        sqlldr,

        "userid="  + args.userid,

        "control=" + args.control,

        "data="    + args.data,

        "log="     + args.log,

        "bad="     + args.bad,

    ]

    print("Starting SQL Loader...")

    print("Command:", " ".join(cmd))

    # universal_newlines=True is the Python 3.6-compatible equivalent of text=True

    result = subprocess.run(cmd, env=env,

                            stdout=subprocess.PIPE,

                            stderr=subprocess.STDOUT,

                            universal_newlines=True)

    print(result.stdout or "")

    # Append run metadata to log

    try:

        with open(args.log, "a") as lf:

            lf.write("\n" + "="*60 + "\n")

            lf.write("Python runner: {}\n".format(datetime.now()))

            lf.write("Return code : {}\n".format(result.returncode))

    except OSError as e:

        print("WARNING: could not write log - {}".format(e))

    if result.returncode == 0:

        print("Data loaded successfully.")

    else:

        print("SQL Loader failed. (exit code: {})".format(result.returncode))

        sys.exit(result.returncode)

if __name__ == "__main__":

    main()

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

create table:

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

CREATE TABLE emp (

    emp_id NUMBER GENERATED ALWAYS AS IDENTITY,

    emp_name VARCHAR2(100),

    salary NUMBER,

    created_time TIMESTAMP DEFAULT SYSTIMESTAMP

);

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

Data file: cat emp.csv

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

PETER,50000

RICHARD,60000

ASHWANI,75000

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

Control File: emp.ctl

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

LOAD DATA

INFILE 'emp.csv'

INSERT

INTO TABLE emp

FIELDS TERMINATED BY ','

TRAILING NULLCOLS

(

  emp_name,

  salary

)

Analysing alert- Metrics Process Limit% is at 99 / Process limit usage % 99 in oracle database

What this means:

Process Limit % = 99% → almost all DB processes are used
Controlled by parameter: PROCESSES
When limit hits → users get errors like:
ORA-00020: maximum number of processes exceeded

Step 1: Check current usage

SELECT resource_name, current_utilization, max_utilization, limit_value
FROM v$resource_limit
WHERE resource_name IN ('processes','sessions');

Step 2: Identify what is consuming processes

SELECT username, program, COUNT(*)
FROM v$session
GROUP BY username, program
ORDER BY 3 DESC;

Check inactive sessions (big culprit)

SELECT status, COUNT(*)
FROM v$session
GROUP BY status;
Check Long idle sessions:
SELECT sid, serial#, username, status, last_call_et
FROM v$session
WHERE status='INACTIVE'
ORDER BY last_call_et DESC;

Drop the mail to Application Owner. Once got confirmation Kill the session.

ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

Note: Don’t kill active business sessions blindly.

Step 4: Increase PROCESSES (permanent fix) 

SHOW PARAMETER processes;

ALTER SYSTEM SET processes=500 SCOPE=SPFILE;

Then restart DB

Shell Script to Generate RMAN Backup Monitoring Report for All Databases on a Single Server

 [oracle@srv1 ~]$ cat rman_backup_report_enhanced.sh

#!/bin/bash

# ===== CONFIGURATION =====

REPORT_DIR="/tmp"

DATE_TAG=$(date +%Y%m%d_%H%M%S)

TEXT_REPORT="$REPORT_DIR/rman_report_$DATE_TAG.log"

HTML_REPORT="$REPORT_DIR/rman_report_$DATE_TAG.html"

# Email (optional)

EMAIL_TO="your_email@example.com"

SEND_EMAIL=false   # change to true if mailx configured

# ===== INIT =====

echo "RMAN Backup Report - $(date)" > $TEXT_REPORT

# HTML Header

cat <<EOF > $HTML_REPORT

<html>

<head>

<style>

body { font-family: Arial; }

table { border-collapse: collapse; width: 100%; }

th, td { border: 1px solid black; padding: 6px; text-align: center; }

th { background-color: #333; color: white; }

.success { background-color: #c6efce; }

.failed { background-color: #ffc7ce; }

</style>

</head>

<body>

<h2>RMAN Backup Report</h2>

<p>Generated on: $(date)</p>

<table>

<tr>

<th>DB Name</th>

<th>Input Type</th>

<th>Status</th>

<th>Start Time</th>

<th>End Time</th>

<th>Duration (Min)</th>

<th>Size (MB)</th>

</tr>

EOF

TOTAL_DB=0

FAILED_COUNT=0

# ===== LOOP DATABASES =====

grep -v '^#' /etc/oratab | grep -v '^$' | while IFS=: read ORACLE_SID ORACLE_HOME REST

do

    export ORACLE_SID

    export ORACLE_HOME

    export PATH=$ORACLE_HOME/bin:$PATH

    TOTAL_DB=$((TOTAL_DB+1))

    echo "Checking DB: $ORACLE_SID" >> $TEXT_REPORT

    RESULT=$(sqlplus -s / as sysdba <<EOF

SET HEADING OFF FEEDBACK OFF PAGESIZE 0 LINESIZE 300

SELECT

    '$ORACLE_SID' || '|' ||

    INPUT_TYPE || '|' ||

    STATUS || '|' ||

    TO_CHAR(START_TIME,'DD-MON-YYYY HH24:MI') || '|' ||

    TO_CHAR(END_TIME,'DD-MON-YYYY HH24:MI') || '|' ||

    ROUND((END_TIME - START_TIME)*24*60) || '|' ||

    ROUND(OUTPUT_BYTES/1024/1024)

FROM

    V\$RMAN_BACKUP_JOB_DETAILS

WHERE

    START_TIME > SYSDATE - 1

ORDER BY START_TIME DESC;

EXIT;

EOF

)

    echo "$RESULT" | while IFS="|" read DB INPUT STATUS START END DURATION SIZE

    do

        [[ -z "$DB" ]] && continue

        # Failure detection

        CLASS="success"

        if [[ "$STATUS" != "COMPLETED" ]]; then

            CLASS="failed"

            FAILED_COUNT=$((FAILED_COUNT+1))

        fi

        # Text report

        printf "%-10s %-12s %-10s %-18s %-18s %-8s %-8s\n" \

        "$DB" "$INPUT" "$STATUS" "$START" "$END" "$DURATION" "$SIZE" >> $TEXT_REPORT

        # HTML report

        cat <<ROW >> $HTML_REPORT

<tr class="$CLASS">

<td>$DB</td>

<td>$INPUT</td>

<td>$STATUS</td>

<td>$START</td>

<td>$END</td>

<td>$DURATION</td>

<td>$SIZE</td>

</tr>

ROW

    done

done

# ===== SUMMARY =====

cat <<EOF >> $HTML_REPORT

</table>

<h3>Summary</h3>

<p>Total Databases Checked: $TOTAL_DB</p>

<p>Failed Jobs: $FAILED_COUNT</p>

</body>

</html>

EOF

echo "====================================" >> $TEXT_REPORT

echo "Total DB Checked: $TOTAL_DB" >> $TEXT_REPORT

echo "Failed Jobs: $FAILED_COUNT" >> $TEXT_REPORT

# ===== EMAIL =====

if [ "$SEND_EMAIL" = true ]; then

    mailx -s "RMAN Backup Report" -a "$HTML_REPORT" "$EMAIL_TO" < $TEXT_REPORT

fi

# ===== OUTPUT =====

echo "Text Report  : $TEXT_REPORT"

echo "HTML Report  : $HTML_REPORT"

Script to monitor the RMAN backup

chmod +x rman_backup_report.sh

./rman_backup_report.sh 

[oracle@srv1 ~]$ cat rman_backup_report.sh

#!/bin/bash

# Output file

REPORT_FILE="/tmp/rman_backup_report_$(date +%Y%m%d_%H%M%S).log"

echo "RMAN Backup Consolidated Report" > $REPORT_FILE

echo "Generated on: $(date)" >> $REPORT_FILE

echo "=========================================" >> $REPORT_FILE

# Loop through databases from oratab

grep -v '^#' /etc/oratab | grep -v '^$' | while IFS=: read ORACLE_SID ORACLE_HOME REST

do

    export ORACLE_SID

    export ORACLE_HOME

    export PATH=$ORACLE_HOME/bin:$PATH

    echo "" >> $REPORT_FILE

    echo "-----------------------------------------" >> $REPORT_FILE

    echo "Database: $ORACLE_SID" >> $REPORT_FILE

    echo "-----------------------------------------" >> $REPORT_FILE

    sqlplus -s / as sysdba <<EOF >> $REPORT_FILE

SET LINESIZE 200

SET PAGESIZE 100

SET TRIMOUT ON

SET TRIMSPOOL ON

COLUMN STATUS FORMAT A10

COLUMN INPUT_TYPE FORMAT A15

COLUMN START_TIME FORMAT A20

COLUMN END_TIME FORMAT A20

SELECT

    INPUT_TYPE,

    STATUS,

    TO_CHAR(START_TIME,'DD-MON-YYYY HH24:MI') START_TIME,

    TO_CHAR(END_TIME,'DD-MON-YYYY HH24:MI') END_TIME

FROM

    V\$RMAN_BACKUP_JOB_DETAILS

WHERE

    START_TIME > SYSDATE - 2

ORDER BY

    START_TIME DESC;

EXIT;

EOF

done

echo "" >> $REPORT_FILE

echo "=========================================" >> $REPORT_FILE

echo "End of Report" >> $REPORT_FILE

# Print location

echo "Report generated at: $REPORT_FILE"

SWAP usage

grep VmSwap /proc/[0-9]*/status | sort -nk2

ps p procesid

for FILE in `ls /proc/[0-9]*/status`; do awk '/VmSwap|Name/ {printf $2 " " $3} END {print ""}' "$FILE" ; done

for FILE in `ls /proc/[0-9]*/status`; do awk '/VmSwap|Name/ {printf $2 " " $3} END {print ""}' "$FILE" ; done | sort -nk2 |grep kB >/tmp/swap.log

HOW TO FIND THE SESSION CONSUMING HIGH MEMORY AT DATABASE LEVEL.

 ###########Top 10 highest memory sessions#############

SELECT s.sid,
       s.serial#,
       s.username,
       s.program,
       p.spid AS os_pid,
       ROUND(st.value/1024/1024,2) AS pga_memory_mb
FROM v$session s
JOIN v$sesstat st ON s.sid = st.sid
JOIN v$statname sn ON st.statistic# = sn.statistic#
JOIN v$process p ON s.paddr = p.addr
WHERE sn.name = 'session pga memory'
---and s.program not like '%ORACLE%'
ORDER BY st.value DESC;

For Multi Tennant DB

col PDB_NAME  form a15
col USERNAME form a15
col MACHINE form a18
col Module form a15
col OS_PID form a10
SELECT 
       c.name AS pdb_name,
       s.inst_id,
       s.sid,
       s.serial#,
       s.username,
       s.machine,
       s.program,
       s.module,
       s.sql_id,
       p.spid AS os_pid,
       ROUND(st.value/1024/1024,2) AS pga_memory_mb,
       s.logon_time
FROM gv$session s
JOIN gv$sesstat st 
     ON s.sid = st.sid 
     AND s.inst_id = st.inst_id
JOIN gv$statname sn 
     ON st.statistic# = sn.statistic# 
     AND st.inst_id = sn.inst_id
JOIN gv$process p 
     ON s.paddr = p.addr 
     AND s.inst_id = p.inst_id
LEFT JOIN v$containers c 
     ON s.con_id = c.con_id
WHERE sn.name = 'session pga memory'
AND s.type = 'USER'
AND s.program NOT LIKE '%ORACLE%'
ORDER BY st.value DESC;

#####This shows sessions active about 1 hour ago with PGA usage###########

SELECT 
    ash.sample_time,
    ash.session_id,
    ash.session_serial#,
    ash.sql_id,
    ash.program,
    ROUND(ash.pga_allocated/1024/1024,2) AS pga_mb
FROM dba_hist_active_sess_history ash
WHERE ash.sample_time BETWEEN SYSDATE - 1/24 AND SYSDATE - 55/1440
ORDER BY ash.pga_allocated DESC;

###Top SQL by PGA Memory Usage

SELECT 
    c.name AS pdb_name,
    s.sid,
    s.serial#,
    s.username,
    s.sql_id,
    s.program,
    ROUND(st.value/1024/1024,2) AS pga_mb
FROM gv$session s
JOIN gv$sesstat st 
     ON s.sid = st.sid 
     AND s.inst_id = st.inst_id
JOIN gv$statname sn 
     ON st.statistic# = sn.statistic#
     AND st.inst_id = sn.inst_id
LEFT JOIN v$containers c 
     ON s.con_id = c.con_id
WHERE sn.name = 'session pga memory'
AND s.type='USER'
ORDER BY st.value DESC;

##Query: SQL_ID Consuming Highest Memory in a Time Window

SELECT 
    c.name AS pdb_name,
    ash.sql_id,
    COUNT(*) samples,
    ROUND(MAX(ash.pga_allocated)/1024/1024,2) AS max_pga_mb,
    ROUND(AVG(ash.pga_allocated)/1024/1024,2) AS avg_pga_mb
FROM dba_hist_active_sess_history ash
LEFT JOIN v$containers c
       ON ash.con_id = c.con_id
WHERE ash.sample_time BETWEEN 
      TO_DATE('08-MAR-2026 01:00','DD-MON-YYYY HH24:MI')
  AND TO_DATE('08-MAR-2026 02:30','DD-MON-YYYY HH24:MI')
AND ash.sql_id IS NOT NULL
GROUP BY c.name, ash.sql_id
ORDER BY max_pga_mb DESC;

--OR only for PDBS

SELECT 
    c.name AS pdb_name,
    ash.sql_id,
    COUNT(*) samples,
    ROUND(MAX(ash.pga_allocated)/1024/1024,2) AS max_pga_mb,
    ROUND(AVG(ash.pga_allocated)/1024/1024,2) AS avg_pga_mb
FROM dba_hist_active_sess_history ash
LEFT JOIN v$containers c
       ON ash.con_id = c.con_id
WHERE ash.sample_time BETWEEN 
      TO_DATE('08-MAR-2026 01:00','DD-MON-YYYY HH24:MI')
  AND TO_DATE('08-MAR-2026 02:30','DD-MON-YYYY HH24:MI')
AND ash.sql_id IS NOT NULL AND c.name <>'CDB$ROOT'
GROUP BY c.name, ash.sql_id
ORDER BY max_pga_mb DESC;

Performance Tuning Oracle 19c (Finding CPU Consuming Query) - Multitenant DB

### Find the SNAPID

select CON_ID,SNAP_ID,STARTUP_TIME,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME from dba_hist_snapshot order by SNAP_ID;

####Below Query will find the sqlid which consumes the most CPU during mention time.######

SELECT
   p.name AS pdb_name,
   ash.session_id,
   ash.session_serial#,
   ash.user_id,
   ash.program,
   ash.sql_id,
   COUNT(*) AS cpu_samples
FROM
   dba_hist_active_sess_history ash,
   v$pdbs p
WHERE
   ash.sample_time BETWEEN
      to_date('11-JAN-2026 23:00','DD-MON-YYYY HH24:MI')
      AND
      to_date('12-JAN-2026 00:30','DD-MON-YYYY HH24:MI')
AND ash.session_state = 'ON CPU'
AND ash.con_id = p.con_id
GROUP BY
   p.name,
   ash.session_id,
   ash.session_serial#,
   ash.user_id,
   ash.program,
   ash.sql_id
ORDER BY cpu_samples DESC
FETCH FIRST 10 ROWS ONLY;

######Once we have details of SQLID we can get the details of sql text fron history view as below:#######

SELECT sql_id, sql_text FROM dba_hist_sqltext WHERE sql_id='2yhkvj9bru81z';

###For Current session: 

select con_id,SERVICE_NAME,sid from v$session where sql_id='247cszx5pfbqa';

or

SELECT

   s.sql_id,
   s.executions_total,
   ROUND(s.cpu_time_total/1000000,2) cpu_seconds,
   t.sql_text
FROM
   dba_hist_sqlstat s,
   dba_hist_sqltext t
WHERE
   s.sql_id = t.sql_id
AND s.sql_id = '2yhkvj9bru81z';

#######Search SQL text by keyword: Suppose you remember only part of query#######

SELECT sql_id, sql_text
FROM dba_hist_sqltext
WHERE LOWER(sql_text) LIKE '%material_records%';

###To find which SQL was running at a certain time:###

--This gives the most active SQLs in last 1 hour with full text.

SELECT
    h.sql_id,
    DBMS_LOB.SUBSTR(t.sql_text, 4000, 1) AS sql_text,
    COUNT(*) AS occurrences
FROM
    dba_hist_active_sess_history h,
    dba_hist_sqltext t
WHERE
    h.sql_id = t.sql_id
AND h.sample_time BETWEEN SYSDATE - (1/24) AND SYSDATE
GROUP BY
    h.sql_id,
    DBMS_LOB.SUBSTR(t.sql_text, 4000, 1)
ORDER BY occurrences DESC
FETCH FIRST 5 ROWS ONLY;

####Find SQL Text from Specific Snapshot--If you know snapshot range from AWR. Here we have snap_id 58 and 59.##

SELECT
t.sql_id,

   DBMS_LOB.SUBSTR(t.sql_text, 4000, 1) AS sql_text
FROM
   dba_hist_sqltext t
WHERE
   t.sql_id IN (
      SELECT DISTINCT sql_id
      FROM dba_hist_sqlstat
      WHERE snap_id BETWEEN 58 AND 59
   );

###########If you want only SQL from a specific PDB:########

SELECT
   t.sql_id,
   DBMS_LOB.SUBSTR(t.sql_text, 4000, 1) AS sql_text
FROM
   dba_hist_sqltext t
WHERE
   t.sql_id IN (
      SELECT DISTINCT sql_id
      FROM dba_hist_sqlstat
      WHERE snap_id BETWEEN 58 AND 59
      AND con_id = 3        -- your PDB con_id
   );

   

####If You Know the name of PDB:####

--Query for PDB = TRAINING

SELECT
   t.sql_id,
   DBMS_LOB.SUBSTR(t.sql_text, 4000, 1) AS sql_text
FROM
   dba_hist_sqltext t
WHERE
   t.sql_id IN (
      SELECT DISTINCT s.sql_id
      FROM dba_hist_sqlstat s,
           v$pdbs p
      WHERE s.con_id = p.con_id
      AND p.name = 'TRAINING'
      AND s.snap_id BETWEEN 58 AND 59
   );

   

###If You Want Top CPU SQL in TRAINING PDB###

SELECT
   s.sql_id,
   ROUND(SUM(s.cpu_time_delta)/1000000,2) AS cpu_seconds,
   DBMS_LOB.SUBSTR(t.sql_text, 4000, 1) AS sql_text
FROM
   dba_hist_sqlstat s,
   dba_hist_sqltext t,
   v$pdbs p
WHERE
   s.sql_id = t.sql_id
AND s.con_id = p.con_id
AND p.name = 'TRAINING'
AND s.snap_id BETWEEN 58 AND 59
GROUP BY
   s.sql_id,
   DBMS_LOB.SUBSTR(t.sql_text, 4000, 1)
ORDER BY cpu_seconds DESC
FETCH FIRST 5 ROWS ONLY;

###If you want ALL SQL executed in last 1 hour in TRAINING###

SELECT
   ash.sql_id,
   COUNT(*) AS samples,
   DBMS_LOB.SUBSTR(t.sql_text, 4000, 1) AS sql_text
FROM
   dba_hist_active_sess_history ash,
   dba_hist_sqltext t,
   v$pdbs p
WHERE
   ash.sql_id = t.sql_id
AND ash.con_id = p.con_id
AND p.name = 'TRAINING'
AND ash.sample_time BETWEEN SYSDATE - (1/24) AND SYSDATE
GROUP BY
   ash.sql_id,
   DBMS_LOB.SUBSTR(t.sql_text, 4000, 1)
ORDER BY samples DESC
FETCH FIRST 10 ROWS ONLY;

###If you want to know who ran those SQLs:###

SELECT
   ash.sql_id,
   u.username,
   ash.program,
   COUNT(*) AS cpu_samples,
   DBMS_LOB.SUBSTR(t.sql_text, 4000, 1) AS sql_text
FROM
   dba_hist_active_sess_history ash,
   dba_hist_sqltext t,
   v$pdbs p,
   dba_users u
WHERE
   ash.sql_id = t.sql_id
AND ash.con_id = p.con_id
AND ash.user_id = u.user_id
AND p.name = 'TRAINING'
AND ash.session_state = 'ON CPU'
AND ash.sample_time BETWEEN SYSDATE - (1/24) AND SYSDATE
GROUP BY
   ash.sql_id,
   u.username,
   ash.program,
   DBMS_LOB.SUBSTR(t.sql_text, 4000, 1)
ORDER BY cpu_samples DESC
FETCH FIRST 10 ROWS ONLY;

Important Notes:

DBA_HIST_SQLTEXT keeps SQL as long as AWR retention is set

Daily Scripts

 1) To find I/O order by Tablespces:

select name, phyrds, phyrds * 100 / (select sum(phyrds) from v$filestat)
phywrts,  phywrts * 100 /(select sum(phywrts) from v$filestat)
from  v$datafile df, v$filestat fs
where df.file# = fs.file# order by phyrds desc;

2) Memory Hungry Oracle Process:


select   sid,name,value from   v$statname n,v$sesstat s where   n.STATISTIC# = s.STATISTIC# and   name like 'session%memory%'order by 3 desc;

select sum(bytes)/1024/1024 Mb from (select bytes from v$sgastat union select value bytes from 
v$sesstat s,v$statname n where n.STATISTIC# = s.STATISTIC# and  n.name = 'session pga memory');

3) To see OS Version using PL/SQL

select DBMS_UTILITY.PORT_STRING from dual;

Script to find Top CPU consuming PDBs (last few minutes)

 SET LINES 200

COL pdb_name FORMAT A20

SELECT

    p.name            AS pdb_name,

    ROUND(SUM(s.value)/100,2) AS cpu_seconds,

    COUNT(*)          AS sessions

FROM

    v$sesstat s,

    v$statname n,

    v$session se,

    v$pdbs p

WHERE

    s.statistic# = n.statistic#

AND s.sid = se.sid

AND n.name = 'CPU used by this session'

AND se.con_id = p.con_id

GROUP BY p.name

ORDER BY cpu_seconds DESC;

==============or================

SET LINES 200
COL pdb_name   FORMAT A15
COL username   FORMAT A15
COL program    FORMAT A30
COL service    FORMAT A20
COL cpu_mins   FORMAT 99990.99
SELECT
    ROWNUM AS rank,
    x.*
FROM
(
    SELECT
        p.name              AS pdb_name,
        s.sid,
        s.serial#,
        s.username,
        s.service_name      AS service,
        s.program,
        s.sql_id,
        ROUND(v.value / (100*60), 2) AS cpu_mins
    FROM
        v$statname  n,
        v$sesstat  v,
        v$session  s,
        v$pdbs     p
    WHERE
        n.name = 'CPU used by this session'
    AND v.statistic# = n.statistic#
    AND v.sid = s.sid
    AND s.con_id = p.con_id
    AND v.value > 0
    ORDER BY v.value DESC
) x
WHERE ROWNUM <= 10;

Automation to execute revoke statement

#!/bin/bash

CSV_FILE="revoke_access.csv"
ORACLE_HOME="/u01/app/oracle/product/19c/dbhome_1"
PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_HOME PATH
# Skip header
tail -n +2 "$CSV_FILE" | while IFS=',' read -r CDB PDB GRANTEE OWNER OBJECT PRIV
do
  echo "Processing: $CDB | $PDB | $GRANTEE | $OWNER.$OBJECT | $PRIV"
  export ORACLE_SID=$CDB
  sqlplus -s / as sysdba <<EOF
SET FEEDBACK OFF
SET HEADING OFF
SET ECHO OFF
SET SERVEROUTPUT ON
ALTER SESSION SET CONTAINER=$PDB;
DECLARE
  v_status VARCHAR2(10);
  v_error  VARCHAR2(4000);
BEGIN
  BEGIN
    EXECUTE IMMEDIATE
      'REVOKE $PRIV ON $OWNER.$OBJECT FROM $GRANTEE';
    v_status := 'SUCCESS';
    v_error  := NULL;
  EXCEPTION
    WHEN OTHERS THEN
      v_status := 'FAILED';
      v_error  := SUBSTR(SQLERRM,1,4000);
  END;
  INSERT INTO SEC_AUDIT.REVOKE_CONTROL_LOG
  (
    CDB_NAME,
    PDB_NAME,
    GRANTEE,
    OBJECT_OWNER,
    OBJECT_NAME,
    PRIVILEGE,
    STATUS,
    ERROR_MESSAGE
  )
  VALUES
  (
    '$CDB',
    '$PDB',
    '$GRANTEE',
    '$OWNER',
    '$OBJECT',
    '$PRIV',
    v_status,
    v_error
  );
  COMMIT;
x
END;
/
EXIT;
EOF
done