When compiling objects (functions/procedures/packages) in Oracle, there are several options available to aid in debugging and performance

1. PLSQL_OPTIMIZE_LEVEL – introduced in Oracle 10g, this system/session option specifies the optimizations Oracle should use to compile program units. These include improvements such as inlining and replacing cursor for loops with more efficient bulk operations such as FORALL.  For more information, see http://docs.oracle.com/cd/E14072_01/server.112/e10820/initparams189.htm
2. Compile with DEBUG option. This option tells Oracle to generate instrumentation code for the PL/SQL debugger. Specifying DEBUG has the same effect as PLSQL_OPTIMIZE_LEVEL=1 according to the 11gR2 docs here

To view the optimization level, debug flags and other settings applying to individual program units (within the current schema), you can query USER_PLSQL_OBJECT_SETTINGS

select * from user_plsql_object_settings

If you have ever needed to add a unique key to a table in Oracle (I’m sure a similar query in SQL Server would work) but couldn’t because of duplicate rows, maybe the following query will help you.

Assume you want to create the following index

alter table FREIGHT_RATE
   add constraint FREIGHT_RATE_UK02 
   unique (CARRIER_EQUIP_TYPE_XREF_ID, 
           CARRIER_LANE_ID,  
           RATE_ADJ_ID, 
           CURRENCY_UOM_ID, 
           EFF_END_DTE)

but can’t because of duplicate records….

The following query will locate and delete duplicate records based on their eff_strt_dte (choosing the most recently added one as the one to keep)

DELETE FROM freight_rate
WHERE freight_rate_id IN(
  WITH unique_ids AS (
     SELECT * from (
        SELECT first_value(freight_rate_id) OVER(PARTITION BY
                                   CARRIER_EQUIP_TYPE_XREF_ID,
                                   CARRIER_LANE_ID,
                                   RATE_ADJ_ID,
                                   CURRENCY_UOM_ID,
                                        EFF_END_DTE
        ORDER BY rate_eff_strt_dte DESC) as ids
     FROM freight_rate)
 ), not_in AS (
     SELECT freight_rate_id FROM freight_rate
     MINUS
     SELECT ids from unique_ids
 )
 select freight_rate_id from not_in
)

Note the use of the WITH clause to come up with a list of ID’s to delete. This was done because of a constraint in place that only allows us to use 5 seconds of CPU time on the database server for any single query. A query similar to the following wouldn’t work:

DELETE FROM freight_rate
WHERE ROWID NOT IN(SELECT first_value(ROWID) OVER(PARTITION BY
                                   CARRIER_EQUIP_TYPE_XREF_ID,
                                   CARRIER_LANE_ID,
                                   RATE_ADJ_ID,
                                   CURRENCY_UOM_ID,
                                   EFF_END_DTE
                      ORDER BY rate_eff_strt_dte DESC)
                   FROM freight_rate);

This query would run for >2min before failing on the 5s CPU limit. The first query runs in just a couple of seconds!