Skip to Main Content
  • Questions
  • How can I track the execution of PL/SQL and SQL?

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Andre.

Asked: September 14, 2000 - 9:24 am UTC

Last updated: November 09, 2021 - 3:49 am UTC

Version: 8.1.6

Viewed 100K+ times! This question is

You Asked

Hi Tom,

How can I know what a given user is executing if his status is ACTIVE ? How can I know which PL/SQL blocks or SQL statements are being run by him ? As to SQL statemets, I can join v$session.user# with v$sqlarea.parsing_user_id ( am I really right ??? ), but I am not sure about PL/SQL blocks. How can I track the execution of those objects and queries (something like V$transaction for entire trasactions) ?

Regards.

and Tom said...

I use the script at the bottom. It shows everyone logged in and if they are active, what they are doing and how long they've been doing it.

If someone is executing PLSQL, what you will see will depend on what the plsql is currently doing. If the plsql is doing SQL, you'll see the SQL. if the plsql is doing lots of PLSQL work -- you'll see that code. What I like to do is have everyone "instrument" their code with calls to dbms_application_info which can fill in the client_info, action, and module columns in v$session. In this fashion, you can see where in a procedure someone is based on the values in these columns. showsql exposes this information to you as well. sqlplus uses it to show you what script someone is running for example...

---------------- showsql.sql --------------------------
column status format a10
set feedback off
set serveroutput on

select username, sid, serial#, process, status
from v$session
where username is not null
/

column username format a20
column sql_text format a55 word_wrapped

set serveroutput on size 1000000
declare
x number;
begin
for x in
( select username||'('||sid||','||serial#||
') ospid = ' || process ||
' program = ' || program username,
to_char(LOGON_TIME,' Day HH24:MI') logon_time,
to_char(sysdate,' Day HH24:MI') current_time,
sql_address, LAST_CALL_ET
from v$session
where status = 'ACTIVE'
and rawtohex(sql_address) <> '00'
and username is not null order by last_call_et )
loop
for y in ( select max(decode(piece,0,sql_text,null)) ||
max(decode(piece,1,sql_text,null)) ||
max(decode(piece,2,sql_text,null)) ||
max(decode(piece,3,sql_text,null))
sql_text
from v$sqltext_with_newlines
where address = x.sql_address
and piece < 4)
loop
if ( y.sql_text not like '%listener.get_cmd%' and
y.sql_text not like '%RAWTOHEX(SQL_ADDRESS)%')
then
dbms_output.put_line( '--------------------' );
dbms_output.put_line( x.username );
dbms_output.put_line( x.logon_time || ' ' ||
x.current_time||
' last et = ' ||
x.LAST_CALL_ET);
dbms_output.put_line(
substr( y.sql_text, 1, 250 ) );
end if;
end loop;
end loop;
end;
/

column username format a15 word_wrapped
column module format a15 word_wrapped
column action format a15 word_wrapped
column client_info format a30 word_wrapped

select username||'('||sid||','||serial#||')' username,
module,
action,
client_info
from v$session
where module||action||client_info is not null;

Rating

  (118 ratings)

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

Comments

thanks

Olga, July 25, 2001 - 6:01 pm UTC

The more I work with Oracle the more new things to learn I find ;)
Thank you for this script

How to register

Anant, May 14, 2002 - 4:25 pm UTC

Tom,
You Say
""instrument" their code with calls to dbms_application_info "

Can you please explain how to do it? Suppose I have a code which SELECTs from two different tables ( One table T1 has 177 million records and T2 has 5000 records) and then INSERTs into another (3rd) table. It is been running for 12 hours and I want to know what is happening? Will I run out of Sort area size or TEMP space ( The Select is with 3-4 Group By clauses). I had run it about 2 months back and it completed in 3 hours. The data has mot increased much but I don't know if the DBA has changed some parameters since then. We have a 13 GB SGA and 80 GB TEMP tablespace. Can I predict with some certainty how much more time will it take to insert the records. I am using Oracle 8.1.7.
Thanks for your help.

Tom Kyte
May 16, 2002 - 11:49 am UTC

Hopefully it is a single insert into select

Look at v$session_longops. You'll find info in there regarding where it is in the processing.

How to register

Anant, May 14, 2002 - 4:26 pm UTC

Tom,
You Say
""instrument" their code with calls to dbms_application_info "

Can you please explain how to do it? Suppose I have a code which SELECTs from two different tables ( One table T1 has 177 million records and T2 has 5000 records) and then INSERTs into another (3rd) table. It is been running for 12 hours and I want to know what is happening? Will I run out of Sort area size or TEMP space ( The Select is with 3-4 Group By clauses). I had run it about 2 months back and it completed in 3 hours. The data has mot increased much but I don't know if the DBA has changed some parameters since then. We have a 13 GB SGA and 80 GB TEMP tablespace. Can I predict with some certainty how much more time will it take to insert the records. I am using Oracle 8.1.7.
Thanks for your help.

Thanks

Anant, May 16, 2002 - 2:17 pm UTC

Thanks Tom I read it in your book - very nicely explained.

Thanks

Change plan

A reader, May 16, 2002 - 6:23 pm UTC

If a SQL is executing and we do certain changes which will impact the performance of this SQL. Then is there anyway we can force the executed SQL to read these changes. Ofcourse without killing that session and re-running it.

T1 -> SQL joining A, B & C executed. Because of unavailable stats on A this SQL will execute for 1hr.
T2 -> Updated stats on table A. This will improve the performance from 1hr to 2mins.
T3 -> Can I anyway force the SQL executed at time T1 to read the changes made at time T2.

Thanks

Tom Kyte
May 17, 2002 - 7:44 am UTC

No, think about it -- how could it? say you changed it from using an index to using a full scan (via the stats). It couldn't just start full scanning as it would undoubtably re-read rows it had already processed.

You need to restart it.

Can't Compile

Ted Persky, November 14, 2002 - 6:46 pm UTC

Tom:

Thanks for posting the script. For some reason, however, I cannot get it to compile. Keep getting 'PLS-00103 Encountered the symbol "/"' at line forty, which is the forward slash following the two "end loops" and the "end" statement. Can you please clarify what I'm doing wrong? Thanks in advance.

Tom Kyte
November 14, 2002 - 7:52 pm UTC

make sure you don't have a blank in front of that slash:

ops$tkyte@ORA920.LOCALHOST> begin
  2    dbms_output.put_line( 'hello' );
  3  end;
  4   /
  5  /
 /
 *
ERROR at line 4:
ORA-06550: line 4, column 2:
PLS-00103: Encountered the symbol "/" The symbol "/" was ignored.



sounds like you do (from the cut and paste maybe) 

You're Incredible!

Ted Persky, November 15, 2002 - 2:00 pm UTC

Tom:

That did it. Thanks for an amazing turnaround time, even in the midst of OracleWorld. Just amazing!

Best wishes,

Ted P.

Very useful, but I have a question...

Colin Davies, November 15, 2002 - 3:41 pm UTC

What eactly is the column LAST_CALL_ET? It's just a number, but I would like to know what the number means. The Oracle docs just say "LAST_CALL_ET NUMBER The last call"


Tom Kyte
November 15, 2002 - 8:19 pm UTC

If a session is currently ACTIVE, this is the number of seconds the statement it is executing has been ACTIVE.

If a session is currently INACTIVE, this is how long its been inactive.

If timed statistics is TRUE, this number is very accurate.
If timed statistics is not enabled, this number is +/- many seconds.

Very Useful Query

Stanley, January 16, 2003 - 7:47 pm UTC

Its a good piece of code.

How to find whether any specific package is being executed currently

kumar, March 06, 2003 - 1:00 pm UTC

Tom,

I have a different situation , I need to run some key purging process, in order to do that I need to find and process whether one specific package is being executed or not? Well this package is being called through another auto-job. Can you hint me? or suggest me an easy SQL to find the specific pacakge or procedure is being executed currently or not?

Thanks

Tom Kyte
March 06, 2003 - 3:45 pm UTC

see dba_ddl_locks, might have to run catblock.sql in $ORACLE_HOME/rdbms/admin in order to have that view.

dba_ddl_locks entries remained till the session closed

kumar, March 06, 2003 - 5:16 pm UTC

Tom,

Thanks and an Excellent clue you have given to me, I tried to use that view on 9iR2 and one interesting thing I found is, If I execute a procedure through SQLPLUS then I query the below SQL:-

Select a.username, b.ower||'.'||b.name
from v$session a, dba_ddl_locks b
where a.sid=b.session_id
and b.type like '%/Procedure%/'.

This has given the exact information and shown that procedures name etc. But when the procedure execution completed successfully, latter also I query the above query, but it was returing the same Procedure entries untill I close the SQLPLUS session. Once if I close SQLPLUS then if I query, then only I get the results without that recently executed procedure. My need here is I need to get the exact information whether the procedure is executing or not executing. Any suggetions

Tom Kyte
March 06, 2003 - 6:47 pm UTC

You can add code to the package such that it:

o sets MODULE using dbms_application_info whenever you enter it and unsets it upon exit. then select * from v$session where module = 'XXXX' will tell you.

o or uses dbms_lock to create a user defined lock upon entry, releases it upon exit.

either way will do it.


but now that I think about it -- dbms_lock is BEST. the package could get the lock in shared mode (many people run the package) but the process that cannot have someone running the lock would get it in EXCLUSIVE mode -- that would prevent anyone from starting the package whilst this process was running.

very useful, but here comes just another idea to test ...

J. Laurindo Chiappa, March 07, 2003 - 8:46 am UTC

Another possible idea : the following script was adapted from the original idea & research from Steve Adams (www.ixora.com.au), I tested in Personal 8ir3 and it worked as desired :

step a) in a quiet database, run the script :

sys@PO8IR3:SQL>@v8_running_sqls
**** Currently Executing Packages ****

no rows selected

sys@PO8IR3:SQL>get v8_running_sqls
  1  prompt **** Currently Executing Packages ****
  2  SELECT
  3    substr(DECODE(o.kglobtyp,
  4      7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE', 12, 'TRIGGER', 13,
  5  'CLASS'),1,15)  "TYPE",
  6    substr(o.kglnaown,1,30)  "OWNER",
  7    substr(o.kglnaobj,1,30)  "NAME",
  8    s.indx  "SID",
  9    s.ksuseser  "SERIAL"
 10  FROM
 11    sys.X$KGLOB  o,
 12    sys.X$KGLPN  p,
 13    sys.X$KSUSE  s
 14  WHERE
 15    o.inst_id = USERENV('Instance') AND
 16    p.inst_id = USERENV('Instance') AND
 17    s.inst_id = USERENV('Instance') AND
 18    o.kglhdpmd = 2 AND
 19    o.kglobtyp IN (7, 8, 9, 12, 13) AND
 20    p.kglpnhdl = o.kglhdadr AND
 21    s.addr = p.kglpnses
 22* ORDER BY 1, 2, 3
 
step b) in another session (not showing) I'm running a procedure who call DBMS_LOCK.SLEEP, and with the other session running the proc :

sys@PO8IR3:SQL>@v8_running_sqls
 **** Currently Executing Packages ****
 
 TYPE      OWNER  NAME         SID     SERIAL
 -------   ------ ------------ --- ----------
 PACKAGE   SYS    DBMS_LOCK     12       3807
 PROCEDURE SCOTT  PROC_RUN      12       3807
 

step c) after the execution (BUT with the other session still conected/alive, just idle) :

 sys@PO8IR3:SQL>@v8_running_sqls
 **** Currently Executing Packages ****
 
no rows selected

Regards,

 Chiappa 

Sys.x$ tables how to grant select if I need to use by another user

Kumar, March 07, 2003 - 11:48 am UTC

Chippa's invention is mind blowing, however if I need to run that query from another user ( who is not sys), perhaps I can not get the select grants on those sys.X$LOB tables. I tried to grant after login as sys but I got an error saying that "ORA-02030: can only select from fixed tables/views". Any work around to query these views from non sys user.

Thanks
Kumar

Tom Kyte
March 07, 2003 - 1:26 pm UTC

sorry -- x$ tables are magical and I give no help in that area at all.

YOU want to use dbms_lock, you NEED to use dbms_lock. just knowing that at this instant -- when you run that query -- no one is using the package, that is not useful to you -- as the instant after you run that query the answer will change (and you should not run your process)

You NEED to use a serialization device.

Excellent warning - I will complete the just for the record

J. Laurindo Chiappa, March 07, 2003 - 2:19 pm UTC

Tom, I agree 101% - X$ are "magical", they CAN and WILL change anytime, without warning, and worse, NO READ-CONSISTENCY here - so, in a very busy db, chances are of X$ being "late", not refreshed, or alike.

SO, really, IF the asker wants to be always sure, he will need serialize (to assure consistency), and make the needed alters in the code to "instrument".

BUT, just for the record : the common way of "grant" X$ things to another user is : as SYS, make a view of the desired x$ and give grant of the view (as developed by Stave Adams in 
http://www.ixora.com.au/scripts/sql/create_xviews.sql
) :

sys@PO8IR3:SQL>create view X_$BH as select * from X$BH;

View created.

sys@PO8IR3:SQL>grant select on X_$BH to scott;

Grant succeeded.

-- connected as the "common" user ...

scott@PO8IR3:SQL>select file#, ts#, obj from sys.x_$bh where rownum < 3;

     FILE#        TS#        OBJ
---------- ---------- ----------
         1          0        110
         1          0         10

OF COURSE, do it at your own risk, no guarantees (I use it to "peek" program executions on development databases, and so on).

 Regards,
 
  Chiappa
   

A reader, August 15, 2003 - 4:51 pm UTC


Track the Sql

Sri, August 23, 2003 - 8:00 am UTC

Dear Tom,

1. I have a third party application that is giving problem. It connects to oracle (8.1.5) using odbc. When I click on a particular button it is giving, "ora-00942: table or view does not exist". Unfortunately, we don't have support for this application now. How to find out what sql it is trying to execute when I click that button. If I can find it, I may be able to replace the table that it is not able to find.

2. Could you please explain regarding the status column in v$session. When does this become active. In documentation, it is given Active means 'Currently Executing SQL'. Does that mean that the status will be active from the time I start executing the sql, till the time it finishes fetching the results? If so, I tried the following: - I logged in to sql*plus with my username. Gave 'select * from all_objects;'. As the sql was executing, I opened another sql*plus window and logged in as system and queried v$session. But still the status of my user is shown as 'INACTIVE' though I am sure that the sql was still executing. Why is this so? Kindly clarify.

Thanks a lot.

Sri


Tom Kyte
August 23, 2003 - 10:31 am UTC

1) you can either use sys.dbms_system to set sql_trace in your session from another session or for this user create a login database trigger that issues:

execute immediate 'alter session set sql_trace=true';


note that the owner of that trigger MUST have alter session granted directly to it, not via a role, for that trigger to be successful.


2) status will be active when your client sends a request to the server. if you open a query -- status will be active during the parse phase. when the query is opened, you'll be inactive again. fetch a row -- active, then not active. and so on.

your test case just means when you queried v$session, sqlplus was in the processing of printing some of the rows it fetched, it was not fetching at that point. if you just kept hitting "/" to run that v$session query over and over you would see your session go active/inactive/active/inactive and so on.

Not what I needed, but not bad

Evan, October 23, 2003 - 11:00 pm UTC

Tom,

I was hoping that the showsql script would show the current sql command so that I could follow scripts as they execute. Even so, it is a great script!!!

Using "shotgun programing" type selects, I narrrowed it down to one SQL statement in a script which goes into never never land.

explain plan shows a cost of about 750 (low). Running your showsql.sql script shows me that it is there (which I get from OEM). OEM shows a steady increase in phys/log reads, so it is doing something.

What can I do to find out WHAT it is doing?

Is there a script to see the current SQL running so that I can use a batch capture rather than OEM to see the SQL?

I am looking for something on the order of:
user SQL
-------- ------------------------
Joeuser select blah,blah
from tableX where boolean
and soforth

Thanks

Evan

Tom Kyte
October 24, 2003 - 9:31 am UTC

umm, that is what showsql does -- it shows the active sessions and the sql they are running.

i'm very confused? it seems to do exactly what you say you want it to do?

Last executed SQL

houman, November 12, 2003 - 3:32 pm UTC


Tom,

Can I have my last executed SQL retrieved from the database?
just by passing the sid. I want those SQL executed in my application not the onse executed by Oracle itself

Tom Kyte
November 12, 2003 - 5:01 pm UTC

you'll get the last sql executed by joining v$session to v$sql. if the last sql executed was recursive SYS sql -- you'll get that (that would be the last sql). we really don't discriminate between them

Houman, November 13, 2003 - 3:40 pm UTC

Tom,

I have a debugging mechanism in my application (c++) where I can dump the SQL statements sent to the database into a file at the client side. Now I want to add the same functionality for PL/SQL packages meaning that I want to dump the queries ran in the cursors and basically all other SQL statement run in the package body into the same file at client side. I want this happen at the same time or before each query is executed in the database.

