Compare bad + good AWR Reports

There are 2 awr related sql scripts which may come handy in this case.
· awrddrpt.sql
· awrddrpi.sql
The awrddrpt.sql SQL script generates an HTML or text report that compares detailed performance attributes and configuration settings between two selected time periods.
To generate an AWR Compare Periods report:

1.

@$ORACLE_HOME/rdbms/admin/awrddrpt.sql

2. Specify whether you want an HTML or a text report:

Enter value for report_type: html
In this example, an HTML report is chosen.

3. Specify the number of days for which you want to list snapshot Ids in the first time period.

Enter value for num_days: 2


4. Specify a beginning and ending snapshot ID for the first time period:

Enter value for begin_snap: 2
Enter value for end_snap: 3


5. Specify the number of days for which you want to list snapshot Ids in the second time period.

Enter value for num_days2: 1


6. Specify a beginning and ending snapshot ID for the second time period:

Enter value for begin_snap2: 26
Enter value for end_snap2: 27


7. Enter a report name, or accept the default report name:

Enter value for report_name:
Using the report name awrdiff_1_102_1_126.txt

Plan taking long time +recently wrong stats gathered (Pin an execute plan in memory)

When a SQL query’s execution plan changes for the worse, pin the old execution plan to memory.


Problem
The top session in the database is running a query against table TABLE1.  It is eating 100% of the CPU and it is processing only 5 records per second when before it processed at least 90 records per second.
The top session in OEM is sid = 123
This query runs every week without issue, we need to see if the execution plan has changed recently:

1. Look up the session in v$session and grab the sql_id

select sql_id
from v$session
where sid = 123;

2. Look up the query in v$sql to get more info

select sql_text, plan_hash_value
from v$sql
where sql_id = ‘2absdksfkd3249’;

It returns the query text and 2228017235 as the plan hash value


3. Find all the hash values for the query
select plan_hash_value
from v$sql_plan
where sql_id = ‘2absdksfkd3249’;

This query actually shows two hash values for the  query id
2228016959
and
2228017235


So the query’s execution plan has changed recently.  This may have happened due to a change in statatistic for the table.  See if the statatistics for the table have changed recently:

4. Check the statistics to see when they were last gathered

select LAST_ANALYZED from dba_tables where table_name = ‘TABLE1’;


This shows that the table was last analyzed on Saturday and since then the query has been running badly.  This is because the Gather Stats job ran this weekend and changed the execute plan for the query.


Solution :
======

It turns out that since the Gather Statistics job ran this past weekend and changed the execution plan for the query.  The previous execution plan worked much better.  The solution in this case is to pin the old execution plan so that the query will use it instead of the new one.  Whenever new statatistics are gathered and a new execution plan is suggested, it will be ignored and the pinned one will be used instead.

This will pin the hash value of old plan to the query id:

DECLARE
 i NATURAL;
BEGIN
  i := dbms_spm.load_plans_from_cursor_cache(‘5xqp2k44fafy9’, 2228016959);
END;
/

Verified the base line creation using the following sql statement…
 
SELECT * FROM dba_sql_plan_baselines;

PT (check sql plan taking long time)

1) Gather stats + bkp stats + flush old plan




Disable the SQL Profile


EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE('spm_id','STATUS','DISABLED');


Flush the SQL from shared pool



Get the address and hash_value of the sql_id:


select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_Id='abcdefg';



Now purge the sql statement



exec DBMS_SHARED_POOL.PURGE ('0000000679JDGY99,1118349065','C');

select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_Id='abscdfs’;

it should show no row selected




--create table for stat's backup

begin   
dbms_stats.create_stat_table(ownname => 'SYSTEM',stattab => 'bkp_stats_TAB',tblspace => 'USERS');
end;
/








--taking backup of current stats of table



begin
dbms_stats.export_table_stats(ownname=>'XYZ',tabname=>'TBLE',stattab => 'bkp_stats_TAB',statid => 'xyz_tblc',statown => 'SYSTEM',cascade=>true);
end;
/


--gather stats

