Oracle high physical reads Sessions wise

SQL> r

1 select

2 OSUSER os_user,username,

3 PROCESS pid,

4 ses.SID sid,

5 SERIAL#,

6 PHYSICAL_READS,

7 BLOCK_CHANGES

8 from v$session ses,

9 v$sess_io sio

10 where ses.SID = sio.SID

11 and username is not null

12 and status='ACTIVE'

13* order by PHYSICAL_READS



OS_USER USERNAME PID SID SERIAL# PHYSICAL_READS BLOCK_CHANGES

---------- --------------- ------------------------------------ ---------- ---------- -------------- -------------

oracle SYS 18038 112 1457 0 2

oracle SYS 18040 120 12352 0 2

oracle SYS 18032 128 24796 0 2

oracle SYS 18030 138 49309 0 2

oracle SYS 18034 135 63569 0 2

oracle SYS 18028 126 15159 0 2

oracle SYS 18036 146 22857 0 2

oracle SYS 18042 124 4489 7 10

oracle SYS 25381 131 54920 1516947 5

oracle SYS 17904 129 29110 15857587 47



10 rows selected.

Oracle Count Big table

How to count (*) big table in oracle ?

if 2 cpu then we can put no of parallel process 2 X 4  = 8 


select /*+ parallel (ttable, 8) */ count(*) from &table_name ttable ;



select /*+ parallel (ttable, 8) */ count(*) from anuj.user_site ttable ;



  COUNT(*)

----------

 280973975



Elapsed: 00:02:10.96

set linesize 200 time on timing on 
define table_name='XXX.XXX' ----- owner.table 
col human_readable for a15
select /*+ parallel (ttable, 50) opt_param('parallel_execution_enabled', 'true') */ count(*) count, dbms_xplan.format_number(count(*)) human_readable from &table_name ttable ;

cpu info 



col STAT_NAME 	for a30
col VALUE 	    for a15
SELECT STAT_NAME, TO_CHAR (VALUE) AS VALUE, COMMENTS   FROM v$osstat
WHERE stat_name IN ('NUM_CPUS', 'NUM_CPU_CORES', 'NUM_CPU_SOCKETS')
UNION
SELECT STAT_NAME, trunc(VALUE / 1024 / 1024 / 1024) || ' GB', COMMENTS   FROM v$osstat
WHERE stat_name IN ('PHYSICAL_MEMORY_BYTES');

====

col param_name 			format a40 heading 	"Parameter Name"
col param_value 		format a38 heading 	"Parameter Value" word_wrap
col isdefault 			format a7 heading 	"Default|Value"

col isses_modifiable 	format a10 heading 	"Session|Modifiable"
col issys_modifiable 	format a10 heading 	"System|Modifiable"
col ismod                          heading 	"Is|Modified"


set linesize 500
col DISPLAY_VALUE for a20 
col DEFAULT_VALUE for a20 
col VALUE for a20
col UPDATE_COMMENT for a20
col DESCRIPTION for a20 
col NAME for a35
 select *
 from v$parameter
 where name in ('cpu_count', 'parallel_threads_per_cpu', 'parallel_degree_policy', 'parallel_degree_limit');

                                                                                                                    Default Session    System
       NUM NAME                                      TYPE VALUE                DISPLAY_VALUE        DEFAULT_VALUE        Value   Modifiable Modifiable ISPDB ISINS ISMODIFIED ISADJ ISDEP ISBAS DESCRIPTION          UPDATE_COMMENT             HASH     CON_ID
