Oracle find full table scan and Cartesian join

Oracle full table scan

Find the sql and tune , if possible

"full scans are not evil, indexes are not good"




Cartesian Joins
Cartesian join is formed when data is retrieved from multiple tables without writing necessary join conditions

SELECT COUNTRY_NAME, REGION_NAME FROM COUNTRIES, REGIONS;
20 rows in the COUNTRIES table and 4 rows in the REGIONS table
This will process 20*4 = 80 rows





SELECT SQL_TEXT ,
USERNAME ,
DISK_READS_PER_EXEC,
BUFFER_GETS ,
DISK_READS,
PARSE_CALLS ,
SORTS ,
EXECUTIONS ,
ROWS_PROCESSED ,
HIT_RATIO,
FIRST_LOAD_TIME ,
SHARABLE_MEM ,
PERSISTENT_MEM ,
RUNTIME_MEM,
CPU_TIME,
ELAPSED_TIME,
ADDRESS,
HASH_VALUE,
sql_id
FROM
(SELECT SQL_TEXT ,
B.USERNAME ,
ROUND((A.DISK_READS/DECODE(A.EXECUTIONS,0,1,A.EXECUTIONS)),2)
DISK_READS_PER_EXEC,
A.DISK_READS ,
A.BUFFER_GETS ,
A.PARSE_CALLS ,
A.SORTS ,
A.EXECUTIONS ,
A.ROWS_PROCESSED ,
100 - ROUND(100 *
A.DISK_READS/GREATEST(A.BUFFER_GETS,1),2) HIT_RATIO,
A.FIRST_LOAD_TIME ,
SHARABLE_MEM ,
PERSISTENT_MEM ,
RUNTIME_MEM,
CPU_TIME,
ELAPSED_TIME,
ADDRESS,
HASH_VALUE,
sql_id
FROM SYS.V_$SQLAREA A, SYS.ALL_USERS B
WHERE A.PARSING_USER_ID=B.USER_ID AND
-- B.USERNAME NOT IN ('SYS','SYSTEM')
B.USERNAME NOT IN
('ORACLE_OCM',
'SYSMAN',
'WMSYS',
'XDB',
'ANONYMOUS',
'CTXSYS',
'MGMT_VIEW',
'OPS$ROOT',
'OPS$ORACLE',
'DBSNMP',
'OPS$DMADMIN',
'SYS',
'SYSTEM',
'JUDEK',
'MDDATA',
'TSMSYS',
'DIP',
'ORDSYS',
'SI_INFORMTN_SCHEMA',
'ORDPLUGINS',
'OUTLN',
'MDSYS')
ORDER BY 3 DESC)
WHERE ROWNUM < 21





set linesize 200
col sql_text format a100
select sql_id,substr(sql_text,1,100) sql_text from sys.v_$sql
where hash_value in (select hash_value
from sys.v_$sql_plan
where options = 'CARTESIAN'
AND operation LIKE '%JOIN%'
-- and OBJECT_OWNER='PROD1'
)
and PARSING_SCHEMA_NAME not in
('ORACLE_OCM',
'SYSMAN',
'WMSYS',
'XDB',
'ANONYMOUS',
'CTXSYS',
'MGMT_VIEW',
'OPS$ROOT',
'OPS$ORACLE',
'DBSNMP',
'OPS$DMADMIN',
'SYS',
'SYSTEM',
'JUDEK',
'MDDATA',
'TSMSYS',
'DIP',
'ORDSYS',
'SI_INFORMTN_SCHEMA',
'ORDPLUGINS',
'OUTLN',
'MDSYS')
order by hash_value




For All the user

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
group by c.sql_id,sql_text, executions)
order by 4 desc




