11 Monday Aug 2014
Tags
SELECT * FROM V$VERSION
Returns something like this…
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – 64bit Production
PL/SQL Release 11.1.0.6.0 – Production
CORE 11.1.0.6.0 Production
TNS for Linux: Version 11.1.0.6.0 – Production
NLSRTL Version 11.1.0.6.0 – Production
23 Wednesday Jan 2013
Posted in Uncategorized
≈ Comments Off on Identify full table scans in Oracle
Tags
I used to have a good query that I was happy with to do this, but I apparently lost it. Here’s some queries I’ve cobbled together today to identify full table scans and check their SQL…and determine that a maybe some new indexes might be in order based on real world queries…
select * from v$sql_plan where operation = ‘TABLE ACCESS’ and options = ‘FULL’
AND timestamp > sysdate – .01
order by TIME
and use the hash value to get the SQL:
select * from v$sql
where HASH_VALUE = {hash value of a result}
05 Monday Nov 2012
Tags
Code:
SELECT TRUNC(datecol, ‘HH24’) FROM mytable
GROUP BY TRUNC(datecol, ‘HH24’);
from http://stackoverflow.com/questions/5111396/sql-group-by-date-hour (I liked this solution better)
<roughCode/> 