🚀 Performance & Tuning
EXPLAIN PLAN, hints, indexes and stats: find and fix slow queries.
| Snippet | |
|---|---|
| ☆EXPLAIN PLAN — Show the optimizer's execution plan |
EXPLAIN PLAN FOR SELECT * FROM employees WHERE deptno = 10; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
|
| ☆INDEX Hint — Force an index access path |
SELECT /*+ INDEX(e emp_dept_idx) */ * FROM employees e WHERE deptno = 10;
|
| ☆PARALLEL Hint — Parallel full scan for large tables |
SELECT /*+ FULL(e) PARALLEL(e, 4) */ COUNT(*) FROM employees e;
|
| ☆CREATE INDEX — Speed up predicate lookups |
CREATE INDEX emp_dept_idx ON employees (deptno);
|
| ☆REBUILD INDEX — Defragment an index online |
ALTER INDEX emp_dept_idx REBUILD ONLINE;
|
| ☆Gather Stats — Refresh optimizer statistics |
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'EMPLOYEES');
|
| ☆Top SQL by Time — Heaviest statements in the shared pool |
SELECT sql_id, executions, ROUND(elapsed_time/1e6, 1) AS sec FROM v$sql ORDER BY elapsed_time DESC FETCH FIRST 10 ROWS ONLY;
|