For not a system user

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 c.PARSING_SCHEMA_NAME not in
('ORACLE_OCM',
'SYSMAN',
'WMSYS',
'XDB',
'ANONYMOUS',
'CTXSYS',
'MGMT_VIEW',
'OPS$ROOT',
'OPS$ORACLE',
'DBSNMP',
'OPS$DMADMIN',
'SYS',
'SYSTEM',
'JUDEK',
'MDDATA',
'TSMSYS',
'DIP',
'ORDSYS',
'SI_INFORMTN_SCHEMA',
'ORDPLUGINS',
'OUTLN',
'MDSYS')
group by c.sql_id,sql_text, executions)
order by 4 desc




col operation for a45
SELECT
LPAD(' ',depth)||P.OPERATION||'_'||P.OPTIONS||' '||P.OBJECT_NAME operation
, last_starts * cardinality e_rows_x_starts
, last_output_rows a_rows
, LAST_CR_BUFFER_GETS bgets
, LAST_DISK_READS pread
, LAST_DISK_WRITES pwrites
, LAST_ELAPSED_TIME elapsed
, LAST_MEMORY_USED
, LAST_TEMPSEG_SIZE
, LAST_EXECUTION
FROM V$SQL_PLAN_statistics_all P
WHERE sql_id='&sql_id'
order by child_number,id
/





col cn format 99
col ratio format 99
col ratio1 format A6
--set pagesize 1000
set linesize 140
break on sql_id on cn
col lio_rw format 999
col "operation" format a60
col a_rows for 999,999,999
col e_rows for 999,999,999
col elapsed for 999,999,999
col TCF_GRAPH format a20
Def v_sql_id=&SQL_ID

select
-- sql_id,
--hv,
childn cn,
--ptime, stime,
case when stime - nvl(ptime ,0) > 0 then
stime - nvl(ptime ,0)
else 0 end as elapsed,
nvl(trunc((lio-nvl(plio,0))/nullif(a_rows,0)),0) lio_ratio,
--id,
--parent_id,
--starts,
--nvl(ratio,0) TCF_ratio,
' '||case when ratio > 0 then
rpad('-',ratio,'-')
else
rpad('+',ratio*-1 ,'+')
end as TCF_GRAPH,
starts*cardinality e_rows,
a_rows,
--nvl(lio,0) lio, nvl(plio,0) parent_lio,
"operation"
from (
SELECT
stats.LAST_ELAPSED_TIME stime,
p.elapsed ptime,
stats.sql_id sql_id
, stats.HASH_VALUE hv
, stats.CHILD_NUMBER childn
, to_char(stats.id,'990')
||decode(stats.access_predicates,null,null,'A')
||decode(stats.filter_predicates,null,null,'F') id
, stats.parent_id
, stats.CARDINALITY cardinality
, LPAD(' ',depth)||stats.OPERATION||' '||
stats.OPTIONS||' '||
stats.OBJECT_NAME||
DECODE(stats.PARTITION_START,NULL,' ',':')||
TRANSLATE(stats.PARTITION_START,'(NRUMBE','(NR')||
DECODE(stats.PARTITION_STOP,NULL,' ','-')||
TRANSLATE(stats.PARTITION_STOP,'(NRUMBE','(NR') "operation",
stats.last_starts starts,
stats.last_output_rows a_rows,
(stats.last_cu_buffer_gets+stats.last_cr_buffer_gets) lio,
p.lio plio,
trunc(log(10,nullif
(stats.last_starts*stats.cardinality/
nullif(stats.last_output_rows,0),0))) ratio
FROM
v$sql_plan_statistics_all stats
, (select sum(last_cu_buffer_gets + last_cr_buffer_gets) lio,
sum(LAST_ELAPSED_TIME) elapsed,
child_number,
parent_id,
sql_id
from v$sql_plan_statistics_all
group by child_number,sql_id, parent_id) p
WHERE
stats.sql_id='&v_sql_id' and
p.sql_id(+) = stats.sql_id and
p.child_number(+) = stats.child_number and
p.parent_id(+)=stats.id
)
order by sql_id, childn , id
/