OraFAQ Forum: Performance Tuning » Wich index is used. Confusion in SQL-trace

Home » RDBMS Server » Performance Tuning » Wich index is used. Confusion in SQL-trace (Oracle 12.1.0.2.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Wich index is used. Confusion in SQL-trace [message #647295]

Mon, 25 January 2016 09:38

mats-larsson@iname.com
Messages: 13
Registered: September 2013
Location: Stockholm

Junior Member

I have a query that sometimes takes long time to run. So I have ran it with SQL-trace. But the trace is confusing.
In the statistics it says index I_TRANS_3 is used which is a bad choice because of poor selectivity. In the executionplan however it says I_TRANS_1 which is a good choice.
The number of rows indicates that I_TRANS_3 is used.

The question why it sometimes takes long time and sometimes is very quick is another issue. I have to analyze it further. So I leave that to another posting.

The question here is why are two different indexes listed and which index is actually used?

*******************************************
SQL ID: fvuvmbpdt7a7d Plan Hash: 3232189787

select ID_TRANS ,TO_CHAR(ID_PERS) ,TYP_TRANS ,TO_CHAR(DAT_TRANS,
  'YYYYMMDDHH24MISS') ,TO_NUMBER(TO_CHAR(DAT_RTA,'YYYYMMDD')) ,BEL_TRANS ,
  TO_CHAR(DAT_UPPDAT,'YYYYMMDDHH24MISS') ,NR_ARENDE ,TO_CHAR(NR_HANDL) ,AR ,
  MANAD ,STATUS ,TO_NUMBER(TO_CHAR(DAT_BESLUT,'YYYYMMDD')) ,ID_BESLUT ,
  TO_NUMBER(TO_CHAR(DAT_FORFALL,'YYYYMMDD')) ,
  TO_NUMBER(TO_CHAR(DAT_FG_FORFALL,'YYYYMMDD')) ,TYP_RTA ,RANTERAKNAD ,
  BEL_RANTEEFFEKT ,TO_NUMBER(TO_CHAR(DAT_BOKF,'YYYYMMDD')) ,NR_GIRO ,
  BELOPP_ORE ,OAVST_INBET ,NR_LOP ,TYP_INDATA ,NR_ARENDE_KFM ,
  TO_CHAR(ID_PERS_OMF) ,TYP_SK_SLAG_OMF ,REF_UTBET ,NR_SAMORDN ,
  STATUS_AVIS_GIN ,ID_ARENDE_DEB ,AR_FROM ,MANAD_FROM ,
  TO_NUMBER(TO_CHAR(DAT_DEKL,'YYYYMMDD'))  
from
 T_TRANS where ((ID_PERS=to_number(:b0) and dat_trans>to_date(:b1,
  'yyyymmddhh24miss')) and dat_trans<=to_date(:b2,'yyyymmddhh24miss')) order 
  by DAT_RTA asc ,TYP_TRANS desc             

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       18      0.16      15.84          0          0          0           0
Execute     19      0.00       0.38         20         20          0           0
Fetch       33    192.48    1492.05    1595266    2961720          0          78
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       70    192.64    1508.28    1595286    2961740          0          78

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 28  (SK_DB06)
Number of plan statistics captured: 3

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          4          6  SORT ORDER BY (cr=29206 pr=9995 pw=0 time=16290373 us cost=2668 size=104 card=1)
         0          4          6   FILTER  (cr=29206 pr=9995 pw=0 time=15939155 us)
         0          4          6    TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED T_TRANS PARTITION: ROW LOCATION ROW LOCATION (cr=29206 pr=9995 pw=0 time=15939136 us cost=2667 size=104 card=1)
    629245     629245     629245     INDEX RANGE SCAN I_TRANS_3 (cr=6376 pr=3513 pw=0 time=13694152 us cost=398 size=0 card=420072)(object id 8140)


Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   MODE: ALL_ROWS
      0   SORT (ORDER BY)
      0    FILTER
      0     TABLE ACCESS   MODE: ANALYZED (BY GLOBAL INDEX ROWID BATCHED)
                 OF 'T_TRANS' (TABLE) PARTITION:ROW LOCATION
 629245      INDEX   MODE: ANALYZED (RANGE SCAN) OF 'I_TRANS_1' (INDEX)

***********************************************************************

Report message to a moderator

Re: Wich index is used. Confusion in SQL-trace [message #647296 is a reply to message #647295]

Mon, 25 January 2016 09:40

mats-larsson@iname.com
Messages: 13
Registered: September 2013
Location: Stockholm

Junior Member

I am using Oracle 12.1.0.2.0

Report message to a moderator

Re: Wich index is used. Confusion in SQL-trace [message #647297 is a reply to message #647295]

Mon, 25 January 2016 09:45

John Watson
Messages: 8929
Registered: January 2010
Location: Global Village

Senior Member

You ran tkprof wuth the explain= option, did you? That tells tkprof to log on to the database and run EXPLAIN PLAN against the statement, so you see both the row source operations (which is the plan that was used) and the result of a fresh parse by EXPLAIN PLAN, which may not be the plan that was used. I never use the explain= option, it just confuses things.

In this case, the difference is bacause your query uses bind variables in non-equality predicates, so EXPLAIN PLAN has no idea what would happen. You may be getting different plans for different binds, perhaps it is switching between indexes. Run tkprof with aggregate=no, and you will see this.

Report message to a moderator

Re: Wich index is used. Confusion in SQL-trace [message #647298 is a reply to message #647297]

Mon, 25 January 2016 10:59

mats-larsson@iname.com
Messages: 13
Registered: September 2013
Location: Stockholm

Junior Member

Thank You
Yes I used option EXPLAIN=
I try Your suggestions tomorrow.

Report message to a moderator

Previous Topic:	Application slow down
Next Topic:	Tune query for Sum function

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 18 19:54:31 CDT 2024