Oracle Top 20 Parallel SQL

SELECT * FROM
(SELECT substr(sql_text,1,100) sql,
ELAPSED_TIME, CPU_TIME,
disk_reads, executions,
disk_reads/executions "Reads/Exec",
hash_value,address
FROM V$SQLAREA
WHERE ( hash_value, address ) in (
SELECT distinct HASH_VALUE, address
FROM v$sql_plan
WHERE DISTRIBUTION is not null )
and disk_reads > 1000
ORDER BY ELAPSED_TIME DESC)
WHERE rownum <= 20;

Warning: Product user profile information not loaded!

SQL> connect scott/XXXX
Error accessing PRODUCT_USER_PROFILE
Warning: Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM
Connected.
SQL> connect system/XXXX
Connected.


SQL> @$ORACLE_HOME/sqlplus/admin/pupbld.sql

Difference between v$sql v$sqltext v$sqltext_with_newlines

v$sql : if you have multiple copies of the query:

select * from x

if user A and B are executing (in shared pool ) above query then we will have 2 rows in v$sql

v$sqlarea:- aggregate of v$sql. It selects out DISTINCT sql.select * from x
will appear there.

v$sqltext:- for full text

v$sqltext_with_newlines: - without the white space replacement and tab.

Database Point-in-Time Recovery RECOVER DATABASE UNTIL TIME

oracle only accept this command at the time of time base recovery (following format, delimited by single quotation marks 'YYYY-MM-DD:HH24:MI:SS' )

Performs a incomplete recovery (=Point in time recovery).

The format of the time is 'YYYY-MM-DD:HH24:MI:SS'


in sql prompt


SQL>recover automatic database until time '2010-05-09:10:45:00';

SQL>recover database UNTIL TIME '2010-05-08:08:42:00';





Pl don't try or waste your time in following date format at the time of Point in Time Recovery

to_date," " etc ...


SQL> alter session set nls_date_format='YYYY-MM-DD:HH24:MI:SS' ;

Session altered.

SQL> RECOVER DATABASE UNTIL TIME to_date('2010-05-08:08:42:00','YYYY-MM-DD:HH24:MI:SS');
ORA-00285: TIME not given as a string constant


SQL> RECOVER DATABASE UNTIL TIME "to_date('2010-05-08:08:42:00','YYYY-MM-DD:HH24:MI:SS')";
ORA-00972: identifier is too long


SQL> RECOVER DATABASE UNTIL TIME to_date('2010-05-08:08:42:00','YYYY-MM-DD:HI24:MI:SS');

ORA-00285: TIME not given as a string constant


SQL> RECOVER DATABASE UNTIL TIME "to_date('2010-05-08:08:42:00','YYYY-MM-DD:HI24:MI:SS')";

ORA-00972: identifier is too long


SQL> RECOVER DATABASE UNTIL TIME to_date('2010-05-08:08:42:00','YYYY-MM-DD:HI24:MI:SS') ;

ORA-00285: TIME not given as a string constant


SQL> ALTER SESSION SET NLS_DATE_FORMAT='DD/MM/YYYY HH24:MI:SS';

Session altered.

SQL> recover database UNTIL TIME "TO_DATE('08/05/2010 08:42:00','DD/MM/YYYY HH24:MI:SS')";

ORA-00972: identifier is too long


SQL> recover database UNTIL TIME TO_DATE('08/05/2010 08:42:00','DD/MM/YYYY HH24:MI:SS');
ORA-00285: TIME not given as a string constant

RECOVER DATABASE UNTIL TIME 'to_date('2006-12-11:10:35:00','YYYY-MM-DD:HH24:MI:SS')'

oracle 10g current SQL Monitor Query

from Web



