Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Erik.

Asked: October 11, 2016 - 3:04 pm UTC

Last updated: July 03, 2023 - 1:39 pm UTC

Version: 12c

Viewed 10K+ times! This question is

You Asked

Hi Tom


For a while I have struggled to optimize response time of SQL below.
When executing SQL I get response time above 500 secs and more than 1 mio. consistents gets in auto trace.

select *
from sag s
join table2 ms on (ms.fk_sagspakke_id = s.FK_SAGSPAKKE_ID)
where
sagsid = 100042140
AND (MDSYS.SDO_RELATE(ms.GEOMETRI, MDSYS.SDO_GEOMETRY(2003, 25832, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1), MDSYS.SDO_ORDINATE_ARRAY(
294245.130000, 6454399.952465,
294245.130000, 5997123.197535,
1040726.720000, 5997123.197535,
1040726.720000, 6454399.952465,
294245.130000, 6454399.952465)), 'mask=ANYINTERACT querytype=window') = 'TRUE');


-- We have 1 row in table sag complying with condition sagsid = 100042140
select count(*) "Count" from sag where sagsid = 100042140
Count
----------------------
1

-- For table table2 we have following counts:
select count(*), count(fk_sagspakke_id), count(distinct fk_sagspakke_id) from table2
COUNT(*) COUNT(FK_SAGSPAKKE_ID) COUNT(DISTINCTFK_SAGSPAKKE_ID)
---------------------- ---------------------- ------------------------------
49050223 33493547 34314

That is table2 has ~15 mio row with nulls in join field fk_sagspakke_id and on average almost 1000 values of FK_SAGSPAKKE_ID are equals.
Not optimal join conditions, we known.

