Skip to Main Content
  • Questions

  • Help in tracking down a rogue update statement

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, ANURAG.

Asked: September 24, 2011 - 11:35 am UTC

Last updated: October 01, 2011 - 5:12 am UTC

Version: 10gR2

Viewed 1000+ times

You Asked

We have an application with two tables (let's call them ORDER and ORDER_H). There is an AFTER INSERT OR UPDATE trigger on ORDER which inserts history records into ORDER_H.

Recently we found that the entire ORDER table is getting updated by some SQL. Columns like UPDATED_BY, UPDATED_ON, ORDER_DATE etc have exactly the same value for all records in the ORDER table, and there are corresponding history entries in the ORDER_H table. The ORDER table gets inserted / updated hundreds of times every day, and this rogue operation happens once every couple of days. There is no specific time of day etc, so it does not look like a batch job is involved.

Our application developers are doing the conventional checks - trying to reproduce the problem in a test environment, code reviews etc, but no luck yet.

I checked v$sql every day to find the statement responsible:
  select * 
    from v$sql
   where lower(sql_text) like '%update % order %' ;

All the SQLs I got were like
 UPDATE ORDER SET <columns> WHERE <primary key> = :B1 ;


Perfect, exactly how it should be.

I further checked the ROWS_PROCESSED and EXECUTIONS columns of v$sql. Both had the same value for each row, supporting my conclusion that each SQL was updating just one row, as it should.

While the application team debugs the issue, I want to see if it is possible to detect that an SQL is updating multiple rows in a table, and if so I want to throw an error. That way we will at least zero down on the operation causing the issue.

a) Is it even possible for an SQL to not be available in v$sql, when "older" sqls are?
b) Is there any way to do a "select count(*) from" the OLD and NEW pseudotables inside a statement trigger (sounds dumb, I know, but I couldn't think of anything else)
c) Can't use SQL%ROWCOUNT because this table gets updated from multiple places within the server code. I'm looking for a database-specific solution.

Any suggestions? Thanks Tom.

and Tom said...

a) yes

b) they are not tables, you'd have to have a row trigger to count how many times the trigger has fired. a before trigger would reset the counter. an after trigger can then take action on the count.



You could use DBMS_FGA to audit the sql performed against this table. That would give you insight into all of the SQL.

You could use the resource manager to limit the amount of UNDO a given transaction was able to generate - that would catch the transaction that does the big update.


I've a feeling it might not be a rogue update updating all of the rows, but rather a rogue bit of code that gets caught in a loop that updates the entire table slow by slow - if you haven't been able to "see" the sql.

