How to read a Explain Plan

How to read a Explain Plan

The diference b/w bytes and cardinality is simple.
An optimizer will be defined in the Explain Plan
Based on that optimizer,

COST - The cost of the operation carried out.It will be compared with the standard cost defined in the optimizer
BYTES - estimate of the no. of bytes accessed by the operation
CARDINALITY - estimate of the no. of rows accessed by the operation

sid.sql -- List currently running session IDs

SET LINES 80 LONG 65536

CLEAR column

COLUMN username FORMAT A10 WRAP
COLUMN prog_event FORMAT A35 WRAP
COLUMN run_time FORMAT A10 JUSTIFY RIGHT
COLUMN sid FORMAT A4 NEW_VALUE sid
COLUMN status FORMAT A10

ACCEPT search_string PROMPT "Search for: "

SELECT  to_char(s.sid) AS sid
,       s.username || chr(10) || s.osuser AS username
,       s.status || chr(10) || 'PID:' || p.spid AS status
,       lpad(
                to_char(
                        trunc(24*(sysdate-s.logon_time))
                ) ||
                to_char(
                        trunc(sysdate) + (sysdate-s.logon_time)
                ,       ':MI:SS'
                )
        , 10, ' ') AS run_time
,       s.program ||  chr(10) || s.event AS prog_event
FROM    v$session s
JOIN    v$process p ON (p.addr = s.paddr)
WHERE   s.username <> 'DBSNMP'
AND     audsid != sys_context('USERENV','SESSIONID')
AND     upper(
                s.osuser || '|' ||
                s.program || '|' ||
                s.event || '|' ||
                s.sid || '|' ||
                s.username || '|' ||
                p.spid
        ) LIKE upper('%&search_string.%')
ORDER BY
        sid
/

Only the SELECT statement above is necessary if running from a GUI tool. The other commands and the chr(10) concatenation simply format the output for SQL*Plus.

plans.sql -- List the EXPLAIN PLAN for a currently running session

SELECT  p.plan_table_output
FROM    v$session s
,       table(dbms_xplan.display_cursor(s.sql_id, s.sql_child_number)) p
where   s.sid = &1
/