Hey my ODC friends ,
Today afternoon I found a very strange phenomenon on oracle 19.3 of Windows platform - querying "SELECT COUNT(*) FROM all_objects" is very rapid on SYS but pretty slow on a COMMON user.
So I decide to use a 10046 event to trace the SQL statement previously mentioned so that expect to find out some valuable clues. Take a look at my operation steps as follows.
(1) on SYS schema:
16:18:35 SYS@ora19c> select count(*) from all_objects;
COUNT(*)
----------
72352
16:18:46 SYS@ora19c>
(2) on C##QWZ schema:
16:28:29 C##QWZ@ora19c> select count(*) from all_objects;
COUNT(*)
----------
55672
16:40:01 C##QWZ@ora19c>
(3) tracking SQL via 10046 event on C##QWZ schema:
SET LINESIZE 150
SET PAGESIZE 150
SET SERVEROUTPUT OFF
ALTER SESSION SET max_dump_file_size = 'UNLIMITED';
ALTER SESSION SET tracefile_identifier = '10046';
ALTER SESSION SET events '10046 trace name context forever,level 12';
select count(*) from all_objects;
ALTER SESSION SET events '10046 trace name context off';
SELECT value FROM v$diag_info WHERE name = 'Default Trace File';
Next generating a trc file named "...10046.trc" such as, "C:\APP\ADMINISTRATOR\VIRTUAL\diag\rdbms\ora19c\ora19c\trace\ora19c_ora_20224_10046.trc".
Afterwards I use this tool "TKPROF" to format the original trc file.
c:\>cd C:\APP\ADMINISTRATOR\VIRTUAL\diag\rdbms\ora19c\ora19c\trace\
C:\app\Administrator\virtual\diag\rdbms\ora19c\ora19c\trace> tkprof ora19c_ora_20224_10046.trc ora19c_ora_20224_10046.txt
TKPROF: Release 19.0.0.0.0 - Development on Fri Mar 20 17:24:58 2020
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
At this moment I find out some clues respectively on both TRC file and TXT.
(1) trc:
PARSING IN CURSOR #2547064615088 len=32 dep=0 uid=108 oct=3 lid=108 tim=515108113935 hv=789896629 ad='7ffbe95f00d8' sqlid='9tz4qu4rj9rdp'
select count(*) from all_objects
END OF STMT
PARSE #2547064615088:c=84774,e=78284,p=0,cr=9,cu=0,mis=1,r=0,dep=0,og=1,plh=4081256306,tim=515108113934
EXEC #2547064615088:c=168,e=168,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=4081256306,tim=515108114232
WAIT #2547064615088: nam='SQL*Net message to client' ela= 2 driver id=1111838976 #bytes=1 p3=0 obj#=62 tim=515108114339
WAIT #2547064615088: nam='PGA memory operation' ela= 9 p1=131072 p2=1 p3=0 obj#=62 tim=515108114473
WAIT #2547064615088: nam='PGA memory operation' ela= 4 p1=131072 p2=1 p3=0 obj#=62 tim=515108114560
WAIT #2547064615088: nam='PGA memory operation' ela= 4 p1=131072 p2=1 p3=0 obj#=62 tim=515108114595
WAIT #2547064615088: nam='PGA memory operation' ela= 5 p1=131072 p2=1 p3=0 obj#=62 tim=515108114629
WAIT #2547064615088: nam='PGA memory operation' ela= 3 p1=131072 p2=1 p3=0 obj#=62 tim=515108114660
WAIT #2547064615088: nam='PGA memory operation' ela= 2 p1=131072 p2=1 p3=0 obj#=62 tim=515108114690
WAIT #2547064615088: nam='PGA memory operation' ela= 2 p1=131072 p2=1 p3=0 obj#=62 tim=515108114719
WAIT #2547064615088: nam='PGA memory operation' ela= 2 p1=131072 p2=1 p3=0 obj#=62 tim=515108114750
WAIT #2547064615088: nam='PGA memory operation' ela= 2 p1=131072 p2=1 p3=0 obj#=62 tim=515108114782
WAIT #2547064615088: nam='PGA memory operation' ela= 2 p1=65536 p2=1 p3=0 obj#=62 tim=515108114872
WAIT #2547064615088: nam='PGA memory operation' ela= 17 p1=65536 p2=2 p3=0 obj#=62 tim=515108114934
WAIT #2547064615088: nam='PGA memory operation' ela= 1 p1=65536 p2=1 p3=0 obj#=62 tim=515108114977
WAIT #2547064615088: nam='PGA memory operation' ela= 2 p1=65536 p2=1 p3=0 obj#=62 tim=515108115019
WAIT #2547064615088: nam='PGA memory operation' ela= 3 p1=131072 p2=1 p3=0 obj#=62 tim=515108115067
WAIT #2547064615088: nam='PGA memory operation' ela= 3 p1=131072 p2=1 p3=0 obj#=62 tim=515108115179
WAIT #2547064615088: nam='PGA memory operation' ela= 2 p1=131072 p2=1 p3=0 obj#=62 tim=515108115212
WAIT #2547064615088: nam='PGA memory operation' ela= 2 p1=131072 p2=1 p3=0 obj#=62 tim=515108115242
WAIT #2547064615088: nam='PGA memory operation' ela= 3 p1=131072 p2=1 p3=0 obj#=62 tim=515108115328
WAIT #2547064615088: nam='PGA memory operation' ela= 2 p1=131072 p2=1 p3=0 obj#=62 tim=515108115361
WAIT #2547064615088: nam='PGA memory operation' ela= 2 p1=131072 p2=1 p3=0 obj#=62 tim=515108115391
WAIT #2547064615088: nam='PGA memory operation' ela= 1 p1=131072 p2=1 p3=0 obj#=62 tim=515108115421
WAIT #2547064615088: nam='PGA memory operation' ela= 2 p1=131072 p2=1 p3=0 obj#=62 tim=515108115455
WAIT #2547064615088: nam='PGA memory operation' ela= 2 p1=65536 p2=1 p3=0 obj#=62 tim=515108115526
WAIT #2547064615088: nam='PGA memory operation' ela= 2 p1=65536 p2=1 p3=0 obj#=62 tim=515108115592
WAIT #2547064615088: nam='PGA memory operation' ela= 3 p1=65536 p2=1 p3=0 obj#=62 tim=515108115634
WAIT #2547064615088: nam='PGA memory operation' ela= 2 p1=65536 p2=1 p3=0 obj#=62 tim=515108115707
CLOSE #2547065541576:c=7,e=7,dep=1,type=3,tim=515108115867
(2) txt:
********************************************************************************
SQL ID: 9tz4qu4rj9rdp Plan Hash: 4081256306
select count(*)
from
all_objects
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.08 0.07 0 9 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 239.07 247.92 0 97115 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 239.15 248.00 0 97124 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 108
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 SORT AGGREGATE (cr=7082171 pr=0 pw=0 time=748602301 us starts=1)
55672 55672 55672 FILTER (cr=7082171 pr=0 pw=0 time=688223289 us starts=1)
73471 73471 73471 HASH JOIN (cr=1223 pr=0 pw=0 time=390395 us starts=1 cost=363 size=10209550 card=73450)
129 129 129 INDEX FULL SCAN I_USER2 (cr=1 pr=0 pw=0 time=14 us starts=1 cost=1 size=512 card=128)(object id 47)
73471 73471 73471 HASH JOIN (cr=1222 pr=0 pw=0 time=292752 us starts=1 cost=362 size=9915750 card=73450)
129 129 129 INDEX FULL SCAN I_USER2 (cr=1 pr=0 pw=0 time=5 us starts=1 cost=1 size=3200 card=128)(object id 47)
73471 73471 73471 TABLE ACCESS FULL OBJ$ (cr=1221 pr=0 pw=0 time=163556 us starts=1 cost=360 size=8079500 card=73450)
0 0 0 NESTED LOOPS (cr=13310 pr=0 pw=0 time=60508 us starts=2913 cost=4 size=32 card=1)
0 0 0 NESTED LOOPS (cr=13310 pr=0 pw=0 time=57578 us starts=2913 cost=3 size=23 card=1)
2913 2913 2913 TABLE ACCESS BY INDEX ROWID IND$ (cr=5880 pr=0 pw=0 time=28212 us starts=2913 cost=2 size=10 card=1)
2913 2913 2913 INDEX UNIQUE SCAN I_IND1 (cr=2963 pr=0 pw=0 time=15521 us starts=2913 cost=1 size=0 card=1)(object id 41)
0 0 0 TABLE ACCESS CLUSTER TAB$ (cr=7430 pr=0 pw=0 time=25877 us starts=2913 cost=1 size=13 card=1)
0 0 0 INDEX RANGE SCAN I_OBJ1 (cr=0 pr=0 pw=0 time=0 us starts=0 cost=1 size=9 card=1)(object id 36)
2236 2236 2236 TABLE ACCESS CLUSTER TAB$ (cr=2730 pr=0 pw=0 time=27359 us starts=2236 cost=2 size=13 card=1)
2236 2236 2236 INDEX UNIQUE SCAN I_OBJ# (cr=178 pr=0 pw=0 time=6652 us starts=2236 cost=1 size=0 card=1)(object id 3)
316 316 316 TABLE ACCESS BY INDEX ROWID SEQ$ (cr=10 pr=0 pw=0 time=1773 us starts=316 cost=1 size=8 card=1)
316 316 316 INDEX UNIQUE SCAN I_SEQ1 (cr=4 pr=0 pw=0 time=862 us starts=316 cost=0 size=0 card=1)(object id 105)
2235 2235 2235 TABLE ACCESS BY INDEX ROWID IND$ (cr=560 pr=0 pw=0 time=14195 us starts=2913 cost=2 size=8 card=1)
2913 2913 2913 INDEX UNIQUE SCAN I_IND1 (cr=61 pr=0 pw=0 time=5876 us starts=2913 cost=1 size=0 card=1)(object id 41)
2141 2141 2141 NESTED LOOPS (cr=3538833 pr=0 pw=0 time=484925668 us starts=14105 cost=2 size=18 card=1)
61969 61969 61969 FIXED TABLE FULL X$KZSRO (cr=3523895 pr=0 pw=0 time=244986964 us starts=14105 cost=0 size=12 card=2)
2141 2141 2141 INDEX RANGE SCAN I_OBJAUTH1 (cr=14938 pr=0 pw=0 time=341186 us starts=61969 cost=1 size=12 card=1)(object id 62)
115 115 115 HASH JOIN (cr=197155 pr=0 pw=0 time=25226862 us starts=2202 cost=3 size=25 card=1)
2281 2281 2281 NESTED LOOPS (cr=6923 pr=0 pw=0 time=37685 us starts=2202 cost=3 size=19 card=1)
2202 2202 2202 TABLE ACCESS BY INDEX ROWID IND$ (cr=4458 pr=0 pw=0 time=25195 us starts=2202 cost=2 size=10 card=1)
2202 2202 2202 INDEX UNIQUE SCAN I_IND1 (cr=2252 pr=0 pw=0 time=13879 us starts=2202 cost=1 size=0 card=1)(object id 41)
2281 2281 2281 INDEX RANGE SCAN I_OBJAUTH1 (cr=2465 pr=0 pw=0 time=9214 us starts=2202 cost=1 size=9 card=1)(object id 62)
3345 3345 3345 FIXED TABLE FULL X$KZSRO (cr=190232 pr=0 pw=0 time=25999473 us starts=761 cost=0 size=12 card=2)
39105 39105 39105 NESTED LOOPS (cr=2511569 pr=0 pw=0 time=416984191 us starts=41982 cost=2 size=18 card=1)
53498 53498 53498 FIXED TABLE FULL X$KZSRO (cr=2468599 pr=0 pw=0 time=364506011 us starts=41982 cost=0 size=12 card=2)
39105 39105 39105 INDEX RANGE SCAN I_OBJAUTH1 (cr=42970 pr=0 pw=0 time=361854 us starts=53498 cost=1 size=12 card=1)(object id 62)
1882 1882 1882 NESTED LOOPS (cr=364607 pr=0 pw=0 time=51066604 us starts=2865 cost=2 size=18 card=1)
6797 6797 6797 FIXED TABLE FULL X$KZSRO (cr=361165 pr=0 pw=0 time=32363959 us starts=2865 cost=0 size=12 card=2)
1882 1882 1882 INDEX RANGE SCAN I_OBJAUTH1 (cr=3442 pr=0 pw=0 time=36614 us starts=6797 cost=1 size=12 card=1)(object id 62)
1 1 1 HASH JOIN (cr=57828 pr=0 pw=0 time=7033648 us starts=426 cost=2 size=28 card=1)
197 197 197 NESTED LOOPS (cr=1534 pr=0 pw=0 time=8339 us starts=426 cost=2 size=22 card=1)
425 425 425 TABLE ACCESS BY INDEX ROWID TABPART$ (cr=866 pr=0 pw=0 time=5067 us starts=426 cost=1 size=10 card=1)
425 425 425 INDEX UNIQUE SCAN I_TABPART_OBJ$ (cr=426 pr=0 pw=0 time=3102 us starts=426 cost=0 size=0 card=1)(object id 818)
197 197 197 INDEX RANGE SCAN I_OBJAUTH1 (cr=668 pr=0 pw=0 time=2626 us starts=425 cost=1 size=12 card=1)(object id 62)
981 981 981 FIXED TABLE FULL X$KZSRO (cr=56294 pr=0 pw=0 time=6673086 us starts=197 cost=0 size=12 card=2)
0 0 0 HASH JOIN (cr=428 pr=0 pw=0 time=20585 us starts=425 cost=2 size=28 card=1)
0 0 0 NESTED LOOPS (cr=428 pr=0 pw=0 time=2451 us starts=425 cost=2 size=22 card=1)
1 1 1 TABLE ACCESS BY INDEX ROWID TABCOMPART$ (cr=426 pr=0 pw=0 time=2097 us starts=425 cost=1 size=10 card=1)
1 1 1 INDEX UNIQUE SCAN I_TABCOMPART$ (cr=425 pr=0 pw=0 time=1724 us starts=425 cost=0 size=0 card=1)(object id 840)
0 0 0 INDEX RANGE SCAN I_OBJAUTH1 (cr=2 pr=0 pw=0 time=5 us starts=1 cost=1 size=12 card=1)(object id 62)
0 0 0 FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=0 us starts=0 cost=0 size=12 card=2)
0 0 0 NESTED LOOPS (cr=302560 pr=0 pw=0 time=37866572 us starts=1031 cost=8 size=82 card=1)
0 0 0 NESTED LOOPS (cr=302560 pr=0 pw=0 time=37865515 us starts=1031 cost=8 size=82 card=5)
0 0 0 NESTED LOOPS (cr=302560 pr=0 pw=0 time=37864792 us starts=1031 cost=5 size=72 card=1)
5155 5155 5155 NESTED LOOPS (cr=299786 pr=0 pw=0 time=18383225 us starts=1031 cost=4 size=60 card=1)
5155 5155 5155 MERGE JOIN CARTESIAN (cr=298754 pr=0 pw=0 time=18354065 us starts=1031 cost=3 size=56 card=1)
1031 1031 1031 INDEX RANGE SCAN I_OBJ5 (cr=2857 pr=0 pw=0 time=11908 us starts=1031 cost=3 size=50 card=1)(object id 40)
5155 5155 5155 BUFFER SORT (cr=295897 pr=0 pw=0 time=18335222 us starts=1031 cost=0 size=12 card=2)
5155 5155 5155 FIXED TABLE FULL X$KZSRO (cr=295897 pr=0 pw=0 time=18312335 us starts=1031 cost=0 size=12 card=2)
5155 5155 5155 INDEX RANGE SCAN I_USER2 (cr=1032 pr=0 pw=0 time=23043 us starts=5155 cost=1 size=4 card=1)(object id 47)
0 0 0 INDEX RANGE SCAN I_OBJAUTH1 (cr=2774 pr=0 pw=0 time=22061 us starts=5155 cost=1 size=12 card=1)(object id 62)
0 0 0 INDEX RANGE SCAN I_DEPENDENCY1 (cr=0 pr=0 pw=0 time=0 us starts=0 cost=2 size=0 card=5)(object id 85)
0 0 0 TABLE ACCESS BY INDEX ROWID DEPENDENCY$ (cr=0 pr=0 pw=0 time=0 us starts=0 cost=3 size=10 card=1)
0 0 0 NESTED LOOPS (cr=88448 pr=0 pw=0 time=11052957 us starts=287 cost=8 size=82 card=1)
0 0 0 NESTED LOOPS (cr=88448 pr=0 pw=0 time=11052624 us starts=287 cost=8 size=82 card=5)
0 0 0 NESTED LOOPS (cr=88448 pr=0 pw=0 time=11052382 us starts=287 cost=5 size=72 card=1)
1510 1510 1510 NESTED LOOPS (cr=87760 pr=0 pw=0 time=5334414 us starts=287 cost=4 size=60 card=1)
1510 1510 1510 MERGE JOIN CARTESIAN (cr=87472 pr=0 pw=0 time=5326089 us starts=287 cost=3 size=56 card=1)
302 302 302 INDEX RANGE SCAN I_OBJ5 (cr=798 pr=0 pw=0 time=3113 us starts=287 cost=3 size=50 card=1)(object id 40)
1510 1510 1510 BUFFER SORT (cr=86674 pr=0 pw=0 time=5361139 us starts=302 cost=0 size=12 card=2)
1510 1510 1510 FIXED TABLE FULL X$KZSRO (cr=86674 pr=0 pw=0 time=5342057 us starts=302 cost=0 size=12 card=2)
1510 1510 1510 INDEX RANGE SCAN I_USER2 (cr=288 pr=0 pw=0 time=6490 us starts=1510 cost=1 size=4 card=1)(object id 47)
0 0 0 INDEX RANGE SCAN I_OBJAUTH1 (cr=688 pr=0 pw=0 time=6228 us starts=1510 cost=1 size=12 card=1)(object id 62)
0 0 0 INDEX RANGE SCAN I_DEPENDENCY1 (cr=0 pr=0 pw=0 time=0 us starts=0 cost=2 size=0 card=5)(object id 85)
0 0 0 TABLE ACCESS BY INDEX ROWID DEPENDENCY$ (cr=0 pr=0 pw=0 time=0 us starts=0 cost=3 size=10 card=1)
0 0 0 HASH JOIN (cr=452 pr=0 pw=0 time=8100 us starts=138 cost=2 size=32 card=1)
0 0 0 NESTED LOOPS (cr=452 pr=0 pw=0 time=1773 us starts=138 cost=2 size=26 card=1)
122 122 122 TABLE ACCESS BY INDEX ROWID TRIGGER$ (cr=276 pr=0 pw=0 time=1114 us starts=138 cost=1 size=14 card=1)
138 138 138 INDEX UNIQUE SCAN I_TRIGGER2 (cr=138 pr=0 pw=0 time=527 us starts=138 cost=0 size=0 card=1)(object id 284)
0 0 0 INDEX RANGE SCAN I_OBJAUTH1 (cr=176 pr=0 pw=0 time=506 us starts=122 cost=1 size=12 card=1)(object id 62)
0 0 0 FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=0 us starts=0 cost=0 size=12 card=2)
0 0 0 HASH JOIN (cr=165 pr=0 pw=0 time=2489 us starts=32 cost=3 size=38 card=1)
0 0 0 NESTED LOOPS (cr=165 pr=0 pw=0 time=637 us starts=32 cost=3 size=32 card=1)
32 32 32 NESTED LOOPS (cr=128 pr=0 pw=0 time=496 us starts=32 cost=2 size=20 card=1)
32 32 32 TABLE ACCESS BY INDEX ROWID TABSUBPART$ (cr=64 pr=0 pw=0 time=293 us starts=32 cost=1 size=10 card=1)
32 32 32 INDEX UNIQUE SCAN I_TABSUBPART$_OBJ$ (cr=32 pr=0 pw=0 time=163 us starts=32 cost=0 size=0 card=1)(object id 830)
32 32 32 TABLE ACCESS BY INDEX ROWID TABCOMPART$ (cr=64 pr=0 pw=0 time=163 us starts=32 cost=1 size=10 card=1)
32 32 32 INDEX UNIQUE SCAN I_TABCOMPART$ (cr=32 pr=0 pw=0 time=81 us starts=32 cost=0 size=0 card=1)(object id 840)
0 0 0 INDEX RANGE SCAN I_OBJAUTH1 (cr=37 pr=0 pw=0 time=105 us starts=32 cost=1 size=12 card=1)(object id 62)
0 0 0 FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=0 us starts=0 cost=0 size=12 card=2)
43 43 43 VIEW (cr=0 pr=0 pw=0 time=138 us starts=43 cost=2 size=13 card=1)
43 43 43 FAST DUAL (cr=0 pr=0 pw=0 time=33 us starts=43 cost=2 size=0 card=1)
0 0 0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us starts=0 cost=2 size=18 card=1)
0 0 0 FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=0 us starts=0 cost=0 size=12 card=2)
0 0 0 INDEX RANGE SCAN I_OBJAUTH1 (cr=0 pr=0 pw=0 time=0 us starts=0 cost=1 size=12 card=1)(object id 62)
0 0 0 HASH JOIN (cr=0 pr=0 pw=0 time=0 us starts=0 cost=2 size=15 card=1)
0 0 0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us starts=0 cost=2 size=15 card=1)
0 0 0 STATISTICS COLLECTOR (cr=0 pr=0 pw=0 time=0 us starts=0)
0 0 0 FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=0 us starts=0 cost=0 size=12 card=2)
0 0 0 INDEX RANGE SCAN I_OBJAUTH2 (cr=0 pr=0 pw=0 time=0 us starts=0 cost=1 size=9 card=1)(object id 63)
0 0 0 INDEX RANGE SCAN I_OBJAUTH1 (cr=0 pr=0 pw=0 time=0 us starts=0 cost=1 size=9 card=1)(object id 62)
0 0 0 VIEW (cr=0 pr=0 pw=0 time=0 us starts=0 cost=10 size=16 card=1)
0 0 0 SORT GROUP BY (cr=0 pr=0 pw=0 time=0 us starts=0 cost=10 size=35 card=1)
0 0 0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us starts=0 cost=8 size=35 card=1)
0 0 0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us starts=0 cost=3 size=115 card=5)
0 0 0 INDEX UNIQUE SCAN I_OLAP_CUBES$ (cr=0 pr=0 pw=0 time=0 us starts=0 cost=0 size=13 card=1)(object id 1232)
0 0 0 TABLE ACCESS BY INDEX ROWID BATCHED DEPENDENCY$ (cr=0 pr=0 pw=0 time=0 us starts=0 cost=3 size=50 card=5)
0 0 0 INDEX RANGE SCAN I_DEPENDENCY1 (cr=0 pr=0 pw=0 time=0 us starts=0 cost=2 size=0 card=5)(object id 85)
0 0 0 INDEX RANGE SCAN I_OBJ1 (cr=0 pr=0 pw=0 time=0 us starts=0 cost=1 size=12 card=1)(object id 36)
0 0 0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us starts=0 cost=2 size=15 card=1)
0 0 0 FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=0 us starts=0 cost=0 size=12 card=2)
0 0 0 INDEX RANGE SCAN I_OBJAUTH2 (cr=0 pr=0 pw=0 time=0 us starts=0 cost=1 size=9 card=1)(object id 63)
0 0 0 TABLE ACCESS BY INDEX ROWID BATCHED USER_EDITIONING$ (cr=2 pr=0 pw=0 time=17 us starts=1 cost=2 size=6 card=1)
2 2 2 INDEX RANGE SCAN I_USER_EDITIONING (cr=1 pr=0 pw=0 time=8 us starts=1 cost=1 size=0 card=2)(object id 133)
0 0 0 TABLE ACCESS BY INDEX ROWID BATCHED USER_EDITIONING$ (cr=0 pr=0 pw=0 time=0 us starts=0 cost=2 size=6 card=1)
0 0 0 INDEX RANGE SCAN I_USER_EDITIONING (cr=0 pr=0 pw=0 time=0 us starts=0 cost=1 size=0 card=2)(object id 133)
0 0 0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us starts=0 cost=2 size=29 card=1)
0 0 0 INDEX SKIP SCAN I_USER2 (cr=0 pr=0 pw=0 time=0 us starts=0 cost=1 size=20 card=1)(object id 47)
0 0 0 INDEX RANGE SCAN I_OBJ4 (cr=0 pr=0 pw=0 time=0 us starts=0 cost=1 size=9 card=1)(object id 39)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
PGA memory operation 127 0.00 0.00
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 842.18 842.18
********************************************************************************
As you can see from above two files it seems like the most slowest step is at FETCH section (look at the value of metric of cpu, elapsed and query) and there appears two wait events "PGA memory operation" and "SQL*Net message from client".
So this time I go to adjust the parameter arraysize in SQL*Plus OR pga size.
(1) increasing arraysize in SQL*Plus:
18:17:56 C##QWZ@ora19c> show arraysize
arraysize 15
18:17:58 C##QWZ@ora19c>
18:17:59 C##QWZ@ora19c> set arraysize 600
18:18:14 C##QWZ@ora19c> show arraysize
arraysize 600
18:18:16 C##QWZ@ora19c>
18:18:17 C##QWZ@ora19c>
18:18:17 C##QWZ@ora19c>
18:18:17 C##QWZ@ora19c> select count(*) from all_objects; <<== 运行9分钟 才返回结果
COUNT(*)
----------
55672
18:27:23 C##QWZ@ora19c>
(2) increasing PGA size:
18:36:46 C##QWZ@ora19c> conn / as sysdba
Connected.
18:36:53 SYS@ora19c> show parameter pga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_limit big integer 2404M
pga_aggregate_target big integer 1202M
18:36:55 SYS@ora19c> alter system set pga_aggregate_target = 2048M;
System altered.
18:40:16 SYS@ora19c> alter system set pga_aggregate_limit = 4096M;
System altered.
18:40:47 SYS@ora19c> show parameter pga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_limit big integer 4G
pga_aggregate_target big integer 2G
18:41:28 C##QWZ@ora19c> select count(*) from all_objects;
COUNT(*)
----------
55672
18:50:40 C##QWZ@ora19c>
Overall, it takes a slight effect that chages from the initially spending 12 mins to 9 mins being improved. Could you have more good idea?
Thanks in advance!
Best Regards
Quanwen Zhao