Real Time issues: Oracle Performance tuning (types / indexes / plsql / queries)

I am looking for a real time solution...

Below are my DB columns. I am using Oracle10g. Please help me in defining table types / indexes and tuned PLSQL / query (both) for the updates and insertion

Insert and Update queries are simple but here we need to take care of the performance because my system will execute such 200 times per second.

Let me know... should I use procedures or simple queries? It is requested to write tuned plsql and query with proper DB table types / indexes.

I would really like to see the performance of my system after continuous 200 updates per second

DB table (columns) (I can change the structure开发者_如何学Python if required so please let me know...)

Play ID  - ID 
Type - Song or Message
Count - Summation of total play
Retries - Summation of total play, if failed. 
Duration - Total Duration
Last Updated - Late Updated Date Time

Thanks in advance ... let me know in case of any confusion...

---

You've not really given a lot of detail about WHAT you are updating etc.

As a basis for you to write your update statements, don't use PL/SQL unless you cannot achieve what you want to do in SQL as the context switching alone will hurt your performance before you even get round to processing any records.

If you are able to create indexes specifically for the update then index the columns that will appear in your update statement's WHERE clause so the records can be found quickly before being updated.

As for inserting, look up the benefits of the /*+ append */ hint for inserting records to see if it will benefit your particular case.

Finally, the table structure you will use will depend on may factors that you haven't even begun to touch on with the details you've supplied, I suggest you either do some research on DB structure or ask your DBA's for a 101 class in it.

Best of luck...

EDIT:

In response to: Play ID - ID ( here id would be song name like abc.wav something..so may be VARCHAR2, yet not decided..whats your openion...is that fine if primary key is of type VARCHAR2....any suggesstions are most welcome...... ) Type - Song or Message ( varchar2) Count - Summation of total play ( Integer) Retries - Summation of total play, if failed. ( Integer) Duration - Total Duration ( Integer) Last Updated - Late Updated Date Time ( DateTime )

There is nothing wrong with having a PRIMARY KEY as a VARCHAR2 data type (though there is often debate about the value of having a non-specific PK, i.e. a sequence). You must, however, ensure your PK is unique, if you can't guarentee this then it would be worth having a sequence as your PK over having to introduce another columnn to maintain uniqueness.

As for declaring your table columns as INTEGER, they eventually will be resolved to NUMBER anyway so I'd just create the table column as a number (unless you have a very specific reason for creating them as INTEGER).

Finally, the DATETIME column, you only need decare it as a DATE datatype unless you need real precision in your time portion, in which case declare it as a TIMESTAMP datatype.

As for helping you with the structure of the table itself (i.e. which columns you want etc.) then that is not something I can help you with as I know nothing of your reporting requirements, application requirements or audit requirements, company best practice, naming conventions etc. I'm afraid that is something for you to decide for yourself.

For performance though, keep indexes to a minumum (i.e. only index columns that will aid your UPDATE WHERE clause search), only update the minimum data possible and, as suggested before, research the APPEND hint for inserts it may help in your case but you will have to test it for yourself.