Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ Oracle 12C RAC optimizer_adaptive_features cause of data into overtime     - RVM installation instructions (Linux)

- RHEL / CentOS / Fedora Install Nagios 4.0.1 (Linux)

- System-level alias vs Oracle ADR functionality (Database)

- Django1.5 Custom User Model (Programming)

- GDB remote connections RX Probe online debug program (Programming)

- Ubuntu install VMware Workstation 11 tutorials at 14.04 / 14.10 (Linux)

- Depth understanding of DB2 table space (Tablespace) (Database)

- MySQL monitoring tools -orzdba (Database)

- How SSHfs mount a remote file system on Linux (Linux)

- CentOS 7 repair MBR and GRUB (Linux)

- Using Python multithreaded mistakes summary (Programming)

- How to install Bugzilla 4.4 on Ubuntu / CentOS 6.x (Linux)

- Protect your files, modify the Linux value Umask (Linux)

- Teamviewer not start in Linux (Linux)

- To get Java class / jar package path (Programming)

- Amazon EC2 server using Rsync + Inotify real-time synchronization (Server)

- TL-WR703N to install OpenWrt process notes (Linux)

- Embedded Linux Optimization (Programming)

- Json Applications of FastJson (Programming)

- Configuring s3c-linux-2.6.28.6-Real6410 appears Unable to find the QT3 installation (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:      
 
- Remove old kernel on Ubuntu (Linux)
- Single Instance ASM under CRS-4124, CRS-4000 error handling (Database)
- ASP.NET 5 tutorial series (Server)
- Ubuntu development Nodejs (Linux)
- Linux Command-line MySQL summary (Database)
- Debian installation (Linux)
- Linux Network Programming - raw socket Example: sending a UDP packet (Programming)
- CentOS 6.5 install Maven and Nexus warehouse agent (Server)
- The security configuration of Linux (Linux)
- After installing minimize RHEL / CentOS 7 we need to do (Linux)
- SQL Server 2012 failover looksalive check and is alive check (Database)
- GEC2440 easiest a kernel compile Linux2.6.30.4 (Programming)
- Iptables use examples (Linux)
- Linux operating system, the internal and external security overview (Linux)
- MySQL bulk insert data script (Database)
- MongoDB Learning the notes (Database)
- Examples of safety testing system notes for RedHat Linux (Linux)
- Help you enhance Python programming languages 27 (Programming)
- Memcached installation, configuration and monitoring (Server)
- Ubuntu 32 compile Android 4.0.4 Problems (Linux)
     
           
     
  CopyRight 2002-2020 newfreesoft.com, All Rights Reserved.