Re: Oracle v7.3.2 Benchmark Tests Update

From: Darren Darnell <dba0077_at_edwardjones.com>
Date: 1996/11/14
Message-ID: <Pine.3.94.961114104251.9749A-100000_at_nddba1>#1/1


I have called Oracle support on the issue of the RULE based optimizer performing slower on v7.3.2. I was informed that Oracle has added a tablespace type parameter. The tablespace types are PERMANENT and TEMPORARY. Apparently the TEMPORARY tablespace does not keep track of extents, since it is only used for sorts.

Changing our temporary table space to type TEMPORARY did not resolve our problem with v7.3.2 performance. By the way, you can change your temporary tablespace type to TEMPORARY using ALTER TABLESPACE tablespace_name TEMPORARY; you cannot have any temporary tables in the tablespace to do this.

The support represenative suggested that I use tkprof to analyze my benchmark tests. I did and here's the results for the largest query in 'bench07':

Oracle v7.1.4 test bench07 tkprof output:


 

SELECT C.CUSTNO,C.FIRSTNAME || ' ' || C.LASTNAME ,ROUND(SUM(A.SHARES *   Q.PRICE ),2) NETWORTH
FROM
 TST1.CUSTOMER C,TST1.QUOTES Q,TST1.ACCOUNT A WHERE A.CUSTNO = C.CUSTNO AND
  A.SYMBOL = Q.SYMBOL AND Q.QUOTEDATE = (SELECT MAX(T.QUOTEDATE) FROM   TST1.QUOTES T WHERE T.SYMBOL = Q.SYMBOL ) GROUP BY C.CUSTNO,C.FIRSTNAME
|| ' ' || C.LASTNAME ORDER BY NETWORTH DESC
    call count cpu elapsed disk query current rows -------- ------- -------- --------- -------- -------- ------- -------

Parse           1      0.02       0.02        0        0       0       0
Execute         2      0.18       0.24        5      164      36       0
Fetch          10     99.75     115.83     2792   969201    2509      10
 
--------  -------  --------  --------- -------- -------- ------- -------
total          13     99.95     116.09     2797   969365    2545      10
 

Misses in library cache during parse: 1
Optimizer hint: RULE
Parsing user id: 55 (???) (recursive depth: 1)  

Rows Execution Plan

-------  ---------------------------------------------------
      0  SELECT STATEMENT   OPTIMIZER HINT: RULE
  30000    SORT (ORDER BY)
  81000      SORT (GROUP BY)
 159187        FILTER
 159187          NESTED LOOPS
  81000            NESTED LOOPS
  81000              TABLE ACCESS (FULL) OF 'ACCOUNT'
  81000              TABLE ACCESS (BY ROWID) OF 'CUSTOMER'
  81000                INDEX (UNIQUE SCAN) OF 'CUSTOMER_PK' (UNIQUE)
 159187            TABLE ACCESS (BY ROWID) OF 'QUOTES'
 240187              INDEX (RANGE SCAN) OF 'QUOTES_PK' (UNIQUE)
 159187          SORT (AGGREGATE)
 240187            INDEX (RANGE SCAN) OF 'QUOTES_PK' (UNIQUE)
 
******************************************************************************
 

Oracle v7.3.2 test bench07 tkprof output:


 

SELECT C.CUSTNO,C.FIRSTNAME || ' ' || C.LASTNAME ,ROUND(SUM(A.SHARES *   Q.PRICE ),2) NETWORTH
FROM
 TST3.CUSTOMER C,TST3.QUOTES Q,TST3.ACCOUNT A WHERE A.CUSTNO = C.CUSTNO AND
  A.SYMBOL = Q.SYMBOL AND Q.QUOTEDATE = (SELECT MAX(T.QUOTEDATE) FROM   TST3.QUOTES T WHERE T.SYMBOL = Q.SYMBOL ) GROUP BY C.CUSTNO,C.FIRSTNAME
|| ' ' || C.LASTNAME ORDER BY NETWORTH DESC
    call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ------

Parse        1      0.01       0.01          0          0          0      0
Execute      2      0.16       0.19          0          0          0      0
Fetch        4    134.24     138.11       2195     968706       2394      4
------- ------  -------- ---------- ---------- ---------- ---------- ------
total        7    134.41     138.31       2195     968706       2394      4
 

Misses in library cache during parse: 0
Optimizer goal: RULE
Parsing user id: 55 (DBA0077) (recursive depth: 1)  

Rows Execution Plan

-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: RULE

  30000 SORT (ORDER BY)
  81000 SORT (GROUP BY)
 159187     FILTER
 159187      NESTED LOOPS
  81000       NESTED LOOPS
  81000        TABLE ACCESS   GOAL: ANALYZED (FULL) OF 'ACCOUNT'
  81000        TABLE ACCESS   GOAL: ANALYZED (BY ROWID) OF 
                   'CUSTOMER'
  81000         INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 
                    'CUSTOMER_PK' (UNIQUE)
 159187       TABLE ACCESS   GOAL: ANALYZED (BY ROWID) OF 'QUOTES'
 240187        INDEX   GOAL: ANALYZED (RANGE SCAN) OF 'QUOTES_PK' 
                   (UNIQUE)
 159187      SORT (AGGREGATE)
 240187       INDEX   GOAL: ANALYZED (RANGE SCAN) OF 'QUOTES_PK' 
                  (UNIQUE)
 
********************************************************************************
 

Darren Darnell, darren.darnell_at_edwardjones.com Database Systems Team, Edward Jones
201 Progress Parkway, Maryland Heights, MO 63043-3042 Phone: 314.515.1172 FAX: 314.515.2599 Received on Thu Nov 14 1996 - 00:00:00 CET

Original text of this message