In my application, c++ part, I have a query class that all queries have to go through that class before be sent to the database therefore it is easy to grab the SQL statement and dump it to the debug file. However in PL/SQL, I don't know where to find the SQL statement being executed in the same order that were sent to the database in a particular session. I know that Oracle somehow keeps track of queries related to a session in a sequential order because this is the way you generate trace file when SQL_TRACE is set to TRUE. Retrieving the current and previous sql statement from V$session joined with v$SQL does not solve my problem either because, when run in the same session, these two SQL always point to the same SELECT statement that is currently reading from V$session not the one before. Consider the below procedure:



CREATE OR REPLACE PROCEDURE FOO AS
CURSOR C1 IS SELECT OBJECT_NAME FROM USER_OBJECTS;
V_USERNAME USER_OBJECTS.OBJECT_NAME%TYPE;
V_DUMMY DUAL.DUMMY%TYPE;
c_STR VARCHAR2(512);
BEGIN

OPEN C1;
C_STR := GET_CURRENT_SQL(MY_SID); -- GET CURRENT SQL STATEMENT FROM ORACLE

DUMP_CURRENT_SQL(C_STR,FILE_NAME); -- DUMP IT TO THE FILE

LOOP
FETCH C1 INTO V_USERNAME;
EXIT WHEN C1%NOTFOUND;
SELECT DUMMY INTO V_DUMMY FROM DUAL;
C_STR := GET_CURRENT_SQL(MY_SID); -- GET CURRENT SQL STATEMENT FROM ORACLE

DUMP_CURRENT_SQL(C_STR,FILE_NAME) -- DUMP IT TO THE FILE
.
.
.
END LOOP;
CLOSE C1;
END;
/


After the execution of the above procedure, I should have the following in the debug file providing that USER_OBJECTS has only 8 records:


. Debug file FILE_NAME

.

SELECT OBJECT_NAME FROM USER_OBJECTS
SELECT DUMMY INTO :a FROM DUAL
SELECT DUMMY INTO :a FROM DUAL
SELECT DUMMY INTO :a FROM DUAL
SELECT DUMMY INTO :a FROM DUAL
SELECT DUMMY INTO :a FROM DUAL
SELECT DUMMY INTO :a FROM DUAL
SELECT DUMMY INTO :a FROM DUAL

.
.
.

Do you have any suggestions how to do that using SQL information stored in Oracle tables/memory?


Tom Kyte
November 13, 2003 - 9:19 pm UTC

plsql running in the server cannot write to the client.

we invented this capability and gave it many great features. it is called:

alter session set sql_trace=true;

that is what you want to do. it has been done for you already.

How to track down possible causes?

j, March 27, 2004 - 11:04 am UTC

hi tom,

we are facing performance problems with some package procedures/functions which used to run for years without those issues. now statspack summary reports elapsed times per execution that in turn can not be reproduced on purpose by just calling that specific pieces of code.

so in order to identify whether it is a matter of the time those code pieces get executed (work load) and/or of the parameters that are used we think of just tracing every execution of that certain package procedures/functions - regardless of who is calling them.

is there a better way to solve the problem? can such a tracing only be achieved by touching the code to be traced: turning trace on - executing the call - turning trace off?

Tom Kyte
March 27, 2004 - 4:38 pm UTC

trace them -- turn it on, run it, turn it off, analyze output.

use the 10046 level 12 and you'll get wait events and bind variables. You'll see if it is workload related

