Oracle Transaction History to check growth

  
How To Query Transaction Count in Oracle
 

SELECT BEGIN_TIME,
END_TIME,
TXNCOUNT,
( (END_TIME - BEGIN_TIME) * 24 * 60 * 60) DIFFSECOND,
TO_CHAR ( (TXNCOUNT / ( (END_TIME - BEGIN_TIME) * 24 * 60 * 60)),
'999999.99')
TRANXPERSECOND
FROM V$UNDOSTAT
WHERE     BEGIN_TIME >= TO_DATE('20.09.2014 00:00','DD.MM.YYYY HH24:MI')
AND BEGIN_TIME <= TO_DATE('25.09.2014 00:00','DD.MM.YYYY HH24:MI')
AND (TXNCOUNT / ( (END_TIME - BEGIN_TIME) * 24 * 60 * 60)) > 50
ORDER BY 1;
 


-- Transaction per hour 
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS'; 
set pages 9999;
column c1 heading "Start|Time" format a30;
column c2 heading "End|Time" format a15;
column c3 heading "Total|Undo|Blocks|Used" format 9,999,999;
column c4 heading "Total|Number of|Transactions|Executed" format 999,999;
column c5 heading "Longest|Query|(sec)" format 999,999;
column c6 heading "Highest|Concurrent|Transaction|Count" format 9,999;

select
TO_CHAR( TO_DATE(TO_CHAR(Begin_Time,'DD-MON-YY HH24'),'DD-MON-YY HH24') , 'DD-MON-YY HH24') c1,
-- TO_CHAR(End_Time,'DD-MON-YY HH24') c2, 
SUM(Undoblks) c3, SUM(Txncount) c4, MAX(Maxquerylen) c5,
MAX(Maxconcurrency) c6
from v$undostat
group by TO_CHAR( TO_DATE(TO_CHAR(Begin_Time,'DD-MON-YY HH24'),
'DD-MON-YY HH24') , 'DD-MON-YY HH24')
order by 1 asc
;



alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS'; 
select BEGIN_TIME,END_TIME,TXNCOUNT ,(( END_TIME - BEGIN_TIME ) * 24 * 60 * 60 ) diffsecond , 
to_char(( TXNCOUNT / (( END_TIME - BEGIN_TIME ) * 24 * 60 * 60 )),'999999.99') tranxpersecond from v$undostat
where BEGIN_TIME >='24-APR-2010 05:35:40' and BEGIN_TIME <='29-APR-2010 16:35:40' 
and ( TXNCOUNT / (( END_TIME - BEGIN_TIME ) * 24 * 60 * 60 )) > 50 order by 1; 








 
Average transaction count 

 
SELECT V1          "Total Commits",
       V2          "Total Rollbacks",
       V3          "Total User Calls",
       T1          "Uptime in days",
       S1 / T1     "Avg Daily DML Transactions",
       V3 / T1     "Avg Daily User Calls"
  FROM (SELECT VALUE     V1
          FROM V$SYSSTAT
         WHERE NAME = 'user commits'),
       (SELECT VALUE     V2
          FROM V$SYSSTAT
         WHERE NAME = 'user rollbacks'),
       (SELECT SUM (VALUE)     S1
          FROM V$SYSSTAT
         WHERE NAME IN ('user commits', 'user rollbacks')),
       (SELECT VALUE     V3
          FROM V$SYSSTAT
         WHERE NAME = 'user calls'),
       (SELECT SYSDATE - STARTUP_TIME T1 FROM V$INSTANCE);




Daily average commit count
 

SELECT (V1 + V2) / T1 "Avg Daily DML Transactions",
    V1 "User Commit",
    V2 "User Rollback"
  FROM (SELECT VALUE V1
          FROM V$SYSSTAT
         WHERE NAME = 'user commits'),
       (SELECT VALUE V2
          FROM V$SYSSTAT
         WHERE NAME = 'user rollbacks'),
       (SELECT SYSDATE - STARTUP_TIME T1 FROM V$INSTANCE);



Transaction Count in the last 24 hours
 
