Re: 9i Slow SQL Performance - EXPERTS apply within

9i plans show "bitmap conversion to rowids", 8i one does not.

There's been a change from 8i to 9i that 9i can convert an usual b-tree index to bitmap for accessing a low-cardinality index in a large table.

You may try the following in 9i and see if the performance improves -

alter session set "_B_TREE_BITMAP_PLANS"=false;

• this was default FALSE in 8i and is TRUE in 9i.

I've seen most of the times this conversion impacts performance for OLTP queries.

HTH. Johne_uk wrote:
> I did the level 12 trace and got the following results. Time seems to
> be in the fetch statement. I guess Optimising the query is the only way
> ahead then.
>
>
> TKPROF: Release 9.2.0.7.0 - Production on Wed Apr 5 15:52:46 2006
>
> Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
>
> Trace file: 3624.txt
> Sort options: default
>
> ********************************************************************************
> count = number of times OCI procedure was executed
> cpu = cpu time in seconds executing
> elapsed = elapsed time in seconds executing
> disk = number of physical reads of buffers from disk
> query = number of buffers gotten for consistent read
> current = number of buffers gotten in current mode (usually for
> update)
> rows = number of rows processed by the fetch or execute call
> ********************************************************************************
>
> alter session set events '10046 trace name context forever, level 12'
>
>
> call count cpu elapsed disk query current
> rows
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> Parse 0 0.00 0.00 0 0 0
> 0
> Execute 1 0.00 0.00 0 0 0
> 0
> Fetch 0 0.00 0.00 0 0 0
> 0
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> total 1 0.00 0.00 0 0 0
> 0
>
> Misses in library cache during parse: 0
> Optimizer goal: CHOOSE
> Parsing user id: SYS
>
> Elapsed times include waiting on following events:
> Event waited on Times Max. Wait Total
> Waited
> ---------------------------------------- Waited ----------
> ------------
> SQL*Net message to client 1 0.00
> 0.00
> SQL*Net message from client 1 5.90
> 5.90
> ********************************************************************************
>
> select a.COMPANY_ID, a.SHIP_ID, a.VOYAGE_NO, a.FIXTURE_ID, a.STARTDATE,
> a.ENDDATE, a.ACCT_COMPANY_ID, a.MIN_VOYAGE_NO, a.MAX_VOYAGE_NO from
> chops_web.va_voyage_list a,
> chops_web.ch_fixture_summary b, chops_web.ma_company c where
> a.company_id = 2 and
> a.enddate >= to_date('01/01/2006 12:00:00 AM','mm/dd/yyyy hh:mi:ss
> am')
> and a.ship_id not in (select field_text_value from
> chops_web.va_config_info
> where company_id = 2 and field_id like 'EXCLUDE_SHIP%') and
> a.company_id = b.company_id and a.fixture_id = b.fixture_id and
> a.company_id = c.company_id and ( b.exclude_from_pool = 0 or
> c.pool_flag
> = 0)
>
> call count cpu elapsed disk query current
> rows
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> Parse 1 0.00 0.00 0 0 0
> 0
> Execute 1 0.00 0.00 0 0 0
> 0
> Fetch 3 36.82 36.05 0 164592 0
> 30
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> total 5 36.82 36.06 0 164592 0
> 30
>
> Misses in library cache during parse: 0
> Optimizer goal: ALL_ROWS
> Parsing user id: SYS
>
> Rows Row Source Operation
> ------- ---------------------------------------------------
> 30 FILTER
> 114690 SORT GROUP BY
> 443468 FILTER
> 443468 NESTED LOOPS
> 116 NESTED LOOPS
> 116 FILTER
> 8042 NESTED LOOPS OUTER
> 2736 NESTED LOOPS OUTER
> 2736 NESTED LOOPS
> 8626 HASH JOIN
> 8636 HASH JOIN
> 9257 HASH JOIN
> 264 NESTED LOOPS
> 1 TABLE ACCESS BY INDEX ROWID OBJ#(37324)
> 1 INDEX UNIQUE SCAN OBJ#(37325) (object id 37325)
> 264 INDEX FULL SCAN OBJ#(37381) (object id 37381)
> 9257 INDEX FAST FULL SCAN OBJ#(37515) (object id 37515)
> 317 INDEX FAST FULL SCAN OBJ#(37392) (object id 37392)
> 9303 TABLE ACCESS FULL OBJ#(37520)
> 2736 TABLE ACCESS BY INDEX ROWID OBJ#(37215)
> 8616 INDEX UNIQUE SCAN OBJ#(37216) (object id 37216)
> 2736 TABLE ACCESS BY INDEX ROWID OBJ#(36505)
> 2736 INDEX UNIQUE SCAN OBJ#(37496) (object id 37496)
> 8038 TABLE ACCESS BY INDEX ROWID OBJ#(36505)
> 8038 AND-EQUAL
> 65431 INDEX RANGE SCAN OBJ#(37495) (object id 37495)
> 61193 INDEX RANGE SCAN OBJ#(37494) (object id 37494)
> 116 TABLE ACCESS BY INDEX ROWID OBJ#(36505)
> 116 INDEX RANGE SCAN OBJ#(37496) (object id 37496)
> 443468 TABLE ACCESS FULL OBJ#(37215)
> 0 TABLE ACCESS FULL OBJ#(37573)
> 0 SORT AGGREGATE
> 0 TABLE ACCESS BY INDEX ROWID OBJ#(36505)
> 0 BITMAP CONVERSION TO ROWIDS
> 0 BITMAP AND
> 0 BITMAP CONVERSION FROM ROWIDS
> 0 INDEX RANGE SCAN OBJ#(37494) (object id 37494)
> 0 BITMAP CONVERSION FROM ROWIDS
> 0 INDEX RANGE SCAN OBJ#(37495) (object id 37495)
>
>
> Elapsed times include waiting on following events:
> Event waited on Times Max. Wait Total
> Waited
> ---------------------------------------- Waited ----------
> ------------
> SQL*Net message to client 3 0.00
> 0.00
> SQL*Net message from client 3 3.74
> 3.79
>
>
>
> ********************************************************************************
>
> OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
>
> call count cpu elapsed disk query current
> rows
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> Parse 1 0.00 0.00 0 0 0
> 0
> Execute 2 0.00 0.00 0 0 0
> 0
> Fetch 3 36.82 36.05 0 164592 0
> 30
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> total 6 36.82 36.06 0 164592 0
> 30
>
> Misses in library cache during parse: 0
>
> Elapsed times include waiting on following events:
> Event waited on Times Max. Wait Total
> Waited
> ---------------------------------------- Waited ----------
> ------------
> SQL*Net message to client 4 0.00
> 0.00
> SQL*Net message from client 4 5.90
> 9.70
>
>
> OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
>
> call count cpu elapsed disk query current
> rows
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> Parse 0 0.00 0.00 0 0 0
> 0
> Execute 0 0.00 0.00 0 0 0
> 0
> Fetch 0 0.00 0.00 0 0 0
> 0
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> total 0 0.00 0.00 0 0 0
> 0
>
> Misses in library cache during parse: 0
>
> 2 user SQL statements in session.
> 0 internal SQL statements in session.
> 2 SQL statements in session.
> ********************************************************************************
> Trace file: 3624.txt
> Trace file compatibility: 9.02.00
> Sort options: default
>
> 1 session in tracefile.
> 2 user SQL statements in trace file.
> 0 internal SQL statements in trace file.
> 2 SQL statements in trace file.
> 2 unique SQL statements in trace file.
> 91 lines in trace file.
Received on Mon Apr 10 2006 - 00:05:35 CDT