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
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 1012 times)

Report message to a moderator

Re: Perfomance issue while extracting XML data [message #460053 is a reply to message #460052] Wed, 09 June 2010 07:47 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
(I was trying to get the plan by running sqlplus but it gave me error )

Ask your DBA to install and grant you PLUSTRACE role.

Regards
Michel

Report message to a moderator

Re: Perfomance issue while extracting XML data [message #460056 is a reply to message #460053] Wed, 09 June 2010 07:52 Go to previous messageGo to next message
harshalonline22
Messages: 74
Registered: March 2007
Location: Pune
Member
surely i will get it done,but please suggest on query issue.

Report message to a moderator

Re: Perfomance issue while extracting XML data [message #460108 is a reply to message #460052] Wed, 09 June 2010 12:26 Go to previous messageGo to next message
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...

Report message to a moderator

Re: Perfomance issue while extracting XML data [message #460176 is a reply to message #460108] Thu, 10 June 2010 01:28 Go to previous messageGo to next message
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?

Report message to a moderator

Re: Perfomance issue while extracting XML data [message #460229 is a reply to message #460176] Thu, 10 June 2010 04:43 Go to previous messageGo to next message
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?

Report message to a moderator

Re: Perfomance issue while extracting XML data [message #460812 is a reply to message #460229] Tue, 15 June 2010 00:31 Go to previous messageGo to next message
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

Report message to a moderator

Re: Perfomance issue while extracting XML data [message #460882 is a reply to message #460812] Tue, 15 June 2010 06:27 Go to previous messageGo to next message
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.

Report message to a moderator

Re: Perfomance issue while extracting XML data [message #473240 is a reply to message #460882] Thu, 26 August 2010 16:39 Go to previous message
harshalonline22
Messages: 74
Registered: March 2007
Location: Pune
Member
I ran this query 

select level from dual connect by level <=1000000;


it took 1.04 min to complete execution.

Report message to a moderator

Previous Topic: Get No of rows Processed while Update statment is still running
Next Topic: AWR and ADDM
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon Nov 25 16:05:34 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.