Hi,
Production DB conf :- ( 10.2.0.4.0)EE RAC DB 2 nodes
( please note that we have one and only one working user in which all tables are located in user tablespace )
Processors :-16 (Xeon , 3.8Ghz)
RAM :- 16GB ( sga_target =11g)
filetype:- ASM
block size :- 8192
OS :- Win Server 2008 EE
Better i/o ( More than 1000 MB/S)
Schema size ( our only working schema ) :- 63G
A long SELECT query with 12 unions takes 17-19 seconds
Test Server conf :- ( 10.2.0.4.0)EE
( Please not that we daily update our Test server Sync to Production server with Network Import with DB-link , drop schema and then import )
Processors :- 8 ( i-7 , 3.4Ghz )
RAM- 8G (sga_target= 1536M )
filetype :- ntfs
block size :- 8192
OS :- Win server 2008 EE
Normal i/o (120MB/S)
Schema size ( same schema as production ) :- 23G
Same select query takes 6-7 seconds
-----------------------------------------------------------
I understand that production Server will have heavy load but, is the result of query taking less time in test server because of size of schema on disk are different?( We have never rebuild an Index, We haven't shrink space yet once )
Please share your thoughts.
Thank you