(low cpu, high elapsed, no wait events would lead me to think "starved for CPU")
(low cpu, high elapsed, lots o'waits -> contention for that resource)
(high cpu -> function does lots of work, tune algorith)

and so on.

j., March 28, 2004 - 5:39 am UTC

... and in order to focus on certain calls the best/only way is to modify that code to turn tracing on and off, right?

please let me ask an additional more specific question:

our statspack reports show a very low parse cpu to parse elapsed percentage (20 to 30) and an event "cpu time" on top of the top 5 timed events (up to 85% of elapsed time are spent for it). furthermore shared pool memory usage is very high (up to 95%). almost up to 3 hard parses per second might indicate some "aging out" issues ...

is it the right conclusion that we should try to:
1: reduce hard parses by increasing shared pool size
2: identify possible causes of "cpu time" wait events and
3: figure out why there is so much time wasted while parsing?

Tom Kyte
March 28, 2004 - 9:13 am UTC

3 hard parses/second with a shared pool that is near full with lots of parsing happening makes me think "no binds" first and foremost.

I'd look there BEFORE even considering upping the shared pool.

j., March 28, 2004 - 12:31 pm UTC

yes, we could double check this. but as mentioned before: the application used to run for years without any "bind issues". most of the processing is done with pl/sql and neither server nor client code has been changed.

as opposed to that several major changes in hard-/software architecture took place in the last few months: 1st - rdbms release (8i -> 9i), 2nd - server hardware and operating system (32 bit -> 64 bit) and last but not least network configuration.

that 's why we just thought of configuration problems first.

but lets assume "bind problems": we 've investigated the shared pool content (using the script provided in your book) and have found out some statements "different" only due to constants instead of bind variables:

10x: SELECT value from sys.v_$mystat where statistic# = ?
7x: SELECT RECORD_SIZE FROM V$CONTROLFILE_RECORD_SECTION WHERE TYPE = ?
5x: select decode(value,'TRUE',1,0) into :b1 from v$option where parameter=?
4x: SELECT "A1"."VALUE" FROM "STATS$SYSSTAT" "A1" WHERE "A1"."NAME"=? AND ...

plus 2 statements in 3 "different" versions
plus 11 statements in 2 "different" versions

our application doesn't issue those statements. so it doesn't look too bad from our point of view.

what would you suggest to further attack our issues?
what 's the most efficient way to identify the root cause(s)?

Tom Kyte
March 28, 2004 - 5:10 pm UTC

how do you know the application ran for years without any bind issues. something changed here (had to). Perhaps what changed is "we are supporting more users" and the bind thing (which kills scalability)......


You see, I do not have as much information as you. All I knew was -- app ran for a while. app appears to be slower now. (something changed, I don't know what)...


and now you dump a ton of *new* info here -- a ton.....


I would trace the application and see what the application is waiting on -- (that was mentioned before :)

avoid statspack for now, go after the application itself.

update and showsql.sql

A reader, April 30, 2004 - 10:00 am UTC

Hi Tom,

I have the following code to update one column of a table and it took a long, long time. So then I tried to find out how bad it is, I only run 10 records (see the commented out part in the where clause). The elapsed time shows: 00:00:06.07. My questions are:

1. The update time is way too long. How to improve this?
2. I used your showsql.sql query during executing this code, and it shows ‘ROLLBACK’ and nothing else. Why?


SQL> @showsql

USERNAME                    SID    SERIAL# PROCESS      STATUS
-------------------- ---------- ---------- ------------ ----------
GGS_ORA                      11         54 14888        INACTIVE
GGS_ORA2                     18       3472 23985        INACTIVE
GGS_ORA2                     34        354 1540:308     ACTIVE
SYSTEM                       41      27970 1360:1624    ACTIVE
--------------------
SYSTEM(41,27970) ospid = 1360:1624 program = sqlplus.exe
Friday    08:41  Friday    08:51 last et = 0
select username||'('||sid||','||serial#||
                ') ospid = ' ||
process ||
                ' program = ' || program username,

to_char(LOGON_TIME,' Day HH24:MI') logon_time,
             to_char(sysdate,'
Day HH24:MI') current_
--------------------
GGS_ORA2(34,354) ospid = 1540:308 program = sqlplus.exe
Friday    08:27  Friday    08:51 last et = 1413
ROLLBACK


declare
    cursor c1 is
    select corp_id, box_num, vendor_type, unit_address
    from   box_inventory;
    /* where vendor_type = 'MT' and unit_address is not null and rownum < 11; */

    rec1       c1%rowtype;
    v_mac_addr varchar2(12);
begin
    open c1;
    loop
        fetch c1 into rec1;
        exit when c1%notfound;
        if ( rec1.unit_address is not null) then
            if ( rec1.vendor_type = 'MT' ) then
                v_mac_addr := lpad( lpad(to_char(to_number(substr(rec1.unit_address, 1, 3)), 'fmXX'), 2, '0') ||
                                    lpad(to_char(to_number(substr(rec1.unit_address, 4, 10)), 'fmXXXXXXXX'), 8, '0'), 12, '0' );
            elsif ( rec1.vendor_type = 'SA' ) then
                v_mac_addr := lpad(substr(rec1.unit_address, 5), 12, '0');
            else
                v_mac_addr := NULL;
            end if;
        else
            v_mac_addr := NULL;
        end if;
        if ( v_mac_addr is not NULL) then
            update box_inventory set mac_address = v_mac_addr
            where corp_id = rec1.corp_id and box_num = rec1.box_num;
        end if;
    end loop;
    close c1;
end;
/

Thanks in advance for your help. 

Tom Kyte
April 30, 2004 - 10:48 am UTC

update box_inventory
set mac_address = decode( vendor_type, 'MT', lpad( lpad( .... ) ),
'SA', lpad( substr( ... ) ) )
where unit_address is not null
and vnedor_type in ( 'MT', 'SA' );


tastes great, less filling - runs super fast too I'll bet.


never write procedural code when there is a simple SQL statement that'll do it.

but if you want to figure out "what is slow" about your code -- sql_trace=true and tkprof are your best friends -- they'll tell you what is "slow"

update and showsql.sql

A reader, April 30, 2004 - 12:10 pm UTC

Tom,

Thanks a lot for the code revision and suggestion. But how about my second question? That is "why the showsql.sql shows 'ROLLBACK' for this update, but nothing else?"

Thanks again.

Tom Kyte
April 30, 2004 - 5:41 pm UTC

you don't have any triggers on that table do you? and those triggers are not autonomous transactions are they?

update and showsql

A reader, April 30, 2004 - 5:56 pm UTC

Tom,

I do have an update trigger on this table. The following is the output from TKPROF. Any suggestions?

********************************************************************************

update box_inventory set mac_address = decode ( vendor_type, 'MT',
lpad( lpad(to_char(to_number(substr(unit_address, 1, 3)), 'fmXX'), 2, '0') ||
lpad(to_char(to_number(substr(unit_address, 4, 10)), 'fmXXXXXXXX'), 8, '0'), 12, '0' ),
'SA', lpad(substr(unit_address, 5), 12, '0') )
where unit_address is not null and vendor_type in ('MT', 'SA') and mac_address is not null

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.42 0.76 1697 4157 24 10
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.42 0.77 1697 4157 24 10

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 31

Rows Row Source Operation
------- ---------------------------------------------------
0 UPDATE (cr=4350 r=1698 w=0 time=5962528 us)
10 TABLE ACCESS FULL BOX_INVENTORY (cr=4157 r=1697 w=0 time=705044 us)

********************************************************************************

INSERT INTO DB_EXEC_LOGS (DB_EXEC_LOG_ID, EXEC_ID, EXEC_NAME, EXEC_DATE, DESCRIPTION)
VALUES(db_exec_log_id_s.nextval, :b3, :b2, SYSDATE, :b1)

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 24 0.02 0.01 1 0 202 24
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 24 0.02 0.01 1 0 202 24

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 31 (recursive depth: 1)
********************************************************************************

COMMIT


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 27 0.01 0.00 0 0 24 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 27 0.01 0.00 0 0 24 0

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 31 (recursive depth: 1)
********************************************************************************

DELETE FROM EQUIP_ACCOUNT
WHERE CORP_ID = :b2
AND SERIAL_NUM = :b1

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 3 0.00 0.00 0 9 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 9 0 0

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 31 (recursive depth: 1)
********************************************************************************

DELETE FROM ACCOUNT_EQUIP_SERVICES
WHERE CORP_ID = :b2
AND SERIAL_NUM = :b1

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 3 0.00 0.00 0 9 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 9 0 0

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 31 (recursive depth: 1)
********************************************************************************

SELECT COUNT(1) FROM dual
WHERE EXISTS ( SELECT 'x' FROM EQUIP_ACCOUNT
WHERE CORP_ID = :b2
AND SERIAL_NUM = :b1)

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 7 0.00 0.00 0 21 0 0
Fetch 7 0.00 0.00 0 21 0 7
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 14 0.00 0.00 0 42 0 7

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 31 (recursive depth: 1)
********************************************************************************

SELECT UNIQUE zip_code
FROM HOUSE_MASTER
WHERE CORP_ID = :b3
AND HOUSE_ID = :b2
AND CUST_NUM = :b1

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 7 0.00 0.00 0 0 0 0
Fetch 7 0.00 0.00 0 28 0 6
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 14 0.00 0.00 0 28 0 6

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 31 (recursive depth: 1)
********************************************************************************

SELECT UNIQUE WIP_CODE
FROM HOUSE_MASTER
WHERE CORP_ID = :b3
AND HOUSE_ID = :b2
AND CUST_NUM = :b1

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 7 0.00 0.00 0 0 0 0
Fetch 7 0.00 0.00 0 28 0 6
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 14 0.00 0.00 0 28 0 6

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 31 (recursive depth: 1)
********************************************************************************

SELECT UNIQUE CUST_PIN
FROM CUSTOMER_MASTER
WHERE CORP_ID = :b3
AND HOUSE_ID = :b2
AND CUST_NUM = :b1

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 7 0.00 0.00 0 0 0 0
Fetch 7 0.00 0.00 0 27 0 6
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 14 0.00 0.00 0 27 0 6

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 31 (recursive depth: 1)
********************************************************************************

UPDATE EQUIP_ACCOUNT
SET zip_code = :b5,
WIP_CODE = :b4,
CUST_PIN = :b3
WHERE CORP_ID = :b2
AND SERIAL_NUM = :b1

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 7 0.00 0.00 0 21 16 7
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 0.00 0.00 0 21 16 7

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 31 (recursive depth: 1)
********************************************************************************

UPDATE EQUIP_ACCOUNT
SET stb_unit_address = :b6,
HOUSE_ID = :b5,
CUST_NUM = :b4,
mac_address = :b3
WHERE CORP_ID = :b2
AND SERIAL_NUM = :b1

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 7 0.00 0.00 0 0 0 0
Execute 7 0.00 0.00 0 21 7 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 14 0.00 0.00 0 21 7 0

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 31 (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
0 UPDATE (cr=0 r=0 w=0 time=0 us)
1 INDEX UNIQUE SCAN EQUIP_ACCOUNT_PK (cr=3 r=0 w=0 time=24 us)(object id 9308)

********************************************************************************

ROLLBACK


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 7 0.00 5.16 0 7 28 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 0.00 5.16 0 7 28 0

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 31 (recursive depth: 1)
********************************************************************************

commit


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 1 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 1 0

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 31
********************************************************************************

alter session set sql_trace = false


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 0

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 31



********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 3 0.00 0.00 0 0 0 0
Execute 4 0.42 0.78 1697 4157 25 10
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 0.42 0.79 1697 4157 25 10

Misses in library cache during parse: 3
Misses in library cache during execute: 1


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 8 0.00 0.00 0 0 0 0
Execute 107 0.03 5.19 1 89 280 32
Fetch 28 0.00 0.00 0 104 0 25
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 143 0.03 5.19 1 193 280 57



Tom Kyte
May 01, 2004 - 9:03 am UTC

I'd be very very very concerned about the commit's and rollbacks in a trigger.

if the trigger is an autonomous transaction -- you would have to work very long and very very hard to convince me it should be (and I doubt you would be successful)

databases are all about transactions -- if you have an autonomous transaction in a trigger, you've probably done it wrong.

update and showsql

A reader, May 01, 2004 - 10:50 pm UTC

Tom,

The following is brief information about our database and why we have to use autonomous transaction in a trigger:

We have several replication databases, which are called R1, R2, R3aa, R3bb, … The R1 has four tables: T1, T2, T3, T4; the R2 has T1, T2, T3, T4, T5, T6, … Tn, the R3 has T5 and T6. All those databases are in different geographic locations.

The data of four tables in R1 database is replicated into R2 with a third party software in real time. The triggers on R2 tables are fired to populate the data into T5 and T6 with more detailed service information retrieved from other R2 tables. The T5 and T6 are in turn replicated into R3aa, R3bb, … databases in real time. The R1 database is maintained by other company and we have no control on it. The third party software data replication is based on Oracle redo log files. Whenever a transaction is committed on R1 database the data is replicated to R2 database. A transaction on R1 could be any size and any order of DMLs on those four tables. This database architecture could introduce some potential problems: deadlock, because in addition to replicate data on R2 and R3, we also need to maintain other massive service data, which could cause DMLs on T5 and T6 tables concurrently with those replication DMLs. No matter how we tune our application code, the deadlock always happens (due to the disordered DMLs applied by the replication software). So finally we decided to use autonomous transaction in triggers to break our transaction down to single DML to avoid deadlock.

We know it is not good programming practice to use autonomous transaction in a trigger, but it seems we have no choice. Any suggestions?

From the previous TKPROF output, I saw the ROLLBACK has total elapsed time of 5.16 seconds. Why rollback takes so long to complete?

Thanks again for you time.

Tom Kyte
May 02, 2004 - 10:07 am UTC

(you know what, I cannot *believe* you didn't mention this when you first stated:

I have the following code to update one column of a table and it took a long,
long time. So then I tried to find out how bad it is, I only run 10 records (see
the commented out part in the where clause). The elapsed time shows:
00:00:06.07. My questions are:

1. The update time is way too long. How to improve this?
2. I used your showsql.sql query during executing this code, and it shows
‘ROLLBACK’ and nothing else. Why?)....

don't you think it would be 'relevant'??





it is not only a bad practice, here it is going to corrupt your data, it probably already has.

have you considered:

a) what happens when you rollback -- but you cannot rollback the other stuff.
b) what happens when an update is restarted and your triggers fire twice.

Run this script, do what the prompts tell you to do, and explain what your stuff might do given the update of two rows fires the row trigger 3 times - once with a set of values that didn't "quite make it to the finish line"

drop table t1;

set echo on


create table t1 ( x int, y int );

create or replace trigger t1_trigger
after update on t1 for each row
begin
dbms_output.put_line
( 'updating ' || :old.x || ',' || :old.y ||
' to ' || :new.x || ',' || :new.y );
end;
/

insert into t1 values ( 1, 1 );
insert into t1 values ( 2, 1 );
commit;

set echo off
prompt in another session, issue "update t1 set x=3 where x = 2" and come back here
set echo on
pause


set echo off
prompt commit the other session
set echo on
update t1 set x=3 where x >= 1;






Anything based on Oracle redo is by definition "not real time", it is definitely asyncronous.

I'm afraid you have a non-performat, non-scalable, rarely available data destroying implementation!


Using autonomous transactions is the worst decision you could make (well, besides "real time replication" that is)


Think about what commit and rollback means and which might entail "less work".

Commit just says -- hey, what you've already done, lets roll with it.
Rollback says -- hey, everything you just did -- regardless of how much work it was -- go back and UNDO it.

databases commit fast (at least oracle does). that is what you are expected to do at the end of a transactions.

databases rollback slow as compared to a commit. you have to go back to everything you touched and untouch it-- put it back.

Subash, May 02, 2004 - 1:41 pm UTC

How to tune PL/SQL ?. Can use please explain the concept with example .
Thanks


Tom Kyte
May 02, 2004 - 4:30 pm UTC


if you can tell me how to tune Java, C, C++, VB, or any 3gl -- then I'll tell you the answer for PLSQL (as it is pretty much the same...)

If you want some in's and outs for plsql, I know this book is good:

</code> http://www.amazon.com/exec/obidos/tg/detail/-/1590592174/ <code>

I was one of the tech editors on it and know it to be accurate and well written.

update and showsql

A reader, May 03, 2004 - 1:18 pm UTC

Tom,

1. The reason that I didn’t tell you the background information at very beginning is at then I disabled all replication processes and did the update. So I thought the update is nothing to do with the data replication. What I didn’t expected was it took much of execution time on ROLLBACK.

2. Yes, we do know the autonomous transactions in a trigger could cause data inconsistency. We have a background process that constantly checks it and does proper work.

3. You are absolutely right. The replication is ‘near real time’, not ‘exact real time’.

4. We found a bug in the trigger, which cause many rows updating to be rolled back. Now we have fixed it.

I really appreciate your help and time.


A reader, May 10, 2004 - 12:09 pm UTC

Hi Tom,
Please what means exactly
Parse CPU to Parse Elapsed
Thanks.

Tom Kyte
May 10, 2004 - 12:51 pm UTC

cpu time spent parsing

vs

wallclock time spent parsing.


if elapsed time of parsing is 5 minutes and cpu time of parsing is 1 minute, you had to wait for 4 minutes for something during parsing -- probably library cache latches OR time on the cpu itself.


a wide variation between the two indicates possible problem.

A reader, May 10, 2004 - 3:23 pm UTC

Thanks Tom

ghost SQL and job

A reader, July 01, 2004 - 4:26 am UTC

Hi

I am using Oracle 10g on Red Hat 3.0.

I see an sql statement whose executions is increasing (shown from v$sql) however from v$session I cant find anyone executing it. I am trying to find out who is executing it because it consumes over 3000 million buffer gets in 2.6 million executions. I executed your showsql.sql without success...

I have another job which calls a procedure, the procedure builds a dynamic sql something like:

sqlstr := 'insert ..... select    ' etc.

execute immediate sqlstr

From v$sql I only see 

DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;  broken BOOLEAN := FALSE; BEGIN pack_zone.refresh_area; :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END; 
with a buffer get of 2671721819 and sql address of 7C55D384

Is it because it´s dynamic SQL?
I want to see it´s run time execution plan but it´s not possible neither when I run

SQL> SELECT * FROM v$sql_plan where address = '7C55D384';
SELECT * FROM v$sql_plan where address = '7C55D384'
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
(Exception signal: 11 (SIGSEGV), code: 1 (Address not mapped to object), addr: 0x5b3f, PC: [0x8608543, msqsub()+23])

!!!!!



Note: I find 10g is not suitable for production! Found many bugs, found 2 in RMAN which make restoring impossible, one in CBO problem with connect by query and 3, or 4 bugs more!!! 

Tom Kyte
July 01, 2004 - 10:37 am UTC

what was the sql, perhaps it is recursive sql (run by oracle).

remember, these v$ views are cumulative - you should use the dbconsole to monitor this stuff or statspack -- they use snapshots and diffs. it is perfectly ok for a query to have lots of lio like that if the instance has been up and you run this query frequently. that number means *nothing* by itself.

same with the job -- you would only see the sql when the job is actually running (so you haven't been looking when the job is running, thats all) and those numbers are CUMULATIVE.

for that last bit, please contact support.


I disagree 100% with your last statement. I've found bugs in windows, I've found bugs in linux, I've found bugs in word, I've found bugs in cisco routers -- now what? do you shut down everything? you do realize there might even be a bug in 9ir2, 8ir3? think about what you are saying.

RMAN Bugs

A reader, July 01, 2004 - 11:26 am UTC

If RMAN has bugs which make restore impossible, stop using it.

A reader, July 02, 2004 - 2:01 pm UTC

How to find out all the sql statements executed by a session if I know the session number? I know Oracle is going to flush part of shared pool whenever it is full. I wanted to see all the available sql’s


Eg:

Lets assume my session id is 10

Sql plus> select * from emp;

Sql plus> select * from dual;

Sql plus> select * from dept;


Now I wanted to see these 2 sqls using v$ or other views? I am not able to see the sql address (address is 0 after the end of the sql execution) in v$session once the sql execution got completed.


Tom Kyte
July 02, 2004 - 2:40 pm UTC

you cannot.

you would have to use fine grained auditing (dbms_fga) or sql_trace=true

Thanks

A reader, July 02, 2004 - 3:31 pm UTC

Thanks for your update. How about if I query for sql’s when the transaction is still pending.

Lets say I have the following transaction

Begin transaction

Update emp set name = ‘aaa’ where eno = 1;

Select name from emp where eno = 1;

Update dept set name = ‘ db’ where dno = 2;

Is there any way get these 3 sql statements ff I run a query against system views (before end of this transaction)


Another question on sql_trace is:

You are correct, I can get all sql statements for a session by using sql_trace. Can I use any trace or other mechanism to get all sql statement in the executed order?

(In sql trace I am going to see 1 sql with n executions if the user executed this stmt multiple times)

I am just looking for a log of all sql staments from a particular session using ascending order by execution time


Tom Kyte
July 02, 2004 - 4:42 pm UTC

no, there is not.

trace files are sequential -- they show a linear execution of things. tkprof aggregates them up by default-- aggregate=no stops that behaviour

Thanks

A reader, July 02, 2004 - 4:55 pm UTC


Query over db link

Jeremy, August 02, 2004 - 4:57 pm UTC

showsql.sql works great, I use it often. Should this also show sql executing on the database via a dblink or other remote (LOCAL=NO) process? It does not appear to. Is there a way to?

Tom Kyte
August 02, 2004 - 6:16 pm UTC

sure it does? it does for me anyway, can you recheck?

OK

Jacob, August 03, 2004 - 3:36 am UTC

Dear Tom,
Is it possible to find out the number of times a procedure
or a function get executed?Can I use the view v$librarycache to find out that?
Please do reply.
Bye!


Tom Kyte
August 03, 2004 - 8:43 am UTC

the only way to do it long term, over database bounces and reliably (even if the instance is up forever) is -- well - auditing.

otherwise, the v$ tables can give you a rough *idea* but since a cache is a cache, and things come and go from caches -- it will not be able to tell you "exactly"

v$sql, v$db_object_cache would be the places to start looking.

Active sessions show none-sql

A reader, August 19, 2004 - 9:52 am UTC

Tom,

I have several sessions that constantly (all day long) show 'ACTIVE' and with empty string in the V$SESSION.USERNAME column. I used your showsql.sql and none of them showed up. I checked at OS level as following:

ps -ef | grep 10660 | grep -v grep
ggs 10660 1 3 01:00:03 ? 0:01 ora_j004_emrg

It shows all of them are oracle jobs (they are all ora_j00n_emrg). When I used OEM to get session's detail information, it shows SCHEMANAME is SYS.

I cannot figure out what are those jobs doing and how do I find more detail information for them and why are they always ACTIVE? Please give me some help.

Thanks as always.

Tom Kyte
August 19, 2004 - 10:10 am UTC

select what from dba_jobs;

or

select what from dba_jobs where job in ( select job from dba_jobs_running );

it'll show you "what" they are running.

(and backgrounds are typically "active"

ops$tkyte@ORA9IR2> /
 
USERNAME   STATUS   PROGRAM
---------- -------- --------------------
           ACTIVE   oracle@localhost.loc
                    aldomain (PMON)
 
           ACTIVE   oracle@localhost.loc
                    aldomain (DBW0)
 
           ACTIVE   oracle@localhost.loc
                    aldomain (LGWR)
 
           ACTIVE   oracle@localhost.loc
                    aldomain (CKPT)
 
           ACTIVE   oracle@localhost.loc
                    aldomain (SMON)
 
           ACTIVE   oracle@localhost.loc
                    aldomain (RECO)
 
           ACTIVE   oracle@localhost.loc
                    aldomain (QMN0)
 
           ACTIVE   oracle@localhost.loc
                    aldomain (ARC0)
 
           ACTIVE   oracle@localhost.loc
                    aldomain (ARC1)
 
           ACTIVE   oracle@localhost.loc
                    aldomain (CJQ0)

OPS$TKYTE  ACTIVE   sqlplus@localhost.lo
                    caldomain (TNS V1-V3
                    )


and the job queues are backgrounds.  they sit around waiting to be told by the job queue coordinator (cjq0) to do something -- so they are "active" but "asleep". 

Re: Active sessions show none-sql

A reader, August 19, 2004 - 11:25 am UTC

Tom,

Thanks for your quick response. Please take a look at the following:

SQL> select s.username, s.osuser, s.status, s.program, spid
  2  from v$session s, v$process p
  3  where s.status = 'ACTIVE' and s.paddr = p.addr;

USERNAME   OSUSER     STATUS   PROGRAM              SPID
---------- ---------- -------- -------------------- ------------
           ggs        ACTIVE   oracle@titan (PMON)  29817
           ggs        ACTIVE   oracle@titan (DBW0)  29819
           ggs        ACTIVE   oracle@titan (LGWR)  29821
           ggs        ACTIVE   oracle@titan (CKPT)  29823
           ggs        ACTIVE   oracle@titan (SMON)  29825
           ggs        ACTIVE   oracle@titan (RECO)  29827
           ggs        ACTIVE   oracle@titan (CJQ0)  29829
           ggs        ACTIVE   oracle@titan (ARC0)  29835
           ggs        ACTIVE   oracle@titan (ARC1)  29837
                      ACTIVE                        10660
                      ACTIVE                        106

USERNAME   OSUSER     STATUS   PROGRAM              SPID
---------- ---------- -------- -------------------- ------------
                      ACTIVE                        29853
                      ACTIVE                        29855
                      ACTIVE                        10662
                      ACTIVE                        7118
SYSTEM     ggs        ACTIVE   sqlplus@titan (TNS V 11149
                               1-V3)

                      ACTIVE                        10044
                      ACTIVE                        10046

18 rows selected.


SQL> select what from dba_jobs where job in (select job from dba_jobs_running);

no rows selected

At OS level:

ggs@titan:/ggs>ps -ef | egrep "29853|29855|10662|7118|10044|10046|10660|106" | grep -v egrep
     ggs   106     1  3  Aug 13  ?         5:38 ora_j000_emrg
     ggs 10044     1  2 16:24:26 ?         0:04 ora_j007_emrg
     ggs 29855     1  3  Aug 13  ?         2:54 ora_j002_emrg
     ggs 29853     1  3  Aug 13  ?         3:08 ora_j001_emrg
     ggs 10660     1  3 01:00:03 ?         0:01 ora_j004_emrg
     ggs 10046     1  3 16:24:27 ?         0:03 ora_j008_emrg
     ggs 10662     1  2 01:00:04 ?         0:01 ora_j005_emrg
     ggs  7118     1  3  Aug 16  ?         0:43 ora_j003_emrg


All of them showed as Oracle jobs, but not the job queue coordinator (cjq0). 

What are they and what kind of jobs they are doing? Please help.

Thanks again. 

Tom Kyte
August 19, 2004 - 4:59 pm UTC

so, they are job queue processes? they are backgrounds, they are normal, they are like smon and so on, they are doing the same thing smon is doing, hanging waiting for something to do -- active, waiting to be posted to be told to do something.

they are normal, they are expected, they are OK.

Getting current session's current SQL

The Interested Reader, August 19, 2004 - 2:53 pm UTC

Tom,

Showsql has proven itself a most useful tool! Thanks very much!

One question: I have experimented with retrieving SQL statements by linking v$session to v$sql for the current session such as:

SELECT
S.PROGRAM,
S.TERMINAL,
Q.SQL_TEXT
FROM
V$SESSION S,
V$SQL Q
WHERE
S.sql_HASH_VALUE = Q.HASH_VALUE(+)
AND S.SQL_ADDRESS = Q.ADDRESS(+)
AND S.audsid = userenv('sessionid');
or
SELECT
S.PROGRAM,
S.TERMINAL,
Q.SQL_TEXT
FROM
V$SESSION S,
V$SQL Q
WHERE
S.PREV_HASH_VALUE = Q.HASH_VALUE(+)
AND S.PREV_SQL_ADDR = Q.ADDRESS(+)
AND S.audsid = userenv('sessionid');

The problem is that the first query (uses v$session's Current SQL statement address and hash) yields, as you all no doubt have guessed, itself for "SQL_TEXT"! The second statement (attempts to find V$SQL for v$session's Previous SQL statement address and hash) yields nothing for "SQL_TEXT" as v$session.PREV_HASH_VALUE and v$session.PREV_SQL_ADDR are null.

I have been toying with the idea of being able to do "focused" tracing and auditing of SQL statements this way; for example, I contemplated using such a statement as a cursor within the body of a trigger, hoping to capture the updating application's SQL statement that launched the trigger.

Is there any way to accomplish this for the current session ( i.e. userenv('sessionid') ) without simply returning the statement making such a query? I.e. does anything in v$session link to anything else that would provide recent/cached/historical SQL statements?

Thanks!

Tom Kyte
August 19, 2004 - 7:53 pm UTC

in 9i, there are functions for the trigger to get the triggering sql:

</code> http://docs.oracle.com/docs/cd/B10501_01/appdev.920/a96590/adg14evt.htm#1005274 <code>

And how to track DML statements?

Teymur Hajiyev, September 08, 2004 - 5:47 am UTC

Dear Tom.

I want to track DML on tables of specific schema. But there is DDL option for shema based trigger.

Result(INSERT/UPDATE/DELETE to each table of schema) will be added as record to DML_Archive table (Table_Name, Operation, SQL).

Can you help me?

Thanks in advance.

Tom Kyte
September 08, 2004 - 9:59 am UTC

well, there is a DDL option

but for DML, you would have to put a trigger on each TABLE -- that is the only way.

More pwerfull decision

Teymur Hajiyev, September 08, 2004 - 10:26 am UTC

Thanks Tom.

I do understand that, I have to write sql script in order to create all trigger at once. Below is my script and works, but may be you have more powerfull decision:


declare
cursor c1 is select table_name from user_tables where table_name <> 'DML_ARCHIVE';
s varchar2(500);
begin
for tab_rec in c1 loop
s := 'CREATE OR REPLACE TRIGGER ' || 'trig_' || tab_rec.table_name || ' AFTER INSERT OR DELETE OR UPDATE ON ' || tab_rec.table_name;
s := s || ' begin IF INSERTING THEN insert into dml_archive values (' || '''' || tab_rec.table_name || '''' || ', ' || '''' || 'INSERT' || '''' || ', sysdate);';
s := s || ' ELSIF UPDATING THEN insert into dml_archive values (' || '''' || tab_rec.table_name || '''' || ', ' || '''' || 'UPDATE' || '''' || ', sysdate);';
s := s || ' ELSIF DELETING THEN insert into dml_archive values( ' || '''' || tab_rec.table_name || '''' || ', ' || '''' || 'DELETE' || '''' || ', sysdate);END IF; END ;';
execute immediate s;
end loop;
end;
/

DML_Archive is a table which I will write tracing results(DMLs) to it, that is why i dont create trigger on it, otherwise it will be recursive call.

Thanks in advance.

Tom Kyte
September 08, 2004 - 10:38 am UTC

hows about audting -- since you are in fact not actually capturing any statements, just use AUDIT.

one command, bamm, you would be done. early lunch.....

hundreds of lines of code....

A reader, September 21, 2004 - 4:28 am UTC

Tom,

showsql gives only few lines of code while tracking users code. since it depends on the value of piece. if i want to get hundreds of lines of code. how to change thie piece value dynamically..

please help us here.

Thanks


Tom Kyte
September 21, 2004 - 7:47 am UTC

did you read the script? do you see something in the predicate that makes it stop after a bit? remove it.

Before I would run any script on my system, I would make sure I knew what it was doing - the "fix" here is pretty simple.

change this loop
for y in ( select max(decode(piece,0,sql_text,null)) ||
max(decode(piece,1,sql_text,null)) ||
max(decode(piece,2,sql_text,null)) ||
max(decode(piece,3,sql_text,null))
sql_text
from v$sqltext_with_newlines
where address = x.sql_address
and piece < 4)
loop
if ( y.sql_text not like '%listener.get_cmd%' and
y.sql_text not like '%RAWTOHEX(SQL_ADDRESS)%')
then
dbms_output.put_line( '--------------------' );
dbms_output.put_line( x.username );
dbms_output.put_line( x.logon_time || ' ' ||
x.current_time||
' last et = ' ||
x.LAST_CALL_ET);
dbms_output.put_line(
substr( y.sql_text, 1, 250 ) );
end if;


to just be a flat query against sqltext with newlines, ordering by piece and printing out each and every piece.

v$view queries generating physical reads??

Russell, November 27, 2004 - 10:58 pm UTC

Hi Tom,

Our System has a small number of users, mostly OLTP. There are however a few users (<5) requesting DSS type queries. Sometimes I like to see what query is running that is taking so long. To show me this I use the following script:

clear breaks
clear columns
break on username skip 2 on SID skip 1 on osuser
column username format a23
column osuser format a16
column users justify centre
column sid justify centre

select nvl(sesion.username,sesion.program) username
, sesion.osuser
, sesion.sid
, sqltext.sql_text
from v$sqltext sqltext, v$session sesion
where sesion.type = 'USER'
and sesion.status = 'ACTIVE'
and sesion.sql_hash_value = sqltext.hash_value
and sesion.sql_address = sqltext.address
order by username, sid, piece
/

I am happy with the results of this script, however it ususally takes between 50-75 secs to complete. Interestingly, the server was rebooted overnight and now the response is <5secs.

I have executed the script as SYS with autotrace to try to see what is happening. But I still don't get it.

The results are:

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (ORDER BY)
2 1 MERGE JOIN
3 2 SORT (JOIN)
4 3 FIXED TABLE (FULL) OF 'X$KSUSE'
5 2 SORT (JOIN)
6 5 FIXED TABLE (FULL) OF 'X$KGLNA'

Statistics
----------------------------------------------------------
533 recursive calls
97 db block gets
172 consistent gets
43 physical reads
12188 redo size
1392 bytes sent via SQL*Net to client
512 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
1 sorts (disk)
14 rows processed

I wasn't expecting to see physical reads and redo...

1. The result set is small, I expected the order by to result in a memory sort. It looks like the join is causing a sort. Is that likely to be the disk sort shown in the stats?

2. Hopefully this isn't a dumb question but, why so much redo? In fact, why is there ANY redo? I thought these were memory structures, therefore no IO required. and it's a select query, I'm not asking for anything to be changed.

3. Is this something to to with me reading/sorting these X$ tables for a duration while Oracle is writing to them behind me?

4. Is there a less costly way to determine what SQL is being performed by active sessions?

Thanks in advance

Tom Kyte
November 28, 2004 - 9:53 am UTC

you have 532 recurisve sql calls -- probably space management to allocate temp space which you read and wrote during that sort to disk (that copy of v$sqltext you made and sorted)

that is the reads, that is the redo (the recursive sql)

up your sort area size considerably to make the sort disk go away and so will these.

12k of redo is tiny.


with these dynamic views, you might be much better served with the procedural approach above.

sttill got physical io on v$views...

Russell, November 29, 2004 - 12:01 am UTC

Thanks Tom,

I think we do need some additional sort size, currently 4M. After some experimentation, I still can't change the behaviour of this query!

I ran the script after an:
alter session set sort_area_size = 1000000000 (out of frustration)

and still observe the following

106 rows selected.

Elapsed: 00:00:24.03

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (ORDER BY)
2 1 MERGE JOIN
3 2 SORT (JOIN)
4 3 FIXED TABLE (FULL) OF 'X$KSUSE'
5 2 SORT (JOIN)
6 5 FIXED TABLE (FULL) OF 'X$KGLNA'


Statistics
----------------------------------------------------------
13553 recursive calls
2653 db block gets
4512 consistent gets
1248 physical reads
324452 redo size
9228 bytes sent via SQL*Net to client
995 bytes received via SQL*Net from client
10 SQL*Net roundtrips to/from client
2 sorts (memory)
1 sorts (disk)
106 rows processed

This problem does not manifest on ALL v$views. v$session has no IO but v$access does have IO? (with 1GB sort area)

select count(*) from v$access;

COUNT(*)
----------
1256

Elapsed: 00:00:32.06

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 VIEW OF 'GV$ACCESS'
3 2 SORT (UNIQUE)
4 3 MERGE JOIN
5 4 SORT (JOIN)
6 5 MERGE JOIN
7 6 SORT (JOIN)
8 7 MERGE JOIN
9 8 SORT (JOIN)
10 9 FIXED TABLE (FULL) OF 'X$KGLLK'
11 8 SORT (JOIN)
12 11 FIXED TABLE (FULL) OF 'X$KGLDP'
13 6 SORT (JOIN)
14 13 FIXED TABLE (FULL) OF 'X$KGLOB'
15 4 SORT (JOIN)
16 15 FIXED TABLE (FULL) OF 'X$KSUSE'

Statistics
----------------------------------------------------------
22984 recursive calls
4555 db block gets
7913 consistent gets
2157 physical reads
559636 redo size
203 bytes sent via SQL*Net to client
189 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
5 sorts (memory)
2 sorts (disk)
1 rows processed

14:54:07 SYS:DWPROD> select count(*) from v$session;

COUNT(*)
----------
106

Elapsed: 00:00:02.01

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 FIXED TABLE (FULL) OF 'X$KSUSE'


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
203 bytes sent via SQL*Net to client
190 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

1. Does the alter session take immediate effect?

2. If so, why are there still disk sorts when there is 1GB of sort area???

3. The time taken to execute these is not causing me any real problems, personally, I can wait. I am concerned that there is another problem here that may be contributing to other performance problems that are currently written off as an IO limitation. Is it possible that Oracle is performing IO on these internal tables and that this may be a source of performance drain???

Tom Kyte
November 29, 2004 - 8:06 am UTC

you have a big shared pool and it "doesn't fit" so it is sorting to disk, that is all (and also explains why it went "faster" after a reboot -- the shared pool hadn't filled up yet, the bigger it is, the longer it takes to sort)


forget about TABLES for a minute, anytime you see:

2 sorts (disk)
^ number > 0

you are hitting disk, period.


this is not doing IO on the v$ tables (but bear in mind, the very act of you querying them has a measurable negative impact on the performance of your system -- they are in memory data structures, you are latching them to query them!).

it is doing IO on temp, autotrace is telling you that.

Clarification

A reader, December 30, 2004 - 5:05 pm UTC

In the last discussion thread, you mentioned that it is doing IO on temp and autotrace is telling you that. Can you please clarify me where in autotrace output I could find it

Tom Kyte
December 30, 2004 - 6:14 pm UTC

2 sorts (disk)

anytime sorts (disk) exceeds zero, you wrote and then read from temp

Where'd my statement go?

Neil, September 29, 2005 - 8:42 am UTC

Hi Tom,
I've been using showsql to see if my long-running statement is, well, still running. It started some hours ago and was showing up quite nicely. Now, it seems to have gone. I identified the sid and serial# for the session and ran a similar query on v$session to the one in FOR x IN ... and this returned an address. When I use that address to query the v$sqltext_with_newlines table ( ... where address = hextoraw('829064A0')... )
I get no rows returned. This is odd as I can still see the text of statements I ran prior to starting the long-runner.

1) Where'd my statement go?
2) This is a SQL statement, and I can't monitor it using v$session_longops - what's the best way of satisfying myself that the statement is still running?

