Oracle 8.1.7 Problems on HP-UX 11.00

[Ghulam Shabir Mallah_1]

Please open attached system configuration information.

I would like to summrise problem here.

1. Update records in Database taking long time. Very Very bad performance.

2. sqlldr direct mode is not working but conventional working fine.

3. Backup taking long time about more then 12 hours and after that system hang/crash. Till now i am able to take backup and system is going live next week.

4. Every day i am recieving very huge data files(text format with | delimeter) from AS/400 server. There are about 14 text files and each file contains about 10 millions records. I am using conventainal path to load data on my oracle database and taking long time to load. data loading time about 8 hours maximum. So That's reason i am looking for direct path. Using direct path i got error message that OS and Hardware is not identical. I could not undersatnd why it is? even i am executing sqlldr from same machine where i have OS, Database on RAID 5. Also data text files are on the same machine. Please Please help help me.
For complete system information please find attached file. Also remonded any paches for oracle and OS GHP-UX 11.

Thanks

Ghulam
DBA

[Tim Adamson_1]

Hi,

There are a few things you can do here:

1. If you have a crash dump, send it to HP to have it analysed. The engineer may provide some good clues to the cause of the hang. If you don't have a crash dump, set your system up to take one. Again, you can log a call to ask how to do this.

2. How much swap do you have? The system has 4Gb of RAM, so I woul assume you have at least 4Gb of swap. Use swapinfo -tam to determine amount of swap.

3. I see a couple of kernel parameters that look bad.

Set timeslice to 10 - give the CPU a chance to do some work!

bufpages has been set to (NPROC * 3) which equates to a buffer cache of about 100Mb. This is way to small. I would set bufpages back to default (NBUF * 2) which will work out at 0 thus implying you are using dynamic buffer cache. Then set dbc_max_pct to 10.

I would think the buffer cache is your main problem at this stage. Insufficient swap would also cause problems.

The kernel changes will require a reboot, but the investment would be worthwhile. Post again if you have further issues after checking the things I have suggested.

Hope it helps.


Tim

[Yogeeraj_1]

hi,
for 1,2,3

Are you running Oracle 8.1.7.4? i.e. have you applied the latest available patchset?

For the Update of records, it is preferable that you finetune your SQL statement using SQLTRACE, TKPROF.

At the system level, you may wish to generate a statspack report.

What backup tool are you using? database backup? RMAN?

Please post more information.

regards
Yogeeraj

[Indira Aramandla]

Hi Ghulam,

1. Update records in Database taking long time. Very Very bad performance.

Have your tables got indexes. Are the tables analyzed. When you perform the update operation make sure you have large rollback segments assigned to the session. And also if you update through a script make sure to commit after every couple of thousand records.

2. sqlldr direct mode is not working but conventional working fine.

To be able to use SQL*Loader in direct mode you have to have the views required for this created as sys. there is a script in ORACLE_HOME/rdbms/admin directory called catldr.sql . This has to be run as SYS or internal. This will creste the necesary views for loading in direct path mode.

3. Backup is taking long time. What tool are you using to backup.


4. Direct path mode loading will be faster.

[Volker Borowski]

Hi,

check your database parameters. They are too small for a box like this

Oralce SGA Size
Total System Global Area 264714728 bytes
Fixed Size 104936 bytes
Variable Size 141557760 bytes
Database Buffers 122880000 bytes
Redo Buffers 172032 bytes

You just gave Oracle 250MB of your 4GB of Memory. That is Waaay to low.

Without knowing, what your application needs from memory and cpu, it might be difficult to give you a proper advice, but you must have information of this in the application manuals.

As a starter, I'd try to evenly divide the memory between application and database, which will suggest that you would configure ORACLE to use round about 1,5GB-2GB of mem as a start.
You need to increase the following Prameters in init.ora configuration file:

db_block_buffers = Database Buffers is given in ORACLE-Blocks and dependes therefore on db_block_size (which may not be changed!!!!)
--> since your current value for db_block_buffers is equal to 120M, I'd multiply the current value by 6 as a starter.
=> This will give you the biggest perf-kick.

shared_pool_size is in bytes and should be increased to 400M as a start.

So those two would make Oracles global memory to increase to about 1,1GB of Memory (from 250M). Since you need some additional Memory for local process purposes, I'd stay with this first and monitor.

Now before you can startup this configuration, you might need to adjust your kernel Parameters, otherwise you might not be able to address the memory. Currently you haven shmmax and maxdsiz as 1GB. Increase them to at least 2GB.

Only a jump-start,
later fine-tuning will be needed !
Volker

[Ghulam Shabir Mallah_1]

I have not change kernal parameters yet, coz there are some developer are doing some urgent work and we are very close to meet date to close this project. But I'll do and will let u know.

1. SWAPINFO (find information in attached file)
2. Oracle 8.1.7.0.0 for HP-UX.
3. Backup methode are using Database backup.
4. I am not using RMAN. Some body told me that I need seprate server for RMAN, IS IT True?
5. I Have about more then 80 Millions records in my database.
6. I increased SGA memory have not seen today any crashing.
7. But Archiver was failed many times.
8. Have not installed any patchs yet. If any body have bundle of patchs please Can I have it.

Please find attached some information about my production's server. Using top command of unix i got image of my server and that time i was loading data using SQL Loader conventail Path. As I already said that SQL Loader direct Path is not working on my server. I cheked CATSDL.SQL which is already installed. Bellow is error message.
"Error checking path homogeneity
ORA-02352: Direct path connection must be homogeneous" As I told you that I am wondering that why I am getting this error, even I am execute sql loader from same server where i have RDBMS on RAID 5 and OS on the same machine.

I am using Oracle 8.1.7.0.0 64 Bit and HP-UNIX 11.00 64 bit.

Also Attached html file of my Rollback segment informations.


Thanks a lot all of you for prompt reply.

Thanks

Ghulam