Rating

  (8 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

ANURAG BAGRI, September 24, 2011 - 8:07 pm UTC

Will read up on DBMS_FGA, thanks!

Regarding the trigger - please confirm if we need to take care of the scenario in which two simultaneous updates on ORDER are issued by two sessions... Will Oracle ensure that the statement and row level triggers for the first update are invoked before the triggers fire for the second update?

"Rogue loop" is definitely possible. The ORDERS table has about 200K rows, so it should stick out like a sore thumb if I run my v$sql query say once every hour through a job. Right now, the max value of EXECUTES is 13k, but that could be because the SQL has aged out?

Regarding my question (a), is it possible for an SQL to age out of the shared pool if "older" SQLs (V$SQL.FIRST_LOAD_TIME) are present?


Tom Kyte
September 25, 2011 - 11:43 am UTC

PLSQL has its own state for each session. If you increment a variable in session1, session2 will have its OWN variable state and will not see that increment.


Regarding my question (a), is it possible for an SQL to age out of the shared
pool if "older" SQLs (V$SQL.FIRST_LOAD_TIME) are present?

yes, if it is not being used, hasn't been used for a while - it would be subject to aging - and the "newer" sql might well be being used right then and there and would be more "popular"..


Issue resolved

ANURAG BAGRI, September 28, 2011 - 4:18 pm UTC

One of our developers found a stored procedure that had an update on ORDER without a WHERE clause. Fixed and rolled out to Production, hopefully that will be it.

v$sql is not as reliable as I had thought... and I could not find the culprit stored proc by querying DBA_SOURCE either.

Thanks anyway. I've been following this site for almost eight years now, keep up the good work!
Tom Kyte
September 28, 2011 - 4:54 pm UTC

v$sql is 100% reliable. It shows you what is currently in there.

And it would be hard to query DBA_SOURCE to find a "missing" where clause. It would be easy to find all procedures that reference that table using dba_dependencies (barring dynamic sql that is), but to discover a missing where clause - it would be fairly hard.

V$SQL will ONLY list the SQLs

A reader, September 28, 2011 - 9:58 pm UTC


Anurag,

V$SQL will list the SQLs executed in the database (if it has not been aged out). Now that you mention the procedure was the culprit, search the procedure name in V$SQL and I am sure you will get hits. V$SQL does not capture the SQLs (DML/DDL)running within packages/procedures/functions etc.

Tom Kyte
September 29, 2011 - 7:01 am UTC

yes it does.


ops$tkyte%ORA11GR2> create or replace package my_pkg
  2  as
  3          procedure p;
  4  end;
  5  /

Package created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create or replace package body my_pkg
  2  as
  3  
  4  procedure p
  5  is
  6  begin
  7          for x in (select * from dual LOOK_AT_ME)
  8          loop
  9                  null;
 10          end loop;
 11  end;
 12  
 13  end;
 14  /

Package body created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select * from v$sql where sql_text like '%LOOK_AT_ME';

no rows selected

ops$tkyte%ORA11GR2> exec my_pkg.p

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> select * from v$sql where sql_text like '%LOOK_AT_ME';

SQL_TEXT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_FULLTEXT                                                                     SQL_ID        SHARABLE_MEM PERSISTENT_MEM RUNTIME_MEM      SORTS LOADED_VERSIONS OPEN_VERSIONS USERS_OPENING    FETCHES EXECUTIONS PX_SERVERS_EXECUTIONS END_OF_FETCH_COUNT USERS_EXECUTING      LOADS FIRST_LOAD_TIME     INVALIDATIONS PARSE_CALLS DISK_READS DIRECT_WRITES BUFFER_GETS APPLICATION_WAIT_TIME CONCURRENCY_WAIT_TIME CLUSTER_WAIT_TIME USER_IO_WAIT_TIME PLSQL_EXEC_TIME JAVA_EXEC_TIME ROWS_PROCESSED COMMAND_TYPE OPTIMIZER_ OPTIMIZER_COST OPTIMIZER_ENV                                                                                                                                                                                                                                                   OPTIMIZER_ENV_HASH_VALUE PARSING_USER_ID PARSING_SCHEMA_ID PARSING_SCHEMA_NAME            KEPT_VERSIONS ADDRESS  TYPE_CHK HASH_VALUE OLD_HASH_VALUE PLAN_HASH_VALUE CHILD_NUMBER
-------------------------------------------------------------------------------- ------------- ------------ -------------- ----------- ---------- --------------- ------------- ------------- ---------- ---------- --------------------- ------------------ --------------- ---------- ------------------- ------------- ----------- ---------- ------------- ----------- --------------------- --------------------- ----------------- ----------------- --------------- -------------- -------------- ------------ ---------- -------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------ --------------- ----------------- ------------------------------ ------------- -------- -------- ---------- -------------- --------------- ------------
SERVICE                                                          SERVICE_HASH MODULE                                                           MODULE_HASH ACTION                                                           ACTION_HASH SERIALIZABLE_ABORTS OUTLINE_CATEGORY                                                   CPU_TIME ELAPSED_TIME OUTLINE_SID CHILD_AD    SQLTYPE R OBJECT_STATUS       LITERAL_HASH_VALUE LAST_LOAD_TIME      I I I I CHILD_LATCH SQL_PROFILE                                                      SQL_PATCH                      SQL_PLAN_BASELINE              PROGRAM_ID PROGRAM_LINE# EXACT_MATCHING_SIGNATURE FORCE_MATCHING_SIGNATURE LAST_ACTI BIND_DATA                                                                                                                                                                                                                                                       TYPECHECK_MEM IO_CELL_OFFLOAD_ELIGIBLE_BYTES IO_INTERCONNECT_BYTES
---------------------------------------------------------------- ------------ ---------------------------------------------------------------- ----------- ---------------------------------------------------------------- ----------- ------------------- ---------------------------------------------------------------- ---------- ------------ ----------- -------- ---------- - ------------------- ------------------ ------------------- - - - - ----------- ---------------------------------------------------------------- ------------------------------ ------------------------------ ---------- ------------- ------------------------ ------------------------ --------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------- ------------------------------ ---------------------
PHYSICAL_READ_REQUESTS PHYSICAL_READ_BYTES PHYSICAL_WRITE_REQUESTS PHYSICAL_WRITE_BYTES OPTIMIZED_PHY_READ_REQUESTS LOCKED_TOTAL PINNED_TOTAL IO_CELL_UNCOMPRESSED_BYTES IO_CELL_OFFLOAD_RETURNED_BYTES
---------------------- ------------------- ----------------------- -------------------- --------------------------- ------------ ------------ -------------------------- ------------------------------
SELECT * FROM DUAL LOOK_AT_ME
SELECT * FROM DUAL LOOK_AT_ME                                                    cgp9s95pyfg7q        10294           2848        2276          0               1             1             1          1          1                     0                  1               0          1 2011-09-29/07:59:54             0           1          2             0           5                     0                     0                 0              1778               0              0              1            3 ALL_ROWS                2 E289FB89E226A8003D011000AEF9C3E2CFFA331056414555519521105555551545545558591555449665851D5511058555555155515122555415A0EA0C5551454265455454449081566E001696C6A35545150102541550441669899BA002141008000008000000400000000400008000000820401F00000000100000800C000                315853415             274               274 OPS$TKYTE                                  0 21698ED0 00       1810316534     2107188093       272002086            0
SYS$USERS                                                                   0 SQL*Plus                                                          -625018272                                                                            0                   0                                                                        1000         3693             20E2C94C          6 N VALID                                0 2011-09-29/07:59:54 N N N Y           0                                                                                                                                     97783             7               1.0590E+19               1.0590E+19 29-SEP-11                                                                                                                                                                                                                                                                             0                              0                 16384
                     2               16384                       0                    0                           0            1            2                          0                              0



v$sql has all sql that is currently parsed in the shared pool. If you execute a sql statement and then don't use it for a while - you will often find it "not in the shared pool" anymore due to aging out and the need for its space to be used by some other query.

DML SQL is easily trackable from V$SQL

Wayne Phillips, September 29, 2011 - 2:10 am UTC 

Anurag

SQL submitted from within PL/SQL is indeed trackable in V$SQL - contrary to what you may have assumed.

Quote: "V$SQL does not capture the SQLs (DML/DDL)running within packages/procedures/functions"

Here is a simple test case:

08:58:09 boa-waynep@pdev> create table my_dml_tab(seq_num number, string_value varchar2(20), ins_date date default sysdate);

Table created.

Elapsed: 00:00:00.23
09:01:26 boa-waynep@pdev> select sql_id, child_number, executions, program_id, program_line#, substr(sql_text ,1,80) sql_piece
09:01:36   2    from v$sql where upper(sql_text) like upper('%source=my_dml_proc%')
09:01:36   3     and sql_text not like '%from v$sql where upper(sql_text)%';

no rows selected

Elapsed: 00:00:00.34
09:01:37 boa-waynep@pdev> CREATE OR REPLACE PROCEDURE my_dml_proc (p_seq_num IN number)
09:01:44   2  IS
09:01:44   3  BEGIN
09:01:44   4       insert /*+  source=my_dml_proc */ into my_dml_tab (seq_num, string_value,ins_date )
09:01:44   5     values (p_seq_num, 'MY_DML_PROC',sysdate);
09:01:44   6
09:01:44   7     commit;
09:01:44   8  END;
09:01:44   9  /

Procedure created.

Elapsed: 00:00:00.73
09:01:45 boa-waynep@pdev> execute my_dml_proc(1);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.26
09:01:52 boa-waynep@pdev> execute my_dml_proc(2);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.02
09:01:52 boa-waynep@pdev> execute my_dml_proc(3);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.02
09:01:53 boa-waynep@pdev> select sql_id, child_number, executions, program_id, program_line#, substr(sql_text ,1,80) sql_piece
09:02:06   2    from v$sql where upper(sql_text) like upper('%source=my_dml_proc%')
09:02:06   3     and sql_text not like '%from v$sql where upper(sql_text)%';

SQL_ID        CHILD_NUMBER EXECUTIONS PROGRAM_ID PROGRAM_LINE# SQL_PIECE
------------- ------------ ---------- ---------- ------------- --------------------------------------------------------------------------------
c2bh6wfpsahvm            0          3   16919671             4 INSERT /*+  source=my_dml_proc */ INTO MY_DML_TAB (SEQ_NUM, STRING_VALUE,INS_DAT

1 row selected.

09:08:49 boa-waynep@pdev> select owner, object_name, object_type from dba_objects where object_id=16919671;

OWNER             OBJECT_NAME                    OBJECT_TYPE
----------------- ------------------------------ -------------------
WAYNEP            MY_DML_PROC                    PROCEDURE

1 row selected.

ANURAG BAGRI, September 29, 2011 - 3:49 pm UTC

Well, the issue has been resolved, but this discussion can help others, so:

1. Not saying v$sql isn't reliable in the sense that some SQLs can bypass it (direct path reads can, but that's not relevant here). I've used v$sql many times to track down issues, this is the first time that the SQL has aged out of the cache so quickly. I checked every day for a week - during which time the rogue SP ran three times - and did not see this SQL even once. (There is no "flush shared pool for better performance" job running either)

2. One characteristic of the rogue SQL was that the MODON column (MODified ON) just had the date value. Usually MODON columns have the full date and time, i.e. SYSDATE. So I queried DBA_SOURCE to see if there was any stored proc / trigger / package body that had the text UPDATE... SO_SERVICEORDER ... SET ... MODON ... TRUNC... SYSDATE:

select * 
  from dba_source
 where lower(text) like '%update %so_serviceorder %set %modon%trunc%sysdate%' ;


Result, nothing. Ok, maybe the update statement is on multiple "lines" - tried different variations of:

select prev.*,next.line,next.text
  from dba_source prev, dba_source next
 where lower(prev.text) like '%update%so_serviceorder%'
   and lower(next.text) like '%trunc%sysdate%' 
   and prev.owner = next.owner
   and prev.name = next.name
   and prev.type = next.type
   and next.line - prev.line between 1 and 15  ;


Still nothing.

So my two favorite Oracle "magic debugging" tools couldn't provide the answers, and I was feeling kind of lost :). My next step would have been to get all the SPs from our config mgmt repository and grep for SO_ServiceOrder, but by that time one of the developers found the issue.

Still not sure why this happened. I'll certainly update the thread if I find the reason. Meanwhile, if anything else comes to mind, please share.
Tom Kyte
September 30, 2011 - 6:03 pm UTC

1) direct path reads cannot bypass v$sql. I don't even know what that would mean??