The field GEOMETRI of table2 holds lines all within the square defined after above expression MDSYS.SDO_ORDINATE_ARRAY(...

-- When joining from sag to table2 we get 248 relevant rows
select count(*) "Count"
from sag s
join table2 ms on (ms.fk_sagspakke_id = s.FK_SAGSPAKKE_ID)
where
sagsid = 100042140

Count
----------------------
248

From our perspective we would expect the geo filter to be applied on these 248 rows potentially reducing to perhaps 100 rows. However this is not what happen.

Explain plan of above SQL say:


----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 2826 | 21 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 9 | 2826 | 21 (0)| 00:00:01 |
|* 2 | TABLE ACCESS BY INDEX ROWID | SAG | 1 | 150 | 3 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | SYS_C006847 | 1 | | 2 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID BATCHED| table2 | 9 | 1476 | 21 (0)| 00:00:01 |
| 5 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 6 | BITMAP AND | | | | | |
| 7 | BITMAP CONVERSION FROM ROWIDS | | | | | |

|* 8 | INDEX RANGE SCAN | tab2_FK_SAGSPAKKE_IDX | 1682 | | 3 (0)| 00:00:01 |
| 9 | BITMAP CONVERSION FROM ROWIDS | | | | | |
| 10 | SORT ORDER BY | | | | | |
|* 11 | DOMAIN INDEX (SEL: 0.100000 %)| tab2_GEOMETRI_RT | 1682 | | 6 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("S"."FK_SAGSPAKKE_ID" IS NOT NULL)
3 - access("S"."SAGSID"=100042140)
8 - access("MS"."FK_SAGSPAKKE_ID"="S"."FK_SAGSPAKKE_ID")
filter("MS"."FK_SAGSPAKKE_ID" IS NOT NULL)

11 - access("MDSYS"."SDO_RELATE"("MS"."GEOMETRI","MDSYS"."SDO_GEOMETRY"(2003,25832,NULL,"MDSYS"."SDO_ELEM_IN
FO_ARRAY"(1,1003,1),"MDSYS"."SDO_ORDINATE_ARRAY"(294245.130000,6454399.952465,294245.130000,5997123.197535,104
0726.720000,5997123.197535,1040726.720000,6454399.952465,294245.130000,6454399.952465)),'mask=ANYINTERACT
querytype=window')='TRUE')

Our interpretation is that Oracle creates an array of 49.000.000 bits and assign 248 1 bits from range scan of tab2_FK_SAGSPAKKE_IDX. Probably fast.
Then Oracle creates yet an array of 49.000.000 bits and now assign 1 to all entries by scanning the geo index tab2_GEOMETRI_RT. Probably slow causing the 1 mio consistent gets in autotrace measurements.
Finally Oracle performe an "and" operation between the 2 bit arrays. Probably fast and then conversion to rowid for retrieval.

We think this bitmap join is a replacement for not being able to add more than one column in geo indexes, see http://docs.oracle.com/database/121/SPATL/GUID-BB6D7C9E-277B-4F49-BDD4-539CFE88D12C.htm#SPATL1019

But in our case we actually do not want bitmap join as sagsid is an acceptable where condition.

We have not been able to get rid the bitmap join.

We have been through docs at https://docs.oracle.com/database/121/TGSQL/tgsql_statscon.htm#TGSQL336 and https://docs.oracle.com/database/121/TGSQL/tgsql_histo.htm#TGSQL366
and found that generation of histograms in small test examples transforme the bitmap conversion to filter predicate for geo filter with dramatic reduction in consistent gets.
But we are not sure if we should pursue this strategy as executions seems to be very sensitive to content of histograms.

We have also consulted https://docs.oracle.com/database/121/TGSQL/tgsql_influence.htm#TGSQL246 and https://docs.oracle.com/database/121/SQLRF/sql_elements006.htm#SQLRF51107
but found no obvious way to avoid bitmap conversion by adding hints.

We also known there is a SQL Plan Management way and a SQL Profile way to pursue, but we have not digging into this yet.


Our Oracle version is:

select banner from v$version
BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
PL/SQL Release 12.1.0.1.0 - Production
CORE 12.1.0.1.0 Production
TNS for Linux: Version 12.1.0.1.0 - Production
NLSRTL Version 12.1.0.1.0 - Production


Best Regards

Erik

and Chris said...

With the bitmap conversions, Oracle searches the relevant indexes. It then converts the rowids it finds to bitmaps. Then combines them.

So in your case it only finds the entries in the indexes that match

- "MS"."FK_SAGSPAKKE_ID"="S"."FK_SAGSPAKKE_ID"
AND
- access("MDSYS"."SDO_RELATE"("MS"."GEOMETRI","MDSYS"."SDO_GEOMETRY"(2003,25832,NULL,"MDSYS"."SDO_ELEM_INFO_ARRAY"(1,1003,1),"MDSYS"."SDO_ORDINATE_ARRAY"(294245.130000,6454399.952465,294245.130000,5997123.197535,1040726.720000,
5997123.197535,1040726.720000,6454399.952465,294245.130000,6454399.952465)),
'mask=ANYINTERACT querytype=window')='TRUE')

You've said that "MS"."FK_SAGSPAKKE_ID"="S"."FK_SAGSPAKKE_ID" returns 248 rows. So Oracle only considers 248 entries.

Oracle thinks there's only 1682 rows that match the spatial condition. Given your query runs for several minutes, this is almost certainly an underestimate.

You could prevent Oracle using this index with the no_index hint. e.g.:

/*+ NO_INDEX(table2 tab2_GEOMETRI_RT) */


If this fixes the problem, you could capture the plan as a baseline. This will lock the query to this plan. For details on how to do this, read:

https://blogs.oracle.com/optimizer/entry/how_do_i_deal_with_a_third_party_application_that_has_embedded_hints_that_result_in_a_sub-optimal_ex

If you need further help, please post the execution plan, not the explain plan. This shows the actual number of rows processed at each step. For details on how to do this, read:

https://blogs.oracle.com/sql/entry/how_to_create_an_execution

Show us the output here. And ensure you use the code tags when you do!

Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Hint NO_INDEX is ignored by optimizer when adding more joins

Erik Andersen, October 13, 2016 - 12:25 pm UTC

Hi Chris

Thank you for your answer. I have looked deep into it.

The SQL in the original question is actually not our real problem, but a narrow down to demonstrate the issue.

Our real issue is the 2 views below:

CREATE OR REPLACE FORCE EDITIONABLE VIEW "EAN"."table_1_BASIS" ("ME_ID", "GEOMETRI", fk_sagspakke_id) AS
  SELECT /*+ QB_NAME(mat_basis) */
          ms.id me_id,
          ms.geometri,
          ms.fk_sagspakke_id
   FROM user_1.table_1 ms
   WHERE  ms.enum_variant IN (1, 2);

CREATE OR REPLACE FORCE EDITIONABLE VIEW "EAN"."table_1_SAG" ("ME_ID", "GEOMETRI", "JOURNALNR", "SAGSID") AS
  SELECT /*+ NO_INDEX(@mat_basis ms table_1_GEOMETRI_RT) */
       ms.me_id,
       ms.geometri,
       s.journal journalnr,
       s.sagsid sagsid
FROM user_1.sag s
JOIN ean.table_1_basis ms ON ms.fk_sagspakke_id = s.fk_sagspakke_id;


In the 2 views I have implemented promoting of index prevention from outer view to inner view. It works fine.
After view creation running this SQL:

SELECT *
FROM ean.table_1_SAG A
WHERE
  SAGSID = 100042140
  AND (MDSYS.SDO_RELATE(A.GEOMETRI, MDSYS.SDO_GEOMETRY(2003, 25832, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1), MDSYS.SDO_ORDINATE_ARRAY(
294245.130000,  6454399.952465,
294245.130000,  5997123.197535,
1040726.720000, 5997123.197535,
1040726.720000, 6454399.952465,
294245.130000,  6454399.952465)), 'mask=ANYINTERACT querytype=window') = 'TRUE');


using SQLPlus commands:

set autotrace off
SET SERVEROUTPUT off
set line 200
set echo off
set term off

spool trace.log

alter session set statistics_level = all;

timing start

SELECT *
FROM ean.table_1_SAG A
WHERE
  SAGSID = 100042140
  AND (MDSYS.SDO_RELATE(A.GEOMETRI, MDSYS.SDO_GEOMETRY(2003, 25832, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1), MDSYS.SDO_ORDINATE_ARRAY(
294245.130000,  6454399.952465,
294245.130000,  5997123.197535,
1040726.720000, 5997123.197535,
1040726.720000, 6454399.952465,
294245.130000,  6454399.952465)), 'mask=ANYINTERACT querytype=window') = 'TRUE');

