How to improve performance "Oracle 8i on Linux"

[Sina]

Hello everyone.

Running Oracle 8i on Redhat Linux.

What are the things you can recommend to improve performance?
The database is query only and it gets updated only once a week. So the main concern is to improve the performace for the response time when queries are performed.

Thank you all

 

[jcote]

I don't know of specific things for Oracle on Linux to improve performance. I would start with all the basics of good Oracle tuning (which is valuable for all platforms), like a big enough buffer pool, proper SGA, good re-utilsation of SQL statement, etc. The first question I would have for you is what type of application will you run on this database, transactionnal or data warehouse?

 

[Sina]

Hi jcote;

Thanks for your prompt reply.
I'm totally new with oracle but I do have expeience with Linux.
Now the database is used for query (data warehouse) application. I will be writing application using php to query the database and return data.

There will not be updates to this database from users.

But I like to improve the database query time access. To basically optimize the server ( Oracle database).

But since I'm new to oracle, If you could kindly give me some details.

Thanks much

 

[jcote]

First of all, in a data warehouse environment, you will want to use a big db_block_size (at least 8k). I don't know what is the maximum you can set, but I would go even higher that 8k. The idea is this: since you use the DB for data warehouse, you will get lots of information each time you do a query on it, contrary to a OLTP application where you get little data at a time. So, by having big blocks, each IO will retrieve more data per IO. You specify the block size at database creation time. You cannot change it after (you have to re-create the database).

Allocate most of the memory available to the DB_BLOCK_BUFFER parameter. On the opposite side, you can have a small SHARED_POOL_SIZE, since your queries should be different from one another (I assume). The Library cache is where the parsed statement resides. Again, in an OLTP environment, the same query gets executed lots of time, and it is usefull to have a parsed version of those, and save parse time, But in a DW environment, this has no meaning.

If you have few users connected to the instance, I would also give a boost to the sort_area_size parameter. This is the amount of memory allocated to each process connected to Oracle for sorting purposes. The more memory allowed, less time will sort be done on disk.

It is critical that create your users with a temporary tablespace (not SYSTEM, which is the default), on another disk, hopefully!)

There is probably lots of other things, but these are guidelines.
Feel free to ask more specifics questions.

Jean

 

[Sina]

Hello Jean.
I have couple of newbie questions, if you could kindly help.
here they are:
1. what is the command to get a list of the table spaces in oracle ?
2. How do I get a list of all the primary keys on a table ?
3. How do I get a list of all the foriegn keys on a table ?
4. How do I get a list of all the indexes on a table ?
5. How do I get a description on a table in oracle (like field defenitions).
6. How do I find out the list of users with access to a specific table / table space?
7. How do I get a list of all the valid table fileds TYPES in oracle?

thank you very much.