Oracle incremental performance

[MSIsam]

I am on the business side of providing requirments for a reporting application and I just received some perplexing perfomance stats from the Database team. The execution time (database run time only) was as follows for the 4 different levels of data.

Region  65 , Division 651, Territory 65100:        4 seconds
Region  65 , Division 651, Territory   ALL:       12 seconds
Region  65 , Division ALL, Territory   ALL:      [COLOR=red]228[/color] seconds
Region ALL , Division ALL, Territory   ALL:      476 seconds

The data is spread fairly evenly and there are:

8 territories in Division 651, one of which is 65100
4 Divisions in Region 65
4 Regions in Nation (Where region = ALL)

When I inquired as to why the disproportionate jump from the division level query to the region level query (only 4X the added data but almost 20X increase in time), I got the following reply:

"The oracle optimizer decides on the execution plan given the statistics it has to work with.   The plan is not always the best for a particular query and set of predicates but when you look at the overall performance of all of the queries it’s actually doing great.   Some products or regions may have more data or higher distribution/density of data, i.e. many factors that affect the execution plan.   In the test below the execution plan for each role is different because of the level of data we are requesting.  The amount of data does vary as you state below but the response times will not necessarily vary by the same factor given that oracle is using a totally separate access path."

Is this just something that I should accept or is there something missing such as an index or statistics capture? I understnad that this may be hard to answer since I cannot provide the specifics of the query but I just wanted to get a reality check.

Any insight is greatly appreciated,
Sam

 

[Turkbear]

Hi,
Ask your DBA, after having the table(s) involved analyzed, to run each query with an Explain Plan to see what is different in the execution plans, and to show you the results ..





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[SantaMufasa]

Besides Turkbear's correct suggestion(s), also have your DBA confirm that indexes exist for the columns that appear within equality expressions in your query's WHERE clause.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[ddiamond]

Sometimes, even if you have the correct indexes defined and your stats are up todate, the optimizer may still not choose to use the indexes. In this case, specifying an optimizer "hint" can be very useful. But I am getting ahead of myself. As Turkbear mentioned, the first step is to look at the execution plans.