What is the difference between Access Logging and Query Loging? I know access logging can be enabled by running Dip script and query logging can be enabled by specifying "Begin Logging" on target object.But, what is the significacne of each of the two options? When should we use access logging and when should we use query logging?
I think the short answer would be, Access Logging is concerned with security (ie. who's doing what ?) where as Query Logging (DBQL) is an aid for debugging (ie what's happening around ?). Where as there are quite a bit of things in common between these two, they tend to differ in the details.
For example, in access logging, you ask the database to log who's doing what on a given object (here object is the ..hmm.... subject... ? :-) ), the information stored is based on the object... not the SQL fired or the user who fired it... So you get to know who was doing what on Accounts Table.
Where as in DBQL, you track the SQLs / Resource/spool usage, steps, and other things which help you understand what's going on, the information that's useful to debug an issue etc. That's the reason it's enabled on a User id rather than an object like say Table or so. The information is grouped based on User & SQL fired. This is what you would use to figure out what the new goofy online application is firing against the database that's taking it a long time to process a report.
This does not mean that DBQL has no use for security. There are scenarios, let's say you need to give some one special access to a power userid for some adhoc "patch work" for a short while, but you want to keep a tab of what this person is doing with the id. That's one place Access Logging can't be of much help. DBQL can be used here to generate a report of what the person did with the "power" he was granted ...
The dip script is for installing access logging macro (a dummy macro). you can start/stop access logging via SQL command (BEGIN LOGGING ...) just like you do with DBQL (BEGIN QUERY LOGGING ...).