Dump And Load Performance.

[Jack@dba]

Hi All,

Last week we done dump and load activity for fue db's.After completing activity we received batch jobs run time report from batch team before and after analysis.After seeing the report we found fue jobs like restoration and application programs are running more time than the previous run time.

1) Steps we followed For dump and load
for single table size is 3 gb for one database.
Dbanalyssis report
Dump .df
Dump .bd
Rename existing table
Load new modified .df
Load with indexes
Dbanalysis report

2) Steps we followed For other databases
Dbanalyssis report
Dump .df
Dump .bd
renane existing table with old
Load .df
Load .bd with indexes
Indexrebuild
Remove renamed table.

In first approach for single table we done dump and load using table move approach on same database.

In Second approach for fue tables in each datababases we done dump and load on same database.

So what problem we are facing is before and after dump and load activity we compared records before and after the activity all records counts r matching and scattering factor also reduced from 2.5 to 1 for all the databases.

But for two databases for two tables there is no change in factor values is for 1 table before and after is 1.8.And second table also no change in value 2.3.

So I not able to Understood if u do dump and load at least 5% increase in performace.But in our case it delaying another 5 mins when comparing with as per batch reports.

As per client request we done dump and load on same database because of this idx analysis report we are seeing no change in values but other databases we done same steps only two tables having issue.

Why index analysis report there is no change in values.

Our version 11.2 Enterprise wdition and Aix 7.1

Thanks....

 

[Rob Fitzpatrick]

1) Steps we followed For dump and load
for single table size is 3 gb for one database.
Dbanalyssis report
Dump .df
Dump .bd
Rename existing table
Load new modified .df
Load with indexes
Dbanalysis report

2) Steps we followed For other databases
Dbanalyssis report
Dump .df
Dump .bd
renane existing table with old
Load .df
Load .bd with indexes
Indexrebuild
Remove renamed table.

I don't understand what you mean. The approaches you have described above look basically identical except number 2 includes "Indexrebuild". I can only guess at what you actually did. Does "Load .db with indexes" mean "load with indexes deactivated"? And in approach 1 "Load with indexes" means "binary load with 'build indexes' option"?

In first approach for single table we done dump and load using table move approach on same database.

In Second approach for fue tables in each datababases we done dump and load on same database.

These sound like the same approach. Or does "table move approach" mean "proutil -C tablemove"?

It would be a lot easier to understand if you just posted your actual procedure, including the commands, instead of trying to describe it without actually revealing its contents.

Also, your procedures are missing backup commands, unless you left those out too.

 

[Jack@dba]

Hi Rob,

Thanks for quick response.

Yes both are same load .bd with indexes and also we used proutil dbname -C table move.

We taken backup before dumping the tables.so I think we did dump and load perfectly and also tabanalys scattering factor values reached ours expectations but we r not able to understand idx analysis report for fue tables values not changed.

Any suggestions or advice from your side ...


Thanksss

 

[cj_brandt]

is there a large amount of records being created / updated in the tests where the performance is slower ? You might have built the indexes to 100% utilized so now there are a large number of split blocks when an index is updated.

 

[TomBascom]

While dumping and loading can, and often does, improve performance it is not automatic. There are many reasons why dumping and loading might not improve performance or may even make it worse. Some of the most common reasons for a poor result that I can think of prior to my 2nd cup of coffee:

1) The index used to dump may be a poor match for the programs using the data. This will result in "logical scatter" and reduced performance.

2) As CJ pointed out, the indexes might be built to 100% utilization thus driving a flurry of block splits post load. (This will usually settle down after a few days.)

3) Misconfiguration of the storage areas being loaded... poor selection of rows ber block, blocks per cluster, create limit and toss limit are among the possible problems.

4) Poor configuration of the underlying storage. Particularly inconsistent use of RAID levels when doing dump & load piecemeal (as this appears to be) -- perhaps the original storage area was on a RAID10 filesystem and the new storage was allocated on RAID 5 or something of that ilk.

5) Other db configuration mistakes -- like truncating the bi file and setting the bi cluster size too small (or to the default) and all of the usual general tuning problems.