SELECT /* SQL Monitor Query for >= 10g */
'(' || s.SID || ',' || s.serial# || ')' "sid/serial",
s.program "pgrm", s.terminal "term", s.username "db user",
DECODE (ROUND (s.last_call_et / 60),'0', '< 1',ROUND (s.last_call_et / 60)) "rtime (mins)",
DECODE (ps."px oper cnt", '', 'N/A', ps."px oper cnt") "px opers",
DECODE (ps."px count", '', 'N/A', ps."px count") "px slaves",
si.block_changes "sess bchgs", si.physical_reads "sess preads",
pss_pr."px preads" "child px preads",
si.consistent_gets "sess cgets", pss_cg."px cgets" "child px cgets",
ss_cpu."sess cpu" "sess cpu", pss_cpu."px cpu" "child px cpu",
sa.optimizer_cost "curr sql cost", su."blocks" "temp blocks",
t."used_ublk" "undo blocks", s.event "wait",
s.seconds_in_wait "wait secs", s.state "wait state",
s.sql_id "current sql id", sa.sql_fulltext "sql text",
DBMS_XPLAN.display_cursor (s.sql_id, s.sql_child_number) "sql xplan"
FROM v$session s,
v$sess_io si,
v$sql sa,
(SELECT qcsid, COUNT (DISTINCT server_set) "px oper cnt", COUNT (*) "px count"
FROM v$px_session
WHERE NOT server_set IS NULL
GROUP BY qcsid, DEGREE) ps,
(SELECT qcsid, SUM (VALUE) "px preads"
FROM v$px_sesstat
WHERE statistic# = 54 AND SID != qcsid
GROUP BY qcsid) pss_pr,
(SELECT qcsid, SUM (VALUE) "px cgets"
FROM v$px_sesstat pss
WHERE statistic# = 50 AND SID != qcsid
GROUP BY qcsid) pss_cg,
(SELECT qcsid, SUM (VALUE) "px cpu"
FROM v$px_sesstat pss
WHERE statistic# = 12 AND SID != qcsid
GROUP BY qcsid) pss_cpu,
(SELECT ss.SID, SUM (ss.VALUE) "sess cpu"
FROM v$sesstat ss
WHERE statistic# = 12
GROUP BY ss.SID) ss_cpu,
(SELECT t.ses_addr, SUM (t.used_ublk) "used_ublk"
FROM v$transaction t
GROUP BY t.ses_addr) t,
(SELECT su.session_addr, SUM (su.blocks) "blocks"
FROM v$sort_usage su
GROUP BY su.session_addr) su
WHERE s.sql_address = sa.address
AND s.sql_hash_value = sa.hash_value
AND s.saddr = su.session_addr(+)
AND s.SID = ps.qcsid(+)
AND s.SID = si.SID(+)
AND s.saddr = t.ses_addr(+)
AND s.SID = pss_pr.qcsid(+)
AND s.SID = pss_cg.qcsid(+)
AND s.SID = pss_cpu.qcsid(+)
AND s.SID = ss_cpu.SID(+)
AND s.TYPE != 'BACKGROUND'
AND s.status = 'ACTIVE'
AND program NOT LIKE ('%(C%') --Eliminate Streams Capture
AND program NOT LIKE ('%(A%') --Eliminate Streams Apply
AND program NOT LIKE ('%(P%') --Eliminate Parallel Slaves
ORDER BY sa.optimizer_cost DESC;

oracle 10g find large full scans SQL and objects

SELECT sql_id,sql_text,
total_large_scans,
executions,
executions * total_large_scans sum_large_scans
FROM
(SELECT c.sql_id sql_id, sql_text,
count(*) total_large_scans,
executions
FROM sys.v_$sql_plan a,
sys.dba_segments b,
sys.v_$sql c
WHERE a.object_owner (+) = b.owner
AND a.object_name (+) = b.segment_name
AND b.segment_type IN ('TABLE', 'TABLE PARTITION')
AND a.operation LIKE '%TABLE%'
AND a.options = 'FULL'
AND c.hash_value = a.hash_value
AND b.bytes / 1024 > 1024
and PARSING_SCHEMA_NAME not in ('SYS','SYSTEM')
group by c.sql_id, sql_text, executions)
order by 4 desc
/





col TABLE_OWNER format a12

