Home » RDBMS Server » Performance Tuning » Finding INSERT statments between 2 AWR snapshots (10.2.0.4)
Finding INSERT statments between 2 AWR snapshots [message #440372] Sat, 23 January 2010 00:04 Go to next message
kamran.irshad
Messages: 6
Registered: January 2010
Location: MA
Junior Member

We have a requirement of (on a daily basis) gathering the information of all the 'INSERT' statements between 2 given AWR snapshots

The output should be like following:


Object Owner Object Name count(*)
========================================
APPS1 TABLE-A 55
APPS1 TABLE-B 32
APPS2 TABLE-Z 20

We tried using the DBA_HIST_SQL_PLAN alongwith DBA_HIST_SQLSTAT having a WHERE clause of --> where operation='INSERT'
but it did not work because DBA_HIST_SQL_PLAN is recording and storing these INSERTS as OPERATION='TABLE ACCESS' and option='BY INDEX ROWID'.

Anyways, We used the following SQL to gather the INSERT information

select p.sql_id,count(*)
from
dba_hist_sqltext p,
dba_hist_sqlstat s
where
s.snap_id between &bgn_id and &end_id
and
p.sql_id = s.sql_id
and
p.command_type=2
group by p.sql_id
order by 2 desc
/

This gave us the following

SQL_ID COUNT(*)
------------- ----------
350myuyx0t1d6 24
bunssq950snhf 24
2prbzh4qfms7u 23
........

So this means that between 2 given AWR snapshots,above are the SQL ID's of all the INSERT statements alongwith the number of
times those were executed between those snapshots

Now, what we are trying to do is to add the object_name and object_owner to this report, i.e. the output should look like

SQL_ID COUNT(*) OBJECT_OWNER OBJECT_NAME
================================================================

350myuyx0t1d6 24 APPS TABLE-A
bunssq950snhf 24 APPS TABLE-B
2prbzh4qfms7u 23 APPS2 TABLE-Z
........


Can someone please show us as to how to accomplish this?

Re: Finding INSERT statments between 2 AWR snapshots [message #440374 is a reply to message #440372] Sat, 23 January 2010 00:32 Go to previous message
kamran.irshad
Messages: 6
Registered: January 2010
Location: MA
Junior Member


Also, here is another observation made by us

select sql_id,substr(sql_text,1,30) from v$sql where command_type=2 and parsing_schema_name not in ('SYS','SYSTEM','SYSAUX','SYSMAN')


SQL_ID SUBSTR(SQL_TEXT,1,30)
------------- ------------------------------
g38vf6bfch1qn INSERT INTO SHIPMENT_NOTES (PR
82f29w3wvh3sb insert into "SHIPMENT_DATE_NOT
g1dc134gvw41b INSERT INTO PROCESS_QUEUE (PRO
9z1vvahcvs60y INSERT INTO PROCESS_PROPERTY (
9z1vvahcvs60y INSERT INTO PROCESS_PROPERTY (
...... 60 rows returned ....

but there is no such record of these SQL in v$sql_plan....

select sql_id,object_owner,object_name,operation,options from v$sql_plan where sql_id in
(
select sql_id from v$sql where command_type=2 and parsing_schema_name not in ('SYS','SYSTEM','SYSAUX','SYSMAN')
)


no rows selected


Can anyone explain as to why is this?
Previous Topic: Query With View Vs Select of the View behave differently
Next Topic: Use of double parallel hint
Goto Forum:
  


Current Time: Wed Dec 07 14:46:02 CST 2016

Total time taken to generate the page: 0.08226 seconds