Home » RDBMS Server » Performance Tuning » Wich index is used. Confusion in SQL-trace (Oracle 12.1.0.2.0)
Wich index is used. Confusion in SQL-trace [message #647295] Mon, 25 January 2016 09:38 Go to next message
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)

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

Re: Wich index is used. Confusion in SQL-trace [message #647296 is a reply to message #647295] Mon, 25 January 2016 09:40 Go to previous messageGo to next message
mats-larsson@iname.com
Messages: 13
Registered: September 2013
Location: Stockholm
Junior Member
I am using Oracle 12.1.0.2.0
Re: Wich index is used. Confusion in SQL-trace [message #647297 is a reply to message #647295] Mon, 25 January 2016 09:45 Go to previous messageGo to next message
John Watson
Messages: 7212
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.
Re: Wich index is used. Confusion in SQL-trace [message #647298 is a reply to message #647297] Mon, 25 January 2016 10:59 Go to previous message
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.
Previous Topic: Application slow down
Next Topic: Tune query for Sum function
Goto Forum:
  


Current Time: Tue Jan 16 02:56:12 CST 2018

Total time taken to generate the page: 0.02083 seconds