C/C++教程

oracle 12c 数据库上编写SQL语句时卡顿的问题解决

本文主要是介绍oracle 12c 数据库上编写SQL语句时卡顿的问题解决,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

开发环境的一个用户数据测试库,是12.1.0.2的数据库。  
使用pl/sql developer连接后,编写sql语句,例如:  
Select a.Id, a.门诊号 From 病人挂号记录 A Where NO = 'Q0000453'  

输入字段时,输完a,再输点之后,大约要5秒才弹出字段选择,选择了字段之后,按回车,大约需要8秒才能继续进行输入。  
这么慢的速度,严重影响了开发人员的调试和编码。  

并且,执行任何单表查询,无论数据多少,也是需要卡一下(  约2秒左右)才返回结果。  

初步判断,可能pl/sql developer的操作过程中涉及到一些系统表的查询,可能数据字典和系统固定对象的统计信息没有收集,导致有些内部视图的访问太慢。  
于是收集  数据字典和系统固定对象的统计信息  
14:02:25 SQL> exec dbms_stats.gather_dictionary_stats;  
PL/SQL 过程已成功完成。  
已用时间:  00: 02: 34.50  
15:23:01 SQL> exec dbms_stats.gather_fixed_objects_stats  
PL/SQL 过程已成功完成。  
已用时间:  00: 03: 56.13  

完成之后,清空共享池:alter system flush shared_pool;  

验证之前慢的操作,仍然还是慢,于是做了一个sql trace,根据跟踪文件,发现以下类似的sql在编写sql语句期间被自动执行了:  
原来是进行了动态采样,这些采样语句的IO都比较高,所以会这么卡。  

SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring   
  optimizer_features_enable(default) no_parallel result_cache(snapshot=3600)   
  */ C1, C2, C3   
FROM  
 (SELECT /*+ qb_name("innerQuery") NO_INDEX_FFS( "O2")  */ 4294967295 AS C1,   
  COUNT(*) AS C2, SUM(CASE WHEN ("O2"."TYPE#"=88) THEN 1 ELSE 0 END) AS C3    
  FROM SYS."OBJ$" SAMPLE BLOCK(44.1745, 8) SEED(1)  "O2" WHERE ("O2"."TYPE#"=  
  88)) innerQuery  




call     count       cpu    elapsed       disk      query    current        rows  
------- ------  -------- ---------- ---------- ---------- ----------  ----------  
Parse       12      0.00       0.00          0          0          0           0  
Execute     12      0.00       0.00          0          0          0           0  
Fetch       12      0.14       0.08          0       9564          0          12  
------- ------  -------- ---------- ---------- ---------- ----------  ----------  
total       36      0.14       0.08          0       9564          0          12  


Misses in library cache during parse: 1  
Optimizer mode: ALL_ROWS  
Parsing user id: 310     (recursive depth: 2)  


Rows     Row Source Operation  
-------  ---------------------------------------------------  
      1  VIEW  (cr=797 pr=0 pw=0 time=5509 us cost=219 size=31 card=1)  
      1   SORT AGGREGATE (cr=797 pr=0 pw=0 time=5506 us)  
      0    TABLE ACCESS SAMPLE OBJ$ (cr=797 pr=0 pw=0 time=5497 us cost=219 size=16 card=1)  


********************************************************************************  


SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring   
  optimizer_features_enable(default) no_parallel result_cache(snapshot=3600)   
  */ C1, C2, C3   
FROM  
 (SELECT /*+ qb_name("innerQuery") NO_INDEX_FFS( "DO")  */ 4294967295 AS C1,   
  COUNT(*) AS C2, SUM(CASE WHEN ("DO"."TYPE#"=92) THEN 1 ELSE 0 END) AS C3    
  FROM SYS."OBJ$" SAMPLE BLOCK(44.1745, 8) SEED(1)  "DO" WHERE ("DO"."TYPE#"=  
  92)) innerQuery  




call     count       cpu    elapsed       disk      query    current        rows  
------- ------  -------- ---------- ---------- ---------- ----------  ----------  
Parse        8      0.00       0.00          0          0          0           0  
Execute      8      0.00       0.00          0          0          0           0  
Fetch        8      0.04       0.04          0       6376          0           8  
------- ------  -------- ---------- ---------- ---------- ----------  ----------  
total       24      0.04       0.04          0       6376          0           8  


Misses in library cache during parse: 1  
Optimizer mode: ALL_ROWS  
Parsing user id: 310     (recursive depth: 2)  


Rows     Row Source Operation  
-------  ---------------------------------------------------  
      1  VIEW  (cr=797 pr=0 pw=0 time=5716 us cost=219 size=31 card=1)  
      1   SORT AGGREGATE (cr=797 pr=0 pw=0 time=5711 us)  
      0    TABLE ACCESS SAMPLE OBJ$ (cr=797 pr=0 pw=0 time=5704 us cost=219 size=16 card=1)  


查了一下动态采样的参数,其值居然是3,修改参数值为0,关闭动态采样:  
alter system set optimizer_dynamic_sampling=0 scope=both;  

执行后大部分操作有一定的性能改善,但没有完全解决,于是又重启了数据库,再进行相应的操作,就不再卡了。  

这篇关于oracle 12c 数据库上编写SQL语句时卡顿的问题解决的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!