| |
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; |
|
|
|