Performance tuning and oracle database before it is connected to a .Net app

Question

User-1204164177 posted

Hi,

I have been given a .Net app connected to quite a large oracle database, and I have been given the task of optimising / tuning the database, i.e adding indexes partitions etc that will enhance performance..

The system has not been rolled out yet, so I am unaware of any bottle knecks or areas where performance needs enhancing, my job is to predict and alter the database as a precursor to it being rolled out..

To do this does anyone have any thaughts about the following process:

1. Predict how the system will be used and write up a list of scenarios, eg reports on x, y, and z, searching of x, y z..

2.   Run the sql for the proposed scenarios to observe quiery times

3. Add indexes / Re-format SQL statements / partitions to optimise the performance

=========================================================================================

Does anyone have any idea of better strategies?

Also, in terms of re-writing SQL statements, I believe there are tools around that will auto generate alternatove optimised SQL ststements from the one given, does anyone know of any of these?

 

Any help would be greatly appriciated

 

 

Answer 1

User1011584402 posted

Hi,

If you can figure out what test scenarios to run, run them, then you can then use various Oracle features to review the performance and provide recommendations. e.g. indexes, changes to SQL, etc.

What version of Oracle are you going to be using? If it's 11g or 10g, then using Oracle Enterprise Manager (OEM) would be the easiest. Oracle has some walkthroughs on this type of work:

1. Oracle by Example (OBE) http://www.oracle.com/technology/obe/start/index.html
2. And in particular Automatic SQL Tuning: http://www.oracle.com/technology/obe/11gr1_db/manage/ast/ast.htm

Oracle keeps performance data for up to 7 days, so if after the app goes into production and you hear users say, the app ground to halt at such and such time yesterday, you can using OEM drill down and find out what the problem was. Nice approaching instead having the users recreate the problem for you. OEM will show the offending SQL and offer recommendations via running a 'Tuning Advisor'. There's lot's more to this and all these features also are available via PL/SQL API's as well.

Let me know if you have any further questions.

Brian.
Oracle Development Tips for those stuck using Oracle ;-)
http://brianpeasey.wordpress.com/

 

 

 

Answer 2

User1400553062 posted

Hi Mattatuni2,

Oracle Performance Tuning focuses on tuning Oracle database for performance in the real time scenario.  The methodology used in the practices is primarily reactive. After configuring tools, monitoring tools and reviewing the available reports, you’ll be presented with the Oracle architecture based on the SQL statement processing of SELECT and DML. An in-depth information about this technology can be gained through our Oracle Performance Tuning training which includes right from the basics to the advanced topics of this tool. Unlike others, Tekslate is not an institute that only delivers subject knowledge of the technology but also assess the trainees and align them to meet their goal during the training period.

If you looking For more information about Performance Tuning

Answer 3

User269602965 posted

Indexes do not always speed thing up with large datasets.  Trial and error testing as you propose.  Oracle also supports different types of indexes each with its own strengths and weaknesses.  Partitioning is only helpful if the front end is only looking for data within one partition, like YEAR 2005.

Depending on the nature of the data and the timeline of how up to date it needs to be, for reporting and such, I often PRE-PROCESS complex logic into front end ready tables so the front end only has to read data (perhaps with key filter variables ready to go) and not grind through pl/sql or sql statements with many business rules and computations.  You can use Materialized views to put on refresh schedule if the data only needs to be refreshed daily, etc.