Hi Guys, We have a 40GB Database (

[sojankurian]

Hi Guys,
We have a 40GB Database (Dynamic Server(Version 7.31.FD6)) running on a unix box(HP-UX n4000 B.11.11 U 9000/800). The database is configured as OLTP
And is configured to use raw device.

While we run few batch reports we facing some performance problem.
These reports are run concurrently using different telnet sessions. Tables used for this reports are pretty big(almost 4 Million Records) and its properly indexed. As its a Account related tables most of the time we have to scan the tables completely, I suspect this cause the performance problem. Initially our report used to read directly from tables, but later we insert to temp table(these temp tables also indexed and created in “With No Log”). Here I just want to know how to improve performance without doing a data purging/housekeeping. Does performance of temp table can be improved by allocating more space to tempdb space? Or is there any better way to get out of this?

Here are some of the important server parameter set.

LOGFILES 48
LOGSIZE 10000
DEADLOCK_TIMEOUT 60
LOCKS 2000000
BUFFERS 300000
PHYSBUFF 20000
LOGBUFF 10000
LOGSMAX 61
CLEANERS 16
SHMVIRTSIZE 1000000
SHMADD 32768
SHMTOTAL 0
CKPTINTVL 300
LRUS 24
LRU_MAX_DIRTY 30
LRU_MIN_DIRTY 10
MAX_PDQPRIORITY 0
DS_MAX_QUERIES 10
DS_TOTAL_MEMORY 1280
DS_MAX_SCANS 1048576


Regards
Sojan

 

[hiten1972]

Hi sojan

this will help you !!


Update statistics has three major functions:
a) Compile Stored Procedures
b) Dropping / Creating Data distributions
c) Collecting Statistics for the Optimizer

The improvements in the new release concentrate on b) and c). There are no significant changes to the usage of UPDATE STATISTICS as the source code has been re-written and is transparent to the user. But, now in the new versions the DBA can follow the progress of Update Statistics and also tweak the memory and PDQPRIORITY to make it go faster.

Update Statistics in Previous Versions
a) This used to run in a SINGLE-THREADED fashion so if a DBA wanted some kind of parallelism, he/she would run multiple UPDATE STATISTICS on different columns simultaneously.
b) Only 4MB was allocated for SORTING SPACE which forced the engine to do multiple scans of the table if it was a pretty big table.
c) DBUPSPACE environment variable was used to control the UPDATE STATISTICS disk space.
d) No method or utility to monitor the progress of Update Statistics.
e) MGM memory not being used at all for UPDATE STATISTICS
f) PSORT_NPROCS was used to start multiple SORTING THREADS and DBSPACETEMP was used for the TEMP DBSPACES.

Update Statistics in the New Versions
a) This can run in parallel mode and uses the PDQPRIORITY environment variable for the purpose. If a table is fragmented the PDQPRIORITY is used to calculate how many of the fragments can be scanned in parallel. For example, if PDQPRIORITY is set to zero it will scan the fragments SERIALLY (old behavior), if it was set to 4, it will scan 4*10=40% of the fragments in parallel, if it is set to 10 it will scan 10*10 = 100% of the fragments in parallel. So, essentially, this means anything over 10 means ALL the fragments in the table will be scanned in parallel.
b) Default sorting memory has been raised to 15MB.
c) DBUPSPACE environment variable can be used to control the default memory for update statistics. For example, if a DBA wants to allocate 35MB of memory for UPDATE STATISTICS, he/she can set the DBUPSPACE environment variable to 0:35. The format of DBUPSPACE is now {max disk space}:{default memory}.
Note: The maximum amount of memory a user can request without setting the PDQPRIORITY is 50MB.
d) If a user wants more than 50MB of memory, he/she can use the PDQPRIORITY environment variable to use the MGM memory as described in a).
Note of caution: If an Update statistics is run for the database after setting PDQPRIORITY the Stored Procedures will store the PDQPRIORITY in their query plan and would run as Multi-threaded procedures. If this behavior is not needed, please run the Update statistics with “for table” clause.
e) PSORT_NPROCS and DBSPACETEMP would behave in the same way as in the previous versions.
f) If Update statistics is run with ‘SET EXPLAIN ON’ it would write the progress in sqexplain.out. A sample output would be as follows:

UPDATE STATISTICS:
==================

Table: informix.t1
Mode: HIGH
Number of Bins: 267 Bin size 2433
Sort data 69.7 MB Sort memory granted 50.0 MB
Estimated number of table scans 2
PASS #1 c3,c2
PASS #2 c1,c4
Light scans enabled
Completed pass 1 in 0 minutes 6 seconds
Completed pass 2 in 0 minutes 4 seconds

OR

UPDATE STATISTICS:
==================

Table: informix.t1
Mode: HIGH
Number of Bins: 267 Bin size 2433
Sort data 69.7 MB PDQ memory granted 73.2 MB
Estimated number of table scans 1
PASS #1 c1,c2,c3,c4
Index scans disabled
Light scans enabled
Completed pass 1 in 0 minutes 9 seconds

Tips of using the new version of Update Statistics
a) Use the SET EXPLAIN to find the SORT DATA and SORT MEMORY GRANTED. If > 50MB memory required, use PDQPRIORITY (this will use DS_TOTAL_MEMORY) and the value you set would define how much of the DS_TOTAL_MEMORY it will use, for example if DS_TOTAL_MEMORY=80MB, setting PDQPRIORITY=50 would allocate 40MB of memory.

b) Use DBUPSPACE if
i) Sort Memory required <= 50MB
ii) Disable Index Scans
iii) More information to be printed in the Explain plan.

The format of DBUPSPACE={Max.disk space}:{Default Memory}:{Options}
In Options:
2 = Disable Index Scans & Enable more information to be printed
1 = Enable more information to be printed.
Recommended: 2

c) Usage of DBUPSPACE precedes usage of PDQPRIORITY so, if you want to use DS_TOTAL_MEMORY but still want to disable index scans you would require to do the following:
i) DBSPACE=0:0:2
ii) PDQPRIORITY=100





regards
Hiten