Tuning Scripts collection

1. get how many session using specific sql_id
2. get execution plan history and its changes using sql_id
3. check sql of active session and it execution plan.

1. get how many session using specific sql_id

select count(*) from v$session where sql_id ='&sql_id';

select ses.sid,ses.value,stat.name from V$SESSTAT ses,V$STATNAME stat
 where ses.statistic#=stat.statistic#
 and sid in ( select sid from v$session where sql_id = '&sql_id')
 and ses.value  0
 order by 1,2 desc
/

2. get execution plan history and its changes using sql_id.
–execution plan

set lines 200;
set pages 300;
select sql_id, PLAN_HASH_VALUE, to_char(timestamp,'DD-MON-YYYY HH24:MI:SS')
from DBA_HIST_SQL_PLAN where sql_id='$sql_id'
/
set lines 200;
set pages 300;
SELECT xx.* FROM DBA_HIST_SQLTEXT yy, table
(DBMS_XPLAN.DISPLAY_AWR(yy.sql_id,null, null, 'ALL' )) xx
WHERE yy.sql_id='705ydchr6zaq4'
/

3. check sql of active session and it execution plan.

SET LINESIZE 145
SET PAGESIZE 9999
COLUMN sid               FORMAT 99999      HEADING 'SID'
COLUMN serial_id         FORMAT 99999999   HEADING 'Serial ID'
COLUMN session_status    FORMAT a9         HEADING 'Status'          JUSTIFY right
COLUMN oracle_username   FORMAT a14        HEADING 'Oracle User'     JUSTIFY right
COLUMN os_username       FORMAT a12        HEADING 'O/S User'        JUSTIFY right
COLUMN os_pid            FORMAT 9999999    HEADING 'O/S PID'         JUSTIFY right
COLUMN session_program   FORMAT a26        HEADING 'Session Program' TRUNC
COLUMN session_terminal  FORMAT a10        HEADING 'Terminal'        JUSTIFY right
COLUMN session_machine   FORMAT a19        HEADING 'Machine'         JUSTIFY right

prompt 
prompt +----------------------------------------------------+
prompt | Active User Sessions (All)                         |
prompt +----------------------------------------------------+

SELECT
    s.sid                sid
  , s.serial#            serial_id
  , lpad(s.status,9)     session_status
  , lpad(s.username,14)  oracle_username
  , lpad(s.osuser,12)    os_username
  , lpad(p.spid,7)       os_pid
  , s.program            session_program
  , lpad(s.terminal,10)  session_terminal
  , lpad(s.machine,19)   session_machine
FROM
    v$process p
  , v$session s
WHERE
      p.addr (+) = s.paddr
  AND s.username IS NOT null
ORDER BY sid
/
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
/

--oracle 9i

SET LINESIZE 145
SET PAGESIZE 9999
COLUMN sid               FORMAT 99999      HEADING 'SID'
COLUMN serial_id         FORMAT 99999999   HEADING 'Serial ID'
COLUMN session_status    FORMAT a9         HEADING 'Status'          JUSTIFY right
COLUMN oracle_username   FORMAT a14        HEADING 'Oracle User'     JUSTIFY right
COLUMN os_username       FORMAT a12        HEADING 'O/S User'        JUSTIFY right
COLUMN os_pid            FORMAT 9999999    HEADING 'O/S PID'         JUSTIFY right
COLUMN session_program   FORMAT a26        HEADING 'Session Program' TRUNC
COLUMN session_terminal  FORMAT a10        HEADING 'Terminal'        JUSTIFY right
COLUMN session_machine   FORMAT a19        HEADING 'Machine'         JUSTIFY right

prompt 
prompt +----------------------------------------------------+
prompt | Active User Sessions (All)                         |
prompt +----------------------------------------------------+

SELECT
    s.sid                sid
  , s.serial#            serial_id
  , lpad(s.status,9)     session_status
  , lpad(s.username,14)  oracle_username
  , lpad(s.osuser,12)    os_username
  , lpad(p.spid,7)       os_pid
  , s.program            session_program
  , lpad(s.terminal,10)  session_terminal
  , lpad(s.machine,19)   session_machine
FROM
    v$process p
  , v$session s
WHERE
      p.addr (+) = s.paddr
  AND s.username IS NOT null
ORDER BY sid
/

CLEAR COLUMN
COLUMN  sql_hash_value  NEW_VALUE sql_hash_value
COLUMN  sql_address     NEW_VALUE sql_address


SELECT  s.sql_hash_value, s.sql_address
FROM    v$session s
WHERE   sid = &1
/


COLUMN "Rows" FORMAT a6
COLUMN "Plan" FORMAT a68 wrap
COLUMN id FORMAT a4 justify right

SET PAGESIZE 500

WITH pt AS (
        SELECT  *
        FROM    v$sql_plan p
        WHERE   p.hash_value = '&sql_hash_value'
        AND     p.address = '&sql_address'
)
SELECT  xid AS id
,       plan AS "Plan"
,       rws AS "Rows"
FROM (
        SELECT  decode(access_predicates || filter_predicates, NULL, ' ', '*') ||
                lpad(id, 3, ' ') AS xid
        ,       lpad(' ',depth-1)||operation||' '|| options||' '||object_name
                || decode(partition_start, NULL, NULL, ' ' || partition_start || ':' || partition_stop)
                AS plan
        ,       lpad(
                        CASE
                                WHEN cardinality > 1000000
                                THEN to_char(trunc(cardinality/1000000)) || 'M'
                                WHEN cardinality > 1000
                                THEN to_char(trunc(cardinality/1000)) || 'K'
                                ELSE cardinality || ' '
                        END
                ,       6
                ,       ' '
                ) AS rws
        ,       id
        FROM    pt
        ORDER BY id
)
UNION ALL
SELECT  NULL
,       chr(10) || 'Access Predicates' || chr(10) || '------------------------'
,       NULL
FROM    dual
UNION ALL
SELECT  to_char(id)
,       access_predicates
,       NULL
FROM    pt
WHERE   access_predicates IS NOT NULL
UNION ALL
SELECT  NULL
,       chr(10) || 'Filter Predicates' || chr(10) || '------------------------'
,       NULL
FROM    dual
UNION ALL
SELECT  to_char(id)
,       filter_predicates
,       NULL
FROM    pt
WHERE   filter_predicates IS NOT NULL
/
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s