a query that uses direct path reads will be in v$sql like any other query would be.



2) and most programmers would code on many lines...

You should learn about lag and lead :)

ops$tkyte%ORA11GR2> create or replace procedure p
  2  as
  3  begin
  4          update
  5          t
  6          set
  7          username
  8          =
  9          5;
 10  end;
 11  /

Procedure created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select owner, name, line
  2    from (
  3  select owner, name, line,
  4         lag(text,3) over (partition by owner, name, type order by line) ||
  5         lag(text,2) over (partition by owner, name, type order by line) ||
  6         lag(text,1) over (partition by owner, name, type order by line) ||
  7             text ||
  8         lead(text,1) over (partition by owner, name, type order by line) ||
  9         lead(text,2) over (partition by owner, name, type order by line) ||
 10         lead(text,3) over (partition by owner, name, type order by line) txt
 11    from dba_source
 12   where owner = 'OPS$TKYTE'
 13         )
 14   where txt like '%update%t%set%username%=%'
 15  /

OWNER                          NAME                                 LINE
------------------------------ ------------------------------ ----------
OPS$TKYTE                      P                                       5
OPS$TKYTE                      P                                       6
OPS$TKYTE                      P                                       7

If SQL is aged out, why not to try AUDITing ?

J. Laurindo Chiappa, September 30, 2011 - 12:58 pm UTC