SELECT 'DATABASE',
       'TOPLAM TRANSACTION - 24 SAAT',
       TO_CHAR (MAX (NEXT_CHANGE#) - MIN (FIRST_CHANGE#), '9,999,999,999') VALUE
  FROM V$LOG_HISTORY
 WHERE TO_DATE (FIRST_TIME, 'DD/MM/RR HH24:MI:SS') > TRUNC (SYSDATE, 'HH24') - 1;
 




Using Metric : 


DBA_HIST_SYSMETRIC_HISTORY
DBA_HIST_SYSMETRIC_SUMMARY

select * from v$SYSMETRIC;
select * from V$SYSMETRIC_HISTORY; -- interval,  1hr history 
select * from V$SYSMETRIC_SUMMARY; --1hr interval, 1hr history

select min(  to_char(begin_time,'DD-MON-YYYY HH24:MI:SS') ) from DBA_HIST_SYSMETRIC_HISTORY;

col value format 9999999999999999999999999999999999999999
col metric_name  format a50
col metric_unit format a40
select metric_name , sum(value) , metric_unit from v$sysmetric_history   group by metric_name ,metric_unit ;

select metric_name , sum(value) , metric_unit from v$sysmetric_history  where metric_name like '%Block Changes Per Sec%'  group by metric_name ,metric_unit    ;

select to_char(begin_time,'DD-MON-YYYY HH24') ,metric_name , sum(value) , metric_unit from DBA_HIST_SYSMETRIC_HISTORY where metric_name like '%Transaction%' 
group by  to_char(begin_time,'DD-MON-YYYY HH24') , metric_name ,metric_unit 
order by  to_char(begin_time,'DD-MON-YYYY HH24') ; 

select to_char(begin_time,'DD-MON-YYYY HH24') ,metric_name , sum(value) , metric_unit from DBA_HIST_SYSMETRIC_HISTORY where metric_name like  '%Executions%' 
group by  to_char(begin_time,'DD-MON-YYYY HH24') , metric_name ,metric_unit 
order by  to_char(begin_time,'DD-MON-YYYY HH24') ; 

 


Comments

Post a Comment

Popular posts from this blog

How To Purge Optimizer Statistics Advisor Old Records From 12.2 Onwards (Doc ID 2660128.1)

  In this Document Goal Solution References APPLIES TO: Oracle Database - Enterprise Edition - Version 12.2.0.1 and later Oracle Cloud Infrastructure - Database Service - Version N/A and later Information in this document applies to any platform. GOAL Goal of this document is to provide the method to purge the old records of Optimizer Statistics Advisor Task namely AUTO_STATS_ADVISOR_TASK that consumes huge SYSAUX space. Huge no.of old records retained in WRI$_ADV_OBJECTS for AUTO_STATS_ADVISOR_TASK or INDIVIDUAL_STATS_ADVISOR_TASK incurs heavy SYSAUX space. AUTO_STATS_ADVISOR_TASK is meant for Automatic Statistics Advisor task while INDIVIDUAL_STATS_ADVISOR_TASK is for Manual Statistics Advisor task.  DBA_ADVISOR_PARAMETERS displays all advisor task parameters and their current values in the database. There is a parameter name called EXECUTION_DAYS_TO_EXPIRE. This parameter is set in no.of days. Executions older than the value(no.of days) set would be purged automaticall...
Read more

Oracle session snapper

-- use dbms_session.sleep instead of dbms_lock on oracle 18c or higher -- last column  is sid  -- scipt_name   stat_to_collect   secods_of_snap  how_many_snap   sessions_to_snap -- @snapper all 100 1 lgdr@* -- @snapper all 100 1 sqlid@* -- @snapper all 190 1 all -- @snapper all 190 1 584 -- @snapper all 30 3 584 -- @snapper all 5 1 869 -- @snapper all 30 1 584 -- @snapper ash 5 1 144 -- @snapper ash=sql_id+event+wait_class+blocking_session+p2+p3 5 1 144 -- @snapper ash 5 1 156 -- @snapper ash 190  1 all -- @snapper ash 5 1 all -- @snapper ash=sid+event+wait_class 5 1 all -- @snapper ash=sid+event+wait_class,ash1=plsql_object_id+plsql_subprogram_id+sql_id 5 1 all -- @snapper ash=sid+event+wait_class,ash1=plsql_object_id+plsql_subprogram_id+sql_id,ash2=program+module+action 5 1 all -- @snapper ash=event+wait_class,stats,gather=ts,tinclude=CPU,sinclude=parse 5 1 all -- @snapper ash=sid+event+wait_class,ash1=sid+sqlid+module,stats,gather...
Read more

Oracle Materialized View In-Depth and Materialized View refresh issues in 19c

   We    had  situation demanding  tuning of  MV  refresh and  options  like  TUNE_MVIEW  and Atomic_Refresh was explored . Hence  Documenting materialized view sample commands  and views in this  blog .     After migration if we are  facing slowness  in  Materialized View refresh , we can explore to re-create  Materialized View   Since  main topic for this blog   was MV issues  in 19c ,  posting that topic   first in line below  We also   have Real-Time Materialized Views  to explore .  To Note :  1) We can  create Mv  on ON PREBUILT TABLE  2) We can create Index on Mv  3) We can  gather stats on mv  just like other table .  4) Mv can be REFRESH_MODE ON COMMIT / REFRESH ON DEMAND. 5) Mv  REFRESH_METHOD can be  Complete / FAST or FORCE . A FORCE attempts a FAST...
Read more