Performance

[sanjay kan]

We have a sql query .
This query performing well in QA but not performaing well in Prod ?
What and where should i look for ??

Any guidence will be appreciated.

SANJAY

[marist89]

Compare the query plans.

[sanjay kan]

Explain plan is also same, we have identical system and database

[marist89]

Is it the same volume of data?
Is the data distributed on disk differently?
Are the SGA's the same?
Is all your hardware functioning properly?

[sanjay kan]

We have ensure that also.
I think that I/O contention ...?????

[marist89]

Maybe. What are your disks doing? How is your data distributed? Have you analyzed your tables & indexes? Are you using the same optimizer in both dbs?

[sanjay kan]

Tables and indexes both are analyze at a regular interval.
We have folowing configuration
Mount Point
u01 oracle software
u02 data and system datafiles,
u03 RBS files
u04 indexes files
u05 temp files
u06 datafiles
u07 datafiles
u08 archivelogfiles.


Control files and redolog groups are distributed across the disk...

I fire following query and Threre is much difference between
PHYRDS and PHYWRTS.

col PHYRDS format 999,999,999
col PHYWRTS formay 999,999,999
col READTIM format 999,999,999
col WRITETIM format 999,999,999
col name format a40
spool fiol.out

select name, phyrds, phywrts, readtim, writetim
from v$filestat a, v$datafile b
where a.file# = b.file#
order by readtim desc

[TimHall]

Just a thought:

Have you gathered stats for the SYS schema. This always trashes the performance on my systems. The data dictionary needs to run with the RBO. Do the following and try again:

conn sys/password@service as sysdba
EXEC DBMS_STATS.DELETE_SCHEMA_STATS('SYS');

If this is the case avoid the GATHER_DATABASE_STATS procedure in favour of GATHER_SCHEMA_STATS.

Cheers

[sanjay kan]

No ......Sir This is not the case.
There no stats for SYS.

[TimHall]

Sorry. Worth a shot!

I would try getting trace on this and using TKPROF. This may give you more detail on the differences between the repsonse from the servers.

Cheers

Tim...