OraFAQ Forum: Performance Tuning » FTS - Full Table Scan

Home » RDBMS Server » Performance Tuning » FTS - Full Table Scan - (merged 3)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

FTS - Full Table Scan - (merged 3) [message #416946]

Wed, 05 August 2009 05:46

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 ?

Report message to a moderator

Re: FTS - Full Table Scan [message #416951 is a reply to message #416946]

Wed, 05 August 2009 06:28

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: FTS - Full Table Scan - [message #416955 is a reply to message #416946]

Wed, 05 August 2009 06:44

ThomasG
Messages: 3212
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.

Report message to a moderator

Re: FTS - Full Table Scan [message #416956 is a reply to message #416951]

Wed, 05 August 2009 06:44

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

Report message to a moderator

Re: FTS - Full Table Scan - [message #416957 is a reply to message #416955]

Wed, 05 August 2009 06:49

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

Report message to a moderator

Re: FTS - Full Table Scan - [message #416960 is a reply to message #416957]

Wed, 05 August 2009 06:58

cookiemonster
Messages: 13958
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.

Report message to a moderator

Re: FTS - Full Table Scan - [message #416961 is a reply to message #416960]

Wed, 05 August 2009 07:07

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

Report message to a moderator

Re: FTS - Full Table Scan - [message #416964 is a reply to message #416961]

Wed, 05 August 2009 07:30

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: FTS - Full Table Scan - [message #416965 is a reply to message #416964]

Wed, 05 August 2009 07:40

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

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Nov 22 12:31:27 CST 2024