The simple example of unpivot.

Unpivot provides very conveninent way, when we search on v$sql_shared_cursor view.
When, we want to know the reason of multiple child cursors, we look at the v$sql_shared_cursor view. But, that view isn’t user-friendly. In this case, we can use unpivot feature from oracle 11g like this. The oracle version of test environment is 11.2.0.3.

-- fnd_shared_cursor.sql
 
WITH v1 AS (
          SELECT *
          FROM   v$sql_shared_cursor
          WHERE  sql_id=:sql_id 
     )
SELECT sql_id ,
       child_number ,
       reason_type ,
       result
FROM   v1
unpivot (result FOR reason_type IN
(
-- columns are vary depend on oracle version
"UNBOUND_CURSOR",
"SQL_TYPE_MISMATCH",
"OPTIMIZER_MISMATCH",
"OUTLINE_MISMATCH",
"STATS_ROW_MISMATCH",
"LITERAL_MISMATCH",
"FORCE_HARD_PARSE",
"EXPLAIN_PLAN_CURSOR",
"BUFFERED_DML_MISMATCH",
"PDML_ENV_MISMATCH",
"INST_DRTLD_MISMATCH",
"SLAVE_QC_MISMATCH",
"TYPECHECK_MISMATCH",
"AUTH_CHECK_MISMATCH",
"BIND_MISMATCH",
"DESCRIBE_MISMATCH",
"LANGUAGE_MISMATCH",
"TRANSLATION_MISMATCH",
"BIND_EQUIV_FAILURE",
"INSUFF_PRIVS",
"INSUFF_PRIVS_REM",
"REMOTE_TRANS_MISMATCH",
"LOGMINER_SESSION_MISMATCH",
"INCOMP_LTRL_MISMATCH",
"OVERLAP_TIME_MISMATCH",
"EDITION_MISMATCH",
"MV_QUERY_GEN_MISMATCH",
"USER_BIND_PEEK_MISMATCH",
"TYPCHK_DEP_MISMATCH",
"NO_TRIGGER_MISMATCH",
"FLASHBACK_CURSOR",
"ANYDATA_TRANSFORMATION",
"PDDL_ENV_MISMATCH",
"TOP_LEVEL_RPI_CURSOR",
"DIFFERENT_LONG_LENGTH",
"LOGICAL_STANDBY_APPLY",
"DIFF_CALL_DURN",
"BIND_UACS_DIFF",
"PLSQL_CMP_SWITCHS_DIFF",
"CURSOR_PARTS_MISMATCH",
"STB_OBJECT_MISMATCH",
"CROSSEDITION_TRIGGER_MISMATCH",
"PQ_SLAVE_MISMATCH",
"TOP_LEVEL_DDL_MISMATCH",
"MULTI_PX_MISMATCH",
"BIND_PEEKED_PQ_MISMATCH",
"MV_REWRITE_MISMATCH",
"ROLL_INVALID_MISMATCH",
"OPTIMIZER_MODE_MISMATCH",
"PX_MISMATCH",
"MV_STALEOBJ_MISMATCH",
"FLASHBACK_TABLE_MISMATCH",
"LITREP_COMP_MISMATCH",
"PLSQL_DEBUG",
"LOAD_OPTIMIZER_STATS",
"ACL_MISMATCH",
"FLASHBACK_ARCHIVE_MISMATCH",
"LOCK_USER_SCHEMA_FAILED",
"REMOTE_MAPPING_MISMATCH",
"LOAD_RUNTIME_HEAP_FAILED",
"HASH_MATCH_FAILED",
"PURGED_CURSOR",
"BIND_LENGTH_UPGRADEABLE",
"USE_FEEDBACK_STATS")
)
where result='Y';
 
--Sample Output
SQL_ID        CHILD_NUMBER  REASON_TYPE          RESULT
c9q44tap900dw 1             EXPLAIN_PLAN_CURSOR  Y
c9q44tap900dw 2             EXPLAIN_PLAN_CURSOR  Y