Home » RDBMS Server » Performance Tuning » Perfomance issue while extracting XML data
Perfomance issue while extracting XML data [message #460052] |
Wed, 09 June 2010 07:46 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
harshalonline22
Messages: 74 Registered: March 2007 Location: Pune
|
Member |
|
|
I have query as below,
SELECT e.job_cntl_id,
extractvalue(xmltype(replace(e.excptn_recrd, '&', '&')),'RECORD/@SECURTY_ID') AS SECURTY_ID,
extractvalue(xmltype(replace(e.excptn_recrd, '&', '&')),'RECORD/@SECURTY_TYPE_ID') AS SECURTY_TYPE_ID,
extractvalue(xmltype(replace(e.excptn_recrd, '&', '&')),'RECORD/@CREATE_DT') AS CREATE_DT,
extractvalue(xmltype(replace(e.excptn_recrd, '&', '&')),'RECORD/@CREATE_USR_ID') AS CREATE_USR_ID,
extractvalue(xmltype(replace(e.excptn_recrd, '&', '&')),'RECORD/@PRICE_DT') AS PRICE_DATE,
extractvalue(xmltype(replace(e.excptn_recrd, '&', '&')),'RECORD/@BEST_PRICE') AS BEST_PRICE,
extractvalue(xmltype(replace(e.excptn_recrd, '&', '&')),'RECORD/@ASK_PRICE') AS ASK_PRICE,
extractvalue(xmltype(replace(e.excptn_recrd, '&', '&')),'RECORD/@MID_PRICE') AS MID_PRICE,
extractvalue(xmltype(replace(e.excptn_recrd, '&', '&')),'RECORD/@BID_PRICE') AS BID_PRICE,
extractvalue(xmltype(replace(e.excptn_recrd, '&', '&')),'RECORD/@HIGH_PRICE') AS HIGH_PRICE,
extractvalue(xmltype(replace(e.excptn_recrd, '&', '&')),'RECORD/@LOW_PRICE') AS LOW_PRICE,
extractvalue(xmltype(replace(e.excptn_recrd, '&', '&')),'RECORD/@MANUAL_PRICE') AS MANUAL_PRICE,
extractvalue(xmltype(replace(e.excptn_recrd, '&', '&')),'RECORD/@LAST_PRICE') AS CLOSE_PRICE,
extractvalue(xmltype(replace(e.excptn_recrd, '&', '&')),'RECORD/@PRICE_SRC_NAME') AS PRICE_SRC_NAME,
e.excptn_detail
FROM (SELECT e.job_cntl_id,
e.excptn_id,
e.excptn_recrd,
wm_concat(ed.excptn_detail) as excptn_detail
FROM gps_audit.excptn e,
gps_audit.excptn_detl ed
WHERE e.job_cntl_id = v_jobid
AND e.as_of_dt = v_as_of_date
AND e.batch_id = ed.batch_id
AND e.excptn_id = ed.excptn_id
GROUP BY e.job_cntl_id, e.excptn_id, e.excptn_recrd) e
The inline view fetches records of 12000 in 10 sec. but when doing "extractvalue(xmltype" on that result set, it is taking about 7 mins.
Following is the table structure,
CREATE TABLE GPS_AUDIT.EXCPTN
(
EXCPTN_ID NUMBER(38) NOT NULL,
JOB_CNTL_ID NUMBER(38) NOT NULL,
EXCPTN_RECRD VARCHAR2(4000) NULL,
CREATE_DT DATE NOT NULL,
CREATE_USR_ID VARCHAR2(50) NOT NULL,
UPDT_DT DATE NOT NULL,
UPDT_USR_ID VARCHAR2(50) NOT NULL,
BATCH_ID NUMBER(38) NOT NULL,
AS_OF_DT DATE NULL
)
TABLESPACE GPS_AUDIT_DAT1
LOGGING
PCTFREE 10
PCTUSED 0
INITRANS 1
MAXTRANS 255
STORAGE(BUFFER_POOL DEFAULT)
NOPARALLEL
NOCACHE
/
CREATE UNIQUE INDEX GPS_AUDIT.EXCPTN_UNK1
ON GPS_AUDIT.EXCPTN(EXCPTN_ID)
TABLESPACE GPS_AUDIT_IDX1
LOGGING
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE(BUFFER_POOL DEFAULT)
NOPARALLEL
NOCOMPRESS
/
CREATE INDEX GPS_AUDIT.EXCPTN_IDX1
ON GPS_AUDIT.EXCPTN(JOB_CNTL_ID)
TABLESPACE GPS_AUDIT_IDX1
LOGGING
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE(BUFFER_POOL DEFAULT)
NOPARALLEL
NOCOMPRESS
/
ALTER TABLE GPS_AUDIT.EXCPTN
ADD CONSTRAINT EXCPTN_PK
PRIMARY KEY (EXCPTN_ID)
ENABLE
VALIDATE
/
CREATE TABLE GPS_AUDIT.EXCPTN_DETL
(
EXCPTN_DETL_ID NUMBER(38) NOT NULL,
EXCPTN_ID NUMBER(38) NOT NULL,
EXCPTN_XREF_ID NUMBER(38) NOT NULL,
EXCPTN_DETAIL VARCHAR2(4000) NULL,
CREATE_DT DATE NOT NULL,
CREATE_USR_ID VARCHAR2(50) NOT NULL,
UPDT_DT DATE NOT NULL,
UPDT_USR_ID VARCHAR2(50) NOT NULL,
BATCH_ID NUMBER(38) NOT NULL,
EXCPTN_TYPE_IND VARCHAR2(1) NULL
)
TABLESPACE GPS_AUDIT_DAT1
LOGGING
PCTFREE 10
PCTUSED 0
INITRANS 1
MAXTRANS 255
STORAGE(BUFFER_POOL DEFAULT)
NOPARALLEL
NOCACHE
/
CREATE UNIQUE INDEX GPS_AUDIT.EXCPTN_DETL_UNK1
ON GPS_AUDIT.EXCPTN_DETL(EXCPTN_DETL_ID)
TABLESPACE GPS_AUDIT_IDX1
LOGGING
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE(BUFFER_POOL DEFAULT)
NOPARALLEL
NOCOMPRESS
/
CREATE INDEX GPS_AUDIT.EXCPTN_DETL_IDX1
ON GPS_AUDIT.EXCPTN_DETL(EXCPTN_ID)
TABLESPACE GPS_AUDIT_IDX1
LOGGING
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE(BUFFER_POOL DEFAULT)
NOPARALLEL
NOCOMPRESS
/
CREATE INDEX GPS_AUDIT.EXCPTN_DETL_IDX2
ON GPS_AUDIT.EXCPTN_DETL(EXCPTN_XREF_ID)
TABLESPACE GPS_AUDIT_IDX1
LOGGING
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE(BUFFER_POOL DEFAULT)
NOPARALLEL
NOCOMPRESS
/
ALTER TABLE GPS_AUDIT.EXCPTN_DETL
ADD CONSTRAINT EXCPTN_DETL_PK
PRIMARY KEY (EXCPTN_DETL_ID)
ENABLE
VALIDATE
/
does the main query is taking time due to " extractvalue(xmltype(replace"??
I tried using the Hint also to use index, but there was no improvment in time.
I have attached plan.. (I was trying to get the plan by running sqlplus but it gave me error )
SQL> set autotrace on
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report
SQL>
-
Attachment: plan.JPG
(Size: 29.84KB, Downloaded 991 times)
|
|
|
|
|
Re: Perfomance issue while extracting XML data [message #460108 is a reply to message #460052] |
Wed, 09 June 2010 12:26 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
1) Are you sue that your query is taking 10 seconds to get all the records, and not just 10 seconds to get the first set of records?
2) Save yourself some processing time - move this code xmltype(replace(e.excptn_recrd, '&', '&')) into the inner select, so you're doing
SELECT e.job_cntl_id,
extractvalue(e.excptn_recrd,'RECORD/@SECURTY_ID') AS SECURTY_ID,
extractvalue(e.excptn_recrd,'RECORD/@SECURTY_TYPE_ID') AS SECURTY_TYPE_ID,
....
e.excptn_detail
FROM (SELECT e.job_cntl_id,
e.excptn_id,
xmltype(replace(e.excptn_recrd, '&', '&')) excptn_recrd,
wm_concat(ed.excptn_detail) as excptn_detail
FROM...
|
|
|
Re: Perfomance issue while extracting XML data [message #460176 is a reply to message #460108] |
Thu, 10 June 2010 01:28 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
harshalonline22
Messages: 74 Registered: March 2007 Location: Pune
|
Member |
|
|
1) Yes i am sure. It takes only 10 secs to return the result set.
2) I tried as suggest by you
SELECT e.job_cntl_id,
extractvalue(e.excptn_recrd,'RECORD/@SECURTY_ID') AS SECURTY_ID,
extractvalue(e.excptn_recrd,'RECORD/@SECURTY_TYPE_ID') AS SECURTY_TYPE_ID,
....
e.excptn_detail
FROM (SELECT e.job_cntl_id,
e.excptn_id,
xmltype(replace(e.excptn_recrd, '&', '&')) excptn_recrd,
wm_concat(ed.excptn_detail) as excptn_detail
FROM...
But still the qry is taking 7 mins to display the result.
I did following as part of solution :
1) Created GTT to insert inner query records
CREATE GLOBAL TEMPORARY TABLE XML_EXCPTN_RECORD
(
SESSION_ID VARCHAR2(100) NOT NULL
JOB_CNTL_ID NUMBER(38) NOT NULL,
EXCPTN_ID NUMBER(38) NOT NULL,
EXCPTN_RECRD XMLTYPE NULL,
EXCPTN_DETAIL VARCHAR2(4000) NULL
)
ON COMMIT PRESERVE ROWS
/
2) INSERT INTO gps.xml_excptn_record (this step took 1 min and 33 secs )
SELECT v_seesion_id,
e.job_cntl_id,
e.excptn_id,
xmltype(replace(e.excptn_recrd, '&', '&')) excptn_recrd,
wm_concat(ed.excptn_detail) as excptn_detail
FROM gps_audit.excptn e,
gps_audit.excptn_detl ed
WHERE e.job_cntl_id = v_job_id
AND e.as_of_dt = v_as_of_dt
AND e.batch_id = ed.batch_id
AND e.excptn_id = ed.excptn_id
GROUP BY e.job_cntl_id, e.excptn_id, e.excptn_recrd ;
3) Then select the records from GTT table. (this step took 2 min and 13 sec)
SELECT e.job_cntl_id,
extractvalue(e.excptn_recrd,'RECORD/@SECURTY_ID') AS SECURTY_ID,
extractvalue(e.excptn_recrd,'RECORD/@SECURTY_TYPE_ID') AS SECURTY_TYPE_ID,
extractvalue(e.excptn_recrd,'RECORD/@CREATE_DT') AS CREATE_DT,
extractvalue(e.excptn_recrd,'RECORD/@CREATE_USR_ID') AS CREATE_USR_ID,
extractvalue(e.excptn_recrd,'RECORD/@PRICE_DT') AS PRICE_DATE,
extractvalue(e.excptn_recrd,'RECORD/@BEST_PRICE') AS BEST_PRICE,
extractvalue(e.excptn_recrd,'RECORD/@ASK_PRICE') AS ASK_PRICE,
extractvalue(e.excptn_recrd,'RECORD/@MID_PRICE') AS MID_PRICE,
extractvalue(e.excptn_recrd,'RECORD/@BID_PRICE') AS BID_PRICE,
extractvalue(e.excptn_recrd,'RECORD/@HIGH_PRICE') AS HIGH_PRICE,
extractvalue(e.excptn_recrd,'RECORD/@LOW_PRICE') AS LOW_PRICE,
extractvalue(e.excptn_recrd,'RECORD/@MANUAL_PRICE') AS MANUAL_PRICE,
extractvalue(e.excptn_recrd,'RECORD/@LAST_PRICE') AS CLOSE_PRICE,
extractvalue(e.excptn_recrd,'RECORD/@PRICE_SRC_NAME') AS PRICE_SRC_NAME,
e.excptn_detail
FROM gps.xml_excptn_record e
WHERE SESSION_ID = v_seesion_id;
But still its not major time improvment.
Are there any factors at DBA level, which can be bottleneck for slow performance?
|
|
|
Re: Perfomance issue while extracting XML data [message #460229 is a reply to message #460176] |
Thu, 10 June 2010 04:43 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
1) Yes i am sure. It takes only 10 secs to return the result set.
How do you know this? What are you doing to confirm this?
If populating the GTT with that query takes 1 minute 33 seconds, then you're going to find it very hard to convince me that the query itself runs in 10 seconds.
I can't think of many interfaces that will display 1200 rows per second, and if you're counting the query as finished when it's displayed the first set of rows then you're using an inaccurate timing.
Other than that, there's not a lot I can think of. The XML functions can be quite slow. Given that you're just fetching single values, you might well be better off using string manipulation functions.
How big are the XML fragments that you're manipulating in this code?
|
|
|
Re: Perfomance issue while extracting XML data [message #460812 is a reply to message #460229] |
Tue, 15 June 2010 00:31 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
harshalonline22
Messages: 74 Registered: March 2007 Location: Pune
|
Member |
|
|
Quote:
How do you know this? What are you doing to confirm this?
I am using Rapid sql tool for executing the inner Select where i can see the time taken by query.
In case of populating GTT also i saw the time in the same tool as i execute everything stepwise.
Give me some time I will surely post the snapshot for similar case as the data in table is cleaned.
2) How big are the XML fragments that you're manipulating in this code?
<RECORD SOI_ID = "16421491" CPP_SECURTY_ALIAS_ID = "104682" CORRELTN_ID = "" PRICE_DT = "06/11/2010" PRICE_SUBSCRBR_ID = "" PRICE_RULE_SET_ID = "5" SECURTY_ID = "320085608" SECURTY_TYPE_ID = "10" SECURTY_NAME = "" SECURTY_OVERRIDE_RULE_SET_ID = "74" GPS_MAC3_ID = "CREDIT SECURITIES" GPS_MAC5_ID = "CORPORATE BOND" CRRNCY_ID = "7428" GPS_CNTRY_ID = "7624" GPS_EXCHNG_ID = "-1"/>
Best Regards,
Harshal
|
|
|
Re: Perfomance issue while extracting XML data [message #460882 is a reply to message #460812] |
Tue, 15 June 2010 06:27 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Try running this query in the Rapid Sql too, and tell me how long it says it takes to run:select level from dual connect by level <=1000000000; I suspect it will say that it takes a bit less than 0.1 seconds to run
The only performance improvement I can think of would be to stop using the XML functions, and do the whole thing with Substr and Instr.
|
|
|
|
Goto Forum:
Current Time: Thu Sep 05 21:41:56 CDT 2024
|