Home » SQL & PL/SQL » SQL & PL/SQL » How is it possible?!
How is it possible?! [message #211457] Thu, 28 December 2006 16:15 Go to next message
leoaspivak
Messages: 3
Registered: December 2006
Location: San Francisco
Junior Member
Hello,

My question is in regards to one sql query (see attached). When I am running this query from sqlplus prompt it completes within 2 minutes. However, when I am running the exact same query within a simple COBOL program (execute this query, create cursor and then processes it), it never completes to create a cursor within 3 hours. I have to kill job.

I had verified that when COBOL runs it connects to a correct database as the sqlplus prompt that I am connecting manually. Local DBAs are clueless on what is going on and recommending to adjust hints on that query. But I can't understand why should I adjust query's hint if it runs fine in sqlplus.

Any advice (any at all) will be greatly appreciated.
Re: How is it possible?! [message #211460 is a reply to message #211457] Thu, 28 December 2006 16:30 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
I refuse to open any attachment from somebody that I don't know.
More than likely the problem is NOT the SQL statement itself, something else closely related to it.
Enable SQL_TRACE at level 12 & run the results thru TKPROF.
Alternatively what happens when you replace your SQL statement with the following:
SELECT SYSDATE FROM DUAL;
Re: How is it possible?! [message #211503 is a reply to message #211460] Fri, 29 December 2006 00:47 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Get rid of all those totally unnecessary parentheses and use some form of indentation, it will improve readability

One reason I can think of is that you execute the statement in sqlplus by replacing variables with hard-coded values. This will very much simplify the optimizer's job, and can result in a different execution plan.
Re: How is it possible?! [message #211563 is a reply to message #211457] Fri, 29 December 2006 07:50 Go to previous messageGo to next message
leoaspivak
Messages: 3
Registered: December 2006
Location: San Francisco
Junior Member
If I replace an existing SQL statements with SELECT DATE FROM DUAL, my binary runs just fine. I was suspecting that something is in sql statement itself but I just cannot trace what it it.

I also verified that parameters that were passed to that sql statement are the same as the one that I hardcoded in sql statement which I am running from sqlplus prompt.
Re: How is it possible?! [message #211570 is a reply to message #211563] Fri, 29 December 2006 09:28 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
It is not the value of the parameters, it is the fact you provide the values on PARSE time to the optimizer via sqlplus, whereas the optimizer has no notion of what values you are going to issue through your cobol program.

Imagine this:

There is a table with 1 million rows.
All these rows but one have a value 'X' for one column.
This column is indexed.

Now, suppose you are the optimizer. I give you these queries
1) select * from my_tab where my_col = 'Y';
2) select * from my_tab where my_col = 'X';
3) select * from my_tab where my_col = :my_val;

It is clear to see that query 1 will be very fast when using the index, since there is only 1 row within the million with value 'Y'.
The second is also easy to spot: use a full table scan, because otherwise you have to do 999,999 index-lookups.
The third one though is NOT clear. What to do? Gamble you will provide 'X' and use a FTS as execution plan or assume :my_val will be 'Y' and use the index?
This is exactly what is going on in your situation. The Cobol-version has no idea what values your parameters are going to get AT PARSE TIME.
To tune this query, replace the hard-coded values in sqlplus with variables as well, so you have a comparable situation.

[Edit: Removed empty lines, to avoid annoying andrew_again Wink]

[Updated on: Fri, 29 December 2006 09:29]

Report message to a moderator

Re: How is it possible?! [message #211574 is a reply to message #211570] Fri, 29 December 2006 10:20 Go to previous message
leoaspivak
Messages: 3
Registered: December 2006
Location: San Francisco
Junior Member
Thanks for advice. I will try it today.
Previous Topic: Inserting record into PL/SQL Tables
Next Topic: Downsides of Data Denormalization
Goto Forum:
  


Current Time: Thu Dec 08 14:38:10 CST 2016

Total time taken to generate the page: 0.11025 seconds