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;

     USER + PRIVILEGES + ROLES  ::



======== > Account Lock <===

select username,account_status from dba_users where account_status like '%LOCK%';

SQL > alter user XYZ account unlock;


 =====> Schema <============
 select owner, sum(bytes)/1024/1024/1024 schema_size_gig from dba_segments group by owner;

 select username from dba_users where username like '%XYZ%';

 alter session set current_schema=XYZ;

  select   sum(bytes)/1024/1024/1024 as size_in_gig, segment_type from  dba_segments where owner='XXX' group by segment_type;

 TABLE_NAME :
 ===========
 select TABLE_NAME ,STATUS,OWNER from dba_tables where OWNER='XXX'

 SQL> select TABLE_NAME ,STATUS,OWNER from dba_tables where table_name='XXX';

 * select OBJECT_NAME ,OWNER from dba_objects where OBJECT_NAME like '%LINK%' and owner='XXX'

  select segment_name,segment_type,bytes/1024/1024 MB from dba_segments where segment_type='TABLE' and segment_name='XXX';

 ============= USER CREATION + GRANT PRIVILIGES ===============

select username from dba_users where username like '%XXX%';
SQL> create user xxx identified by xxx;

SQL> grant create session to  xxxx;



>>>>>
    select role from dba_roles where role like '%READ_ONLY%';

>>>  select 'grant select on SHEMANAME.'||table_name|| '  to USERNAME;' from dba_tables where owner='XXXX';


======== USER MAP ==================

 select role from dba_roles where role like '%READ_ONLY%';

select * from DBA_ROLE_PRIVS where GRANTEE=upper('&user');
select grantee,table_name,privilege from dba_tab_privs where grantee='&rolename';

=========


The best way I believe is the take a spool of the grants and execute them once after the import is complete
==> ROLES
select 'grant '||PRIVILEGE||' on '||OWNER||'.'||TABLE_NAME||' to XXTEST;' from dba_tab_privs where GRANTEE='XXTEST' ;
 ===============================================

    DATA GUARD Handy:

************************************************************************************************************************************************************
primay :   SELECT MESSAGE FROM V$DATAGUARD_STATUS;


SELECT to_char(CURRENT_SCN) FROM V$DATABASE;

SQL> select name, open_mode, database_role from v$database;

select max(sequence#) from v$log_history;

Check redo received and applied on standby:
==========================================

SELECT SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG;
SELECT SEQUENCE#, APPLIED ,COMPLETION_TIME ,STAMP FROM V$ARCHIVED_LOG where COMPLETION_TIME like '%06-JUL-18%';

SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG  WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG
GROUP BY THREAD#) ORDER BY 1;

select process,status,sequence# from v$managed_standby;

Identify the missing archive log file.

 SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;


 SQL> select thread#, max (sequence#) from v$archived_log where APPLIED='YES' group by thread#;
 THREAD# MAX(SEQUENCE#)

———- ————–

         1             22

         2             14

         3             12
 ************************************************************************************************************************************************************

 Crosscheck this :
   
 
  Check redo received and applied on standby.
 
  SELECT ARCH.THREAD#  , ARCH.SEQUENCE#  , APPL.SEQUENCE#  , (ARCH.SEQUENCE# – APPL.SEQUENCE#)  FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN
(SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN
(SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;



Identify the missing archive log file.

 SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

col NAME for a70
select SEQUENCE#,THREAD#,STATUS,APPLIED,NAME from v$archived_log where APPLIED='YES' order by NAME;

col NAME for a70
select SEQUENCE#,THREAD#,STATUS,APPLIED,NAME from v$archived_log where APPLIED='NO';

col NAME for a70
select SEQUENCE#,THREAD#,STATUS,APPLIED,NAME from v$archived_log where APPLIED='YES' AND SEQUENCE# BETWEEN 80886 AND 81999;
use first query

 =========================
  set head off
set echo off
set feedback off
SELECT (ARCH.SEQUENCE# - APPL.SEQUENCE#) difference
FROM
(SELECT DISTINCT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH
,
(SELECT DISTINCT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;

===================  ARCH Difference query================================

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received",
APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN
(SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN
(SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;
==========================================
errors

select status , error from v$archive_dest where des_id=2;

select process,client_process,status,thread#,sequence#,block#,blocks from v$managed_standby;


SQL> select process,client_process,status,thread#,sequence#,block#,blocks from v$managed_standby;

PROCESS   CLIENT_P STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
--------- -------- ------------ ---------- ---------- ---------- ----------
ARCH      ARCH     CONNECTED             0          0          0          0
ARCH      ARCH     CONNECTED             0          0          0          0
ARCH      ARCH     CONNECTED             0          0          0          0
ARCH      ARCH     CONNECTED             0          0          0          0
MRP0      N/A      WAIT_FOR_LOG          1     169866          0          0
RFS       LGWR     RECEIVING             1     169990     242070          8
RFS       ARCH     IDLE                  0          0          0          0


SELECT SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG where APPLIED='NO' ;



alter database recover managed standby database cancel

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE disconnect from session
alter database recover managed standby database disconnect from session;

============  REAL TIME APPLY =======

select DEST_ID,dest_name,status,type,srl,recovery_mode from v$archive_dest_status where dest_id=1;

  select database_role , open_mode from v$database;

DATABASE_ROLE
----------------
PHYSICAL STANDBY

SQL> select name ,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
WFLPRD    READ ONLY WITH APPLY

SQL> select dest_id , recovery_mode from v$archive_dest_status where dest_id=1;

   DEST_ID RECOVERY_MODE
---------- -----------------------
         1 MANAGED




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