Re: How to count record selects?

TimC wrote:
> Hello all & thanks for looking,
>
> I was wondering if there's any way (for Oracle 9i2) to count the number of
> times a record is selected from the db?
>
> I have a bunch of static data tables from which records are retrieved by a
> web service, and I would like the database to handle hit counts at the db
> level so I don't have to code anything in the retrieval service.
>
> Any suggestions?
>
> TIA,
> Tim
>

Just thinking out loud. Hmm. I wonder if I could force a feature used for something entirely different into this scenario. I can establish security policy procedures for each table if I want to implement a VPD (Virtual Private Database - aka Fine-Grained Access Control - aka Row-Level Security).

Okay, I attach my security policy to each table for which I want to monitor hits ... but instead of adding a predicate to the where clause, I simply increment a counter in my session context ... something like:

   hit_count := to_number(sys_context('hit_context','mytable')) + 1;    dbms_session.set_context('hit_context','mytable',to_char(hit_count));

To find out how many hits occurred, I could:

   select sys_context('hit_context','mytable') as mytable_count from dual;

Like I said, I'm just thinking out loud here. Don't know if it's a "good" solution or not. Might put an undue load on the system. But the VPD I have on my production database doesn't seem to put too much load on queries. Hmm. Received on Thu Dec 19 2002 - 15:22:35 CST