The DBMS_SQLTUNE package is the interface for tuning SQL on demand. Its Doc pages are here.  Have a look.  There is a lot of stuff to do to set a tuning task, run it, report on it and then get it to do something useful.  We've wrapped all that into our SQL Tuning Advisor function which means you dont need to start writing plsql API calls to make this work.  Stick in your dodgy query, click the advisor button and visualize the results.

Here's a look at how to do this.  Firstly, we need to grant a few permissions to our user. I'm doing this as sys.
 Then, for this demo, I want to clean out all the statistics on the tables I want to look at.
 Now, here's my initial query, getting total and mean salary grouped by departments.
When we then run the tuning advisor, a new tab appears on the worksheet which has the main results from the tuning sesstion.  This tab has four main sections to it. These are the statistics which the advior found on the objects in the query, changes to the profile which is in use, any indexes which need to be added.  Finally, if needed, there is a restructuring tab which may have some sql to help you restructure your query.


We can see from the output above that the statistics are not available and the tool is recommending refreshing statistics on the objects in the original query.

We can then go and analyze the tables to see if that helps.


We can then check that the stats are fresh and at the time of posting, this is current.



Now, going back the tuning advisor and running it again, shows some different stats

Heres the final look at what the Tuning advisor tells us at the end of the second run.  This is the standard text output that comes from the tuning package

GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : staName14054
Tuning Task Owner  : HRDEMO
Tuning Task ID     : 9295
Workload Type      : Single SQL Statement
Execution Count    : 1
Current Execution  : EXEC_9255
Execution Type     : TUNE SQL
Scope              : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status  : COMPLETED
Started at         : 04/19/2012 07:33:50
Completed at       : 04/19/2012 07:33:50

-------------------------------------------------------------------------------
Schema Name: HRDEMO
SQL ID     : 028hrurkuc6ah
SQL Text   : SELECT SUM(E.Salary),
               AVG(E.Salary),
               COUNT(1),
               E.Department_Id
             FROM Departments D,
               Employees E
             GROUP BY E.Department_Id
             ORDER BY E.Department_Id

-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------

1- Restructure SQL finding (see plan 1 in explain plans section)
----------------------------------------------------------------
  An expensive cartesian product operation was found at line ID 2 of the
  execution plan.

  Recommendation
  --------------
  - Consider removing the disconnected table or view from this statement or
    add a join condition which refers to it.

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original
-----------
Plan hash value: 2187233893

 
---------------------------------------------------------------------------------------
| Id  | Operation               | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |             |    11 |    77 |    35   (3)| 00:00:01 |
|   1 |  SORT GROUP BY          |             |    11 |    77 |    35   (3)| 00:00:01 |
|   2 |   MERGE JOIN CARTESIAN  |             |  2889 | 20223 |    34   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL    | EMPLOYEES   |   107 |   749 |     3   (0)| 00:00:01 |
|   4 |    BUFFER SORT          |             |    27 |       |    32   (4)| 00:00:01 |
|   5 |     INDEX FAST FULL SCAN| DEPT_ID_PKX |    27 |       |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$1
   3 - SEL$1 / E@SEL$1
   5 - SEL$1 / D@SEL$1
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - (#keys=1) "E"."DEPARTMENT_ID"[NUMBER,22], COUNT(*)[22], 
       COUNT("E"."SALARY")[22], SUM("E"."SALARY")[22]
   2 - (#keys=0) "E"."SALARY"[NUMBER,22], "E"."DEPARTMENT_ID"[NUMBER,22]
   3 - "E"."SALARY"[NUMBER,22], "E"."DEPARTMENT_ID"[NUMBER,22]
   4 - (#keys=0) 

-------------------------------------------------------------------------------

For doing this without SQL Developer, there are several things which you need to do. I have a little graphic which looks at each of the steps which need to be taken to create a tuning job in the normal SQL*Plus interface.  The main steps are creating task tuning tasks, and then interpreting the output.
Finally, this functionality is part of the SQL Worksheet in SQLDeveloper, which together with trace file editing, explain plan and autotrace, hierarchical profiler and monitoring sessions adds more tools to the toolbox for trying to find issues with you code.