---------- ----------------------------------- ---------- -------------------- -------------------- -------------------- ------- ---------- ---------- ----- ----- ---------- ----- ----- ----- -------------------- -------------------- ---------- ----------
       111 cpu_count                                    3 16                   16                   0                    TRUE    FALSE      IMMEDIATE  TRUE  TRUE  FALSE      FALSE FALSE FALSE number of CPUs for t                      1095434542          3
                                                                                                                                                                                                his instance

      3392 parallel_degree_policy                       2 AUTO                 AUTO                 MANUAL               FALSE   TRUE       IMMEDIATE  TRUE  TRUE  FALSE      FALSE FALSE FALSE policy used to compu                      1979359697          3
                                                                                                                                                                                                te the degree of par
                                                                                                                                                                                                allelism (MANUAL/LIM
                                                                                                                                                                                                ITED/AUTO/ADAPTIVE)

      3403 parallel_threads_per_cpu                     3 2                    2                    2                    TRUE    FALSE      IMMEDIATE  FALSE TRUE  FALSE      FALSE FALSE FALSE number of parallel e                      1020981983          3
                                                                                                                                                                                                xecution threads per
                                                                                                                                                                                                 CPU

      3776 parallel_degree_limit                        2 CPU                  CPU                  CPU                  TRUE    TRUE       IMMEDIATE  TRUE  TRUE  FALSE      FALSE FALSE FALSE limit placed on degr                      3004872640          3
                                                                                                                                                                                                ee of parallelism

===

Parallel_index Hint Is Not Honored (Doc ID 2294720.1)

he index is not partitioned.
So, you cannot use the parallel_index hint.

Oracle Temp segment usage per session

set linesize 300 pagesize 300
col kill for a15
SELECT ''''||a.sid ||','|| a.serial#||',@'||a.inst_id ||'''' kill,a.sql_id,a.username,  c.spid Process,b.tablespace tablespace, a.status, sum(b.extents)* 1024*1024 space from  gv$session a,gv$sort_usage b, gv$process c, dba_tablespaces d
where    a.saddr  = b.session_addr
and      a.inst_id  = b.inst_id
and      a.paddr  = c.addr
and      a.inst_id  = c.inst_id
and      b.tablespace=d.tablespace_name
group by a.username, a.osuser, ''''||a.sid ||','|| a.serial#||',@'||a.inst_id ||'''',a.sql_id,c.spid,b.tablespace, a.status;


set linesize 300 pagesize 300
col kill  for a15
col sql_text for a60 wrap
select ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill, S.username,s.sql_id,T.blocks * TBS.block_size/1024/1024 mb_used, T.tablespace,T.sqladdr address, Q.hash_value, Q.sql_text from gv$sort_usage t, gv$session s, gv$sqlarea q, dba_tablespaces tbs
where 1=1
and t.session_addr = s.saddr
and t.inst_id = s.inst_id
and t.sqladdr = q.address (+)
and t.tablespace = tbs.tablespace_name
order by s.sid;