set term on

select * from table(dbms_xplan.display_cursor);

timing stop

spool off


I get this execution plan:

SQL> @exe_plan.sql

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
SQL_ID  dknbnjdfkgrqm, child number 4
-------------------------------------
SELECT * FROM ean.table_1_SAG A WHERE   SAGSID = 100042140   AND
(MDSYS.SDO_RELATE(A.GEOMETRI, MDSYS.SDO_GEOMETRY(2003, 25832, NULL,
MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1), MDSYS.SDO_ORDINATE_ARRAY(
294245.130000,  6454399.952465, 294245.130000,  5997123.197535,
1040726.720000, 5997123.197535, 1040726.720000, 6454399.952465,
294245.130000,  6454399.952465)), 'mask=ANYINTERACT querytype=window')
= 'TRUE')

Plan hash value: 1249522991

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                          | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                               |       |       |  3520 (100)|          |
|   1 |  NESTED LOOPS                        |                               |   248 | 46128 |  3520  (98)| 00:00:01 |
|*  2 |   TABLE ACCESS BY INDEX ROWID        | SAG                           |     1 |    22 |     3   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN                 | SYS_C006847                   |     1 |       |     2   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS BY INDEX ROWID BATCHED| table_1                       |   248 | 40672 |  3517  (98)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN                  | table_1_FK_SAGSPAKKE_IDX      |  1682 |       |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("S"."FK_SAGSPAKKE_ID" IS NOT NULL)
   3 - access("S"."SAGSID"=100042140)
   4 - filter((INTERNAL_FUNCTION("MS"."ENUM_VARIANT") AND
              "MDSYS"."SDO_RELATE"("MS"."GEOMETRI","MDSYS"."SDO_GEOMETRY"(2003,25832,NULL,"MDSYS"."SDO_ELEM_INFO_ARRAY"(1,10
              03,1),"MDSYS"."SDO_ORDINATE_ARRAY"(294245.13,6454399.952465,294245.13,5997123.197535,1040726.72,5997123.197535
              ,1040726.72,6454399.952465,294245.13,6454399.952465)),'mask=ANYINTERACT querytype=window')='TRUE'))
   5 - access("MS"."FK_SAGSPAKKE_ID"="S"."FK_SAGSPAKKE_ID")

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
       filter("MS"."FK_SAGSPAKKE_ID" IS NOT NULL)

