My Important SQLs - Oracle

 Oracle Metadata SQLs

-- index usage
WITH index_usage AS
(
    SELECT pl.sql_id, pl.object_owner, pl.object_name,pl.operation,
            pl.options, count(1) as use_count
        FROM v$sql_plan pl
        WHERE pl.operation = 'INDEX'
        GROUP BY pl.sql_id, pl.object_owner, pl.object_name,pl.operation, pl.options
)
SELECT
        ix.table_owner, ix.table_name, ix.index_name,iu.operation,
        iu.options, ss.sql_text, ss.executions
    FROM all_indexes ix
    LEFT OUTER JOIN index_usage iu
             ON ix.owner = iu.object_owner
             AND ix.index_name = iu.object_name 
    LEFT OUTER JOIN v$sqlstats ss
        ON iu.sql_id = ss.sql_id
    WHERE ix.owner = '<<owner name>>'
    ORDER BY ix.table_name, ix.index_name;

-- what sql is executing right now
select
s.sid, s.username, s.osuser,
q.sql_text, q.optimizer_cost,
s.blocking_session, bs.username as blocking_user,
bs.machine as blocking_machine, bs.module as blocking_module,
bq.sql_text as blocking_sql, s.event as wait_event,
q.sql_fulltext
from v$session s
inner join v$sql q
  on s.sql_id = q.sql_id
left join v$session bs -- blocking sessions
  on s.blocking_session = bs.sid
left join v$sql bq -- blocking queries
  on bs.sql_id = bq.sql_id
where s.type = 'USER'

-- query to get query which consume more resources
select * from
( select sql_id, sql_text, executions, elapsed_time, cpu_time, buffer_gets, disk_reads,
          elapsed_time/executions as avg_elapsed_time,
          cpu_time/executions as avg_cpu_time,
          buffer_gets/executions as avg_buffer_gets,
          disk_reads/executions as avg_disk_reads
    from v$sqlstats
    where executions > 0
    order by elapsed_time/executions desc
)
where rownum <=25

-- SQLs causing locking
SELECT
            V$SESSION.OSUSER,
            V$SESSION.SQL_ID SQL_ID,
            V$SQLAREA.SQL_FULLTEXT
        FROM V$LOCKED_OBJECT
        LEFT JOIN V$SESSION ON V$SESSION.SID = V$LOCKED_OBJECT.SESSION_ID
        LEFT JOIN V$SQLAREA ON V$SQLAREA.SQL_ID = V$SESSION.SQL_ID

-- Query to get work being done with insert and update
SELECT * FROM V$SESSION_LONGOPS;
-- Who is running What query
SELECT OSUSER, SESSIONCOUNT, SQLTEXT
FROM
    (
        SELECT
            V$SESSION.OSUSER,
            V$SESSION.SQL_ID SQL_ID,
            COUNT (*) SESSIONCOUNT
        FROM V$SESSION
        GROUP BY
            V$SESSION.OSUSER,
            V$SESSION.SQL_ID
    )
    SS
    INNER JOIN
    (SELECT
         V$SQLAREA.SQL_FULLTEXT SQLTEXT,
         V$SQLAREA.SQL_ID SQL_ID
     FROM V$SQLAREA) ST
    ON ST.SQL_ID = SS.SQL_ID
ORDER BY
    SESSIONCOUNT DESC,
    OSUSER
-- Table space query   
SELECT
    df.tablespace_name "Tablespace",
    totalusedspace "Used MB",
    (df.totalspace - tu.totalusedspace) "Free MB",
    df.totalspace "Total MB",
    ROUND (100 * ((df.totalspace - tu.totalusedspace) / df.totalspace)) "Pct. Free"
FROM
    (
        SELECT
            tablespace_name,
            ROUND (SUM (bytes) / 1048576) TotalSpace
        FROM dba_data_files
        GROUP BY tablespace_name
    )
    df,
    (
        SELECT
            ROUND (SUM (bytes) / (1024 * 1024)) totalusedspace,
            tablespace_name
        FROM dba_segments
        GROUP BY tablespace_name
    )
    tu
WHERE df.tablespace_name = tu.tablespace_name;
-- Query to find out unusable indexes

select b.table_name,b.index_name,  a.partition_name
from dba_ind_partitions a, dba_indexes b
where a.status='UNUSABLE' and b.owner='DW_ODS'
and a.index_owner=b.owner and a.index_name=b.index_name
order by  1,2,3