set linesize 300 pagesize 300
col kill for a15
col username for a20
col program for a20
col tablespace for a12
select * from (select ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill ,s.sql_id,s.PREV_SQL_ID,u.tablespace, s.username, s.logon_time, program, u.extents, ((u.blocks*8)/1024) as MB,i.inst_id,i.host_name from gv$session s, gv$sort_usage u ,gv$instance i
where s.saddr=u.session_addr
and u.inst_id=i.inst_id
and s.username not in ( 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' , 'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' )
order by mb desc) a
where rownum <20;

Oracle TEMP tablespace usage

temp space info
temp usage
Temporary tablespace usage
all about temp tablespace
temp space usage



SYS AS SYSDBA>!cat temp.sql

col tablespace_name format a20

spool tempsp.txt
select sysdate dtstamp,
s.tablespace_name,
d.tbspc_mb,
s.total_blocks*8192/1024/1024 temp_tot_mb,
s.used_blocks*8192/1024/1024 temp_used_mb,
s.free_blocks*8192/1024/1024 temp_free_mb
from v$sort_segment s,
(select tablespace_name,sum(bytes/1024/1024) tbspc_mb
from dba_data_files
group by tablespace_name
union
select tablespace_name,sum(bytes/1024/1024) tbspc_mb
from dba_temp_files
group by tablespace_name) d
where s.tablespace_name=d.tablespace_name;

col sid format a10
col username format a12
col tablespace format a15

select s.sid || ',' || s.serial# sid,
s.username,
u.tablespace,
a.sql_text,
round(((u.blocks*p.value)/1024/1024),2) size_mb
from v$sort_usage u,
v$session s,
v$sqlarea a,
v$parameter p
where s.saddr = u.session_addr
and a.address (+) = s.sql_address
and a.hash_value (+) = s.sql_hash_value
and p.name = 'db_block_size'
and s.username != 'SYSTEM'
group by
s.sid || ',' || s.serial#,
s.username,
a.sql_text,
u.tablespace,
round(((u.blocks*p.value)/1024/1024),2);

or

compute sum of mb on report
compute sum of pct on report
col sid format a10 heading "Session ID"
col username format a10 heading "User Name"
col sql_text format a8 heading "SQL"
col tablespace format a10 heading "Temporary|TS Name"
col mb format 999,999,990 heading "Mbytes|Used"
col pct format 990.00 heading "% Avail|TS Spc"

select s.sid || ',' || s.serial# sid,
s.username,
u.tablespace,
substr(a.sql_text, 1, (instr(a.sql_text, ' ')-1)) sql_text,
u.blocks/128 mb,
((u.blocks/128)/(sum(f.blocks)/128))*100 pct,
s.sql_id
from v$sort_usage u,
v$session s,
v$sqlarea a,
dba_data_files f
where s.saddr = u.session_addr
and a.address (+) = s.sql_address
and a.hash_value (+) = s.sql_hash_value
and f.tablespace_name = u.tablespace
group by
s.sid || ',' || s.serial#,
s.username,
substr(a.sql_text, 1, (instr(a.sql_text, ' ')-1)),
u.tablespace,
u.blocks/128,
s.sql_id




-----

select (s.tot_used_blocks/f.total_blocks)*100 as "percent used"
from (select sum(used_blocks) tot_used_blocks from v$sort_segment) s,
(select sum(blocks) total_blocks from dba_temp_files) f;

spool off


SELECT A.tablespace_name tablespace, D.mb_total,SUM (A.used_blocks * D.block_size)/1024/1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size)/1024/1024 mb_free
FROM v$sort_segment A,(SELECT B.name, C.block_size, SUM (C.bytes) /1024 /1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size ) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;


TABLESPACE MB_TOTAL MB_USED MB_FREE
-------------------- ---------- ---------- ----------
TEMP 13240 1 13239




col SID_SERIAL format a12
col PROGRAM format a15
col OSUSER format a10
col MODULE format a10
col SPID format a10
col USERNAME format a10
col SQL_ID format a14
SELECT S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
P.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
COUNT(*) statements,s.sql_id
FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE T.session_addr = S.saddr
AND S.paddr = P.addr
AND T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,P.program, TBS.block_size, T.tablespace,s.sql_id
ORDER BY sid_serial;

SID_SERIAL USERNAME OSUSER SPID MODULE PROGRAM MB_USED TABLESPACE STATEMENTS SQL_ID
------------ ---------- ---------- ---------- ---------- --------------- ---------- -------------------- ---------- --------------
138,3 PROD1 admin 28520 documentum oracle@co 1 TEMP 1
@dc ratst
tm-01tst (
TNS V1-V3)



SELECT b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid, a.serial#,
a.username, a.osuser, a.status

FROM v$session a,v$sort_usage b

WHERE a.saddr = b.session_addr

ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks;


TABLESPACE SEGFILE# SEGBLK# BLOCKS SID SERIAL# USERNAME OSUSER STATUS
-------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ------------------------
TEMP 201 9 128 138 3 PROD1 admin INACTIVE




col USERNAME format a20
col TABLESPACE format a20

SELECT SE.USERNAME,
SE.SID,
se.serial#,
SU.EXTENTS,
(SU.BLOCKS * TO_NUMBER(RTRIM(P.VALUE)))/1024/1024 AS SPACE,
TABLESPACE,
SEGTYPE
FROM V$SORT_USAGE SU, V$PARAMETER P, V$SESSION SE
WHERE P.NAME = 'db_block_size'
AND SU.SESSION_ADDR = SE.SADDR
ORDER BY SE.USERNAME, SE.SID




set linesize 200

col user format a14

select USER,SESSION_NUM,sortu.TABLESPACE,sysdate,sql_text,sorts,sqla.sql_id

from v$sort_usage sortu,v$sqlarea sqla

