I'm on oracle 10.2.0.4 , 4 node rac on Redhat Linux. Storage is an HP EVA with no performance issues I'm aware of.
When issueing the select part of the sql below the data returns within 2 secs.
When inserting the results of this query into an empty tables as show below the insert takes over 20 minutes.
Can anyone help me understand where this massive diffrernce occurs - sql trace shows its all CPU but the differnece seems excessive for the ammount of data written (17'000 rows, although rows can be large as have 2 cols of varchar2(4000 byte).). Obviously you would expect writing the data to be more time consuming but seems excessive. Just want to understand/comnfirm whather there is an acutal issue or simply we need to tune the insert (with parallel hints etc)
insert into TAB_TEST
(NUMBER ,
APPLICATIONNUMBER,
APPLICATION_SCOPE_CODE,
APPLICATION_SCOPE_TEXT)
(SELECT number, applicationnumber,
RTRIM(XMLAGG (XMLELEMENT (e, TRIM (ig_key) || ', ')).EXTRACT ('//text()'), ', ') AS application_scope_code,
RTRIM(XMLAGG (XMLELEMENT (e, TRIM (scopetype) || ', ')).EXTRACT ('//text()'), ', ') AS application_scope_text
FROM
(SELECT DISTINCT
s.number,
s.applicationnumber,
s.item_type,
s.ig_key,
scopetype
FROM v_tmp_varscope_h s, siamed_human_user.tbllkpscopetypes st
WHERE s.ig_key = st.ig_key (+))
GROUP BY number, applicationnumber);
From sql trace
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.04 0 96 0 0
Execute 1 1123.69 1118.47 472 939 21004 17842
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 1123.72 1118.52 472 1035 21004 17842
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 1715
Rows Row Source Operation
------- ---------------------------------------------------
17842 SORT GROUP BY (cr=535 pr=472 pw=472 time=760476724 us)
18915 VIEW (cr=535 pr=0 pw=0 time=147244 us)
18915 SORT UNIQUE (cr=535 pr=0 pw=0 time=128327 us)
19011 HASH JOIN RIGHT OUTER (cr=535 pr=0 pw=0 time=101068 us)
542 VIEW index$_join$_006 (cr=31 pr=0 pw=0 time=9845 us)
542 HASH JOIN (cr=31 pr=0 pw=0 time=9842 us)
542 INDEX RANGE SCAN IDX_SICTG_TPO_KEY (cr=4 pr=0 pw=0 time=26 us)(object id 480122)
542 INDEX FAST FULL SCAN IDX_SICTG_TPO_NAME (cr=27 pr=0 pw=0 time=6994 us)(object id 480125)
19011 HASH JOIN RIGHT OUTER (cr=504 pr=0 pw=0 time=50752 us)
19190 INDEX FAST FULL SCAN CK_TBLAPPLICATIONS_AK (cr=101 pr=0 pw=0 time=23 us)(object id 488055)
18968 TABLE ACCESS FULL T_VARIATION_SCOPE (cr=403 pr=0 pw=0 time=49 us)
********************************************************************************