Re: Accessed Columns and Tables

anthonywmhui_at_hotmail.com wrote in message <7l9sa4$9h7$1_at_nnrp1.deja.com>...
>A had thousands of SQL that access hundreds of tables in my data
>warehouse. How can I know which column or table is being accessed. Any
>tool available to analyze the SQL and produre this kind of
>information? Or does ORACLE provide any tool/way to collect this kind
>of information? Any other Suggestions?

Interesting problem. There's no real solution to this ito of scanning the database. You can scan the V$SESSION and V$SQLAREA tables continuously for SQL statements. But this in itself is a can of worms. How many times per minutes do you need to scan to record every single queries against the database?. Many of the queries will ran for some time causing you to record the same query multiple times. What do you do with parallel query processes? What size overhead that this scanning place on the database? How do you parse the SQL statements and extract the column and table names? What does this tell you besides what is accessed? What if you also need other stats such as the user, the front-end tool used, the elapsed time of the query? Etc. etc.

The only solution to this is to install special middleware. A kind of SQL gateway cum governor. The front-end tools connect to the middleware. In turn the middleware connects to Oracle and run the queries on behalf of the user. Query completes and it returns the data to the user. And all the time the middleware software logs who is doing what, the SQL statements, the elapsed time for a query, the amount of data returned. It can also play policeman - it can only allow some user access to certain tables and columns. It can limit the amount of data extracted from the warehouse. Etc. etc.

There are middleware/governors like this available. I think HP was trying to sell us one when I was working on a warehouse (which explains why I know of it! :-). But how well these work and whether their costs are justified, I have no idea.

regards,
Billy Received on Wed Jun 30 1999 - 02:26:29 CDT