Hi every Oracle ACE, gurus and Oracle specialists around the world,
I had experience a slow performance on the new testing environment.
Production environment:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
System name: Linux
Node name: sssserver
Release: 2.6.9-89.0.23.ELsmp
Version: #1 SMP Fri Mar 5 17:56:21 EST 2010
Machine: i686
Instance name: iiinstance
Redo thread mounted by this instance: 1
The new test environment, which we want to migrate, is:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 – Production
System name: HP-UX
Node name: sssserver
Release: B.11.31
Version: U
Machine: ia64
Instance name: iiinstance
Redo thread mounted by this instance: 1
The “same” parameters for SGA and same for PGA (automatic). Both databases has optimizer_mode=rules. We trace some sessions in both environment, and for a particular query I found something that makes me create this question. Both environment have the same numbers of CPU, the new test environment most part of the time is idle.
I re-read, trying to find some clue, the Cary Millsap’s book (Oracle Performance Tuning), the Christian Antognini’s book (Troubleshooting Oracle Performance), metalink note “Interpreting Raw SQL_TRACE and DBMS_SUPPORT.START_TRACE output (Doc ID 39817.1)”. But I’m not clear about FETCH means in and why is so expensive in this new environment.
FETCH : Fetch rows from a cursor (Metalink note 39817.1)
As far as I know FETCH (Database call) could means:
Fetch rows from a cursor (qer*Fetch,this could be the of sub-programs), could mean, take "some" rows from cursor on PGA read the SELECTED rows from buffer cache (the numbers depends for instance on arraysize) and send them to the client, could mean, put in TCP buffer, the that part belongs to “SQL*Net message to client” wait event.
I extracted one execution from both environment, and I have this:
HP-UX:
EXEC #12:c=0,e=98,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=3,tim=136907993581
WAIT #12: nam='SQL*Net message to client' ela= 1 p1=1413697536 p2=1 p3=0
WAIT #12: nam='SQL*Net message from client' ela= 726 p1=1413697536 p2=1 p3=0
FETCH #12:c=20000,e=1729746,p=0,cr=248,cu=0,mis=0,r=0,dep=0,og=3,tim=136909724125
WAIT #12: nam='SQL*Net message to client' ela= 2 p1=1413697536 p2=1 p3=0
WAIT #12: nam='SQL*Net message from client' ela= 5434181 p1=1413697536 p2=1 p3=0
Linux:
EXEC #12:c=0,e=155,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=3,tim=1291139738461944
WAIT #12: nam='SQL*Net message to client' ela= 4 p1=1413697536 p2=1 p3=0
WAIT #12: nam='SQL*Net message from client' ela= 890 p1=1413697536 p2=1 p3=0
FETCH #12:c=5999,e=5552,p=0,cr=233,cu=0,mis=0,r=0,dep=0,og=3,tim=1291139738468502
WAIT #12: nam='SQL*Net message to client' ela= 5 p1=1413697536 p2=1 p3=0
WAIT #12: nam='SQL*Net message from client' ela= 5411124 p1=1413697536 p2=1 p3=0
Realized:
• No rows
• Optimizer goal is 3 that means rules
• No physical reads
• The sentence is a SELECT
• Just 15 more CR between both environment
• Spend in HP-UX 3,33 time CPU fetching nothing than Linux
• Don’t think in application, that happens in application or SQL*Plus (Whatever arraysize is, and remember “no rows”)
Why for fetching “nothing” this database call spend 311 times in HP-UX than Linux?
I just want to know what exactly FETCH means (description of the process or calls) for both operating systems.
Thank you in advance for any help.
John
PS: Please avoid write links from Mr. Burlesson’s pages (dba-oracle.com)
Edited by: Ospino on 28-nov-2011 16:02