SQL>@create_data
_

Has been looking at me for four hours! I KNOW it must be doing something, but I'd like to know what! 

Tom Kyte
September 30, 2005 - 7:39 am UTC

(use of dbms_application_info for a long running process is highly recommended)

could your current statement be DDL? DDL isn't in v$sql until 10g

No DDL

Neil, September 30, 2005 - 8:38 am UTC

It was just an ordinary piece of SQL. It took nearly eight hours to run, and it disappeared from showsql's sight after about half an hour (Version 8.1.7). This morning, with the help of this inestimable site, I have reduced the run-time to 10 minutes - so it's not such an issue!
However, I'd still be interested to know where it went.
I noticed you had a very long day yesterday (first postings at 6 am, then onto FSOUG, and last post at 9pm) - so this can wait till Monday. Put your feet up, man!

Tom Kyte
September 30, 2005 - 10:49 am UTC

Hmm, I cannot reproduce that - can you define "ordinary piece of sql"?

How about RAC

A reader, April 18, 2006 - 9:59 am UTC

Tom,

I tried your script showsql and it works great in a dedicated server with one database. But have you updated
the script to run in a RAC(one than once instance)?


Thank you.

Tom Kyte
April 18, 2006 - 10:04 am UTC

go for it - everything is right there for you to start from.

A specific detail that I don't think has been covered so far ...

Lee Elms, April 18, 2006 - 11:26 am UTC

Is it possible to determine whether a transaction has made any uncommitted changes to the database ? i.e. a transaction has executed some PL/SQL code that might have executed some DML statements ... is there a data dictionary view that has an indication of whether any changes were made ?

Tom Kyte
April 18, 2006 - 2:25 pm UTC

well, you can tell if a transaction has begin - which would necessitate a commit or rollback (v$transaction).

That a transaction is STARTED is sufficient I would think.

Does that do it - if not - why not? (they are jamming the undo segment - that is what basically counts...)

To Lee Elms

Michel Cadot, April 18, 2006 - 12:27 pm UTC

Lee,

If a transaction makes some modifications then there is a rollback segment associated to it and there is an entry in v$transaction. So you can query:
select * from v$transaction, v$session
where ses_addr=saddr and sid=<your session id>;

You don't know which modifications have been made but you know there was some.

Note: distributed transactions also have a rollback segment associated to them even if they don't make any modification.

Regards
Michel


Lee Elms, April 26, 2006 - 5:43 am UTC

Tom,

In a program that allows users to arbitrary SQL / PL/SQL (something like TOAD), I want to be able to indicate whether any changes have been made by the SQL. Michels response seems to give me pretty well what I want (?) ...

Tom Kyte
April 26, 2006 - 8:10 am UTC

you can certain query v$transaction (a view not normally available to most people) to see if you are in a transaction - yes. But it will not tell you if the last sql statement executed made a change.

typically, your program would notice that it did some statement capable of making a modifiction and would ask the database how many rows were affected (eg: you would not need to do that query, you should already know since you should be printing out "N rows affected" or something to that effect)

Check whether the certain procedure is being used, and by which session

Sean, December 11, 2006 - 5:05 pm UTC

Hi Tom,

I want to check whether the certain package is being used. Here is the test:
I run this procedure in one session, and I can see the entry in v$access or dba_ddl_locks. But I can still see the entry in these two views even the execution completes, until I quit the session. Here is the procedure:

Create procedure p1 is
Begin
Sys.dbms_lock.sleep(60);
End;

Thanks so much for your help. -- Sean


Program column in V$SESSION

Ravi Kumar, January 09, 2007 - 11:00 am UTC

Hi Tom,

I have a question about Program Column in V$SESSION.

For some sessions of our Application, It shows the program name with the complete path and for few other sessions it just shows the Program Name(exe) without any path.

Could you please explain Why, and how can I change that to Not to show Path.

Thanks in Advance..
Ravi




Tom Kyte
January 11, 2007 - 9:31 am UTC

depends on how the program was started - the command line used.

Finding a bad qry

marc, January 12, 2007 - 3:32 pm UTC

I have a 3rd party application that is error-ing out with ORA-00942: table or view does not exist. But this app is not giving back the sql. I have run traces but the trace only returns valid sqls. Is there a way to trace a broken sql statement?

Need some help with 9i/10g migration

Todd, January 16, 2007 - 5:06 pm UTC

Tom,

I'm looking around for some help with this problem. We're testing a 9i to 10g migration and I would ultimately like to have any 9i oracle sql/commands immediately executed on the 10g instance.

Currently we're using
CREATE OR REPLACE TRIGGER SYS.LOGIN_TRACE_TRIGGER
AFTER LOGON ON DATABASE
begin
IF (upper(sys_context('userenv', 'session_user' )) ='MYUSER')
THEN
execute immediate 'alter session set sql_trace=true';
END IF;
end;
/

And then submitting the parsed SQL trace to the 10g instance, but I'm having some issues with bind variables not being visible. Tkprof is awesome but I'm having a hard time marrying back the SQL issued in tkprof to the raw sql trace files to determine timestamps. Basically I'm trying to replay the SQL from one machine on to another. I'd love to measure and benchmark it.

I just can't seem to pull it off.

We know that there are going to be 9i/10g issues. We've identified a handful and we know that there will be more. I s there an easier way to get the SQL from the 9i instance replayed on the 10g instance?

Ram, March 26, 2007 - 7:51 am UTC

Can we do Audit trail with out enabling the Init.Ora parameter(audit_sys_operations= true and audit_trail=DB)? i mean how to track what are the DB objects are access/used from front end Application.
Tom Kyte
March 26, 2007 - 11:06 am UTC

if you want to use Oracle's AUDIT command, you have to enable it - yes.

A reader, March 26, 2007 - 3:06 pm UTC

Thanks For your prompt reply.
NO AUDIT command as well as NO trigger.Is there any other way out to do Audit in Oracle 10gRelease2.
how about the V$session and V$SQL ?
Tom Kyte
March 27, 2007 - 9:19 am UTC

what do you mean "no audit command"

let me put it this way - if you are saying someone has said "we need audit" and someone else is saying "you cannot use the AUDIT command, you cannot ... you cannot ..."

then you will not achieve your goal.

there is:

a) the audit command, easy, fast
b) triggers - slow, hard, cumbersome
c) dbms_fga - captures selects (9i) and modification statements (10g) - easy fast


EXECUTIBLE FILE

AHLAM SADAGH, March 27, 2007 - 1:08 pm UTC

HI TOM..
HWO WE CAN CREATE AN EXECUTIBLE FILE (FMX) FROM SOURCE FILE (FMB) (IN FORM BUILDER APPLICATION)
WHEN WE WASH TO EXECUTE IT ON ANOTHER DEVICE WITHOUT LOADING ORACLE DEVELOPER ON LATER DEVICE...

AHLAM SADAGH..
COMPUTER PROGRAMMER.
SANA'A/YEMEN..

A reader, March 28, 2007 - 12:12 am UTC

To Ahlam Sadagh,

1. Please use lower case letters.
2. What does your post(question) have to do with the original thread?

Rahul.

Database Trigger Status

Manoj, May 02, 2007 - 3:15 am UTC

Hi Tom,

How can I find out the change in status of a particular DB trigger over a period of time? Does Oracle store any history of this?

Thanks,
Suprabhat
Tom Kyte
May 02, 2007 - 8:19 am UTC

nope, such micro changes would not be stored, you can enable auditing to audit things people do in the database - it would show people trying to create things, drop things and such.

Track DDL

parlee, May 14, 2007 - 7:54 am UTC

Hi Tom,

I've been using your script showsql.sql to check the currently running sql in the database. However, when sql statements like "CREATE TABLE a_table AS select * from other_table" are running, sohwsql.sql doesn't show up any SQL. I am using 9.2.0.4 and 10.2.0.2 database versions. Please could you explain

