Home » RDBMS Server » Performance Tuning » Issues with V$SQLTEXT (Oracle 10g R2)
Issues with V$SQLTEXT [message #384885] Thu, 05 February 2009 11:48 Go to next message
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
Smile
Re: Issues with V$SQLTEXT [message #384887 is a reply to message #384885] Thu, 05 February 2009 11:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Instead of rating your own question (we are pretty sure you are proud of it) you should read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Use SQL*Plus and copy and paste your sessions.

Regards
Michel
Re: Issues with V$SQLTEXT [message #384895 is a reply to message #384887] Thu, 05 February 2009 12:07 Go to previous messageGo to next message
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 #384896 is a reply to message #384885] Thu, 05 February 2009 12:11 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
what business problem are you really trying to solve?
Re: Issues with V$SQLTEXT [message #384900 is a reply to message #384896] Thu, 05 February 2009 12:18 Go to previous messageGo to next message
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 Go to previous messageGo to next message
BlackSwan
Messages: 25039
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 #384905 is a reply to message #384903] Thu, 05 February 2009 12:43 Go to previous messageGo to next message
manusunil
Messages: 7
Registered: August 2007
Junior Member
BlackSwan wrote on Thu, 05 February 2009 12:25
>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.


My bad, of course the bad sql under the package is causing the issue and we are not able to identify them...

the queries are fired by the application and not from sql prompt... nevertheless we can generate the trace on those sessions but as i said earlier the issue is shortlived and the traces are not really coming up as big help Sad

it happened 3 times in last week and we dont really have the answer to the actual issue.

it would surely be of great help if we could figure out what exactly happens by running the "EXEC" that thereafter the qry is visible in v$sqltext

Thanks for understanding
Manu
Re: Issues with V$SQLTEXT [message #384906 is a reply to message #384905] Thu, 05 February 2009 12:46 Go to previous messageGo to next message
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 #384907 is a reply to message #384885] Thu, 05 February 2009 12:55 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you.

Most applications contain hundreds (or thousands) of SELECT statements.

Assuming for the sake of this discussion you can do as you desire,
How do you identify the "bad" SQL contained within v$SQLTEXT;
as opposed the "good" SQL?

With free advice, you get what you paid for it!

Good Luck on your quest for the Golden Fleece.




Re: Issues with V$SQLTEXT [message #384909 is a reply to message #384885] Thu, 05 February 2009 13:03 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you.

>there are no deadlocks..no locked objects..no invalid indexes etc..no stale statistics..
OK, but what about mutex waits?
default INITRANS values can be way too small when "high" number of simultaneous users are running application.
It took me weeks to track down the lurking culprits in 1 app.
Re: Issues with V$SQLTEXT [message #384919 is a reply to message #384907] Thu, 05 February 2009 14:39 Go to previous messageGo to next message
manusunil
Messages: 7
Registered: August 2007
Junior Member
BlackSwan wrote on Thu, 05 February 2009 12:55
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you.

Most applications contain hundreds (or thousands) of SELECT statements.

Assuming for the sake of this discussion you can do as you desire,
How do you identify the "bad" SQL contained within v$SQLTEXT;
as opposed the "good" SQL?

With free advice, you get what you paid for it!

Good Luck on your quest for the Golden Fleece.





I believe the first step would be to be able to identify the currently running sqls.. there after we may be able to dig further in to see if we can trace down to the bad sql.

Nevertheless, Thanks for the best wishes Smile

i have come back to home now, tomorrow i'll paste the exact output.

No matter how weired my request seems, let me assure you its genuine.

Thank you for your "free" advice and precious time.
Re: Issues with V$SQLTEXT [message #384921 is a reply to message #384885] Thu, 05 February 2009 14:48 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
>I believe the first step would be to be able to identify the currently running sqls.
Easier said than done!
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.
Re: Issues with V$SQLTEXT [message #384924 is a reply to message #384921] Thu, 05 February 2009 14:51 Go to previous message
manusunil
Messages: 7
Registered: August 2007
Junior Member
BlackSwan wrote on Thu, 05 February 2009 14:48

Easier said than done!



Smile 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
Previous Topic: Top 5 Events
Next Topic: update query tuning
Goto Forum:
  


Current Time: Tue Dec 06 06:31:50 CST 2016

Total time taken to generate the page: 0.15079 seconds