sql - How to check whether index is being used or not in Oracle -

select *    (select temp.*,                 rownum rnum            (select entry_guid                    alertdevtest.entry                   upper(alert_name) = 'alertname'                         , user_guid = 'alertproductclienttest'                         , product_code = '-101'                         , status_code != 13) temp           rownum <= 2500)   rnum >= 0;   select *    (select temp.*,                 rownum rnum            (select entry_guid                    alertdevtest.entry                   upper(alert_name) = 'alertname'                         , user_guid = 'alertproductclienttest'                         , product_code = '-101'                         , status_code != 13                         , product_view in ( 'pview' )) temp           rownum <= 2500)   rnum >= 0;  

am running above queries , seeing performance degradation in second query compare first one. difference being additional filter , product_view in ('pview') in second query. has index on column. please let me know can reason performance degradation , how can check whether index being used or not? using oracle sql developer , tried checking explain plan couldn't details.

in oracle sql developer, when have sql in worksheet, there button "explain plan", can hit f10. after execute explain plan, show in bottom view of sql developer. there column "object_name", tell index being used. example, in query ran, in left column (operation) shows "select statement" first, sort (aggregate) , index (range scan) , in object_name column shows ticker_idx1, name of index on table.

so can see via object_name column indexes being used.

it can happen oracle cost based optimizer chooses sub-optimal execution plan. many times updating statistics solve issue. other choices add additional indexes, in other words multi-column index. can hint sql statement, needed. also, it's possible rewrite query.