Computing column access statistics in MySQL and / or Oracle

Question

We're currently questioning our company's data model (database schemes), which has grown wildly and distributedly over the years and now suffers from redundancy and lacks clarity. Different databanks (Oracle and MySQL) are queried by different tools, developed by separate teams for different purposes (billing, reporting, business, etc).

In order to get a clearer picture of the data dependencies of the different tools, i.e. which exact pieces of information does tool T need, one way would be to identify them directly from the SQL queries that each databank executes. Alternatively, it would be much more interesting to have the databank collect these statistics itself.

Examples of such statistics would be

• how often is column C from table T accessed
• how often are table T1 and T2 joined together

Does anyone know how to do this with MySQL or Oracle or both?

Answer 1

MySQL has a general query log. Instructions for how to enable the log and read the log are part of the MySQL reference manual.

The MySQL general query log is a list of SQL connects, statements, and disconnects.

You would have to process the MySQL general query log to get the statistics you want.

The Oracle Business Intelligence Server has a query log. I couldn't find anything about general Oracle databases having a query log.

Answer 2

Oracle has a SYS.col_usage$ table which has statistics on how individual columns are referenced in WHERE clauses. Unfortunately there is no record of the relationship between columns. Also no record of a column is just read (like in a WHERE clause).

The EQUALITY_PREDS columns are for references in a where clause like: COL = 123 The EQUIJOIN_PREDS are for joins like A.COL = B.COL There are also counters for NONEQUIJOIN_PREDS, RANGE_PREDS, LIKE_PREDS, NULL_PREDS.

The information is collected and made available to the system while statistics are being generated.

An example script is here: link text