General
cancel
Showing results for 
Search instead for 
Did you mean: 

How to judge a table need export/import?

ericfjchen
Regular Advisor

How to judge a table need export/import?

If a SQL command need full table scan, the execution time is related the
table's high water mark. If this table is executed by DML frequently, we must
run "alter table xxx move" to reorg it. The SQL execution time will become
fast.
Our question is....Can we use any Oracle tool or SQL to judge a table need move
to lower the table's high water mark?
11 REPLIES
Indira Aramandla
Honored Contributor

Re: How to judge a table need export/import?

Hi Eric,

You can reorg the table data by export and import when the table data is fragmented.

If the table data is ofen deleted for a regular cleanup or some archival reason, then the table needs to be reorged to coalesce the free space. Or it the table has grown to many extents then you will need to erport the data, resize the table with proper initial and next extents and then import the data back.
For table tables with frequent deletes you would know from the application or your business rules. Fot table with many extents you can query the dba_segments and dba_extents views.

Indira A
Never give up, Keep Trying
Indira Aramandla
Honored Contributor

Re: How to judge a table need export/import?

Eric,

Here is more derived from Metalink.
First, if you have a situation where there are multiple, concurrent processes doing DML against a segment, and it´s storage parameters are such that it´s allocating new extents rapidly, then it´s possible to see enqueue contention on the ST enqueue. But, two things to remember here: if you don´t see any enqueue contention then the number of extents and the rate at which they are growing is not a problem, and even if you do see enqueue contention, it could be on any of a number of enqueues. Look at X$KSQST to determine which enqueue is giving you problems.

If you fall into this category, you probably don´t need to rebuild the object. Just increase the NEXT extent size, and the system will start allocating the larger size extents, thereby reducing the frequency at which extents are allocated, thereby reducing the demand for the ST enqueue.

The other situation is where you have one or more segments with a huge number of extents, and even if it´s relatively static, it can cause performance problems when you query some of the data dictionary views, such as DBA_EXTENTS or DBA_FREE_SPACE, etc. In this case, the problem is the sheer number of extents allocated to the segment(s) in question, and if it´s really causing you a problem, you´ll need to rebuild the segments to solve the problem. But, before you do, consider, how often do you really run those types of queries? Is it worth your time to spend rebuilding the objects? Or can you live w/ poor performance on an occasional data dictionary query?

Finally, never forget, if you´re not currently suffering from a significant performance problem, then what problem are you trying to solve? Don´t worry about numbers of extents in a segment unless you have evidence that it´s causing a real performance proble
Never give up, Keep Trying
Yogeeraj_1
Honored Contributor

Re: How to judge a table need export/import?

hi,

It must be a strange application that you need to lower a HWM frequently -- perhaps a candidate for rolling partition windows? Sounds like you might be adding and purging lots of data -- perhaps a partition can be used more efficiently to avoid this?

You can use DBMS_SPACE package and basically the 2 procedures free blocks and unused space.

You will be able to get:
Free Blocks...... Number of blocks on the freelist
Total Blocks..... Total blocks allocated to the table
Total Bytes...... Total bytes allocated to the table
Unused Blocks.... Blocks that have never contained data
Unused Bytes..... The above in bytes

You can get most of the information supplied by this package by analyzing the table and using queries against user_tables and user_segments. The freelist analysis is more detailed using this package as you can look at each freelist independently.

hope this helps!

regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
ericfjchen
Regular Advisor

Re: How to judge a table need export/import?

I am a DBA. AP team challenge us which table we should reorg for them. Therefore from DBA's view point, can we find out which table should be reorg? ....Pls kindly give a idea?

Thanks

Eric
Jean-Luc Oudart
Honored Contributor

Re: How to judge a table need export/import?

Hi

Before thinking should I reorg a table you should have a performance baseline (per application / transaction)that is acceptable.
Agree on threshold and monitor the performance. You say the AP challenge you on the DBA work why not challenge them on the SQL transaction ! The maximum gain on a transaction will come from DB design, SQL coding 1st, then DB parameters.

See attachment. may be a bit old but may give you some relevant information.

Regards
Jean-Luc
fiat lux
ericfjchen
Regular Advisor

Re: How to judge a table need export/import?

Hi,

Thanks for all of your comments. I am very confused. Is it DBA's duty to find out which table is fregmented? Any comment will be highly appreciated.

Thanks

Eric
Jean-Luc Oudart
Honored Contributor

Re: How to judge a table need export/import?

Well,

in my view the DBA has to monitor the database for performance (among other jobs). The AP should give info on which database object would be fragmented in such way that the application would suffer (I mean you could live with lots of "fragmented" tables but they may not impact the overall performance). Therefore a good commmunication is important between you and them.

Regards
Jean-Luc
fiat lux
Mobeen_1
Esteemed Contributor

Re: How to judge a table need export/import?

Eric,
It depends on how you look at it. Generally i would think that the onus is on the DBA group to mark tables for reorg.

As many of our colleagues have pointed out rightly reasons for need to reorg.

There should be generally a SLA estabilished between the developers/application support team and the DBA for performance of a database. When ever the performance falls below the SLA, then it should be the DBAs who would need to look at the performance and suggest and carry out measures to improve the performance.

regards
Mobeen
Jean-Luc Oudart
Honored Contributor

Re: How to judge a table need export/import?

Eric,

regarding the topic I would suggest you read Metalink Note:186826.1 :
"Various Aspects of Fragmentation"

Regards
Jean-Luc
fiat lux
Fred Ruffet
Honored Contributor

Re: How to judge a table need export/import?

Eric,

IMHO, monitoring table fragmentation is part of DBA's job, as far as disks fragmentation is part of Sysadmin's. Now both have some ways to deal with this problem.

Regarding DBA's job, it is now much better to go into 'alter table xxx move' process than export/import. This will help removing chained rows and too big high water marks (fragmentation itself could be avoided using, for example, local management and uniform extent size).

If your problem is high water mark, you should find a way to have a ratio between high water mark and real data size for which your performance seems to be no more acceptable. Then you can produce a script telling which tables reach this ratio and so need rebuild (move). Running it periodically will help keep better perfs.

Regards,

Fred


--

"Reality is just a point of view." (P. K. D.)
Fred Ruffet
Honored Contributor

Re: How to judge a table need export/import?

This doc is also not too bad ;)

Regards,

Fred
--

"Reality is just a point of view." (P. K. D.)