| Issues with V$SQLTEXT [message #384885] |
Thu, 05 February 2009 11:48  |
manusunil
Messages: 7
Registered: August 2007
|
Junior Member |
|
|
session 1:
==========
-- creating my packages
sql>conn myusr/usr@test
connected
sql> create or replace package mypkg as
procedure main;
procedure main2;
procedure main3;
procedure main4;
procedure main5;
procedure main6;
end;
/
Package Created
sql>create or replace package body mypkg as
procedure main is
begin
main2;
end;
procedure main2 is
begin
main3;
end;
procedure main3 is
begin
main4;
end;
procedure main4 is
begin
main5;
end;
procedure main5 is
begin
main6;
end;
procedure main6 is
begin
for i in 1..100000
loop
update mytbl set mycol=myvalue where mycol2=mycond;
end loop;
end;
end;
/
Package Created
-- running the package
sql>call mypkg.main();
-- the package is in progress
session 2:
========
sql>conn sys as sysdba/mypwd@test
Connected
sql> select sql_text from v$sqltext where hash_value = (select sql_hash_value from v$session where username='myusr') order by piece;
sql_text
=======
call mypkg.main();
POINT TO NOTE: IT IS NOT SHOWING ME THE QUERY BUT THE CALL MADE TO THE PACKAGE
Session 1
========
--pressed ctrl+c to cancel the package call
sql>exec mypkg.main();
-- the package is in progress
session 2:
========
sql> select sql_text from v$sqltext where hash_value = (select sql_hash_value from v$session where username='myusr') order by piece;
sql_text
=======
update mytbl set mycol=myvalue where mycol2=mycond;
NOW, WHEN I USED "EXEC" INSTEAD OF "CALL", I CAN SEE THE SQL QUERY WHICH IS IN PROGRESS
NOW THE BEST PART
Session 1
========
--pressed ctrl+c to cancel the package call
sql>call mypkg.main();
-- the package is in progress
session 2:
========
sql> select sql_text from v$sqltext where hash_value = (select sql_hash_value from v$session where username='myusr') order by piece;
sql_text
=======
update mytbl set mycol=myvalue where mycol2=mycond;
NOW WHEN I CANCEL AND USE THE "CALL" IT SHOWS ME THE QUERY.
I HAVE TRIED VARIOUS COMBINATION & PERMUTATIONS
AND MY OBSERVATION IS THAT WHEN I USE "EXEC"...
EVEN IF I CANCEL IT IN MIDDLE ...
THEN I CAN SEE THE QUERY IN PROGRESS
AND THEREAFTER EVEN IF I USE "CALL" I CAN SEE THE QUERY
INFERENCE: There is some condition/parameter/session level setting that is getting enabled by the "EXEC"
and hence after that i can see the query in progress even when i use "CALL"
QUESTION: .. WHAT IS THAT CONDITION/PARAMETER/SETTING
Thanks in advance for your support
|
|
|
|
|
|
| Re: Issues with V$SQLTEXT [message #384895 is a reply to message #384887] |
Thu, 05 February 2009 12:07  |
manusunil
Messages: 7
Registered: August 2007
|
Junior Member |
|
|
Dear Michel
I am not proud of anything.. but in dire need of a solution
This issue is a severity one escalation... and we are not able to find any solution.
Have raised SR with ORACLE and awaiting their response.
the code of course is a fake one... that's because cant put the actual code on a public forum, m sure u'll understand that.
also kindly note that oracle version is already mentioned in the thread name =. "Issues with V$SQLTEXT (Oracle 10g R2)"
hope u'll not mind my naive post formatting techniques and would help me in finding the solution.
Thanks
Manu
[Updated on: Thu, 05 February 2009 12:13] Report message to a moderator |
|
|
|
|
|
| Re: Issues with V$SQLTEXT [message #384900 is a reply to message #384896] |
Thu, 05 February 2009 12:18 |
manusunil
Messages: 7
Registered: August 2007
|
Junior Member |
|
|
| BlackSwan wrote on Thu, 05 February 2009 12:11 | what business problem are you really trying to solve?
|
The actual issue lies with certain packages which create bottleneck and "temporary" performance concerns on the database.
And we are only getting the Package names in production and unable to trace it to the problematic query.
While doing R&D i happen to run across this scenario.. that when i call the package with "exec" i can see the code.
But the point is.. issue comes in production and not in test...
and in production this is being called by the application
Thanks
Manu
|
|
|
|
| Re: Issues with V$SQLTEXT [message #384903 is a reply to message #384885] |
Thu, 05 February 2009 12:25 |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
>The actual issue lies with certain packages which create bottleneck and "temporary" performance concerns on the database.
Packages (PL/SQL) are not the bottleneck; "bad" SQL is the problem.
ALTER SESSION SET SQL_TRACE=TRUE
process the resultant trace file with TKPROF.
The output will show which are slow running SQL & then tune them.
[Updated on: Thu, 05 February 2009 12:28] Report message to a moderator |
|
|
|
|
|
| Re: Issues with V$SQLTEXT [message #384906 is a reply to message #384905] |
Thu, 05 February 2009 12:46 |
manusunil
Messages: 7
Registered: August 2007
|
Junior Member |
|
|
also kindly note
there are no deadlocks..no locked objects..no invalid indexes etc..no stale statistics...
we have verified the stats of all the objects referred in the pkg by the sqls!
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Issues with V$SQLTEXT [message #384924 is a reply to message #384921] |
Thu, 05 February 2009 14:51 |
manusunil
Messages: 7
Registered: August 2007
|
Junior Member |
|
|
| BlackSwan wrote on Thu, 05 February 2009 14:48 |
Easier said than done!
|
Indeed!
| BlackSwan wrote on Thu, 05 February 2009 14:48 |
I believe that V$SQLTEXT holds current AND past queries.
SELECT COUNT(*) FROM V$SQLTEXT WHERE PIECE = 1 AND SQL_TEXT LIKE 'SELECT%';
Please run the above on Production DB server & post the results.
|
Surely i'll do that
Thanks
Manu
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
