PRO*C Performance issues- Comments wanted!

Recently I had a little chat with a guy at Oracle about performance. I explained several methods I use in Oracle 6 and Oracle7 and he offered up some more suggestions. But I also asked several questions of which only about half where answered. So, I though if I posted a slightly modified and more orginized trascription of our talk, it would: 1) be informative for everyone to see these "TIPS" and 2) maybe someone out there can answer the questions that wern't responed to.

---


> From dnatelso_at_oracle.com

> How much experience have you gotten with Oracle and tuning ?


I'll take it that you don't mind talking shop with me. :) I have been
working with Oracle 6.0.27.9.3 (on ODT 1.1) for a couple years and
recently with 6.0.37 (which I don't have installed anymore)
and Oracle7 7.0.12 (on SCO Unix 3.2.4v2) for a couple months now.

Mainly all I concern myself with is PRO*C and performance associated
with developing a multi-user interactive adventure game (like a MUD).
My tables tend to be large in definition (columns) and contain realitively
small amounts of total actual data (about 10 MBs right now). Most all the
tables are key'ed by a CREATE UNIQUE INDEX with a single ID number.

Since I do a lot of queries, the most important thing is getting small amounts
of data to the application as quickly as possible with causing the least
amount of multi-user contention.



> With Pro*C it is important to set a few things when you precompile if you

> want max performance:

> 

> hold_cursor=yes

> 

> Otherwise you will parse each time you re-open your cursor(s).

> 

> mode=oracle

> 

> In ANSI standard SQL a select ename into ename from emp (one row expected),

> must send a second request to see if there is a second row (to print an error

> message, too many rows).  mode=oracle turns this off, plus allows you to

> (in a while loop) access a cursor many times without closing and reopening

> it (see the Pro*C manual if I am not clear here) after commits.


I develop on a 486-50 with 16MB ram (Which by the way in a UNIX environment
is faster then a 486-DX2-66). My production machine will be a 486-50
with 32MB ram and several (at least 3) small SCSI disk drives that I'll either
Stripe with UNIX software or manually divi up the tables using TABLESPACE.



> You can also stripe with Oracle using multiple tablespace files for ONE

> table, have initial extent = size of one raw partition, and minextents =

> number of raw partitions on the create table statement.  


* Havn't decided which to do- the strip software supports RAW datafiles. I
think that the striping software might be more effective though. Any opinion?



> block_buffers


Not being in a production environment, I just set these to a reasonable 400
or 500. Seems to be enough to cashe everything- I'll have to play more games
once I get many users on a regular basis.



> Only index pages and data pages read after an index read are cached with LRU.

> Full table scans go on the "aged" end of the LRU chain because we assume

> you will see it once.  If your data tables are small, try building a

> concatened index with all the columns needed by a select.

>

> Set your multiblock_read_count = 32 (32 blocks / I/O, default = 8).


* I NEVER do a full table scan. At most I might read 32 rows in a single
set. 80% of the time though, I'm getting just 1 row at a time. Also, since
most of the time I'm just getting one row (maybe a lot of columns [30-40]
from that row) wouldn't a small multiblock_read_count be alright?



> explain plan


I use this mainly to validate that what I'm doing DOES what I expect it to
do. Usually I'm right (rule based isn't that hard to understand). One
thing I did get from a PLAN was that adding 2 columns to an index allowed
my application to get those columns from the index and not have to read
the database. It seemed to speed things up a bit and the columns where just
a numbers, so no big deal. Oh, Version 7 plans are an improvement over v6.



> sort_area_size


I toyed with it a bit, but rarly do I ever use a ORDER BY clause or DISTINCT
so it dosn't get used much anyway (at least I don't think so). 



> Sorts and merge joins use the sort area size in memory and when it needs

> more you allocate temporary extents in the user's temp. tablespace.

 


> temporary tablespace sizing


* Now that's a good question. I don't think I do anythign with this. Is there
something I might want to try? What uses the temorary tablespace? Only sorts
and Joins?



> The storage parameters should be X*sort_area_size +2 or 4 bytes overhead, 

