Home » RDBMS Server » Performance Tuning » Query requires tuning (Oracle 11gr2, sun)
Query requires tuning [message #623716] Fri, 12 September 2014 22:59 Go to next message
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 #623717 is a reply to message #623716] Fri, 12 September 2014 23:06 Go to previous messageGo to next message
BlackSwan
Messages: 25797
Registered: January 2009
Location: SoCal
Senior Member
>WHERE VMP.PROPERTY_VIEW.PFI = VMP.PROPERTY.VIEW_PFI
>AND VMP.PROPERTY.PFI = VMA.ADDRESS.PROPERTY_PFI

every column above should be INDEXED. Are they?

Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/m/433888/136107/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) output from SQL_TRACE & tkprof

Are statistics current for all TABLES & INDEXES?
Re: Query requires tuning [message #623718 is a reply to message #623717] Fri, 12 September 2014 23:09 Go to previous messageGo to next message
BlackSwan
Messages: 25797
Registered: January 2009
Location: SoCal
Senior Member
>Likewise I have three different autonomous queries.

what is the difference between an "autonomous query" & "non-autonomous query"?
Re: Query requires tuning [message #623722 is a reply to message #623718] Sat, 13 September 2014 02:01 Go to previous messageGo to next message
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 #623734 is a reply to message #623722] Sat, 13 September 2014 09:41 Go to previous messageGo to next message
BlackSwan
Messages: 25797
Registered: January 2009
Location: SoCal
Senior Member
> 5 | TABLE ACCESS FULL | ADDRESS | 3409K| 760M| | 22598
above is from the first post which shows ADDRESS table has 3409K rows
below is from the second post which shows ADDRESS table has 2717K rows
>4 | TABLE ACCESS FULL | ADDRESS | 2717K| 51M|

Please explain this apparent discrepancy.
Re: Query requires tuning [message #623743 is a reply to message #623734] Sat, 13 September 2014 15:04 Go to previous messageGo to next message
John Watson
Messages: 7219
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 Go to previous messageGo to next message
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 #623754 is a reply to message #623744] Sun, 14 September 2014 02:13 Go to previous messageGo to next message
John Watson
Messages: 7219
Registered: January 2010
Location: Global Village
Senior Member
So which query do you want to tune?
Re: Query requires tuning [message #623772 is a reply to message #623722] Sun, 14 September 2014 09:11 Go to previous messageGo to next message
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 Go to previous messageGo to next message
John Watson
Messages: 7219
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
John Watson
Messages: 7219
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 Go to previous messageGo to next message
Roachcoach
Messages: 1508
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 Go to previous message
John Watson
Messages: 7219
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.
Previous Topic: sql_id of a sql not the same in different databases
Next Topic: Is there a way to monitor historical locks in Standard Edition
Goto Forum:
  


Current Time: Fri Jan 19 23:52:46 CST 2018

Total time taken to generate the page: 0.07037 seconds