Home » RDBMS Server » Performance Tuning » How to get the CBO to recognize a FBI
How to get the CBO to recognize a FBI [message #184751] Thu, 27 July 2006 14:28 Go to next message
brekhof
Messages: 1
Registered: July 2006
Junior Member
Hi,

I've a problem supplying the CBO with the correct information (I think) as it
returns always a sub-optimal execution plan. The background information:
Table AHD_REQUEST is a 4.4 million row table containing some 190+ fields, it has it records either updated
or inserted by an external proces. Deleting is not done (a field ARCHIVED is used to mark a record as deleted).
The table contains information about support requests (tickets) including a field REQ_ACTIVE_FLAG which is either 1
(active) or 0 (not active, closed) in which case REQ_CLOSE_DATE contains a valid date (otherwise it is NULL).
The table is used in several customer websites to provide information about active tickets and
the tickets that were closed during the last month.
As the queries retrieving that information were (very) slow I decided to partition the table on REQ_CLOSE_DATE (each

month of the current year has it's own partition, before that the data is gathered on a yearly basis.
Furthermore I created a function based index using the following syntax:
created index AKTIEF on AHD_REQUEST(decode(REQ_ACTIVE_FLAG,1,1,NULL)=1)
It is used to retrieve the active records. The FBI works like a charm, having a size of only 0.69 Mb when
tested.
However, we have several views using a syntax like:
Select <fields>
from AHD_REQUEST, AHD_CI
WHERE REQ_CI_FK = CI_ID (+)
AND REQ_REQUESTOR_ACCOUNTCODE_NAME = 'AC0047'
AND REQ_SERVICE_ENUM = 18

AND (REQ_ACTIVE_FLAG=1 OR MONTHS_BETWEEN(SYSDATE, REQ_CLOSE_DATE)<1)
It gives an execution plan NOT using the AKTIEF index but a cost of only 57. It runs in about 37 seconds.

-------------------------------------------------------------------------------------------------------------------

------------ | Id | Operation | Name |

Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------

------------
| 0 | SELECT STATEMENT | | 1 | 271 | 57 (0)| 00:00:01 |

| |
| 1 | NESTED LOOPS OUTER | | 1 | 271 | 57 (0)| 00:00:01 |

| |
| 2 | PARTITION RANGE ALL | | 1 | 244 | 56 (0)| 00:00:01 |

1 | 15 |
|* 3 | TABLE ACCESS BY LOCAL INDEX ROWID| AHD_REQUEST | 1 | 244 | 56 (0)| 00:00:01 |

1 | 15 |
| 4 | BITMAP CONVERSION TO ROWIDS | | | | | |

| |
| 5 | BITMAP AND | | | | | |

| |
| 6 | BITMAP CONVERSION FROM ROWIDS | | | | | |

| |
|* 7 | INDEX RANGE SCAN | IX_REQ_ACCOUNTCODE_NAME | 8664 | | 26 (0)| 00:00:01 |

1 | 15 |
| 8 | BITMAP CONVERSION FROM ROWIDS | | | | | |

| |
|* 9 | INDEX RANGE SCAN | IX_SERVICEENUM | 8664 | | 26 (0)| 00:00:01 |

1 | 15 |
| 10 | TABLE ACCESS BY INDEX ROWID | AHD_CI | 1 | 27 | 1 (0)| 00:00:01 |

| |
|* 11 | INDEX UNIQUE SCAN | AHD_CI_ID__PK | 1 | | 1 (0)| 00:00:01 |

| |
-------------------------------------------------------------------------------------------------------------------

------------

Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(MONTHS_BETWEEN(SYSDATE@!,INTERNAL_FUNCTION("REQ_CLOSE_DATE"))<1 OR "REQ_ACTIVE_FLAG"=1)
7 - access("REQ_REQUESTOR_ACCOUNTCODE_NAME"='AC0047')
9 - access("REQ_SERVICE_ENUM"=18)
11 - access("REQ_CI_FK"="CI_ID"(+))

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
7653 consistent gets
0 physical reads
0 redo size
20153 bytes sent via SQL*Net to client
469 bytes received via SQL*Net from client
10 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
129 rows processed

However the 37 seconds for the first time is considered to be way to long, hence my enhanced query which runs in 2

seconds:
SELECT <fields>
FROM AHD_REQUEST
, AHD_CI
WHERE REQ_CI_FK = CI_ID (+)
AND REQ_REQUESTOR_ACCOUNTCODE_NAME = 'AC0047'
AND REQ_SERVICE_ENUM = 18
AND MONTHS_BETWEEN(SYSDATE, REQ_CLOSE_DATE)<1
AND REQ_CLOSE_DATE > SYSDATE -32 -- USE the PARTITIONING
UNION ALL
SELECT /*+ INDEX(AHD_REQUEST,AKTIEF) */ <fields>
FROM AHD_REQUEST
, AHD_CI
WHERE REQ_CI_FK = CI_ID (+)
AND REQ_REQUESTOR_ACCOUNTCODE_NAME = 'AC0047'
AND REQ_SERVICE_ENUM = 18
AND DECODE(REQ_ACTIVE_FLAG,1,1,NULL)=1 -- USE the FBI



-------------------------------------------------------------------------------------------------------------------

------------- | Id | Operation | Name |

Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ------------------------------

--------------------------------------------------------------------------------------------------


| 0 | SELECT STATEMENT | | 2 | 542 | 3553 (99)| 00:00:53 |

| |
| 1 | UNION-ALL | | | | | |

| |
| 2 | NESTED LOOPS OUTER | | 1 | 271 | 51 (4)| 00:00:01 |

| |
| 3 | PARTITION RANGE ITERATOR | | 1 | 244 | 50 (4)| 00:00:01 |

KEY | 15 |
|* 4 | TABLE ACCESS BY LOCAL INDEX ROWID| AHD_REQUEST | 1 | 244 | 50 (4)| 00:00:01 |

KEY | 15 |
| 5 | BITMAP CONVERSION TO ROWIDS | | | | | |

| |
| 6 | BITMAP AND | | | | | |

| |
| 7 | BITMAP CONVERSION FROM ROWIDS | | | | | |

| |
| 8 | SORT ORDER BY | | | | | |

| |
|* 9 | INDEX RANGE SCAN | IX_REQ_CLOSE_DATE | 611 | | 18 (0)| 00:00:01 |

KEY | 15 |
| 10 | BITMAP CONVERSION FROM ROWIDS | | | | | |

| |
|* 11 | INDEX RANGE SCAN | IX_REQ_ACCOUNTCODE_NAME | 611 | | 26 (0)| 00:00:01 |

KEY | 15 |
| 12 | TABLE ACCESS BY INDEX ROWID | AHD_CI | 1 | 27 | 1 (0)| 00:00:01 |

| |
|* 13 | INDEX UNIQUE SCAN | AHD_CI_ID__PK | 1 | | 1 (0)| 00:00:01 |

| |
| 14 | NESTED LOOPS OUTER | | 1 | 271 | 3502 (1)| 00:00:52 |

| |
|* 15 | TABLE ACCESS BY GLOBAL INDEX ROWID| AHD_REQUEST | 1 | 244 | 3501 (1)| 00:00:52 |

ROWID | ROWID |
|* 16 | INDEX RANGE SCAN | AKTIEF | 30195 | | 38 (0)| 00:00:01 |

| |
| 17 | TABLE ACCESS BY INDEX ROWID | AHD_CI | 1 | 27 | 1 (0)| 00:00:01 |

| |
|* 18 | INDEX UNIQUE SCAN | AHD_CI_ID__PK | 1 | | 1 (0)| 00:00:01 |

| |


-------------------------------------------------------------------------------------------------------------------

-------------


Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("REQ_SERVICE_ENUM"=18)
9 - access("REQ_CLOSE_DATE">SYSDATE@!-32)
filter(MONTHS_BETWEEN(SYSDATE@!,INTERNAL_FUNCTION("REQ_CLOSE_DATE"))<1 AND "REQ_CLOSE_DATE">SYSDATE@!-32)
11 - access("REQ_REQUESTOR_ACCOUNTCODE_NAME"='AC0047')
13 - access("REQ_CI_FK"="CI_ID"(+))
15 - filter("REQ_REQUESTOR_ACCOUNTCODE_NAME"='AC0047' AND "REQ_SERVICE_ENUM"=18)
16 - access(DECODE("REQ_ACTIVE_FLAG",1,1,NULL)=1)
18 - access("REQ_CI_FK"="CI_ID"(+))

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
7492 consistent gets
0 physical reads
0 redo size
20153 bytes sent via SQL*Net to client
469 bytes received via SQL*Net from client
10 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
129 rows processed



In the end I went from 37 to 2 seconds (the explain plan was made on a development machine), however only because I

hinted that Oracle should use the FBI, otherwise it will ignore its existence. All statistics etc. are up to date

(gathered with dbms_stats every night). As a hint is only a hint and something Oracle could ignore I would like some

reason why Oracle isn't using the FBI and what I should do to remedy that situation.

Further information: Oracle 10G r1 on zLinux on a (virtual) 3 processor IBM Mainframe with 4 Gb memory (db_cache

about 2.8 Gb). Total records in this table : about 4.4 milion of which 30.000 have a REQ_ACTIVE_FLAG with the value 1.





Re: How to get the CBO to recognize a FBI [message #184765 is a reply to message #184751] Thu, 27 July 2006 17:52 Go to previous messageGo to next message
wagnerch
Messages: 58
Registered: July 2006
Member
The QUERY_REWRITE_INTEGRITY and QUERY_REWRITE_ENABLED parameters must be set or the server will not be able to rewrite the queries.

Another option is to create a histogram (wouldn't need query rewrite enabled). The example below creates a table (t1) with 4,022,500 rows, where 22,500 of them have the flag set to 1 and the rest have it set to 0.

create table t1 as
with generator as (
   select --+ materialize
          rownum id
	 from all_objects
	where rownum <= 2000
)
select rownum id, 0 flag
from generator,generator
Table created
 
insert into t1
with generator as (
   select --+ materialize
          rownum id
	 from all_objects
	where rownum <= 150
)
select (2000*2000) + rownum id, 1 flag
from generator,generator
22500 rows inserted
 
 
commit
Commit complete
 
alter table t1 add (primary key (id))
Table altered
 
create index t1_idx on t1(flag) compress
Index created
 
begin
dbms_stats.gather_table_stats(tabname => 'T1', ownname => user, estimate_percent
end;
PL/SQL procedure successfully completed
 
explain plan for
select * from t1
where flag = 0
Explained
 
select * from table(dbms_xplan.display)
PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
Plan hash value: 3617692013                                                     
                                                                                
--------------------------------------------------------------------------      
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |      
--------------------------------------------------------------------------      
|   0 | SELECT STATEMENT  |      |  4000K|    26M|  2601  (15)| 00:00:19 |      
|*  1 |  TABLE ACCESS FULL| T1   |  4000K|    26M|  2601  (15)| 00:00:19 |      
--------------------------------------------------------------------------      
                                                                                
Predicate Information (identified by operation id):                             
---------------------------------------------------                             
                                                                                
   1 - filter("FLAG"=0)                                                         
13 rows selected
 
 
explain plan for
select * from t1
where flag = 1
Explained
 
select * from table(dbms_xplan.display)
PLAN_TABLE_OUTPUT                                                               
--------------------------------------------------------------------------------
Plan hash value: 546753835                                                      
                                                                                
--------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        | 22500 |   153K|    81   (5)| 00:0
|   1 |  TABLE ACCESS BY INDEX ROWID| T1     | 22500 |   153K|    81   (5)| 00:0
|*  2 |   INDEX RANGE SCAN          | T1_IDX | 22500 |       |    39   (6)| 00:0
--------------------------------------------------------------------------------
                                                                                
Predicate Information (identified by operation id):                             
---------------------------------------------------                             
                                                                                
   2 - access("FLAG"=1)                                                         
14 rows selected
Re: How to get the CBO to recognize a FBI [message #184772 is a reply to message #184765] Thu, 27 July 2006 21:52 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Instead of the FBI, did it use some other index. It may think that the other index is better.

You could also try a bitmap index. Obviously it will never be used when the flag is 0, but it should work nicely with 1, and will also result in more intuative code. With only two possible values, it shouldn't take up too much space.

Ross Leishman
Re: How to get the CBO to recognize a FBI [message #184815 is a reply to message #184751] Fri, 28 July 2006 02:59 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Your first query, which didn't use the FBI when you think it should have was
Select <fields>
from AHD_REQUEST, AHD_CI
WHERE REQ_CI_FK = CI_ID (+) 
AND REQ_REQUESTOR_ACCOUNTCODE_NAME = 'AC0047' 
AND REQ_SERVICE_ENUM = 18 
AND (REQ_ACTIVE_FLAG=1 OR MONTHS_BETWEEN(SYSDATE, REQ_CLOSE_DATE)<1) 


There is no way this query CAN use the FBI you created - the funtion that is indexed is not in the query.
Am I missing something?
Previous Topic: Optimizing Hierachical query
Next Topic: Help need .. Urgent
Goto Forum:
  


Current Time: Tue Dec 06 04:13:41 CST 2016

Total time taken to generate the page: 0.17436 seconds