Home » SQL & PL/SQL » SQL & PL/SQL » How to tune Query on Large Oracle Table
How to tune Query on Large Oracle Table [message #672716] Thu, 25 October 2018 07:23 Go to next message
sacharya2017
Messages: 19
Registered: January 2017
Junior Member
Hi,
I would like to know the ways to Tune SQL statements using large oracle non-partitioned table, large in size and large in number of rows.

I can think of ways

1>DB_FILE_MULTIBLOCK_READ_COUNT?
2> turn on parallelism if have enough resources?
3>have large buffer cache and cache the table data?
4>what else?

thanks
Re: How to tune Query on Large Oracle Table [message #672718 is a reply to message #672716] Thu, 25 October 2018 07:32 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
sacharya2017 wrote on Thu, 25 October 2018 05:23
Hi,
I would like to know the ways to Tune SQL statements using large oracle non-partitioned table, large in size and large in number of rows.

I can think of ways

1>DB_FILE_MULTIBLOCK_READ_COUNT?
2> turn on parallelism if have enough resources?
3>have large buffer cache and cache the table data?
4>what else?

thanks
1) determine where current time is being spent.
2) devise alternative to reduce that time.


Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/mv/msg/84315/433888/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof



Re: How to tune Query on Large Oracle Table [message #672719 is a reply to message #672718] Thu, 25 October 2018 07:41 Go to previous messageGo to next message
gazzag
Messages: 1082
Registered: November 2010
Location: Bristol, UK
Senior Member
And INSERT scripts for any data, presumably? Smile
Re: How to tune Query on Large Oracle Table [message #672720 is a reply to message #672718] Thu, 25 October 2018 07:42 Go to previous messageGo to next message
sacharya2017
Messages: 19
Registered: January 2017
Junior Member
hi Black Swan,

I do not have any of this info that you have asked. The reason I am posting this question is because it was asked in interview so i'm just looking for general guideline to start with or what should be DBA's approach if you were to tune SQL like this. yes you look at the explain plan and you see it's doing full tables scan and it requires most of the data to satisfy the query.

thanks
Re: How to tune Query on Large Oracle Table [message #672722 is a reply to message #672720] Thu, 25 October 2018 08:09 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
sacharya2017 wrote on Thu, 25 October 2018 05:42
hi Black Swan,

I do not have any of this info that you have asked. The reason I am posting this question is because it was asked in interview so i'm just looking for general guideline to start with or what should be DBA's approach if you were to tune SQL like this. yes you look at the explain plan and you see it's doing full tables scan and it requires most of the data to satisfy the query.

thanks
Did you get a job offer as a result of this interview?
Re: How to tune Query on Large Oracle Table [message #672723 is a reply to message #672722] Thu, 25 October 2018 08:31 Go to previous messageGo to next message
sacharya2017
Messages: 19
Registered: January 2017
Junior Member
BlackSwan wrote on Thu, 25 October 2018 08:09
sacharya2017 wrote on Thu, 25 October 2018 05:42
hi Black Swan,

I do not have any of this info that you have asked. The reason I am posting this question is because it was asked in interview so i'm just looking for general guideline to start with or what should be DBA's approach if you were to tune SQL like this. yes you look at the explain plan and you see it's doing full tables scan and it requires most of the data to satisfy the query.

thanks
Did you get a job offer as a result of this interview?
no I did not. not sure why are you asking this?
Re: How to tune Query on Large Oracle Table [message #672734 is a reply to message #672720] Thu, 25 October 2018 14:15 Go to previous messageGo to next message
John Watson
Messages: 8351
Registered: January 2010
Location: Global Village
Senior Member
sacharya2017 wrote on Thu, 25 October 2018 13:42
hi Black Swan,

I do not have any of this info that you have asked. The reason I am posting this question is because it was asked in interview so i'm just looking for general guideline to start with or what should be DBA's approach if you were to tune SQL like this. yes you look at the explain plan and you see it's doing full tables scan and it requires most of the data to satisfy the query.

thanks
You were probably being asked about the method you would follow. My answer would be:
1. Determine how Oracle is executing the SQL
2. Work out why Oracle is executing it that way
3. See if any other ways would be better
4. If so, how can I push the CBO in that direction

Your suggestions a]were all precise technical changes which might have no relevance to the problem.
Re: How to tune Query on Large Oracle Table [message #672777 is a reply to message #672734] Fri, 26 October 2018 08:51 Go to previous messageGo to next message
EdStevens
Messages: 1250
Registered: September 2013
Senior Member
John Watson wrote on Thu, 25 October 2018 14:15
sacharya2017 wrote on Thu, 25 October 2018 13:42
hi Black Swan,

I do not have any of this info that you have asked. The reason I am posting this question is because it was asked in interview so i'm just looking for general guideline to start with or what should be DBA's approach if you were to tune SQL like this. yes you look at the explain plan and you see it's doing full tables scan and it requires most of the data to satisfy the query.

thanks
You were probably being asked about the method you would follow. My answer would be:
1. Determine how Oracle is executing the SQL
2. Work out why Oracle is executing it that way
3. See if any other ways would be better
4. If so, how can I push the CBO in that direction

Your suggestions a]were all precise technical changes which might have no relevance to the problem.
Indeed, it appears that the OP falls into the too common trap of approaching an interview like a college semester exam instead of approaching it like an discussion. And assuming that, like an exam, every question is supposed to have a precise technical answer when quite often they are probing the candidate's methods more than his knowledge of specifics.
Re: How to tune Query on Large Oracle Table [message #672803 is a reply to message #672734] Fri, 26 October 2018 12:19 Go to previous messageGo to next message
sacharya2017
Messages: 19
Registered: January 2017
Junior Member
Hi John,
Thank you for your valuable thoughts and ideas. I have tried to write out honest reply that I had got from them as answers to your questions/thoughts.
1. Determine how Oracle is executing the SQL
==>Execution plan shows it's doing full table scan, most of the time is spent there. (event is db file scattered read).

2. Work out why Oracle is executing it that way
==> Oracle CBO is executing that way because more than 90% of the table data is being requested by that Query.

3. See if any other ways would be better
==> Turning on Parallelism to some degree would help to some extent but they don't want Direct path read as this reporting query is being run more often and don't want to by-pass buffer cache.

thanks


Re: How to tune Query on Large Oracle Table [message #672806 is a reply to message #672777] Fri, 26 October 2018 12:51 Go to previous message
sacharya2017
Messages: 19
Registered: January 2017
Junior Member
EdStevens wrote on Fri, 26 October 2018 08:51
John Watson wrote on Thu, 25 October 2018 14:15
sacharya2017 wrote on Thu, 25 October 2018 13:42
hi Black Swan,

I do not have any of this info that you have asked. The reason I am posting this question is because it was asked in interview so i'm just looking for general guideline to start with or what should be DBA's approach if you were to tune SQL like this. yes you look at the explain plan and you see it's doing full tables scan and it requires most of the data to satisfy the query.

thanks
You were probably being asked about the method you would follow. My answer would be:
1. Determine how Oracle is executing the SQL
2. Work out why Oracle is executing it that way
3. See if any other ways would be better
4. If so, how can I push the CBO in that direction

Your suggestions a]were all precise technical changes which might have no relevance to the problem.
Indeed, it appears that the OP falls into the too common trap of approaching an interview like a college semester exam instead of approaching it like an discussion. And assuming that, like an exam, every question is supposed to have a precise technical answer when quite often they are probing the candidate's methods more than his knowledge of specifics.
Hi EdStevens,
Yes, The process should be more towards discussion rather than technical changes. but as you're being judged during the entire process not by just 1 or 2 but several professionals, so you got to bring your technical expertise/skills/experience to present the case. My Interview lasted for 7 hours(8:00-3:00). I was interviewed by more than 15 people. no lunch time.
The feedback I got after 4 days
"Mr. Sacharya you have an excellent personality.Your core Database skills/knowledge is very good. we all have liked you but.....we have a different requirements and hence not moving forward with you."

I don't regret what has happened as This does not decrease my Value not even a single bit just because it's their inability to see how good/worth am I. Life moves on so do I. The only reason I had asked this question here to see if I can improve and be better at something like this.

regards
Previous Topic: Generate Random Id's of existing Id's
Next Topic: How to make a trigger on the table for the history table
Goto Forum:
  


Current Time: Tue Sep 29 21:24:29 CDT 2020