Re: regular expression and v$sql question
- From: Saibabu Devabhaktuni <saibabu_d@xxxxxxxxx>
- To: free <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 6 Jul 2011 01:19:31 -0700 (PDT)
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
Other related posts:
