SQLAccess Advisor

SQL> conn / as sysdba
SQL> BEGIN
2 DBMS_ADVISOR.quick_tune(
3 advisor_name => DBMS_ADVISOR.SQLACCESS_ADVISOR,
4 task_name => 'test_quick_tune',
5 attr1 => 'SELECT * FROM hr.employees WHERE upper(last_name) = ''SMITH''');
6 END;
7 /

From Enterprise Manager, click Advisor Central. From the page, you can see test_quick_tune. Click that task name. In the page Result for Task, click the Recommendation tab. You will see a recommendation for tuning your index based on your query.