1. why it doesn't show
2. what script to run to track DDL statements like this.

Thanks very much

Tom Kyte
May 14, 2007 - 2:10 pm UTC

DDL won't appear in v$sql in 9i.


but in 10g, it sure does:

--------------------
OPS$TKYTE(38,39) ospid = 20029 command = 1 program = sqlplus@tkyte-pc.us.oracle.com (TNS V1-V3) dedicated server=20030
Monday    15:05  Monday    15:06 last et = 3
create table t as select * from big_table.big_table
ops$tkyte%ORA10GR2>



re-look at your 10g example, it should be there.

Track DDL

parlee, May 15, 2007 - 7:06 am UTC

So how do we check the DDL in 9i? Is there another view where it can be seen.

Thanks as always.

Tom Kyte
May 15, 2007 - 4:51 pm UTC

it is not in any of the v$ views, no.

TRACk DML

Gazazou, May 16, 2007 - 8:01 am UTC

Dear TOM

I'm using ora_sql_txt as you told in trigger after to retrieve DML sql; I have insert result in log_activity table, my problem is that values for column is like varibale.
UPDATE COUNTRY
   set 
       country_name=:b2
    where country_id = :b1

How can i get in my sql text the values of :b2 and :b1.

Thanks

TRACK DML

Gazazou, May 16, 2007 - 8:08 am UTC

This is the code
CREATE OR REPLACE TRIGGER log_country 
after insert or update or delete on country for each row
declare 
v_current_sql  varchar2(250); 
v_adress_ip    varchar2(250);
v_operation    varchar2(20):=null; 
sql_text       ora_name_list_t;
addr          varchar2(100);
terminal      varchar2(100);
n number;
stmt VARCHAR2(2000);
begin
       begin
   n := ora_sql_txt(sql_text);
   FOR i IN 1..n LOOP
    stmt := stmt || sql_text(i);
   END LOOP;
   stmt:=substr(stmt,1,2000);
  exception
  when others then null; 
  end; 
      begin 
   select   SYS_CONTEXT('USERENV', 'IP_ADDRESS', 15),SYS_CONTEXT('USERENV', 'TERMINAL',100)  into addr,terminal 
   from dual ; 
   exception 
   when others then null; 
  end; 
if inserting then v_operation:='insertion'  ;end if; 
if updating then v_operation:='modification';end if; 
if deleting then v_operation:='suppression' ;end if; 
if v_operation is not null 
then 
 if inserting or deleting or updating
  then insert into activitylog 
   (activity_date,activity_operation,activity_statement,activity_table,user_id, 
   name_column_key1_id,name_column_key2_id,activity_ip_adress,activity_terminal) 
   values (sysdate,v_operation,stmt,'country',lower(user),'country_id','',addr,terminal); 
  end if; 
end if; 
exception 
when no_data_found then null; 
end;
/

A reader, May 22, 2007 - 8:47 am UTC

How can i get the sql executed within the procedure. If i have three result set out from procedure and some select into statements within procedure. I want to get all these statements.
Tom Kyte
May 22, 2007 - 9:33 am UTC

"get them", what do you mean by "get them"

trace event

ignorant, May 22, 2007 - 2:35 pm UTC

Hi Tom,
I am not sure what the reader in the previous post is looking for, but maybe these can help -
Event 10928 - Trace PL/SQL Execution
This event traces PL/SQL execution

ALTER SESSION SET EVENTS
'10928 trace name context forever, level 1';

Event 10938 - Dump PL/SQL Execution Statistics
This event dumps PL/SQL execution statistics.

ALTER SESSION SET EVENTS
'10938 trace name context forever, level 1';

This event currently generates the following output

--NOTICE ---------------------------------------
--PL/SQL TRACE INFORMATION IS NOW IN THE DATABASE
-- To create the trace tables, use the script --
-- rdbms/admin/tracetab.sql under ORACLE_HOME --


What are your thoughts on these events?
Thanks
Tom Kyte
May 22, 2007 - 7:10 pm UTC

don't really use them.

When does Elapsed time get Set in V$session

A reader, June 12, 2007 - 12:17 am UTC

Hi Tom,
I am using you proc to track how long a particular session ran for through the last_call_et (elapsed time in seconds).

I see that Sometimes for the same SID,SERIAL# Oracle does not reset this column even if it runs different say DML statements.
Is it because its part of the same may be pl/sql block or procedure.

I am trying to track how long a particular activity took but
to my surprise even for the same sid,serial# when we do different DMLS the last_call_et does not get reset.

Any suggestions why this behaviour and how to then track individual statements for the same session. I am only interested in how long it takes to run.

May be I am missing something fundamental here.
Thank you Sir
Tom Kyte
June 12, 2007 - 9:53 am UTC

last_call_et is updated about every three seconds, if you watch it, you'll see it climb in 3 second intervals for things...


And it is the time the last CALL started - if the call was to a plsql procedure with thousands of SQL statements - they are all part of "that call".

last_call_et is the time the session has been in the status (eg: ACTIVE, INACTIVE, NONE) it is currently in. A change to the STATUS column "resets" this counter.

The individual 'statement' is the CALL - the plsql block or whatever.

SQL_TRACE shows the separate SQL statements one by one.

For the above review Db version 9208

A reader, June 12, 2007 - 12:19 am UTC


Some more

A reader, June 12, 2007 - 12:24 am UTC

Just for the above last_call_et review :
The last_call_et column keeps accumualting the time for active sessions.
I would expect it to reset at evey new DML even if the session is active.

Is there any way to track that.

Thanx
Tom Kyte
June 12, 2007 - 9:57 am UTC

sql-tracing does that.

last_CALL_et is about the last call - the last thing that changed the STATUS column value.


No other way

A reader, June 12, 2007 - 12:37 pm UTC

Tom,
Thanx for you response. Is there no other way then to
track individual SQL statement Times if we dont want to use sql tracing.

REASON I just want to continously monitor these or any individual SQL statments taking long time to finish.
in Production DB where I cant enable system level tracing.

I thought last_call_et would give me that but from your response it looks like it wont.

Is there no other V$ view which would have this info ?

so on

A reader, August 22, 2007 - 5:08 pm UTC

(low cpu, high elapsed, no wait events would lead me to think "starved for CPU")
(low cpu, high elapsed, lots o'waits -> contention for that resource)
(high cpu -> function does lots of work, tune algorith)

and so on.

"SO ON"

Can you please continue with your so on.
Tom Kyte
August 23, 2007 - 10:50 am UTC

and so on just means in this context "please use your knowledge of what your system does and how computers work to put 2 and 2 together, look at the available information and attempt to understand it"


Only 1 SQL running? really?

Ramanathan, September 12, 2007 - 8:33 am UTC

Hi Tom

I ran the shoqsql.sql code, and here's the output:

HOVRADS(596,8227) ospid = 396:5296 program = sqlplusw.exe
Wednesday 06:47 Wednesday 07:25 last et = 0
SELECT USERNAME||'('||SID||','||SERIAL#|| ') ospid = ' || PROCESS || ' program = ' || PROGRAM USERNA
ME, TO_CHAR(LOGON_TIME,' Day HH24:MI') LOGON_TIME, TO_CHAR(SYSDATE,' Day HH24:MI') CURRENT_TIME, SQL
_ADDRESS, LAST_CALL_ET FROM V$SESSION WHERE STATUS
--------------------
SYSMAN(845,3) ospid = 1234 program = OMS
Tuesday 04:04 Wednesday 07:25 last et = 18
BEGIN EMD_NOTIFICATION.QUEUE_READY(:1, :2, :3); END;
Elapsed: 00:00:01.51



This looks like there is only 1 SQL statement / PL/SQL block currently running, and I know that cannot be the case as there are approx 500 sessions in the v$session table. What am I missing here?
Tom Kyte
September 15, 2007 - 4:39 pm UTC

having 500 sessions in v$session does not mean ANY OF THEM are doing anything
ops$tkyte%ORA10GR2> select status, count(*) from v$session where username is not null group by status;

STATUS     COUNT(*)
-------- ----------
ACTIVE            1
INACTIVE         14



I had 15, 14 of them - doing nothing. One of them - well, it was looking at the other 14 sessions doing nothing (that active one is the session doing the query to see what the sessions are doing)



SQL_TEXT of current transaction

Chee Yong, June 16, 2008 - 7:14 am UTC

DBA_LCY at egiroar> create table t1 as
2 select * from dba_objects;

Table created.

DBA_LCY at egiroar> create table t2 as
2 select * from dba_objects;

Table created.

DBA_LCY at egiroar> delete from t1 ;

9512 rows deleted.

DBA_LCY at egiroar> delete from t2;

9513 rows deleted.
----
My simple question, is there anyway that I can "SELECT" the above 2 delete statement executed from other session? Because sometime I manage to find locked object from v$locked_object join with dba_objects. But I need to know the sql statement that cause the table lock. Is it from v$transaction?
Tom Kyte
June 16, 2008 - 12:12 pm UTC



no, there is no list of "sql executed by transaction" stored or recorded.

Now, if you have access to the ASH tables (active session history), you can see most of the sql executed by a session - but in a connection pool environment that might not be very meaningful as sessions are 'long' and filled with lots of requests over time.

SQL_TEXT of current transaction

A reader, June 17, 2008 - 12:14 am UTC

DBA_LCY at egiroar> create table t1
  2  as select * from dba_objects;

Table created.

DBA_LCY at egiroar> create table t2
  2  as select * from dba_objects;

Table created.

DBA_LCY at egiroar> delete from t1;

9512 rows deleted.

DBA_LCY at egiroar> delete from t2;

9513 rows deleted.

DBA_LCY at egiroar> select sid from v$session where audsid = userenv('SESSIONID');

       SID
----------
       249

--- 
from other session I query 

select sql_text from v$sql sql  , v$transaction t , v$active_session_history ash
where sql.sql_id = ash.sql_id
and t.xid = ash.xid
and ash.session_id = 249; 

SQL_TEXT
--------------------------------------------------
delete from t2
delete from t2

Why I only get "delete from t2"? Is it because the ash never sample the "delete from t1"?

Tom Kyte
June 17, 2008 - 9:49 am UTC

http://docs.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_1007.htm#I1030299

it is a sample, once a second, of what is active at that point in time. It will not be complete, it is a sample


from above

...
Now, if you have access to the ASH tables (active session history), you can see most of the sql executed by a session
....

sql execution statistic

Yoel, August 14, 2008 - 12:13 am UTC

Hi Tom,

Your code is to track the current execution of PLSQL and SQL.
But is there a view we can select or a way to get the number of execution per day for a given sql_id?

I am asking that because my DB control is showing a number that I could not believe.
An sql was executed about 28975 times within every hour.
That is 8 times per second.

So i want to crosscheck this number.

Database Instance: <dbname> > Top Activity > SQL Details: 40970dsdhrt7v

In the 'Details' section of that page, at the 'Statistic' tab there's 'Execution per Hour' chart. Click on a snapshot.

And i have this statistic below.

Execution Statistics

Total Per Execution Per Row
Executions 28975 1 0.01
CPU Time (sec) 182.13 0.01 0.00
Buffer Gets 1671308 57.68 0.63
Disk Reads 2 0.00 0.00
Direct Writes 0 0.00 0.00
Rows 2660817 91.83 1
Fetches 94283 3.25 0.04

Thank you in advance for your time and knowledge.

Regards,
Yoel
Tom Kyte
August 18, 2008 - 9:57 am UTC

... But is there a view we can select or a way to get the number of execution per
day for a given sql_id?
...

statspack and or AWR reports do that. You would need to snapshot the v$ views once a day (AWR does that, statspack you would set up)

and then run a report... we take two observations and subtract them.


sql execution statistic : i found it

Yoel, August 14, 2008 - 2:38 am UTC

Hi Tom,

I think i've found it. it is in DBA_HIST_SQLSTAT.
I should be looking at the EXECUTIONS_DELTA column.

Cheers,
Tom Kyte
August 18, 2008 - 9:58 am UTC

it has some/many of them - yes.

how to find stored procedure execution completed or not

Meagal, September 05, 2008 - 11:39 pm UTC

Tom:

I fired execution of stored procedure from sqlplus session (from my office PC) and later found that the execution will take more than hour to complete. So i left sqlplus window open and left the office.

I connected to the server from my home pc and was trying to find out whether stored procedure execution completed or not.

Here are the two things i checked :

1. from v$session, i checked the session status and it was INACTIVE.

2. from v$session_wait, the WAIT EVENT was "SQL*Net message from client".. Does that mean that stored procedure execution completed and sqlplus session is just idle and waiting for user action.

Are there any other ways to confirm the stored procedure completion ?



Tom Kyte
September 08, 2008 - 8:56 am UTC

if the session was inactive, the stored procedure had completed (it might have failed, it might have succeeded, that only you can tell) - but it is done running.

If you have any sort of long running (more than 3-5 seconds is long to me), code like that, it should be instrumented with calls to dbms_application_info set_session_longops - so you can MONITOR its progress in v$session_longops!

And of course, if your stored procedure is an hour long thing, one would think you might want to "background it" (run it via dbms_scheduler), the scheduler keeps an execution history for you. Additionally, a batch job like that should be instrumented with its own sort of "history" capability - so you have a record of the run times, success/failure, inputs, etc long term...

Getting full SQL text

John, November 04, 2008 - 7:30 am UTC

Hi Tom, 

I am trying to use your script to get full text that is why I altered 
dbms_output.put_line(substr( y.sql_text, 1, 250 ) ); 
to
 dbms_output.put_line(y.sql_text);

I changed the code to get full text...but the code failed with following error:

 1  declare
  2      x number;
  3  begin
  4      for x in
  5      ( select username||'('||sid||','||serial#||
  6                  ') ospid = ' ||  process ||
  7                  ' program = ' || program username,
  8               to_char(LOGON_TIME,' Day HH24:MI') logon_time,
  9               to_char(sysdate,' Day HH24:MI') current_time,
 10               sql_address, LAST_CALL_ET
 11          from v$session
 12         where status = 'ACTIVE'
 13           and rawtohex(sql_address) <> '00'
 14           and username is not null order by last_call_et )
 15      loop
 16          for y in ( select max(decode(piece,0,sql_text,null)) ||
 17                            max(decode(piece,1,sql_text,null)) ||
 18                            max(decode(piece,2,sql_text,null)) ||
 19                            max(decode(piece,3,sql_text,null))
 20                                 sql_text
 21                       from v$sqltext_with_newlines
 22                      where address = x.sql_address)
 23                        --and piece < 4)
 24          loop
 25              if ( y.sql_text not like '%listener.get_cmd%' and
 26                   y.sql_text not like '%RAWTOHEX(SQL_ADDRESS)%')
 27              then
 28                  dbms_output.put_line( '--------------------' );
 29                  dbms_output.put_line( x.username );
 30                  dbms_output.put_line( x.logon_time || ' ' ||
 31                                        x.current_time||
 32                                        ' last et = ' ||
 33                                        x.LAST_CALL_ET);
 34                  dbms_output.put_line(
 35                             y.sql_text);
 36              end if;
 37          end loop;
 38      end loop;
 39* end;
SQL> /
--------------------
ESOPS_01(414,22681) ospid = 25256 program = uzpomsi@apollo (TNS V1-V3)
Tuesday   23:00  Tuesday   23:05 last et = 0
declare
*
ERROR at line 1:
ORA-20000: ORU-10028: line length overflow, limit of 255 chars per line
ORA-06512: at "SYS.DBMS_OUTPUT", line 35
ORA-06512: at "SYS.DBMS_OUTPUT", line 133
ORA-06512: at line 34

Your code shows on first 250 characters of SQL text. Can you please inform how can I get full SQL text? 

Am I missing something?

Cheers,




Tom Kyte
November 11, 2008 - 12:03 pm UTC

ops$tkyte@ORA8I.WORLD> create or replace 
     procedure p ( p_str in varchar2 )
  2  is
  3     l_str   long := p_str || chr(10);
  4     l_piece long;
  5     n       number;
  6  begin
  7      loop
  8          exit when l_str is null;
  9          n := instr( l_str, chr(10) );
 10          l_piece := substr( l_str, 1, n-1 );
 11          l_str   := substr( l_str, n+1 );
 12             loop
 13                exit when l_piece is null;
 14                dbms_output.put_line( substr( l_piece, 1, 
                                                        250 ) );
 15                l_piece := substr( l_piece, 251 );
 16          end loop;
 17     end loop;
 18  end;
 19  /



use p instead of dbms_output.put_line until you get to a release where the linesize can be 32k.

Problems with SQL Function calls

John Davey, March 11, 2009 - 9:29 am UTC

I have been running something similar to your script (well - similar-ish) that depends upon the link between sql_address in v$session and the v$sql,v$sqltext etc views.