EXEC SYS.DBMS_STATS.GATHER_TABLE_STATS( 'XYZ','TBL', CASCADE => TRUE ,degree => 28,estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO');

********************* FIX after gather stats + flush old plan ********************

 SQL> select NUM_ROWS,LAST_ANALYZED,degree from dba_tables where table_name='TBL ' and owner='XYZ';

  NUM_ROWS LAST_ANAL DEGREE
---------- --------- ----------------------------------------
  22150995 08-JAN-19          1


SQL> select NUM_ROWS,LAST_ANALYZED,degree from dba_indexes  where index_name='TBLINDX_PK' and owner='XYZ';

  NUM_ROWS LAST_ANAL DEGREE
---------- --------- ----------------------------------------
  21882106 08-JAN-19 10



===
SQL> select LAST_DDL_TIME from dba_objects where OBJECT_NAME='TBLINDEX_PK';

LAST_DDL_
---------
05-JAN-19

SQL> alter index XYZ.TBLINDEX_PK noparallel;

Index altered.

SQL>  select LAST_DDL_TIME from dba_objects where OBJECT_NAME='TBLINDEX_PK';

LAST_DDL_
---------
09-JAN-19

SQL>  select NUM_ROWS,LAST_ANALYZED,degree from dba_indexes  where index_name='TBLINDEX_PK' and owner='XYZ';

  NUM_ROWS LAST_ANAL DEGREE
---------- --------- ----------------------------------------
  21882106 08-JAN-19 1

SQL>  select NUM_ROWS,LAST_ANALYZED,degree from dba_indexes  where index_name='TBLINDEX_PK' and owner='XYZ';

  NUM_ROWS LAST_ANAL DEGREE
---------- --------- ----------------------------------------
  21882106 08-JAN-19 1

SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_Id='ao9yn1shdk';

ADDRESS          HASH_VALUE
---------------- ----------
000000045BD67B88 1118349065

SQL> exec DBMS_SHARED_POOL.PURGE ('000000045BD67B88,1118349065','C');

PL/SQL procedure successfully completed.



SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_Id='ao9yn1shdk';

ADDRESS          HASH_VALUE
---------------- ----------
000000045BD67B88 1118349065

High cpu consuming SQL + Sessions Info + Long & Blocking Sessions

How to To Find sessions / SQL’s which consuming High CPU in Oracle Database.

Statspack + AWR + Below quries:
===============================
1) Get the Process ID (PID) from TOP command which consume high CPU Usages.

So the query to get the session details (SID) from OS PID (SPID) will be as per following.

select s.sid from v$process p, v$session s
where s.paddr=p.addr and p.spid = (PID) ;

3) Once we get the session ID, base on this information we can get the actual SQL statement which is causing
HIGH CPU usage on database server.

We can use the following query to get the actual SQL STATEMENT.