Note
-----
   - statistics feedback used for this statement


39 rows selected.
Elapsed: 00:00:02.65


Until now everything is ok.

The 2 views actually need more joins to fulfill business requirements. I therefore add yet another join and recreate views:

CREATE OR REPLACE FORCE EDITIONABLE VIEW "EAN"."table_1_BASIS" ("ME_ID", "GEOMETRI", fk_sagspakke_id) AS
  SELECT /*+ QB_NAME(mat_basis) USE_NL(ms sp1) */
          ms.id me_id,
          ms.geometri,
          ms.fk_sagspakke_id
   FROM user_1.table_1 ms
   inner JOIN user_1.table_2 sp1 on sp1.id = ms.fk_frapunkt_id
   WHERE  ms.enum_variant IN (1, 2);

CREATE OR REPLACE FORCE EDITIONABLE VIEW "EAN"."table_1_SAG" ("ME_ID", "GEOMETRI", "JOURNALNR", "SAGSID") AS
  SELECT /*+ NO_INDEX(@mat_basis ms table_1_GEOMETRI_RT) */
       ms.me_id,
       ms.geometri,
       s.journal journalnr,
       s.sagsid sagsid
FROM user_1.sag s
JOIN ean.table_1_basis ms ON ms.fk_sagspakke_id = s.fk_sagspakke_id;


I now execute test SQL again:

SELECT *
FROM ean.table_1_SAG A
WHERE
  SAGSID = 100042140
  AND (MDSYS.SDO_RELATE(A.GEOMETRI, MDSYS.SDO_GEOMETRY(2003, 25832, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1), MDSYS.SDO_ORDINATE_ARRAY(
294245.130000,  6454399.952465,
294245.130000,  5997123.197535,
1040726.720000, 5997123.197535,
1040726.720000, 6454399.952465,
294245.130000,  6454399.952465)), 'mask=ANYINTERACT querytype=window') = 'TRUE');


Test SQL now runs for 4,5 minute.

Then I execute select * from table(dbms_xplan.display_cursor) saying:

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
--------------------------------------------------
SQL_ID  dknbnjdfkgrqm, child number 5
-------------------------------------
SELECT * FROM ean.table_1_SAG A WHERE   SAGSID = 100042140   AND
(MDSYS.SDO_RELATE(A.GEOMETRI, MDSYS.SDO_GEOMETRY(2003, 25832, NULL,
MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1), MDSYS.SDO_ORDINATE_ARRAY(
294245.130000,  6454399.952465, 294245.130000,  5997123.197535,
1040726.720000, 5997123.197535, 1040726.720000, 6454399.952465,
294245.130000,  6454399.952465)), 'mask=ANYINTERACT querytype=window')
= 'TRUE')

