11
5
Oracle SQL 优化学习记录
10墨值下载
Oracle SQL 优化学习记录
第0章 环境
第1章 SQL优化必懂概念
1.1、基数（CARDINALITY）
某个列的唯一键（Distinct_Keys）的数量；
主键列的基数等于表的总行数；
一般情况下，当查询结果是返回表中5%以内的数据时，应该走索引；当查询结果返回的是超过表
中5%的数据时，应该走全表扫描；
1.2、选择性（SELECTIVITY)
基数与总行数的比值再乘以100%就是某个列的选择性；
1.3、收集表统计信息
备份scott用户：
oracle*o11g-/home/oracle>$expdp \' / as sysdba \' dumpfile=expdp_scott.dump
logfile=expdp_scott.log schemas=scott
初始化环境：
oracle*o11g-/home/oracle>$sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Sep 27 13:08:43 2019
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SYS@o11g>grant dba to scott;
Grant succeeded.
SYS@o11g>alter user scott account unlock ;
SYS@o11g>alter user scott identified by tiger;
SYS@o11g>conn scott/tiger;
SCOTT@o11g>create table test as select * from dba_objects ;
1.4、获取表每个列的基数与选择性
1.5、什么样的列必须创建索引呢？
当一个列出现在where条件中，该列没有创建索引并且选择性大于20%，那么该列就必须创建索
引；
只有大表才会产生性能问题
begin
dbms_stats.gather_table_stats(ownname => 'SCOTT',
tabname => 'TEST',
estimate_percent => 100,
method_opt => 'for all columns size ?
1/auto',
no_invalidate => FALSE,
degree => 1,
cascade => TRUE);
end;
/
SCOTT@o11g>select a.column_name,
b.num_rows,
a.num_distinct Cardinality,
round(a.num_distinct / b.num_rows * 100, 2) selectivity
from dba_tab_col_statistics a, dba_tables b
where a.owner = b.owner
and a.table_name = b.table_name
and a.owner = 'SCOTT'
and a.table_name = 'TEST';
COLUMN_NAME NUM_ROWS CARDINALITY SELECTIVITY
--------------- ---------- ----------- -----------
OWNER 86963 30 .03
OBJECT_NAME 86963 52421 60.28
SUBOBJECT_NAME 86963 142 .16
OBJECT_ID 86963 86963 100
DATA_OBJECT_ID 86963 9077 10.44
OBJECT_TYPE 86963 45 .05
CREATED 86963 925 1.06
LAST_DDL_TIME 86963 1026 1.18
TIMESTAMP 86963 1065 1.22
STATUS 86963 1 0
TEMPORARY 86963 2 0
GENERATED 86963 2 0
SECONDARY 86963 2 0
NAMESPACE 86963 21 .02
EDITION_NAME 86963 0 0
15 rows selected.
1.6、抓出必须创建索引的列
1.6.1、刷新数据库监控信息
1.6.2、获取需要创建索引的列
1.7、直方图
for all columns size 1 表示对所有列都不收集直方图；
“skewonly” 选项，它的时间性很强，因为它检查每个索引中每列值的分布。如果 dbms_stats 发
现一个索引中具有不均匀分布的列，它将为该索引创建直方图，以帮助基于成本的 SQL 优化器决
定是使用索引还是全表扫描访问。
1.8、回表（TABLE ACCESS BY INDEX ROWID）
通过索引中记录的rowid访问表中的数据就叫回表；
回表一般是单块读；
回表次数太多会严重影响SQL性能；
1.9、获取数据存放的块数量
begin
dbms_stats.flush_database_monitoring_info;
end;
/
select r.name owner,
o.name table_name,
c.name column_name,
equality_preds, --等值过滤
equality_preds, --等值join，比如where a.id=b.id
nonequijoin_preds, -- 不等值join
range_preds, --范围过滤次数 > >= < <= between and
like_preds, --like过滤
null_preds, --null过滤
timestamp
from sys.col_usage$ u, sys.obj$ o, sys.col$ c, sys.user$ r
where o.obj# = u.obj#
and c.obj# = u.obj#
and c.col# = u.intcol#
and r.name = 'SCOTT'
and o.name = 'TEST';
通过指定 dbms_stats 的 method_opt 参数，来创建直方图。在 method_opt 子句中有三个相
关选项，即 skewonly、repeat 和 auto。
of 87
10墨值下载
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文档的来源（墨天轮），文档链接，文档作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
墨天轮 运营
最新上传
下载排行榜
1
2
9-数据库人的进阶之路：从PG分区、SQL优化到拥抱AI未来（罗敏）.pptx
3
1-PG版本兼容性案例（彭冲）.pptx
4
2-TDSQL PG在复杂查询场景中的挑战与实践-opensource.pdf
5
6-PostgreSQL 哈希索引原理浅析（文一）.pdf
6
3-AI时代的变革者-面向机器的接口语言(MOQL)_吕海波.pptx
7
8-基于PG向量和RAG技术的开源知识库问答系统MaxKB.pptx
8
4-IvorySQL V4：双解析器架构下的兼容性创新实践.pptx
9
7-拉起PG好伙伴DifySupaOdoo.pdf
10
《云原生安全攻防启示录》李帅臻.pdf
相关文档
评论