Home » RDBMS Server » Performance Tuning » FTS - Full Table Scan - (merged 3)
FTS - Full Table Scan - (merged 3) [message #416946] Wed, 05 August 2009 05:46 Go to next message
satish2130
Messages: 5
Registered: August 2009
Location: India
Junior Member

I have one table. It has 2 indexes only.
I am doing INSERT AS SELECT statement.
It's internally doing FTS.
It takes around 18-21 mins.
Presently, the value of the parameter, db_file_multiblock_read_count is 16.

I applied optimizer hint opt_param. It works fast.

I applied the hint "opt_param('db_file_multiblock_read_count',64)".
I got 50% gain..

How do we do to complete FTS within the less time ?
Re: FTS - Full Table Scan [message #416951 is a reply to message #416946] Wed, 05 August 2009 06:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
How do we do to complete FTS within the less time ?

Quote:
I applied the hint "opt_param('db_file_multiblock_read_count',64)".
I got 50% gain..

Is this not the answer?
Increase the value of the parameter.

Regards
Michel
Re: FTS - Full Table Scan - [message #416955 is a reply to message #416946] Wed, 05 August 2009 06:44 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:
Oracle 10gR2 Database - Optimizer 9i


How did you manage to patch the 9i Optimizer into the 10g Database? Very Happy

Well. As to your "Problem". How many rows are you inserting? If you are inserting ALL rows from the source table, then there is now way to avoid a full table scan, since you need the data from the entire table.
Re: FTS - Full Table Scan [message #416956 is a reply to message #416951] Wed, 05 August 2009 06:44 Go to previous messageGo to next message
satish2130
Messages: 5
Registered: August 2009
Location: India
Junior Member

Hi,

I would like to know any other solution on this??

if I applied 64 using opt_param hint, It will complete 5-8 min's for FTS

For example, SELECT /*+ opt_param('db_file_multiblock_read_count',64) */

The above hint is applied for the FTS. It works fine. We could not use this hint because It might be use more CPU. In the sametime, there are other jobs are running.

We could not use the index because the statement returns more than 60%. The selectivity is more.

I need to complete this statement execution < 8 Min's

Could you please provide any other solutions?

Thank you in advance.

Thanks and Regards,
Satish
Re: FTS - Full Table Scan - [message #416957 is a reply to message #416955] Wed, 05 August 2009 06:49 Go to previous messageGo to next message
satish2130
Messages: 5
Registered: August 2009
Location: India
Junior Member


there are some issues on the production. we are still keep 9i optimizer in the Oracle 10g. Smile

I would like to know the best reponse time using FTS.

is there any hints for FTS to get better performance?

Plz. provide any suggestion.

Thanks,
Satish
Re: FTS - Full Table Scan - [message #416960 is a reply to message #416957] Wed, 05 August 2009 06:58 Go to previous messageGo to next message
cookiemonster
Messages: 12418
Registered: September 2008
Location: Rainy Manchester
Senior Member
satish2130 wrote on Wed, 05 August 2009 12:49

there are some issues on the production. we are still keep 9i optimizer in the Oracle 10g. Smile



That's not possible so maybe you should explain what you mean.

Quote:

I would like to know the best reponse time using FTS.

is there any hints for FTS to get better performance?

Plz. provide any suggestion.

Thanks,
Satish


As far as I'm aware virtually nothing changes the speed of a full table scan apart from the parameter you've already found.

Re: FTS - Full Table Scan - [message #416961 is a reply to message #416960] Wed, 05 August 2009 07:07 Go to previous messageGo to next message
satish2130
Messages: 5
Registered: August 2009
Location: India
Junior Member

Thank you for your reply,

I am not aware any issues while migrating from 9i to 10g.
I have taken up this database support recently.

What is the impact if we use this optimzer hint opt_param for altering the INIT parameter default values.

Pleae let me know if you have any updates on this..

Thanks,
Satish
Re: FTS - Full Table Scan - [message #416964 is a reply to message #416961] Wed, 05 August 2009 07:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
What is the impact if we use this optimzer hint opt_param for altering the INIT parameter default values.


It uses this value for this query.

Regards
Michel
Re: FTS - Full Table Scan - [message #416965 is a reply to message #416964] Wed, 05 August 2009 07:40 Go to previous messageGo to next message
satish2130
Messages: 5
Registered: August 2009
Location: India
Junior Member

Ok.

Is there any negative impact on the other jobs if they are running simultaneously??

Is there any chance to increase the buffer size/Temporary Segement for this Query or anything else?

Please let me know your valuable suggestions,

Thanks,
Satish

[Updated on: Wed, 05 August 2009 07:44]

Report message to a moderator

Re: FTS - Full Table Scan - [message #416971 is a reply to message #416965] Wed, 05 August 2009 08:29 Go to previous message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ You use more blocks but you need them so you will anyway use them but as you load them in few you lock less the SGA and so it is positive
2/ I don't understand what you mean

Regards
Michel
Previous Topic: Selection/Updation from a table based on condition
Next Topic: A high value of Rollback per Transaction (97,74%)
Goto Forum:
  


Current Time: Thu Dec 08 04:04:53 CST 2016

Total time taken to generate the page: 0.08729 seconds