sojankurian
Programmer
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
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