-- Before optimization
EXPLAIN PLAN FOR
SELECT d.department_name,
       COUNT(*) as employee_count,
       AVG(e.salary) as avg_salary
FROM employees e
JOIN departments d ON e.department_id = d.department_id
GROUP BY d.department_name;

-- Generated execution plan analysis
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- AI-Optimized query with hints
SELECT /*+ LEADING(d e) USE_NL(e) INDEX(e emp_dept_idx) */
    d.department_name,
    COUNT(*) as employee_count,
    AVG(e.salary) as avg_salary
FROM departments d
JOIN employees e ON e.department_id = d.department_id
GROUP BY

-- Index analysis
SELECT i.table_name,
       i.index_name,
       i.column_name,
       i.column_position,
       t.num_rows,
       t.avg_row_len,
       i.clustering_factor
FROM user_ind_columns i
JOIN user_tables t ON i.table_name = t.table_name
WHERE i.table_name = 'EMPLOYEES'
ORDER BY i.index_name, i.column_position;

-- Generated index recommendations
CREATE INDEX emp_salary_dept_idx ON employees(department_id, salary);
CREATE INDEX emp_hire_date_idx ON employees(hire_date)

-- Original query
SELECT c.customer_name,
       o.order_date,
       p.product_name,
       oi.quantity
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE o.order_date >= ADD_MONTHS(SYSDATE, -6);

-- Optimized query with proper join order
SELECT /*+ LEADING(o oi p c) USE_NL(oi) USE_NL(p) USE_NL(c)
          INDEX(o ord_date_idx)
          INDEX(oi order_items_pk) */
    c.customer_name,
    o.order_date,
    p.product_name,
    oi.quantity
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date >= ADD_MONTHS(SYSDATE, -6)

-- Original query with subquery
SELECT e.employee_id,
       e.first_name,
       e.salary
FROM employees e
WHERE e.salary > (
    SELECT AVG(salary)
    FROM employees
    WHERE department_id = e.department_id
);

-- Optimized query using analytic functions
SELECT employee_id,
       first_name,
       salary
FROM (
    SELECT employee_id,
           first_name,
           salary,
           AVG(salary) OVER (PARTITION BY department_id) as dept_avg_salary
    FROM employees
) e
WHERE

-- Query performance metrics
SELECT sql_id,
       executions,
       elapsed_time/1000000 as elapsed_seconds,
       (elapsed_time/1000000)/NULLIF(executions,0) as avg_seconds,
       buffer_gets/NULLIF(executions,0) as avg_buffer_gets,
       disk_reads/NULLIF(executions,0) as avg_disk_reads,
       rows_processed/NULLIF(executions,0) as avg_rows,
       sql_text
FROM v$sql
WHERE parsing_schema_name = USER
AND last_active_time > SYSDATE - 1/24
ORDER BY elapsed_time DESC

-- System resource monitoring
SELECT ss.username,
       ss.sql_id,
       ss.event,
       ss.wait_class,
       ss.seconds_in_wait,
       ss.state
FROM v$session ss
WHERE ss.status = 'ACTIVE'
AND ss.username IS NOT NULL
ORDER BY ss.seconds_in_wait DESC

-- Buffer cache hit ratio
SELECT a.name,
       b.size_for_estimate,
       round(b.buffers_for_estimate) buffers_for_estimate,
       round(b.estd_physical_read_factor,2) estd_physical_read_factor,
       round(b.estd_physical_reads,2) estd_physical_reads
FROM v$db_cache_advice b,
     v$database a
WHERE b.name = 'DEFAULT'
AND b.block_size = (SELECT value FROM v$parameter WHERE name = 'db_block_size')
ORDER BY

-- PGA usage statistics
SELECT name,
       value/1024/1024 as value_mb
FROM v$pgastat
WHERE unit = 'bytes'
ORDER BY value DESC