> because this is the chunk size Oracle will be moving around and if this is

> not a multiple of sort_area_size, you get extra space allocated in the temp

> tablespace.

 


> rollback


I find that many small (40K) segments work much better in a multi-user
environment. So I've been using about 4 or 5. The problem is, the occational
qurry that I need to update a large amount of data requires me to shutdown
and bring the database up with only the SYSTEM rollback segement. But usually
that's only for mantainance anyways.

* I read somewhere about Oracle7 handling dynamic rollback-sizes, but the
option of OPTIMAL being assiciated with a table kinda threw me- so I'm not
doing anything in that arena (yet). Any helpful suggestions/hints?



> Oracle7 has a range that growth will shrink back to, plus any SQL statement

> can pick its rollback segment manually.

> 

> You should NEVER use the system rollback segment, put them in their own

> tablespace.  Fragmenting the system tablespace causes you a performance

> degradation.


Humm, well I guess I'm guilty of doing this enough... I didn't realise until
recently that I could specify a ROLLBACK segment that the SQL statment must
use. That will cirtainly help.

* Does an EXP/IMP of the entire database clean that up or does IMP only clean
user tables? Actually, home much DOES EXP/IMP help fragmentation? I've seen
that most tables after brought in via IMP seem to be only one extention- there
must be some intellegence there. Would it be wize to EXP/IMP an entire
database on a regular basis (keeping in mind that my database is "small")?



> etc.

- LOG FILES
I set logfiles so that they don't do any checkpoints until they are full and
I create several at about 2-3MB each.

- CONTEXT AREA (v6)
I found that context_area and context_incr had a dramatic effect on performance
in my PRO*C applications. Since I do MANY queries, some with many columns some
with only a few, I found that setting both values to 8K helped alot. I don't
think this is something in v7 since queries are kept in the SGA- right?



> This is the user memory allocated in a shadow process, thus the more you

> ask for the more used, but better performance!

> 

> Remember (above) full table scan blocks are put at the end of the LRU chain.

> Oracle7.1 (going beta soon) allows multiple processors to do a full table

> scan (parallel query) and uses them in the sort/merge routines also.


- RAW FILES
I've actually benched the database with and without using RAW datafiles,
and in 6.0.27 (ODT 1.1) it seemed to be actually SLOWER to use RAW devices.
But I still opt for the higher reliability (avoiding the UNIX buffers).



> Are you running 6.0.27 (this is 3-4 years old and buggy compared to 6.0.37

> or Oracle7.0.13).


Actually, I have found 6.0.27 to be quite robust and had little trouble with
it. That is why I left it intalled on one of my development machines (so I
can make sure my application runs on Version 6 and 7). I do run Oracle 7.0.12
on the machine I plan to make production.

* What is 7.0.13? Is it available to SCO UNIX users/developers?

- Asyncronous IO (v7)
I've setup my UNIX 3.2.4v2 to do this, but being that I'm currently using
only one disk drive, there is no notiticable difference (obviously). I think
that having to set an ENVIRONMENT varrible instead of an INITORAC.ORA parameter
is really STUPID (re USE_AIO=TRUE in .profile).

* Is there any way to test if it is working or being used/installed at all?



> The quickest way to increase performance is to have 3 disks instead of one

> and put REDO, rollback, data and index in different tablespaces.  Then

> try and balance the load on the disks using iostat so each disk is doing

> the same number of I/Os per second (balance your whole system to max. your

> hardware).


- Post wait sleep driver (v7)
Contrary to the documentation, in my single user tests at least, I found
that the did not speed things up at all and (I think) probably slowed them
down a bit. It's sometimes hard to tell with only millisecond access times.



> This is for AIX (instead of using semaphores, IBM has post-wait kernel stuff).


Are you sure that SCO Unix does need/use this? I thought the docs said that
SCO UNIX could benifit from the post wait sleep driver (being faster/more active
then semephores). "ipcs" _definatly_ shows semaphores active when this is NOT
set and has no active semaphores when it IS active.

