sql - Difference in elapsed time of the query where everything in the trace seems to be identical -

while analysing traces of program , 1 before , 1 have run after creating 2 new indexes, found queries having high runtime compared previous run(which not using of new indexes have created , have identical execution plans).

one particular query :

select gp.period_year, gp.period_name, gp.period_year || '-' ||  lpad(gp.period_num, 2, '0')  gl_periods gp gp.period_type = :b3 , gp.period_set_name = :b2 ,  gp.adjustment_period_flag = 'n' , trunc (:b1 ) between trunc  (gp.start_date) , trunc (gp.end_date)  before stats: call     count       cpu    elapsed       disk      query    current        rows ------- ------  -------- ---------- ---------- ---------- ----------  ---------- parse        1      0.00       0.00          0          0          0           0 execute 1738112     31.63      31.26          0          0          0           0 fetch   1738112    778.18     780.36         17    6952448          0     1738112 ------- ------  -------- ---------- ---------- ---------- ----------  ---------- total   3476225    809.81     811.63         17    6952448          0     1738112  after stats: call     count       cpu    elapsed       disk      query    current        rows ------- ------  -------- ---------- ---------- ---------- ----------  ---------- parse        1      0.00       0.00          0          0          0           0 execute 1738112     75.80      76.87          0          0          0           0 fetch   1738112   1490.70    1499.47         17    6952448          0     1738112 ------- ------  -------- ---------- ---------- ---------- ----------  ---------- total   3476225   1566.50    1576.36         17    6952448          0     1738112 

and wait events query

1. db file sequential read,
2. latch: cache buffers chains,
3. latch: shared pool

which not having total wait time of more second.

so want know why particular query showing such difference in runtime? areas should cause of runtime difference.

i have run programs in same instance. let me know if more information required.

as per oracle documentation on performance tuning:

you can use indexes boost performance of queries return less 15% of rows in table. query returns 15% or more of rows in table executed faster full scan, is, reading rows sequentially.

so in case please check number of rows fetched not greater or equal 15%.

you can refer performance tuning documentation.