How to find the plan for a particular SQL in the SMB

SELECT sql_text,plan_name,sql_handle,creator,origin,created, last_executed,enabled,accepted,fixed,autopurge,module
FROM   DBA_SQL_PLAN_BASELINES
WHERE  sql_text LIKE '%your sql text%';

Joined with v$sql, to reveal the SQL ID, and with some formatting: find all plans created for a specific SQL ID, including those no longer in the cursor cache:

select  b.SQL_handle,
        b.plan_name,
        b.origin,
        to_char(b.created, 'dd.mm.rrrr hh24:mi') created,
        to_char(b.last_modified, 'dd.mm.rrrr hh24:mi')last_mod, 
        to_char(b.last_executed, 'dd.mm.rrrr hh24:mi') last_exe,
        b.enabled,
        b.accepted, 
        b.fixed,
        b.optimizer_cost,
        b.executions,
        b.cpu_time,
        b.buffer_Gets,
        b.disk_Reads,
        CASE WHEN b.executions > 0 then
        ROUND((b.elapsed_time/b.executions)/1000000,1) 
        ELSE NULL
        END AS "seconds per execution",
        a.sql_id 
FROM DBA_SQL_PLAN_BASELINES b LEFT OUTER JOIN v$sql a
ON (b.plan_name = a.sql_plan_baseline)
WHERE b.sql_text LIKE 'SELECT * FROM( SELECT KLM.VIEWTYPE KLMTYPE , TS%';

Result may look like the following:

SQL_HANDLE	PLAN_NAME	ORIGIN	CREATED	LAST_MOD	LAST_EXE	ENABLED	ACCEPTED	FIXED	OPTIMIZER_COST	EXECUTIONS	CPU_TIME	BUFFER_GETS	DISK_READS	seconds pr execution	SQL_ID
SQL_5bdd86105a3f22a2	SQL_PLAN_5rrc621d3y8p2a8f06e05	MANUAL-LOAD	11.08.2016 14:42	12.08.2016 07:36	12.08.2016 07:36	YES	YES	YES	10948	27	35862546	7299186	297	1,4	5h0syvrgsjf0j
SQL_5bdd86105a3f22a2	SQL_PLAN_5rrc621d3y8p23e45ab6f	MANUAL-LOAD	11.08.2016 14:42	11.08.2016 14:47		NO	YES	NO	30537	1	3549461	434057	5371	10,8
SQL_5bdd86105a3f22a2	SQL_PLAN_5rrc621d3y8p28dd60076	MANUAL-LOAD	11.08.2016 14:42	11.08.2016 15:02		NO	YES	NO	1324	2	18519310636	1131813	0	9253,6

From the output above, we can see that the fixed plan is the most efficient, that's why it was marked as FIXED in the SMB.

For finding a specific plan name:

SELECT plan_name,
         sql_handle,
         creator,
         origin,
         TO_CHAR (created, 'dd.mm.yyyy hh24:mi') "created",
         TO_CHAR (last_executed, 'dd.mm.yyyy hh24:mi') "lastexecuted",
         enabled,
         accepted,
         fixed,
         autopurge,
         module,
         ROUND (ELAPSED_TIME / 1000000) "duration in seconds",
         cpu_time,
         buffer_gets,
         disk_reads
    FROM DBA_SQL_PLAN_BASELINES
WHERE plan_name='SQL_PLAN_asryr8hfgjd5hddf0c98b'

For finding all Plans created today:

SELECT plan_name,
         sql_handle,
         creator,
         origin,
         TO_CHAR (created, 'dd.mm.yyyy hh24:mi') "created",
         TO_CHAR (last_executed, 'dd.mm.yyyy hh24:mi') "lastexecuted",
         enabled,
         accepted,
         fixed,
         autopurge,
         module,
         ROUND (ELAPSED_TIME / 1000000) "duration in seconds",
         cpu_time,
         buffer_gets,
         disk_reads
    FROM DBA_SQL_PLAN_BASELINES
WHERE TO_CHAR (created, 'dd.mm.yyyy') = TRUNC (SYSDATE)