当前位置: 首页 > news >正文

做熊猫tv网站的目的天津企业seo

做熊猫tv网站的目的,天津企业seo,国外排版网站,网站建设企业排名所有的sql 通过dblink 查询全部等待中, 同一个SQL 20多个session 在跑,等待事件network,可能怀疑是不是网络断开了,导致没有返回 执行sql 如下: BEGIN Xdblink ; END; 去到dblink 所在的db,发现20多个sql在…

所有的sql 通过dblink 查询全部等待中,

同一个SQL 20多个session 在跑,等待事件network,可能怀疑是不是网络断开了,导致没有返回

执行sql 如下:

BEGIN X@dblink ; END;

去到dblink 所在的db,发现20多个sql在执行一个sql,等待事件fast object check point

发现同一个sql的两个执行计划,最终发现PK index失效导致。

The following SELECT Statement shows different elapsed time from the original database to the coloning database.

For the excution plan, database parameters and rows of table are same on two databases.

SELECT *
FROM xxx MMT
WHERE mmt.xxx_id = 1661


1. On Original database: elapsed time is 10s.

2. On cloning database: elapsed time is 72s. 

The direct path read wait event from cloning database consumes 65s in the below 10046 trace.

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.04 0.03 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 7.75 72.17 611660 612506 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 7.79 72.21 611660 612506 0 1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 173
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
  1 1 1 TABLE ACCESS FULL xxx (cr=612506 pr=611660 pw=0 time=0 us cost=167711 size=277 card=1)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net more data to client 1 0.00 0.00
enq: KO - fast object checkpoint 1 0.00 0.00
direct path read 38390 0.45 65.02 <<<<<<<<<<<<<<< direct path read : 65s
SQL*Net message from client 2 37.56 37.56

enq: KO - fast object checkpoint 1 0.00 0.00 --SGA 压力 不足

1.

"enq: KO - fast object checkpoint" is a wait event that is waiting until the checkpoints finishes in a particular object level.
At checkpoints, dirty buffers(updated buffers) on the buffer cache has to be written out to the disk by DBWR.
This checkpoint occurs by object level so if the process is not related to this particular object, the client should not have a delay from it.
However when DBWR process is under high load, it might affect to the performance.

2. 

All reporting queries are doing full table scans concurrently. Oracle uses direct path reads instead of db file scattered reads. And direct path reads require a checkpoint
 

SOLUTION

"_serial_direct_read"=NEVER

 WARNING: This solution is for non-Exadata system only. On Exadata system please don't disable direct path reads as it is critical to SmartIO benefits.

3. 就是cache不足, keep为0 所以不停的读盘

  • EM displays a huge amount of 'Application' and 'Other' on the 'Average active sessions' chart compared to normal
  • CKPT process consistently high in the process list
  • AWR top 5 wait events show :
    • "enq: KO - fast object checkpoint"
    • "reliable message"
  • ASH report Top SQL with Top Events shows queries with high waits on "enq: KO - fast object checkpoint"
  • A review of the tables in the query revealed that the table is defined to use the keep cache:

    ...
    STORAGE(INITIAL 327680 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
    BUFFER_POOL KEEP FLASH_CACHE    <<<######
    DEFAULT CELL_FLASH_CACHE DEFAULT)

  • The Initialization  parameters section in AWR report indicate that DB_KEEP_CACHE_SIZE was not set,

CAUSE

Tables involved in the process that is waiting on these events have buffer_pool = keep but the keep buffer pool was not configured on these instances.
Altering these tables to use the default buffer pool resolved the issue.

This issue was due to the excessive direct path reads that occurred as a result of the misconfiguration explained above and is dealt with in:
Bug 12530276 High 'direct path read' waits when buffer pools are not setup.
 

SOLUTION

1. The bug is fixed in 11.2.0.3

2. Workarounds:

  • Change the object definitions with keep buffer cache to use the default buffer cache
  • Allocate a keep buffer pool by setting parameter DB_KEEP_CACHE_SIZE to a non-zero value

CHANGES

CAUSE

It's due to buffer cache overflow on cloning database. So, the query become slow when data reads from the disk.

Note : The SGA_SIZE is 32G. However the the current usage of buffer cache(__db_cache_size) reaches to 30G. So, It indicted that buffer cache was almost fully used by other hot data.

SOLUTION

Options:

1. Flush buffer cache only when it's a test server.

    sqlplus / as sysdba


   SQL> alter system flush buffer_cache;

 - or -

2. keep the data of table into keep buffer cache

-- Check the table size
select BYTES/1024/1024 as SIZE_MB , table_name, owner from dba_segments where table_name = '<TABLE_NAME>';


-- Check KEEP buffer cache size


select component, current_size
from v$memory_dynamic_components
where component = 'KEEP buffer cache';


-- Increase keep buffer cache size if needed


alter system set db_keep_cache_size = 500m scope=both;

Note: the size 500m is an example.



-- Keep whole data of table into keep buffere cache
alter table <TABLE_NAME> storage( buffer_pool keep);

-- Check the table whether in keep buffer cache
select buffer_pool
from dba_tables
where table_name = '<TABLE_NAME>';

http://www.hengruixuexiao.com/news/21319.html

相关文章:

  • 建站购物网站如何做市场推广方案
  • 湖南网站建设制作西安网站seo外包
  • 十大最佳摄影网站如何做网络宣传推广
  • 网站建设多少钱怎么卖百度大数据查询
  • 建设银行网站看不到e商十大经典案例
  • 河北建设工程信息网网seo排名优化点击软件有哪些
  • 可做区域代理的网站百度搜索风云排行榜
  • .net 网站开发书籍网店怎么开
  • 做暧小视频xo网站青岛百度seo代理
  • 餐饮网站设计公司网络营销有几种方式
  • 十分钟日本在线观看视频seo关键词选择及优化
  • sm做任务的网站有哪些怎么建立网站快捷方式
  • 没效果优化防疫措施+科学精准防控
  • 商城的网站建设安徽seo顾问服务
  • 北京网站策划服务房地产十大营销手段
  • 重庆企业网站推广59软文网
  • 公司建立网站费用公众号推广接单平台
  • wordpress评论可见插件徐州seo企业
  • 住房和城乡建设部贰级建造师网站营销软件哪个好
  • 什么蓝色 适合公司网站主色站长之家ip查询
  • 永久免费手机建站长春网站建设方案优化
  • c++语言做网站网络媒体有哪些
  • 建设网站容易吗长沙网络公司排名
  • 网站宣传的手段有哪些seo中国
  • 做海岛旅游预定网站的深圳网络推广营销公司
  • 公司做英文网站排名优化公司哪家靠谱
  • 成都网站建设哪里好点郑州百度关键词seo
  • 合肥中科大网站开发优化营商环境建议
  • 网站租空间多少钱seo优化推广技巧
  • 网站更换标题查询网138网站域名