Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ Oracle 12C RAC optimizer_adaptive_features cause of data into overtime     - By way of a binary installation innobackupex (Database)

- Linux System Getting Started Learning: Repair fatal error openssl aes h no such file or directory (Linux)

- How do I delete a NEEDS RECOVERY rollback state of undo tablespace (Database)

- Learning C ++ Standard Template Library and data structures (Programming)

- JavaScript prototype and prototype chain and project combat (Programming)

- UNIX file permissions in the "set user ID bit" (Linux)

- Linux server security settings to close unused ports (Linux)

- Fast Learning Clojure (Programming)

- MongoDB upgrade from 2.4.9 to 2.6.0 and PHP record of mongo extension upgrade from 1.4.5 to 1.5.1 (Database)

- Ubuntu How to mount iso file (Linux)

- C ++ containers (Programming)

- How to improve the performance of Ruby On Rails (Linux)

- Deploy Mono 4 and Jexus 5.6 on CentOS7 (Server)

- Linux garbled file delete method (Linux)

- Git common skills (Linux)

- No password on Oracle and MySQL login (Database)

- How to build Mono 3.4.0 / 3.4.1 on Windows (Linux)

- MySQL 5.6 Open full query log (Database)

- To install minimize RHEL / CentOS 7 (Linux)

- Getting Started with Linux system to learn: how to install the Shrew Soft IPsec VPN on Linux (Linux)

 
         
  Oracle 12C RAC optimizer_adaptive_features cause of data into overtime
     
  Add Date : 2018-11-21      
         
         
         
  Problem analysis

10046 event tracking method using the direct formation of the database event log upload for analysis and found that the main difference lies in the following two sql statements have appeared every time when uploading and executing user instead of uploading users, but the database SYS user.


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

SQL ID: frjd8zfy2jfdq Plan Hash: 510421217

SELECT executions, end_of_fetch_count, elapsed_time / px_servers
  elapsed_time, cpu_time / px_servers cpu_time,
  buffer_gets / executions buffer_gets
FROM
 (SELECT sum (executions) as executions, sum (case
  when px_servers_executions> 0 then
  px_servers_executions else executions end)
  as px_servers, sum (end_of_fetch_count) as end_of_fetch_count,
                sum (elapsed_time) as elapsed_time,
  sum (cpu_time) as cpu_time, sum (buffer_gets) as
  buffer_gets FROM gv $ sql
  WHERE executions> 0 AND sql_id =: 1
                              AND parsing_schema_name =: 2)


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- --------- ------------ ----------
Parse 64 0.00 0.00 0 0 0 0
Execute 64 0.24 0.91 0 0 0 0
Fetch 64 0.14 0.42 0 0 0 64
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- --------- ------------ ----------
Parse 64 0.00 0.00 0 0 0 0
Execute 64 0.24 0.91 0 0 0 0
Fetch 64 0.14 0.42 0 0 0 64
------- ------ -------- ---------- ---------- --------- ------------ ----------
total 192 0.39 1.34 0 0 0 64

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Number of plan statistics captured: 64

