— 查看执行时间
— 输出结果：执行时间 10.5秒

— 步骤2：分析执行计划
EXPLAIN PLAN FOR
SELECT * FROM fgedu.sales
WHERE sale_date BETWEEN ‘2024-01-01’ AND ‘2024-12-31’
GROUP BY product_id
ORDER BY total_amount DESC;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

— 输出结果：全表扫描

— 步骤3：创建索引
CREATE INDEX idx_sales_sale_date ON fgedu.sales(sale_date);

— 步骤4：重新执行查询
SELECT * FROM fgedu.sales
WHERE sale_date BETWEEN ‘2024-01-01’ AND ‘2024-12-31’
GROUP BY product_id
ORDER BY total_amount DESC;

— 查看执行时间
— 输出结果：执行时间 3.2秒

— 步骤5：创建分区表
— 重命名原表
ALTER TABLE fgedu.sales RENAME TO fgedu.sales_old;

— 创建分区表
CREATE TABLE fgedu.sales (
sale_id NUMBER PRIMARY KEY,
sale_date DATE,
product_id NUMBER,
customer_id NUMBER,
amount DECIMAL(10, 2),
total_amount DECIMAL(10, 2)
)
PARTITION BY RANGE (sale_date) (
PARTITION p202401 VALUES LESS THAN (TO_DATE(‘2024-02-01’, ‘YYYY-MM-DD’)),
PARTITION p202402 VALUES LESS THAN (TO_DATE(‘2024-03-01’, ‘YYYY-MM-DD’)),
PARTITION p202403 VALUES LESS THAN (TO_DATE(‘2024-04-01’, ‘YYYY-MM-DD’)),
PARTITION p202404 VALUES LESS THAN (TO_DATE(‘2024-05-01’, ‘YYYY-MM-DD’)),
PARTITION p202405 VALUES LESS THAN (TO_DATE(‘2024-06-01’, ‘YYYY-MM-DD’)),
PARTITION p202406 VALUES LESS THAN (TO_DATE(‘2024-07-01’, ‘YYYY-MM-DD’)),
PARTITION p202407 VALUES LESS THAN (TO_DATE(‘2024-08-01’, ‘YYYY-MM-DD’)),
PARTITION p202408 VALUES LESS THAN (TO_DATE(‘2024-09-01’, ‘YYYY-MM-DD’)),
PARTITION p202409 VALUES LESS THAN (TO_DATE(‘2024-10-01’, ‘YYYY-MM-DD’)),
PARTITION p202410 VALUES LESS THAN (TO_DATE(‘2024-11-01’, ‘YYYY-MM-DD’)),
PARTITION p202411 VALUES LESS THAN (TO_DATE(‘2024-12-01’, ‘YYYY-MM-DD’)),
PARTITION p202412 VALUES LESS THAN (TO_DATE(‘2025-01-01’, ‘YYYY-MM-DD’))
);

— 迁移数据
INSERT INTO fgedu.sales SELECT * FROM fgedu.sales_old;

— 创建分区索引
CREATE INDEX idx_sales_sale_date ON fgedu.sales(sale_date) LOCAL;

— 步骤6：重新执行查询
SELECT * FROM fgedu.sales
WHERE sale_date BETWEEN ‘2024-01-01’ AND ‘2024-12-31’
GROUP BY product_id
ORDER BY total_amount DESC;

— 查看执行时间
— 输出结果：执行时间 1.5秒

— 步骤7：使用并行查询
SELECT /*+ PARALLEL(4) */ * FROM fgedu.sales
WHERE sale_date BETWEEN ‘2024-01-01’ AND ‘2024-12-31’
GROUP BY product_id
ORDER BY total_amount DESC;

— 查看执行时间
— 输出结果：执行时间 0.8秒

— 查看执行时间
— 输出结果：执行时间 15.3秒

— 步骤2：分析执行计划
EXPLAIN PLAN FOR
SELECT
p.product_name,
c.customer_name,
SUM(s.amount) AS total_amount,
COUNT(*) AS order_count
FROM fgedu.sales s
JOIN fgedu.products p ON s.product_id = p.product_id
JOIN fgedu.customers c ON s.customer_id = c.customer_id
WHERE s.sale_date BETWEEN ‘2024-01-01’ AND ‘2024-12-31’
GROUP BY p.product_name, c.customer_name
ORDER BY total_amount DESC;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

— 输出结果：全表扫描和哈希连接

— 步骤3：创建索引
CREATE INDEX idx_sales_product_id ON fgedu.sales(product_id);
CREATE INDEX idx_sales_customer_id ON fgedu.sales(customer_id);
CREATE INDEX idx_products_product_id ON fgedu.products(product_id);
CREATE INDEX idx_customers_customer_id ON fgedu.customers(customer_id);

— 步骤4：优化表结构
— 添加外键约束
ALTER TABLE fgedu.sales ADD CONSTRAINT fk_sales_product FOREIGN KEY (product_id) REFERENCES fgedu.products(product_id);
ALTER TABLE fgedu.sales ADD CONSTRAINT fk_sales_customer FOREIGN KEY (customer_id) REFERENCES fgedu.customers(customer_id);

