Select statement

Development (ABAP Development WorkBench, ABAP/4 programming)

Moderators: Snowy, thx4allthefish, YuriT, Gothmog

Post Reply
Deeler Stan
Posts: 410
Joined: Thu Mar 31, 2005 3:44 am

Select statement

Post by Deeler Stan » Tue Jun 17, 2008 3:51 am

We have moved from 4.0 to 4.7 on oracle

We have a problem with a select using sum aggregation, in that on the original 4.0 it takes 0 seconds, on 4.7 with oracle it takes 7.5 mins to run the same statement.

We have investigated and found out that it's the aggregation where the problem lies.

My question is, is there any way of finding out what the sql passed to the underlying system (oracle) is.

Cheers

Stan

m@t
Posts: 4119
Joined: Thu Sep 04, 2003 7:37 am
Location: Switzerland

Re: Select statement

Post by m@t » Tue Jun 17, 2008 4:15 am

SQL trace?

m@t
TULY Image The quality of answers is roughly proportional to the quality of the question.

The downside of being better than everyone else is that people tend to assume you're pretentious.

Deeler Stan
Posts: 410
Joined: Thu Mar 31, 2005 3:44 am

Re: Select statement

Post by Deeler Stan » Tue Jun 17, 2008 4:26 am

Hi M@T

Does that give the actual db native sql or sap sql.

ilya
Posts: 2327
Joined: Thu Oct 31, 2002 4:49 am
Contact:

Re: Select statement

Post by ilya » Tue Jun 17, 2008 4:59 am

Deeler Stan wrote:Hi M@T

Does that give the actual db native sql or sap sql.
Of course it gives you native SQL.
ilya

More input data: http://abaplog.wordpress.com
Sapfans ABAP FAQ: viewtopic.php?t=94198
Rich's ABAP Knowledge Corner: http://www.richard-harper.me.uk/Kb/default.html
Function modules documentation: http://www.se37.com

wilhitern1
Posts: 135
Joined: Thu May 15, 2008 4:25 pm
Location: Butterball LLC., Goldsboro, NC, USA
Contact:

Re: Select statement

Post by wilhitern1 » Tue Jun 17, 2008 7:34 am

M @t, Am I way off that there is likely a missing index here?
Neal Wilhite (WilhiteRN1)Image

m@t
Posts: 4119
Joined: Thu Sep 04, 2003 7:37 am
Location: Switzerland

Re: Select statement

Post by m@t » Tue Jun 17, 2008 10:31 am

That was my first thought too, but Deeler Stan said "We have investigated and found out that it's the aggregation where the problem lies."

m@t
TULY Image The quality of answers is roughly proportional to the quality of the question.

The downside of being better than everyone else is that people tend to assume you're pretentious.

Dr Sidewalk
Posts: 3531
Joined: Mon Oct 21, 2002 11:06 am
Location: Everywhere...

Re: Select statement

Post by Dr Sidewalk » Tue Jun 17, 2008 10:58 am

Jeez, without seeing the code you will NOT get an answer.

You've not identified whether the sql is accessing SAP tables or user tables, and until further posts you've not mentioned the indicies.

Post the code, then you'll get reasonable advice.

I suspect the version of Oracle has also been upgraded as well as the SAP version. Is this so?.

Dr Sidewalk.
"In the middle of difficulty lies opportunity" - Albert Einstein
"Money isn't everything in life, unless you don't have it"
"Fail to plan, plan to fail"
"Success is a journey, not a destination."

wilhitern1
Posts: 135
Joined: Thu May 15, 2008 4:25 pm
Location: Butterball LLC., Goldsboro, NC, USA
Contact:

Re: Select statement

Post by wilhitern1 » Tue Jun 17, 2008 11:51 am

I'd bet your right. When we wend from 4.5 to 4.7 we had to do 3 oracle upgrades.
Neal Wilhite (WilhiteRN1)Image

Dr Sidewalk
Posts: 3531
Joined: Mon Oct 21, 2002 11:06 am
Location: Everywhere...

Re: Select statement

Post by Dr Sidewalk » Wed Jun 18, 2008 3:40 am

But, Oracle tends to be squeaky clean so I bet it's not Oracle causing the problem.

Again, unless you post the problem SQL you've got no chance of anyone helping you with any success, which I assume is what you want.

Dr Sidewalk
"In the middle of difficulty lies opportunity" - Albert Einstein
"Money isn't everything in life, unless you don't have it"
"Fail to plan, plan to fail"
"Success is a journey, not a destination."

Deeler Stan
Posts: 410
Joined: Thu Mar 31, 2005 3:44 am

Re: Select statement

Post by Deeler Stan » Wed Jun 18, 2008 5:29 am

It's really a wierd one.

It's also not really my problem as I'm just helping out but.

Efffectively the code is

select werks sum( field1) sum( field2 ).... into (......) from ztable where.......

Now, this has gone from 4.0 where it takes seconds to 4.7 where it takes any time between 4 and 7 mins.

We deleted and re-created the index and that made no difference in timings.

If we do
select into table.
loop at table
at first.
sum
... etc...

it takes seconds.

Now, heres the interesting thing. The information I have is that the systems are on different partitions of the same box, both on the same version of oracle. Apparently, when the select is entered directly into oracle it takes seconds.

Figure that out, I can't.

abap_doctor
Posts: 6955
Joined: Thu Mar 25, 2004 11:29 am
Location: Airstrip One

Re: Select statement

Post by abap_doctor » Wed Jun 18, 2008 6:19 am

Deeler Stan wrote:Now, this has gone from 4.0 where it takes seconds to 4.7 where it takes any time between 4 and 7 mins.
I think you've hit it on the head... :shock:
The Doc
Tuly Idiot Magna Grand Docot Image

ssii
Posts: 222
Joined: Sat Nov 11, 2006 9:57 am
Location: France

Re: Select statement

Post by ssii » Wed Jun 18, 2008 6:28 am

abap_doctor wrote:
Deeler Stan wrote:Now, this has gone from 4.0 where it takes seconds to 4.7 where it takes any time between 4 and 7 mins.
I think you've hit it on the head... :shock:
Very interesting !!! thanks for the joke...
SSII is back

Dr Sidewalk
Posts: 3531
Joined: Mon Oct 21, 2002 11:06 am
Location: Everywhere...

Re: Select statement

Post by Dr Sidewalk » Wed Jun 18, 2008 10:59 am

What is the Oracle code created as a result of your Open SQL?. Can you capture it using ST05 and post.

You still have not posted the SQL statement. For example, is there a 'GROUP BY' clause in your SQL because I can't see one. Why abbreviate the code, just post the damn thing. You're just making it more difficult for anyone to help you.

So, post the complete SQL statement, along with the ST05 effeciency rating and Oracle SQL statement generated. I assume you know how to utilise ST05 for this purpose.

Dr Sidewalk
"In the middle of difficulty lies opportunity" - Albert Einstein
"Money isn't everything in life, unless you don't have it"
"Fail to plan, plan to fail"
"Success is a journey, not a destination."

Post Reply