Hello,Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

Hello,

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production.

We have a SQL statement using views of views that can normally execute 1 second, can now 'randomly' sometimes take 2.5 minutes. Running said statement via SQL Developer in a loop with same bind vars we do sometimes see this big difference in performance, have checked shared pool and not hard parsing , checked AWR history and can see at least four different explain plans for the sql, could anybody be some kind as to consider what I should do next?

Ran statement through tuning advisor and recommendation was to create new index with 5 different columns, but reluctant to just create such a big index at the moment. Possible clue we moved into new year 2019 but this doesn't explain why query sometimes runs fast.