0

We are using Oracle 19c Database. I would like to know below things. Please help me to get below details.

1)How many transactions performed in my database per day?

2)How many transactions performed on a particular table?

3)How many read / write/ read write transactions performed on a table?

4)Has a specific package been invoked or not?

5)Does the table access directly or view or through a package procedure or function?

2 Answers 2

2

1)How many transactions performed in my database per day?

  1. If you have performance tuning pack, you can get it from AWR: for example for last 24 hours:
with
 awr_stats as (
    select 
       snap_id
      ,sn.begin_interval_time
      ,sn.end_interval_time
      ,st.stat_id
      ,st.stat_name
      ,st.value-lag(st.value) over(partition by stat_id order by snap_id) as delta
    from 
       dba_hist_snapshot sn
       join dba_hist_sysstat st 
            using (snap_id,dbid,instance_number)
    where
     st.stat_name in (
         'user commits'
        ,'transaction rollbacks'
    )
    and end_interval_time >= sysdate - interval '1' day
 )
select *
from awr_stats
where delta is not null
order by snap_id,stat_name
;

2)How many transactions performed on a particular table?

3)How many read / write/ read write transactions performed on a table?

2-3. There is no such statistics by default. If you really need that you need to learn how to use logminer for that. But maybe there are some segment statistics that maybe interesting for you. You can check them like this:

with 
 segs as (
    select
       sn.begin_interval_time
      ,sn.end_interval_time
      ,o.owner
      ,o.object_type
      ,o.object_name
      ,o.data_object_id
      ,ss.*
    from dba_hist_snapshot sn
    join dba_hist_seg_stat ss 
         on  sn.snap_id         = ss.snap_id 
         and sn.dbid            = ss.DBID
         and sn.instance_number = ss.instance_number
    join dba_objects o
         on o.data_object_id=ss.dataobj#
--         and o.object_id=ss.obj#
    where 1=1
--    and end_interval_time > systimestamp - interval '10' day
    and owner!='SYS'
 )
select * 
from segs
order by snap_id,obj#,dataobj#
/

4)Has a specific package been invoked or not?

  1. Sounds a bit strange. Don't know what exactly you want to get and why... You can get some statistics about loaded objects into the library cache, like 'loads','pins','executions' from (g)v$db_object_cache:
select * 
from v$db_object_cache c 
where 1=1
and namespace = 'TABLE/PROCEDURE'
and type = 'PACKAGE'
/

But still I'm not sure what exactly do you need and why...

5)Does the table access directly or view or through a package procedure or function?

Again, you haven't provided enough details... Probably you need object dependencies (dba/all/user_dependencies) or PL/SCOPE: https://docs.oracle.com/database/121/ADFNS/adfns_plscope.htm

0
  1. Not sure about this one, as "transaction" can mean a lot of things depending on the context: application-level, system-level, number of commits or number of rows affected (generally not the same), volume of redo logs generated (arguably the most practical measure for a DBA), etc.

2-3. At the table level, this is best determined through auditing, which you must configure yourself. This is not configured "out-of-the-box".

4-5. This is best determined through instrumentation you write into your code yourself. Oracle will not track package executions by default, and auditing will only track execution at the package level, not the individual procedure or function level within the package.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.