select sql_id,
table_owner,
table_name,
table_type,
size_kb,
statement_count,
reference_count,
executions,
executions * reference_count total_scans
from
(select c.sql_id sql_id,
a.object_owner table_owner,
a.object_name table_name,
b.segment_type table_type,
b.bytes / 1024 size_kb,
sum(c.executions ) executions,
count( distinct a.hash_value ) statement_count,
count( * ) reference_count
from
sys.v_$sql_plan a,
sys.dba_segments b,
sys.v_$sql c
where a.object_owner (+) = b.owner
and a.object_name (+) = b.segment_name
and b.segment_type in ('TABLE', 'TABLE PARTITION')
and a.operation like '%TABLE%'
and a.options = 'FULL'
and a.hash_value = c.hash_value
and b.bytes / 1024 > 1024
and PARSING_SCHEMA_NAME not in ('SYS','SYSTEM')
-- and a.object_owner not in ('SYS','SYSTEM')
and a.object_owner not in ('SYS','SYSTEM','PERFSTAT','SYSMAN','WKSYS')
group by c.sql_id,a.object_owner, a.object_name, a.operation, b.bytes / 1024, b.segment_type
order by 4 desc, 1, 2 );

oracle tablespace cron job

-bash-3.00$ cat /export/home/oracle/scripts/space.sh
#!/bin/bash
export ORACLE_BASE=/opt/oracle
export ORACLE_HOME=/opt/oracle/product/10.2
export PATH=$ORACLE_HOME/bin:.:$PATH
export ORACLE_SID=cccdb

sqlplus -s /nolog <connect / as sysdba
set pagesize 100 lines 120
set feed off heading off
break on REPORT;
compute sum label 'Total' of SIZE_MB on REPORT;
compute sum label 'Total Free MB' of FREE_MB on REPORT;
compute sum label 'Total Used MB' of USED_MB on REPORT;
COLUMN dummy NOPRINT
COLUMN pct_used FORMAT 999.9 HEADING "%|Used"
COLUMN name FORMAT a21 HEADING "Tablespace Name"
COLUMN Kbytes FORMAT 999,999,999 HEADING "MBytes"
COLUMN used FORMAT 999,999,999 HEADING "Used"
COLUMN free FORMAT 999,999,999 HEADING "Free"
COLUMN largest FORMAT 999,999,999 HEADING "Largest"
COLUMN maxsize FORMAT 999,999,999 HEADING "MAXSIZE"
BREAK ON report
COMPUTE sum OF kbytes ON REPORT
COMPUTE sum OF free ON REPORT
COMPUTE sum OF used ON REPORT

spool tablespace1.alert
select '********************************************' from dual;
select 'TABLESPACE REPORT1 PRODUCTION DATABASE ' from dual;
select to_char(sysdate, 'Dy DD-Mon-YYYY HH24:MI:SS') as "Report Time" from dual;
select '********************************************' from dual;
set feed on heading on
SELECT
NVL(b.tablespace_name,nvl(a.tablespace_name,'UNKOWN')) name
, kbytes_alloc/1024 kbytes
, nvl(kbytes_alloc/1024,0) - NVL(kbytes_free/1024,0) used
, NVL(kbytes_free,0) free
, ((kbytes_alloc-NVL(kbytes_free,0))/kbytes_alloc)*100 pct_used
, NVL(largest,0) largest
,MAXBYTES/1024/1024 MAXSIZE
,AUTOEXTENSIBLE
FROM ( SELECT SUM(bytes)/1024 Kbytes_free
, MAX(bytes)/1024 largest
, tablespace_name
FROM sys.dba_free_space
GROUP BY tablespace_name
) a
, ( SELECT SUM(bytes)/1024 Kbytes_alloc
, tablespace_name,AUTOEXTENSIBLE,MAXBYTES
FROM sys.dba_data_files
GROUP BY tablespace_name,AUTOEXTENSIBLE,MAXBYTES
) b
WHERE a.tablespace_name (+) = b.tablespace_name
order by 1;

spool off
exit
EOF
if [ `cat tablespace1.alert|wc -l` -gt 0 ]
then
# echo "test"
cat tablespace1.alert > tablespace1.tmp
# mailx -s "TABLESPACE ALERT for Production Database" -r oracle@joshua anuj.@tus.co.uk < tablespace1.tmp
#/usr/lib/sendmail -s "TABLESPACE ALERT1 for Joshua" anuj.singh@pus.co.uk < tablespace1.tmp

#cat tablespace.tmp | mailx -s "mail test" anuj.s@ptus.co.uk
fi