However we recently changed some code to modularise it into PL/SQL from a GUPTA interface ( good ! ..) but implementation was not as straightforward as anticipated and in the end the effective code , in the form of a packaged function, was called via a SELECT statement in the form 'SELECT package_main.main FROM dual' ( bad .. ) . We have had problems and it has been impossible to use existing monitoring scripts. This is because the sql_address in v$session is now invariant and points solely to the 'SELECT package_main.main FROM dual' statement.

I am assuming we have two choices if we want to monitor properly:-

i) re-engineer so we don't have to use a sql function call in a select statement

ii) instrument heavily on a more or less line by line basis


Am I right ? Or is there an alternative way of tracking progress in the v$ views ( we are on 10.2.0.4)

John Davey
Tom Kyte
March 12, 2009 - 7:41 am UTC

I would suggest both i and ii as being "something that should be done regardless".

But, not sure what you mean, I ran:

ops$tkyte%ORA10GR2> create or replace function f return number
  2  as
  3          l_cnt number;
  4  begin
  5          select count(*) into l_cnt
  6          from (select 1 from all_objects, all_objects);
  7
  8          return l_cnt;
  9  end;
 10  /

Function created.

ops$tkyte%ORA10GR2> select f from dual;



in one session and my "showsql" script shows:
ops$tkyte%ORA10GR2> @showsql

USERNAME        SID_SERIAL      STATUS     MODULE          ACTION
--------------- --------------- ---------- --------------- ---------------
CLIENT_INFO     LAST_CALL_ET
--------------- ------------
OPS$TKYTE       '145,497'       ACTIVE     SQL*Plus
                           0

OPS$TKYTE       '159,458'       ACTIVE     5 rows of 5
gen_data T                48


2 rows selected.

--------------------
OPS$TKYTE(159,458) ospid = 14841 command = 3 program =
sqlplus@localhost.localdomain (TNS V1-V3) dedicated server=1020
Tuesday   16:44  Thursday  06:33 last et = 48
SELECT COUNT(*) FROM (SELECT 1 FROM ALL_OBJECTS, ALL_OBJECTS)


meaning - the sql that runs in the function called from sql overrides the main sql from the outside - I don't see select f from dual, I see the sql the function is running.


Please clarify or demonstrate how to reproduce.

My results are different

John Davey, March 16, 2009 - 9:01 am UTC

Tom

We are running 10.2.0.4 on OEL.

For the same function, followed by running showsql, I get :-

****************

TRACKER 153 3371 3365 ACTIVE

&

--------------------
TRACKER(153,3371) ospid = 3365 program = sqlplus@ct-db.charttrack.local (TNS V1-V3)
Monday 12:21 Monday 12:27 last et = 3
select f from sys.dual


********************
Our results appear to be somwhat different.

Regs


Supplemental to last entry

John Davey, March 16, 2009 - 1:30 pm UTC

Not exactly sure I'm running the same sql as you, to judge by the column output you show here. I'm using the code right at the top of the article :-


************

column username format a20
column sql_text format a55 word_wrapped

set serveroutput on size 1000000
declare
x number;
begin
for x in
( select username||'('||sid||','||serial#||
') ospid = ' || process ||
' program = ' || program username,
to_char(LOGON_TIME,' Day HH24:MI') logon_time,
to_char(sysdate,' Day HH24:MI') current_time,
sql_address, LAST_CALL_ET
from v$session
where status = 'ACTIVE'
and rawtohex(sql_address) <> '00'
and username is not null order by last_call_et )
loop
for y in ( select max(decode(piece,0,sql_text,null)) ||
max(decode(piece,1,sql_text,null)) ||
max(decode(piece,2,sql_text,null)) ||
max(decode(piece,3,sql_text,null))
sql_text
from v$sqltext_with_newlines
where address = x.sql_address
and piece < 4)
loop
if ( y.sql_text not like '%listener.get_cmd%' and
y.sql_text not like '%RAWTOHEX(SQL_ADDRESS)%')
then
dbms_output.put_line( '--------------------' );
dbms_output.put_line( x.username );
dbms_output.put_line( x.logon_time || ' ' ||
x.current_time||
' last et = ' ||
x.LAST_CALL_ET);
dbms_output.put_line(
substr( y.sql_text, 1, 250 ) );
end if;
end loop;
end loop;
end;
/

column username format a15 word_wrapped
column module format a15 word_wrapped
column action format a15 word_wrapped
column client_info format a30 word_wrapped

select username||'('||sid||','||serial#||')' username,
module,
action,
client_info
from v$session
where module||action||client_info is not null;

*****************************

I don't know if you are running something substantially different.

Regs
John
Tom Kyte
March 17, 2009 - 10:29 am UTC

Basically the same underlying queries, just different formatting


and looking at it, wow, is it old :) listener.get_cmd - that is from 7.3 and before.... From the old old sql*text architecture when they used a "pipe server" to process queries.


column username format a15 word_wrapped
column module format a15 word_wrapped
column action format a15 word_wrapped
column client_info format a15 word_wrapped
column status format a10
column sid_serial format a15
set feedback off
set serveroutput on

set feedback on
select username, ''''||sid||','||serial#||'''' sid_serial, status , module, action, client_info,
last_call_et
from v$session
where username is not null
/
set feedback off

column username format a20
column sql_text format a55 word_wrapped

set serveroutput on size 1000000
declare
    x number;
    pid number;
procedure p ( p_str in varchar2 )
is
   l_str   long := p_str;
begin
   loop
      exit when l_str is null;
      dbms_output.put_line( substr( l_str, 1, 250 ) );
      l_str := substr( l_str, 251 );
   end loop;
end;
begin
    for x in
    ( select username||'('||sid||','||serial#||
                ') ospid = ' ||  process ||
                ' command = ' || command ||
                ' program = ' || program username,
             to_char(LOGON_TIME,' Day HH24:MI') logon_time,
             to_char(sysdate,' Day HH24:MI') current_time,
             sql_address, LAST_CALL_ET, sid, paddr
        from v$session
       where status = 'ACTIVE'
         and sid <> ( select sid from v$mystat where rownum = 1 )
         and rawtohex(sql_address) <> '00'
         and username is not null order by last_call_et )
    loop
        select max(spid) into pid from v$process where addr = x.paddr;

        dbms_output.put_line( '--------------------' );
        dbms_output.put_line( x.username || ' dedicated server=' || pid );
        dbms_output.put_line( x.logon_time || ' ' ||
                          x.current_time||
                          ' last et = ' ||
                          x.LAST_CALL_ET);
        for y in ( select sql_text
                     from v$sqltext_with_newlines
                    where address = x.sql_address
                      order by piece )
        loop
            p( y.sql_text );
        end loop;

    end loop;
end;
/

set feedback on

No difference

john davey, March 20, 2009 - 12:57 pm UTC

Tom

No difference. I still get the same as you expected I suppose. I've been working round the issue by selecting from v$sql where users_executing > 0 and 'guessing' (in an educated way using the program_id) ) the state of play.

regs

JBD

Tracking Performance of SQL over Time

Robert, September 10, 2009 - 10:23 am UTC

Hi Tom,

<< Hoping this is on topic >>

How can I track the performance of a given query over time?
Can this be done with OEM Grid Control (performance tuning pack)?

Thanks,

Robert.
Tom Kyte
September 14, 2009 - 11:33 am UTC

yes, dba_hist views (part of AWR - the performance/tuning pack) have this.

http://docs.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_3183.htm#sthref1599

Do we have to write our own queries?

Robert, September 15, 2009 - 4:39 pm UTC

Tom,

Do we have to write our own queries for DBA_HIST_SQLSTAT, or can this be done via OEM Grid Control front end?

If our own queries, would you give a simple example against DBA_HIST_SQLSTAT?

I've written many of these types of queries against STATSPACK data... but the DBA_HIST_SQLSTAT comes with 'built-in' DELTA data... for example, how do we use this data in our homemade queries?

Thank you,

Robert.
Tom Kyte
September 16, 2009 - 8:44 am UTC

... for example, how do we use
this data in our homemade queries?
...

the same way you did against statspack, you get to understand the data (we document these views) and then you query it.

But yes, Enterprise manager exposes all of this data for you, as would @awrrpt (a text way to do it like statspack would do)

Proving Poor Scalability

Robert, September 16, 2009 - 1:34 pm UTC

Tom,

We have an important 3rd party application which is designed on the (very 'flexible', but) poorly scalable design of tables with attribute name/value pairs.

I want to show folks that as the size of the tables grow by a factor of 'n' that the SQL load on the server (cpu) for queries using these tables is increasing by a factor of 'nx'... or 'n^x' (e.g. because every query has to do at least 3 or 4 self-joins to various tables).

I think DBA_HIST_SQLSTAT will have all the info I need.
Do you think I am on the right track with this?

Thanks,

Robert.
Tom Kyte
September 16, 2009 - 5:43 pm UTC

yes it would - for the query performance.

DBA_HIST_SEG_STAT would be useful for judging the size over time

Balaji

Balaji, November 17, 2009 - 1:20 am UTC

Dear Tom,

Appreciate your support and interest. We upgraded to 10R2. 10.2.0.4 Patch set 24. 

The following error occurs while using your showsql Code. 
Please advice. 

SQL> declare
  2      x number;
  3  begin
  4      for x in
  5      ( select username||'('||sid||','||serial#||
  6                  ') ospid = ' ||  process ||
  7                  ' program = ' || program username,
  8               to_char(LOGON_TIME,' Day HH24:MI') logon_time,
  9               to_char(sysdate,' Day HH24:MI') current_time,
 10               sql_address, LAST_CALL_ET
 11          from v$session
 12         where status = 'ACTIVE'
 13           and rawtohex(sql_address) <> '00'
 14           and username is not null order by last_call_et )
 15      loop
 16          for y in ( select max(decode(piece,0,sql_text,null)) ||
 17                            max(decode(piece,1,sql_text,null)) ||
 18                            max(decode(piece,2,sql_text,null)) ||
 19                            max(decode(piece,3,sql_text,null))
 20                                 sql_text
 21                       from v$sqltext_with_newlines
 22                      where address = x.sql_address
 23                        and piece < 4)
 24          loop
 25              if ( y.sql_text not like '%listener.get_cmd%' and
 26                   y.sql_text not like '%RAWTOHEX(SQL_ADDRESS)%')
 27              then
 28                  dbms_output.put_line( '--------------------' );
 29                  dbms_output.put_line( x.username );
 30                  dbms_output.put_line( x.logon_time || ' ' ||
 31                                        x.current_time||
 32                                        ' last et = ' ||
 33                                        x.LAST_CALL_ET);
 34                  dbms_output.put_line(
 35                            substr( y.sql_text, 1, 250 ) );
 36              end if;
 37          end loop;
 38      end loop;
 39  end;
 40  /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind
ORA-06512: at line 4

The Code worked fine in 9i, we recently moved to 10.2.0.4

Tom Kyte
November 23, 2009 - 1:41 pm UTC

I cannot reproduce, but here is what I'd like you to do.

make it be as small as possible, remove all code that does not contribute of the error - eg: lines 16-37 probably (replace with NULL), as well as 1 and 2.

Then, remove bits of the sql statement that do not apply - comment out the columns one by one until you have the smallest number of columns possible and still reproduce.


then, post that code, along with that sql statement just run in sqlplus - outside of plsql - so we can see the output - and tell us about the character set of your database and anything else you can think of that might be relevant.

Balaji, November 23, 2009 - 11:37 pm UTC

Dear Tom,

Thank you very much for your response. We migrated to UTF 8 character set last year.

As per you advice, I tried chomping down no. of columns and found that sqladdress is the culprit which is RAW datatype.

By replacing the sqladdress with sql_id the code worked.

Thanks a lot for your support.




DECLARE
x NUMBER;
BEGIN
FOR x IN (SELECT a.inst_id,
a.username
|| '('
|| a.SID
|| ','
|| a.serial#
|| ') ospid = '
|| b.spid
|| ' program = '
|| a.program username,
TO_CHAR (a.logon_time, ' Day HH24:MI') logon_time,
TO_CHAR (SYSDATE, ' Day HH24:MI') CURRENT_TIME,
a.sql_id, a.last_call_et
FROM gv$session a, gv$process b
WHERE a.status = 'ACTIVE'
AND a.paddr = b.addr
AND RAWTOHEX (a.sql_address) <> '00'
AND a.username IS NOT NULL
ORDER BY a.last_call_et)
LOOP
FOR y IN (SELECT MAX (DECODE (piece, 0, sql_text, NULL))
|| MAX (DECODE (piece, 1, sql_text, NULL))
|| MAX (DECODE (piece, 2, sql_text, NULL))
|| MAX (DECODE (piece, 3, sql_text, NULL)) sql_text
FROM gv$sqltext_with_newlines
WHERE sql_id = x.sql_id AND piece < 4)
LOOP
IF ( y.sql_text NOT LIKE '%listener.get_cmd%'
AND y.sql_text NOT LIKE '%RAWTOHEX(SQL_ADDRESS)%'
)
THEN
DBMS_OUTPUT.PUT_LINE ('--------------------');
DBMS_OUTPUT.PUT_LINE (x.sql_id || ' ' || x.username);
DBMS_OUTPUT.PUT_LINE ( x.logon_time
|| ' '
|| x.CURRENT_TIME
|| ' last et = '
|| x.last_call_et
);
DBMS_OUTPUT.PUT_LINE (SUBSTR (UPPER (y.sql_text), 1, 255));
END IF;
END LOOP;
END LOOP;
END;
/


I prefer the above to view the plan output by using
SELECT * FROM TABLE (DBMS_XPLAN.display_cursor ('&SQL_ID'))
-----------------------------------------------------------

Then I included the RAWTOHEX in the column list of select , which worked as well.


DECLARE
x NUMBER;
BEGIN
FOR x IN (SELECT a.inst_id,
a.username
|| '('
|| a.SID
|| ','
|| a.serial#
|| ') ospid = '
|| b.spid
|| ' program = '
|| a.program username,
TO_CHAR (a.logon_time, ' Day HH24:MI') logon_time,
TO_CHAR (SYSDATE, ' Day HH24:MI') CURRENT_TIME,
RawtoHex(a.sql_address) sql_address, a.last_call_et
FROM gv$session a, gv$process b
WHERE a.status = 'ACTIVE'
AND a.paddr = b.addr
AND RAWTOHEX (a.sql_address) <> '00'
AND a.username IS NOT NULL
ORDER BY a.last_call_et)
LOOP
FOR y IN (SELECT MAX (DECODE (piece, 0, sql_text, NULL))
|| MAX (DECODE (piece, 1, sql_text, NULL))
|| MAX (DECODE (piece, 2, sql_text, NULL))
|| MAX (DECODE (piece, 3, sql_text, NULL)) sql_text
FROM gv$sqltext_with_newlines
WHERE address = x.sql_address AND piece < 4)
LOOP
IF ( y.sql_text NOT LIKE '%listener.get_cmd%'
AND y.sql_text NOT LIKE '%RAWTOHEX(SQL_ADDRESS)%'
)
THEN
DBMS_OUTPUT.PUT_LINE ('--------------------');
DBMS_OUTPUT.PUT_LINE (x.sql_address || ' ' || x.username);
DBMS_OUTPUT.PUT_LINE ( x.logon_time
|| ' '
|| x.CURRENT_TIME
|| ' last et = '
|| x.last_call_et
);
DBMS_OUTPUT.PUT_LINE (SUBSTR (UPPER (y.sql_text), 1, 255));
END IF;
END LOOP;
END LOOP;
END;
/

Please advice

Thank you.



V$ for literal data versus bind variables

Marcus, December 02, 2009 - 4:15 pm UTC

Is there a view that will show the literal SQL run? What I mean is that when I select from V$SQL, the SQL_TEXT looks like : INSERT INTO TABLE1 (COL1,COL2) VALUES (:1,:2)

I need to see the actual data submitted.
e.g. INSERT INTO TABLE1 (COL1,COL2) VALUES ('FirstVal',12)

What I am trying to get is a list of insert, update or delete statements run against one schema and run those same SQL statements against a second schema in the same order of execution. I am hopeful to be able to write something like:

Select SQL_FULLTEXT
from V$SQL
where FIRST_LOAD_TIME > SYSDATE-(1/24) AND (SQL_TEXT like 'INSERT%'...)
order by FIRST_LOAD_TIME

This recordset would be sent via a web service to schema2, which would process the statements.

Is this poosible?
Tom Kyte
December 04, 2009 - 9:35 am UTC

we do not track that minutiae, can you imagine how large v$sql would be otherwise? It would not be able to contain everything. We execute in general thousands of statements per second - each with different inputs.

Now that I read your full posting, I'll ask a very basic question:

instead of telling me how you think you want to do something, ask me how do I accomplish <your GOAL, not method, goes here>


To, what are you trying to do (do not simply repeat what you said, tell me your ultimate goal, NOT how you think you must achieve this goal)

V$ for literal data versus bind variables

