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

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

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?

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

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.

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.