Home » SQL & PL/SQL » SQL & PL/SQL » SQL Data Extraction for CLOB (Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production)
SQL Data Extraction for CLOB [message #675165] Wed, 13 March 2019 02:02 Go to next message
rahul1982
Messages: 51
Registered: November 2011
Location: Pune
Member
Hello,

I'm executing select statement on below table that has 4 CLOB column. SQL takes time to pull all the records, there are around 50 K records. I tried adding parallel hint but it has not improved the extraction.

Below is the explain plan. Please suggest how SQL extraction can be improved.

EXPLAIN PLAN FOR 
SELECT  ACCOUNT_ID,
ACCOUNT_RECORD_TYPE,
STG_ELM_CASE.DESCRIPTION,--CLOB
ACTIONS_TAKEN_COMMENT,--CLOB
ADDITIONAL_NOTES,--CLOB
BASE_MODULES--CLOB
ENGAGED_SME_CSM
FROM
case_desc;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT                                                                                                                                                                                                                                                                                           
-------------------------------------------------------------------------------------
Plan hash value: 1938165839
 
----------------------------------------------------------------------------------
| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |              | 58828 |    92M|  4407   (1)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| STG_ELM_CASE | 58828 |    92M|  4407   (1)| 00:00:01 |
----------------------------------------------------------------------------------

8 rows selected.

Thanks,
Rahul
SQL Data Extraction for CLOB [message #675166 is a reply to message #675165] Wed, 13 March 2019 02:02 Go to previous messageGo to next message
rahul1982
Messages: 51
Registered: November 2011
Location: Pune
Member
Hello,

I'm executing select statement on below table that has 4 CLOB column. SQL takes time to pull all the records, there are around 50 K records. I tried adding parallel hint but it has not improved the extraction.

Below is the explain plan. Please suggest how SQL extraction can be improved.

EXPLAIN PLAN FOR 
SELECT  ACCOUNT_ID,
ACCOUNT_RECORD_TYPE,
STG_ELM_CASE.DESCRIPTION,--CLOB
ACTIONS_TAKEN_COMMENT,--CLOB
ADDITIONAL_NOTES,--CLOB
BASE_MODULES--CLOB
ENGAGED_SME_CSM
FROM
case_desc;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT                                                                                                                                                                                                                                                                                           
-------------------------------------------------------------------------------------
Plan hash value: 1938165839
 
----------------------------------------------------------------------------------
| Id  | Operation         | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |              | 58828 |    92M|  4407   (1)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| STG_ELM_CASE | 58828 |    92M|  4407   (1)| 00:00:01 |
----------------------------------------------------------------------------------

8 rows selected.

Thanks,
Rahul
Re: SQL Data Extraction for CLOB [message #675167 is a reply to message #675165] Wed, 13 March 2019 02:12 Go to previous messageGo to next message
John Watson
Messages: 7811
Registered: January 2010
Location: Global Village
Senior Member
I'm surprised parallel query did not run faster. How did you enable it? How long does it take?

(by the way, I wish you would not say "record" when you mean "row").
Re: SQL Data Extraction for CLOB [message #675168 is a reply to message #675167] Wed, 13 March 2019 02:21 Go to previous messageGo to next message
rahul1982
Messages: 51
Registered: November 2011
Location: Pune
Member
I have added parallel hint as below -

SELECT /*+ PARALLEL(4) */ ACCOUNT_ID,
ACCOUNT_RECORD_TYPE,
STG_ELM_CASE.DESCRIPTION,--CLOB
ACTIONS_TAKEN_COMMENT,--CLOB
ADDITIONAL_NOTES,--CLOB
BASE_MODULES--CLOB
ENGAGED_SME_CSM
FROM
case_desc;

SQL took around 50 mins to pull 16K rows.
Re: SQL Data Extraction for CLOB [message #675169 is a reply to message #675168] Wed, 13 March 2019 02:24 Go to previous messageGo to next message
John Watson
Messages: 7811
Registered: January 2010
Location: Global Village
Senior Member
Well, go on: what was the parallel execution plan? How long does it take to run serial? And how many rows are there? 16k or 50k?
Re: SQL Data Extraction for CLOB [message #675170 is a reply to message #675169] Wed, 13 March 2019 02:29 Go to previous messageGo to next message
John Watson
Messages: 7811
Registered: January 2010
Location: Global Village
Senior Member
Incidentally, what is the connection between CASE_DESC and STG_ELM_CASE ?
Re: SQL Data Extraction for CLOB [message #675171 is a reply to message #675169] Wed, 13 March 2019 02:30 Go to previous messageGo to next message
rahul1982
Messages: 51
Registered: November 2011
Location: Pune
Member
Below is the SQL plan with parallel hint

PLAN_TABLE_OUTPUT                                                                                                                                                                                                                                                                                           
----------------------------------------------------------------------------------------------------------------------
Plan hash value: 3830272449
 
---------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name      | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |           | 58810 |    92M|  1212   (0)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR      |           |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)| :TQ10000  | 58810 |    92M|  1212   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX BLOCK ITERATOR |           | 58810 |    92M|  1212   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   4 |     TABLE ACCESS FULL| CASE_DESC | 58810 |    92M|  1212   (0)| 00:00:01 |  Q1,00 | PCWP |            |
---------------------------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT                                                                                                                                                                                                                                                                                           
-----------------------------------------------------------------------------------------------------------------------
 
Note
-----
   - Degree of Parallelism is 4 because of hint

15 rows selected. 

There are 50k rows, select statement is running from last 50 mins and so far it has pulled 16k rows.

What is mean by - How long does it take to run serial?
Re: SQL Data Extraction for CLOB [message #675172 is a reply to message #675171] Wed, 13 March 2019 02:32 Go to previous messageGo to next message
John Watson
Messages: 7811
Registered: January 2010
Location: Global Village
Senior Member
Quote:
What is mean by - How long does it take to run serial?
"serial" means "not parallel".
Re: SQL Data Extraction for CLOB [message #675173 is a reply to message #675170] Wed, 13 March 2019 02:33 Go to previous messageGo to next message
rahul1982
Messages: 51
Registered: November 2011
Location: Pune
Member
Sorry, my mistake. Forgot to remove STG_ELM_CASE.

Correct select statement is -

SELECT /*+ PARALLEL(4) */ ACCOUNT_ID,
ACCOUNT_RECORD_TYPE,
DESCRIPTION,--CLOB
ACTIONS_TAKEN_COMMENT,--CLOB
ADDITIONAL_NOTES,--CLOB
BASE_MODULES--CLOB
ENGAGED_SME_CSM
FROM
case_desc;
Re: SQL Data Extraction for CLOB [message #675174 is a reply to message #675173] Wed, 13 March 2019 02:36 Go to previous messageGo to next message
John Watson
Messages: 7811
Registered: January 2010
Location: Global Village
Senior Member
You had better start again, this time using the correct table, and stating how long the queries take and how many rows are returned.
Re: SQL Data Extraction for CLOB [message #675175 is a reply to message #675174] Wed, 13 March 2019 02:42 Go to previous message
rahul1982
Messages: 51
Registered: November 2011
Location: Pune
Member
okay! I'll do that.
Previous Topic: how can I allow this procedure to attach to email(pdf file in the directory), pdf file that is > 32
Next Topic: ORA-29273: HTTP request failed ORA-06512: at "SYS.UTL_HTTP", line 1720 ORA-29024: Certificate valida
Goto Forum:
  


Current Time: Wed Mar 20 06:18:32 CDT 2019