How can I see (with SQL*plus) that is the last SQL statement
Vishal Shah, September 30, 2001 - 4:25 am UTC
This article was extremely helpful. We had been worrying over how to find the culprit sql for a few hours. Your code helped us solve the issue in minutes. thanks a bunch.
Bind variables
Dan Benins, March 14, 2002 - 12:36 pm UTC
This prints out the general SQL but it replaces the values with bind variables ie :b1 is there anyway to get the actual sql with the actual values.
March 14, 2002 - 1:13 pm UTC
Not without having TRACED the session. The bind variable values are not visible outside of the session itself.
In Oracle9i, using fine grained auditing, we can capture the query and the inputs (binds) to the query in the audit trail -- that will permit you to see this information.
RE: This prints out the general SQL...
Mark A. Williams, March 14, 2002 - 1:56 pm UTC
>>> This prints out the general SQL but it replaces the values with bind variables...
I don't think Tom's code is replacing the values with bind variables as suggested - the actual code would be using bind variables (which I would consider a good thing).
Did I misread Tom's code?
- Mark
March 14, 2002 - 4:02 pm UTC
No, you are correct -- i'm not rewriting the SQL. The bind variables (good things) were in there, he was looking for the values -- which you cannot get from v$ tables (only for YOUR session could you see that)
9i fine grained auditing
Doug, March 14, 2002 - 5:17 pm UTC
Tom,
Have you got any examples on fine grained auditing ?
March 14, 2002 - 6:18 pm UTC
Here is one:
Now, we'll create our application schema
demo@TKYTE9I.US.ORACLE.COM>
demo@TKYTE9I.US.ORACLE.COM>
demo@TKYTE9I.US.ORACLE.COM> create table emp as select * from scott.emp;
Table created.
demo@TKYTE9I.US.ORACLE.COM> create table dept as select * from scott.dept;
Table created.
demo@TKYTE9I.US.ORACLE.COM>
demo@TKYTE9I.US.ORACLE.COM> set echo off
Enter to continue
=================================================================
And set up auditing on it. We are interested in the EMP table
and only when sal > 1500 and when you select the SAL column:
demo@TKYTE9I.US.ORACLE.COM>
demo@TKYTE9I.US.ORACLE.COM>
demo@TKYTE9I.US.ORACLE.COM> begin
2 dbms_fga.add_policy
3 ( object_schema => user,
4 object_name => 'EMP',
5 policy_name => 'ENAME_SAL',
6 audit_condition => 'sal > 1500',
7 audit_column => 'SAL' );
8 end;
9 /
PL/SQL procedure successfully completed.
demo@TKYTE9I.US.ORACLE.COM>
demo@TKYTE9I.US.ORACLE.COM>
demo@TKYTE9I.US.ORACLE.COM> set echo off
Enter to continue
=================================================================
if you do not use CBO, you will get "false positives", it
cannot check your predicate using RBO
demo@TKYTE9I.US.ORACLE.COM>
demo@TKYTE9I.US.ORACLE.COM> analyze table emp compute statistics;
Table analyzed.
demo@TKYTE9I.US.ORACLE.COM>
demo@TKYTE9I.US.ORACLE.COM> set echo off
Enter to continue
=================================================================
Now, we'll see that if we do not select SAL, no audit
demo@TKYTE9I.US.ORACLE.COM>
demo@TKYTE9I.US.ORACLE.COM>
demo@TKYTE9I.US.ORACLE.COM> select empno, ename from emp;
EMPNO ENAME
---------- ----------
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
7876 ADAMS
7900 JAMES
7902 FORD
7934 MILLER
14 rows selected.
demo@TKYTE9I.US.ORACLE.COM>
demo@TKYTE9I.US.ORACLE.COM> exec print_table( 'select * from dba_fga_audit_trail' )
PL/SQL procedure successfully completed.
demo@TKYTE9I.US.ORACLE.COM>
demo@TKYTE9I.US.ORACLE.COM> set echo off
Enter to continue
=================================================================
Now, we'll only statements that should be audited are (and fully at that)
demo@TKYTE9I.US.ORACLE.COM>
demo@TKYTE9I.US.ORACLE.COM> variable sal number
demo@TKYTE9I.US.ORACLE.COM>
demo@TKYTE9I.US.ORACLE.COM> execute :sal := 1400
PL/SQL procedure successfully completed.
demo@TKYTE9I.US.ORACLE.COM> select empno, ename from emp where sal < :sal;
EMPNO ENAME
---------- ----------
7369 SMITH
7521 WARD
7654 MARTIN
7876 ADAMS
7900 JAMES
7934 MILLER
6 rows selected.
demo@TKYTE9I.US.ORACLE.COM>
demo@TKYTE9I.US.ORACLE.COM> execute :sal := 1700
PL/SQL procedure successfully completed.
demo@TKYTE9I.US.ORACLE.COM> select empno, ename from emp where sal < :sal;
EMPNO ENAME
---------- ----------
7369 SMITH
7499 ALLEN
7521 WARD
7654 MARTIN
7844 TURNER
7876 ADAMS
7900 JAMES
7934 MILLER
8 rows selected.
demo@TKYTE9I.US.ORACLE.COM>
demo@TKYTE9I.US.ORACLE.COM> exec print_table( 'select * from dba_fga_audit_trail' )
SESSION_ID : 1520
TIMESTAMP : 14-mar-2002 18:21:08
DB_USER : DEMO
OS_USER : Thomas?Kyte
OBJECT_SCHEMA : DEMO
OBJECT_NAME : EMP
POLICY_NAME : ENAME_SAL
SCN : 919457
SQL_TEXT : select empno, ename from emp where sal < :sal
SQL_BIND : #1(4):1700
COMMENT$TEXT :
-----------------
PL/SQL procedure successfully completed.
demo@TKYTE9I.US.ORACLE.COM>
demo@TKYTE9I.US.ORACLE.COM> set echo off
Enter to continue
=================================================================
The FGA works on the base tables, you cannot get around it via
a higher level view
demo@TKYTE9I.US.ORACLE.COM>
demo@TKYTE9I.US.ORACLE.COM>
demo@TKYTE9I.US.ORACLE.COM> create or replace view v
2 as
3 select emp.ename, emp.sal salary, dept.dname
4 from emp, dept
5 where emp.deptno = dept.deptno
6 /
View created.
demo@TKYTE9I.US.ORACLE.COM>
demo@TKYTE9I.US.ORACLE.COM> set echo off
Enter to continue
=================================================================
And now select from the view
demo@TKYTE9I.US.ORACLE.COM>
demo@TKYTE9I.US.ORACLE.COM> select dname, salary from v;
DNAME SALARY
-------------- ----------
ACCOUNTING 1300
ACCOUNTING 5500
ACCOUNTING 2450
RESEARCH 3000
RESEARCH 1100
RESEARCH 3000
RESEARCH 2975
RESEARCH 800
SALES 950
SALES 1500
SALES 2850
SALES 1250
SALES 1250
SALES 1600
14 rows selected.
demo@TKYTE9I.US.ORACLE.COM> exec print_table( 'select * from dba_fga_audit_trail' )
SESSION_ID : 1520
TIMESTAMP : 14-mar-2002 18:21:08
DB_USER : DEMO
OS_USER : Thomas?Kyte
OBJECT_SCHEMA : DEMO
OBJECT_NAME : EMP
POLICY_NAME : ENAME_SAL
SCN : 919457
SQL_TEXT : select empno, ename from emp where sal < :sal
SQL_BIND : #1(4):1700
COMMENT$TEXT :
-----------------
SESSION_ID : 1520
TIMESTAMP : 14-mar-2002 18:21:26
DB_USER : DEMO
OS_USER : Thomas?Kyte
OBJECT_SCHEMA : DEMO
OBJECT_NAME : EMP
POLICY_NAME : ENAME_SAL
SCN : 919467
SQL_TEXT : select dname, salary from v
SQL_BIND :
COMMENT$TEXT :
-----------------
PL/SQL procedure successfully completed.
demo@TKYTE9I.US.ORACLE.COM>
demo@TKYTE9I.US.ORACLE.COM>
demo@TKYTE9I.US.ORACLE.COM> set echo off
How to track DDL's
Sai, September 09, 2003 - 5:35 pm UTC
Hi Tom,
We have a big table creation's(ctas) is going on in our database. How can I track which session is creating what table and complete create table statement?
If I get what ctas statement a user is executing, I can estimate how long is it going to run from the table size. It is too hard to judge from "db file scattered read scan" wait event vs table name, becuase we do have lots of full table scan's going on aside from the ctas commands.
September 10, 2003 - 10:34 am UTC
take a look at v$session_longops
Is it a typo
A reader, September 11, 2003 - 11:00 pm UTC
Tom,
In your response you have mentioned to replace WHERE status = 'ACTIVE' with "WHERE status = status". Can you let me know what that means.
September 12, 2003 - 9:50 am UTC
just pulls all rows where status is not null, I was just saying "if you'd like to see more data... for sessions that are not active"
Seeing SQL for killed sessions
Faheem Rao, April 02, 2004 - 9:55 am UTC
Tom,
I am trying to find out the Sql executed by the session which are currently not active, they have been killed or ended.
V$sql gives you the statistics for the SQL executed , I can join V$seesion and v$sql putting V$session.sql_address = v$sql.address, which will give me sqls which are being currently executing. Once they are done(means their status comes to inactive), sql_address in v$session will be '00' , but in v$sql I still can see that sql , but I will be missing the session info(who executed that sql). There is no session info in v$sql.
Can you please help me trying to find out sqls executed by killed or inactive sessions.
Thanks
April 02, 2004 - 10:26 am UTC
v$sql is a common cache we all share.
we don't have a list of all sql's executed by a session. that list could be quite "huge"
sessing SQL for killed sessions
Faheem Rao, April 14, 2004 - 11:08 pm UTC
Tom ,
Thanks for the response!,
Going in little more detail , if I want to maintain sql list(per session) for a short intervel of time. let say for last 24 hrs. Then is there any any by which I can force to maintain SQL address in V$session for a short interval of time, or is there any internal event can be fired before resetting sql address(in v$session) and I can pick that event.
Basically I can query(every 5 second or soo starting on a login system trigger) and store v$sesion info in a temp_table, but how do I track the sql execute in between those 5 seconds. I can reduce my query time interval to one seocnd but to my understading I still be missing some Sqls executed. Is there any way out??
any help would be greatly appreciated!!
Regards
Faheem Rao
April 15, 2004 - 8:26 am UTC
this facility just doesn't exist (short of sql_trace=true but that would *kill* you, totally kill you)
you could look at FGA (fine grained auditing) and a routine to purge the audit trail over time.
Joining v$session and v$sqlarea to obtain current session sql
Sven Bleckwedel, June 16, 2004 - 5:55 pm UTC
Tom,
I looked for a way to use what you showed above to help a business to register (using a trigger) update (only) statements sended by one application, only to check from where are they having problems. Auditing (or something else) is to be implemented in a near future, but this problem appeared before. The developers couldn´t find the source of this problem and sometimes a lot of lines appear to be updated without any reason and with wrong values. Maybe somebody is doing wrong things, and I provided a trigger to capture terminal, program, previous and new values, but without storing the current session sql statement. Doing some research, noted that was possible to join v$session and v$sqlarea to obtain what they need, as described below:
SQL> col username format a10
SQL> col machine format a10
SQL> col terminal format a10
SQL> col program format a20
SQL> select username, audsid, machine, terminal, program, sql_address
2 from v$session
3 where audsid=(select userenv('sessionid') from dual);
USERNAME AUDSID MACHINE TERMINAL PROGRAM SQL_ADDR
---------- ---------- ---------- ---------- -------------------- --------
SYSTEM 200349 USP004 USP004 SQLPLUSW.EXE 10B40BE4
SQL> col sql_text format a35
SQL> set timing on
SQL> select a.username, a.terminal, a.program, b.sql_text
2 from v$session a, v$sqlarea b
3 where a.audsid=(select userenv('sessionid') from dual)
4 and b.address=a.sql_address;
USERNAME TERMINAL PROGRAM SQL_TEXT
---------- ---------- -------------------- -----------------------------------
SYSTEM USP004 SQLPLUSW.EXE select a.username, a.terminal, a.pr
ogram, b.sql_text from v$session a,
v$sqlarea b where a.audsid=(select
userenv('sessionid') from dual)
and b.address=a.sql_address
Elapsed: 00:00:14.00
SQL> select a.username, a.terminal, a.program, b.sql_text
2 from v$sqlarea b, v$session a
3 where a.audsid=(select userenv('sessionid') from dual)
4 and a.sql_address=b.address;
USERNAME TERMINAL PROGRAM SQL_TEXT
---------- ---------- -------------------- -----------------------------------
SYSTEM USP004 SQLPLUSW.EXE select a.username, a.terminal, a.pr
ogram, b.sql_text from v$sqlarea b,
v$session a where a.audsid=(select
userenv('sessionid') from dual)
and a.sql_address=b.address
Elapsed: 00:00:14.07
SQL> set timing off
SQL> select count(*) from v$session;
COUNT(*)
----------
41
SQL> select count(*) from v$sqlarea;
COUNT(*)
----------
35322
But noted also that the response time was very slow. Tried another simplified statement, the response time was similar:
SQL> set timing on
SQL> select sql_text
2 from v$sqlarea
3 where address=(select sql_address
4 from v$session
5 where audsid=(select userenv('sessionid') from dual));
SQL_TEXT
-----------------------------------
select sql_text from v$sqlarea wher
e address=(select sql_address
from v$session
where audsid=(select userenv('
sessionid') from dual))
Elapsed: 00:00:09.03
Looking for the results using single statements only, noted that the response time isn´t so slow:
SQL> select userenv('sessionid') from dual;
USERENV('SESSIONID')
--------------------
200376
Elapsed: 00:00:00.00
SQL> select sql_address
2 from v$session
3 where audsid=200376;
SQL_ADDR
--------
17136AA0
Elapsed: 00:00:00.00
SQL> select sql_text
2 from v$sqlarea
3 where address=hextoraw('17136AA0');
SQL_TEXT
-----------------------------------
select sql_address from v$session w
here audsid=200376
Elapsed: 00:00:00.09
But combined, the response time could be inviable for doing something using triggers. Could be there any other way to obtain these results ?
Tks in adv,
Sven
June 16, 2004 - 6:19 pm UTC
but -- if you run a query to get a query, the query you'll get is the query you are running.
you cannot scrape the triggering sql out this way. the query you run to get the triggering update will push out the current SQL from v$session!
You could use something like this:
ops$tkyte@ORA9IR2> l
1 select sql_text
2 from v$sql
3 where address in ( select address
4 from v$open_cursor
5 where sid = (select sid
6 from v$mystat
7 where rownum=1) )
8* and upper(sql_text) like '%'
ops$tkyte@ORA9IR2> /
SQL_TEXT
-------------------------------------------------------------------------------
SELECT MINOR_VERSION FROM SYS.CDC_SYSTEM$
SELECT COUNT(*) FROM USER_POLICIES V WHERE V.OBJECT_NAME = :b1 AND POLICY_N
AME LIKE '%xdbrls%'
SELECT COUNT(*) FROM ALL_POLICIES V WHERE V.OBJECT_OWNER = :b1 AND V.OBJECT
_NAME = :b2 AND POLICY_NAME LIKE '%xdbrls%'
select pol#, usr_name, usr_labels, package, privs from lbac$user_logon where us
r_name = :username
SELECT MAX(TAG#) FROM LBAC$LAB
SELECT POL# FROM LBAC$POL ORDER BY POL#
SELECT ATTRIBUTE FROM V$CONTEXT WHERE NAMESPACE = 'LBAC$LABELS'
SELECT POL#,PACKAGE FROM LBAC$POL WHERE BITAND(FLAGS,1) = 1 ORDER BY PACKAGE
select sql_text from v$sql where address in ( select address
from v$open_cursor where sid = (select sid
from v$mystat wh
ere rownum=1) ) and upper(sql_text) like '%'
9 rows selected.
Elapsed: 00:00:00.05
ops$tkyte@ORA9IR2>
replace % with the %TABLE_NAME% where table_name is the name of the table in the trigger. It'll over audit, but that is more or less unavoidable.
I can't catch the last SQL Text
Antonio, June 22, 2004 - 6:20 am UTC
Hi Tom,
I tried to use your script, but i ve got a problem when I use it whit FGA,
here is my script
exec DBMS_FGA.ADD_POLICY(OBJECT_SCHEMA => 'DEMO',OBJECT_NAME =>
'REF_UR',POLICY_NAME => 'POL_REF_UR',HANDLER_SCHEMA =>
'DEMO',HANDLER_MODULE=> 'CAPTURE',ENABLE => TRUE)
And here is the Capture procedure
CREATE OR REPLACE procedure CAPTURE_FGA(V_SCHEMA VARCHAR2,V_TABLE
VARCHAR2,V_POLICY VARCHAR2)
AS
x number;
scenario number;
testsq varchar2(4000);
begin
for x in
( select username||'('||sid||','||serial#||
') ospid = ' || process ||
' program = ' || program, username,
to_char(LOGON_TIME,' Day YYYY-MM-DD HH24:MI:SS') logon_time ,
to_char(sysdate,' Day YYYY-MM-DD HH24:MI:SS') current_time,
sql_address,prev_sql_addr,LAST_CALL_ET,MACHINE,TERMINAL,process,program,
audsid,sid,serial#,osuser,a.module,b.CPU_TIME,b.EXECUTIONS,b.FETCHES,b.INVALIDATI
ONS,
b.LOADS,
b.OPTIMIZER_COST,b.OPTIMIZER_MODE,b.sorts,b.OPEN_VERSIONS,b.ROWS_PROCESSED,
b.BUFFER_GETS,b.DISK_READS,b.PARSE_CALLS,b.ELAPSED_TIME,b.USERS_EXECUTING,b.USERS
_OPENING,
b.LOADED_VERSIONS,b.FIRST_LOAD_TIME,b.LAST_LOAD_TIME
from v$session a,v$sql b
where status = 'ACTIVE'
and rawtohex(prev_sql_addr) <> '00'
and b.address = prev_sql_addr
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
piece < 4
--and address =x.sql_address
and address in
(select prev_sql_addr from v$session
where sid = 18
and serial# = 475)
)
loop
if ( y.sql_text not like '%listener.get_cmd%' and
y.sql_text not like '%rawtohex(sql_addresse)%' )
then
insert into rt_suivi_consult_aas
(trc_schema,trc_table,trc_policy,dt_trace,sql_text_line,terminal,
machine,username,process,program,sid,serial#,osuser,module,logon_time,
CPU_TIME,EXECUTIONS,FETCHES,INVALIDATIONS,LOADS,OPTIMIZER_COST,OPTIMIZER_MODE,
sorts,OPEN_VERSIONS,ROWS_PROCESSED,BUFFER_GETS,DISK_READS,PARSE_CALLS,ELAPSED_TIM
E,
USERS_EXECUTING,USERS_OPENING,LOADED_VERSIONS,
FIRST_LOAD_TIME,LAST_LOAD_TIME)
values
(v_schema,v_table,v_policy,x.current_time,y.sql_text,x.terminal,
x.machine,x.username,x.process,x.program,x.sid,x.serial#,x.osuser,
x.module,x.logon_time,x.cpu_time,x.EXECUTIONS,x.FETCHES,x.INVALIDATIONS,
x.LOADS,x.OPTIMIZER_COST,x.OPTIMIZER_MODE,
x.sorts,x.OPEN_VERSIONS,x.ROWS_PROCESSED,
x.BUFFER_GETS,x.DISK_READS,x.PARSE_CALLS,
x.ELAPSED_TIME,x.USERS_EXECUTING,x.USERS_OPENING,
x.LOADED_VERSIONS,x.FIRST_LOAD_TIME,x.LAST_LOAD_TIME);
end if;
end loop;
end loop;
end;
/
then, when I do a
select * from ref_ur
I check my rt_suivi_consult_aas table, to see If I catched the good sql text
the sql text catched is
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
and not "select * from ref_ur"
do you have an idea.
June 22, 2004 - 8:53 am UTC
please -- look at the other place you asked this and just ask once?
Auditing SQL statement calling a function
Douglas, June 23, 2004 - 12:33 am UTC
create table tab1 (col1 number, col2 number);
insert into tab1 values (10, 15);
insert into tab1 values (20, 25);
insert into tab1 values (30, 35);
commit;
create package pkg1 as
function func1(i in number)
return number;
end;
/
create package body pkg1 as
function func1(i in number)
return number is
begin
-- additional code that audit the calling SQL statement
return i+1;
end;
end;
/
select pkg1.func1(col1), pkg1.func1(col2) from tab1;
When the above select statement is executed once, func1 will be called 6 times.
When the above select statement is executed again, func1 will be called additional 6 times.
I want to add some SQL auditing code into to func1 so that:
When the above select statement is executed once, the additional codes will be executed 1 time.
When the above select statement is executed again, the additional codes will be executed an additional 1 time.
That is, I want to audit the SQL statement per SQL call instead of per function call.
How can I do that?
Thx!!
June 23, 2004 - 8:44 am UTC
well, it'll be executed some number of times -- could be more than 6, could forseeably even be less than 6.
I cannot think of a way to achieve your goal without telling the package "please reset yourself" between SQL invocations.
To audit, you should consider using dbms_fga, it may well do all you need without writing any code.
A reader, June 23, 2004 - 10:34 am UTC
In fact, this is a simplified modal for my real project. I need the SQL statement level work not only for auditing. So the dbms_fga cannot solve my problem totally.
I see that a database encryption software(Protegrity) do this auditing job well using my approach, using a wrapped produre with external procedure call. So I guess that there should be something to identify the SQL duration in either standard PL/SQL packages or OCI functions.
Thanks for your reply anyway!!
June 23, 2004 - 11:20 am UTC
dbms_fga can capture your SQL
it does capture your SQL.
hence, you can skip the "capture sql" part and do whatever else you feel is necessary.
but nothing is going to tell you "new query" - nothing in general "could" (eg: there is nothing but nothing stopping you from running a query in your procedure that calls this very same procedure again).
You would have to demark that yourself, the boundaries.
----------------------------------------------------------------------
quick update -- just remembered something.
we could use an application context here. The function can pass itself an application context value, in the function -- we would compare that to the current "as of now" value. If they are the same -- first time, change the value. For the rest of that query execution -- they will be "different". when you run the query again -- they will be "the same"
eg:
ops$tkyte@ORA9IR2> create or replace context my_ctx using my_pkg
2 /
Context created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace package my_pkg
2 as
3 function foo( x in number, p_context_val in varchar2 ) return number;
4 end;
5 /
Package created.
ops$tkyte@ORA9IR2> show err
No errors.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace package body my_pkg
2 as
3 function foo( x in number, p_context_val in varchar2 ) return number
4 as
5 begin
6 if ( nvl(sys_context('my_ctx','flag'),'0') = nvl(p_context_val,'0') )
7 then
8 dbms_output.put_line( 'first call, x = ' || x );
9 dbms_session.set_context( 'my_ctx', 'flag', nvl(sys_context('my_ctx','flag'),'0')+1 );
10 end if;
11
12 return x;
13 end;
14
15 end;
16 /
Package body created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select my_pkg.foo( user_id, sys_context( 'my_ctx', 'flag' ) ) from all_users where username like '%A%';
MY_PKG.FOO(USER_ID,SYS_CONTEXT('MY_CTX','FLAG'))
------------------------------------------------
36
166
46
53
58
63
126
102
120
82
86
188
12 rows selected.
ops$tkyte@ORA9IR2> exec null;
first call, x = 36
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2> select my_pkg.foo( user_id, sys_context( 'my_ctx', 'flag' ) ) from all_users where username like '%B%';
MY_PKG.FOO(USER_ID,SYS_CONTEXT('MY_CTX','FLAG'))
------------------------------------------------
19
35
58
59
158
63
126
84
8 rows selected.
ops$tkyte@ORA9IR2> exec null;
first call, x = 19
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2> select my_pkg.foo( user_id, sys_context( 'my_ctx', 'flag' ) ) from all_users where username like '%C%';
MY_PKG.FOO(USER_ID,SYS_CONTEXT('MY_CTX','FLAG'))
------------------------------------------------
33
58
59
60
126
84
86
92
8 rows selected.
ops$tkyte@ORA9IR2> exec null;
first call, x = 33
PL/SQL procedure successfully completed.
<b>see the dbms_output got called once per query -- all you need do is add that magic "last parameter" to your function call -- it is read consistent (will have the same value) during that 'call'</b>
Douglas, June 24, 2004 - 7:25 am UTC
Thx for your great solution. Unfortunatly, this can only solve half of my problem. Here's more complete prototype to my real case:
create package "PKG1" as
function encrypt(i in number)
return number;
function decrypt(i in number)
return number;
end;
/
create package body "PKG1" as
function encrypt(i in number)
return number is
begin
-- additional code that audit the calling SQL statement
return i+1;
end;
function decrypt(i in number)
return number is
begin
-- additional code that audit the calling SQL statement
return i-1;
end;
end;
/
create table "TAB1_E" ("COL1" number, "COL2" number);
create view "TAB1_V" ("COL1", "COL2", "ROW_ID") as
select "PKG1".decrypt(T."COL1"),
"PKG1".decrypt(T."COL2"),
T.ROWID
from "TAB1_E" T;
CREATE TRIGGER "TAB1_VT" INSTEAD OF
INSERT OR UPDATE ON "TAB1_V"
begin
if INSERTING then
insert into "TAB1_E" ("COL1", "COL2") values (
"PKG1".encrypt(:new."COL1"),
"PKG1".encrypt(:new."COL2"));
elsif UPDATING then
update "TAB1_E" set
"COL1" = "PKG1".encrypt(:new."COL1"),
"COL2" = "PKG1".encrypt(:new."COL2")
where ROWID = :new."ROW_ID";
end if;
end;
/
create view "TAB1" ("COL1", "COL2") as
select T."COL1",
T."COL2"
from "TAB1_V" T;
insert into TAB1 values (10, 15);
insert into TAB1 values (20, 25);
insert into TAB1 values (30, 35);
commit;
Your solution can solve the problem when selecting the topmost view "TAB1", but not when inserting or updating "TAB1".
June 24, 2004 - 10:06 am UTC
Lots of "stuff happening by pure magic" in this system. I much rather prefer the straight forward way.
tell me -- what is wrong with the insert/update? not sure I'm following you here. I see single statement inserts in your example - three individual statements. what is your "goal" -- to audit once per transaction or something??
Douglas, June 24, 2004 - 10:38 am UTC
Sorry that i have missed something. Just forget about the 3 insert statements, and initialize the table with:
insert into TAB1 ("COL1", "COL2") values (10, 15);
insert into TAB1 ("COL1", "COL2") values (20, 25);
insert into TAB1 ("COL1", "COL2") values (30, 35);
insert into TAB1 ("COL1", "COL2") values (40, 15);
insert into TAB1 ("COL1", "COL2") values (50, 15);
commit;
create table "TAB2" ("COL1" number, "COL2" number);
insert into TAB2 ("COL1", "COL2") values (100, 1);
insert into TAB2 ("COL1", "COL2") values (200, 2);
insert into TAB2 ("COL1", "COL2") values (300, 3);
commit;
My goal is to audit(and do some other work) once for each of the following statements:
select "COL1", "COL2" from tab1;
insert into tab1 select "COL1", "COL2" from tab2;
update tab1 set "COL1" = 0 where "COL2" = 15;
The select statement can be audited, once per calling SQL statement, by using your solution in decrypt function.
So, no problem for the select statement.
However, for the insert statement, the statement:
insert into "TAB1_E" ("COL1", "COL2") values (
"PKG1".encrypt(:new."COL1"),
"PKG1".encrypt(:new."COL2"));
will be called multiple times (3 times in this setting).
By using your solution, the audit code will be called multiple times.
The update case is similar to the insert case.
I hope you understand what I mean. Thank you!
June 24, 2004 - 10:50 am UTC
are you allowed to have an extra column (dummy column) added to the view, something we can use to pass the sys_context value to the trigger
(this would all be so much better without magical side effects like instead of triggers and other "cute" things -- i'd prefer a much more straight forward approach that would not try to hide lots of stuff happening by side effect)
Douglas, June 24, 2004 - 11:23 am UTC
It is not allowed. Let me explain a bit. In the begining, there is a table(TAB1) with 2 columns (COL1, COL2). The table(TAB1_E), views (TAB1_V, TAB1) and trigger (TAB1_VT) are the products after encrypting the table TAB1.
For the encrypted table to work with compiled program running before encryption, one of the requirement is: the view TAB1 should have the same structure as the table TAB1 before the encryption. And that's why the dummy view TAB1 is here to select a subset of TAB1_V and do nothing else.
The most suprising thing is that I see that another database encryption software is do that prefectly, without extra column, extra parameters in function call. What I guess is it relies something about the context, as it uses OCIContextGenerateKey/OCIContextGetValue/OCIContextSetValue/OCIAttrGet in the external procedure. (I see it by using depends.exe of Visual Studio, not seeing the source code, so I don't know how exactly it does)
p.s. The TAB1_V should works fine if the existing programs using the table TAB1 do not use something like "select * from TAB1", "insert into TAB1 values (1, 2)". But I thinks that many programs/programmers are using that.
Anyway, thanks again for your kindly suggestion.
June 24, 2004 - 3:00 pm UTC
then I cannot imagine for now a way to accomplish it.
identify poor query
J, August 04, 2005 - 2:16 am UTC
I would like to capture those top query with user information in database. If go by CPU_TIME, through v$sqlarea, how can we identify the query in high CPU_TIME? as number of executions is varied from 0 to big number. If one query is submitted with parallel degree n, automatically, the executions will be set to n. or only thing we could do is to identify who has been in top CPU usage with which sql statement, regardless how many times the sql may be executed? Thanks for any suggestion.
August 04, 2005 - 9:20 am UTC
and who says the query with the biggest cpu time is the worst query
why not physical IO
or logical IO, logical IO is a killer too.
but, if you want an "average per execution", just divide number of times executed into the number.
Does not seem necessary though. If you have two queries:
query 1, 10000000 cpu seconds
query 2, 10000000 cpu seconds
and query 1 is executed 10000 times
and query 2 is executed 10 times
which one do you "fix"? They both consume gobs of cpu, I'd want to fix query 1 so as to not execute it so often (algorithm change, probably a programmer doing slow by slow coding instead of set based programing). I'd want to fix query 2 to be more efficient
but I'd want to fix both.
yes
J, August 04, 2005 - 10:50 am UTC
It is true. Both query are CPU killer. Yes, physical I/O (disk_reads) and logical I/O (buffer_gets) are killer too. I will just do one thing at a time to see if we could fix some problem.
quick question: what is loads and 'users_executing' trying to tell us in v$sqlarea? For executions of 1, can I interprete the stats for one time execution of the query? and 0 to active execution?
Thanks!
executions in v$sqlarea
J, August 09, 2005 - 8:35 pm UTC
Yes, I thought so too. However, we caught different scenarios:
1. when executions=0, the user is actually running one script which was loaded last time he/she submitted.
2. I caught one sql goes to executions=1 directly, but with user_executing=1 - they are actively running at that time
3. if there is parallel degree (n) enabled on table, the executions will equal to n directly.
I am wondering how in reality this table is populated. We ended up with looking for active session with last_est_tm in v$session for active session (also have to exclude parallel child sessions too) to identify long running processes. once a while, we have run-away process in our database that is active for last 24 hours or so, and we want to capture those.
Thanks!
August 10, 2005 - 9:24 am UTC
1) i didn't understand that sentence.
2) or that.
3) that is an interesting effect (not normally worried about parse/execute with PQ. if you are doing PQ, parsing isn't your performance issue..)
but it won't be "equal" as you say. the parse calls will be multiplied by the degree of parallelism used due to the parallel execution servers.
and now we are into run away processes?? but just look at v$session, last_call_et, it finds those "long running sessions", you already have the information?
How to find out running SQL
Niyasi, September 06, 2005 - 5:45 pm UTC
Hi Tom,
I need to find out current running SQL (Delete Statement) from a statement level trigger. I created a statement level trigger on a table, whose records will be deleted by Oracle Purge Concurrent Process. The whole exercise is to copy table data with LONG columns into a staging Tables from where it will be moved to History database. If I can find out current DELETE CLAUSE WITH CONDITIONS from a statement level trigger, I can select LONG columns and keep in temporary staging tables.
Niyasi Pediyakkal
September 06, 2005 - 9:18 pm UTC
well, plsql will not work over 32k so..... well, you have a problem way before getting the sql statement.
How to find out running SQL
Niyasi Pediyakkal, September 07, 2005 - 9:50 am UTC
Isn't possible I can use COPY COMMAND on SQLPlus level through PL/SQL( by creating a file on OS and ran it)?
September 07, 2005 - 1:42 pm UTC
if you wanted, but it wouldn't be very easy to maintain or even be sure if it worked.
In plsql, why not just "insert into t select * from something_else"?????
v$session , STATUS column output.
Suvendu, September 12, 2005 - 4:52 am UTC
Hi Tom,
Here is my test case about active user status from v$session. Im confused about this STATUS column output.
Connected to SYS from one SQL*PLUS window:
03:11:52 sys@TEST> select username, status from v$session where username is not null;
USERNAME STATUS
------------------------------ --------
SYS ACTIVE
Elapsed: 00:00:00.02
Connected to SCOTT from another SQL*PLUS window and the following transaction:
03:13:40 scott@TEST> update emp set sal=sal+100;
14 rows updated.
Elapsed: 00:00:00.01
03:17:21 scott@TEST> commit;
Commit complete.
03:17:21 scott@TEST>
03:12:27 sys@TEST> select username, status from v$session where username is not null;
USERNAME STATUS
------------------------------ --------
SCOTT INACTIVE
SYS ACTIVE
Elapsed: 00:00:00.01
03:17:31 sys@TEST>
My question,
How could be scotts session is INACTIVE, though its currently doing the transaction? Please, would you confirm same.
Thanking you.
Regards,
Suvendu
September 12, 2005 - 7:34 am UTC
but it is "inactive", not doing anything, not executing SQL when you asked. Scott isn't doing anything at the moment you queried v$session, scott is inactive.
Have scott run
select count(*) from all_objects;
and while that is running query v$session, then scott will be active.
What about hash_value?
Mark Gokmna, September 12, 2005 - 9:15 am UTC
I always use address and hash_value together when joining v$sqltext or v$sqlarea with v$session, and that is what the Reference manual says in the description of those views. Why is your script using only the address?
September 12, 2005 - 10:19 am UTC
there will be one sql at a given memory address.
Get sqltext
Jairo Ojeda, November 10, 2005 - 8:35 pm UTC
Tom, I need to get the last sql text, but I run a query to get it and as you said, the query I'll get is the query I'm running.
so, can you help me to get the last sql text before the query that I run to get it.
November 12, 2005 - 8:00 am UTC
give more information - is this in a trigger? ora_sql_txt - a function - will give you that.
in a procedure
A reader, November 14, 2005 - 12:05 pm UTC
I don't use a trigger, I need to get the sqltext on a package, can I use the ora_sql_text in a procedure, somethink like:
CREATE OR REPLACE PACKAGE PKG_TEST
AS
PROCEDURE PP_TEST( n TEST1.a%TYPE );
END PKG_TEST;
/
CREATE OR REPLACE PACKAGE BODY PKG_TEST
AS
PROCEDURE PP_BITACORA( puser PLS_INTEGER ) IS
BEGIN
INSERT INTO bitacora(usuario, sqltext, programa, equipo)
SELECT username, -- string with the ora_sql_text value
(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)) ||
MAX(DECODE(piece,4,sql_text,NULL)) ||
MAX(DECODE(piece,5,sql_text,NULL))
FROM v$sqltext_with_newlines
WHERE address = sql_address AND
piece < 6) sqltext, MODULE, machine
FROM v$session WHERE audsid = puser;
END PP_BITACORA;
PROCEDURE PP_TEST( n TEST1.a%TYPE ) IS
cv_texto CONSTANT TEST1.c%TYPE := 'Test';
ci_user CONSTANT PLS_INTEGER := USERENV('sessionid');
BEGIN
UPDATE TEST1 SET c = c||' '||cv_texto WHERE a = b;
pp_bitacora(ci_user);
END PP_TEST;
END PKG_TEST;
/
November 14, 2005 - 2:11 pm UTC
ora_sql_txt is only valid in a trigger, it is an "event" function.
why do you need to do this, what are you trying to accomplish (we have fine grained auditing if you are trying to audit?)
Thanks
Jairo Ojeda, November 15, 2005 - 7:33 pm UTC
Tom, Oracle recommends to move the table sys.aud$ to other tablespace. The same apply to sys.fga_log$?
November 16, 2005 - 8:36 am UTC
Please contact support and work with them on this (even moving the aud$ table - it is not "recommended" in general - it is something you must work with support to do)
capture sql's
sns, January 10, 2006 - 6:19 pm UTC
I have an application with several asp pages. Each page has quite of SQL's embedded in that.
On a normal day, the web page takes couple of seconds to pop up (if I click on simething on that). The number of SQL's may be 50 or even 100.
I want to capture all the SQL statements that gets executed when I navigate to any asp page. These sql's may run in fraction of a second.
What is the best way to capture them when I bring up my application?
What is the best way to generate trace files on the session that runs the application?
Thanks,
January 10, 2006 - 7:44 pm UTC
use sql_trace
make your pages able to TRACE themselves (like all of mine do...)
Make it such that if you pass a parameter to your page - your page would issue a "alter session set sql_trace=true" and at the end "sql_trace=false"
Then, whenever you want tracing enabled, you got it.
exactly how?
sns, January 10, 2006 - 7:59 pm UTC
I am not aware of how web pages have been written also I am not a web developer. Could you tell me how to make the pages to enable the trace themselves (with example preferably)?
Thanks,
January 10, 2006 - 8:11 pm UTC
not with ASP's I can't. I don't run any software that can "do" an ASP.
You'd sort of want to be a web developer in order to do this though, wouldn't you?
Sqltext in 10g for oracle submitted jobs
Anil, May 16, 2006 - 6:10 am UTC
Tom,
I use to get the current running sql's with following below query in 9i.
SELECT A.SID,B.HASH_VALUE, OSUSER, USERNAME, SQL_TEXT
FROM V$SESSION A, V$SQLTEXT B
WHERE B.HASH_VALUE = A.SQL_HASH_VALUE
AND USERNAME LIKE upper('%SCHEMA%')
ORDER BY B.HASH_VALUE, B.PIECE;
This is will work in 10g also for user-triggered sqls,stored procedures etc.
But when oracle submits job i'm not able find which qurery is running.
Seems For oracle jobs in 10g for V$session contain column SQL_HASH_VALUE as Zero and hence i'm not able find the any sql's running.
Could you please any of you help me out how to get the Sqltext for Orcle submited Jobs in 10g.
Is there any way to access the module information
Shanker Ram, August 10, 2006 - 11:16 am UTC
I know that we can query the server to which the user is connected to using the following query. But I'm looking for some query which will enable me to tell which form (Oracle forms) the user is currently using.
For example we have Form1,Form2,Form3,Form4 and Form5.
I sould like the result to tell me user X is accessing Form 4 currently.
This query will enable me to update my application server while the other users are accessing the application
SELECT sid,
serial#,
username,
status,
schemaname,
osuser,
process,
machine,
terminal,
program,
module,
resource_consumer_group
FROM v$session a
WHERE type= 'USER'
August 10, 2006 - 12:56 pm UTC
if the form is kind enough to populate the module column via a call to dbms_application_info - it would. So, do your forms do that?
V$session
Shankar Ram, August 10, 2006 - 3:36 pm UTC
My V$session table does not give information for the following columns.
PROGRAM, MODULE, ACTION and CLIENT_INFO. How do I populate these values. Do I need to populate them from the forms session, or is there anyway to populate the same using some database triggers.
Thanks
SRAM
August 10, 2006 - 11:24 pm UTC
you call dbms_application_info from within your application.
only YOUR application knows what module is in place - the database doesn't
Thanks
A reader, August 10, 2006 - 4:48 pm UTC
Thanks for the informaiton. I'm using DBMS_APPLICATION_INFO.SET_MODULE in my form to set the values.
thanks again
Shankar ram.
v$sqlarea - a silly question
A reader, December 30, 2006 - 1:15 am UTC
Hi Tom, this may sound stupid and silly to you and most of us, but I just could not stop myself from asking...
how long does v$sqlarea keep its data ? 3 months ? 1 year ? is there any parameters we can set for this table to keep the data for x period of time ?
many thanks to you (or whoever kind enough to answer this)
December 30, 2006 - 9:45 am UTC
it is a function of
a) size of shared pool
b) number of unique sql statements and other things that consume shared pool memory
it is an LRU (least recently used) memory cache. The stuff in there might be there for a long long long time, or seconds. It depends.
V$SQLAREA history
Roderick, December 31, 2006 - 3:25 pm UTC
And of course v$sqlarea will get cleared out whenever the database instance is restarted (or some DBA decides to flush the shared pool). In 10g some subset snapshot of that information gets archived for some time in the Automatic Workload Repository.
How can I get the last query a session run?
A Reader, January 01, 2007 - 10:52 pm UTC
Hi Tom,
I need to monitor the last query a session had run. I can do that without any problem by using OEM, both the web and java based client version. When I use a query similar to the following:
SELECT A.SID,B.HASH_VALUE, OSUSER, USERNAME, SQL_TEXT
FROM V$SESSION A, V$SQLTEXT B
WHERE B.HASH_VALUE = A.SQL_HASH_VALUE
AND MACHINE='MACHINE_NAME'
ORDER BY B.HASH_VALUE, B.PIECE;
I got no rows returned. But when I go to OEM, I can still get the query the session or the machine's last run.
Using OEM needs multiple clicks and can only get one session at a time. How can I get the last query run by sessions/machines using a SQL?
Thank you for your help.
How to see last unsuccessful sql query
A reader, August 10, 2007 - 9:56 am UTC
Hey Tom,
Datasbase version : 8.1.7.4
my front-end developer is trying to run one setup which issues sql statement behind the screen and that particular statement is giving error (invalid column). He can't see sql so he asked me but i have used swl tracing and tkprof but it only gives me queries which ran successfully.
Is there any way we can get the last unsuccessful sql queries?
Thanks
August 14, 2007 - 3:01 pm UTC
are you sure about that?
=====================
PARSE ERROR #3:len=18 dep=0 uid=61 oct=3 lid=61 tim=1159294844108563 err=904
select z from dua
WAIT #3: nam='SQL*Net break/reset to client' ela= 30 driver id=1650815232 break?=1 p3=0 obj#=-1 tim=1159294844110125
err=904, invalid column...
How to get last unsucessful sql
A reader, August 15, 2007 - 10:29 am UTC
Hey Tom,
Thanks for your response.
Yes i am sure about that.
I did not understand from where you got this ouptut which you showed in last post. can you please make it more clear . Also i am new to this field so may be i am not understanding terminology properly.
Thanks
August 20, 2007 - 7:14 pm UTC
I turned on trace.
I ran the bad sql.
it was in the trace file.
trace
Andras Gabor, August 16, 2007 - 2:06 am UTC
Hello,
I often use SQL Net trace in these cases. And then dig into the raw net trace (sometimes process it with some tool).
In your case you could also look into the sql trace without tkprof-ing it (the output in Tom's reply was some raw sql trace output).
If that does not help, go and trace the sql net communication. (starting point:
http://download.oracle.com/docs/cd/B19306_01/network.102/b14213/sqlnet.htm#sthref615 )
Good luck,
Sql_hash_Value 10g for oracle submitted jobs
dejan, September 28, 2007 - 6:40 am UTC
Hi Tom,
I am facing the same problem as Anil from Singapore in this page. Problem starts with 10g and I can't see (or I don't know how to look ..) what Active background sessions are doing at the moment. I can't see currently running SQL because Sql_hash_value is null for background sessions initated by jobs, and this was not the case in version 9i.
--
It is realy annoying or better worrying not to know what active sessions are doing right now. What will you suggest?
--
Thanks
September 28, 2007 - 5:40 pm UTC
what is your precise version number.
and have you considered instrumenting your jobs with dbms_application_info so they tell you exactly what they are doing and how fast they are doing it?
Sql_hash_Value 10g for oracle submitted jobs
dejan, October 05, 2007 - 11:26 am UTC
As I said I saw v$session.sql_hash_value for job sessions in 9i (9.1%, 9.2% including 9.2.0.8), but this is not the case in 10g (10.2.0.2 and 10.2.0.3)
--
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bi
PL/SQL Release 10.2.0.2.0 - Production
CORE 10.2.0.2.0 Production
TNS for Solaris: Version 10.2.0.2.0 - Production
--
I'am fan of using dbms_application_info an i try to instrument as much as possible of my code. Usually I use action and module to assign package name and location
But in all cases it is simply not posibble to make such an instrumentation ..
--
When some job (running problematic code) needs investigation my logic was (in 9i) to find active sessions and with sql_hash_value go to v$sql, v$sql_plan and v$sqltext_with_newlines. In very short time I could point to
piece of problematic code. This now is impossible to do for jobs in 10g. (except instrument single sql with dbms_application_info)
--
DECLARE
v_jobno number;
v_plsql_block varchar2(2000) :=
'
Declare
v_cnt integer;
Begin
--DBMS_APPLICATION_INFO.Set_Action(''JOB-test'');
for i in 1 .. 100 loop
Select count(*) into v_cnt from all_objects;
end loop;
end;
';
--
BEGIN
dbms_job.submit(v_jobno, v_plsql_block);
END;
/
commit
/
Regards Dejan
10g jobs - v$session.sql_address set to 00
Remi Dumont, November 06, 2007 - 3:44 pm UTC
It seems impossible to fetch the current sql query running in a session when that session is a job using the v$session.sql_address reference. It is set to 00. (10g Enterprise Edition Release 10.2.0.3.0)
Anil from Singapore and dejan from Slovenia already wrote about this in this thread.
Besides using "user-defined" ways (like dbms_application_info.set_client_info), is there a way to get it ?
10g jobs - v$session.sql_address set to 00
Remi Dumont, November 09, 2007 - 8:45 am UTC
About my previous posting... I finally found an answer which I am sharing:
Search for the following patch at Oracle: Bug 5928612 - SQL_ADDRESS COLUMN IN V$SESSION VIEW FOR JOB IS ZERO (Doc ID: Note:433381.1)
v$sql and v$session,
A reader, September 05, 2008 - 5:49 pm UTC
Hello,
When a new combination of address, hash_value, child_number (unique combination) enters into V$SQL, will a corresponding entry create in V$SESSION as well?
I have seen a cursor that was created (based on first_load_time) an hour ago in V$SQL, but I didn't see an entry in V$SESSION view. I have automated a job that runs every 5 minutes collecting info from v$session into my own table, but I am collecting sql_hash_value only along with other typical v$session attributes.
Now I modified my job to collect sql_address and sql_id as well.
September 08, 2008 - 8:33 am UTC
... will a corresponding entry create in V$SESSION
as well?
..
no? I don't see the relationship you are inferring. v$session has an entry for each currently established session. v$sql has an entry for each parsed sql representation in the shared pool. v$session MIGHT point you to a sql statement in v$sql if that session represented in v$session has a "current sql" statement to point to. But most of the sql in v$sql won't be pointed to at any given point in time (in general, there are hundreds of v$session entry, thousands to tens of thousands of v$sql entries)
Trace PLSQL
Jatin, September 30, 2009 - 7:49 am UTC
Hi Tom
I face an issue wherein an application developer came calling that a job is taking unnessarily long to complete (it took some 3 hrs earlier on a warehouse, now it was not completing in 5-6 hours). As she provided me no other information but the sid, I tried to find out the sql which was running using the code provided above, and this is what I get:
declare
cutoff date;
timeout number:=18000; -- 5 hours
begin :jobname := nvl('mip-odspre.ldtablespre',NULL); cutoff:=EDWHRUN.RETURN_CUTOFF; -- ODS.LOAD_ODS_MIP_PKG.ODS_DOMAINS(trunc(cutoff-1), trunc(cutoff+1),timeout); ---- exception when others then DBMS_OUTPUT.PUT_LINE('Error in (' || sqlcode || ', ' || Sqlerrm || ')'); end;I also checked if there are any locks or if there is something in session longops but I did not get to the rootcause. (however later issue was fixed on gathering stats and re running the job)
Can you help me by telling how to interpret performance bottleneck in such a situation? The problem is that I cannot trace to the SQL level what is happening and what is the bottleneck in the database..? (i.e. even though the stats were stale how could have I generated a plan in that case?)
Please help.
Regards
Jatin
v$sqlarea and v$session
Rupal, March 03, 2010 - 5:04 am UTC
Hi Tom,
I need to fetch and save the information of all sql queries executed by the users into the database.I am firing the following query in my database every minute and saving the data to a database table:
SELECT s.USERNAME,
s.OSUSER,
s.MACHINE,
s.TERMINAL,
s.PROGRAM,
s.MODULE,
a.sql_text,
to_char(s.LOGON_TIME,'DD-MON-YYYY HH24:MI:SS') AS LOGON_TIMESTAMP,
s.service_name
FROM v$session s, v$sqlarea a
WHERE a.address = s.sql_address
However, I am missing out on many queries being fired by the users - some of the standard daily reports being run on the database.Not all the queries executed by the users are getting logged into the table.
Can you please guide if I am doing something wrong in above query? or What might be the reason for missing out on many queries?
Your help will be highly appreciated.
Thanks a lot for your time!
March 03, 2010 - 7:16 am UTC
... I need to fetch and save the information of all sql queries executed by the
users into the database ...
technically - not possible. You will always stand a chance with sampling of missing that.
Your query retrieves sql that is currently being executed. It is missing all of the SQL that was executed 5 seconds ago, but isn't currently being executed. Think about what your query does - make sure you understand it. If you join v$sqlarea (which an an AGGREGATE of v$sql) to v$session like that - what will you get? If you cannot answer that - time to hit the docs and read about the views until you do!
However, on a good note, the database already does this, efficiently. We call it AWR - or statspack.
Instead of doing this every minute (that would be a complete waste of resources. At least tuning your database would be easier if you did this, we would start by disabling your job and seeing massive improvements in performance!), you would let AWR gather it's hourly samples - or you would schedule a statspack job every 30-60 minutes. You would not only get the bulk of the SQL (the high load SQL anyway - the relevant, important SQL), you would get much more information as well.
So, in short, do not do this.
Use the builtin tools - AWR or statspack.
capture SQLs in a sequential order,
A reader, March 11, 2010 - 10:22 am UTC
Hello,
We have DML and even DDL statements that are bundled together in a package (or a procedure). These statements run serially. This stored package runs often in the database (depends on the flat files we get from external resource). There is no checkpoint between those DML statements to capture the start time and the end time. We are trying to find out a method of capturing the start time of the DML statement, end time time along with other values like execution time, rows processed and so on.
I have a script that runs every 2 hours and captures information from gv$sql. What I don't get from this information is the sequential order of the SQL statements that were executed through a package.
For example, if my package contains the following statements
1. INSERT into ABC
2. UPDATE XYZ
3. ANALYZE ABC
4. MERGE PQR
5. SPLIT PARTITION ABC
6. CREATE INDEX ON TEMP_XYZ
7. DELETE from PQR
All the above run serially. They can run in parallel but the parameter value for the package will be different, in other words, if they run in parallel then it will be through a different process.
How can I capture the information from v$sql or any data dictionary view that tells me the start date and end date of statement 1, start date and end date of statement 2 and so on.
Thanks,
March 12, 2010 - 2:12 pm UTC
you cannot get that from any of the existing v$ tables - it doesn't exist, in order to do that, we would have to (for every application, for every session) track every sql in order - in other words, we'd have to have sql_trace enabled for every session - for ever. And that'll never work.
ASH (active session history) can give you SOME insight, but it will not be finely detailed and could easily miss bits and pieces because it uses sampling in the background, not an active 'trace'
You would instrument your code to capture this application specific level of detail. In fact, this is something that should be thought about in the design of virtually every software module - every time (but hardly ever is :( )
This is something you'll want to go back to the code and add - definitely, you'll never be sorry you did - and you'll want to ensure that it is DESIGNED into every future bit of code created at your place of work. In all probability, you don't really just want "every sql in sequential order" but you'll want things like "the time is took to perform process X, which does 1,000 sql's" - no need to time the 1,000 sqls, just need to time process X. By specifying that sort of need at design time, you'll get precisely what you need.
understanding sql_address
Vishal, February 19, 2011 - 12:46 am UTC
Hi Tom,
I am writing down my understanding of sql_address and have a doubt for the same
1.sql_address is the address of a parsed query in the shared pool
2. sql_id is what oracle generates for every parsed query. If oracle finds the same query being executed twice then the second execution of the query also gets the previous sql_id
Now, what if the same query is fired from 2 different clients at the same time (each client is having a different bind variable value,although the query is the same)
Will we have 2 rows in v$sqlarea, each with different sql_id. Again will the sql_address for both these queries be the same
Regards,
Vishal
February 20, 2011 - 12:18 pm UTC
1) sure
2) sql_id is a hash of a sql statement, there is a 1:1 relationship from a sql statement to it's hash and a 1:m relationship from the hash to the sql statements (eg: it is possible for two or more select statements that are different looking to share the same hash, but if you have two identical sql statements, they will ALWAYS have the same hash)
answer: you will almost certainly have one sql statement in the shared pool since you will be both trying to latch (lock, serialize) the same regions of memory.
when I say almost certainly - that leaves rooms for things like "session 1 has all_rows and session two has first_rows optimization" and "we are using cursor_sharing = similar" and so on.
More info
Vishal, February 21, 2011 - 4:28 am UTC
Hi Tom,
Many thanks for your kind reply.
Im quoting a part of your reply below:
"you will almost certainly have one sql statement in the shared pool since you will be both trying to latch (lock, serialize) the same regions of memory"
Lets say that the query of the 1st session is trying to latch a specific linked list of the hash table in the shared pool.
Is it possible that the bind value of the 2nd query be such that it latches some other linked list of the hash table.If this case is possible then we are probably saying that the 2nd query would wait to get the same sql_id although it has to retrive data from a different block (I am assuming that duplicate sql_id's do not exist in the shared pool,not sure if this assumption is correct or not)
Again if we assume that the blocks of the data required by both queries fall under the same linked list, then would it be a sequential process of data retrival for the 2 queries. (Like the 1st query latches 1st and gets the data and the 2nd query gets the same sql_id only after the completion of the 1st query)
In case you feel that I should do some homework before approaching you then please point me to some detail documentation by oracle on this subject as I couldnt find any
Thanks once again for such an sharing your wisdom
Regards,
Vishal
February 22, 2011 - 9:38 pm UTC
... Is it possible that the bind value of the 2nd query be such that it latches
...
Not if we are looking at a sql_id, they all go to the same place based on the sql_text, not on binds, not on anything else other than sql text.