— 步骤5：重新执行查询
SELECT
p.product_name,
c.customer_name,
SUM(s.amount) AS total_amount,
COUNT(*) AS order_count
FROM fgedu.sales s
JOIN fgedu.products p ON s.product_id = p.product_id
JOIN fgedu.customers c ON s.customer_id = c.customer_id
WHERE s.sale_date BETWEEN ‘2024-01-01’ AND ‘2024-12-31’
GROUP BY p.product_name, c.customer_name
ORDER BY total_amount DESC;

— 查看执行时间
— 输出结果：执行时间 8.7秒

— 步骤6：使用并行查询
SELECT /*+ PARALLEL(4) */
p.product_name,
c.customer_name,
SUM(s.amount) AS total_amount,
COUNT(*) AS order_count
FROM fgedu.sales s
JOIN fgedu.products p ON s.product_id = p.product_id
JOIN fgedu.customers c ON s.customer_id = c.customer_id
WHERE s.sale_date BETWEEN ‘2024-01-01’ AND ‘2024-12-31’
GROUP BY p.product_name, c.customer_name
ORDER BY total_amount DESC;

— 查看执行时间
— 输出结果：执行时间 4.2秒

— 步骤7：创建物化视图
CREATE MATERIALIZED VIEW fgedu.sales_summary
REFRESH COMPLETE ON DEMAND
AS
SELECT
p.product_name,
c.customer_name,
SUM(s.amount) AS total_amount,
COUNT(*) AS order_count
FROM fgedu.sales s
JOIN fgedu.products p ON s.product_id = p.product_id
JOIN fgedu.customers c ON s.customer_id = c.customer_id
GROUP BY p.product_name, c.customer_name;

— 执行物化视图查询
SELECT * FROM fgedu.sales_summary
ORDER BY total_amount DESC;

— 查看执行时间
— 输出结果：执行时间 0.5秒

— 查看会话状态
SELECT * FROM v$session WHERE status = ‘ACTIVE’;

— 查看等待事件
SELECT * FROM v$session_wait;

— 步骤2：优化数据库参数
— 优化内存参数
ALTER SYSTEM SET sga_target = 16G SCOPE=SPFILE;
ALTER SYSTEM SET pga_aggregate_target = 8G SCOPE=SPFILE;

— 优化I/O参数
ALTER SYSTEM SET db_writer_processes = 8 SCOPE=SPFILE;
ALTER SYSTEM SET log_buffer = 32M SCOPE=SPFILE;

— 优化并行参数
ALTER SYSTEM SET parallel_degree_policy = ‘AUTO’ SCOPE=SPFILE;
ALTER SYSTEM SET parallel_max_servers = 32 SCOPE=SPFILE;

— 重启数据库使参数生效
SHUTDOWN IMMEDIATE;
STARTUP;

— 步骤3：优化存储配置
— 检查存储使用情况
SELECT tablespace_name, used_percent
FROM dba_tablespace_usage_metrics;

— 增加表空间
ALTER TABLESPACE users ADD DATAFILE ‘/yashandb/fgdata/users02.dbf’ SIZE 100G AUTOEXTEND ON;

— 步骤4：优化索引
— 分析索引使用情况
SELECT index_name, table_name, status, num_rows
FROM user_indexes
WHERE table_name IN (‘SALES’, ‘PRODUCTS’, ‘CUSTOMERS’);

— 重建索引
ALTER INDEX idx_sales_sale_date REBUILD;
ALTER INDEX idx_sales_product_id REBUILD;
ALTER INDEX idx_sales_customer_id REBUILD;

— 步骤5：优化查询语句
— 重写查询语句
SELECT
p.product_name,
SUM(s.amount) AS total_amount
FROM fgedu.sales s
JOIN fgedu.products p ON s.product_id = p.product_id
WHERE s.sale_date BETWEEN ‘2024-01-01’ AND ‘2024-12-31’
GROUP BY p.product_name
ORDER BY total_amount DESC;

— 使用绑定变量
DECLARE
v_start_date DATE := TO_DATE(‘2024-01-01’, ‘YYYY-MM-DD’);
v_end_date DATE := TO_DATE(‘2024-12-31’, ‘YYYY-MM-DD’);
BEGIN
FOR rec IN (
SELECT
p.product_name,
SUM(s.amount) AS total_amount
FROM fgedu.sales s
JOIN fgedu.products p ON s.product_id = p.product_id
WHERE s.sale_date BETWEEN v_start_date AND v_end_date
GROUP BY p.product_name
ORDER BY total_amount DESC
) LOOP
DBMS_OUTPUT.PUT_LINE(rec.product_name || ‘: ‘ || rec.total_amount);
END LOOP;
END;
/

— 步骤6：监控查询性能
— 启用SQL跟踪
ALTER SESSION SET sql_trace = TRUE;

— 执行查询
SELECT * FROM fgedu.sales
WHERE sale_date BETWEEN ‘2024-01-01’ AND ‘2024-12-31’
GROUP BY product_id
ORDER BY total_amount DESC;

— 查看跟踪文件
SELECT value FROM v$diag_info WHERE name = ‘Default Trace File’;

— 分析跟踪文件
TKPROF trace_file.trc output.txt EXPLAIN=sys/sys