If the SQL is being aged so quickly, did you tried, as suggested by Tom, AUDIT the SQLs , probably via FGA ? This is very effective, while V$SQL never promised to get all the SQLs, SQL AUDIT by definition does it...
Another option could be TRACE the SQLs - with this option again, by definition no SQL could be lost...

Regards,

Chiappa

ANURAG BAGRI, September 30, 2011 - 8:37 pm UTC

@Tom

1) Sorry, I intended to write "direct path write". insert /*+ append */, sqlldr etc.

2) Analytics do rock, wow. I've seen some examples here and even tried to work with it a bit, but I never ever thought of applying it to this issue. Now let me try and parse what you did there... thanks so much.

@Chiappa

This is a production system, it is not practical to trace each and every session's SQLs. DBMS_FGA looks promising, and I'll definitely read up about it for future use.
Tom Kyte
October 01, 2011 - 5:12 am UTC

1) you would still be wrong. that is just sql. it is in v$sql.

Got it

ANURAG BAGRI, September 30, 2011 - 9:24 pm UTC

Obvious, of course, now that you've shown us how it is done :)

LAG(3), LAG(2), LAG(1) get the text from the previous three lines. These are concatenated with the current line, and LEAD(1..3) get the text from the next three lines. PARTITON BY is analogous to GROUP BY, and ORDER BY is, well, ORDER BY. Both are important to get the correct behavior.

If the text you want to search for is widely dispersed, add as many LAG()s and LEAD()s as you want - this query concatenates four to seven lines (the first three lines of the procedure will not have three LAGs and last three will not have three LEADs).

Performance is very good.

General technique, useful for "flattening" any result set. Very nice.

Most of our developers like to write sql code in CAPS, ugh, so I replaced txt with lower(txt) in the where clause. Worked like a charm on the original problem.


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Security

All of the vital components for a secure database are covered in the Security guide.