Marcus, December 04, 2009 - 2:01 pm UTC

Yea, I was of the thinking that it would not be stored that way. I understand that reasoning.

I have two database schemas separated by networks. They are connected via a guard. schema1 has data that is interacted with on that network. Schema2 needs all of the data from schema1 as well as any additional data that resides on that network. We can send requests to schema1, but data flow is only one way. So, all data comparison for the "merge" is done on schema2.

So far the logic for inserts and updates is understood. Our problem is how to track and manage deletes from schema1 and reflect those same changes in schema2. If you have a direction to suggest, I am open to exploring.
Tom Kyte
December 04, 2009 - 5:37 pm UTC

streams for unidirectional replication.

or materialized views even.

read about replication, it already exists, you do not need to reinvent it.


http://www.oracle.com/pls/db102/search?remark=quick_search&word=replication&tab_id=&format=ranked

streams for unidirectional replication

Marcus, December 07, 2009 - 10:45 am UTC

That is something I will review much further. Thank you for the link.

In first review, I see:
"Configure your network and Oracle Net so that the str1.net database can communicate with the str2.net database"

We do not have a direct link possibility. We have to migrate the changes from schema1 to schema2 via web services (limited by the rules of a guard).

Do you see this as a show stopper, or will this mechanism still allow "capture" on schema1, pass data into a web service, and then have schema2 read that web service and "apply" the changes?
Tom Kyte
December 07, 2009 - 3:21 pm UTC

... We have to migrate the changes from
schema1 to schema2 via web services (limited by the rules of a guard).

...

good luck with that. No one here will be able to help you. You'll be reinventing technology and features that have been part of the database for over 16 years.

In other words, you are going to write a lot of stuff that won't work correctly - won't work at all sometimes - will be entirely unreliable - and will keep you stuck in the same place in your job *FOREVER* because you will be the only one that can tweak or tune the beast.

been there, done that, never going to that play again.

We do not have a direct link possibility.

That (don't take this personally) is an outright lie.

You have chosen to make the simple (secure and securable and auditable and whatever-able) solution not possible.

CHOSEN.

It is not being forced on your company, your company has made this choice.

And it will cost them lots.
And give them nothing in return.



all in the name of security - we'll let arbitrary web services go through but not identifiable, verifiable traffic. Great.

Less secure, but harder to use. All a big bonus from where I'm sitting (not really)

streams for unidirectional replication

Marcus, December 11, 2009 - 8:45 am UTC

I could not agree more. It is an artificial constraint imposed by a customer. We are working on correcting as much as we can.

I have read through the documentation, but did not see something that we may need. Can we apply some logic rules on the replication process?

e.g. Can the replication provide fidelity to replicate all of the columns in a table except one or two? We need to be able to customize the replication to sync "most" of the data from schema1 to schema2.

Also, would we be able to select which type of dml to "sync"? Could we sync inserts but not updates?
Tom Kyte
December 11, 2009 - 8:51 am UTC

... Can we apply some logic rules on the replication process?
...

yes.

... would we be able to select which type of dml to "sync"?..

yes, you would implement an apply filter to apply what you wanted. You could turn updates into a flag delete of the existing data and an insert if you wanted.

Read about custom apply routines.

how can i get my oca certificate soonest

ifeanyi, December 11, 2009 - 11:52 am UTC

I have been going to clases,and i cant understand some tearminology in oracle,am just new to it and i need the certificate to stand out from the crowd,advice
Tom Kyte
December 14, 2009 - 7:44 am UTC

Sorry, but it takes years to stand out from the crowd. A piece of paper isn't going to do anything for you in a face to face interview.


Education.oracle.com would be the site to go to to explore certification.

Concepts Manual

Rick, December 11, 2009 - 1:39 pm UTC

ifeanyi, read the Oracle Concepts manual, its free online. http://tahiti.oracle.com/

Trace user dmls

A reader, December 13, 2009 - 3:36 pm UTC

I have read the whole thread but it does not quite address a particular blocking scenerio.

Application is heavy OLTP with Front end as oracle forms with thousands of lines of code in various events.

Out of no where comes a blocking where the only workaround we have for now is to kill the blocker session, but that means we have to man the system and there are other $$$ loss in case of a blocking.

We tried all (even went thu the sqls in this thread) but all in vain.

Here is the information we have upfront. When blocking occurs and yes we did put in some proactive means so what I am telling you is after the capture procs in place.

A session runs a dml (which we do not know but it is a dml as seen from v$transaction and v$lock with row exclusive on a table). Then this session do not seem to commit or rollback and goes about running other sqls (like select etc). This goes on say for few minutes until another sesssion wanted to work on that held up row, and then the blocking occurs. By this time we will know thanks to oracle check blocking scripts for waiter and holder. At this time what we have in hand is the dml which the waiter wanted to execute and is waiting for a resource to be free for a table row. While the blocker, well his session is idle for some time, the currentl sql id is empty and prev sql id is a query (select statement -normal not select for update).
In this case we have no clue that the actual update or delete statement the blocker executed. If we known we can search the exact dml syntax in forms and see the code line.
Neverthelss we then kill the blocker. Later we earch the last sql atatement that was recorded in his prev sql id (and the time of this sql statement is far ahead of the star_time in the v$transaction, so for sure the session ran the dml lot earlier, then there must have been a bug or code leak-and session goes on to run other sql queries) and we found that sql sttement in one form, which is a large form and it is coded properly. it has many update to that table but nothing seems wrong, else the form would lock all the time? isnt it?
So how do we solve this issue?

We can not trace the sesssion as its ifweb90 and there are hundreds of it.

Q1: is there a way we can find all pending dmls for a sid?

Q2: tracing the start_time from v$transaction (after joining with v$lock for the blocker sid), gives us an update statement (in sql_redo col) but it has a rowid column and it is reconstructed? how do we get the exact dml from logminer views so we can search it in forms?

Q3: Issue is not even searching, we have around 10 forms that we know for that table (as the blockign always occur on order table) but wewnet thru the forms code and did not find any thin wrong,


We have this issue like once a week.

Cheers

Tom Kyte
December 14, 2009 - 8:39 am UTC

... Out of no where comes a blocking ...

sorry, things do not come "out of nowhere", truly, they do not.


You have identified the blocker session. Ok, you are "heavy OLTP", heavy oltp is characterized by short, fast, small transactions. But, if you can actually identify a blocking session - that means that session IS NOT SHORT, FAST as far as transactions go - is it?


... A session runs a dml (which we do not know but it is a dml as seen from
v$transaction and v$lock with row exclusive on a table). Then this session do
not seem to commit or rollback and goes about running other sqls (like select
etc). This goes on say for few minutes until another sesssion wanted to work on
that held up row, and then the blocking occurs....

Umm, I stopped reading there. You have identified exactly the issue, exactly the problem, it is exactly what is described in virtually every discussion of blocking and locking ever written by anyone anywhere? I'm confused.


... While the blocker, well his
session is idle for some time, the currentl sql id is empty and prev sql id is
a query (select statement -normal not select for update).
...

well, WHO IS this blocker, what is the CODE this blocker executes (you said you were proactive, you should know the CODE that is executing - why don't you?)


q1: if you enable auditing, in this case fine grained auditing, yes. You could also contact support and ask them how to dump a session state before you kill it.

q2: don't go down this path, we do not have a history of everything a transaction has done in memory, in real time, that would be killer performance and memory wise. You are confusing as all get out here since sql_redo is log miner information and has nothing at all to do with v$transaction.


q3: if your forms code was instrumented (proactive), you would know what form - for part of what form - was being idle, was doing the blocking.





Anyway, maybe you should just set an max idle time, you can have the session killed automagically for you - until you find the bug in your code.

Same as old

Shahid, December 13, 2009 - 3:40 pm UTC

Sorry i forgot to put my info when I commented.

I still am not sure

shahid, December 14, 2009 - 10:42 am UTC

<<<Anyway, maybe you should just set an max idle time, you can have the session killed automagically for you - until you find the bug in your code.>>>
I said there are tens of people in the back office using application and many of them can remain idle for a long time until they receive an order request on phone, all this time, they still have the app form in front of their screen open, meaning connected to the database (yes sid will be idle but we can not decide to kill that sid because it is idle).

<<<<q3: if your forms code was instrumented (proactive), you would know what form - for part of what form - was being idle, was doing the blocking.>>>
We know which form but it has tens of lines of code and we do not know which exact DML is holding the block. Now we have looked at each line and all seemed in order, exceptions are coded properly as well commit and rollback.If this was not the case we have blocking more often.

What we wanted to know the EXACT DML of the blocker so that we can search that string in forms. See later in the email.

<<<<
q2: don't go down this path, we do not have a history of everything a transaction has done in memory, in real time, that would be killer performance and memory wise. You are confusing as all get out here since sql_redo is log miner information and has nothing at all to do with v$transaction.
>>>
I am not confused at all. Allow me to elaborate.

Please Look at the following sequence to better understand our situation.

1. When the blocking happens we run the sql query which gives us the blocker sid and the waiters sid).

2. Select * from v$session where sid=<blocker sid>
sql_id is null, and prev sql_id always point to a select statement which is not the statement which locked the row, hence we do not know what dml blocker has executed that caused the blocking.

3. Select * from v$lock where sid=<blocker_sid>
we see tm and tx rows. This means that the bloker sid has ran one dml which locked a row in the table (on which the waiters are waiting) and perhaps due to a bug or something, commit or rollback has not followed, and the blocker is also not waiting for the dml to complete because his last sql as stated above is the sql query. Now joining it with v$transaction we get the xidusn, xidslot, xidsqn.
These values will be used later when perfoming log mining to identify the sql_redo. But that sql_redo is reconstructed and with a rowid, hence again we failed to find the syntax of the exact dml that blockers issues.
4. We then killed the blocker.

Now you undertand why we can not find which statement has caused the blocking?

Cheers



Tom Kyte
December 14, 2009 - 10:51 am UTC

...
I said there are tens of people in the back office using application and many
of them can remain idle for a long time until they receive an order request on
phone, all this time, they still have the app form in front of their screen
open, meaning connected to the database (yes sid will be idle but we can not
decide to kill that sid because it is idle).
...

then that means your application has given the ability to the user to

a) start a transaction (lock data)
b) sit there for a long long long time and keep it locked. To sit there as long as they want.

Perhaps your coders need to look into adding something in the application (like a TIMER that they create when they start a transaction and expires in N minutes and tells the user "you took too long, goodbye")



... We know which form but it has tens of lines of code and we do not know which
exact DML is holding the block....

you know the table - what more do you need? You know the form, you know (therefore) the function, you know the table, you know exactly where the 'bug is', where the user can start a transaction and leave it open for *as darn long as they like*


... What we wanted to know the EXACT DML of the blocker so that we can search that
string in forms....

It doesn't have to be a single DML, it could be many DML's all working on the same or similar data. And what happens when you find there are 50 DMLS on the table - you still *DO NOT KNOW*. If your developers know your application, they should in fact be able to answer this.




You did not mention you were using logminer - that is new information. I presumed you must be - but you never provided that information. We do *not* record the sql a session executes unless you

a) enable fine grained auditing
b) enable sql_trace
c) log it yourself using trace ( proactive INSTRUMENTATION your coders would have coded )


You'll never find the statement because

a) we don't record it
b) you don't record it
c) it likely isn't "a dml", it is likely "a set of N dmls that might be the statement"


but you know what - it isn't even relevant - it doesn't matter. You don't care WHAT statement it is.

What you care about is only

a) people can start a transaction and lock data
b) that data is wanted by others
c) the person in (a) is never made to commit, they can keep that open forever


You need to fix (a), you do not need to know the table, you do not need to know the function even, you only need to know that (a) you can open a transaction and never commit or roll it back within a span of time.

That, that is the only thing you need to fix. Do not go down your current path, the bug is you allow people to open a transaction "open ended", for as long as they like. You don't want that - that is the bug - fix that.


shahid, December 14, 2009 - 12:40 pm UTC

<<<<You need to fix (a), you do not need to know the table, you do not need to know the function even, you only need to know that (a) you can open a transaction and never commit or roll it back within a span of time.

That, that is the only thing you need to fix. Do not go down your current path, the bug is you allow people to open a transaction "open ended", for as long as they like. You don't want that - that is the bug - fix that. >>>

I concur with your statements above and these are true in all development code which goes thru quality check.
But you have been making general statements to my problem which are by the way true, however I though when we have pin pointed the problem to such a state, perhaps we can better have a way like oracle will point us to the line of code that generates the lock (if not the statement).
Irony is that the code has been reviewed by developers many times and I have myself reviwed the code and it looks pretty much ok. You have a block of if elsif and under each block you do an update to the order table. Outside the block you use if %rowcount <=0 rollback else commit and thats about it. In the exception rollback is issued.
For sure it is an application bug but then how to resolve it? Yes we may not need to know the exact update statement (its actually one only dml because in all cases, v$lock contains only one transaction for that sid and it is a row exclusive lock) and as long as you reach to a block in the form , you should be able to track it. Guess even knowing this, we are at a loss.

Many thanks for your help and I appreciate your comments.

If I am successful in finding a solution and the cause, I will update this thread for your kind information.

Have a good day
Cheers

Tom Kyte
December 14, 2009 - 3:29 pm UTC

we have no idea, clue, bearing on what line of code caused it - all we know is

a) session a did something to lock row X
b) session b wants to lock row X

this is the most basic, died in the wool case of blocking/locking. You don't need to know the SQL statement that caused it (even though your developers should be able to find it - based on their intimate knowledge of the *application code*, something the database has no clue about). You just need to know a) user can open transaction and b) they do not have to close it - ever.



Just put a timer in place, query v$transaction and look for transactions owned by the SID of the current session (create them a nice view that joins v$transaction with v$session for their sid). If they query the v$transaction stuff, they get the start time, they can monitor how long they've been in a transaction and they can report in a log somewhere what they were doing in a transaction that is lasting so long.

If the user has access to a block, they almost surely have access to modify data (outside of your process). Your commit and rollback logic isn't sufficient - you have the ability to start transactions without your code undoubtedly in the form.

Shahid, December 15, 2009 - 1:04 am UTC

Bingo, thats what we have done yesterday. That is created a job that runs every minute to check for a prolonged locking from ifwen90.exe session (>=20 secs, usually in oltp even 5 secs is a killer but based on our prev knowlege the lock goes for about 2 minutes before blocking occurs).
We then dump data from various tables into a report (from v$transaction, v$session, v$lock, v$open_cursor.)
Interestingly today just a short while ago we received such an alert (we set the job to send us an email). We did not create a fix it job yet, but we managed to kiss the session JUST ABOUT when the blocking started (that was at the 200 secs- we have decided that for now we will attach a fix it job that will kill the blocker after 60 secs of cont log and it is accepted by the business as no ifweb90 session should hold a lock more than even 5 secs in ideal case).
Anyway coming back to our findings from the script we put in , we found the transaction start time and the logon time of that ifwen90, we match the logon time to the activity table (app table) to trace which logical userid (physical person) logged in. However we found nothing special that he was doing, neverthless developers are working from this point forward, lets hope something came up, But atleast from dbas point of view we gave them a good area to start, while earlier we were not having any clue.

Thanks for your help.
Cheers


How much Instrumentation?

Tom, July 07, 2010 - 10:59 am UTC

I am trying to determine how much instrumentation is warranted in a government application that I am modernizing in which I have no read access in production. I am currently purposing to capture all package.procedures calls, passed parameters and dynamic sql to a logging table, exceptions to an exception table. A report will execute when an error occurs and email support a report (code, message, stack trace…) and the package.procedure, paramaeter list, and dynamic sql that occurred for the user around an interval parameter currently set to 5 minutes. I am trying to get read access to the system since I know that reproduction of errors without the underlying data can be difficult at best. I am struggling with how much implementation to put into the code. I have seen instrumentation that has an “enter and exit” for each procedure/function call, other logging around certain transactions. I am considering putting logging around every transaction that would log all SQL statements not just dynamic and all parameters so that an error report would deliver almost everything needed to reproduce the problem except for the base data. This would notify us of a problem before a user reports the issue and allow for us to determine a fix as quickly as possible. We would most likely only need to log into the production system (if we can get access) and execute the query with the supplied parameters to reproduce the problem, provided that it is an issue that is reproducible in the database.

Do I need an “enter and exit” log for every procedure/function? Do you see value in this type of logging (kind of reminds me of table APIs for every table)? Does Oracle provide logging functionality out of the box where a support person could gain access without going through a DBA (10.2 version)? I am already getting push back from their technical lead about the overhead of code Instrumentation (java guy). I will be utilizing your comments here at ask tom and your blogs to try to squash his voice. What am I missing to be as instrumented as possible? Most of what I have added has come from ideas from your AskTom site and book.

Thank you for your advice!


Tom Kyte
July 08, 2010 - 12:04 pm UTC

I had a habit of making every other line of code instrumented.

... to a logging table...

