Oracle slow query? Check SQL Advisor

1) Take a snapshot before doing the slow step, remember the snapshot id
2) Take a snapshot after doing the slow step, remember the snapshot id
3) Login to the Enterprise Management console, go to Performance tab,
there will be a Snapshots link at bottom, click it
4) Select the begin snapshot id, select Create SQL Tuning Set from the
Actions dropdown list, then click Go
5) Give a proper name to the SQL tuning set, specify the end snapshot
id, then click OK
6) Click the link of sql tuning set that you just created
7) Click the Schedule SQL Advisor, schedule it as immediately (the
default option), click submit
8) The advisor will process all the SQLs in this tuning set, check which
SQLs have findings
9) Select the sql that you want to tune, view the recommendation

Oracle refresh statistics and indexes

Some access plan are slower than expected? Some unexpected table scans performed even if the indexes are well defined?

General suggestion:

Refresh db statistics (go to -> database configuration -> refresh statistics)
Refresh indexes tables (go to -> database configuration -> refresh index tables)

Oracle get the access plan

From sqlplus, execute the following commands:
SQL> spool accessplan.txt
SQL> set autotrace on
SQL> set wrap off
SQL> set linesize 120
SQL> write here the query you need to get the access plan
SQL> spool off

accessplan.txt will be located in the directory where you launch sqlplus