Home » RDBMS Server » Performance Tuning » Typical Question of Query Performance
Typical Question of Query Performance [message #514473] Tue, 05 July 2011 01:02 Go to next message
Manoj.Gupta.91
Messages: 239
Registered: March 2008
Location: Delhi
Senior Member
Hi All,

I've been asked a question recently by someone.

Question : I've a query that will take 20 hours to execute. But I cann't wait for 20 hours and need to give output to customer after 3 hours. What will you do to finish this activity in a given duration.

My Answers :
1.) I'll look into the query execution plan and tune the query.
2.) I'll create indexes on the table as per the requirement.
3.) I'll use hints if required.
4.) I'll check with DBA may be he can help me in some Database Level parameter settings to execute it faster.
5.) I'll set CURSOR_SHARING = SIMILAR
6.) I'll execute the query in Parallel.


These were the all possible solutions I gave him. But he said that he has already done all these exercises. It looked like that he was not convinced with these answers.

Can anybody propose any possible solution to achieve this goal.

Thanks & Regards
Manoj

Report message to a moderator

Re: Typical Question of Query Performance [message #514478 is a reply to message #514473] Tue, 05 July 2011 01:28 Go to previous messageGo to next message
cookiemonster
Messages: 13959
Registered: September 2008
Location: Rainy Manchester
Senior Member
I'd be asking for more details of what the query actually does and work from there. The question is vague and your list is fairly decent.
I'd question point 5 though. Why have you included that?

Report message to a moderator

Re: Typical Question of Query Performance [message #514515 is a reply to message #514473] Tue, 05 July 2011 04:37 Go to previous messageGo to next message
michael_bialik
Messages: 621
Registered: July 2006
Senior Member
IMHO - your answers are just fine (except #5, as laredy mentioned).

I would like to get TKPROF as well.

Even if "someone" looked at EXPLAIN plan or created an indexes - it doesn't mean that he understood what EXPLAIN says or created a "correct" indexes.

HTH

Report message to a moderator

Re: Typical Question of Query Performance [message #514528 is a reply to message #514515] Tue, 05 July 2011 05:24 Go to previous message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
There is also the (often unpopular) possibility that it's just a massive query - sometimes they do just take a long time. It depends on what it is doing.


Another option, since it sounds like an interview question, is that it was not a database question but a how do you deal with impossible requests style instead.

Report message to a moderator

Previous Topic: Query doing FTS on a big table
Next Topic: Solution req :Performance issue with partitioned table
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Nov 28 13:28:36 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.