| Determining UPDATE statements from DBA_HIST_SQL_PLAN [message #439888] |
Tue, 19 January 2010 21:46  |
kamran.irshad
Messages: 6
Registered: January 2010
Location: MA
|
Junior Member |
|
|
Hi,
we are trying to find out how many UPDATE statements are executed in our database on hourly basis (between 2 AWR snapshots) using the following SQL
select
p.sql_id,
p.object_name,
p.operation,
p.options,
count(1)
from
dba_hist_sql_plan p,
dba_hist_sqlstat s
where
s.snap_id between &bgn_id and &end_id
and
p.object_owner not in ('SYS','SYSTEM','SYSAUX')
and
p.operation='UPDATE'
and
p.sql_id = s.sql_id
group by
p.sql_id,
p.object_name,
p.operation,
p.options
order by
2,3,4;
But the problem here is that in our database, there are 2 types of update statements
type # 1. --> Update <table name> set .......
type # 2. --> select col1,col2 from <table name> for update ..............
Our query is only reporting the UPDATE of type#1 because DBA_HIST_SQL_PLAN stores the type#2 update statment as "SELECT" (under column 'OPERATION') instead of storing it as "UPDATE"
How can we tweak the above SQL so that it gives us the details of not only type# 1 update statement but also type# 2 update statement as well.
Can you please submit the revised query that would accomplish the task we are looking for?
Thanks
|
|
|
|
|
|
|
|
|
|
| Re: Determining UPDATE statements from DBA_HIST_SQL_PLAN [message #440098 is a reply to message #440001] |
Thu, 21 January 2010 01:23  |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
I would go further, and say that SELECT...FOR UPDATE is often bad coding. Rows may be locked for some time, for no purpose: bad for concurrency, and can cause a whole application to lock up. I've even seen it used for read consistency, because programmers didn't know about flashback queries or read-only transactions. I would investigate these statements, and see if locking the rows is really necessary.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
