# 2. 优化外部表定义
# 创建带并行度的外部表
SQL> CREATE TABLE fgedu.external_orders_parallel (
2 order_id NUMBER,
3 customer_id NUMBER,
4 order_date DATE,
5 amount NUMBER
6 ) ORGANIZATION EXTERNAL (
7 TYPE ORACLE_LOADER
8 DEFAULT DIRECTORY external_dir
9 ACCESS PARAMETERS (
10 RECORDS DELIMITED BY NEWLINE
11 FIELDS TERMINATED BY ‘,’
12 MISSING FIELD VALUES ARE NULL
13 (order_id, customer_id, order_date DATE ‘YYYY-MM-DD’, amount)
14 )
15 LOCATION (‘orders.csv’)
16 ) PARALLEL 4;

# 3. 测试并行查询性能
SQL> SET TIMING ON
SQL> SELECT * FROM fgedu.external_orders WHERE amount > 2000;

ORDER_ID CUSTOMER_ID ORDER_DATE AMOUNT
———- ———– ———- ———-
3 3 2026-03-15 3000
4 4 2026-04-15 4000

Elapsed: 00:00:00.01

SQL> SELECT * FROM fgedu.external_orders_parallel WHERE amount > 2000;

ORDER_ID CUSTOMER_ID ORDER_DATE AMOUNT
———- ———– ———- ———-
3 3 2026-03-15 3000
4 4 2026-04-15 4000

Elapsed: 00:00:00.00

# 4. 优化外部表访问参数
# 创建带预处理器的外部表
SQL> CREATE TABLE fgedu.external_orders_gz (
2 order_id NUMBER,
3 customer_id NUMBER,
4 order_date DATE,
5 amount NUMBER
6 ) ORGANIZATION EXTERNAL (
7 TYPE ORACLE_LOADER
8 DEFAULT DIRECTORY external_dir
9 ACCESS PARAMETERS (
10 RECORDS DELIMITED BY NEWLINE
11 FIELDS TERMINATED BY ‘,’
12 MISSING FIELD VALUES ARE NULL
13 PREPROCESSOR external_dir:’gunzip.sh’
14 (order_id, customer_id, order_date DATE ‘YYYY-MM-DD’, amount)
15 )
16 LOCATION (‘orders.csv.gz’)
17 );

# 5. 创建预处理器脚本
$ echo “#!/bin/sh” > /backup/external/gunzip.sh
$ echo “gunzip -c $1” >> /backup/external/gunzip.sh
$ chmod +x /backup/external/gunzip.sh

# 6. 测试压缩文件的外部表访问
$ gzip /backup/external/orders.csv
$ mv /backup/external/orders.csv.gz /backup/external/orders.csv.gz

SQL> SELECT * FROM fgedu.external_orders_gz;

ORDER_ID CUSTOMER_ID ORDER_DATE AMOUNT
———- ———– ———- ———-
1 1 2026-01-15 1000
2 2 2026-02-15 2000
3 3 2026-03-15 3000
4 4 2026-04-15 4000

# 7. 验证调优效果
# 执行查询，比较性能
SQL> EXPLAIN PLAN FOR SELECT * FROM fgedu.external_orders_parallel WHERE amount > 2000;

Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
————————————————————————–
Plan hash value: 3912729313

————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
————————————————————————–
| 0 | SELECT STATEMENT | | 2 | 36 | 3 (0)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 2 | 36 | 3 (0)| 00:00:01 | Q1 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 2 | 36 | 3 (0)| 00:00:01 | Q1 | PCWC | |
| 4 | TABLE ACCESS FULL| EXTERNAL_ORDERS_PARALLEL| 2 | 36 | 3 (0)| 00:00:01 | Q1 | PCWP | |
————————————————————————–

Note
—– – dynamic statistics used: dynamic sampling (level=2)

# 8. 测试大批量数据的外部表性能
# 创建大文件
$ for i in {1..10000}; do echo “$i,$((i%1000+1)),2026-01-01,$((i*10))” >> /backup/external/large_orders.csv; done

# 创建外部表
SQL> CREATE TABLE fgedu.external_large_orders (
2 order_id NUMBER,
3 customer_id NUMBER,
4 order_date DATE,
5 amount NUMBER
6 ) ORGANIZATION EXTERNAL (
7 TYPE ORACLE_LOADER
8 DEFAULT DIRECTORY external_dir
9 ACCESS PARAMETERS (
10 RECORDS DELIMITED BY NEWLINE
11 FIELDS TERMINATED BY ‘,’
12 MISSING FIELD VALUES ARE NULL
13 (order_id, customer_id, order_date DATE ‘YYYY-MM-DD’, amount)
14 )
15 LOCATION (‘large_orders.csv’)
16 ) PARALLEL 8;

# 9. 测试查询性能
SQL> SET TIMING ON
SQL> SELECT COUNT(*) FROM fgedu.external_large_orders;

COUNT(*)
———-
10000

Elapsed: 00:00:00.05

SQL> SELECT customer_id, SUM(amount) total_amount FROM fgedu.external_large_orders GROUP BY customer_id;

CUSTOMER_ID TOTAL_AMOUNT
———– ————
1 50050
2 100100
3 150150
…
1000 5005000

Elapsed: 00:00:00.10

Part05-风哥经验总结与分享

5.1 外部表调优最佳实践

• 选择合适的外部表类型：根据数据特点选择合适的外部表类型
• 优化外部表定义：优化外部表的访问参数和并行度
• 定期监控：定期监控外部表使用情况，及时发现问题
• 分析瓶颈：分析外部表性能的瓶颈，采取相应的措施
• 持续优化：根据数据量和访问模式持续优化外部表配置

5.2 外部表调优注意事项

• 根据数据特点选择合适的外部表类型
• 优化外部表定义，提高访问效率
• 定期监控外部表使用情况，及时发现问题
• 结合其他性能工具，全面分析外部表性能
• 与开发团队分享外部表调优结果，提高应用程序性能

5.3 外部表调优建议

• 建立外部表调优流程，定期执行外部表配置和分析
• 培训DBA，提高外部表管理能力
• 建立外部表调优结果的审核机制
• 跟踪外部表使用的变化趋势
• 与Oracle支持团队保持沟通，获取外部表调优的最佳实践

更多视频教程www.fgedu.net.cn

更多学习教程公众号风哥教程itpux_com

from oracle:www.itpux.com