Plan hash value: 2222986838

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                          | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                               |       |       |    21 (100)|          |
|   1 |  NESTED LOOPS                        |                               |   248 | 46128 |    21   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS BY INDEX ROWID        | SAG                           |     1 |    22 |     3   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN                 | SYS_C006847                   |     1 |       |     2   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS BY INDEX ROWID BATCHED| table_1                       |   248 | 40672 |    21   (0)| 00:00:01 |
|   5 |    BITMAP CONVERSION TO ROWIDS       |                               |       |       |         |             |
|   6 |     BITMAP AND                       |                               |       |       |         |             |
|   7 |      BITMAP CONVERSION FROM ROWIDS   |                               |       |       |         |             |
|*  8 |       INDEX RANGE SCAN               | table_1_FK_SAGSPAKKE_IDX      |  1682 |       |     3   (0)| 00:00:01 |
|   9 |      BITMAP CONVERSION FROM ROWIDS   |                               |       |       |         |             |
|  10 |       SORT ORDER BY                  |                               |       |       |         |             |
|  11 |        DOMAIN INDEX (SEL: 0.100000 %)| table_1_GEOMETRI_RT           |  1682 |       |     6   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("S"."FK_SAGSPAKKE_ID" IS NOT NULL)

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------
   3 - access("S"."SAGSID"=100042140)
   4 - filter((INTERNAL_FUNCTION("MS"."ENUM_VARIANT") AND "MS"."FK_FRAPUNKT_ID" IS NOT NULL))
   8 - access("MS"."FK_SAGSPAKKE_ID"="S"."FK_SAGSPAKKE_ID")
       filter("MS"."FK_SAGSPAKKE_ID" IS NOT NULL)

Note
-----
   - statistics feedback used for this statement


At this step I think it is strange to use 4,5 minute to handle less than 2000 rows in above execution plan.

I therefore trace test SQL and get


Statistics
----------------------------------------------------------
    3277528  recursive calls
         21  db block gets
    2187268  consistent gets
     550994  physical reads
          0  redo size
      43509  bytes sent via SQL*Net to client
       1046  bytes received via SQL*Net from client
         25  SQL*Net roundtrips to/from client
          1  sorts (memory)
          1  sorts (disk)
        248  rows processed

Elapsed: 00:04:34.27


And this explains it for me: SQL is executed in 4,5 minute due to 2.2 mio buffer gets (0.5 mio being even physical gets)
My question is now: how come these 2.2 mio buffer gets do not show up in execution plan extracted by select * from table(dbms_xplan.display_cursor) above?

Anyway, I try execute dbms_xplan.display_cursor based on SQLId as suggested in your guide:

select * from table(dbms_xplan.display_cursor('dknbnjdfkgrqm', null, 'ALLSTATS LAST'));

and I get


SQL_ID  dknbnjdfkgrqm, child number 5
-------------------------------------
SELECT * FROM ean.table_1_SAG A WHERE   SAGSID = 100042140   AND
(MDSYS.SDO_RELATE(A.GEOMETRI, MDSYS.SDO_GEOMETRY(2003, 25832, NULL,
MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1), MDSYS.SDO_ORDINATE_ARRAY(
294245.130000,  6454399.952465, 294245.130000,  5997123.197535,
1040726.720000, 5997123.197535, 1040726.720000, 6454399.952465,
294245.130000,  6454399.952465)), 'mask=ANYINTERACT querytype=window')
= 'TRUE')

