Varying run times, explaining statistics

[SJSFoxPro]

We have an application that is executing the same query with widely varying times in different database instances.
The problem is that in our Development instance, it runs fine, in our Production instance it is getting hung up causing reports to time out.

We have checked for any discrepancies in server environments, database parameters, etc. Both are Oracle9i databases and we found nothing about the environments to explain the difference in execution time.

Can anyone help me with questions to research or provide any answer to what might cause a discrepancy like this?

Here's the query (there are only 412 rows in this table!):
SELECT DISTINCT ancillary_cty_town_cd FROM CTY_TOWN_CD_TBL
WHERE UPPER(cty_town_nme) = UPPERb1)
AND ancillary_cty_town_cd <> 0
AND ancillary_cty_town_cd is not null
AND void <> 'Y'

We have the following statistic information:
PRODUCTION RESULTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 4339 5.56 68.23 0 8822 26781 0
Fetch 4339 3.05 5.38 0 21695 0 4105

DEVELOPMENT RESULTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.01 0 0 0 0
Execute 4515 0.08 0.09 0 0 0 0
Fetch 4515 2.56 2.51 0 22575 0 4515

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 64 (USER) (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
4105 SORT UNIQUE
9318 TABLE ACCESS FULL CTY_TOWN_CD_TBL

I appreciate any direction anyone can give me to help answer the "why" being asked. We must first explain why the databases are acting differently on the same query before we can give it back to application development to rewrite or tune the query itself... (Which does make sense - we want to understand the cause first.)

Thank You!

 

[SantaMufasa]

SJS,

Have you confirmed that:

1) the same indexes on the Production instance are on the Dev instance?
2) you have re-calculated statistics recently on the Production instance's schema that owns the query's tables?
3) the query's tables in both instances contain roughly the same number of rows?

Let us know,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[SJSFoxPro]

Knowing that you are an EXPERT, now I feel a little more confident in my instincts... I asked the same questions. And, the answers are yes, yes, and yes.

My problem is that I am very new to using statistics and tracing.

Can you lead me any further?

 

[dbtoo2001]

Here's the query (there are only 412 rows in this table!):"

According to your stats, you are showing over 4500 rows.
And in the your production system you are showing high cpu usage. Run statspack and see what it is doing.

Execute 4339 5.56 68.23 0 8822 26781 0
Fetch 4339 3.05 5.38 0 21695 0 4105

 

[SJSFoxPro]

There really are just 412 rows in the table. It is pinned in memory when the database is started. Is there a way I can verify that once the database is up?

Can you point me to any documentation for a definition of each column in the stats so I can better understand what the numbers produced mean? I need to better understand why there are such differences in the numbers in the query and current columns between instances.

Thanks!

 

[dbtoo2001]

http://www.oracle.com/technology/deploy/performance/pdf/statspack_tuning_otn_new.pdf

 

[SJSFoxPro]

We have resolved the issue and I wanted to document our findings here to finish up this thread...

After running the queries in both instances and looking at the explain plans generated, we looked at the create table statements (in practice, the structure is supposed to be the same in both instances). But, of course, the structures were different in this case!

In the development instance, the create table included "NOPARALLEL", in production, the create table included "PARALLEL (DEGREE DEFAULT INSTANCES 1)".

When the query was run in production using the NOPARALLEL hint (i.e., SELECT /*+ NOPARALLEL(CTY_TOWN_CD_TBL) */ DISTINCT...), it produced the same explain plan and the same results as in development.

Thanks for the help! A lot gained from the research!