How to use hinted SQL to fix bad SQL plan

As I said in a previously blog Outline, SQL profile and SQL Plan Management(SPM), outline is the oldest approach to fix SQL plan, but it’s the easiest one to use, and works for all oracle version, and do NOT need extra license.

I’d like to elaborate the most frequently used scenario: a SQL is using a bad SQL plan. We can use SQL hints to get a GOOD plan. We want to use outline to fix the SQL plan to the GOOD one. In another word, we want to use a SQL plan with different SQL text(hints) but logically the same.

Let’s create an test scenario:
1. Create a test table


ALEX@ZENG: SQL> create table t (id number, name varchar2(100));

Table created.

ALEX@ZENG: SQL> insert into t select 1, object_name from dba_objects;

74562 rows created.

ALEX@ZENG: SQL> create index t_idx1 on t(id);

Index created.

ALEX@ZENG: SQL> exec dbms_stats.gather_table_stats('ALEX','T');

PL/SQL procedure successfully completed.

ALEX@ZENG: SQL> show parameter optimizer_features_enable

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_features_enable            string      10.2.0.4

2. Create the bad SQL plan scenario


ALEX@ZENG: SQL> update t set id=rownum;

74563 rows updated.

ALEX@ZENG: SQL> commit;

Commit complete.
ALEX@ZENG: SQL> set autotrace traceonly
ALEX@ZENG: SQL> select * from t where id=1;

1 row selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2153619298

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 74556 |  1820K|    74   (5)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    | 74556 |  1820K|    74   (5)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ID"=1)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        319  consistent gets
          0  physical reads
          0  redo size
        593  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

3. Use hint to get a better plan

--it's better to use index scan because there's only one row with id=1 now
--We can get the plan by adding hint

ALEX@ZENG: SQL> explain plan for select /*+ index(t t_idx1) */ * from t where id=1;

Explained.

ALEX@ZENG: SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3292636276

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        | 74556 |  1820K|   150   (2)| 00:00:02 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T      | 74556 |  1820K|   150   (2)| 00:00:02 |
|*  2 |   INDEX RANGE SCAN          | T_IDX1 | 74556 |       |    55   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"=1)

14 rows selected.

Since INDEX RANGE SCAN is better plan, we want to use it to replace the FULL TABLE SCAN plan.

Step 1. Get the bad SQL hash value


ALEX@ZENG: SQL> select address, hash_value, sql_text from v$sql where sql_text like 'select * from t%';

ADDRESS          HASH_VALUE SQL_TEXT
---------------- ---------- ------------------------------------------------------------
000000045F900938  716428968 select * from t where id=1
...

Step 2. Create a SQL file with hints

$ cat good.sql
select /*+ index(t t_idx1) */ * from t where id=1;
$

Step 3. Fix the SQL plan using script fix_plan.sh

#!/bin/bash

