Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: May 15, 2017 - 7:32 pm UTC

Last updated: May 19, 2017 - 10:43 pm UTC

Version: 11.2

Viewed 1000+ times

You Asked

Hello,

I have a need to find out "active" tables in a 11.2 (and 12.1) database. Active means - a table that has been involved in any of the DML or SELECT statement.

I could use v$sql to get the full text of the statement and figure out. Besides this, any other way to get this list? Auditing is not an option since it is not a common practice across all our databases (we have in 1000's).

Thanks,

and Connor said...

Check out DBA_TAB_MODIFICATIONS

It's not updated in real time, but gives good numbers. Note that when you gather statistics, the details are cleared, so you'd need a means to occasionally dump the information to a table.

You could also make some inferences from v$segment_statistics

SQL> select distinct  STATISTIC_NAME from v$segment_statistics
  2  order by 1;

STATISTIC_NAME
----------------------------------------------------------------
IM non local db block changes
IM populate CUs
IM prepopulate CUs
IM repopulate (trickle) CUs
IM repopulate CUs
IM scans
ITL waits
buffer busy waits
db block changes
gc buffer busy
gc cr blocks received
gc current blocks received
gc remote grants
logical reads
optimized physical reads
optimized physical writes
physical read requests
physical reads
physical reads direct
physical write requests
physical writes
physical writes direct
row lock waits
segment scans
space allocated
space used


'db block changes' is a reasonable indicator that a change to the object has been made.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Follow up..

A reader, May 15, 2017 - 7:42 pm UTC

Thank you. Correct me if I am wrong, DBA_TAB_MODIFICATIONS apparently doesn't list out the tables that are being just SELECTed. It probably gives the list that have undergone DML.

For SELECTed tables, any different approach?

Thanks,

Connor McDonald
May 19, 2017 - 10:43 pm UTC

That's a much more soft definition, because we dont really select tables, we run queries.

For example:

SQL> set autotrace traceonly explain
SQL> select count(*)
  2  from scott.emp e,
  3       scott.dept d
  4  where e.deptno = d.deptno;

Execution Plan
----------------------------------------------------------
Plan hash value: 2083865914

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     3 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| EMP  |    14 |    42 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("E"."DEPTNO" IS NOT NULL)

SQL> set autotrace off


The *query* references DEPT but we never actually touched DEPT. Does this count ?

If you want to see where tables are *referenced* then you could mine the sql text in v$sqlstats and make some guesses. You could also mine the object_name in v$sqlplan (but there is still the caveat above).

Similarly, v$segment_statistics can give some rough guides....

Joseph Charpak, May 16, 2017 - 1:52 pm UTC

Isn't this what Heat Maps are for?
Connor McDonald
May 17, 2017 - 3:34 pm UTC

Yes, but you also need an Advanced Compression license for that
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.