oracle修改最后一位数字,Oracle小问题之五（smQ)

最新推荐文章于 2021-06-17 18:11:58 发布

颖爷

最新推荐文章于 2021-06-17 18:11:58 发布

阅读量396

点赞数

文章标签： oracle修改最后一位数字

本文探讨了Oracle数据库中的SQL性能问题，通过分析执行计划和等待事件，发现全表扫描导致的性能瓶颈。提出了使用hashjoin替代nestedloopjoin、调整表的pctfree值、使用hashcluster以及优化统计信息等解决方案。通过对 latch 和缓冲池的监控，以及对索引和全表扫描的比较，展示了优化策略如何显著提升查询性能。

摘要由CSDN通过智能技术生成

生成一维的数字

SELECT rn

FROM (

SELECT level rn FROM dual connect by

level<=99999) a

where rn>=10000

;

DB_BLOCK_CHECKING

DB_LOST_WRITE_PROTECT

在一个表x上有索引的表执行下面两个SQL

,第二个要慢了几十倍

select * from t100 where x= 8888;

select * from t100 where x=

floor(dbms_random.value(2000000,0));

看执行计划，差别很大：

第一个走索引，

|* 2 | INDEX

RANGE

SCAN | T100_IX_1

| 1

| | 3

第二个是全表扫描：

analyze table ANDZEN.t100 delete statistics;

exec DBMS_STATS.gather_table_stats(ownname=>'ANDZEN',tabname=>'T100',estimate_percent=>15,options=>'GATHER',block_sample=>TRUE

,method_opt=>'for table for all indexed columns',degree=> 4 )

;

latch: cache

buffers chains 诊断追踪

select * from (select

sid,event,p1,p2,p3,p1text,WAIT_TIME,SECONDS_IN_WAIT from

v$session_wait where wait_class# <> 6 order

by wait_time desc) where rownum <=10;

SELECT name,

gets,

misses,

sleeps,

immediate_gets,

immediate_misses FROM v$latch

WHERE name = 'cache buffers chains';

SELECT *

FROM (SELECT O.OWNER, O.OBJECT_NAME, O.OBJECT_TYPE, SUM(TCH)

TOUCHTIME

FROM X$BH B, DBA_OBJECTS O

WHERE B.OBJ = O.DATA_OBJECT_ID

AND B.TS# > 0

GROUP BY O.OWNER, O.OBJECT_NAME, O.OBJECT_TYPE

ORDER BY SUM(TCH) DESC)

WHERE ROWNUM <= 10;

SELECT *

FROM ( SELECT addr,

ts#,

file#,

dbarfil,

dbablk,

tch

FROM x$bh

ORDER BY tch DESC)

WHERE ROWNUM < 11;

主要原因和应对：

sql执行频率很高，并且又是同时访问还存在全表扫描现象

存在较大的排序

如果有可能使用hash join代替nested loop join

使用hash cluster建表的方式减缓热块现象

调整表的pctfree值，将数据尽可能的分布到多个块中

example:

select * from v$latch_misses where sleep_count>300 order by

--8 cache buffers

chains kcbgtcr: kslbegin

excl 0 42185302 41430510 2148970 kcbgtcr: kslbegin excl

select sid,event,p1text,p1,p2text,p2,p3text,p3,wait_class from

v$session_wait where wait_class<>'Idle'

select se.sid,se.serial#,se.sql_id,se.prev_sql_id,sql_text from

v$session se,v$sql sq where se.sql_id=sq.sql_id and se.sid=946;

--1 946 26136 7p5ds3m5vtg1y 89camvzd2vfu8 select state_id, context, groupid, clusterid, broadcast, syn_id

from sde.EPGIS_CLUSTER_SYN$ a where (( groupid=3 and clusterid !=3)

or ( groupid !=3 and broadcast = 1 and exists (select state_id from

sde.EPGIS_SYN_PARTITIONS$ where syn_id = a.syn_id and

(partition_id=-1 or (partition_id >= 9 and partition_id

<=12))))) and syn_id > 1149467 order by syn_id

select * from v$session_wait where wait_class<>'Idle'

--根据P1值对应latch的地址的关系，查看相关latch的信息

select child# "cCHILD",ADDR "sADDR",GETS "sGETS",MISSES

"sMISSES",SLEEPS "sSLEEPS"

from v$latch_children

where name='cache buffers chains' and ADDR in

('07000007B9BD9418','07000007C0B38D90','07000007B9BD9418')

order by 5,1,2,3;

--查询热点buffer出自哪些对象

select e.owner ||'.'|| e.segment_name segment_name,e.extent_id

extent#,x.dbablk-e.block_id+1 block#,x.tch,l.child#

from sys.v$latch_children l,sys.x$bh x,sys.dba_extents e

where x.hladdr in ('07000007B9BD9418','07000007C0B38D90')

and e.file_id=x.file# and x.hladdr=l.addr and x.dbablk between

e.block_id and e.block_id + e.blocks-1

order by x.tch desc;

Hash

cluster的高性能优势没有出现，反而低于普通索引，解决？

hash cluster 的查询速度难以提高：

维持在单线程平均每秒查询50次左右，而同期对于b*tree索引的索引查询都可以达到500/s的查询性能

多次测试排除了几种可能性：

数据类型

hashkey数量

hash函数效率

排除执行计划异常

cache参数

最后在“数据量”有点难以确定，前面的几个测试以小于现有表的数量测试，发现性能一切正常，且结构相同。

经过多次测试，进跟踪使用4线程并行执行，发现大量等待 db file sequential read ；

基本疑凶大致锁定；

采用手段

alter cluster my_hcluster01 cache;

alter cluster my_hcluster01 STORAGE ( buffer_pool keep );

并发四线程测试，cmd窗口里面久违的超爽刷屏的界面终于出现！！

None

49999

Est time :seconds

Est time :microseconds

0:01:34.364397

优化后四个并行线程(4core I5 cpu)执行结果如下，每秒2100次查询：

SQL> select 50000*4/95 from dual;

50000*4/95

----------

2105.26315

32G

100G的4k块pagetable多大

256M ，768M

select 32*power(1024,3)/4/1024*32/power(1024,2) from dual ；

100g/4/K=

100*1024*1024

SELECT 100*1024*1024/4*32 from DUAL ；

SQL> select 32*power(1024,3)/4/1024*32/power(1024,2) from dual;

32*POWER(1024,3)/4/1024*32/POWER(1024,2)

----------------------------------------

256

SQL> SELECT 100*1024*1024/4*32 from DUAL;

100*1024*1024/4*32

------------------

838860800

How to

get an sql_id exec

plan from Oracle?

select output from