Home » RDBMS Server » Performance Tuning » Determining UPDATE statements from DBA_HIST_SQL_PLAN (10.2.0.4)
Determining UPDATE statements from DBA_HIST_SQL_PLAN [message #439888] Tue, 19 January 2010 21:46 Go to next message
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 #439892 is a reply to message #439888] Tue, 19 January 2010 22:15 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>But the problem here is that in our database, there are 2 types of update statements
I disagree.

SELECT ... FOR UPDATE does not change any data.
Only UPDATE <table_name> actually changes data.

You should not count SELECT operations

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

[Updated on: Tue, 19 January 2010 22:16]

Report message to a moderator

Re: Determining UPDATE statements from DBA_HIST_SQL_PLAN [message #439996 is a reply to message #439892] Wed, 20 January 2010 08:57 Go to previous messageGo to next message
kamran.irshad
Messages: 6
Registered: January 2010
Location: MA
Junior Member

Hi,

Thanks for the reply
So basically what is a "select for update" statement

we do see a lot of these in our application and will apprciate if someone can shed some light on these statements as to what their purpose is if they are not actually UPDATE statements
Re: Determining UPDATE statements from DBA_HIST_SQL_PLAN [message #440001 is a reply to message #439996] Wed, 20 January 2010 09:17 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>So basically what is a "select for update" statement
It used to "reserve" data that the application code expects to change (UPDATE) in the near future somewhat later in the code stream.
It prevents other sessions from changing the SELECTed data until this session issues COMMIT or ROLLBACK.


It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

[Updated on: Wed, 20 January 2010 09:18]

Report message to a moderator

Re: Determining UPDATE statements from DBA_HIST_SQL_PLAN [message #440098 is a reply to message #440001] Thu, 21 January 2010 01:23 Go to previous message
John Watson
Messages: 8961
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.
Previous Topic: "os thread startup" in top 5 Events
Next Topic: Table Access by ROWID vs UNIQUE INDEX SCAN
Goto Forum:
  


Current Time: Tue Dec 10 02:23:07 CST 2024