Home » RDBMS Server » Networking and Gateways » Performance difference between Windows PC and Windows on VMware (Windows 7 32-bit vs Windors Server 2003 Std x64 on VMware Virtual Platform)
Performance difference between Windows PC and Windows on VMware [message #542710] Thu, 09 February 2012 19:48 Go to next message
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
Senior Member
Account Moderator
G'day,

We are having major differences in performance when accessing an Oracle 11gR2 database from two different environments.

I won't sadden you with the gory details of the full application but I will use an indicative example of our problem.

We have SQL Developer in both environments. In each of these SQL Developer instances is a defined a connection to the same database on another machine. So there is the PC looking at a database and a virtual instance looking at the same database.

Selecting the '+' sign next to 'tables' for that connection, on the PC the list of four tables comes back in 4 seconds but on the virtual instance the list of the same four tables comes back in 1 minute and 50 seconds.

This sort of timing issue is repeatable for all accesses to the Oracle database on the third machine.

Has anyone had a similar problem? Did you solve it? What other information do you need to help me solve this problem?

David
Re: Performance difference between Windows PC and Windows on VMware [message #542711 is a reply to message #542710] Thu, 09 February 2012 19:55 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
DBMS_SESSION.SESSION_TRACE_ENABLE(TRUE,TRUE,'ALL_EXECUTIONS');
alter session set events '10046 trace name context forever, level 12';
-- invoke the SQL code
DBMS_SESSION.SESSION_TRACE_ENABLE(FALSE,FALSE,NULL);
DBMS_MONITOR.SESSION_TRACE_ENABLE(
    session_id   IN  BINARY_INTEGER DEFAULT NULL,
    serial_num   IN  BINARY_INTEGER DEFAULT NULL,
    waits        IN  BOOLEAN DEFAULT TRUE,
    binds        IN  BOOLEAN DEFAULT FALSE,
    plan_stat    IN  VARCHAR2 DEFAULT NULL);
Oracle recommends that you use the DBMS_MONITOR and DBMS_SESSION packages instead. 
SQL_TRACE is retained for backward compatibility only.


trace file shows where actual time is being spent
Re: Performance difference between Windows PC and Windows on VMware [message #542713 is a reply to message #542711] Thu, 09 February 2012 20:35 Go to previous messageGo to next message
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
Senior Member
Account Moderator
BS,

Okay, I 'exec'ed the 'dbms_session', my simple 'select', the second 'dbms_session', and then the 'dbms_monitor'.

What do I do after that?

David
Re: Performance difference between Windows PC and Windows on VMware [message #542714 is a reply to message #542713] Thu, 09 February 2012 20:47 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>What do I do after that?
The result is a trace file in udump folder.
The trace file can be post processed using the "tkprof" utility

tkprof tracefile resultsfile explain=username/password
bcm@bcm-laptop:~$ tkprof
Usage: tkprof tracefile outputfile [explain= ] [table= ]
              [print= ] [insert= ] [sys= ] [sort= ]
  table=schema.tablename   Use 'schema.tablename' with 'explain=' option.
  explain=user/password    Connect to ORACLE and issue EXPLAIN PLAN.
  print=integer    List only the first 'integer' SQL statements.
  aggregate=yes|no
  insert=filename  List SQL statements and data inside INSERT statements.
  sys=no           TKPROF does not list SQL statements run as user SYS.
  record=filename  Record non-recursive statements found in the trace file.
  waits=yes|no     Record summary for any wait events found in the trace file.
  sort=option      Set of zero or more of the following sort options:
    prscnt  number of times parse was called
    prscpu  cpu time parsing
    prsela  elapsed time parsing
    prsdsk  number of disk reads during parse
    prsqry  number of buffers for consistent read during parse
    prscu   number of buffers for current read during parse
    prsmis  number of misses in library cache during parse
    execnt  number of execute was called
    execpu  cpu time spent executing
    exeela  elapsed time executing
    exedsk  number of disk reads during execute
    exeqry  number of buffers for consistent read during execute
    execu   number of buffers for current read during execute
    exerow  number of rows processed during execute
    exemis  number of library cache misses during execute
    fchcnt  number of times fetch was called
    fchcpu  cpu time spent fetching
    fchela  elapsed time fetching
    fchdsk  number of disk reads during fetch
    fchqry  number of buffers for consistent read during fetch
    fchcu   number of buffers for current read during fetch
    fchrow  number of rows fetched
    userid  userid of user that parsed the cursor

the "resultsfile" contain overall timing results & should be posted back here
Re: Performance difference between Windows PC and Windows on VMware [message #542721 is a reply to message #542714] Thu, 09 February 2012 23:07 Go to previous message
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
Senior Member
Account Moderator
BS,

I ran the following through both the Windows 7 and Windows 2003 server copies of SQL Developer.
--
set pages 1000
--
set tim on
--
-- Test how long a query takes.
--
exec dbms_session.session_trace_enable(true,true,'ALL_EXECUTIONS');
--
alter session set events '10046 trace name context forever, level 12';
--
-- invoke the SQL code
--
select * from all_tables where owner = 'my_user';
--
exec dbms_session.session_trace_enable(false,false,null);
--
exec DBMS_MONITOR.SESSION_TRACE_ENABLE;
--

I then fed the trace files through Spotlight and annoyingly there is very little difference between them.

What does the final command "DBMS_MONITOR.SESSION_TRACE_ENABLE" contribute to this trace?

David
Previous Topic: Shared Server OR Dedicated Server
Next Topic: Fatal NI connect error 12537
Goto Forum:
  


Current Time: Thu Mar 28 05:00:10 CDT 2024