oracle缓冲区命中率低,数据库缓冲区命中率特别低

我发现数据库缓冲区命中率特别低后,对buffer_cache_size进行了增加,从256M增加到1G,可是没有明显的改善,下面是最近的报告STATSPACK report forDB NameDB IdInstance Inst Num Release Cluster Host------------ ----------- ------------ -...

weixin_39638623

511人浏览 · 2021-04-05 14:29:11

weixin_39638623 · 2021-04-05 14:29:11 发布

我发现数据库缓冲区命中率特别低后,对buffer_cache_size进行了增加,从256M增加到1G,可是没有明显的改善,下面是最近的报告

STATSPACK report for

DB Name DB Id Instance Inst Num Release Cluster Host

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

ORCL 1186791764 orcl 1 9.2.0.4.0 NO P570

Snap Id Snap Time Sessions Curs/Sess Comment

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

Begin Snap: 5 15-5月 -07 14:44:45 72 3.6

End Snap: 6 15-5月 -07 16:13:39 72 3.4

Elapsed: 88.90 (mins)

Cache Sizes (end)

~~~~~~~~~~~~~~~~~

Buffer Cache: 1,024M Std Block Size: 4K

Shared Pool Size: 256M Log Buffer: 512K

Load Profile

~~~~~~~~~~~~ Per Second Per Transaction

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

Redo size: 6,369.52 3,300.47

Logical reads: 15,786.10 8,179.82

Block changes: 26.41 13.68

Physical reads: 11,167.57 5,786.65

Physical writes: 2.33 1.21

User calls: 85.73 44.42

Parses: 45.17 23.41

Hard parses: 0.22 0.12

Sorts: 6.64 3.44

Logons: 0.03 0.02

Executes: 64.83 33.59

Transactions: 1.93

% Blocks changed per Read: 0.17 Recursive Call %: 11.13

Rollback per transaction %: 0.97 Rows per Sort: 4.33

Instance Efficiency Percentages (Target 100%)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Buffer Nowait %: 100.00 Redo NoWait %: 100.00

Buffer Hit %: 29.26 In-memory Sort %: 99.99

Library Hit %: 99.68 Soft Parse %: 99.51

Execute to Parse %: 30.32 Latch Hit %: 99.58

Parse CPU to Parse Elapsd %: 92.54 % Non-Parse CPU: 99.33

Shared Pool Statistics Begin End

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

Memory Usage %: 86.73 92.58

% SQL with executions>1: 17.92 20.69

% Memory for SQL w/exec>1: 16.34 19.49

Top 5 Timed Events

~~~~~~~~~~~~~~~~~~ % Total

Event Waits Time (s) Ela Time

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

CPU time 1,993 79.35

db file scattered read 5,579,635 470 18.70

db file sequential read 1,273,623 17 .67

log file sync 10,236 12 .48

log file parallel write 10,336 9 .37

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

Wait Events for DB: ORCL Instance: orcl Snaps: 5 -6

-> s - second

-> cs - centisecond - 100th of a second

-> ms - millisecond - 1000th of a second

-> us - microsecond - 1000000th of a second

-> ordered by wait time desc, waits desc (idle events last)

Avg

Total Wait wait Waits

Event Waits Timeouts Time (s) (ms) /txn

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

db file scattered read 5,579,635 0 470 0 542.0

db file sequential read 1,273,623 0 17 0 123.7

log file sync 10,236 0 12 1 1.0

log file parallel write 10,336 9,977 9 1 1.0

log file sequential read 103 0 5 45 0.0

control file parallel write 1,755 0 2 1 0.2

db file parallel write 1,086 0 2 2 0.1

latch free 194 143 1 4 0.0

async disk IO 193 0 0 2 0.0

buffer busy waits 3,945 0 0 0 0.4

direct path read 79 0 0 2 0.0

control file sequential read 1,668 0 0 0 0.2

SQL*Net more data to client 631 0 0 0 0.1

direct path write 33 0 0 0 0.0

log file switch completion 2 0 0 2 0.0

log file single write 2 0 0 0 0.0

LGWR wait for redo copy 1 0 0 0 0.0

SQL*Net message from client 457,328 0 100,515 220 44.4

SQL*Net message to client 457,328 0 0 0 44.4

SQL*Net more data from clien 6,140 0 0 0 0.6

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

Background Wait Events for DB: ORCL Instance: orcl Snaps: 5 -6

-> ordered by wait time desc, waits desc (idle events last)

Avg

Total Wait wait Waits

Event Waits Timeouts Time (s) (ms) /txn

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

log file parallel write 10,334 9,975 9 1 1.0

log file sequential read 103 0 5 45 0.0

control file parallel write 1,755 0 2 1 0.2

db file parallel write 1,086 0 2 2 0.1

async disk IO 193 0 0 2 0.0

control file sequential read 1,267 0 0 0 0.1

direct path read 25 0 0 1 0.0

latch free 1 0 0 4 0.0

log file single write 2 0 0 0 0.0

direct path write 25 0 0 0 0.0

LGWR wait for redo copy 1 0 0 0 0.0

rdbms ipc message 25,935 15,298 30,996 1195 2.5

pmon timer 1,798 1,798 5,208 2897 0.2

smon timer 17 17 5,016 ###### 0.0

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

SQL ordered by Gets for DB: ORCL Instance: orcl Snaps: 5 -6

-> End Buffer Gets Threshold: 10000

-> Note that resources reported for PL/SQL includes the resources used by

all SQL statements called within the PL/SQL code. As individual SQL

statements are also reported, it is possible and valid for the summed

total % to exceed 100

腾讯云开发者社区

腾讯云面向开发者汇聚海量精品云计算使用和开发经验，营造开放的云计算技术生态圈。

更多推荐

Springboot+MySQL 实现从数据库获取数据展示到前端

1.运行效果效果图如下：2.创建数据库表数据库名为"springboot",数据库表名为"user"，数据库表里面放了5条数据，字段分别为：“id”，“name”，“age”，“email”。3.新建SpringBoot项目1、选择Spring Initial...

腾讯云开发者社区

数据库实验二数据库的简单查询和连接查询实验

– l）用Transact-SQL语句表示下列操作．在学生选课库中实现其数据查询操作．– ①求数学系学生的学号和姓名。select sno,sname from student where sdept='MA';– ②求选修了课程的学生学号。select distinct sno from sc;– ③求选修C1课程的学生学号和成绩，并要求对查询结果按成绩降序排列，如果成绩相同则按学号升序排列。s

腾讯云开发者社区

Spring Boot Mybatis数据库datasource多数据源配置

application.yaml文件配置spring:datasource:db1:driver-class-name: com.mysql.cj.jdbc.Driverjdbc-url: jdbc:mysql://192.168.0.1:3306/ellassay_dev?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBe