Re: Trace access to one specific table

There is an un(der)-documented feature in v9 that monitors the way that columns are used in predicates. The information is updated in table sys.col_usage$, identifying the column by object number, and internal column position The content is maintained at intervals by smon, so can be a little out of date; but it lists number of predicates which were for:

    equality checks
    equijoins
    non-equijoins
    range predicates
    like predicates
    null predicates
so you can get a good idea of how the table is accessed, and how frequently.

This may help.

-- 
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html


Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____UK___November


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


"Frank Foss" <fozzie_beer_at_hotmail.com> wrote in message
news:bql9h4$23avd3$1_at_ID-190416.news.uni-berlin.de...


> Greetings

> I am running Oracle Ent.Ed with partitioning 9.2.0.4 on HP-UX 11.00

>

> I was wondering if it is possible to trace all SQL statements from

multiple


> sessions that touches a specific table in a specific schema?

>

> The reason why I want this is that I am looking at converting about half a

> dozen tables to Index-Organized tables, and I would like to know the

access


> pattern for these tables.

> Armed with this knowledge, I could weigh pro/con of conversion, or perhaps

> have the programmers alter the code to conform with column order or

> something to make the most of the IOTs.

>

> It would be nice if I didn't have to do full traces of all sessions, that

> would take a long time to sift thru, but I will if that is the only way.

>

> Best regards,

> Frank Foss

> Junior DBA

>

>