Home » RDBMS Server » Performance Tuning » functional desing advise needed
functional desing advise needed [message #126776] Wed, 06 July 2005 11:05 Go to next message
Messages: 137
Registered: July 2005
Location: US
Senior Member
I have a very busy and good normalized schema where a lot of selects and inserts/updates based on this selects are happening simultanteously - usually 64 to 72 processes in the same time. There's an application logic problem which we are trying to resolve, but it'll take time.
All processes insert data and select this data form the same tables, and sometimes update the same data based on the select. Every insert create new select process.
In this case I have a lot of concurency, sequential reads, latches, waits... Monitoring I see how 3-4 processes are going after the same data. The peformance issues start after the 3-4 mln of records are inserted into 7 base tables.
As I cannot change the application for the moment, but I need to improve the performance, I have 4 questions:
1. I was trying to separate reads and writes creating a view for the reads. Is this going to help me to reduce the concurency or supporting the view will be too big overhead?
2. I was thinking also that a partitioning will help a lot but the data is so different that I do not know how to organize it. What other approach I could take to help me solve my problem? Also, any helpful hint for partitioning will be greatly appreciated.
3. If I'm going to change the application, what approach I should take?
4. What if I use IOT - how big they could grow? Can I partition them as well? I'm familiar with the concept and use some in the past but cannot picture them in my situation now although almost all of my tables are with single point of access.
Is this going to be more efficiant for me?

Any idea is appreciated,
Re: functional desing advise needed [message #126792 is a reply to message #126776] Wed, 06 July 2005 12:13 Go to previous messageGo to next message
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
1. No. A view is just a stored query. There isn't going to be any overhead in using it, but it won't help since you will be hitting the underlying tables.

2. Partitioning may or may not help, it is useful for physically separating chunks of data. Breaking a larger table into smaller pieces to work with. What do you mean your data is different? Partitioning is done at the table level, so you can use a different partitioning method for each table.

3. I don't think we have enough information.

4. I'm pretty sure IOT can be partitioned. They may or may not help in your situation. They are useful for grouping data together on disk, sorted by key value. They also eliminate having separate storage (that must be read and maintained) for indexes vs tables. All stored as one object, in the form of an index.

Basically I think we need more info to help. Is it the retrieving data that is the problem or the inserting new data? Do you have proper statistics gathered? What else can you tell us about what you are doing and why you think there is a problem?

Also read the concepts guide sections on partitioning and on IOT and on VIEWS.
Re: functional desing advise needed [message #127781 is a reply to message #126776] Wed, 13 July 2005 05:36 Go to previous messageGo to next message
Messages: 7
Registered: July 2005
Location: Écija, Sevilla
Junior Member

You should first identify what is your botleneck, inserts, selects , updates.

If your problem is really on select statements, you should review is you have the apropiate indexes, you have to analyze tables/indexes frecuently (I´ll supouse you use cost-based optim), you also have to rebuild your indexes frecuently, partitioning will also help you (with the apropiate indexes) if your problem are select statements.

If the problem is the insert ratio, you should consider the parameters "freelists" and "freelist groups", with an apropiated use of then, you can have big performance gains. ("Freelist Management With Oracle 8i" by Stephan Haisley). In this case, i think partitioning will not help.

If your problem are updates, you should review parameters like "initrans" , "pctfree", "pctused" , and, in general all related with storage, also, rollback segments have to be well dimensioned.

Another thing you should consider is the db_block_size of your database, if you whant to reduce buffer contention , and your rows are not too big, perhaps a small block_size will improve performane.

Re: functional desing advise needed [message #127864 is a reply to message #127781] Wed, 13 July 2005 14:49 Go to previous messageGo to next message
Messages: 137
Registered: July 2005
Location: US
Senior Member
The problem is not separated with Insert, Upd, del,or selects.
It's when all of them comes together. It's the high concurency I do not how to optimize and I need help for.
Imagine that you have 72 users. Each of them inserts adderess, name, numerics,details. All base tables. In the moment the inserts are done, the same process starts to select from the same tables. Let say, I have inserted info for SW-product. Then I want to know what other products are like SW - has the same name, is reported by the same producer, belongs to the same category, sometime even has the same price(thanks God this is rearly used),etc. Then depends on what has been returned, I have to evaluate the information. For each match I have, I need to insert a records to fill the info in second set of tables.
Of course, I have already inserted my records there.So, I'll need to update if the info is already there and then to re-query again. It's bad practice to insert,select, update from the same table in the same time, but if this was a 3-4 processes, that should not be so bad, but they are 100s. The question is that in a while the data set is really big - imagine over 4mln products and they grow every day. The one which is killing me is that different processes are going after the same data - selecting in the same time, updating it in the same time, inserting and selecting the same again.
This is part of my statpack:
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 2,764,639.56 15,567.24
Logical reads: 48,779.16 274.67
Block changes: 15,885.70 89.45
Physical reads: 0.64 0.00
Physical writes: 268.28 1.51
User calls: 7,326.36 41.25
Parses: 99.74 0.56
Hard parses: 0.10 0.00
Sorts: 6.17 0.03
Logons: 0.08 0.00
Executes: 4,458.15 25.10
Transactions: 177.59

% Blocks changed per Read: 32.57 Recursive Call %: 10.01
Rollback per transaction %: 0.00 Rows per Sort: 13.34

Instance Efficiency Percentages (Target 100%)
Buffer Nowait %: 99.54 Redo NoWait %: 100.00
Buffer Hit %: 100.00 In-memory Sort %: 100.00
Library Hit %: 100.00 Soft Parse %: 99.90
Execute to Parse %: 97.76 Latch Hit %: 99.43
Parse CPU to Parse Elapsd %: 8.37 % Non-Parse CPU: 99.24

Shared Pool Statistics Begin End
------ ------
Memory Usage %: 23.94 26.50
% SQL with executions>1: 63.30 64.45
% Memory for SQL w/exec>1: 52.95 62.28

Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Call Time
-------------------------------------------- ------------ ----------- ---------
log file sync 694,482 18,258 25.34
latch: cache buffers chains 626,401 16,705 23.19
enq: TX - index contention 402,157 10,181 14.13
CPU time 7,578 10.52
buffer busy waits 819,243 7,037 9.77
Thanks a lot for any idea...
Re: functional desing advise needed [message #127902 is a reply to message #127864] Thu, 14 July 2005 01:07 Go to previous messageGo to next message
Messages: 7
Registered: July 2005
Location: Écija, Sevilla
Junior Member

As I see on statistics you have reported, one of the top wait events is "latch: cache buffers chains", there is a good document (that now I can´t find in the web, but I´ll put here as attachement, "Advanced OLTP tunning for the Oracle RDBMS" by Roby Sherman), where you can find how to identify the exact problem and some related solutions (take a look to the recomendations on parameter _db_block_hash_buckets, we have this parameter on 4096).
Take care also of parameters "freelist" and "freelist groups", they have a big impact on performance, however you have only one instance , parameter "freelist groups" will give you a big performance insert rate , however you´ll have to monitor your storage, there are important storage implications on changing this parameters. (See document "Freelist Management With Oracle ..." ).

Another thing you can do is to reduce the lock overhead related to the critical objects , by disabling locks:


As the good book : "Oracle8i Internal Services for Waits, Latches, Locks, and Memory" , by Steve Adams (http://www.ixora.com.au/) recomends.

Take care about this, because, if you do so, you couldn´t lock the table/s for operations like change a column, add a column...
and this command (alter table ... disable/enable table lock) has to be executed when the database isn´t in a heavy loaded state.

We have locks disabled for almost all the tables off our principal database, we reduced our contention on those tables.

Re: functional design advise needed [message #127943 is a reply to message #127902] Thu, 14 July 2005 04:52 Go to previous message
Messages: 27
Registered: July 2005
Location: Moscow, Russia
Junior Member


Another point is redo log tuning. It's a delicate process, but...

For example, check your database alert log for recognizing log switch interval. In case if log switching occurs frequently than 30 minutes - increase the size of redo log data files.

May be you have a problem with slow I/O performance on disk which contains redo log data files or you need to separate log files and undo, data and index data files on different disks.
Try to use iostat command for check I/O operations bottlenecks.

Or may be you need some changes in your application. To avoid frequently commit operations try to use batch transactions.

Previous Topic: Database performance issue
Next Topic: Influence of snapshot (STATSPACK) in object locks
Goto Forum:

Current Time: Sun Sep 20 23:04:14 CDT 2020