Home » RDBMS Server » Performance Tuning » java.sql.SQLTimeoutException: ORA-01013 (Oracle 12c)
java.sql.SQLTimeoutException: ORA-01013 [message #656865] Thu, 20 October 2016 21:02 Go to next message
ajamitjain129@gmail.com
Messages: 36
Registered: July 2014
Location: bangalore
Member
Hi,

I am executing one query whose working fine in QA, DEV environment but in production environment we are getting SQLTimeoutException exception.
we are using Spring Oracle 12c. I want to know how can i resolve this issue. table having below number of records.

select count(*) from Supplier;--175739515
select count(*) from C_EXTERNAL;--144
select count(*) from DC_ERR_MSG;--83

This is sample query.

SELECT Supplier.*, ec.error_cat_id
FROM Supplier LEFT JOIN DC_ERR_MSG dc
ON Supplier.ERR_MSG_ID = DC_ERR_MSG.ERR_MSG_ID
LEFT JOIN C_EXTERNAL ec
ON dc.error_cate = ec.error_cate;

Thanks,
Amit

Report message to a moderator

Re: java.sql.SQLTimeoutException: ORA-01013 [message #656866 is a reply to message #656865] Thu, 20 October 2016 21:32 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
Senior Member
I gather that you are looking for a way to speed up your query to avoid the SQLTimeoutException that causes the ORA-01013. For maximum efficiency of your query, you need to have indexes on the columns that are used in join conditions and any filter conditions and current statistics so that the optimizer can choose the best execution plan. You can use EXPLAIN PLAN to show what your optimizer is doing and post that here for further analysis.

Report message to a moderator

Re: java.sql.SQLTimeoutException: ORA-01013 [message #656867 is a reply to message #656866] Fri, 21 October 2016 00:34 Go to previous messageGo to next message
ajamitjain129@gmail.com
Messages: 36
Registered: July 2014
Location: bangalore
Member
thanks for reply!
please find complete query in the attachment.
we have couple of index on the table but not on that column which is have been used in the current query.

WITH sup AS
( SELECT distinct msg_id,msg_status_id,provider_id,EXTERNAL_SYSTEM_ID,updated,USGUSER_ID, ERR_MSG_ID
FROM Supplier_upd_log
WHERE  updated BETWEEN TO_DATE('2016-07-20', 'YYYY-MM-DD') AND TO_DATE('2016-07-21', 'YYYY-MM-DD')
) , sul AS
( SELECT sup.*, ec.error_category_id
FROM sup LEFT JOIN DC_ERR_MSG dc
ON dc.ERR_MSG_ID = sup.ERR_MSG_ID
LEFT JOIN GIO_OWNER.C_ERROR_CATEGORY ec
ON dc.error_category_id = ec.error_category_id
) ,
SD AS
(SELECT EXTERNAL_SYSTEM_NAME,
  COUNT(DISTINCT PROVIDER_ID) PROP_REQ,
  COUNT(PROVIDER_ID) TOT_REQ ,
  COUNT(DISTINCT (
  CASE
    WHEN MSG_STATUS_ID <> 1
    THEN PROVIDER_ID
  END)) PROP_ERR,
  COUNT((
  CASE
    WHEN MSG_STATUS_ID <> 1
    THEN PROVIDER_ID
  END)) ERR_REQ,
  COUNT((
  CASE
    WHEN error_category_id = 2
    THEN PROVIDER_ID
  END)) BUS_ERR_REQ,
  COUNT((
  CASE
    WHEN error_category_id = 1
    THEN PROVIDER_ID
  END)) SUP_ERR_REQ,
  COUNT((
  CASE
    WHEN error_category_id = 4
    THEN PROVIDER_ID
  END)) APP_ERR_REQ
FROM sul AD,
  C_EXTERNAL_SYSTEM ES
WHERE ES.EXTERNAL_SYSTEM_ID = AD.EXTERNAL_SYSTEM_ID
AND ES.EXTSYS_TYPE_ID       = 3
AND ES.EXTERNAL_SYSTEM_ID  <> 5
GROUP BY EXTERNAL_SYSTEM_NAME
)
SELECT EXTERNAL_SYSTEM_NAME       AS SUPPLIER_NAME,BUS_ERR_REQ,APP_ERR_REQ,SUP_ERR_REQ,
PROP_REQ                        AS PROPERTIES_REQUESTED,
TOT_REQ                         AS TOTAL_CALL_COUNTS,
ERR_REQ                         AS ERROR_COUNTS,
ROUND(ERR_REQ/TOT_REQ * 100, 2) AS ERROR_PERCENT,
PROP_ERR                        AS ERRD_PROPERTIES,
ROUND(BUS_ERR_REQ /TOT_REQ * 100, 2) AS BUSINESS_ERROR_PERCENT,
ROUND(SUP_ERR_REQ/TOT_REQ * 100, 2) AS SUPPLIER_ERROR_PERCENT,
ROUND(APP_ERR_REQ/TOT_REQ * 100, 2) AS APPLICATION_ERROR_PERCENT
FROM SD;


Insert into Supplier_upd_log (INDEX_OWNER,INDEX_NAME,UNIQUENESS,STATUS,INDEX_TYPE,TEMPORARY,PARTITIONED,FUNCIDX_STATUS,JOIN_INDEX,COLUMNS,COLUMN_EXPRESSION) values ('GIO_OWNER','FK_SUPPUL#EXTS#EXTS_ID','NONUNIQUE','N/A','NORMAL','N','YES',null,'NO','EXTERNAL_SYSTEM_ID, UPDATED',null);
Insert into Supplier_upd_log (INDEX_OWNER,INDEX_NAME,UNIQUENESS,STATUS,INDEX_TYPE,TEMPORARY,PARTITIONED,FUNCIDX_STATUS,JOIN_INDEX,COLUMNS,COLUMN_EXPRESSION) values ('GIO_OWNER','FK_SUPPUL#MSGST#MSGST_ID_S','NONUNIQUE','VALID','NORMAL','N','NO',null,'NO','MSG_STATUS_ID',null);
Insert into Supplier_upd_log (INDEX_OWNER,INDEX_NAME,UNIQUENESS,STATUS,INDEX_TYPE,TEMPORARY,PARTITIONED,FUNCIDX_STATUS,JOIN_INDEX,COLUMNS,COLUMN_EXPRESSION) values ('GIO_OWNER','FK_SUPPUL#EXTS#EXTS_ID_S','NONUNIQUE','VALID','NORMAL','N','NO',null,'NO','EXTERNAL_SYSTEM_ID',null);
Insert into Supplier_upd_log (INDEX_OWNER,INDEX_NAME,UNIQUENESS,STATUS,INDEX_TYPE,TEMPORARY,PARTITIONED,FUNCIDX_STATUS,JOIN_INDEX,COLUMNS,COLUMN_EXPRESSION) values ('GIO_OWNER','FK_SUPPUL#MSGST#MSGST_ID','NONUNIQUE','N/A','NORMAL','N','YES',null,'NO','MSG_STATUS_ID, UPDATED',null);
Insert into Supplier_upd_log (INDEX_OWNER,INDEX_NAME,UNIQUENESS,STATUS,INDEX_TYPE,TEMPORARY,PARTITIONED,FUNCIDX_STATUS,JOIN_INDEX,COLUMNS,COLUMN_EXPRESSION) values ('GIO_OWNER','PK_SUPPUL#SUPPUL_ID','UNIQUE','VALID','NORMAL','N','NO',null,'NO','SUPPLIER_UPD_LOG_ID',null);

[mod-edit: contents of attachment inserted into post by bb]
  • Attachment: Sql_Query.sql
    (Size: 3.12KB, Downloaded 2109 times)

[Updated on: Fri, 21 October 2016 02:02] by Moderator

Report message to a moderator

Re: java.sql.SQLTimeoutException: ORA-01013 [message #656870 is a reply to message #656867] Fri, 21 October 2016 00:59 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
ajamitjain129@gmail.com

we have couple of index on the table but not on that column which is have been used in the current query.
Perhaps you should have rather created indexes first, test your query and THEN post a reply.

Report message to a moderator

Re: java.sql.SQLTimeoutException: ORA-01013 [message #656879 is a reply to message #656867] Fri, 21 October 2016 02:35 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
Senior Member
Please run the following, then post the result.

set linesize 130
select * from user_ind_columns 
where  table_name in ('SUPPLIER_UPD_LOG', 'C_EXTERNAL', 'DC_ERR_MSG', 'C_ERROR_CATEGORY')
order  by index_name, column_position
/
explain plan for
WITH sup AS
( SELECT distinct msg_id,msg_status_id,provider_id,EXTERNAL_SYSTEM_ID,updated,USGUSER_ID, ERR_MSG_ID
FROM Supplier_upd_log
WHERE  updated BETWEEN TO_DATE('2016-07-20', 'YYYY-MM-DD') AND TO_DATE('2016-07-21', 'YYYY-MM-DD')
) , sul AS
( SELECT sup.*, ec.error_category_id
FROM sup LEFT JOIN DC_ERR_MSG dc
ON dc.ERR_MSG_ID = sup.ERR_MSG_ID
LEFT JOIN GIO_OWNER.C_ERROR_CATEGORY ec
ON dc.error_category_id = ec.error_category_id
) ,
SD AS
(SELECT EXTERNAL_SYSTEM_NAME,
  COUNT(DISTINCT PROVIDER_ID) PROP_REQ,
  COUNT(PROVIDER_ID) TOT_REQ ,
  COUNT(DISTINCT (
  CASE
    WHEN MSG_STATUS_ID <> 1
    THEN PROVIDER_ID
  END)) PROP_ERR,
  COUNT((
  CASE
    WHEN MSG_STATUS_ID <> 1
    THEN PROVIDER_ID
  END)) ERR_REQ,
  COUNT((
  CASE
    WHEN error_category_id = 2
    THEN PROVIDER_ID
  END)) BUS_ERR_REQ,
  COUNT((
  CASE
    WHEN error_category_id = 1
    THEN PROVIDER_ID
  END)) SUP_ERR_REQ,
  COUNT((
  CASE
    WHEN error_category_id = 4
    THEN PROVIDER_ID
  END)) APP_ERR_REQ
FROM sul AD,
  C_EXTERNAL_SYSTEM ES
WHERE ES.EXTERNAL_SYSTEM_ID = AD.EXTERNAL_SYSTEM_ID
AND ES.EXTSYS_TYPE_ID       = 3
AND ES.EXTERNAL_SYSTEM_ID  <> 5
GROUP BY EXTERNAL_SYSTEM_NAME
)
SELECT EXTERNAL_SYSTEM_NAME       AS SUPPLIER_NAME,BUS_ERR_REQ,APP_ERR_REQ,SUP_ERR_REQ,
PROP_REQ                        AS PROPERTIES_REQUESTED,
TOT_REQ                         AS TOTAL_CALL_COUNTS,
ERR_REQ                         AS ERROR_COUNTS,
ROUND(ERR_REQ/TOT_REQ * 100, 2) AS ERROR_PERCENT,
PROP_ERR                        AS ERRD_PROPERTIES,
ROUND(BUS_ERR_REQ /TOT_REQ * 100, 2) AS BUSINESS_ERROR_PERCENT,
ROUND(SUP_ERR_REQ/TOT_REQ * 100, 2) AS SUPPLIER_ERROR_PERCENT,
ROUND(APP_ERR_REQ/TOT_REQ * 100, 2) AS APPLICATION_ERROR_PERCENT
FROM SD
/
select plan_table_output from table (dbms_xplan.display())
/

Report message to a moderator

Re: java.sql.SQLTimeoutException: ORA-01013 [message #656881 is a reply to message #656879] Fri, 21 October 2016 04:33 Go to previous messageGo to next message
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
Senior Member
I'd push the check EXTERNAL_SYSTEM_ID <> 5 into the sup WITH clause, otherwise you'll be selecting data that just gets discarded later on.
Do the left joins in sul need to be left joins?

And if you're going to use ANSI syntax, be consistent and use it through out.

Report message to a moderator

Re: java.sql.SQLTimeoutException: ORA-01013 [message #657932 is a reply to message #656865] Mon, 28 November 2016 08:30 Go to previous messageGo to next message
ajamitjain129@gmail.com
Messages: 36
Registered: July 2014
Location: bangalore
Member
Hi All,

This issue got resolved. Sorry for late response.
Thanks for your support. Am having only 2 years experience. Am learning so many thing from this forum. Once again i want to thanks all of you.

Report message to a moderator

Re: java.sql.SQLTimeoutException: ORA-01013 [message #657939 is a reply to message #657932] Mon, 28 November 2016 10:12 Go to previous messageGo to next message
Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Quote:
This issue got resolved.
How?

Report message to a moderator

Re: java.sql.SQLTimeoutException: ORA-01013 [message #657948 is a reply to message #657939] Mon, 28 November 2016 17:24 Go to previous message
ajamitjain129@gmail.com
Messages: 36
Registered: July 2014
Location: bangalore
Member
Hi Michel,

We have created index for every where clause column and we have used force index for Supplier_upd_log table in the query.

Thanks,
Amit

Report message to a moderator

Previous Topic: Query Tuning
Next Topic: In-database archival
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sun Feb 02 17:49:17 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.