I would not do that, I would have switches that would let me optionally write it out to a table when I wanted it - it would not use a table.

logging the errors - great, perfect - but (and this is important) do it ONLY at the top level, not at the bottom level of the code. That is, the stored procedure called by the client should be:

create procedure p
as
begin
...
exception when others
then
log_error( ..... ); -- this is an autonomous transaction that logs it and commits the logged error
RAISE;
end;


you should not have a when others anywhere else in the code. Then, when you do have an error that is reported by the end user ( a trouble ticket is generated ), you would have an administrative screen that permits you to enable tracing for that user and they are asked to reproduce the bug. That is the trace you use to see what is going on.

If you have tracing always on, you would end up doing more work tracing than work done for real.

you might be interested in this package
http://tylermuth.wordpress.com/2010/04/16/logger-1-3-0-released/


How much Instrumentation?

Tom, July 08, 2010 - 1:34 pm UTC

Thank you for your time! :)
I will proceed with your recommendations.

SONU, June 10, 2011 - 6:28 pm UTC

Hi Tom,

Is it possible that log_error procedure can have value of the procedure p's parameter? (in this example there is not parameter but lets say there is a parameter p1 and its value is 10 when exception occured and I want that value in log_error procedure without passing this parameter value ? i know when we call log_error at that time we can pass this value but I do not want to pass.. is it possible? I think its not but still want to check as oracle is very big and I have little knowledge.
Tom Kyte
June 11, 2011 - 2:32 pm UTC

you will have to pass, there is no meta data that describes the inputs

sonu, June 14, 2011 - 7:30 pm UTC

Ok, thanks tom!.
Is it possible to access one variable value whose name contains in another variable?

ie. let say

v1 varchar2(10); -- will hold name of variable
v2 number := 5;

begin
v1:= 'v2'; -- store name of variable.

now I want to access value 5 of variable v2 by using variable v1. is it possible?

such thing we do in unix shell or batch command..
Tom Kyte
June 17, 2011 - 11:55 am UTC

not in plsql. Not unless it is a package global variable anyway. You can do it with them - using dynamic sql - but it isn't anything you really want to be doing.


If you need an associative array - so you can have "key value pairs", you can do that with a plsql index by table:

type myarray is table of varchar2(30) index by varchar2(30);


l_data myarray;

....

l_data( 'v2' ) := 'some value';


@Sonu

Abhisek, June 15, 2011 - 1:59 am UTC

declare
v1 varchar2(10) := 'ABC'; -- will hold name of variable
v2 number := 5;

begin
DBMS_OUTPUT.PUT_LINE(v1);
v1:= 'v2';
DBMS_OUTPUT.PUT_LINE(v1);
v1:= v2;
DBMS_OUTPUT.PUT_LINE(v1);
end;


OUTPUT:
ABC
v2 --Note: there is no variable replacement
5 --Note: When not in quote, variable replaced

In unix, if we use sigle quotes, no interpolation of variable occurs and is considered as string:
v1=5;
v2='ABC'
v1='$v2'
echo $v1
v1="$v2"
echo $v1
echo $v2

Output
$v2 --Note: When in single quote, no variable replacement
ABC --Note: When in double quote, variable replaced
ABC

Unix behave same as oracle in this case. We are assigning the value of a variable to other variable. when a string is assigned, string is displayed.

Hope it helps.

sonu

sonu, June 15, 2011 - 5:31 pm UTC

thanks abhisek for your answers

I think I missed some information in my questions.. sorry for that.. what I want is this.

v1 varchar2(50) := 'Main';
v2 number := 5;
v3 varcha2(10) := 'Name';

Now v1 := function () -- this function will return 'v2' ||'#'|| 'v3 -- this is nothing but combination of two variables's name and store them into one variable.

dbms_output.put_line (v1) will print "v2 ||'#'|| v3" but I want to print 5#Name
Tom Kyte
June 17, 2011 - 1:21 pm UTC

see above, if you want that, you can use an associative array - otherwise, no.

@Sonu

A reader, June 17, 2011 - 3:19 am UTC

Hi Sonu,

create or replace function chkDemo
return varchar2
as
v2 number     := 5;
v3 varchar2(10)     := 'Name';
begin
return v2 ||'#'|| v3; --Note: not using 'v2' || '#' || 'v3'
end; 

declare
v1 varchar2(50)    := 'Main';
begin
v1 := chkdemo();
dbms_output.put_line (v1);
end;


In case you have some other issues with it I will wait till Mr. Tom replies you as I may not be able to understand you.

sonu

sonu, June 17, 2011 - 7:35 pm UTC

Thanks A reader

see problem is the variable's value are not in chekdemo function it is in my anonyms block/procedure.

my actual problem is this: checkdemo function know from where I called and what are the parameters in that procedures. checkdemo doesn't know what are the values of that parameters. So checkdemo will just return all the parameters. (now this function can be called from different procedures and different procedures will have diffrent parameters so becuase of that I am making one string of the variables and return that string. so calling program will get one string which is nothing but the list of parameters and then I want to pass this parameters string to other procedure but with its value not with name.
hope it makes clear now. as per my knowledge I know it is not possible but just want to check with Tom or others as it may possible.

thanks everyone

Tom Kyte
June 20, 2011 - 9:31 am UTC

not possible, checkdemo will have to be passed formal, named parameters.

stored procedure call logging

BC, December 06, 2011 - 9:22 am UTC

Tom,

One of the web pages that we support executes uses a stored procedure to perform data searches based on the criteria entered from the web page. Based on the parameters entered this stored procedure builds a dynamic sql statements ( Using Bind Variables, Of Course ). All parameters received by and any errors generated from the stored procedure are logged in a table. This is done by calling a separate procedure within the package that creates an autonomous transaction to do the inserts.

Since this procedure is executed over 6000 times per hour,
Can we use a different approach to insert the data, perhaps like submitting job so that the stored procedure returns quicker to the calling Java program ?

Thanks

BC



Tom Kyte
December 06, 2011 - 11:29 am UTC

submitting a job - with all of the inputs needed to be inserted - would like take much longer than....

just inserting them.


Just make sure you are using bulk operations so you can do the insert in a single execution - don't insert the parameters one at a time, use forall to insert them all at one. Have as few indexes as humanly possible on the affected tables

tracking SQL

Keir, December 07, 2011 - 4:44 am UTC

Hi Tom,
First of all, thanks for your help over the years, I have learned a great deal from your insight.

I am working with 11.2 and am trying to track what an application/single user does on our database. Ideally I would like a sequential list of the queries run (including bind variables) by this one user.

I've successfully created a trace file with dbms_monitor and captured all the bind variables which looks promising. Using tkprof with the RECORD option *should* then output all the sql queries used which it does but only distinct queries (as opposed to an complete list with repeats) and it does not include the bind variables.

Is there another way of tracing another session to see what commands they are running? I have looked at auditing and other tracing techniques but to no avail.

Apologies if this if I'm missing something really obvious.

Again, many thanks for your time (and patience!!!) over the
years

Keir
Tom Kyte
December 07, 2011 - 1:23 pm UTC

tkprof aggregate=NO

to get all of the sequential queries in order.

Stored Procedure Call Logging

bc, July 30, 2012 - 8:34 pm UTC

Tom,

Back to my question about "stored procedure call logging" from December 6, 2011 - 9am Central time zone

I am confused by your response, The procedure receives about 30 parameters, it uses this to build the dynamic sql statements
( Using Bind Variables, Of Course ), These parameters are recorded in a table one record that consists of 30
colums for parameter values and 3 ( PK + audit ) additional columns. This is then inserted by a procedure with
autonomous transaction. Since we are inserting one record and then performing a commit, we are wondering if this
would cause database performance issues due to frequent commits. How do we measure the affect of this ?

What would an alternative approach to record these call be ? Message Queue ? Is there something builtin 11.2 that we could use ?

Is my concern baseless ?

Tom Kyte
July 31, 2012 - 11:58 am UTC

Anything that made them persistent would be subject to a commit - a message queue, anything.


In plsql, commits are asynchronous (see
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1415454871121#20061201507022
for a further explanation ) so you will not incur log file sync waits for these in general.

The overhead will exist, but should be nominal.


I'll stick with my response above, just do the insert. If you are storing the parameters in a "key value" pair table - make sure to bulk forall insert them. But just insert - it'll take a lot less work than submitting a job (which needs be committed too) or a message in a queue (which needs be committed too) or <pretty much anything else>


Every page on asktom does this already - APEX automates this all - they just insert.


11:25 PST

Karthik.K, August 01, 2012 - 1:29 pm UTC

hi Tom,

let me tell you i am a nee bee in Oracle DBA. For the showsql.sql script i do not want the serial and SID in place of username instead i need the original username or owner name there and is there a chase to include sql_text and track changes made to the job which is running from long hours

Please help
Tom Kyte
August 01, 2012 - 3:24 pm UTC

the username is already there??

sql status

Big Fan, September 19, 2013 - 7:35 pm UTC

Hi Tom , how do i know what is the current status of an sql which shows as active in the sessions . it is a huge sql statement showing as running for last 12 hours . how do i know what is it doing now precisely .

it is like --- for i in ( huge 1000 line sql stmt that returns millon rows )
loop
insert into x;
end loop;
will i see the sql in active sessions until the loop is completed ?


execute time of sql and pl/sql

Peter, January 24, 2014 - 6:33 pm UTC

Hi Tom,

Usually we can use DBA_HIST_SQLSTAT and DBA_HIST_SNAPSHOT to get an average execution time of a sql_id(SQL or PL/SQL blocks) in selected snap id. Is there any way to find the maximum and minimum execution time of a sql_id in a selected snap id ?

Thanks,

trace my plsql object as like toad sql monitor

Zenny, February 24, 2017 - 10:14 am UTC

I want to trace a session whatever plsql object run like in toad sql monitor tools which trace from starting point to end point query and also display plsql object with parameter?

how it possible in oracle without using toad sql monitoring tools?
Connor McDonald
February 25, 2017 - 1:17 am UTC

I dont know about Toad monitoring tools, but check out DBMS_MONITOR for selective tracing based on module/action information.

Julian has a nice write up here

http://www.juliandyke.com/Diagnostics/Packages/DBMS_MONITOR/SERV_MOD_ACT_TRACE_ENABLE.php


Zenny, February 25, 2017 - 9:58 am UTC

When I fire below query
select * from v$sql s where s.SQL_ID='cygcj5msrmw75';

here one row found but using above sql_id or address of v$sql and
find session with help of v$session like
select * from v$session where sql_id='cygcj5msrmw75'
then i don't found any row even in v$active_session_history.

so what's another way to found session detail using sql_id?
Connor McDonald
February 26, 2017 - 2:06 am UTC

v$sql contains statements that are running or have been run recently. So its quite possible that no session is *currently* running that SQL, and so you wont see it via queries to v$session.

v$active_session_history (ASH) has a *better* chance of picking up the sql_id and matching to a session, but there is no guarantees because ASH takes a sample of running sessions each second, so if your particular SQL runs quickly or rarely, then it could be missed as well.

If its not appearing in ASH, then the obvious question is - why are you interested in this particular sql_Id ?


Database operations monitor | 12c

Rajeshwaran, Jeyabal, February 04, 2019 - 10:53 am UTC

Team,

Was reading about Real time operations monitor in Oracle 12c @ https://docs.oracle.com/en/database/oracle/oracle-database/12.2/tgsql/monitoring-database-operations.html#GUID-DC573FB7-40C5-4D6D-BE81-EF356900B444

and here is a quick demo from local database.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

demo@ORA12C> select sid,serial#
  2  from v$session
  3  where username ='SCOTT';

       SID    SERIAL#
---------- ----------
       398      31583

demo@ORA12C> variable x number
demo@ORA12C> exec :x := dbms_sql_monitor.begin_operation(dbop_name=>'MY_DEMO2',session_id=>398,session_serial=>31583);

PL/SQL procedure successfully completed.

demo@ORA12C> print x

         X
----------
         2


Then switched to SCOTT schema and ran these two queries.

select /*+ monitor */ owner,object_type,count(*)
from big_Table
group by owner,object_type
order by 3 desc;

select /*+ monitor */ owner,max(object_id),count(*)
from big_Table
group by owner
order by 3 desc
fetch first 5 rows only ;


Now switched back to the DEMO schema and ended the operation like this.

demo@ORA12C> exec dbms_sql_monitor.end_operation(dbop_name=>'MY_DEMO2',dbop_eid=>:x);

PL/SQL procedure successfully completed.



Post that when tried to generate the SQL Monitor report for the entire database operation id = 2, got only one sql-id reported(typically the last executed sql from scott schema) not both sql's, Kindly advise.

demo@ORA12C> set pagesize 0 echo off timing off linesize 1000 trimspool on trim on long 2000000 longchunksize 2000000 feedback off
demo@ORA12C> select dbms_sql_monitor.report_sql_monitor(dbop_exec_id=>2,type=>'text',report_level=>'ALL') from dual;
SQL Monitoring Report

SQL Text
------------------------------
select /*+ monitor */ owner,max(object_id),count(*) from big_Table group by owner order by 3 desc fetch first 5 rows only

Global Information
------------------------------
 Status              :  DONE (ALL ROWS)
 Instance ID         :  1
 Session             :  SCOTT (398:31583)
 SQL ID              :  0p8wjxam3tns9
 SQL Execution ID    :  16777216
 Execution Started   :  02/04/2019 15:56:23
 First Refresh Time  :  02/04/2019 15:56:23
 Last Refresh Time   :  02/04/2019 15:56:25
 Duration            :  2s
 Service             :  ORA12c
 Program             :  sqlplus.exe
 Fetch Calls         :  2

Global Stats
===========================================================================
| Elapsed |   Cpu   |    IO    |  Other   | Fetch | Buffer | Read | Read  |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Calls |  Gets  | Reqs | Bytes |
===========================================================================
|    1.71 |    0.28 |     1.33 |     0.10 |     2 |  18822 |  599 | 147MB |
===========================================================================

SQL Plan Monitoring Details (Plan Hash Value=1236154803)
=============================================================================================================================================================
| Id |          Operation          |   Name    |  Rows   | Cost |   Time    | Start  | Execs |   Rows   | Read | Read  |  Mem  | Activity | Activity Detail |
|    |                             |           | (Estim) |      | Active(s) | Active |       | (Actual) | Reqs | Bytes | (Max) |   (%)    |   (# samples)   |
=============================================================================================================================================================
|  0 | SELECT STATEMENT            |           |         |      |         1 |     +2 |     1 |        5 |      |       |     . |          |                 |
|  1 |   SORT ORDER BY             |           |       5 | 6474 |         1 |     +2 |     1 |        5 |      |       |  2048 |          |                 |
|  2 |    VIEW                     |           |       5 | 6473 |         1 |     +2 |     1 |        5 |      |       |     . |          |                 |
|  3 |     WINDOW SORT PUSHED RANK |           |    907K | 6473 |         1 |     +2 |     1 |        5 |      |       |  2048 |          |                 |
|  4 |      HASH GROUP BY          |           |    907K | 6473 |         1 |     +2 |     1 |       19 |      |       |   6MB |          |                 |
|  5 |       TABLE ACCESS FULL     | BIG_TABLE |    907K | 4009 |         2 |     +1 |     1 |       1M |  599 | 147MB |     . |          |                 |
=============================================================================================================================================================

demo@ORA12C>

Connor McDonald
April 10, 2019 - 10:38 am UTC

I think you're misinterpreting the function here. It is not like (say) a trace where we are capturing every statement. It is more saying that tasks "A", "B" and "C" constitute a single unit of work (eg "hire new employee" might be 3 select's, an insert and 2 updates).

You then call dbms_sql_monitor.report_sql_monitor with the db_op_name/dbop_exec_id pairing to get an assessment of that entire operation.

Database operations monitor | 12c

Rajeshwaran, Jeyabal, February 18, 2019 - 6:55 am UTC

Team - did i missing something here, are you looking for more info? kindly advice

A reader, November 02, 2021 - 7:27 pm UTC

Hi tom
1)when i tried to connect with co.../oracle, I'm getting this error.

ERROR:

ORA-06550: line 1, column 7:

PLS-00201: identifier 'DBMS_OUTPUT.DISABLE' must be declared

ORA-06550: line 1, column 7:

PL/SQL: Statement ignored



2)@?/rdbms/admin/catalog.sql

@?/rdbms/admin/catproc.sql

@?/rdbms/admin/utlrp.sql

@?/rdbms/admin/utlrp.sql

@?/rdbms/admin/utlrp.sql


I followed this, after updating starts the server again

now I can able to connect with all the other users, but I'm getting error in procedures. and I cant see any data when I run the query

can you please help me

Connor McDonald
November 09, 2021 - 3:49 am UTC

I'd suggest creating your database using dbca to ensure everything gets done correctly.

eg

https://connor-mcdonald.com/2019/12/12/dbca-silent-mode-windows/

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library