Home » RDBMS Server » Performance Tuning » Query requires tuning (Oracle 11gr2, sun)
| Query requires tuning [message #623716] |
Fri, 12 September 2014 22:59  |
AviatoR
Messages: 43
Registered: February 2009
|
Member |
|
|
Hello DBA's,
I face a performance issue with this query, It is consuming too much of logical reads(buffer gets) which is a pain to the database. This is one of the query which causing grief. Likewise I have three different autonomous queries.
SELECT VMP.PROPERTY_VIEW.SHAPE,
VMP.PROPERTY.PFI,
VMP.PROPERTY.LGA_CODE,
VMP.PROPERTY.PROPNUM,
VMP.PROPERTY.PROPERTY_TYPE,
VMP.PROPERTY.MULTI_ASSESSMENT,
VMP.PROPERTY.STATUS,
VMP.PROPERTY.PFI_CREATED,
VMP.PROPERTY.UFI,
VMP.PROPERTY.UFI_CREATED,
VMP.PROPERTY.UFI_OLD,
VMP.PROPERTY_VIEW.PFI,
VMP.PROPERTY_VIEW.BASE_PFI,
VMP.PROPERTY_VIEW.CENTROID_PFI,
VMP.PROPERTY_VIEW.GRAPHIC_TYPE,
VMP.PROPERTY_VIEW.Z_LEVEL,
VMP.PROPERTY_VIEW.PFI_CREATED,
VMP.PROPERTY_VIEW.UFI,
VMP.PROPERTY_VIEW.UFI_CREATED,
VMP.PROPERTY_VIEW.UFI_OLD,
VMA.ADDRESS.PFI,
VMA.ADDRESS.PROPERTY_PFI,
VMA.ADDRESS.EZI_ADDRESS,
VMA.ADDRESS.SOURCE,
VMA.ADDRESS.SOURCE_VERIFIED,
VMA.ADDRESS.IS_PRIMARY,
VMA.ADDRESS.GEOCODE_FEATURE,
VMA.ADDRESS.DISTANCE_RELATED_FLAG,
VMA.ADDRESS.LOCATION_DESCRIPTOR,
VMA.ADDRESS.BLG_UNIT_TYPE,
VMA.ADDRESS.BLG_UNIT_PREFIX_1,
VMA.ADDRESS.BLG_UNIT_ID_1,
VMA.ADDRESS.BLG_UNIT_SUFFIX_1,
VMA.ADDRESS.BLG_UNIT_PREFIX_2,
VMA.ADDRESS.BLG_UNIT_ID_2,
VMA.ADDRESS.BLG_UNIT_SUFFIX_2,
VMA.ADDRESS.FLOOR_TYPE,
VMA.ADDRESS.FLOOR_PREFIX_1,
VMA.ADDRESS.FLOOR_NO_1,
VMA.ADDRESS.FLOOR_SUFFIX_1,
VMA.ADDRESS.FLOOR_PREFIX_2,
VMA.ADDRESS.FLOOR_NO_2,
VMA.ADDRESS.FLOOR_SUFFIX_2,
VMA.ADDRESS.BUILDING_NAME,
VMA.ADDRESS.COMPLEX_NAME,
VMA.ADDRESS.HOUSE_PREFIX_1,
VMA.ADDRESS.HOUSE_NUMBER_1,
VMA.ADDRESS.HOUSE_SUFFIX_1,
VMA.ADDRESS.HOUSE_PREFIX_2,
VMA.ADDRESS.HOUSE_NUMBER_2,
VMA.ADDRESS.HOUSE_SUFFIX_2,
VMA.ADDRESS.DISP_PREFIX_1,
VMA.ADDRESS.DISP_NUMBER_1,
VMA.ADDRESS.DISP_SUFFIX_1,
VMA.ADDRESS.DISP_PREFIX_2,
VMA.ADDRESS.DISP_NUMBER_2,
VMA.ADDRESS.DISP_SUFFIX_2,
VMA.ADDRESS.ROAD_NAME,
VMA.ADDRESS.ROAD_TYPE,
VMA.ADDRESS.ROAD_SUFFIX,
VMA.ADDRESS.LOCALITY_NAME,
VMA.ADDRESS.STATE,
VMA.ADDRESS.POSTCODE,
VMA.ADDRESS.MESH_BLOCK,
VMA.ADDRESS.NUM_ROAD_ADDRESS,
VMA.ADDRESS.NUM_ADDRESS,
VMA.ADDRESS.ADDRESS_CLASS,
VMA.ADDRESS.OUTSIDE_PROPERTY,
VMA.ADDRESS.LABEL_ADDRESS,
VMA.ADDRESS.FEATURE_QUALITY_ID,
VMA.ADDRESS.PFI_CREATED,
VMA.ADDRESS.UFI_CREATED,
VMA.ADDRESS.UFI_OLD,
VMA.ADDRESS.OBJECTID
FROM VMA.ADDRESS,
VMP.PROPERTY_VIEW,
VMP.PROPERTY
WHERE VMP.PROPERTY_VIEW.PFI = VMP.PROPERTY.VIEW_PFI
AND VMP.PROPERTY.PFI = VMA.ADDRESS.PROPERTY_PFI
Explain Plan:
Execution Plan
----------------------------------------------------------
Plan hash value: 3966211374
--------------------------------------------------------------------------------
-------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU
)| Time |
--------------------------------------------------------------------------------
-------------
| 0 | SELECT STATEMENT | | 3360K| 1140M| | 123K (1
)| 00:28:54 |
|* 1 | HASH JOIN | | 3360K| 1140M| 427M| 123K (1
)| 00:28:54 |
|* 2 | HASH JOIN | | 3348K| 389M| 210M| 48730 (1
)| 00:11:23 |
| 3 | TABLE ACCESS FULL| PROPERTY_VIEW | 2835K| 178M| | 24360 (1
)| 00:05:42 |
| 4 | TABLE ACCESS FULL| PROPERTY | 3397K| 181M| | 5888 (2
)| 00:01:23 |
| 5 | TABLE ACCESS FULL | ADDRESS | 3409K| 760M| | 22598 (2
)| 00:05:17 |
--------------------------------------------------------------------------------
-------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("PROPERTY"."PFI"="ADDRESS"."PROPERTY_PFI")
2 - access("PROPERTY_VIEW"."PFI"="PROPERTY"."VIEW_PFI")
Statistics
----------------------------------------------------------
3636 recursive calls
0 db block gets
219897 consistent gets
360805 physical reads
0 redo size
1932445553 bytes sent via SQL*Net to client
2504993 bytes received via SQL*Net from client
227327 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3409468 rows processed
|
|
|
|
|
|
|
|
| Re: Query requires tuning [message #623722 is a reply to message #623718] |
Sat, 13 September 2014 02:01  |
AviatoR
Messages: 43
Registered: February 2009
|
Member |
|
|
The below is the view that is being used by application to access the above posted query.
SELECT /*+ FIRST_ROWS (1000)*/
SHAPE,
ADD_HOUSE_NUMBER_1 || ADD_HOUSE_SUFFIX_1 AS HOUSE_NUMBER_1,
ADD_HOUSE_SUFFIX_1 AS HOUSE_SUFFIX_1,
ADD_HOUSE_NUMBER_2 AS HOUSE_NUMBER_2,
ADD_HOUSE_SUFFIX_2 AS HOUSE_SUFFIX_2
FROM VMP.V_PROPERTY_MP_ADDRESS
WHERE (ADD_BLG_UNIT_ID_1 IS NULL
OR ADD_BLG_UNIT_ID_1 = 1)
AND PROP_STATUS = 'A';
Execution plan:
Execution Plan
----------------------------------------------------------
Plan hash value: 3757197664
--------------------------------------------------------------------------------
--------------------
| Id | Operation | Name | Rows | Bytes | Cos
t (%CPU)| Time |
--------------------------------------------------------------------------------
--------------------
| 0 | SELECT STATEMENT | | 1003 | 103K| 40
10 (1)| 00:00:57 |
| 1 | NESTED LOOPS | | | |
| |
| 2 | NESTED LOOPS | | 1003 | 103K| 40
10 (1)| 00:00:57 |
| 3 | NESTED LOOPS | | 1003 | 40120 | 20
17 (1)| 00:00:29 |
|* 4 | TABLE ACCESS FULL | ADDRESS | 2717K| 51M|
10 (0)| 00:00:01 |
|* 5 | TABLE ACCESS BY INDEX ROWID| PROPERTY | 1 | 20 |
2 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | PROPERTY_UN1 | 1 | |
1 (0)| 00:00:01 |
|* 7 | INDEX UNIQUE SCAN | PROPERTY_VIEW_UN1 | 1 | |
1 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID | PROPERTY_VIEW | 1 | 66 |
2 (0)| 00:00:01 |
--------------------------------------------------------------------------------
--------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("ADDRESS"."BLG_UNIT_ID_1" IS NULL OR "ADDRESS"."BLG_UNIT_ID_1"=1)
5 - filter("PROPERTY"."STATUS"='A')
6 - access("PROPERTY"."PFI"="ADDRESS"."PROPERTY_PFI")
7 - access("PROPERTY_VIEW"."PFI"="PROPERTY"."VIEW_PFI")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
17032549 consistent gets
1548119 physical reads
0 redo size
943642947 bytes sent via SQL*Net to client
2009408 bytes received via SQL*Net from client
182543 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2738021 rows processed
And yes, it has got indexes on the column that is used in where clause.
DDL :-
Property_view Table:
CREATE TABLE VMP.PROPERTY_VIEW
(
PFI VARCHAR2(10 BYTE) NOT NULL
, BASE_PFI VARCHAR2(10 BYTE)
, CENTROID_PFI VARCHAR2(10 BYTE)
, STATUS VARCHAR2(1 BYTE)
, GRAPHIC_TYPE VARCHAR2(1 BYTE)
, Z_LEVEL VARCHAR2(2 BYTE)
, PFI_CREATED DATE
, UFI NUMBER(10, 0) NOT NULL
, UFI_CREATED DATE
, UFI_OLD NUMBER(10, 0)
, OBJECTID NUMBER(*, 0) NOT NULL
, SHAPE MDSYS.SDO_GEOMETRY
, CONSTRAINT PROPERTY_VIEW_UN1 PRIMARY KEY
(
PFI
)
ENABLE
)
NOLOGGING
TABLESPACE VMPDATA
PCTFREE 10
INITRANS 1
STORAGE
(
INITIAL 1048576
NEXT 1048576
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOCOMPRESSCREATE INDEX VMP.PROPERTY_VIEW_FK1 ON VMP.PROPERTY_VIEW (BASE_PFI ASC)
NOLOGGING
TABLESPACE VMPINDEX
PCTFREE 5
INITRANS 2
STORAGE
(
INITIAL 1048576
NEXT 1048576
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL
CREATE INDEX VMP.PROPERTY_VIEW_FK2 ON VMP.PROPERTY_VIEW (CENTROID_PFI ASC)
NOLOGGING
TABLESPACE VMPINDEX
PCTFREE 5
INITRANS 2
STORAGE
(
INITIAL 1048576
NEXT 1048576
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL
CREATE INDEX VMP.PROPERTY_VIEW_NU_GRAPHIC_TYPE ON VMP.PROPERTY_VIEW (GRAPHIC_TYPE ASC)
NOLOGGING
TABLESPACE VMPINDEX
PCTFREE 5
INITRANS 2
STORAGE
(
INITIAL 1048576
NEXT 1048576
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL
CREATE UNIQUE INDEX VMP.PROPERTY_VIEW_PK ON VMP.PROPERTY_VIEW (UFI ASC)
NOLOGGING
TABLESPACE VMPINDEX
PCTFREE 10
INITRANS 2
STORAGE
(
INITIAL 1048576
NEXT 1048576
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL
CREATE INDEX VMP.PROPERTY_VIEW_SD ON VMP.PROPERTY_VIEW (SHAPE) INDEXTYPE IS MDSYS.SPATIAL_INDEX
PARAMETERS('tablespace=VMPINDEX work_tablespace=SDO_WORK layer_gtype=MULTIPOLYGON')
NOPARALLEL
CREATE UNIQUE INDEX VMP.PROPERTY_VIEW_UN1 ON VMP.PROPERTY_VIEW (PFI ASC)
NOLOGGING
TABLESPACE VMPINDEX
PCTFREE 10
INITRANS 2
STORAGE
(
INITIAL 1048576
NEXT 1048576
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLELALTER TABLE VMP.PROPERTY_VIEW
ADD CONSTRAINT PROPERTY_VIEW_PK UNIQUE
(
UFI
)
ENABLE
Property Table:
CREATE TABLE VMP.PROPERTY
(
PFI VARCHAR2(10 BYTE)
, VIEW_PFI VARCHAR2(10 BYTE)
, STATUS VARCHAR2(1 BYTE)
, LGA_CODE VARCHAR2(3 BYTE)
, PROPNUM VARCHAR2(20 BYTE)
, PROPERTY_TYPE VARCHAR2(1 BYTE)
, MULTI_ASSESSMENT VARCHAR2(1 BYTE)
, PFI_CREATED DATE
, UFI NUMBER(10, 0) NOT NULL
, UFI_CREATED DATE
, UFI_OLD NUMBER(10, 0)
, OBJECTID NUMBER(*, 0) NOT NULL
, CONSTRAINT PROPERTY_PK PRIMARY KEY
(
UFI
)
ENABLE
)
NOLOGGING
TABLESPACE VMPDATA
PCTFREE 10
INITRANS 1
STORAGE
(
INITIAL 1048576
NEXT 1048576
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOCOMPRESSCREATE INDEX VMP.PROPERTY_FK1 ON VMP.PROPERTY (VIEW_PFI ASC)
NOLOGGING
TABLESPACE VMPINDEX
PCTFREE 5
INITRANS 2
STORAGE
(
INITIAL 1048576
NEXT 1048576
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL
CREATE INDEX VMP.PROPERTY_FK5 ON VMP.PROPERTY (STATUS ASC)
NOLOGGING
TABLESPACE VMPINDEX
PCTFREE 5
INITRANS 2
STORAGE
(
INITIAL 1048576
NEXT 1048576
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL
CREATE INDEX VMP.PROPERTY_FK6 ON VMP.PROPERTY (LGA_CODE ASC)
NOLOGGING
TABLESPACE VMPINDEX
PCTFREE 5
INITRANS 2
STORAGE
(
INITIAL 1048576
NEXT 1048576
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL
CREATE UNIQUE INDEX VMP.PROPERTY_PK ON VMP.PROPERTY (UFI ASC)
NOLOGGING
TABLESPACE VMPINDEX
PCTFREE 10
INITRANS 2
STORAGE
(
INITIAL 1048576
NEXT 1048576
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL
CREATE INDEX VMP.PROPERTY_PROPNUM ON VMP.PROPERTY (PROPNUM ASC)
NOLOGGING
TABLESPACE VMPINDEX
PCTFREE 5
INITRANS 2
STORAGE
(
INITIAL 1048576
NEXT 1048576
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL
CREATE UNIQUE INDEX VMP.PROPERTY_UN1 ON VMP.PROPERTY (PFI ASC)
NOLOGGING
TABLESPACE VMPINDEX
PCTFREE 10
INITRANS 2
STORAGE
(
INITIAL 1048576
NEXT 1048576
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLELALTER TABLE VMP.PROPERTY
ADD CONSTRAINT PROPERTY_UN1 UNIQUE
(
PFI
)
ENABLE
Address Table:
CREATE TABLE VMA.ADDRESS
(
PFI VARCHAR2(10 BYTE)
, PROPERTY_PFI VARCHAR2(10 BYTE)
, EZI_ADDRESS VARCHAR2(80 BYTE)
, SOURCE VARCHAR2(3 BYTE)
, SOURCE_VERIFIED DATE
, IS_PRIMARY VARCHAR2(1 BYTE)
, PROPERTY_STATUS VARCHAR2(1 BYTE)
, GEOCODE_FEATURE VARCHAR2(1 BYTE)
, DISTANCE_RELATED_FLAG VARCHAR2(1 BYTE)
, LOCATION_DESCRIPTOR VARCHAR2(45 BYTE)
, BLG_UNIT_TYPE VARCHAR2(4 BYTE)
, HSA_FLAG VARCHAR2(1 BYTE)
, HSA_UNIT_ID VARCHAR2(5 BYTE)
, BLG_UNIT_PREFIX_1 VARCHAR2(2 BYTE)
, BLG_UNIT_ID_1 NUMBER(5, 0)
, BLG_UNIT_SUFFIX_1 VARCHAR2(2 BYTE)
, BLG_UNIT_PREFIX_2 VARCHAR2(2 BYTE)
, BLG_UNIT_ID_2 NUMBER(5, 0)
, BLG_UNIT_SUFFIX_2 VARCHAR2(2 BYTE)
, FLOOR_TYPE VARCHAR2(4 BYTE)
, FLOOR_PREFIX_1 VARCHAR2(2 BYTE)
, FLOOR_NO_1 NUMBER(5, 0)
, FLOOR_SUFFIX_1 VARCHAR2(2 BYTE)
, FLOOR_PREFIX_2 VARCHAR2(2 BYTE)
, FLOOR_NO_2 NUMBER(5, 0)
, FLOOR_SUFFIX_2 VARCHAR2(2 BYTE)
, BUILDING_NAME VARCHAR2(45 BYTE)
, COMPLEX_NAME VARCHAR2(45 BYTE)
, HOUSE_PREFIX_1 VARCHAR2(2 BYTE)
, HOUSE_NUMBER_1 NUMBER(6, 0)
, HOUSE_SUFFIX_1 VARCHAR2(2 BYTE)
, HOUSE_PREFIX_2 VARCHAR2(2 BYTE)
, HOUSE_NUMBER_2 NUMBER(6, 0)
, HOUSE_SUFFIX_2 VARCHAR2(2 BYTE)
, DISP_PREFIX_1 VARCHAR2(2 BYTE)
, DISP_NUMBER_1 NUMBER(6, 0)
, DISP_SUFFIX_1 VARCHAR2(2 BYTE)
, DISP_PREFIX_2 VARCHAR2(2 BYTE)
, DISP_NUMBER_2 NUMBER(6, 0)
, DISP_SUFFIX_2 VARCHAR2(2 BYTE)
, ROAD_NAME VARCHAR2(45 BYTE)
, ROAD_TYPE VARCHAR2(15 BYTE)
, ROAD_SUFFIX VARCHAR2(2 BYTE)
, LOCALITY_NAME VARCHAR2(46 BYTE)
, LGA_CODE VARCHAR2(3 BYTE)
, STATE VARCHAR2(3 BYTE)
, POSTCODE VARCHAR2(4 BYTE)
, MESH_BLOCK VARCHAR2(11 BYTE)
, NUM_ROAD_ADDRESS VARCHAR2(60 BYTE)
, NUM_ADDRESS VARCHAR2(20 BYTE)
, ADDRESS_CLASS VARCHAR2(1 BYTE)
, ADD_ACCESS_TYPE VARCHAR2(2 BYTE)
, OUTSIDE_PROPERTY VARCHAR2(1 BYTE)
, LABEL_ADDRESS VARCHAR2(1 BYTE)
, FEATURE_QUALITY_ID VARCHAR2(20 BYTE)
, PFI_CREATED DATE
, UFI NUMBER(10, 0) NOT NULL
, UFI_CREATED DATE
, UFI_OLD NUMBER(10, 0)
, OBJECTID NUMBER(*, 0) NOT NULL
, SHAPE MDSYS.SDO_GEOMETRY
, CONSTRAINT ADDRESS_PK PRIMARY KEY
(
UFI
)
ENABLE
)
NOLOGGING
TABLESPACE VMLANDINFODATA
PCTFREE 10
INITRANS 1
STORAGE
(
INITIAL 1048576
NEXT 1048576
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOCOMPRESSCREATE INDEX VMA.ADDRESS_FK1 ON VMA.ADDRESS (PROPERTY_PFI ASC)
NOLOGGING
TABLESPACE VMLANDINFOINDEX
PCTFREE 5
INITRANS 2
STORAGE
(
INITIAL 1048576
NEXT 1048576
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL
CREATE INDEX VMA.ADDRESS_NU1 ON VMA.ADDRESS (ROAD_NAME ASC)
NOLOGGING
TABLESPACE VMLANDINFOINDEX
PCTFREE 5
INITRANS 2
STORAGE
(
INITIAL 1048576
NEXT 1048576
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL
CREATE INDEX VMA.ADDRESS_NU3 ON VMA.ADDRESS (LOCALITY_NAME ASC)
NOLOGGING
TABLESPACE VMLANDINFOINDEX
PCTFREE 5
INITRANS 2
STORAGE
(
INITIAL 1048576
NEXT 1048576
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL
CREATE INDEX VMA.ADDRESS_NU4 ON VMA.ADDRESS (ROAD_NAME ASC, LOCALITY_NAME ASC)
NOLOGGING
TABLESPACE VMLANDINFOINDEX
PCTFREE 5
INITRANS 2
STORAGE
(
INITIAL 1048576
NEXT 1048576
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL
CREATE INDEX VMA.ADDRESS_NU5 ON VMA.ADDRESS (MESH_BLOCK ASC)
NOLOGGING
TABLESPACE VMLANDINFOINDEX
PCTFREE 5
INITRANS 2
STORAGE
(
INITIAL 1048576
NEXT 1048576
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL
CREATE UNIQUE INDEX VMA.ADDRESS_PK ON VMA.ADDRESS (UFI ASC)
NOLOGGING
TABLESPACE VMLANDINFOINDEX
PCTFREE 10
INITRANS 2
STORAGE
(
INITIAL 1048576
NEXT 1048576
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL
CREATE INDEX VMA.ADDRESS_SD ON VMA.ADDRESS (SHAPE) INDEXTYPE IS MDSYS.SPATIAL_INDEX
PARAMETERS('tablespace=VMLANDINFOINDEX work_tablespace=SDO_WORK layer_gtype=POINT')
NOPARALLEL
CREATE UNIQUE INDEX VMA.ADDRESS_UN1 ON VMA.ADDRESS (PFI ASC)
NOLOGGING
TABLESPACE VMLANDINFOINDEX
PCTFREE 10
INITRANS 2
STORAGE
(
INITIAL 1048576
NEXT 1048576
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL
CREATE INDEX VMA.HOUSE_NUMBER_COM_IDX ON VMA.ADDRESS (HOUSE_NUMBER_1 ASC, HOUSE_NUMBER_2 ASC)
NOLOGGING
TABLESPACE VMLANDINFOINDEX
PCTFREE 10
INITRANS 2
STORAGE
(
INITIAL 1048576
NEXT 1048576
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL
CREATE INDEX VMA.LOCALITY_NAME_IDX ON VMA.ADDRESS (SOUNDEX(LOCALITY_NAME) ASC)
NOLOGGING
TABLESPACE VMLANDINFOINDEX
PCTFREE 10
INITRANS 2
STORAGE
(
INITIAL 1048576
NEXT 1048576
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL
CREATE INDEX VMA.ROAD_NAME_IDX ON VMA.ADDRESS (SOUNDEX(ROAD_NAME) ASC)
NOLOGGING
TABLESPACE VMLANDINFOINDEX
PCTFREE 10
INITRANS 2
STORAGE
(
INITIAL 1048576
NEXT 1048576
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLELALTER TABLE VMA.ADDRESS
ADD CONSTRAINT ADDRESS_UN1 UNIQUE
(
PFI
)
ENABLE
|
|
|
|
|
|
| Re: Query requires tuning [message #623743 is a reply to message #623734] |
Sat, 13 September 2014 15:04 |
John Watson
Messages: 8962
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
@BS, no discrepancy, the lower figure is the number the CBO expects after applying a predicate. I think the major difference is that the FIRST_ROWS hint pushes the CBO towards nested loop joins implemented with index range scans rather than hash joins implemented with table full scans.
@OP, I would say that the real problem here is that there is no problem. This,
Quote:consuming too much of logical reads(buffer gets) which is a pain to the database is not a problem. A problem would be, for example, "the query takes X seconds, the users need it to complete in Y seconds".
|
|
|
|
| Re: Query requires tuning [message #623744 is a reply to message #623743] |
Sat, 13 September 2014 15:57 |
AviatoR
Messages: 43
Registered: February 2009
|
Member |
|
|
@John the problem here is it takes more time to complete the task, which makes the application connection to timeout. How to bring the response time down?
@blackswan the first query is the base view, which is accessed by another view(second post) which is apparently used by application. I posted you the select query from the view(first post).
[Updated on: Sun, 14 September 2014 09:09] Report message to a moderator |
|
|
|
|
|
| Re: Query requires tuning [message #623772 is a reply to message #623722] |
Sun, 14 September 2014 09:11 |
AviatoR
Messages: 43
Registered: February 2009
|
Member |
|
|
John the below query requires tuning
SELECT /*+ FIRST_ROWS (1000)*/
SHAPE,
ADD_HOUSE_NUMBER_1 || ADD_HOUSE_SUFFIX_1 AS HOUSE_NUMBER_1,
ADD_HOUSE_SUFFIX_1 AS HOUSE_SUFFIX_1,
ADD_HOUSE_NUMBER_2 AS HOUSE_NUMBER_2,
ADD_HOUSE_SUFFIX_2 AS HOUSE_SUFFIX_2
FROM VMP.V_PROPERTY_MP_ADDRESS
WHERE (ADD_BLG_UNIT_ID_1 IS NULL
OR ADD_BLG_UNIT_ID_1 = 1)
AND PROP_STATUS = 'A';
Execution plan:
Execution Plan
----------------------------------------------------------
Plan hash value: 3757197664
--------------------------------------------------------------------------------
--------------------
| Id | Operation | Name | Rows | Bytes | Cos
t (%CPU)| Time |
--------------------------------------------------------------------------------
--------------------
| 0 | SELECT STATEMENT | | 1003 | 103K| 40
10 (1)| 00:00:57 |
| 1 | NESTED LOOPS | | | |
| |
| 2 | NESTED LOOPS | | 1003 | 103K| 40
10 (1)| 00:00:57 |
| 3 | NESTED LOOPS | | 1003 | 40120 | 20
17 (1)| 00:00:29 |
|* 4 | TABLE ACCESS FULL | ADDRESS | 2717K| 51M|
10 (0)| 00:00:01 |
|* 5 | TABLE ACCESS BY INDEX ROWID| PROPERTY | 1 | 20 |
2 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | PROPERTY_UN1 | 1 | |
1 (0)| 00:00:01 |
|* 7 | INDEX UNIQUE SCAN | PROPERTY_VIEW_UN1 | 1 | |
1 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID | PROPERTY_VIEW | 1 | 66 |
2 (0)| 00:00:01 |
--------------------------------------------------------------------------------
--------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("ADDRESS"."BLG_UNIT_ID_1" IS NULL OR "ADDRESS"."BLG_UNIT_ID_1"=1)
5 - filter("PROPERTY"."STATUS"='A')
6 - access("PROPERTY"."PFI"="ADDRESS"."PROPERTY_PFI")
7 - access("PROPERTY_VIEW"."PFI"="PROPERTY"."VIEW_PFI")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
17032549 consistent gets
1548119 physical reads
0 redo size
943642947 bytes sent via SQL*Net to client
2009408 bytes received via SQL*Net from client
182543 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2738021 rows processed
Note: The address table goes in full table scan, i even created index on null values (indexed null values) still it goes to FTS. Null values are around 2.7 Million rows and with value 1 we have around 100,000 rows!
Is there any way to tweak the query?
[Updated on: Sun, 14 September 2014 09:24] Report message to a moderator |
|
|
|
| Re: Query requires tuning [message #623773 is a reply to message #623772] |
Sun, 14 September 2014 09:27 |
John Watson
Messages: 8962
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
OK. How long does it take? How long would you like it to take? How many rows does it return? Why did you put in that FIRST_ROWS hint? What happens without the hint?
And, most importantly, what is the query for the view VMP.V_PROPERTY_MP_ADDRESS? Please do not say that the view is that other query you posted, because it isn't. The column names don't match.
Lastly, when you post execution plans please adjust the line length to make them readable.
|
|
|
|
| Re: Query requires tuning [message #623779 is a reply to message #623773] |
Sun, 14 September 2014 10:43 |
AviatoR
Messages: 43
Registered: February 2009
|
Member |
|
|
|
Yes the first query is for VMP.V_PROPERTY_MP_ADDRESS. while generating quick ddl, it didn't render properly in SQL developer, therefore alias were not displayed. Sure next time i'll adjust the length of the line. The query takes more than 3 minutes, it should be better if it takes less than a minute.
|
|
|
|
| Re: Query requires tuning [message #623780 is a reply to message #623779] |
Sun, 14 September 2014 11:18 |
John Watson
Messages: 8962
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
First, please correct the DDL you have posted. It doesn't run, I think because you have replaced semi-colons with line breaks. You cannot expect other people to debug your code for you. And you need to remove the references to tabespaces and to schemas: no-one else has those tablepaces and schemas, so until you remove them, no-one can run the code.
I also note that you have ignored my other questions, and have failed to provide the correct code for the view.
Apart from all that, until you index the join column of your ADDRESS table, a full scan is the only sensible execution plan. If you index it, then you will probably get a different join order and improved performance.
|
|
|
|
| Re: Query requires tuning [message #624213 is a reply to message #623780] |
Fri, 19 September 2014 02:19 |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
|
The address table is being tested for IS NULL. Barring some bitmap/[function based index/virtual column and app code changes] style tomfoolery, FTS is the only option.
|
|
|
|
| Re: Query requires tuning [message #624218 is a reply to message #624213] |
Fri, 19 September 2014 03:01 |
John Watson
Messages: 8962
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
OP appears to have lost interest in this (as he did in his topic on OTN).
However, the query can in fact be tuned: if you use PROPERTY as the driving table, then an indexed path is possible because ADDRESS could be accessed by an indexed nested loop join. More information on the filtered and unfiltered row cardinalities would help. Or of course a simple test using a LEADING hint.
|
|
|
|
Goto Forum:
Current Time: Thu Jan 02 18:08:28 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