Rows (st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- -------------------- -------------------------------
         1 1 1 VIEW (cr = 0 pr = 0 pw = 0 time = 20602 us)
         1 1 1 SORT AGGREGATE (cr = 0 pr = 0 pw = 0 time = 20596 us)
         0 0 0 PX COORDINATOR (cr = 0 pr = 0 pw = 0 time = 20573 us)
         0 0 0 PX SEND QC (RANDOM): TQ10000 (cr = 0 pr = 0 pw = 0 time = 0 us)
         0 0 0 VIEW GV $ SQL (cr = 0 pr = 0 pw = 0 time = 0 us)
         0 0 0 FIXED TABLE FIXED INDEX X $ KGLCURSOR_CHILD (ind: 2) (cr = 0 pr = 0 pw = 0 time = 0 us)


Elapsed times include waiting on following events: Event waited on Times Max Wait Total Waited ---------------------------------. ------- Waited ---------- ------------ PX Deq: reap credit 1448 0.00 0.02
  PX Deq: Join ACK 193 0.00 0.25
  IPC send completion sync 128 0.00 0.08
  PX Deq: Parse Reply 128 0.06 0.24
  PX Deq: Execute Reply 128 0.00 0.06
  reliable message 64 0.00 0.06
  PX Deq: Signal ACK EXT 128 0.00 0.01
  PX Deq: Slave Session Stats 128 0.00 0.01
  enq: PS - contention 66 0.00 0.09
  KJC: Wait for msg sends to complete 5 0.00 0.00
  latch: shared pool 1 0.00 0.00
********************************************************************************

SQL ID: b4wp0a8dvkf0 Plan Hash: 4033942373

SELECT executions, end_of_fetch_count, elapsed_time / px_servers
  elapsed_time, cpu_time / px_servers cpu_time,
  buffer_gets / executions buffer_gets
FROM
 (SELECT sum (executions_delta) as EXECUTIONS,
  sum (case when px_servers_execs_delta> 0
  then px_servers_execs_delta else
  executions_delta end) as px_servers,
  sum (end_of_fetch_count_delta) as end_of_fetch_count,
  sum (elapsed_time_delta) as ELAPSED_TIME, sum (cpu_time_delta)
  as CPU_TIME, sum (buffer_gets_delta) as BUFFER_GETS
       FROM DBA_HIST_SQLSTAT s,
  V $ DATABASE d, DBA_HIST_SNAPSHOT sn
                           WHERE s.dbid = d.dbid
       AND bitand (nvl (s.flag, 0), 1) = 0 AND
  sn.end_interval_time> (select
  systimestamp at TIME ZONE dbtimezone from dual) - 7
                         AND s.sql_id =: 1
   AND s.snap_id = sn.snap_id AND
  s.instance_number = sn.instance_number AND s.dbid = sn.dbid
                             AND parsing_schema_name =: 2)


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- --------- ------------ ----------
Parse 64 0.00 0.00 0 0 0 0
Execute 64 0.08 0.08 0 0 0 0
Fetch 64 0.13 0.27 0 1170 0 64
------- ------ -------- ---------- ---------- --------- ------------ ----------
total 192 0.21 0.36 0 1170 0 64

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Number of plan statistics captured: 3

Rows (st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- -------------------- -------------------------------
         1 1 1 VIEW (cr = 18 pr = 0 pw = 0 time = 6289 us cost = 17 size = 78 card = 1)
         1 1 1 SORT AGGREGATE (cr = 18 pr = 0 pw = 0 time = 6282 us)
         0 0 0 NESTED LOOPS (cr = 18 pr = 0 pw = 0 time = 6263 us cost = 15 size = 124 card = 1)
         0 0 0 NESTED LOOPS (cr = 18 pr = 0 pw = 0 time = 6258 us cost = 15 size = 124 card = 1)
         0 0 0 HASH JOIN (cr = 18 pr = 0 pw = 0 time = 6256 us cost = 14 size = 97 card = 1)
         0 0 0 NESTED LOOPS (cr = 18 pr = 0 pw = 0 time = 6244 us cost = 14 size = 97 card = 1)
         0 0 0 STATISTICS COLLECTOR (cr = 18 pr = 0 pw = 0 time = 6241 us)
         0 0 0 NESTED LOOPS (cr = 18 pr = 0 pw = 0 time = 6223 us cost = 13 size = 81 card = 1)
         1 1 1 MERGE JOIN CARTESIAN (cr = 0 pr = 0 pw = 0 time = 5648 us cost = 0 size = 10 card = 1)
         1 1 1 FIXED TABLE FULL X $ KCCDI (cr = 0 pr = 0 pw = 0 time = 3449 us cost = 0 size = 10 card = 1)
         1 1 1 BUFFER SORT (cr = 0 pr = 0 pw = 0 time = 2187 us cost = 0 size = 0 card = 1)
         1 1 1 FIXED TABLE FULL X $ KCCDI2 (cr = 0 pr = 0 pw = 0 time = 2136 us cost = 0 size = 0 card = 1)
         0 0 0 PARTITION RANGE ITERATOR PARTITION: KEY KEY (cr = 18 pr = 0 pw = 0 time = 568 us cost = 13 size = 71 card = 1)
         0 0 0 TABLE ACCESS BY LOCAL INDEX ROWID BATCHED WRH $ _SQLSTAT PARTITION: KEY KEY (cr = 18 pr = 0 pw = 0 time = 511 us cost = 13 size = 71 card = 1)
         0 0 0 INDEX RANGE SCAN WRH $ _SQLSTAT_INDEX PARTITION: KEY KEY (cr = 18 pr = 0 pw = 0 time = 477 us cost = 11 size = 0 card = 2) (object id 8299)
         0 0 0 TABLE ACCESS BY INDEX ROWID WRM $ _SNAPSHOT (cr = 0 pr = 0 pw = 0 time = 0 us cost = 1 size = 16 card = 1)
         0 0 0 INDEX UNIQUE SCAN WRM $ _SNAPSHOT_PK (cr = 0 pr = 0 pw = 0 time = 0 us cost = 0 size = 0 card = 1) (object id 8695)
         0 0 0 TABLE ACCESS FULL WRM $ _SNAPSHOT (cr = 0 pr = 0 pw = 0 time = 0 us cost = 1 size = 16 card = 1)
         0 0 0 INDEX UNIQUE SCAN WRM $ _SNAPSHOT_PK (cr = 0 pr = 0 pw = 0 time = 0 us cost = 0 size = 0 card = 1) (object id 8695)
         0 0 0 TABLE ACCESS BY INDEX ROWID WRM $ _SNAPSHOT (cr = 0 pr = 0 pw = 0 time = 0 us cost = 1 size = 27 card = 1)
         0 0 0 FAST DUAL (cr = 0 pr = 0 pw = 0 time = 0 us cost = 2 size = 0 card = 1)


Elapsed times include waiting on following events:
  Event waited on Times Max. Wait Total Waited
  ---------------------------------------- Waited --------- --------------
  Disk file operations I / O 3 0.00 0.00
  control file sequential read 448 0.00 0.18
********************************************************************************
After data analysis, these two statements is due to the opening of the new features of Oracle 12C generated, this feature is "optimizer_adaptive_features". Its main function is to collect during the statement execution statistics table in real-time, convenient Oracle choose a more accurate implementation plan.

However, this feature is very RAC loss of performance because it needs to find a global view gv $ sql data, in the case of multiple concurrent instances will be executed, refer to this information.

Because "optimizer_adaptive_features" declared parameters can be obtained good results in the OLAP data warehouse environment, the actual light in weight upload queries OLTP system, you can turn off this new feature.

Experimental testing

Because this parameter supports session-level and system-level adjustments, since you can insert time and query time within a single testing session.

Insertion test
Adopt and submit multiple TARGETTABLE data.
When you open "optimizer_adaptive_features" 2.2 seconds, turn the feature off 0.375.

Query test
Using the system to execute up statements to be tested.

When you open "optimizer_adaptive_features" 0.05 seconds, turn the feature off 0.02 seconds.

Close Information
Because "optimizer_adaptive_features" dynamic parameters do not need to restart the system-level changes. Already available in a test environment test.

Statement is executed

 alter system set optimizer_adaptive_features = false scope = both;
     
         
         
         
  More:      
 
- How do I upgrade to Ubuntu 15.04 (Beta) (Linux)
- Build Nginx + uWSGI + Flask operating environment under CentOS 6.4 tutorial (Server)
- Installation Enpass secure password manager on Ubuntu (Linux)
- MySQL Study of --Percona Server 5.5 Upgrade 5.6 (Database)
- CentOS6 install Redis (Database)
- CMake Quick Start Tutorial (Linux)
- Netfilter / Iptables Comments (Linux)
- Installation image conversion tool on your Ubuntu Converseen 0.8.1 (Linux)
- 14.04.3 Ubuntu configuration and successfully compiled source code Android 6.0 r1 (Programming)
- Cobbler remotely install CentOS system (Linux)
- Ant command-line compiler Android project (Programming)
- Spring MVC Exception Handling (Programming)
- To install Samba server on CentOS 6.6 (Server)
- MySQL database to open a remote connection method (Database)
- Modify grub solve computer startup error: ERROR 17 (Linux)
- To delete the directory and all specified files under the Mac (Linux)
- grep command Detailed and relevant examples (Linux)
- Linux security settings Basics (Linux)
- Under Ubuntu on how to use iptables firewall (Linux)
- Linux security configuration (Linux)
     
           
     
  CopyRight 2002-2020 newfreesoft.com, All Rights Reserved.