Home » RDBMS Server » Performance Tuning » Full table scan (10.2.0.1 , window 7)
Full table scan [message #580076] Wed, 20 March 2013 00:43 Go to next message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
Dear All,

Below is the select stmt -

SELECT a.TXT_CUSTOMER_CD,
       a.TXT_CUSTOMER_NAME,
       TXT_CUSTOMER_STATUS,
       TO_CHAR (a.dat_start_dt, :"SYS_B_00") DAT_START_DT,
       :"SYS_B_01" AS Dat_Start_Time,
       DECODE (TXT_IND_CORP_FLAG,
               :"SYS_B_02", :"SYS_B_03",
               :"SYS_B_04", :"SYS_B_05",
               :"SYS_B_06", :"SYS_B_07")
          AS "Type",
       b.TXT_AREAVILLAGE,
       b.TXT_CITYDISTRICT,
       b.NUM_PINCODE,
       b.TXT_PINCODE_LOCALITY,
       b.TXT_STATE,
       DECODE (TXT_CUSTOMER_STATUS,
               :"SYS_B_08", :"SYS_B_09",
               :"SYS_B_10", :"SYS_B_11",
               :"SYS_B_12")
          AS Approval_status
  FROM GENMST_CUSTOMER a, GENMST_LOCATION b
 WHERE     b.NUM_LOCATION_CD = a.NUM_PERMANENT_LOCATION_CD
       AND a.txt_firstname LIKE :"SYS_B_13"
       AND ROWNUM < :"SYS_B_14"


below is my explain plan -

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2755851763

--------------------------------------------------------------------------------------------------
| Id  | Operation              | Name            | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                 |   442K|    49M|       |   107K  (1)| 00:21:27 |
|*  1 |  COUNT STOPKEY         |                 |       |       |       |            |          |
|*  2 |   HASH JOIN            |                 |   442K|    49M|    39M|   107K  (1)| 00:21:27 |
|*  3 |    TABLE ACCESS FULL   | GENMST_CUSTOMER |   444K|    33M|       | 57355   (2)| 00:11:29 |
|   4 |    INDEX FAST FULL SCAN| IDX_CD1         |    10M|   378M|       | 23157   (1)| 00:04:38 |
--------------------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM<TO_NUMBER(:SYS_B_14))
   2 - access("B"."NUM_LOCATION_CD"="A"."NUM_PERMANENT_LOCATION_CD")
   3 - filter("A"."TXT_FIRSTNAME" LIKE :SYS_B_13)

18 rows selected.



Though, i made index on the where clause column-

CREATE INDEX INS.IDX_LOCATION_CD ON INS.GENMST_CUSTOMER
(NUM_PERMANENT_LOCATION_CD)
LOGGING
TABLESPACE INDX_16
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


Even after that, explain plan shows full table scan on table "GENMST_CUSTOMER". Kindly suggest, how to bring down cost to CPU.

Regards,
Ishika


[EDITED by LF: fixed [code] tags]

[Updated on: Wed, 20 March 2013 01:52] by Moderator

Report message to a moderator

Re: Full table scan [message #580077 is a reply to message #580076] Wed, 20 March 2013 01:14 Go to previous messageGo to next message
michael_bialik
Messages: 621
Registered: July 2006
Senior Member
1. Try creating an index on column that limits the number of selected rows (instead of JOIN):

CREATE INDEX ... ON GENMST_CUSTOMER ( txt_firstname ) ...


2. It also depends what value you are passing as parameter :"SYS_B_13" ( If the value is '%' or '%<something>%' then
optimizer still may decide to perform full table scan on GENMST_CUSTOMER table.

3. Ensure that your stats are up to date.

HTH
Re: Full table scan [message #580083 is a reply to message #580077] Wed, 20 March 2013 01:48 Go to previous messageGo to next message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
Thanks Michael,

I will do what u have suggested and then revert.
Re: Full table scan [message #580093 is a reply to message #580076] Wed, 20 March 2013 06:16 Go to previous message
snowball
Messages: 229
Registered: April 2006
Location: China
Senior Member

Hi, ishika
As the sql use so many bind variables, the predicate is "TXT_FIRSTNAME".
I'm afraid you might facing the bind peeking issue as the like statement condition is unknow and first_name might not be very selecty.
Previous Topic: SubQuery - Joins performance compare
Next Topic: How to reduce cost of query ...Give Indexing on FT Scan
Goto Forum:
  


Current Time: Thu Mar 28 15:05:50 CDT 2024