Re: Oracle v7.3.2 Benchmark Tests Update

Darren Darnell wrote:
>
> 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)
>
>

On studying this for quite some time, I throwing in my 2 cents.

The difference in performance is in the CPU time spent in fetch. Everything else ( number of rows fetched, execution path ) is identical. The number of disk reads is smaller in 7.3 than in 7.1 on this query.

My suggestions are the following.
1. In a rule based query, the last table is the "DRIVING table". So, use the smallest table(number of rows) as the last table. Account here is the driving table.
2. Write a function that takes Quote Symbol and returns the max QuoteDate. This will definitly improve your performance.

SOrt_direct_writes, IMHO is only for temporary tablespaces and does not effect SORT_AREA_SIZE.

Let us know what you find out.

Venkat Jayaram
Principal COnsultant
Enterprise SYstems COnsulting, Inc.

std disclaimer applies. Received on Sun Nov 17 1996 - 00:00:00 CET