Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ Oracle 12C RAC optimizer_adaptive_features cause of data into overtime     - Recover Ubuntu 14.04 wakes up from standby in case mouse keyboard appears dead (Linux)

- Linux system started to learn: Teaches you install Fedora 22 on VirtualBox (Linux)

- The first IOS Objective-C program (Programming)

- To compile install and test Swift under Linux (Linux)

- VMware Workstation virtual machine cloning (Linux)

- U disk to install Windows and Ubuntu 15.04 dual system (Programming)

- CentOS system Amoeba + MySQL Master-slave configuration (Database)

- Java interface and inheritance (Programming)

- Debian Stable (Jessie 8.1) of normal.mod not found (Linux)

- Ubuntu 15.04 and CentOS 7 to deploy .NET, Mono and DNX (Server)

- How to use awk command in Linux (Linux)

- GitLab issued Merge Request return error 500 when the two solutions log (Linux)

- Ubuntu 14.10 installation SecureCRT 7.3 (Linux)

- Mac Docker deploy development environment (Server)

- Six Ways to view slides can be implemented Android (Programming)

- Create Your Own Docker base image in two ways (Linux)

- Supervisor Installation and Configuration (Server)

- How to Start a Linux application running in the background using the terminal mode (Linux)

- Linux installed Cisco Packet Tracer (Linux)

- MySQL 5.6 master-slave replication configuration (Database)

 
         
  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:      
 
- Linux operating system security management skills (Linux)
- The Linux firewall is configured to use proxy (Linux)
- Linux kernel socket protocol stack routing lookup cache mechanism (Linux)
- Linux Bash share tips for getting started (Linux)
- Apache POI Excel Document Processing (Linux)
- ApacheDS configuration of users and user groups to achieve SSO (Server)
- Ubuntu Gnome and Fedora 22 Gnome desktop, extended to achieve global menu (Linux)
- DRBD switchover (Server)
- Linux System Getting Started Learning: install software packages on Ubuntu and Fedora (Linux)
- MySQL optimization tabs (Database)
- To install Emacs under CentOS 6.5 (Linux)
- Ubuntu install Liferea news subscription software (Linux)
- Docker Basic Concepts (Linux)
- Ubuntu configuration SVN and http mode access (Server)
- Unsafe reboot start (Linux)
- Xmanager Remote Desktop connection CentOS (Linux)
- IronPython and C # to interact (Programming)
- PostgreSQL procedural language learning (Database)
- Windows Remote Desktop Management CentOS 6.4 (Linux)
- Disk partition MBR (Linux)
     
           
     
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.