Re: Oracle v7.3.2 Benchmark Tests Update
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