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 nullExtracting 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