学步园

how to have one session id in oracle:
SQL> select sys_context('USERENV','sid') from dual;
HOw to have the lock of this session
select s.osuser,l.id1,l.id2,l.lmode,l.request,l.block,l.type
from v$session s, v$lock l
where s.sid=l.sid
and s.sid=sys_context('USERENV','sid') ;

select s.osuser,l.id1,l.id2,l.lmode,l.request,l.block,l.type
from v$session s, v$lock l
where s.sid=l.sid
and s.username='CUSTOMER';

How to have redo size of one statement:
SELECT s.VALUE
FROM v$mystat s, v$statname n
WHERE s.STATISTIC#=n.STATISTIC#
AND n.NAME='redo size';

How to identify which program, which session invoke a query:

SELECT user#,username,machine, program,s.sql_id FROM gv$session s ,

gv$sql t

WHERE s.sql_address=t.ADDRESS

AND s.sql_hash_value=t.HASH_VALUE

AND t.SQL_ID='569quxwafwxv9';

How to identify which query the process invoke:

select a.username, a.osuser, a.program, spid, sid, a.serial#

from gv$session a, gv$process b

where a.paddr = b.addr

and spid = '&pid';

How to open cursor for the process(using top have the sid and serial#)

select b.username 'USERNAME', a.sql_text 'SQL_TEXT'

from gv$open_cursor a, gv$session b

where b.sql_address =a.address

and b.sql_hash_value = a. hash_value

and b.sid = &sid

and b.serial# ='&serial';

How to see the execution plan of one sql(be aware of the difference of gv$sql and gv$sqlarea, eg.gv$sql,gv$sqlarea,gv$sqltext)

SELECT P.ID,LPAD('  ',2*ID)||P.OPERATION,P.OBJECT_NAME NAME,P.CARDINALITY ROWSS,P.BYTES,P.COST,P.CPU_COST,P.TIME

FROM GV$SQL S, 

GV$SQL_PLAN P

WHERE S.SQL_ID=P.SQL_ID

AND S.CHILD_NUMBER=P.CHILD_NUMBER

AND S.SQL_ID='2hhj92u2qtzhp';

select * from table(dbms_xplan.display_cursor('2hhj92u2qtzhp',1));

How to see a query is working:

select sess_io.sid,
       sess_io.block_gets,
       sess_io.consistent_gets,
       sess_io.physical_reads,
       sess_io.block_changes,
       sess_io.consistent_changes
  from v$sess_io sess_io, v$session sesion
 where sesion.sid = sess_io.sid
   and sesion.username is not null

How to check the value of a bind variable in sql:

select sesion.sid,
       sesion.username,
       sesion.sql_id,
       sesion.sql_child_number,
       sql_bind_capture.name,
       sql_bind_capture.value_string
  from v$sql_bind_capture sql_bind_capture, v$session sesion
 where sesion.sql_hash_value = sql_bind_capture.hash_value
   and sesion.sql_address    = sql_bind_capture.address
   and sesion.username is not null

Extracting the optimizer environment settings for SQL that is executing
select sesion.sid,
       sesion.username,
       name,
       isdefault,
       value
  from v$sql_optimizer_env sql_optimizer_env, v$session sesion
 where sesion.sql_hash_value = sql_optimizer_env.hash_value
   and sesion.sql_address    = sql_optimizer_env.address
   and sesion.username is not null

Extracting the statistics for a single execution of a SQL statement
select sesion.sid,
       sesion.username,
       sql_plan_statistics.operation_id        "Id",
       sql_plan_statistics.last_output_rows    "Rows",
       sql_plan_statistics.last_cr_buffer_gets "Consistent Gets",
       sql_plan_statistics.last_disk_reads     "Disk Reads"
  from v$sql_plan_statistics sql_plan_statistics, v$session sesion
 where sesion.sql_hash_value = sql_plan_statistics.hash_value
   and sesion.sql_address    = sql_plan_statistics.address
   and sesion.username is not null