Home » RDBMS Server » Performance Tuning » Difference in performance of a SQL Query for same execution plan (Oracle 10g)
icon9.gif  Difference in performance of a SQL Query for same execution plan [message #321582] Tue, 20 May 2008 15:04 Go to next message
amorphous4u
Messages: 34
Registered: December 2007
Location: Boston, US
Member

Hi,

I fired a query and retrieved the results in few seconds. The query is some thing like

SELECT C1
FROM table
WHERE c2 = val2
and c3 = val3;

But once i increased the number of columns in selection the performance decrease. And query is runing for minutes now.
Query become like
SELECT C1, C2, C3, C4
FROM table
WHERE c2 = val2
and c3 = val3;

Now the thing is execution plan has not changed.

I am not getting the idea how it has got slow.

Can anyone please help me out with this and let me know what I am doing wrong or what can i do to increase the performance?

Thanks and Regards,
Amit Verma
Re: Difference in performance of a SQL Query for same execution plan [message #321588 is a reply to message #321582] Tue, 20 May 2008 15:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Now the thing is execution plan has not changed.

Post it as well as all useful information about your table, indexes, statistics, optimizer parameters and so on.

Regards
Michel
Re: Difference in performance of a SQL Query for same execution plan [message #321624 is a reply to message #321582] Tue, 20 May 2008 21:23 Go to previous messageGo to next message
harrysmall3
Messages: 109
Registered: April 2008
Location: Massachusetts
Senior Member
Hi Amit,

I am very eager to see the full information as Michel notes for valued analysis but am curious if external factors have come into play such as peak system usage times which plague me at my shop.

Did you run both versions back to back and then after version two, which slowed down, did you switch back to version 1 with same results? With what you presented my instincts scream "concurrent activity" of some sort.

And only because this is a topic of great interest to me do I jump ahead with another blind hypothesis. Is C1 part of a concatonated index with C2 and C3 such that C1 could be retrieved directly from the index in query one, but with the expanded columns the table data blocks now must be accessed?

For some reason this is what popped into my head in replacement of the example columns - a demographic db with c3 = state, c2 = city, c1 = zip code as an index.

then query #1 = select zip code from demo_table where state = val and city = val.

Then the query gets expanded, eg, as select zip code, population, area_size, num_voters where state = same val and city = same val.

Ok, I ramble!

Look forward to hearing more Smile
Regards,
Harry
Re: Difference in performance of a SQL Query for same execution plan [message #321632 is a reply to message #321582] Tue, 20 May 2008 22:21 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>I fired a query and retrieved the results in few seconds. The query is some thing like

>But once i increased the number of columns in selection the performance decrease. And query is runing for minutes now.

Forgive me but I seriously doubt the response time change is reproducible & dependent upon the number of columns returned.

Keep in mind that Oracle reads whole data blocks at a time.
Therefore the whole row is returned into the SGA; regardless of which columns exist or not in the SELECT clause.

Re: Difference in performance of a SQL Query for same execution plan [message #321642 is a reply to message #321632] Tue, 20 May 2008 23:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Forgive me but I seriously doubt the response time change is reproducible & dependent upon the number of columns returned.


Maybe the added columns are LOB or chained/overflow ones. Wink

Regards
Michel
Re: Difference in performance of a SQL Query for same execution plan [message #321792 is a reply to message #321642] Wed, 21 May 2008 05:58 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
[offtopic alert]I thought when chaining occurs, the WHOLE ROW is migrated, not just columns?

Or are you talking about rows that exceed a single block? But that's not chaining, is it?[/offtopic alert]
Re: Difference in performance of a SQL Query for same execution plan [message #321811 is a reply to message #321792] Wed, 21 May 2008 06:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, I was talking about rows that are greater than a block or that have more than 254 columns.

Regards
Michel
Re: Difference in performance of a SQL Query for same execution plan [message #321920 is a reply to message #321582] Wed, 21 May 2008 17:08 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
Need to post the explain plans.

Maybe an index was invalidated inbetween executions, maybe column C4 is not indexed but all others are, who knows.

Re: Difference in performance of a SQL Query for same execution plan [message #322235 is a reply to message #321920] Thu, 22 May 2008 20:51 Go to previous message
harrysmall3
Messages: 109
Registered: April 2008
Location: Massachusetts
Senior Member
That was my original thought too, under criteria per the original post that the execution plans did not change when the second query was parsed.

The first query could retrieve the data right from the index and not have to access the table data blocks. I've much to learn in these regards though.

Previous Topic: DMT
Next Topic: Incorrect optimizer_index_caching affect on plan optimization
Goto Forum:
  


Current Time: Sat Dec 03 10:10:29 CST 2016

Total time taken to generate the page: 0.08195 seconds