SELECT
  se.event,
  se.total_waits,
  ROUND(se.time_waited/100,2) AS time_waited_s
FROM v$system_event se
JOIN v$event_name en ON en.name = se.event
WHERE en.wait_class <> 'Idle'
ORDER BY se.time_waited DESC
FETCH FIRST 20 ROWS ONLY;

SELECT
  wait_class,
  ROUND(time_waited/100,2) AS time_waited_s
FROM v$system_wait_class
WHERE wait_class <> 'Idle'
ORDER BY time_waited DESC;

SELECT
  s.sid, s.serial#, s.username, s.status,
  s.event, s.wait_class, s.seconds_in_wait,
  s.sql_id, s.machine, s.program
FROM v$session s
WHERE s.state = 'WAITING'
  AND s.wait_class <> 'Idle'
ORDER BY s.seconds_in_wait DESC
FETCH FIRST 30 ROWS ONLY;

SELECT
  s.sid AS blocked_sid,
  s.serial# AS blocked_serial,
  s.username AS blocked_user,
  s.blocking_session AS blocker_sid,
  s.event,
  s.seconds_in_wait,
  s.sql_id
FROM v$session s
WHERE s.blocking_session IS NOT NULL
ORDER BY s.seconds_in_wait DESC;

SELECT
  bs.sid blocker_sid,
  ws.sid waiter_sid,
  lo.object_id,
  o.owner, o.object_name, o.object_type,
  ws.seconds_in_wait,
  ws.event
FROM v$session ws
JOIN v$session bs ON bs.sid = ws.blocking_session
LEFT JOIN v$locked_object lo ON lo.session_id = ws.sid
LEFT JOIN dba_objects o ON o.object_id = lo.object_id
ORDER BY ws.seconds_in_wait DESC;

SELECT
  sql_id,
  executions,
  ROUND(elapsed_time/1e6,2) AS elapsed_s,
  ROUND(cpu_time/1e6,2)     AS cpu_s,
  buffer_gets,
  disk_reads,
  rows_processed
FROM v$sql
WHERE executions > 0
ORDER BY elapsed_time DESC
FETCH FIRST 20 ROWS ONLY;

SELECT
  sql_id,
  executions,
  buffer_gets,
  ROUND(buffer_gets/NULLIF(executions,0)) AS gets_per_exec,
  ROUND(elapsed_time/1e6,2) AS elapsed_s
FROM v$sql
WHERE executions > 0
ORDER BY buffer_gets DESC
FETCH FIRST 20 ROWS ONLY;

SELECT
  sql_id,
  executions,
  disk_reads,
  ROUND(disk_reads/NULLIF(executions,0)) AS reads_per_exec,
  buffer_gets,
  ROUND(elapsed_time/1e6,2) AS elapsed_s
FROM v$sql
WHERE executions > 0
ORDER BY disk_reads DESC
FETCH FIRST 20 ROWS ONLY;

-- Remplace :SQL_ID
SELECT sql_fulltext
FROM v$sql
WHERE sql_id = :SQL_ID
FETCH FIRST 1 ROWS ONLY;

-- Remplace :SQL_ID
SELECT * FROM TABLE(
  dbms_xplan.display_cursor(:SQL_ID, NULL, 'ALLSTATS LAST +PEEKED_BINDS')
);

SELECT event, total_waits, ROUND(time_waited/100,2) AS time_waited_s
FROM v$system_event
WHERE event IN ('log file sync','log file parallel write')
ORDER BY time_waited DESC;

SELECT name, value
FROM v$sysstat
WHERE name IN ('redo size','redo writes','user commits','user rollbacks');

SELECT
  df.file_name,
  fs.phyrds,
  fs.phywrts,
  ROUND(fs.readtim/100,2)  AS read_time_s,
  ROUND(fs.writetim/100,2) AS write_time_s,
  fs.singleblkrds,
  fs.avgiotim
FROM v$filestat fs
JOIN dba_data_files df ON df.file_id = fs.file#
ORDER BY (fs.phyrds + fs.phywrts) DESC
FETCH FIRST 20 ROWS ONLY;

SELECT
  owner, object_name, object_type,
  SUM(CASE WHEN statistic_name='logical reads' THEN value END) AS logical_reads,
  SUM(CASE WHEN statistic_name='physical reads' THEN value END) AS physical_reads,
  SUM(CASE WHEN statistic_name='ITL waits' THEN value END) AS itl_waits
FROM v$segment_statistics
WHERE statistic_name IN ('logical reads','physical reads','ITL waits')
GROUP BY owner, object_name, object_type
ORDER BY logical_reads DESC NULLS LAST
FETCH FIRST 20 ROWS ONLY;

SELECT
  o.owner, o.object_name, o.object_type,
  COUNT(*) AS cached_blocks
FROM v$bh bh
JOIN dba_objects o ON o.data_object_id = bh.objd
GROUP BY o.owner, o.object_name, o.object_type
ORDER BY cached_blocks DESC
FETCH FIRST 20 ROWS ONLY;

SELECT name, value
FROM v$sysstat
WHERE name IN ('workarea executions - optimal',
               'workarea executions - onepass',
               'workarea executions - multipass',
               'sorts (disk)',
               'sorts (memory)');

SELECT
  s.sid, s.serial#, s.username, s.sql_id,
  tu.tablespace,
  ROUND(tu.blocks * ts.block_size / 1024 / 1024, 2) AS temp_mb,
  s.program, s.machine
FROM v$tempseg_usage tu
JOIN v$session s ON s.saddr = tu.session_addr
JOIN (SELECT tablespace_name, block_size FROM dba_tablespaces) ts
  ON ts.tablespace_name = tu.tablespace
ORDER BY temp_mb DESC;

SELECT name, value
FROM v$sysstat
WHERE name IN ('parse count (total)',
               'parse count (hard)',
               'execute count',
               'session cursor cache hits');

SELECT
  s.sid, s.serial#, s.username, s.status,
  s.sql_id, s.event, s.wait_class,
  s.machine, s.program
FROM v$session s
WHERE s.status = 'ACTIVE'
  AND s.username IS NOT NULL
ORDER BY s.sid;

SELECT
  i.owner, i.index_name, i.table_name,
  s.leaf_blocks,
  NVL(us.used, 'NO') AS used_in_monitoring
FROM dba_indexes i
LEFT JOIN dba_index_usage us
  ON us.owner = i.owner AND us.name = i.index_name
LEFT JOIN dba_ind_statistics s
  ON s.owner = i.owner AND s.index_name = i.index_name
WHERE i.owner NOT IN ('SYS','SYSTEM')
ORDER BY s.leaf_blocks DESC NULLS LAST
FETCH FIRST 30 ROWS ONLY;