Monitoring of use of tables ...

[erthal]

Friends,

I have a production DW, and in it some tables are not being more accessed. How do I know what these tables are?

With Index I can ask Oracle that monitors and through the View v $ object_usage see which indexes that were not used. But how to do this with tables?

Hugs,
Gustavo.

[pauloaleo]

As this question is 2004, do you use Oracle? 7?

[erthal]

Paul,

You are mistaken, the question is from 08/11/2006.

My registration is that it was done in 2004.

Hugs,

[erthal]

Paulo,

Complementing, the bank is 10gr2.

Hugs,

[pauloaleo]

Have you put the tables in monitoring then?

[erthal]

Paul,

This type of detail is only used in the collection of statistics.

What I need is a way to find out if a table is suffering charge or some kind of access by the user.

Hugs,

[victorhugomuniz]

Would not that help you?

Select all

 
select *  
from v$sql sql, 
     v$session se 
where sql.sql_id = se.sql_id 

If you find out what the name of your DW process can filter for it and then in sql_text know the name of the table

[pauloaleo]

So why monitoring.

Indixes and monitoring tables update their use in the dictionary and can be viewed by V $ object_usage.

follows an example:



After a period, role query:

1]]

See if IF The Products_PK Index is Being Used.

Select all

select distinct PROD_ID from SH.PRODUCTS; 
 
Table..........................Index..........................Monitoring Used 
------------------------------ ------------------------------ ---------- ---- 
PRODUCTS...................... PRODUCTS_PK....................YES........YES 
.............................. PRODUCTS_PROD_CAT_IX.......... YES........NO 
.............................. PRODUCTS_PROD_STATUS_BIX...... YES........NO 
.............................. PRODUCTS_PROD_SUBCAT_IX........YES.

has a column called Used, which in case of non-use, is like no.

Hugs

[erthal]

My noble, and if for example a table does not have any kind of index?

This solution will not work.

In addition, the index needs to be updated so that a record "exists" in the V $ object_usage.

The problem persists.

Hugs,

[pauloaleo]

Updated not, used.

As for no index tables I would use the DBA_HIST_SEG_STAT view to check the physical Reads variation. Or Physical Writes. If they had variation in a period, it is because the table is used.

Hugs