thanks
July 25, 2001 - 6pm Central time zone
Reviewer: Olga from Canada
The more I work with Oracle the more new things to learn I find ;)
Thank you for this script
How to register
May 14, 2002 - 4pm Central time zone
Reviewer: Anant from usa
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.
Followup May 16, 2002 - 11am Central time zone:
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
May 14, 2002 - 4pm Central time zone
Reviewer: Anant from usa
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
May 16, 2002 - 2pm Central time zone
Reviewer: Anant from USA
Thanks Tom I read it in your book - very nicely explained.
Thanks
Change plan
May 16, 2002 - 6pm Central time zone
Reviewer: A reader
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
Followup May 17, 2002 - 7am Central time zone:
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
November 14, 2002 - 6pm Central time zone
Reviewer: Ted Persky from San Diego, CA USA
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.
Followup November 14, 2002 - 7pm Central time zone:
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!
November 15, 2002 - 2pm Central time zone
Reviewer: Ted Persky from San Diego, CA USA
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...
November 15, 2002 - 3pm Central time zone
Reviewer: Colin Davies from Pasadena, California
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"
Followup November 15, 2002 - 8pm Central time zone:
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
January 16, 2003 - 7pm Central time zone
Reviewer: Stanley from us
Its a good piece of code.
How to find whether any specific package is being executed currently
March 6, 2003 - 1pm Central time zone
Reviewer: kumar from Escondido
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
Followup March 6, 2003 - 3pm Central time zone:
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
March 6, 2003 - 5pm Central time zone
Reviewer: kumar from Escondido
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
Followup March 6, 2003 - 6pm Central time zone:
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 ...
March 7, 2003 - 8am Central time zone
Reviewer: J. Laurindo Chiappa from Sao Paulo, SP Brazil
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
March 7, 2003 - 11am Central time zone
Reviewer: Kumar from Escondido
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
Followup March 7, 2003 - 1pm Central time zone:
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
March 7, 2003 - 2pm Central time zone
Reviewer: J. Laurindo Chiappa from SP, Sao Paulo, Brazil
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
August 15, 2003 - 4pm Central time zone
Reviewer: A reader
Track the Sql
August 23, 2003 - 8am Central time zone
Reviewer: Sri from India
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
Followup August 23, 2003 - 10am Central time zone:
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
October 23, 2003 - 11pm Central time zone
Reviewer: Evan from Atlanta Ga USA
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
Followup October 24, 2003 - 9am Central time zone:
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
November 12, 2003 - 3pm Central time zone
Reviewer: houman from Canada
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
Followup November 12, 2003 - 5pm Central time zone:
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
November 13, 2003 - 3pm Central time zone
Reviewer: Houman
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?
Followup November 13, 2003 - 9pm Central time zone:
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?
March 27, 2004 - 11am Central time zone
Reviewer: j
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?
Followup March 27, 2004 - 4pm Central time zone:
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.
March 28, 2004 - 5am Central time zone
Reviewer: j.
... 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?
Followup March 28, 2004 - 9am Central time zone:
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.
March 28, 2004 - 12pm Central time zone
Reviewer: j.
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)?
Followup March 28, 2004 - 5pm Central time zone:
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
April 30, 2004 - 10am Central time zone
Reviewer: A reader from Herndon, VA
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.
Followup April 30, 2004 - 10am Central time zone:
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
April 30, 2004 - 12pm Central time zone
Reviewer: A reader from Herndon, VA
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.
Followup April 30, 2004 - 5pm Central time zone:
you don't have any triggers on that table do you? and those triggers are not autonomous
transactions are they?
update and showsql
April 30, 2004 - 5pm Central time zone
Reviewer: A reader from Herndon, VA
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
Followup May 1, 2004 - 9am Central time zone:
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
May 1, 2004 - 10pm Central time zone
Reviewer: A reader from Herndon, VA
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.
Followup May 2, 2004 - 10am Central time zone:
(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.
May 2, 2004 - 1pm Central time zone
Reviewer: Subash from India
How to tune PL/SQL ?. Can use please explain the concept with example .
Thanks
Followup May 2, 2004 - 4pm Central time zone:
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:
http://www.amazon.com/exec/obidos/tg/detail/-/1590592174/
I was one of the tech editors on it and know it to be accurate and well written.
update and showsql
May 3, 2004 - 1pm Central time zone
Reviewer: A reader from Herndon, VA
Tom,
1. The reason that I didnt 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 didnt 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.
May 10, 2004 - 12pm Central time zone
Reviewer: A reader
Hi Tom,
Please what means exactly
Parse CPU to Parse Elapsed
Thanks.
Followup May 10, 2004 - 12pm Central time zone:
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.
May 10, 2004 - 3pm Central time zone
Reviewer: A reader
Thanks Tom
ghost SQL and job
July 1, 2004 - 4am Central time zone
Reviewer: A reader
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!!!
Followup July 1, 2004 - 10am Central time zone:
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
July 1, 2004 - 11am Central time zone
Reviewer: A reader
If RMAN has bugs which make restore impossible, stop using it.
July 2, 2004 - 2pm Central time zone
Reviewer: A reader
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
sqls
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.
Followup July 2, 2004 - 2pm Central time zone:
you cannot.
you would have to use fine grained auditing (dbms_fga) or sql_trace=true
Thanks
July 2, 2004 - 3pm Central time zone
Reviewer: A reader
Thanks for your update. How about if I query for sqls 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
Followup July 2, 2004 - 4pm Central time zone:
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
July 2, 2004 - 4pm Central time zone
Reviewer: A reader
Query over db link
August 2, 2004 - 4pm Central time zone
Reviewer: Jeremy from Chicago
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?
Followup August 2, 2004 - 6pm Central time zone:
sure it does? it does for me anyway, can you recheck?
OK
August 3, 2004 - 3am Central time zone
Reviewer: Jacob from CT,USA
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!
Followup August 3, 2004 - 8am Central time zone:
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
August 19, 2004 - 9am Central time zone
Reviewer: A reader from Herndon, VA USA
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.
Followup August 19, 2004 - 10am Central time zone:
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
August 19, 2004 - 11am Central time zone
Reviewer: A reader from Herndon, VA USA
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.
Followup August 19, 2004 - 4pm Central time zone:
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
August 19, 2004 - 2pm Central time zone
Reviewer: The Interested Reader from Chicago, IL, USA
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!
And how to track DML statements?
September 8, 2004 - 5am Central time zone
Reviewer: Teymur Hajiyev from Azerbaijan
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.
Followup September 8, 2004 - 9am Central time zone:
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
September 8, 2004 - 10am Central time zone
Reviewer: Teymur Hajiyev from Azerbaijan
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.
Followup September 8, 2004 - 10am Central time zone:
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....
September 21, 2004 - 4am Central time zone
Reviewer: A reader
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
Followup September 21, 2004 - 7am Central time zone:
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??
November 27, 2004 - 10pm Central time zone
Reviewer: Russell from Australia
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
Followup November 28, 2004 - 9am Central time zone:
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...
November 29, 2004 - 12am Central time zone
Reviewer: Russell from Australia
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???
Followup November 29, 2004 - 8am Central time zone:
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
December 30, 2004 - 5pm Central time zone
Reviewer: A reader
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
Followup December 30, 2004 - 6pm Central time zone:
2 sorts (disk)
anytime sorts (disk) exceeds zero, you wrote and then read from temp
Where'd my statement go?
September 29, 2005 - 8am Central time zone
Reviewer: Neil from London
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!
Followup September 30, 2005 - 7am Central time zone:
(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
September 30, 2005 - 8am Central time zone
Reviewer: Neil from London
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!
Followup September 30, 2005 - 10am Central time zone:
Hmm, I cannot reproduce that - can you define "ordinary piece of sql"?
How about RAC
April 18, 2006 - 9am Central time zone
Reviewer: A reader
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.
Followup April 18, 2006 - 10am Central time zone:
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 ...
April 18, 2006 - 11am Central time zone
Reviewer: Lee Elms from UK
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 ?
Followup April 18, 2006 - 2pm Central time zone:
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
April 18, 2006 - 12pm Central time zone
Reviewer: Michel Cadot from France
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
April 26, 2006 - 5am Central time zone
Reviewer: Lee Elms from UK
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 (?) ...
Followup April 26, 2006 - 8am Central time zone:
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
December 11, 2006 - 5pm Central time zone
Reviewer: Sean from NJ, USA
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
January 9, 2007 - 11am Central time zone
Reviewer: Ravi Kumar from Gurgaon, India
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
Followup January 11, 2007 - 9am Central time zone:
depends on how the program was started - the command line used.
Finding a bad qry
January 12, 2007 - 3pm Central time zone
Reviewer: marc from usa
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
January 16, 2007 - 5pm Central time zone
Reviewer: Todd from Atlanta GA
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?
March 26, 2007 - 7am Central time zone
Reviewer: Ram from India
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.
Followup March 26, 2007 - 11am Central time zone:
if you want to use Oracle's AUDIT command, you have to enable it - yes.
March 26, 2007 - 3pm Central time zone
Reviewer: A reader
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 ?
Followup March 27, 2007 - 9am Central time zone:
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
March 27, 2007 - 1pm Central time zone
Reviewer: AHLAM SADAGH from SANA'A/YEMEN
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..
March 28, 2007 - 12am Central time zone
Reviewer: A reader
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
May 2, 2007 - 3am Central time zone
Reviewer: Manoj from India
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
Followup May 2, 2007 - 8am Central time zone:
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
May 14, 2007 - 7am Central time zone
Reviewer: parlee from UK
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
Followup May 14, 2007 - 2pm Central time zone:
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
May 15, 2007 - 7am Central time zone
Reviewer: parlee from uk
So how do we check the DDL in 9i? Is there another view where it can be seen.
Thanks as always.
Followup May 15, 2007 - 4pm Central time zone:
it is not in any of the v$ views, no.
TRACk DML
May 16, 2007 - 8am Central time zone
Reviewer: Gazazou from Guinea
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
May 16, 2007 - 8am Central time zone
Reviewer: Gazazou from Guinea
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;
/
May 22, 2007 - 8am Central time zone
Reviewer: A reader
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.
Followup May 22, 2007 - 9am Central time zone:
"get them", what do you mean by "get them"
trace event
May 22, 2007 - 2pm Central time zone
Reviewer: ignorant from NY
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
Followup May 22, 2007 - 7pm Central time zone:
don't really use them.
When does Elapsed time get Set in V$session
June 12, 2007 - 12am Central time zone
Reviewer: A reader
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
Followup June 12, 2007 - 9am Central time zone:
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
June 12, 2007 - 12am Central time zone
Reviewer: A reader
Some more
June 12, 2007 - 12am Central time zone
Reviewer: A reader
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
Followup June 12, 2007 - 9am Central time zone:
sql-tracing does that.
last_CALL_et is about the last call - the last thing that changed the STATUS column value.
No other way
June 12, 2007 - 12pm Central time zone
Reviewer: A reader
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
August 22, 2007 - 5pm Central time zone
Reviewer: A reader
(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.
Followup August 23, 2007 - 10am Central time zone:
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?
September 12, 2007 - 8am Central time zone
Reviewer: Ramanathan from India
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?
Followup September 15, 2007 - 4pm Central time zone:
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
June 16, 2008 - 7am Central time zone
Reviewer: Chee Yong from Singapore
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?
Followup June 16, 2008 - 12pm Central time zone:
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
June 17, 2008 - 12am Central time zone
Reviewer: A reader
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"?
sql execution statistic
August 14, 2008 - 12am Central time zone
Reviewer: Yoel from Singapore
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
Followup August 18, 2008 - 9am Central time zone:
... 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
August 14, 2008 - 2am Central time zone
Reviewer: Yoel from Singapore
Hi Tom,
I think i've found it. it is in DBA_HIST_SQLSTAT.
I should be looking at the EXECUTIONS_DELTA column.
Cheers,
Followup August 18, 2008 - 9am Central time zone:
it has some/many of them - yes.
how to find stored procedure execution completed or not
September 5, 2008 - 11pm Central time zone
Reviewer: Meagal from NJ
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 ?
Followup September 8, 2008 - 8am Central time zone:
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
November 4, 2008 - 7am Central time zone
Reviewer: John
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,
Followup November 11, 2008 - 12pm Central time zone:
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
March 11, 2009 - 9am Central time zone
Reviewer: John Davey from UK
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
Followup March 12, 2009 - 7am Central time zone:
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
March 16, 2009 - 9am Central time zone
Reviewer: John Davey from UK
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
March 16, 2009 - 1pm Central time zone
Reviewer: John Davey from UK
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
Followup March 17, 2009 - 10am Central time zone:
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
March 20, 2009 - 12pm Central time zone
Reviewer: john davey from UK
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
September 10, 2009 - 10am Central time zone
Reviewer: Robert from Memphis, USA.
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.
Do we have to write our own queries?
September 15, 2009 - 4pm Central time zone
Reviewer: Robert from Memphis, USA.
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.
Followup September 16, 2009 - 8am Central time zone:
... 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
September 16, 2009 - 1pm Central time zone
Reviewer: Robert from Memphis, USA.
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.
Followup September 16, 2009 - 5pm Central time zone:
yes it would - for the query performance.
DBA_HIST_SEG_STAT would be useful for judging the size over time
Balaji
November 17, 2009 - 1am Central time zone
Reviewer: Balaji from UAE
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
Followup November 23, 2009 - 1pm Central time zone:
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.
November 23, 2009 - 11pm Central time zone
Reviewer: Balaji from UAE
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
December 2, 2009 - 4pm Central time zone
Reviewer: Marcus
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?
Followup December 4, 2009 - 9am Central time zone:
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
December 4, 2009 - 2pm Central time zone
Reviewer: Marcus
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.
streams for unidirectional replication
December 7, 2009 - 10am Central time zone
Reviewer: Marcus
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?
Followup December 7, 2009 - 3pm Central time zone:
... 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
December 11, 2009 - 8am Central time zone
Reviewer: Marcus
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?
Followup December 11, 2009 - 8am Central time zone:
... 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
December 11, 2009 - 11am Central time zone
Reviewer: ifeanyi from nigerian
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
Followup December 14, 2009 - 7am Central time zone:
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
December 11, 2009 - 1pm Central time zone
Reviewer: Rick
ifeanyi, read the Oracle Concepts manual, its free online. http://tahiti.oracle.com/
Trace user dmls
December 13, 2009 - 3pm Central time zone
Reviewer: A reader
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
Followup December 14, 2009 - 8am Central time zone:
... 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
December 13, 2009 - 3pm Central time zone
Reviewer: Shahid from SA
Sorry i forgot to put my info when I commented.
I still am not sure
December 14, 2009 - 10am Central time zone
Reviewer: shahid from sa
<<<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
Followup December 14, 2009 - 10am Central time zone:
...
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.
December 14, 2009 - 12pm Central time zone
Reviewer: shahid from sa
<<<<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
Followup December 14, 2009 - 3pm Central time zone:
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.
December 15, 2009 - 1am Central time zone
Reviewer: Shahid from sa
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
|