* But, like I said, even though I use it, it seems to be slower not faster to
a very small degree- will that tapper off or have reverse effects with many
concurrent users?

- Sequences
I make use of sequences to create new ID numbers when rows are added
to the database. Some are cashed, some arn't. Oh, and I generate NEXTVAL
by qurrying for in a normal table where I know a row key value. This
is more portable then using the DUAL table, but is the DUAL table faster?
Even if I don't get columns from the MY table and it's index is BTREE
and has a unique ID number?

EDITOR NOTE: Since this conversation, I did change my sequence retrieval
             routine to use the DUAL table and it _was_ noticablly faster.

- Array
I use array selects in a couple places in the application. It DOES save a
lot of time in execution of several queries that retrieve 32 rows each
(small number of columns). Definatly use these when you can spare the array
space (memory) in your program.

Another thing that is very helpful in some situations is to select ROWIDs
from a table and store them in a local array. Then using:
	number_of_rows = (short)(sqlca.sqlerrd[2]);
(before the commit) you can use a for() loop or such to do some special
processing on all the rows retrieved. This is extreamly helpful when dealing
with non-unique keys.

- Redunancy
In one case, I keep non-normalized data (duplicated fields) in a seperate
table so that a query that is performed often dosn't have to be a join (which
would have to be a union of 2 other tables anyway). This seems to work well-
but you have to be very careful to build in all the integrety functions into
the PRO*C application to keep the columns in sink.



> What bottlenecks are you running into ?


Well as I stated in a previous post- I find that the SQL optimizer slows my
performace down significantly (well considering most of my queries are under
.5 second qurry time- it doubles that). So, I've set the initorac.ora
paramater: OPTIMIZER_MODE=RULE. I don't know if I'm doing someting wrong but
if this is true, I'd hate to see RULE based go away. But doesn't Oracle plan
to drop RULE based method for the future?

I also used Ingres on this application a LONG time ago, and its response time
to a small query was well over a second (or two) every time. That was
unaccetable so I choose Oracle.



> The shared SQL cache in Oracle7 should reduce optimiser parsing, plus setting

> proc to hold_cursors.  You could also try using stored procedures from your

> C program (pass an array of results back).


* What/where would be good places/things to make stored procedures out of?
Usually, all I do is load data to a global buffer, sometimes do some field
updates, and less often do some inserts. But most any time there is any logic
associated with such, I need rather detailed access to the data via 'C'. I'm
a bit rusty on stored procedures- maybe you could give an example or two where
I might use them to increase performance.

- HASHED TABLE
I've been scratching my brain as to where I might use a HASHED table. Most
all of my tables are keyed with a single unique ID number, so on the serface
it sounds like the ideal place to use them. But then I get into the fact that
rows are deleted (in moderation) and inserted (in moderation) on a regular
basis. Each time a new area/building is created in the adventure world, it
needs to add a bunch of rows. I'm not sure if I can acuratly guess the
table size max.

* Are HASH tables really that much faster? Can I increase the dataspace
allocated to a HASH table if I hit near the limit I set? Is it even worth
the bother? Would if be fair to bench the HASH by just droping the existing
BTREE unique index and creating a HASHed one?

- ODD TABLES
* A question, I have these two weird tables called INDEX$BADNESS_STATS or
somthing simular. Are they used in O7 or were they accidently imported
from a V6 I was using? If I recreate the indexes can I delete them (in v6
too)? They make me nervous and I keep thinking they are overflow pointers or
something (even though I don't think BTREE has overflow). Any ideas?

* Also, I have not been able to use the SQL*DBA in screen mode as it has some
really screwy key commands and dosn't seem to work well on SCOconsole. But
I might just have been to lazy to read the docs. Got any tips?



> David Natelson

> Sales Consultant

> Oracle Federal Group               Internet  :  dnatelso_at_oracle.com

---

Well that's it. I hope it's readable (and usful) to you people. I would like
to get answers to the questions I felt wern't answered or need more discusion.
I have marked these questions with a "*". Thanks for your time!

James Dicke             Minion Development Corp.
Internet: despair_at_csd4.csd.uwm.edu