锐客网

  1. 首页 > 睿知 > it技术 > >

oracle怎么优化or oracle怎么优化10万条多表查询的sql

IT技术使用

oracle sql 用什么可以替代or ， 这样查询特别慢可以用union，比如select 内容 fromuser where name='张三' union select 内容 fromuser where name='李四'，相当于select 内容 fromuser where name='张三' or name='李四' ，因为union会用到索引，不知道你这个表有没有索引，表的数据多大？
如何设置使oracle10g性能最优 性能调优 步骤一、磁盘方面调优
1.规范磁盘阵列
RAID 10比RAID5更适用于OLTP系统，RAID10先镜像磁盘，再对其进行分段，由于对数据的小规模访问会比较频繁，所以对OLTP适用 。而RAID5,优势在于能够充分利用磁盘空间 ， 并且减少阵列的总成本 。但是由于阵列发出一个写入请求时 ， 必须改变磁盘上已修改的块，需要从磁盘上读取“奇偶校验”块，并且使用已修改的块计算新的奇偶校验块，然后把数据写入磁盘，且会限制吞吐量 。对性能有所影响，RAID5适用于OLAP系统 。
2.数据文件分布
分离下面的东西,避免磁盘竞争
?SYSTEM表空间
?TEMPORARY表空间
?UNDO表空间
?联机重做日志（放在最快的磁盘上）
?操作系统磁盘
?ORACLE安装目录
?经常被访问的数据文件
?索引表空间
?归档区域（应该总是与将要恢复的数据分离）
例：
2/: System
2/u01: Oracle Software
2/u02: Temporary tablespace, Control file1
2/u03: Undo Segments, Control file2
2/u04: Redo logs, Archive logs, Control file4
2/u05: System, SYSAUX tablespaces
2/u06: Data1 ,control file3
2/u07: Index tablespace
2/u08: Data2
通过下列语句查询确定IO问题
select name ,phyrds,phywrts,readtim,writetim
from v$filestat a,v$datafile b
where a.file#=b.file# order by readtim desc;
3.增大日志文件
u增大日志文件的大小，从而增加处理大型INSERT,DELETE,UPDATE操作的比例
查询日志文件状态
select a.member,b.* from v$logfile a,v$log b where a.GROUP#=b.GROUP#
查询日志切换时间
select b.RECID,to_char(b.FIRST_TIME,'yyyy-mm-dd hh24:mi:ss') start_time,a.RECID,to_char(a.FIRST_TIME,'yyyy-mm-dd hh24:mi:ss') end_time,round(((a.FIRST_TIME-b.FIRST_TIME)*25)*60,2) minutes
from v$log_history a ,v$log_history b
where a.RECID=b.RECID 1
order by a.FIRST_TIME desc
增大日志文件大小，以及对每组增加日志文件（一个主文件、一个多路利用文件）
u增大LOG_CHECKPOINT_INTERVAL参数 ， 现已不提倡使用它
如果低于每半小时切换一次日志，就增大联机重做日志大小 。如果处理大型批处理任务时频繁进行切换，就增大联机重做日志数目 。
alter database add logfile member ‘/log.ora’ to group 1;
alter database drop logfile member ‘/log.ora’;
4.UNDO表空间
修改三个初始参数：
UNDO_MANAGEMENT=AUTO
UNDO_TABLESPACE=CLOUDSEA_UNDO
UNDO_RETENTION=#of minutes
5.不要在系统表空间中执行排序
二、初始化参数调优
32位的寻址最大支持应该是2的32次方 ， 就是4G大小 。但实际中32位系统（XP，windows2003等MS32位系统, ubuntu等linux32 位系统）要能利用4G内存 ， 都是采用内存重映射技术 。需要主板及系统的支持 。如果关闭主板BIOS的重映射功能，系统将不能利用4G内存，可能只达3.5G.而在windows下看到的一般为3.25G 。所以SGA设置为内存的40%，但不能超过3.25G
1.重要初始化参数
lSGA_MAX_SIZE
lSGA_TARGET
lPGA_AGGREGATE_TARGET
lDB_CACHE_SIZE
lSHARED_POOL_SIZE
2.调整DB_CACHE_SIZE来提高性能
它设定了用来存储和处理内存中数据的SGA区域大?。幽诖嬷腥∈荼却排炭?0000倍以上
根据以下查询出数据缓存命中率
select sum(decode(name,'physical reads',value,0)) phys,
sum(decode(name,'db block gets',value,0)) gets,
sum(decode(name,'consistent gets',value,0)) con_gets,
(1- (sum(decode(name,'physical reads',value,0))/(sum(decode(name,'db block gets',value,0)) sum(decode(name,'consistent gets',value,0)) ) ))*100 Hitratio
from v$sysstat;
一个事务处理程序应该保证得到95%以上的命中率，命中率从90%提高到98%可能会提高500%的性能 ， ORACLE正在通过CPU或服务时间与等待时间来分析系统性能，不太重视命中率 ， 不过现在的库缓存和字典缓存仍将命中率作为基本的调整方法 。
在调整DB_CACHE_SIZE时使用V$DB_CACHE_ADVICE
select size_for_estimate, estd_physical_read_factor, estd_physical_reads
from v$db_cache_advice
where name = 'DEFAULT';
如果查询的命中率过低，说明缺少索引或者索引受到限制，通过V$SQLAREA视图查询执行缓慢的SQL
3.设定DB_BLOCK_SIZE来反映数据读取量大小
OLTP一般8K
OLAP一般16K或者32K
4.调整SHARED_POOL_SIZE以优化性能
正确地调整此参数可以同等可能地共享SQL语句，使得在内存中便能找到使用过的SQL语句 。为了减少硬解析次数，优化对共享SQL区域的使用 ， 需尽量使用存储过程、使用绑定变量
保证数据字典缓存命中率在95%以上
select ((1- sum(getmisses)/(sum(gets) sum(getmisses)))*100) hitratio
from v$rowcache
where gets getmisses 0;
如果命中率小于 99％，就可以考虑增加shared pool 以提高library cache 的命中率
SELECT SUM(PINS) "EXECUTIONS",SUM(RELOADS) "CACHE MISSES WHILE EXECUTING",1 - SUM(RELOADS)/SUM(PINS)
FROM V$LIBRARYCACHE;
通常规则是把它定为DB_CACHE_SIZE大小的50%－150% ， 在使用了大量存储过程或程序包，但只有有限内存的系统里，最后分配为150% 。在没有使用存储过程但大量分配内存给DB_CACHE_SIZE的系统里 ， 这个参数应该为10%－20%
5.调整PGA_AGGREGATE_TARGET以优化对内存的应用
uOLTP ：totalmemory*80%*20%
uDSS:totalmemory*80%*50%
6.25个重要初始化参数
2DB_CACHE_SIZE：分配给数据缓存的初始化内存
2SGA_TARGET：使用了自动内存管理，则设置此参数 。设置为0可禁用它
2PGA_AGGREGATE_TARGET：所有用户PGA软内存最大值
2SHARED_POOL_SIZE：分配给数据字典、SQL和PL／SQL的内存
2SGA_MAX_SIZE：SGA可动态增长的最大内存
2OPTIMIZER_MODE：
2CURSOR_SHARING：把字面SQL转换成带绑定变更的SQL，可减少硬解析开销
2OPTIMIZER_INDEX_COST_ADJ：索引扫描成本和全表扫描成本进行调整 ， 设定在1－10间会强制频繁地使用索引，保证索引可用性
2QUERY_REWRITE_ENABLED：用于启用具体化视图和基于函数的索引功能
2DB_FILE_MULTIBLOCK_READ_COUNT：对于全表扫描，为了更有效执行IO ， 此参数可在一次IO中读取多个块
2LOG_BUFFER：为内存中没有提交的事务分配缓冲区（非动态参数）
2DB_KEEP_CACHE_SIZE：分配给KEEP池或者额外数据缓存的内存
2DB_RECYCLE_CACHE_SIZE：
2DBWR_IO_SLAVES：如果没有异步IO，参数等同于DB_WRITER_PROCESSES模拟异步IO而分配的从SGA到磁盘的写入器数 。如果有异步IO，则使用DB_WRITER_PROCESSES设置多个写程序，在DBWR期间更快地写出脏块
2LARGE_POOL_SIZE：分配给大型PLSQL或其他一些很少使用的ORACLE选项LARGET池的总块数
2STATISTICS_LEVEL：启用顾问信息，并可选择提供更多OS统计信息来改进优化器决策 。默认：TYPICAL
2JAVA_POOL_SIZE：为JVM使用的JAVA存储过程所分配的内存
2JAVA_MAX_SESSIONSPACE_SIZE：跟踪JAVA类的用户会话状态所用内存上限
2MAX_SHARED_SERVERS：当使用共享服务器时的共享服务器上限
2WORKAREA_SIZE_POLICY：启用PGA大小自动管理
2FAST_START_MTTR_TARGET：完成一次崩溃恢复的大概时间／S
2LOG_CHECKPOINT_INTERVAL：检查点频率
2OPEN_CURSORS：指定了保存用户语句的专用区域大小，如此设置过高会导致ORA-4031
2DB_BLOCK_SIZE：数据库默认块大小
2OPTIMIZER_DYNAMIC_SAMPLING：控制动态抽样查询读取的块数量，对正在使用全局临时表的系统非常有用
三、SQL调优1.使用提示
1.1改变执行路径
通过OPTIMIZER_MODE参数指定优化器使用方法 ， 默认ALL_ROWS
?ALL_ROWS 可得最佳吞吐量执行查询所有行
?FIRST_ROWS(n) 可使优化器最快检索出第一行:
select/*FIRST_ROWS(1) */store_id,…fromtbl_store
1.2使用访问方法提示
允许开发人员改变访问的实际查询方式，经常使用INDEX提示
?CLUSTER 强制使用集群
?FULL
?HASH
?INDEX语法：/*INDEX (TABLE INDEX1,INDEX2….) */ COLUMN 1,….
当不指定任何INDEX时，优化器会选择最佳的索引
SELECT /*INDEX */ STORE_ID FROM TBL_STORE
?INDEX_ASC 8I开始默认是升序，所以与INDEX同效
?INDEX_DESC
?INDEX_COMBINE用来指定多个位图索引，而不是选择其中最好的索引
?INDEX_JOIN只需访问这些索引，节省了重新检索表的时间
?INDEX_FFS执行一次索引的快速全局扫描，只处理索引，不访问具体表
?INDEX_SS
?INDEX_SSX_ASC
?INDEX_SS_DESC
?NO_INDEX
?NO_INDEX_FFS
?NO_INDEX_SS
1.3使用查询转换提示
对于数据仓库非常有帮助
?FACT
?MERGE
?NO_EXPAND 语法：/*NO_EXPAND */ column1,…
保证OR组合起的IN列表不会陷入困境，/*FIRST_ROWS NO_EXPAND */
?NO_FACT
?NO_MERGE
?NO_QUERY_TRANSFORMATION
?NO_REWRITE
?NO_STAR_TRANSFORMATION
?NO_UNSET
?REWRITE
?STAR_TRANSFORMATION
?UNSET
?USE_CONCAT
1.4使用连接操作提示
显示如何将连接表中的数据合并在一起 ， 可用两提示直接影响连接顺序 。LEADING指定连接顺序首先使用的表 ， ORDERED告诉优化器基于FROM子句中的表顺序连接这些表 ， 并使用第一个表作为驱动表（最行访问的表）
ORDERED语法：/*ORDERED */ column 1,….
访问表顺序根据FROM后的表顺序来
LEADING语法：/*LEADING(TABLE1) */ column 1,….
类似于ORDER，指定驱动表
?NO_USE_HASH
?NO_USE_MERGE
?NO_USE_NL
?USE_HASH前提足够的HASH_AREA_SIZE或PGA_AGGREGATE_TARGET
通常可以为较大的结果集提供最佳的响应时间
?USE_MERGE
?USE_NL通常可以以最快速度返回一个行
?USE_NL_WITH_INDEX
1.5使用并行执行
?NO_PARALLEL
?NO_PARALLEL_INDEX
?PARALLEL
?PARALLEL_INDEX
?PQ_DISTRIBUTE
1.6其他提示
?APPEND 不会检查当前所用块中是否有剩余空间，而直接插入到表中，会直接将数据添加到新的块中 。
?CACHE 会将全表扫描全部缓存到内存中，这样可直接在内存中找到数据 ， 不用在磁盘上查询
?CURSOR_SHARING_EXACT
?DRIVING_SITE
?DYNAMIC_SAMPLING
?MODEL_MIN_ANALYSIS
?NOAPPEND
?NOCACHE
?NO_PUSH_PRED
?NO_PUSH_SUBQ
?NO_PX_JOIN_FILTER
?PUSH_PRED
?PUSH_SUBQ强制先执行子查询 ， 当子查询很快返回少量行时，这些行可以用于限制外部查询返回行数 ， 可极大地提高性能
例：select /* PUSH_SUBQ */ emp.empno,emp.ename
From emp,orders
where emp.deptno=(select deptno from dept where loc=’1’)
?PX_JOIN_FILTER
?QB_NAME
2.调整查询
2.1在V$SQLAREA中选出最占用资源的查询
HASH_VALUE：SQL语句的Hash值 。
ADDRESS：SQL语句在SGA中的地址 。
PARSING_USER_ID：为语句解析第一条CURSOR的用户
VERSION_COUNT：语句cursor的数量
KEPT_VERSIONS：
SHARABLE_MEMORY：cursor使用的共享内存总数
PERSISTENT_MEMORY：cursor使用的常驻内存总数
RUNTIME_MEMORY：cursor使用的运行时内存总数 。
SQL_TEXT：SQL语句的文本（最大只能保存该语句的前1000个字符） 。
MODULE,ACTION：用了DBMS_APPLICATION_INFO时session解析第一条cursor时信息
SORTS: 语句的排序数
CPU_TIME: 语句被解析和执行的CPU时间
ELAPSED_TIME: 语句被解析和执行的共用时间
PARSE_CALLS: 语句的解析调用(软、硬)次数
EXECUTIONS: 语句的执行次数
INVALIDATIONS: 语句的cursor失效次数
LOADS: 语句载入(载出)数量
ROWS_PROCESSED: 语句返回的列总数
select b.username,a.DISK_READS,a.EXECUTIONS,a.DISK_READS/decode(a.EXECUTIONS,0,1,a.EXECUTIONS) rds_exec_ratio,a.SQL_TEXT
from v$sqlarea a ,dba_users b
where a.PARSING_USER_ID=b.user_id and a.DISK_READS100 order by a.DISK_READS desc;
2.2在V$SQL中选出最占用资源的查询
与V$SQLAREA类似
select * from
(select sql_text,rank() over (order by buffer_gets desc) as rank_buffers,to_char(100*ratio_to_report(buffer_gets) over (),'999.99') pct_bufgets from v$sql)
where rank_buffers 11
2.3确定何时使用索引
2当查询条件只需要返回很少的行（受限列）时，则需要建立索引，不同的版本中这个返回要求不同
V5:20%V7:7%V8i,V9i:4%V10g: 5%
查看表上的索引
selecta.table_name,a.index_name,a.column_name,a.column_position,a.table_owner
from dba_ind_columns a
where a.table_owner='CLOUDSEA'
2修正差的索引，可使用提示来限制很差的索引，如INDEX ， FULL提示
2在SELECT 和WHERE中的列使用索引
如: select name from tbl where no=?
建立索引：create index test on tbl(name,no) tablespace cloudsea_index storage(….)
对于系统中很关键的查询，可以考虑建立此类连接索引
2在一个表中有多个索引时可能出现麻烦，使用提示INDEX指定使用索引
2使用索引合并 ， 使用提示INDEX_JOIN
2基于函数索引，由于使用了函数造成查询很慢.必须基于成本的优化模式,参数：
QUERY_REWRITE_ENALED=TRUE
QUERY_REWRITE_INTEGRITY=TRUSTED (OR ENFORCED)
create index test on sum(test);
2.4在内存中缓存表
将常用的相对小的表缓存到内存中，但注意会影响到嵌套循环连接上的驱动表
【oracle怎么优化or oracle怎么优化10万条多表查询的sql】alter table tablename cache;
2.5使用EXISTS 与嵌套子查询 代替IN
SELECT …FROM EMP WHERE DEPT_NO NOT IN (SELECT DEPT_NO FROM DEPTWHERE DEPT_CAT=’A’);
(方法一: 高效)
SELECT ….FROM EMP A,DEPT B WHERE A.DEPT_NO = B.DEPT( ) AND B.DEPT_NO IS NULL AND B.DEPT_CAT( ) = ‘A’
(方法二: 最高效)
SELECT ….FROM EMP E WHERE NOT EXISTS (SELECT ‘X’ FROM DEPT D WHERE D.DEPT_NO = E.DEPT_NO AND DEPT_CAT = ‘A’);
四、使用STATSPACK和AWR报表调整等待和闩锁
1.10GR2里的脚本
在$ORACLE_HOME/RDBMS/ADMIN下
Spcreate.sql通过调用spcusr.sql spctab.sql 和spcpkg.sql创建STATSPACK环境，使用SYSDBA运行它
Spdrop.sql调用sptab.sql和spdusr.sql删除整个STATSPACK环境，使用SYSDBA运行它
Spreport.sql这是生成报表的主要脚本，由PERFSTAT用户运行
Sprepins.sql为指定的数据库和实例生成实例报表
Sprepsql.sql为指定的SQL散列值生成SQL报表
Sprsqins.sql为指定的数据库和实例生成SQL报表
Spauto.sql使用DBMS_JOB自动进行统计数据收集（照相）
Sprepcon.sql配置SQLPLUS变量来设置像阈值这样的内容的配置文件
Spurge.sql删除给定数据库实例一定范围内的快照ID ， 不删除基线快照
Sptrunc.sql截短STATSPACK表里所有性能数据
五、执行快速系统检查1.缓冲区命中率
查询缓冲区命中率
select(1 - (sum(decode(name, 'physical reads',value,0)) /
(sum(decode(name, 'db block gets',value,0))
sum(decode(name, 'consistent gets',value,0))))) * 100 "Hit Ratio"
fromv$sysstat;
oracle中如何优化多句Or的条件 。。。要优化or查询，只要你知道一点：
sql or查询是左截断的，也就是指要or符合条件，就不再走后面的or checking了
明白了这个 ， 唯一的优化方案就是
把越能命中的or语句放到最前面，增加前面减少后面的or 逻辑判断
oracle 查询的sql语句特别慢，是什么原因，是or特别慢吗，用什么优化，急急急?。。?/h2>把查询计划的内容发出来，你这一大堆代码谁能看出来啥啊 。看你的代码这么长 ， 条件那么多 ， 语句用了函数，很多低效的or,not in等操作，另外还用了group by，order by，左右连接等等，如果表数据量很大的话，你这个语句性能不好是预料中的事情 。如果你这条语句无法优化，建议从调整表结构角度考虑
oracle怎么优化or的介绍就聊到这里吧，感谢你花时间阅读本站内容，更多关于oracle怎么优化10万条多表查询的sql、oracle怎么优化or的信息别忘了在本站进行查找喔 。

    推荐阅读


    上一篇：外国人评测华为鸿蒙系统，华为mate20鸿蒙评测

    下一篇：板车直播网红，板车哥纪录片