SELECT papf.per_information2                  Payroll_Tax_State
          ,haou.name                             Legal_Employer
          ,COUNT(DISTINCT paaf.assignment_id)    Emp_Count
    FROM per_all_assignments_f        paaf
         ,per_all_people_f            papf
         ,hr_soft_coding_keyflex      hsck
         ,hr_all_organization_units   haou
  WHERE paaf.soft_coding_keyflex_id  = hsck.soft_coding_keyflex_id
    AND hsck.segment1                = haou.organization_id -- This join is causing FT scan as 'segment1' column does not have any index defined over it
    AND haou.organization_id         IN (SELECT DISTINCT haou.organization_id
                                           FROM per_all_assignments_f       paaf
                                                ,hr_soft_coding_keyflex     hsck
                                                ,hr_all_organization_units  haou
                                          WHERE paaf.soft_coding_keyflex_id  = hsck.soft_coding_keyflex_id
                                            AND hsck.segment1                = haou.organization_id
                                            AND paaf.payroll_id              IN (61, 62)
                                            AND TRUNC(SYSDATE) BETWEEN paaf.effective_start_date AND paaf.effective_end_date
                                        )
    AND paaf.person_id               = papf.person_id
    AND papf.per_information2        IN (SELECT DISTINCT per_information2 
                                           FROM per_all_people_f 
                                          WHERE TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date
                                            AND per_information2 IS NOT NULL
                                        )
    AND paaf.primary_flag            = 'Y'
    AND papf.effective_start_date    BETWEEN '01-JAN-2010' AND '31-DEC-2010'
    AND papf.effective_end_date      BETWEEN '01-JAN-2010' AND '31-DEC-2010'
    AND paaf.effective_start_date    BETWEEN '01-JAN-2010' AND '31-DEC-2010'
    AND paaf.effective_end_date      BETWEEN '01-JAN-2010' AND '31-DEC-2010'
  GROUP BY papf.per_information2, haou.name;

1) have you used proper data types for your data?  Is a date storead as a DATE or a VARCHAR2 or a NUMBER?

2) have you defined NOT NULL where an attribute is in fact required?

3) have you defined constraints (PK/UK/FK).  You can in 11g use RELY constraints if you don't want the overhead of actually enforcing them.

4) are your foreign keys supported by an index?  Remember, indexes with the right leading columns are good enough for any constraint these days so your indexes no longer have to exactly match your constraints.

5) have you collected statistics on your tables and columns and indexes etc.?  Consider using SKEWONLY option and collecting histograms when faced with difficult situations.  Google it if you don't know what SKEWONLY is.

6) have you turned on CPU costing?  Test it first if you have not, it can change things.

DATATYPE = information
NOT NULL = information
CONSTRAINT = information
INDEX = information
STATISTICS = information
SYSTEM INFO = information

1) you can use a TOW diagram (see his book on sql tuning in amazon (DAN TOW)).  This will help you to understand the most likely correct ordering of tables for getting data.

2) you can take the "AMOUNT OF DATA MOVEMENT" approach.  This method is full of so many holes you can drive a bus through it.  And yet, it seems to work really well just the same.  However, it would take some time to explain.

1) check your query plan to make sure you are getting the right cardinality estimates particularly for the intial steps in the plan.  Cardinalities should be close for simple queries, and within 5x of the right number for complex situtations (multi-column indexes).  If you see cardinalities that are orders of magnitude off, then you have some problem that needs addressing (usually stats are off).

2) check the query plan to see if you have the right driving table.  Where you start is key.  The TOW diagram can assist with this.

3) check the query plan for table ordering.  Does it follow the order predicted by the TOW diagram reasonably well?  It does not have to match exactly, but it should make sense just the same.

4) check the query plan join methods.  Are you doing NESTED LOOP when joining millions of rows?  Why?  Should you be using HASH JOIN instsead?

5) check the query plan filtering and access predicate actions in the query plan.  Do they make sense to you.  If not figure out why either you are wrong, or the optimizer is wrong.

6) consider if there are situations where new indexes or enhancing existing indexes will help.

7) consider rewrites of the query.  This can get involved, especially of the query is calling PLSQL and you want to convert the PLSQL calls to some equivelant inline SQL.  But this usually results in the most gain.

8) have you checked database parameter settings for reasonableness.  Maybe your optimizer mode is CHOOSE or ALL_ROWS but you are working some kind of OLTP system and need a flavor of FIRST_ROWS_N.  Maybe your PGA_AGGREAGATE_TARGET (or whatever they call it these days) is not big enough for proper sorting/hash joins).

19.1.2.2 Hints for Enabling Optimizer Features
The OPTIMIZER_FEATURES_ENABLE hint acts as an umbrella parameter for enabling a series of optimizer features based on an Oracle Database release number. This hint is a useful way to check for plan regressions after database upgrades.

Specify the release number as an argument to the hint. The following example runs a query with the optimizer features from Oracle Database 11g Release 1 (11.1.0.6):

SELECT /*+ optimizer_features_enable(11.1.0.6') */ employee_id, last_name
FROM    employees
ORDER BY employee_id;