| Finding INSERT statments between 2 AWR snapshots [message #440372] |
Sat, 23 January 2010 00:04  |
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  |
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?
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
