Home » SQL & PL/SQL » SQL & PL/SQL » Oracle Import/Export
Oracle Import/Export [message #298901] Fri, 08 February 2008 04:06 Go to next message
hedonist123
Messages: 119
Registered: August 2007
Senior Member
Hi,

I wish to drop and recreate a table with appropriate storage parameters.

I am facing a situation in which the table has been created with an initial extent of 64 kb but now the table as grown to 432 MB.

As a result I am facing performance issues.

Is this the right approach to be followed?

Is recreating the table required? Or is there any other way out?

Thanks,

Sharath

Re: Oracle Import/Export [message #298902 is a reply to message #298901] Fri, 08 February 2008 04:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
As a result I am facing performance issues.

Are you sure? What make you say that? Do you use DMT or LMT?

ALTER TABLE MOVE

Regards
Michel
Re: Oracle Import/Export [message #298906 is a reply to message #298902] Fri, 08 February 2008 04:17 Go to previous messageGo to next message
hedonist123
Messages: 119
Registered: August 2007
Senior Member
Hi Michel,

Well, the table has around 2 million rows. My application takes data from this table and 4 other tables via a common column.

However, it does a full table access on this table as the common column does not have an index. The bad part is that we cannot create an index on this table as all the values for this columns for all the 2 million rows is same.

Could you please give me some advice on what to do to overcome this issue?
Re: Oracle Import/Export [message #298908 is a reply to message #298906] Fri, 08 February 2008 04:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You didn't answer my questions. Did you measure something? Did you just "think" it is as it?

Could you please give me some advice on what to do to overcome this issue?
I think I answered this.

Regards
Michel
Re: Oracle Import/Export [message #298909 is a reply to message #298908] Fri, 08 February 2008 04:26 Go to previous messageGo to next message
hedonist123
Messages: 119
Registered: August 2007
Senior Member
Sorry Michel.

Performance Issue is that the query takes about 20 seconds to execute.

From the UI however, the page takes upto 2-3 minutes to load.

I am not exactly sure whether it is DMT or LMT.

How do I check it?

I am new to Database Development.

What did you mean by 'ALTER TABLE MOVE'?
Re: Oracle Import/Export [message #298911 is a reply to message #298909] Fri, 08 February 2008 04:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Performance Issue is that the query takes about 20 seconds to execute.
From the UI however, the page takes upto 2-3 minutes to load.

So the problem is UI.

Quote:
am not exactly sure whether it is DMT or LMT.
How do I check it?

SQL> select extent_management from dba_tablespaces where tablespace_name='TS_D01';
EXTENT_MAN
----------
LOCAL

1 row selected.


ALTER TABLE MOVE is a statement. See SQL Reference.

Regards
Michel
Re: Oracle Import/Export [message #298917 is a reply to message #298911] Fri, 08 February 2008 04:39 Go to previous messageGo to next message
hedonist123
Messages: 119
Registered: August 2007
Senior Member
When I ran the query it returned local.

Is that alright? Or should I make some modifications?

I reorganised the table as per your command 'alter table tablename move'
Re: Oracle Import/Export [message #298921 is a reply to message #298917] Fri, 08 February 2008 04:51 Go to previous messageGo to next message
hedonist123
Messages: 119
Registered: August 2007
Senior Member
Michel,

The query is taking 40 seconds.

What I would like to know is that is would recreating the table with appropriate storage parameters improve performance?
Re: Oracle Import/Export [message #298927 is a reply to message #298921] Fri, 08 February 2008 05:02 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You say you have a table with 2 million rows. You cannot create an index to speed up your query, because " as all the values for this columns for all the 2 million rows is same."

This means that your query will return all 2 million rows. No way that this is done within 40 seconds...
Re: Oracle Import/Export [message #298943 is a reply to message #298917] Fri, 08 February 2008 06:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You are in LMT and number of extents is irrelevant in your query performances.

Regards
Michel
Re: Oracle Import/Export [message #298947 is a reply to message #298927] Fri, 08 February 2008 06:03 Go to previous messageGo to next message
hedonist123
Messages: 119
Registered: August 2007
Senior Member
Hi Frank,

I guess the time that the query takes is much more.

But, is there some way u could suggest to opimtise the query?

Thanks

Sharath

Re: Oracle Import/Export [message #298949 is a reply to message #298943] Fri, 08 February 2008 06:05 Go to previous messageGo to next message
hedonist123
Messages: 119
Registered: August 2007
Senior Member
Oh, that is not good news.

This means that even if I recreate the table with new storage parameters, it will not affect my performance.

Thanks for the tip, Michel.

However, is there anything else I could do improve performance?
Re: Oracle Import/Export [message #298951 is a reply to message #298949] Fri, 08 February 2008 06:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The only way to improve FTS performances (with the same hardware) is to reduce the number of blocks.
Having bigger extents may help if your current extent size is less than "db_file_multiblock_read_count * tablespace block size".

Regards
Michel
Re: Oracle Import/Export [message #298953 is a reply to message #298951] Fri, 08 February 2008 06:12 Go to previous messageGo to next message
hedonist123
Messages: 119
Registered: August 2007
Senior Member
Michel,

Could you please tell me how to get these parameters from my db?

Which tables should I query?

Sharath
Re: Oracle Import/Export [message #298974 is a reply to message #298953] Fri, 08 February 2008 06:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"show parameter db_file_multiblock_read_count"
Tablespace block size is in dba_tablespaces.

Regards
Michel
Re: Oracle Import/Export [message #298978 is a reply to message #298974] Fri, 08 February 2008 07:07 Go to previous messageGo to next message
hedonist123
Messages: 119
Registered: August 2007
Senior Member
Hi Michel,

My current extent is 65536.

My db_file_multiblock_read_count is 16 while my block size is 8192.

which comes to 131072.

So the extent is lesser.

I guess then recreating the table is an option that will guarantee some improvement in performance?

Sharath
Re: Oracle Import/Export [message #298987 is a reply to message #298978] Fri, 08 February 2008 07:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No, it will not guarantee anything, it may statistically improve the performances but just a couple of seconds.

Regards
Michel
Re: Oracle Import/Export [message #298990 is a reply to message #298987] Fri, 08 February 2008 07:36 Go to previous message
hedonist123
Messages: 119
Registered: August 2007
Senior Member
Oh! alright!

Thanks a lot Michel.

Very Big Help!

Sharath
Previous Topic: Sequence number
Next Topic: eliminate duplicate rows (merged same question by two different users)
Goto Forum:
  


Current Time: Mon Dec 05 14:52:01 CST 2016

Total time taken to generate the page: 0.10966 seconds