if [ $# -lt 3 ]; then
cat <<EOF
Fix given SQL plan using given sql in file
  usage:    fix_plan.sh <hash_value> <hinted_sql_file> <owner>
  examples: fix_plan.sh 716428968 good.sql ALEX
EOF
exit 1
fi

HASH_VALUE=$1
SQL_FILE=$2
OL_OWNER=$3

echo "HASH_VALUE : $HASH_VALUE"
echo "SQL_FILE   : $SQL_FILE"
echo "OL_OWNER   : $OL_OWNER"
echo ""

#Create outline by hash_value
function create_ol_from_hashvalue {
HASH_VALUE=$1
OL_NAME=$2
OL_OWNER=$3

#generate create outline sql
#I didn't use dbms_outln.create_outline, because it cannot create given name outline
# and there's no hash value in V$SQL and DBA_OUTLINES to associate the two 
# according to "How To Match a SQL Statement to a Stored Outline (Doc ID 743312.1)"
sqlplus -S "/ as sysdba" > /tmp/tmp_$OL_NAME.sql <<EOF
set feedback off
set serveroutput on size unlimited
declare
v_sqltext varchar2(32000);
begin
  --get sql text
  select dbms_lob.substr(SQL_FULLTEXT, 30000, 1 ) into v_sqltext from v\$sql where hash_value = $HASH_VALUE and rownum=1;

  dbms_output.put_line('alter session set current_schema = $OL_OWNER;');
  v_sqltext := 'create or replace outline $OL_NAME for category TEMP_PLAN on ' || chr(10) || v_sqltext || chr(10) ||';';
  dbms_output.put_line(v_sqltext);
  dbms_output.put_line('exit;');
end;
/
EOF

sqlplus -S "/ as sysdba" @/tmp/tmp_$OL_NAME.sql
}

#Create outline from sql file
function create_ol_from_sqlfile {
SQL_FILE=$1
OL_NAME=$2
OL_OWNER=$3

#generate create outline sql
cat > /tmp/tmp_$OL_NAME.sql <<EOF
alter session set current_schema = $OL_OWNER;
create or replace outline $OL_NAME for category TEMP_PLAN on
`cat $SQL_FILE`
exit;
EOF

sqlplus -S "/ as sysdba" @/tmp/tmp_$OL_NAME.sql

}

#Exchange outlines, make GOOD_SQL plan to GOOD_PLAN category
#Ref: How to Edit a Stored Outline to Use the Plan from Another Stored Outline (Doc ID 730062.1)
function exchange_outline {
OL1=$1
OL2=$2
OL_OWNER=$3

sqlplus -S "/ as sysdba" <<EOF
set feedback off
alter session set current_schema = $OL_OWNER;
create private outline OLFROM from $OL1;
create private outline OLTO from $OL2;
update ol$ set hintcount=(select hintcount from ol$ where ol_name='OLTO') where ol_name='OLFROM';
delete from ol$ where ol_name='OLTO';
update ol$ set ol_name='OLTO' where ol_name='OLFROM';
commit;
execute dbms_outln_edit.refresh_private_outline('OLTO');
create or replace outline $OL1 from private OLTO for category GOOD_PLAN;
drop outline $OL2;
exit;
EOF

}

#display outline
function display_outline {
OL_NAME=$1
OL_OWNER=$2

sqlplus -S "/ as sysdba" <<EOF
set pagesize 1000 linesize 160
set long 32000
col hint format a55
col join_pos format a45
col owner    format a12
col name     format a18
col ts       format a14
col h        format 999
col category format a12
col sql_text format a80
col used     format a6

select name, sql_text, category, used, to_char(TIMESTAMP, 'YY-mm-dd hh24:MI')
 from  dba_outlines
where name = '$OL_NAME' and OWNER = '$OL_OWNER';

select ol_name name, category, hint#, stage# stage, hint_text hint, join_pred join_pos
 from outln.ol\$hints
 where ol_name = '$OL_NAME'
 order by ol_name, hint#;
exit;
EOF
}

#main function
echo "1. Create outline OL_$HASH_VALUE for SQL $HASH_VALUE"
create_ol_from_hashvalue $HASH_VALUE OL_$HASH_VALUE $OL_OWNER

echo "2. Create outline OL_TEMP for SQL in $SQL_FILE"
create_ol_from_sqlfile $SQL_FILE OL_TEMP $OL_OWNER

echo "3. Exchange outline OL_$HASH_VALUE with OL_TEMP, and drop OL_TEMP"
exchange_outline OL_$HASH_VALUE OL_TEMP $OL_OWNER

echo "4. Display final outline for SQL $HASH_VALUE : OL_$HASH_VALUE in category GOOD_PLAN "
display_outline OL_$HASH_VALUE $OL_OWNER

Run it:

$./fix_plan.sh 716428968 good.sql ALEX
HASH_VALUE : 716428968
SQL_FILE : good.sql
OL_OWNER : ALEX

1. Create outline OL_716428968 for SQL 716428968

Session altered.

Outline created.

2. Create outline OL_TEMP for SQL in good.sql

Session altered.

Outline created.

3. Exchange outline OL_716428968 with OL_TEMP, and drop OL_TEMP
4. Display final outline for SQL 716428968 : OL_716428968 in category GOOD_PLAN

NAME SQL_TEXT CATEGORY USED TO_CHAR(TIMEST
------------------ -------------------------------------------------------------------------------- ------------ ------ --------------
OL_716428968 select * from t where id=1 GOOD_PLAN UNUSED 13-12-29 19:39

1 row selected.

NAME CATEGORY HINT# STAGE HINT JOIN_POS
------------------ ------------ ---------- ---------- ------------------------------------------------------- ---------------------------------------------
OL_716428968 GOOD_PLAN 1 1 OUTLINE_LEAF(@"SEL$1")
OL_716428968 GOOD_PLAN 2 1 IGNORE_OPTIM_EMBEDDED_HINTS
OL_716428968 GOOD_PLAN 3 1 OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
OL_716428968 GOOD_PLAN 4 1 DB_VERSION('11.2.0.2')
OL_716428968 GOOD_PLAN 5 1 OPT_PARAM('_b_tree_bitmap_plans' 'false')
OL_716428968 GOOD_PLAN 6 1 OPT_PARAM('_index_join_enabled' 'false')
OL_716428968 GOOD_PLAN 7 1 OPT_PARAM('_optim_peek_user_binds' 'false')
OL_716428968 GOOD_PLAN 8 1 OPT_PARAM('_optimizer_skip_scan_enabled' 'false')
OL_716428968 GOOD_PLAN 9 1 OPT_PARAM('optimizer_index_cost_adj' 30)
OL_716428968 GOOD_PLAN 10 1 INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" ("T"."ID"))

10 rows selected.

Step 4. Flush the bad SQL plan

--if it's 10g, use grant to invalidate the SQL plan
SQL> grant select on ALEX.T to dba;

--if it's 11g, use procedure DBMS_SHARED_POOL.PURGE
SQL> select ADDRESS, HASH_VALUE from V$SQL where hash_value = 716428968;

ADDRESS          HASH_VALUE
---------------- ----------
000000045F900938  716428968

1 row selected.

SQL> exec DBMS_SHARED_POOL.PURGE ('000000045F900938, 716428968','C');

PL/SQL procedure successfully completed.

Step 5. Check the new plan

--it's IMPORTANT to set use_stored_outlines to use the stored outline category if not yet
--You can set it at session level and test the plan first if needed
SQL> alter system set use_stored_outlines = GOOD_PLAN;

System altered.

ALEX@ZENG: SQL> set autotrace traceonly
ALEX@ZENG: SQL> select * from t where id=1;

1 row selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3292636276

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    27 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T      |     1 |    27 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_IDX1 |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"=1)

Note
-----
   - outline "OL_716428968" used for this statement

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        185  consistent gets
          0  physical reads
          0  redo size
        597  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

There is a problem caused by “ghost” parameter use_stored_outlines. Why?
You cannot set it in inital file, and you cannot see it after setting it, and it’s lost its value after DB restart.
So we need to create a trigger to set it automatically when DB start:

SYS@ZENG: SQL> show parameter use_stored_outlines
SYS@ZENG: SQL>
SYS@ZENG: SQL> create or replace trigger enable_outlines_trig
--Ref : How to Enable USE_STORED_OUTLINES Permanently (Doc ID 560331.1)
after startup on database
begin
execute immediate('alter system set use_stored_outlines=GOOD_PLAN');
end;
/

Trigger created.

Until then, you can have a good sleep without worry about the bad SQL plan causes DB high session/load.

Advertisements

How to add hint for SQL using oracle view

A developer asked me to tune a SQL recently. It turned out that the column is very skewed, and Oracle cannot find the optimized plan. Analyzing the table column with histograms may solve the problem, may not solve the issue because it’s quite sensitive to data selected, and it may have side impacts to other SQLs. As the developer can change the SQL easily, I choose to add hints for the SQL. The SQL is based on view which is different from adding hints on tables. Here is the steps to add hints for a SQL on view:

Step 1. Get the sub-query name of the table we want to add hint on:

SQL>  explain plan for select parent_task_id,task_id, title, status_label from bugtracking where BUGTRACKING_STATUS <5;

Explained.

ARADMIN@TRACE:prod SQL> select * from table(DBMS_XPLAN.DISPLAY(NULL, NULL, 'ALL'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2453567761

----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       | 25202 |  2928K|  3280   (5)| 00:00:19 |
|   1 |  NESTED LOOPS OUTER|       | 25202 |  2928K|  3280   (5)| 00:00:19 |
|*  2 |   TABLE ACCESS FULL| T508  | 25202 |  2534K|  3272   (5)| 00:00:19 |
|*  3 |   INDEX UNIQUE SCAN| IB508 |     1 |    16 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$E213D7C1
   2 - SEL$E213D7C1 / T508@SEL$3  <-- this is the subquery name in SQL/view
   3 - SEL$E213D7C1 / B508@SEL$2

....

We want to add an index hint on table T508. It’s in query block SEL$3.

Step 2. Add hints at the subquery

SQL>  explain plan for       select/*+ index(@SEL$3 T508 I508_660000000_1) */ parent_task_id,task_id, title, status_label from bugtracking where BUGTRACKING_STATUS <5;

Explained.

ARADMIN@TRACE:prod SQL> select * from table(DBMS_XPLAN.DISPLAY(NULL, NULL, 'ALL'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2546157666

-------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                  | 25202 |  2928K|  9024   (1)| 00:00:51 |
|   1 |  NESTED LOOPS OUTER          |                  | 25202 |  2928K|  9024   (1)| 00:00:51 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T508             | 25202 |  2534K|  9016   (1)| 00:00:51 |
|*  3 |    INDEX RANGE SCAN          | I508_660000000_1 | 25202 |       |    40   (3)| 00:00:01 |
|*  4 |   INDEX UNIQUE SCAN          | IB508            |     1 |    16 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$E213D7C1
   2 - SEL$E213D7C1 / T508@SEL$3
   3 - SEL$E213D7C1 / T508@SEL$3
   4 - SEL$E213D7C1 / B508@SEL$2

As you can see, the index hint on view format is /*+ index(SUBQUERY_NAME TABLE_NAME INDEX_NAME) */

Debug a simple PK update SQL takes 500+ seconds in Oracle

We run into a problem recently in Oracle database at Solaris. A simple PK update SQL takes 500+ seconds to finish. It didn’t happen all the time, less than 1% chances. It causes app timeout when it happens.

We observed more wait time on “SQL*Net Message to client” in statspack data, no other obviously abnormal events beside this. We suspect it’s caused by network issue. SA told us the network usage is only 20%~30%. We did ping test from db host to client. It didn’t show any obviously problems.

Later, we did a test to switch 2 DB’s host in a db family. It makes the problem moved to the other host. At this point, we are sure it’s not DB problem. It must be host or network issue. Later we found the “TCP retransmission” rate is high at the problematic host. Finally, SA changed the network interface to fix this issue.

We can check the “TCP retransmission” using below scripts:

while true loop
do
 echo -n "`date` : "
 netstat -s -P tcp | grep tcpRetrans
 sleep 1
done;

You can reference this webpage for more about the TCP retrasmission issue.

Enqueue Waits in Oracle Database 10g

Enqueue waits events usually causes problems in busy Oracle databases. But oracle online document only have some of them until 11gR2, not sure why. I paste these information here for quick reference.

In Oracle Database 10g Release 1, each enqueue type is represented by its own wait event, making it much easier to understand exactly what type of enqueue the session is waiting for. You do not need to decipher the values from the P1, P2, P3, P1RAW, P2RAW, and P3RAW columns in the V$SESSION_WAIT or the V$SESSION view.

The following table lists all the enqueue waits in Oracle Database 10g Release 1 and describes what the enqueue is for. This information is available in the X$KSQST structure. The aggregated statistics for each of these enqueue types is displayed by the view V$ENQUEUE_STAT.

Enqueue Type Description
enq: AD – allocate AU Synchronizes accesses to a specific OSM (Oracle Software Manager) disk AU
enq: AD – deallocate AU Synchronizes accesses to a specific OSM disk AU
enq: AF – task serialization Serializes access to an advisor task
enq: AG – contention Synchronizes generation use of a particular workspace
enq: AO – contention Synchronizes access to objects and scalar variables
enq: AS – contention Synchronizes new service activation
enq: AT – contention Serializes alter tablespace operations
enq: AW – AW$ table lock Allows global access synchronization to the AW$ table (analytical workplace tables used in OLAP option)
enq: AW – AW generation lock Gives in-use generation state for a particular workspace
enq: AW – user access for AW Synchronizes user accesses to a particular workspace
enq: AW – AW state lock Row lock synchronization for the AW$ table
enq: BR – file shrink Lock held to prevent file from decreasing in physical size during RMAN backup
enq: BR – proxy-copy Lock held to allow cleanup from backup mode during an RMAN proxy-copy backup
enq: CF – contention Synchronizes accesses to the controlfile
enq: CI – contention Coordinates cross-instance function invocations
enq: CL – drop label Synchronizes accesses to label cache when dropping a label
enq: CL – compare labels Synchronizes accesses to label cache for label comparison
enq: CM – gate Serializes access to instance enqueue
enq: CM – instance Indicates OSM disk group is mounted
enq: CT – global space management Lock held during change tracking space management operations that affect the entire change tracking file
enq: CT – state Lock held while enabling or disabling change tracking to ensure that it is enabled or disabled by only one user at a time
enq: CT – state change gate 2 Lock held while enabling or disabling change tracking in RAC
enq: CT – reading Lock held to ensure that change tracking data remains in existence until a reader is done with it
enq: CT – CTWR process start/stop Lock held to ensure that only one CTWR (Change Tracking Writer, which tracks block changes and is initiated by the alter database enable block change tracking command) process is started in a single instance
enq: CT – state change gate 1 Lock held while enabling or disabling change tracking in RAC
enq: CT – change stream ownership Lock held by one instance while change tracking is enabled to guarantee access to thread-specific resources
enq: CT – local space management Lock held during change tracking space management operations that affect just the data for one thread
enq: CU – contention Recovers cursors in case of death while compiling
enq: DB – contention Synchronizes modification of database wide supplemental logging attributes
enq: DD – contention Synchronizes local accesses to ASM (Automatic Storage Management) disk groups
enq: DF – contention Enqueue held by foreground or DBWR when a datafile is brought online in RAC
enq: DG – contention Synchronizes accesses to ASM disk groups
enq: DL – contention Lock to prevent index DDL during direct load
enq: DM – contention Enqueue held by foreground or DBWR to synchronize database mount/open with other operations
enq: DN – contention Serializes group number generations
enq: DP – contention Synchronizes access to LDAP parameters
enq: DR – contention Serializes the active distributed recovery operation
enq: DS – contention Prevents a database suspend during LMON reconfiguration
enq: DT – contention Serializes changing the default temporary table space and user creation
enq: DV – contention Synchronizes access to lower-version Diana (PL/SQL intermediate representation)
enq: DX – contention Serializes tightly coupled distributed transaction branches
enq: FA – access file Synchronizes accesses to open ASM files
enq: FB – contention Ensures that only one process can format data blocks in auto segment space managed tablespaces
enq: FC – open an ACD thread LGWR opens an ACD thread
enq: FC – recover an ACD thread SMON recovers an ACD thread
enq: FD – Marker generation Synchronization
enq: FD – Flashback coordinator Synchronization
enq: FD – Tablespace flashback on/off Synchronization
enq: FD – Flashback on/off Synchronization
Enqueue Type Description
enq: FG – serialize ACD relocate Only 1 process in the cluster may do ACD relocation in a disk group
enq: FG – LGWR redo generation enq race Resolves race condition to acquire Disk Group Redo Generation Enqueue
enq: FG – FG redo generation enq race Resolves race condition to acquire Disk Group Redo Generation Enqueue
enq: FL – Flashback database log Synchronizes access to Flashback database log
enq: FL – Flashback db command Synchronizes Flashback Database and deletion of flashback logs
enq: FM – contention Synchronizes access to global file mapping state
enq: FR – contention Begins recovery of disk group
enq: FS – contention Synchronizes recovery and file operations or synchronizes dictionary check
enq: FT – allow LGWR writes Allows LGWR to generate redo in this thread
enq: FT – disable LGWR writes Prevents LGWR from generating redo in this thread
enq: FU – contention Serializes the capture of the DB feature, usage, and high watermark statistics
enq: HD – contention Serializes accesses to ASM SGA data structures
enq: HP – contention Synchronizes accesses to queue pages
enq: HQ – contention Synchronizes the creation of new queue IDs
enq: HV – contention Lock used to broker the high watermark during parallel inserts
enq: HW – contention Lock used to broker the high watermark during parallel inserts
enq: IA – contention Information not available
enq: ID – contention Lock held to prevent other processes from performing controlfile transaction while NID is running
enq: IL – contention Synchronizes accesses to internal label data structures
Enqueue Type Description
enq: IM – contention for blr Serializes block recovery for IMU txn
enq: IR – contention Synchronizes instance recovery
enq: IR – contention2 Synchronizes parallel instance recovery and shutdown immediate
enq: IS – contention Synchronizes instance state changes
enq: IT – contention Synchronizes accesses to a temp object’s metadata
enq: JD – contention Synchronizes dates between job queue coordinator and slave processes
enq: JI – contention Lock held during materialized view operations (such as refresh, alter) to prevent concurrent operations on the same materialized view
enq: JQ – contention Lock to prevent multiple instances from running a single job
enq: JS – contention Synchronizes accesses to the job cache
enq: JS – coord post lock Lock for coordinator posting
enq: JS – global wdw lock Lock acquired when doing wdw ddl
enq: JS – job chain evaluate lock Lock when job chain evaluated for steps to create
enq: JS – q mem clnup lck Lock obtained when cleaning up q memory
enq: JS – slave enq get lock2 Gets run info locks before slv objget
enq: JS – slave enq get lock1 Slave locks exec pre to sess strt
enq: JS – running job cnt lock3 Lock to set running job count epost
enq: JS – running job cnt lock2 Lock to set running job count epre
enq: JS – running job cnt lock Lock to get running job count
enq: JS – coord rcv lock Lock when coord receives msg
enq: JS – queue lock Lock on internal scheduler queue
enq: JS – job run lock – synchronize Lock to prevent job from running elsewhere
enq: JS – job recov lock Lock to recover jobs running on crashed RAC inst
Enqueue Type Description
enq: KK – context Lock held by open redo thread, used by other instances to force a log switch
enq: KM – contention Synchronizes various Resource Manager operations
enq: KP – contention Synchronizes kupp process startup
enq: KT – contention Synchronizes accesses to the current Resource Manager plan
enq: MD – contention Lock held during materialized view log DDL statements
enq: MH – contention Lock used for recovery when setting Mail Host for AQ e-mail notifications
enq: ML – contention Lock used for recovery when setting Mail Port for AQ e-mail notifications
enq: MN – contention Synchronizes updates to the LogMiner dictionary and prevents multiple instances from preparing the same LogMiner session
enq: MR – contention Lock used to coordinate media recovery with other uses of datafiles
enq: MS – contention Lock held during materialized view refresh to set up MV log
enq: MW – contention Serializes the calibration of the manageability schedules with the Maintenance Window
enq: OC – contention Synchronizes write accesses to the outline cache
enq: OL – contention Synchronizes accesses to a particular outline name
enq: OQ – xsoqhiAlloc Synchronizes access to olapi history allocation
enq: OQ – xsoqhiClose Synchronizes access to olapi history closing
enq: OQ – xsoqhistrecb Synchronizes access to olapi history globals
enq: OQ – xsoqhiFlush Synchronizes access to olapi history flushing
enq: OQ – xsoq*histrecb Synchronizes access to olapi history parameter CB
enq: PD – contention Prevents others from updating the same property
enq: PE – contention Synchronizes system parameter updates
Enqueue Type Description
enq: PF – contention Synchronizes accesses to the password file
enq: PG – contention Synchronizes global system parameter updates
enq: PH – contention Lock used for recovery when setting proxy for AQ HTTP notifications
enq: PI – contention Communicates remote Parallel Execution Server Process creation status
enq: PL – contention Coordinates plug-in operation of transportable tablespaces
enq: PR – contention Synchronizes process startup
enq: PS – contention Parallel Execution Server Process reservation and synchronization
enq: PT – contention Synchronizes access to ASM PST metadata
enq: PV – syncstart Synchronizes slave start_shutdown
enq: PV – syncshut Synchronizes instance shutdown_slvstart
enq: PW – prewarm status in dbw0 DBWR0 holds this enqueue indicating pre-warmed buffers present in cache
enq: PW – flush prewarm buffers Direct Load needs to flush prewarmed buffers if DBWR0 holds this enqueue
enq: RB – contention Serializes OSM rollback recovery operations
enq: RF – synch: per-SGA Broker metadata Ensures r/w atomicity of DG configuration metadata per unique SGA
enq: RF – synchronization: critical ai Synchronizes critical apply instance among primary instances
enq: RF – new AI Synchronizes selection of the new apply instance
enq: RF – synchronization: chief Anoints 1 instance’s DMON (Data Guard Broker Monitor) as chief to other instance’s DMONs
enq: RF – synchronization: HC master Anoints 1 instance’s DMON as health check master
enq: RF – synchronization: aifo master Synchronizes critical apply instance failure detection and failover operation
enq: RF – atomicity Ensures atomicity of log transport setup
Enqueue Type Description
enq: RN – contention Coordinates nab computations of online logs during recovery
enq: RO – contention Coordinates flushing of multiple objects
enq: RO – fast object reuse Coordinates fast object reuse
enq: RP – contention Enqueue held when resilvering is needed or when data block is repaired from mirror
enq: RS – file delete Lock held to prevent file from accessing during space reclamation
enq: RS – persist alert level Lock held to make alert level persistent
enq: RS – write alert level Lock held to write alert level
enq: RS – read alert level Lock held to read alert level
enq: RS – prevent aging list update Lock held to prevent aging list update
enq: RS – record reuse Lock held to prevent file from accessing while reusing circular record
enq: RS – prevent file delete Lock held to prevent deleting file to reclaim space
enq: RT – contention Thread locks held by LGWR, DBW0, and RVWR (Recovery Writer, used in Flashback Database operations) to indicate mounted or open status
enq: SB – contention Synchronizes logical standby metadata operations
enq: SF – contention Lock held for recovery when setting sender for AQ e-mail notifications
enq: SH – contention Enqueue always acquired in no-wait mode; should seldom see this contention
enq: SI – contention Prevents multiple streams table instantiations
enq: SK – contention Serialize shrink of a segment
enq: SQ – contention Lock to ensure that only one process can replenish the sequence cache
enq: SR – contention Coordinates replication / streams operations
enq: SS – contention Ensures that sort segments created during parallel DML operations aren’t prematurely cleaned up
Enqueue Type Description
enq: ST – contention Synchronizes space management activities in dictionary-managed tablespaces
enq: SU – contention Serializes access to SaveUndo Segment
enq: SW – contention Coordinates the ‘alter system suspend’ operation
enq: TA – contention Serializes operations on undo segments and undo tablespaces
enq: TB – SQL Tuning Base Cache Update Synchronizes writes to the SQL Tuning Base Existence Cache
enq: TB – SQL Tuning Base Cache Load Synchronizes writes to the SQL Tuning Base Existence Cache
enq: TC – contention Lock held to guarantee uniqueness of a tablespace checkpoint
enq: TC – contention2 Lock during setup of a unique tablespace checkpoint in null mode
enq: TD – KTF dump entries KTF dumping time/scn mappings in SMON_SCN_TIME table
enq: TE – KTF broadcast KTF broadcasting
enq: TF – contention Serializes dropping of a temporary file
enq: TL – contention Serializes threshold log table read and update
enq: TM – contention Synchronizes accesses to an object
enq: TO – contention Synchronizes DDL and DML operations on a temp object
enq: TQ – TM contention TM access to the queue table
enq: TQ – DDL contention DDL access to the queue table
enq: TQ – INI contention TM access to the queue table
enq: TS – contention Serializes accesses to temp segments
enq: TT – contention Serializes DDL operations on tablespaces
enq: TW – contention Lock held by one instance to wait for transactions on all instances to finish
Enqueue Type Description
enq: TX – contention Lock held by a transaction to allow other transactions to wait for it
enq: TX – row lock contention Lock held on a particular row by a transaction to prevent other transactions from modifying it
enq: TX – allocate ITL entry Allocating an ITL entry in order to begin a transaction
enq: TX – index contention Lock held on an index during a split to prevent other operations on it
enq: UL – contention Lock held used by user applications
enq: US – contention Lock held to perform DDL on the undo segment
enq: WA – contention Lock used for recovery when setting watermark for memory usage in AQ notifications
enq: WF – contention Enqueue used to serialize the flushing of snapshots
enq: WL – contention Coordinates access to redo log files and archive logs
enq: WP – contention Enqueue to handle concurrency between purging and baselines
enq: XH – contention Lock used for recovery when setting No Proxy Domains for AQ HTTP notifications
enq: XR – quiesce database Lock held during database quiesce
enq: XR – database force logging Lock held during database force logging mode
enq: XY – contention Lock used by Oracle Corporation for internal testing

Reference : Oracle Wait Interface: A Practical Guide to Performance Diagnostics & Tuning

We can get the description of p1, p2 and p3 at v$event_name. You can also get them from p1text, p2text and p3text from v$session if the event is happening right now.

I pasted the enq event here for reference:

NAME PARAMETER1 PARAMETER2 PARAMETER3
enq: AD – allocate AU name|mode group and disk number AU number
enq: AD – deallocate AU name|mode group and disk number AU number
enq: AF – task serialization name|mode task id 0
enq: AG – contention name|mode workspace # generation
enq: AM – client registration name|mode id1 id2
enq: AM – rollback COD reservation name|mode id1 id2
enq: AM – shutdown name|mode id1 id2
enq: AO – contention name|mode workspace # object #
enq: AS – modify service name|mode 0 0
enq: AS – service activation name|mode 0 0
enq: AT – contention name|mode 0 0
enq: AU – audit index file name|mode XML audit index file 0
enq: AW – AW generation lock name|mode operation workspace #
enq: AW – AW state lock name|mode operation workspace #
enq: AW – AW$ table lock name|mode operation workspace #
enq: AW – user access for AW name|mode operation workspace #
enq: BF – PMON Join Filter cleanup name|mode node#/parallelizer# bloom#
enq: BF – allocation contention name|mode node#/parallelizer# bloom#
enq: BR – file shrink name|mode operation file #
enq: BR – proxy-copy name|mode operation file #
enq: CF – contention name|mode 0 operation
enq: CI – contention name|mode opcode type
enq: CL – compare labels name|mode object # 0
enq: CL – drop label name|mode object # 0
enq: CM – gate name|mode disk group # type
enq: CM – instance name|mode disk group # type
enq: CN – race with init name|mode reg id 0
enq: CN – race with reg name|mode reg id 0
enq: CN – race with txn name|mode reg id 0
enq: CT – CTWR process start/stop name|mode operation operation parm
enq: CT – change stream ownership name|mode operation operation parm
enq: CT – global space management name|mode operation operation parm
enq: CT – local space management name|mode operation operation parm
enq: CT – reading name|mode operation operation parm
enq: CT – state name|mode operation operation parm
enq: CT – state change gate 1 name|mode operation operation parm
enq: CT – state change gate 2 name|mode operation operation parm
enq: CU – contention name|mode handle handle
enq: DB – contention name|mode EnqMode 0
enq: DD – contention name|mode disk group type
enq: DF – contention name|mode 0 file #
enq: DG – contention name|mode disk group type
enq: DL – contention name|mode object # 0
enq: DM – contention name|mode type type
enq: DN – contention name|mode 0 0
enq: DP – contention name|mode 0 0
enq: DR – contention name|mode 0 0
enq: DS – contention name|mode 0 0
enq: DT – contention name|mode 0 0
enq: DV – contention name|mode object # 0
enq: DX – contention name|mode transaction entry # 0
enq: FA – access file name|mode disk group number file number
enq: FB – contention name|mode tablespace # dba
enq: FC – open an ACD thread name|mode disk group thread
enq: FC – recover an ACD thread name|mode disk group thread
enq: FD – Flashback coordinator name|mode Internal Internal
enq: FD – Flashback on/off name|mode Internal Internal
enq: FD – Marker generation name|mode Internal Internal
enq: FD – Restore point create/drop name|mode Internal Internal
enq: FD – Tablespace flashback on/off name|mode Internal Internal
enq: FG – FG redo generation enq race name|mode disk group type
enq: FG – LGWR redo generation enq race name|mode disk group type
enq: FG – serialize ACD relocate name|mode disk group type
enq: FL – Flashback database log name|mode Log # zero
enq: FL – Flashback db command name|mode Log # zero
enq: FM – contention name|mode 0 0
enq: FP – global fob contention name|mode low file obj add high file obj add
enq: FR – contention name|mode disk group unused
enq: FS – contention name|mode 0 type
enq: FT – allow LGWR writes name|mode disk group thread
enq: FT – disable LGWR writes name|mode disk group thread
enq: FU – contention name|mode 0 0
enq: HD – contention name|mode disk group 0
enq: HP – contention name|mode tablespace # dba
enq: HQ – contention name|mode object # hash value
enq: HV – contention name|mode object # 0
enq: HW – contention name|mode table space # block
enq: IA – contention name|mode 0 0
enq: ID – contention name|mode 0 0
enq: IL – contention name|mode object # 0
enq: IM – contention for blr name|mode pool # 0
enq: IR – contention name|mode 0 0/1
enq: IR – contention2 name|mode 0 0/1
enq: IS – contention name|mode 0 type
enq: IT – contention name|mode object # 0
enq: JD – contention name|mode 0 0
enq: JI – contention name|mode view object # 0
enq: JQ – contention name|mode 0 0
enq: JS – contention name|mode service ID queue type
enq: JS – evt notify name|mode service ID queue type
enq: JS – evtsub add name|mode service ID queue type
enq: JS – evtsub drop name|mode service ID queue type
enq: JS – job recov lock name|mode service ID queue type
enq: JS – job run lock – synchronize name|mode service ID queue type
enq: JS – q mem clnup lck name|mode service ID queue type
enq: JS – queue lock name|mode service ID queue type
enq: JS – sch locl enqs name|mode service ID queue type
enq: JS – wdw op name|mode service ID queue type
enq: KK – context name|mode 0 redo thread
enq: KM – contention name|mode type type
enq: KO – fast object checkpoint name|mode 2 0
enq: KP – contention name|mode 0 0
enq: KT – contention name|mode plan # 0
enq: MD – contention name|mode master object # 0
enq: MH – contention name|mode 0 0
enq: MK – contention name|mode 0 0
enq: ML – contention name|mode 0 0
enq: MN – contention name|mode session ID 0
enq: MO – contention name|mode 0 0
enq: MR – contention name|mode 0 or file # type
enq: MS – contention name|mode master object # 0
enq: MW – contention name|mode Schedule Id 0
enq: OC – contention name|mode 1 2
enq: OL – contention name|mode hash value 0
enq: OQ – xsoq*histrecb name|mode resource id 0
enq: OQ – xsoqhiAlloc name|mode resource id 0
enq: OQ – xsoqhiClose name|mode resource id 0
enq: OQ – xsoqhiFlush name|mode resource id 0
enq: OQ – xsoqhistrecb name|mode resource id 0
enq: OW – initialization name|mode 0 0
enq: OW – termination name|mode 0 0
enq: PD – contention name|mode property name key hash
enq: PE – contention name|mode parno 0
enq: PF – contention name|mode 0 0
enq: PG – contention name|mode 0 0
enq: PH – contention name|mode 0 0
enq: PI – contention name|mode operation serial #
enq: PL – contention name|mode 0 0
enq: PR – contention name|mode 0 0
enq: PS – contention name|mode instance slave ID
enq: PT – contention name|mode disk group # type
enq: PV – syncshut name|mode 0 0
enq: PV – syncstart name|mode 0 0
enq: PW – flush prewarm buffers name|mode 0 0
enq: PW – perwarm status in dbw0 name|mode 0 0
enq: RB – contention name|mode disk group 0
enq: RF – RF – Database Automatic Disable name|mode lock operation lock value
enq: RF – RF – FSFO Observed name|mode lock operation lock value
enq: RF – RF – FSFO connectivity name|mode lock operation lock value
enq: RF – RF – FSFO state name|mode lock operation lock value
enq: RF – RF – FSFO synchronization name|mode lock operation lock value
enq: RF – RF – FSFO wait name|mode lock operation lock value
enq: RF – atomicity name|mode lock operation lock value
enq: RF – new AI name|mode lock operation lock value
enq: RF – synch: DG Broker metadata name|mode lock operation lock value
enq: RF – synchronization: HC master name|mode lock operation lock value
enq: RF – synchronization: aifo master name|mode lock operation lock value
enq: RF – synchronization: chief name|mode lock operation lock value
enq: RF – synchronization: critical ai name|mode lock operation lock value
enq: RN – contention name|mode thread number log number
enq: RO – contention name|mode 2 0
enq: RO – fast object reuse name|mode 2 0
enq: RP – contention name|mode file # 1 or block
enq: RR – contention name|mode lock# not used
enq: RS – file delete name|mode record type record id
enq: RS – persist alert level name|mode record type record id
enq: RS – prevent aging list update name|mode record type record id
enq: RS – prevent file delete name|mode record type record id
enq: RS – read alert level name|mode record type record id
enq: RS – record reuse name|mode record type record id
enq: RS – write alert level name|mode record type record id
enq: RT – contention name|mode redo thread type
enq: RU – contention name|mode 0 0
enq: RU – waiting name|mode 0 0
enq: RW – MV metadata contention name|mode table obj# 0
enq: SB – contention name|mode 0 0
enq: SE – contention name|mode Session-id Serial#
enq: SF – contention name|mode 0 0
enq: SH – contention name|mode 0 0
enq: SI – contention name|mode object # 0
enq: SK – contention name|mode tablespace # dba
enq: SQ – contention name|mode object # 0
enq: SR – contention name|mode operation sequence # / apply #
enq: SS – contention name|mode tablespace # dba
enq: ST – contention name|mode 0 0
enq: SU – contention name|mode table space # 0
enq: SW – contention name|mode 0 0
enq: TA – contention name|mode operation undo segment # / other
enq: TB – SQL Tuning Base Cache Load name|mode 1 2
enq: TB – SQL Tuning Base Cache Update name|mode 1 2
enq: TC – contention name|mode checkpoint ID 0
enq: TC – contention2 name|mode checkpoint ID 0
enq: TD – KTF dump entries name|mode 0 0
enq: TE – KTF broadcast name|mode 0 0
enq: TF – contention name|mode tablespace # relative file #
enq: TL – contention name|mode 0 0
enq: TM – contention name|mode object # table/partition
enq: TO – contention name|mode object # 1
enq: TQ – DDL contention name|mode QT_OBJ# 0
enq: TQ – INI contention name|mode QT_OBJ# 0
enq: TQ – TM contention name|mode QT_OBJ# 0
enq: TS – contention name|mode tablespace ID dba
enq: TT – contention name|mode tablespace ID operation
enq: TW – contention name|mode 0 operation
enq: TX – allocate ITL entry name|mode usn<<16 | slot sequence
enq: TX – contention name|mode usn<<16 | slot sequence
enq: TX – index contention name|mode usn<<16 | slot sequence
enq: TX – row lock contention name|mode usn<<16 | slot sequence
enq: UL – contention name|mode id 0
enq: US – contention name|mode undo segment # 0
enq: WA – contention name|mode 0 0
enq: WF – contention name|mode 0 0
enq: WL – contention name|mode log # / thread id # sequence #
enq: WP – contention name|mode 0 0
enq: WR – contention name|mode thread id # sequence #
enq: XH – contention name|mode 0 0
enq: XQ – recovery name|mode disk group # unused
enq: XQ – relocation name|mode disk group # unused
enq: XR – database force logging name|mode operation 0
enq: XR – quiesce database name|mode operation 0
enq: XY – contention name|mode id1 id2
enq: ZG – contention name|mode file group id version id

For the P1 values which is “name|mode”, we can decode it like this:

select chr(bitand(p1,-16777216)/16777215)||chr(bitand(p1, 16711680)/65535) "Name",
bitand(p1, 65535) "Mode"
from v$session;