| How to get the CBO to recognize a FBI [message #184751] |
Thu, 27 July 2006 14:28  |
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  |
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 |
rleishman
Messages: 3728
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 |
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?
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