where upper (tablespace) like 'TEMP%'

and sortu.SQLHASH=sqla.HASH_VALUE ;


USER SESSION_NUM TABLESPACE SYSDATE SQL_TEXT SORTS SQL_ID

-------------- ----------- -------------------- --------------- ---------------------------------------------------------------------------------------------------- ---------- --------------

SYS 3 TEMP 03-OCT-11 SELECT dm_dbalias_B.R_OBJECT_ID FROM prod1.DM_SYSOBJECT_S dm_dbalias_B WHERE (dm_dbalias_B.R_OBJ 0 5q7x0kvp3u19r

ECT_ID=:objectp AND dm_dbalias_B.I_VSTAMP=:versionp)


============

from web


Temporary tablespace usage


In database :

CREATE TABLE "AVAIL"."MONITOR_SORTSEG"
( "TABLESPACE_NAME" VARCHAR2(31),
"USED_EXTENTS" NUMBER,
"FREE_EXTENTS" NUMBER,
"ISSUE_TIME" DATE);

In OS : (monitor_temp.sh)

#!/bin/ksh
SCRIPT=1; export SCRIPT
. ~orfnprd/.profile
$ORACLE_HOME/bin/sqlplus -s "/ as sysdba" <<\EOF
set serveroutput on
begin
insert into monitor_sortseg
select tablespace_name,USED_EXTENTS,FREE_EXTENTS,sysdate
from v\$sort_segment where upper(tablespace_name)='TEMP' and free_extents<=500;
if(SQL%ROWCOUNT<>0) then
dbms_output.put_line('Start -------- Start Date:'||sysdate);
dbms_output.put_line('TEMP free extent is lower than 50 .... Finding SQL statement');
insert into monitor_sortuse select USER,SESSION_NUM,sortu.TABLESPACE,
sysdate,sql_text,sorts from v\$sort_usage sortu,v\$sqlarea sqla where upper
(tablespace)='TEMP' and sortu.SQLHASH=sqla.HASH_VALUE ;
commit;
dbms_output.put_line('End ----------End Date:'||sysdate);
end if;
end;
/
exit;
EOF


0,5,10,15,20,25,30,40,45,50,55 * * * * sample_temp.sh > sample_temp.log 2>&1


====





temp tablespace usage


COLUMN username HEADING "UserName" FORMAT a30
COLUMN osuser HEADING "OS User" FORMAT A10
COLUMN qcsid HEADING "QC" FORMAT 9999
COLUMN sid HEADING "SID" FORMAT 9999
COLUMN segtype HEADING "Segment|Type" FORMAT A10
COLUMN sql_id HEADING "SQL ID" FORMAT A15

CLEAR BREAKS
COMPUTE SUM OF sum_blocks ON qcsid
COMPUTE SUM OF sum_blocks ON osuser
COMPUTE SUM OF sum_blocks ON report
COMPUTE SUM OF sum_mb ON qcsid
COMPUTE SUM OF sum_mb ON osuser
COMPUTE SUM OF sum_mb ON report
BREAK ON username ON osuser ON qcsid ON report

SELECT s.username
, s.osuser
, ps.qcsid
, s.sid
, su.segtype
, su.sql_id
, SUM(su.blocks) sum_blocks
, ROUND(SUM(su.blocks) * dt.block_size / (1024 * 1024) ) sum_mb
FROM v$sort_usage su
, v$session s
, v$px_session ps
, dba_tablespaces dt
WHERE s.sid = ps.sid(+)
AND s.saddr = su.session_addr
AND s.serial# = su.session_num
AND su.tablespace = dt.tablespace_name
GROUP BY s.username
, s.osuser
, s.sid
, ps.qcsid
, su.contents
, su.segtype
, su.sqladdr
, su.sqlhash
, su.sql_id
, dt.block_size
ORDER BY s.username
, s.osuser
, ps.qcsid
, su.sqladdr
, s.sid
/



SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size)/1024/1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size)/1024/1024 mb_free
FROM v$sort_segment A,
(
SELECT B.name, C.block_size, SUM (C.bytes)/1024/1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;