Session, SQL, SID and Cursor Scripts
This script gets information about all database sessions
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
SET LINESIZE 500 SET PAGESIZE 1000SET FEEDBACK OFF SET FEEDBACK OFF SELECT Substr(a.username,1,15) "Username", a.osuser "OS User", a.sid "Session ID", a.serial# "Serial No", d.spid "Process ID", a.lockwait "LockWait", a.status "Status", Trunc(b.value/1024) "PGA (Kb)", Trunc(e.value/1024) "UGA (Kb)", a.module "Module", Substr(a.machine,1,15) "Machine", a.program "Program", Substr(To_Char(a.logon_Time,'DD-Mon-YYYY HH24:MI:SS'),1,20) "Time" FROM v$session a, v$sesstat b, v$statname c, v$process d, v$sesstat e, v$statname f WHERE a.paddr = d.addr AND a.sid = b.sid AND b.statistic# = c.statistic# AND c.name = 'session pga memory' AND a.sid = e.sid AND e.statistic# = f.statistic# AND f.name = 'session uga memory' ORDER BY 1,2; SET PAGESIZE 14 SET FEEDBACK ON |
A query containing V$tatname and time information
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SELECT SUBSTR(v$session.username,1,15), v$session.process,v$session.sid, v$session.serial#, SUBSTR(v$session.program,1,23),v$session.status, v$session.terminal, SUBSTR(v$statname.name,1,30), ROUND(((v$timer.hsecs - v$sesstat.VALUE)/100/60/60),2) HOURS FROM v$timer,v$statname,v$sesstat,v$session WHERE v$statname.statistic# = v$sesstat.statistic# AND v$sesstat.statistic# IN (13,14) AND v$sesstat.sid = v$session.sid ORDER BY v$session.username,v$session.sid,v$sesstat.statistic#; |
A script that shows who the objects in a schema are accessing
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SET SERVEROUTPUT ON SET PAGESIZE 1000 SET LINESIZE 255 SET VERIFY OFFSELECT Substr(a.object,1,30) object, a.type, a.sid, b.username, b.osuser, b.program FROM v$access a, v$session b WHERE a.sid = b.sid AND a.owner = Upper('&1'); PROMPT SET PAGESIZE 18 |
Script showing all the connected users
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT Substr(a.object,1,30) object, set lines 100 pages 999 col ID format a15 select username , sid || ',' || serial# "ID" , status , last_call_et "Last Activity" from v$session where username is not null order by status desc , last_call_et desc ; |
To display how long users have been active
|
1 2 3 4 5 6 7 8 |
SELECT Substr(a.object,1,30) object, set lines 100 pages 999 select username , floor(last_call_et / 60) "Minutes" , status from v$session where username is not null order by last_call_et ; |
A script that lists according to the time user log on time
|
1 2 3 4 5 6 7 8 9 10 |
SELECT Substr(a.object,1,30) object, select username , osuser , sid || ',' || serial# "ID" , status , to_char(logon_time, 'hh24:mi dd/mm/yy') login_time , last_call_et from v$session where username is not null order by login_time |
Script to retrieve SID (Session ID), Serial and OS Process ID
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
col "SID/SERIAL" format a10 col username format a15 col osuser format a15 col program format a40 select s.sid || ',' || s.serial# "SID/SERIAL" , s.username , s.osuser , p.spid "OS PID" , s.program from v$session s , v$process p Where s.paddr = p.addr order by to_number(p.spid) |
A script to retrieve the sql statement that a specific user is running at that moment
|
1 2 3 4 5 6 |
Select sql_text from v$sqlarea where (address, hash_value) in (select sql_address, sql_hash_value from v$session where username like '&username') |
To retrieve information that the DBMS has about an entered OS Process ID
|
1 2 3 4 5 6 7 8 9 10 |
select s.username , s.sid , s.serial# , p.spid , last_call_et , status from V$SESSION s , V$PROCESS p where s.PADDR = p.ADDR and p.spid='&pid' |
To retrieve all currently active SQL
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
set feedback off set serveroutput on size 9999 column username format a20 column sql_text format a55 word_wrapped begin for x in (select username||'('||sid||','||serial#||') ospid = '|| process || ' program = ' || program username, to_char(LOGON_TIME,' Day HH24:MI') logon_time, to_char(sysdate,' Day HH24:MI') current_time, sql_address, sql_hash_value from v$session where status = 'ACTIVE' and rawtohex(sql_address) '00' and username is not null ) loop for y in (select sql_text from v$sqlarea where address = x.sql_address ) loop if ( y.sql_text not like '%listener.get_cmd%' and y.sql_text not like '%RAWTOHEX(SQL_ADDRESS)%' ) then dbms_output.put_line( '--------------------' ); dbms_output.put_line( x.username ); dbms_output.put_line( x.logon_time || ' ' || x.current_time || ' SQL#=' || x.sql_hash_value); dbms_output.put_line( substr( y.sql_text, 1, 250 ) ); end if; end loop; end loop; end; |
List all the transactions taking a long time
|
1 2 3 4 5 6 7 8 9 10 11 |
set lines 100 pages 999 col username format a15 col message format a40 col remaining format 9999 select username , to_char(start_time, 'hh24:mi:ss dd/mm/yy') started , time_remaining remaining , message from v$session_longops where time_remaining = 0 order by time_remaining desc; |
Listing all of the users’ opened cursors
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
set pages 999 select sess.username , sess.sid , sess.serial# , stat.value cursors from v$sesstat stat , v$statname sn , v$session sess where sess.username is not null and sess.sid = stat.sid and stat.statistic# = sn.statistic# and sn.name = 'opened cursors current' order by value |
An alternative to the previous script
|
1 2 3 4 5 6 7 8 9 10 |
set lines 100 pages 999 select count(hash_value) cursors , sid , user_name from v$open_cursor group by sid , user_name order by cursors |
Listing the SQL statements that use the most amount of resources
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 |
SET LINESIZE 500 SET PAGESIZE 1000 SET FEEDBACK OFF SET VERIFY OFF SET SERVEROUTPUT ON PROMPT DECLARE CURSOR c_sql IS SELECT Substr(a.sql_text,1,50) sql_text, Trunc(a.disk_reads/Decode(a.executions,0,1,a.executions)) reads_per_execution, a.buffer_gets, a.disk_reads, a.executions, a.sorts, a.address FROM v$sqlarea a ORDER BY 2 DESC; BEGIN Dbms_Output.Enable(1000000); Dbms_Output.Put_Line(Rpad('SQL Text',50,' ') || Lpad('Reads/Execution',16,' ') || Lpad('Buffer Gets',12,' ') || Lpad('Disk Reads',12,' ') || Lpad('Executions',12,' ') || Lpad('Sorts',12,' ') || Lpad('Address',10,' ')); Dbms_Output.Put_Line(Rpad('-',50,'-') || ' ' || Lpad('-',15,'-') || ' ' || Lpad('-',11,'-') || ' ' || Lpad('-',11,'-') || ' ' || Lpad('-',11,'-') || ' ' || Lpad('-',11,'-') || ' ' || Lpad('-',9,'-')); FOR cur_rec IN c_sql LOOP Dbms_Output.Put_Line(Rpad(cur_rec.sql_text,50,' ') || Lpad(cur_rec.reads_per_execution,16,' ') || Lpad(cur_rec.buffer_gets,12,' ') || Lpad(cur_rec.disk_reads,12,' ') || Lpad(cur_rec.executions,12,' ') || Lpad(cur_rec.sorts,12,' ') || Lpad(cur_rec.address,10,' ')); IF c_sql%ROWCOUNT = &&1 THEN EXIT top_sql; END IF; END LOOP; END; / PROMPT SET PAGESIZE 14 SET FEEDBACK ON |
The SQL at the sql_address
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SET LINESIZE 500 SET PAGESIZE 1000 SET FEEDBACK OFF SET VERIFY OFF SELECT a.sql_text "SQL Text" FROM v$sqltext a WHERE a.address = Upper('&&1') ORDER BY a.piece; PROMPT SET PAGESIZE 14 SET FEEDBACK ON -- enter the value at the v$session table's SQL_ADDRESS column |
Jobs that are currently running
|
1 2 3 |
SELECT sid, r.job, log_user, r.this_date, r.this_sec FROM dba_jobs_running r, dba_jobs j WHERE r.job = j.job |
All operations that are taking a long time
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
COLUMN sid FORMAT 999 COLUMN serial# FORMAT 9999999 COLUMN machine FORMAT A30 COLUMN progress_pct FORMAT 99999999.00 COLUMN elapsed FORMAT A10 COLUMN remaining FORMAT A10 SELECT s.sid, s.serial#, s.machine, ROUND(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60) elapsed, ROUND(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60) remaining, ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct FROM v$session s, v$session_longops sl WHERE s.sid = sl.sid AND s.serial# = sl.serial#; |
Getting the SQL statement’s EP and disk information
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
select sql_text , executions , to_char((((disk_reads+buffer_gets)/executions) * 8192)/1048576, '9,999,999,990.00') as total_gets_per_exec_mb , to_char((( disk_reads /executions) * 8192)/1048576, '9,999,999,990.00') as disk_reads_per_exec_mb , to_char((( buffer_gets /executions) * 8192)/1048576, '9,999,999,990.00') as buffer_gets_per_exec_mb , parsing_user_id from v$sqlarea where executions > 10 order by 6 desc / |
All open cursors
|
1 2 3 4 5 6 |
SELECT a.user_name, a.sid, a.sql_text FROM v$open_cursor a ORDER BY 1,2 / |
OS process’ ID the SQL statement that it’s running
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
SET LINESIZE 500 SET PAGESIZE 1000 SET FEEDBACK OFF SELECT s.sid, s.status "Status", p.spid "Process", s.schemaname "Schema Name", s.osuser "OS User", Substr(a.sql_text,1,120) "SQL Text", s.program "Program" FROM v$session s, v$sqlarea a, v$process p WHERE s.sql_hash_value = a.hash_value (+) AND s.sql_address = a.address (+) AND s.paddr = p.addr; SET PAGESIZE 14 SET FEEDBACK ON / |
Displaying the Tree-Style Execution plan
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
COLUMN PLAN FORMAT A50 HEADING "Execution Plan" COLUMN OBJECT_NAME FORMAT A30 HEADING "Object" COLUMN OBJECT_TYPE FORMAT A10 HEADING "Type" COLUMN BYTES FORMAT 9999999999 HEADING "Bytes" COLUMN COST FORMAT 9999999 HEADING "Cost" COLUMN PARTITION_START FORMAT 9999999 HEADING "PStart" COLUMN PARTITION_STOP FORMAT 9999999 HEADING "PEnd" SET PAGESIZE 1000 SET LINESIZE 1000 SET VERIFY OFF SELECT LPAD(' ', 2 * (level - 1)) || Decode (level,1,NULL,level-1 || '.' || pt.position || ' ') || Initcap(pt.operation) || Decode(pt.options,NULL,'',' (' || Initcap(pt.options) || ')') plan, pt.object_name, pt.object_type, pt.bytes, pt.cost, pt.partition_start, pt.partition_stop FROM plan_table pt START WITH pt.id = 0 AND pt.statement_id = '&1' CONNECT BY PRIOR pt.id = pt.parent_id AND pt.statement_id = '&1'; SET VERIFY ON |
An alternative script to retrieve SQL syntaxes, SID (Session IDs), OS User Process IDs
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
SET VERIFY OFF SET LINESIZE 255 COL SID FORMAT 999 COL STATUS FORMAT A8 COL PROCESS FORMAT A10 COL SCHEMANAME FORMAT A16 COL OSUSER FORMAT A16 COL SQL_TEXT FORMAT A120 HEADING 'SQL QUERY' COL PROGRAM FORMAT A30 SELECT s.sid, s.status, s.process, s.schemaname, s.osuser, a.sql_text, p.program FROM v$session s, v$sqlarea a, v$process p WHERE s.SQL_HASH_VALUE = a.HASH_VALUE AND s.SQL_ADDRESS = a.ADDRESS AND s.PADDR = p.ADDR / SET VERIY ON SET LINESIZE 255 |
Another script that outputs running jobs
|
1 2 3 4 5 6 7 8 9 10 11 |
SET LINESIZE 500 SET PAGESIZE 1000 SET VERIFY OFF SELECT a.job "Job", a.sid, a.failures "Failures", Substr(To_Char(a.last_date,'DD-Mon-YYYY HH24:MI:SS'),1,20) "Last Date", Substr(To_Char(a.this_date,'DD-Mon-YYYY HH24:MI:SS'),1,20) "This Date" FROM dba_jobs_running a; SET PAGESIZE 14 SET VERIFY ON |
SQL statements that were run between specified times
|
1 2 3 4 5 6 |
SELECT * FROM V_$SQLAREA WHERE FIRST_LOAD_TIME BETWEEN '2011-01-10/13:00:08' AND '2011-01-10/14:00:22' |
Kategori seçin...