Re: regular expression and v$sql question

From: Saibabu Devabhaktuni <saibabu_d_at_yahoo.com>
Date: Wed, 6 Jul 2011 01:19:31 -0700 (PDT)
Message-ID: <1309940371.37450.YahooMailRC_at_web65906.mail.ac4.yahoo.com>

If I understand your question correctly, you would like to get the list of all DML sql statements for a given table and then track their performance metrics across awr snapshots.

Assuming that there are not many new sql statements being introduced often, first thing you need to do is to get the mapping between sql_id and the table names. Here are few options to do the sql mapping:

1. Join v$sql with v$object_dependency (this will work very well for no join sql statements, you probably need to eliminate tables doing the select query within a DML by excluding their object_names from v$sql_plan versus v$object_dependency)
2. Join v$active_session_history and v$logmnr_contents by XID column to map sql_id from ash with seg_name from logminer. This requires you to use logminer on redologs.
3. If you can afford to, create statement level triggers on all tables and as part of the trigger code insert current sql into the global logging table. You can get the current sql (or prev_sql_id) in trigger context by running a query against v$session where sid=sys_context('USERENV', 'SID') and audsid=sys_context('USERENV', 'SESSIONID')'.
4. You can also use fine grained auditing.

I do not recommend using your own parser to fetch table names out of sql statements, unless you know your application really well.

For tracking the performance metrics across awr snapshots:

1. No sql performance metrics are double counted by Oracle. There will be a new entry in v$sql for every child cursor that is in use, all metrics reflect usage of that particular child cursor. Awr simply rolls them up by sql_id and plan_hash_value when it takes the snapshot. If a cursor gets aged out from the shared pool, awr will not have any visibility into that. If a cursor gets invalidated due to DDL, new child cursor is spawned (old child cursor will be purged) and all the performance metrics for the old cursor will be gone from v$sql; again awr will not have full visibility into it.
2. You can query dba_hist_sqlstat for executions_delta, buffer_gets_delta, etc... by sql_id, snap_id, instance_number, plan_hash_value..

Hope it helps.

Thanks,
 Sai
http://sai-oracle.blogspot.com

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jul 06 2011 - 03:19:31 CDT