TKPROF - Database tunning question

From: Tung Nguyen <tnguyen_at_fmco.com>
Date: Mon, 7 Feb 2000 17:37:35 -0500
Message-ID: <aQHn4.10646$S64.300204@nnrp1.uunet.ca>

Hi there

Please see if you can help me out. This is about database tunning.

We have a PowerBuilder Application runs on an NT box (2 CPUs Pentium. 300) and a HP-UNIX Box (2 CPUs, 2GB of physical memory, no idea about the rest). I exported the schema from theUNIX box and loaded into the NT box to make sure the application hits the same database (schema).

When we ran the same operation on the application. The time it took on the NT box is 50% faster than the UNIX box.

I traced both servers, tkprof them and what I noticed is that the CPU and Elapsed time for Fetch are almost double on the UNIX box.

Most of the problem happens on SELECT. Below are part of the trace file. It is a SELECT statement. The execution plan is the same. All so there are another 4 Oracle instances runs on the UNIX box. By looking at the trace I have a feeling that the UNIX is CPU-bound.

Am I right?
If I stop all others Oracle instances will it help? Since the cpu and elapsed columns a in hundredth second. Which one give me the total time

            sum(total cpu + total elapsed)
            OR
            sum (total elapsed)

Thanks in advance for your help.

Tung Nguyen

From the UNIX Box ----------------------------------------------------- call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ------

Parse 1 0.03 0.04 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 12.49 12.51 933 2070 2 201
------- ------ -------- ---------- ---------- ---------- ---------- ------

total 3 12.52 12.55 933 2070 2 201

Misses in library cache during parse: 1 Optimizer goal: CHOOSE
Parsing user id: 22 (SYLVAN)

Rows Execution Plan
------- ---------------------------------------------------

      0 SELECT STATEMENT GOAL: CHOOSE     218 FILTER
  45487 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'SECURITY'     218 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'PK_NON_CASH_SEC'                (UNIQUE)
    201 SORT (AGGREGATE)

    201     FIRST ROW
    201      INDEX   GOAL: ANALYZED (RANGE SCAN) OF 'SECURITY_ID_IDX'
                 (UNIQUE)

****************************************************************************

From the NT BOX -----------------------------------------

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ------

Parse 1 0.03 0.03 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 7.59 7.84 1983 3346 2 201
------- ------ -------- ---------- ---------- ---------- ---------- ------

total 3 7.62 7.87 1983 3346 2 201

Misses in library cache during parse: 1 Optimizer goal: CHOOSE
Parsing user id: 149 (JANUS_400)

Rows Execution Plan
------- ---------------------------------------------------

0 SELECT STATEMENT GOAL: CHOOSE 218 FILTER
45487 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'SECURITY' 218 INDEX (UNIQUE SCAN) OF 'PK_NON_CASH_SEC' (UNIQUE) 201 SORT (AGGREGATE)

    201     FIRST ROW
    201      INDEX   GOAL: ANALYZED (RANGE SCAN) OF 'SECURITY_ID_IDX'
                 (UNIQUE)

****************************************************************************

......
.....
.....
.....
......

And these are the overall of the two sessions

FROM UNIX Box -----------------------------------------------------------
****************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ------

Parse 7045 45.12 48.72 0 0 0 0
Execute 7045 3.23 3.38 0 7725 37301 6937
Fetch 151 385.95 387.71 55047 104605 196 14962
------- ------ -------- ---------- ---------- ---------- ---------- ------

total 14241 434.30 439.81 55047 112330 37497 21899

Misses in library cache during parse: 7025

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ------

Parse 6921 3.94 4.15 0 0 0 0
Execute 6933 5.71 5.47 0 241852 1553 1473
Fetch 6942 1.51 1.56 4 35136 2 6904
------- ------ -------- ---------- ---------- ---------- ---------- ------

total 20796 11.16 11.18 4 276988 1555 8377

Misses in library cache during parse: 3

7051 user SQL statements in session. 6915 internal SQL statements in session. 13966 SQL statements in session.
7029 statements EXPLAINed in this session.

Trace file: c:\temp\ora_28069.trc
Trace file compatibility: 7.03.02
Sort options: default

1 session in tracefile.
7051 user SQL statements in trace file. 6915 internal SQL statements in trace file. 13966 SQL statements in trace file. 7040 unique SQL statements in trace file. 7029 SQL statements EXPLAINed using schema:

           sylvan.plan_table
             Schema was specified.
             Existing table was used.

126262 lines in trace file.

FROM NT Box -------------------------------------------------------------
****************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ------

Parse 3379 43.63 53.11 0 0 0 0
Execute 3379 2.31 2.88 15 4826 21936 3284
Fetch 103 114.81 123.60 60656 105310 116 10982
------- ------ -------- ---------- ---------- ---------- ---------- ------

total 6861 160.75 179.59 60671 110136 22052 14266

Misses in library cache during parse: 3367

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ------

Parse 3472 9.79 12.72 0 0 0 0
Execute 3521 3.75 5.16 1790 323624 1862 1572
Fetch 3601 0.80 1.22 33 24750 2 3507
------- ------ -------- ---------- ---------- ---------- ---------- ------

total 10594 14.34 19.10 1823 348374 1864 5079

Misses in library cache during parse: 39

3398 user SQL statements in session. 3454 internal SQL statements in session. 6852 SQL statements in session.
3383 statements EXPLAINed in this session.

Trace file: ora00208.trc
Trace file compatibility: 7.03.02
Sort options: default

1 session in tracefile.
3398 user SQL statements in trace file. 3454 internal SQL statements in trace file. 6852 SQL statements in trace file. 3412 unique SQL statements in trace file. 3383 SQL statements EXPLAINed using schema:

           janus_400.plan_table
             Schema was specified.
             Existing table was used.

62171 lines in trace file.

Received on Mon Feb 07 2000 - 16:37:35 CST