Home » RDBMS Server » Performance Tuning » functional desing advise needed
 functional desing advise needed [message #126776] |
Wed, 06 July 2005 11:05  |
mjschwenger
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,
Thanks,
MJ
|
|
|
|
|
|
| Re: functional desing advise needed [message #127781 is a reply to message #126776] |
Wed, 13 July 2005 05:36  |
SoporteDBA
Messages: 7
Registered: July 2005
Location: Écija, Sevilla
|
Junior Member |
|
|
Hello,
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 |
mjschwenger
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...
MJ
|
|
|
|
| Re: functional desing advise needed [message #127902 is a reply to message #127864] |
Thu, 14 July 2005 01:07 |
SoporteDBA
Messages: 7
Registered: July 2005
Location: Écija, Sevilla
|
Junior Member |
|
|
Hello,
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:
ALTER TABLE ... DISABLE TABLE LOCK;
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.
|
|
|
|
|
|
Goto Forum:
Current Time: Thu May 01 12:22:57 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