SELECT SQL_TEXT from V$SQLTEXT_WITH_NEWLINES where HASH_VALUE
= (select sql_hash_value from v$session
where SID = (SID_WITCH_CAPTURED_IN_STEP_2) ;


*******************************************************************

--# from below query you will findout sid:-

SELECT se.username, ss.sid, ROUND (value/100) "CPU Usage"
FROM v$session se, v$sesstat ss, v$statname st
WHERE ss.statistic# = st.statistic#
AND name LIKE '%CPU used by this session%'
AND se.sid = ss.SID
AND se.username IS NOT NULL
ORDER BY value DESC;

#
select
ss.username,
se.SID,
VALUE/100 cpu_usage_seconds
from
v$session ss,
v$sesstat se,
v$statname sn
where
se.STATISTIC# = sn.STATISTIC#
and
NAME like '%CPU used by this session%'
and
se.SID = ss.SID
and
ss.status='ACTIVE'
and
ss.username is not null
order by VALUE desc;

#
select sql_hash_value, count(*) from v$session
where status = 'ACTIVE' group by sql_hash_value order by 2 desc;

#
select sql_text,users_executing from v$sql where hash_value = <give hash value here which is out put of above query>;




================== Long Running sessions (>60 mins) ============


LongRunning Session   GV$

set echo off  linesize 200  pages 1000  head on  feedback on
 col username format a10
 col start_time format a15
 col curr_time format a15
 col osuser format a10
 col opname format a10
 col target format a25
 col tremain format 999999.99
 col elamin format 999999.99
 select a.sid,substr(b.username,1,10) username,substr(b.osuser,1,10) osuser,to_char(a.start_time,'dd-mon:hh24:mi:ss') start_time,
 to_char(sysdate,'dd-mon:hh24:mi:ss') curr_time,a.opname,a.target,a.totalwork,a.sofar,(a.elapsed_Seconds)/60 elamin,
 a.time_remaining/60 tremain
 from v$session_longops a,
 v$session b
 where a.totalwork<>a.sofar
 and a.sid=b.sid
 order by 3
 /


 ========>  Exact Query <===========
 set lines 300
 SELECT S.SID,S.SERIAL#,S.USERNAME,S.STATUS, s.sql_id,S.LAST_cALL_ET/60 "Min",S.OSUSER,S.MACHINE
FROM V$SESSION S,V$PROCESS P
WHERE S.PADDR=P.ADDR
AND S.USERNAME IS NOT NULL
AND S.STATUS='ACTIVE'
and S.LAST_cALL_ET/60>60 order by S.LAST_cALL_ET/60;

**********************************

 select sql_text from v$sqlarea where SQL_ID='&sqlid';

 *******
 alter system kill session '1421,14693';

=========RAMN LONG RUNNING =======

  select b.sid, b.serial#, a.spid, b.client_info,b.LOGON_TIME from v$process a, v$session b  where a.addr=b.paddr and client_info  like 'rman%';

=====> SQL TEXT FROM SQL ID ::

 set lines 300
 select a.sid,a.program,b.sql_text from v$session a, v$sqltext b where a.sql_hash_value = b.hash_value and a.sid=487
 order by a.sid,hash_value,piece;

 SQL> select sid, serial#,SQL_ADDRESS, status,PREV_SQL_ADDR from v$session where sid='76';

  select sid, serial#,SQL_ADDRESS, status,PREV_SQL_ADDR ,WAIT_TIME from v$session where sid='344';

SQL>  select SQL_ID,sql_text from v$sqltext where address='00000004EB782658';




*************** Sessions Info ***********
No.of connections:
 =================
 show parameter sessions

 SQL> SELECT COUNT(*) FROM v$session WHERE STATUS = 'ACTIVE';

 >select resource_name, current_utilization, max_utilization, limit_value from v$resource_limit where resource_name in ('sessions', 'processes');

 > select count(*),sum(decode(status, 'ACTIVE',1,0)) from v$session where type= 'USER'

######################## BLOCKING SESSIONS ########################

   col WAIT_CLASS for a20
 select blocking_session, sid, serial#, wait_class, seconds_in_wait from v$session where blocking_session is not NULL order by blocking_session ;

 Select (select username from v$session where sid=a.sid) blocker,
a.sid,' is blocking ',(select username from v$session where sid=b.sid) blockee,b
.sid from v$lock a, v$lock b where a.block = 1 and b.request > 0 and a.id1 = b.id1 and a.id2 = b.id2;

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

GoldenGate-- key comands

 The extract status, and details and replicat status, lag details and any long running transactions etc.

About Manager

info all
status manager
send manager childstatus

About Extract :


info extract ext1, detail
send extract ext1 status

stats extract ext1

stats extract ext2 reportrate hr  --> hr, min, sec can be used to check the processing rate

send extract myload2, getlag

lag extract ext2

About Replicat :

info replicat rep1, detail
send replicat rep1 status

stats replicat rep1

stats replicat myload2 reportrate sec  --> hr, min, sec can be used to check the processing rate

send replicat myload2, getlag

lag replicat myload2*


================= Long Running Sessions in GG================

About Long Running Transactions Oracle Golden Gate Replication will not propagate transactions to the destination which have NOT been committed on the source yet.
However Oracle Golden gate will write the open transaction data to the dirtemp directory in the home directory.

This data will then be read back later when the transaction is committed and needs to be propagated to the destination.
This is especially true when bulk transactions are run, processing a large account of the uncommitted data, which then has to be written temporarily to the dirtemp location.

SET LINES 300

COL start_time FOR A20

COL sid FOR 99999

COL serial# FOR 999999

COL username FOR A20

COL status FOR A10

COL schemaname FOR A10

COL process FOR A10

COL machine FOR A15

COL program FOR A30

COL module FOR A35

COL logon_time FOR A20

SELECT t.start_time,s.sid,s.serial#,s.username,

s.status, s.schemaname, s.process,s.machine,

s.program, s.module, used_ublk, used_urec,

TO_CHAR(s.logon_time,'mon-dd-yyyy HH24:MI:SS') logon_time

FROM v$transaction t, v$session s

WHERE s.saddr = t.ses_addR

ORDER BY start_time;