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

- NAT and firewall under Linux (Linux)

- MySQL common functions summary (Database)

- How UTorrent download the ISO image from the command line (Linux)

- MySQL Installation Troubleshooting (Database)

- xargs Detailed description (Linux)

- Use virt-manager to create and manage virtual machines (Linux)

- The minimum initial use of the Linux operating system RancherOS feelings (Linux)

- You can not ignore the seven Git tips (Linux)

- Common Linux system performance monitoring command (Linux)

- JavaScript property of checkbox.disabled (Programming)

- Install Xshell on Mac OS X (Linux)

- Acting on JavaScript events (Programming)

- CentOS 7 How to connect to a wireless network (Linux)

- Those things packaged using Gradle to Android (Programming)

- mysqldump implement database logical backup (Database)

- VirtualBox modify the size of the virtual machine disk VDI (Linux)

- PostgreSQL export table structure information (Database)

- Shell commonly commands (Linux)

- Oracle Enterprise Linux 64-bit install apache-tomcat-7.0.53 step (Server)

 
         
  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:      
 
- To install Ganglia configuration of experience under CentOS 5.5 (Linux)
- Use backup-manager system backup on Linux (Linux)
- Debian users to install FFmpeg 2.2.2 (Linux)
- OpenWRT environment to build (Linux)
- Oracle rebuild index script (Database)
- Ubuntu 15.10 installation and deployment Swift development environment (Linux)
- HTML5 Fundamentals study notes (Programming)
- Shared directory settings between Linux and Linux (Linux)
- Oracle Client Easy Connection error ORA-12154, TNS-03505 (Database)
- Zabbix Agent (Server)
- AngularJS application unit testing started (Programming)
- MongoDB, Cassandra, HBase transaction design strategy (Database)
- 25 Git Usage Tips (Linux)
- Install Kali Linux via PXE network (Linux)
- Android Studio Getting Started Hello World (Programming)
- To install OwnCloud 7.0.4 under Ubuntu (Linux)
- How to install MySQL on Linux Dock (Database)
- Linux modify the network interface name (Linux)
- CentOS6 5 Configure SSH password Free (Linux)
- CentOS 7.0 running Docker kernel error solution (Server)
     
           
     
  CopyRight 2002-2020 newfreesoft.com, All Rights Reserved.