Plan hash value: 2222986838

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                          | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                               |      1 |        |    248 |00:04:31.35 |    2187K|    550K|    193K|       |       |          |         |
|   1 |  NESTED LOOPS                        |                               |      1 |    248 |    248 |00:04:31.35 |    2187K|    550K|    193K|       |       |          |         |
|*  2 |   TABLE ACCESS BY INDEX ROWID        | SAG                           |      1 |      1 |      1 |00:00:00.01 |       4 |      0 |      0 |       |       |          |         |
|*  3 |    INDEX UNIQUE SCAN                 | SYS_C006847                   |      1 |      1 |      1 |00:00:00.01 |       3 |      0 |      0 |       |       |          |         |
|*  4 |   TABLE ACCESS BY INDEX ROWID BATCHED| table_1                       |      1 |    248 |    248 |00:04:31.35 |    2187K|    550K|    193K|       |       |          |         |
|   5 |    BITMAP CONVERSION TO ROWIDS       |                               |      1 |        |    248 |00:04:31.35 |    2187K|    550K|    193K|       |       |          |         |
|   6 |     BITMAP AND                       |                               |      1 |        |      1 |00:04:31.35 |    2187K|    550K|    193K|       |       |          |         |
|   7 |      BITMAP CONVERSION FROM ROWIDS   |                               |      1 |        |      1 |00:00:00.01 |       4 |      0 |      0 |       |       |          |         |
|*  8 |       INDEX RANGE SCAN               | table_1_FK_SAGSPAKKE_IDX      |      1 |   1682 |    248 |00:00:00.01 |       4 |      0 |      0 |       |       |          |         |
|   9 |      BITMAP CONVERSION FROM ROWIDS   |                               |      1 |        |      1 |00:04:31.35 |    2187K|    550K|    193K|       |       |          |         |
|  10 |       SORT ORDER BY                  |                               |      1 |        |   8029K|00:04:30.42 |    2187K|    550K|    193K|   848M|  9525K|  100M (1)|     761K|
|  11 |        DOMAIN INDEX (SEL: 0.100000 %)| table_1_GEOMETRI_RT           |      1 |   1682 |     49M|00:03:41.37 |    2187K|    438K|      0 |       |       |          |         |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("S"."FK_SAGSPAKKE_ID" IS NOT NULL)
   3 - access("S"."SAGSID"=100042140)
   4 - filter((INTERNAL_FUNCTION("MS"."ENUM_VARIANT") AND "MS"."FK_FRAPUNKT_ID" IS NOT NULL))
   8 - access("MS"."FK_SAGSPAKKE_ID"="S"."FK_SAGSPAKKE_ID")
       filter("MS"."FK_SAGSPAKKE_ID" IS NOT NULL)


Note
-----
   - statistics feedback used for this statement


This output says that 49mio rows are visited in line 11 in geo index compared to expected visit of 1682 rows.
Even though we advice SQL not to use index table_1_GEOMETRI_RT, it now use it.

How do we prevent SQL using geo index in bitmap conversions?

Best Regards

Erik


Chris Saxon
October 13, 2016 - 2:42 pm UTC

how come these 2.2 mio buffer gets do not show up in execution plan extracted by select * from table(dbms_xplan.display_cursor)

Because it's not an execution plan! It's an explain plan. Which is more-or-less a guess.

Basically Oracle is expecting the index scan to return just 1682. But it's really returning everything (or close enough)!

So, first thing to check - are the stats on this index up-to-date? If you gather them again, does it make any difference?

Second thing to ponder. Given that:

MDSYS.SDO_RELATE(A.GEOMETRI, MDSYS.SDO_GEOMETRY(2003, 25832, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1), MDSYS.SDO_ORDINATE_ARRAY(
294245.130000,  6454399.952465,
294245.130000,  5997123.197535,
1040726.720000, 5997123.197535,
1040726.720000, 6454399.952465,
294245.130000,  6454399.952465)), 'mask=ANYINTERACT querytype=window') = 'TRUE'


Returns all the rows, is it even worth having this index? Are there any queries you'll run where this will return just a few rows?

If the answer's no, you could avoid the problem by dropping it. Adn save yourself its storage and DML overheads!

Unable to collect statistics for spatial index

Erik Andersen, October 27, 2016 - 2:35 pm UTC

Hi Chris

Thank you for your answer.

Using a reduced data set (2.000.000 rows) we have realized that generating index statistics transform spatial condition from an access predicate condition to a filter predicate condition.
And this decreases response time dramatically so we are happy about it.

Our next issue is that we are unable to generate index statistics for full data set of 49.000.000 rows.

We use this SQL to generate index statistics on the reduced data set
begin 
  DBMS_STATS.GATHER_INDEX_STATS (ownname => 'TEST_USER_A', indname => 'GEOMETRI_IDX', estimate_percent => 100, force => TRUE);
end;


If we adjust estimate percent to 1 (see following SQL) we observe the same response time for gathering of index statistic.
begin 
  DBMS_STATS.GATHER_INDEX_STATS (ownname => 'TEST_USER_A', indname => 'GEOMETRI_IDX', estimate_percent => 1, force => TRUE);
end;


How come that decreasing estimate_percent from 100 to 1 does reflect reduced response time for gathering of index statistic?

Our measurement of response time for gathering index statistics on different reduced data sets show these result:
Gathering index statistics for table with 500.000 rows take 12 secs
Gathering index statistics for table with 2.000.000 rows takes 172 secs
Gathering index statistics for table with 4.000.000 rows takes 688 secs

This list indicate a second order dependency on row size. If this is a right assumption then generating index on full data set will take:

49/4*49/4*688 secs = ~100.000 secs (more than 24 hour)


We are not even able to run gathering of index statistics on full data set. When running this

begin 
  DBMS_STATS.GATHER_INDEX_STATS (ownname => 'REAL_USER', indname => 'GEOMETRI_IDX', estimate_percent => 0.1, force => TRUE);
end;


on full data set (49.000.000 rows) it runs for ~3 hours and then fails with incident report saying PGA memory exceeded:

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
ORACLE_HOME = /oracle/app/oracle/product/12.1.0/dbhome_1
System name: Linux
Node name: dkdst405ora614
Release: 2.6.32-642.6.1.el6.x86_64
Version: #1 SMP Thu Aug 25 12:42:19 EDT 2016
Machine: x86_64
Instance name: MM12
Redo thread mounted by this instance: 1
Oracle process number: 66
Unix process pid: 10428, image: oracle@dkdst405ora614


*** 2016-10-26 19:59:20.095
*** SESSION ID:(253.10551) 2016-10-26 19:59:20.095
*** CLIENT ID:() 2016-10-26 19:59:20.095
*** SERVICE NAME:(MM12) 2016-10-26 19:59:20.095
*** MODULE NAME:(SQL*Plus) 2016-10-26 19:59:20.095
*** ACTION NAME:() 2016-10-26 19:59:20.095

Dump continued from file: /oracle/app/oracle/diag/rdbms/mm12/MM12/trace/MM12_ora_10428.trc
ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT
ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT


The value of pga_aggregate_limit is defined to 2G, see
SQL> show parameter PGA

NAME TYPE VALUE
------------------------------------ ----------- ----------
pga_aggregate_limit big integer 2G
pga_aggregate_target big integer 980M
SQL>


Our questions are:
1. How do we control the extent/duration of gathering of index statistics for spatial index, as value of estimate_percent seems to be ignored?
2. How do we solve the PGA limit exceeded? Isn't 2G enough memory for the collection task? We could increase value of pga_aggregate_limit but concurrent users and the fact that machine only has 10Gb physical memory may result in swapping.

Chris Saxon
October 28, 2016 - 2:50 pm UTC

Have you tried using DBMS_STATS.AUTO_SAMPLE_SIZE for the estimate percent?

If this doesn't help I think you're going to have to take this up with support.

Link to solution

A reader, November 23, 2016 - 9:07 am UTC

Hi

Thanks for your help.

This update is just for your information. It seems that the issue is solved by SR 3-13577973581 at Oracle Support, see

https://support.oracle.com/epmos/faces/SrDetail?_afrLoop=357113304979466&srNumber=3-13577973581&srProdClass=Database&_afrWindowMode=0&_adf.ctrl-state=pwuvagmin_4

Best Regard

Erik
Connor McDonald
November 24, 2016 - 4:08 am UTC

Thanks for getting back to us.

Solution requested

Frank Reimann, February 01, 2019 - 10:44 am UTC

Hello Erik,
hello Chris,
do You see a way to share the solution (SR) with us. We're faced with a similar situation related to "bitmap conversion from/to rowids" and spatial oeprations (sdo_relate)?
It would be very helpfully

Kind regards
Frank
Chris Saxon
February 04, 2019 - 1:03 pm UTC

Sorry, I don't have access to see SRs. We'll have to hope that Erik is able to share the details of bug/patch numbers.

get rid of bitmap joins

Hendrik, July 03, 2023 - 11:25 am UTC

you can get rid of bitmap joins by using the hidden parameter
ALTER Session SET _b_tree_bitmap_plans=FALSE
Chris Saxon
July 03, 2023 - 1:39 pm UTC

True, though you should avoid setting underscore parameters; generally this is something you should only do under direction of Oracle support. It's best to do this at the query level via hints if necessary.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.