asked on

dbms_lob.substr is causing perfomance issues, running out of 32GB of 'TEMP' table space in Oracle

I am trying to understand sql query written by my previous employee, we are using dbms_lob.substr oracle function to extract the data from 'nclob' data type, dbms_lob.substr is causing perfomance issues, running out of 32GB of 'TEMP' table space in Oracle, we have around 10 sql view's to pull data from 10 different tables. i join those views in my bigger sql for generating a report, so i noticed when i use join riskfactor views more then 5 views, i am running out of temp space i get ---> ORA-01652: unable to extend temp segment by 128 in tablespace TEMP, even though the nclob value is just single word, there might about 10k records like that, but still it comes under 10mb or less, our report data is always less then 10mb when we export to excel sheet in toad, not sure dbms_lob.substr function is using temp space in run time to pull the data or something else.
Please help me.

Table name --> qq.riskfactor

Column name      DataType
ID             NUMBER (10)
FIELD            NCLOB
VALUE            NCLOB

SQL query view riskfactorStructue
select
id,
TO_CHAR(dbms_lob.substr(qrf.field,32767,1)) Field,
TO_CHAR(dbms_lob.substr(qrf.value,32767,1)) value
from qq.riskfactor qrf
where dbms_lob.substr(qrf.field,32767,1) in ('Structure')

view1 ---> riskfactorStructure
view2 ---> riskfactorBusinesactivity
view3 ---> riskfactorpaycountry
view4 ---> riskfactortype
......... so on

i attached the screenshot of the view output to get the idea about the data

WAS

ASKER

Also what is the 32767,1 number means in this line --> TO_CHAR(dbms_lob.substr(qrf.value,32767,1)) value

Alex [***Alex140181***]

I doubt that this is the core problem, but you have to use TO_NCHAR for NCLOB!

The form of the VARCHAR2 buffer must match the form of the CLOB parameter. In other words, if the input LOB parameter is of type NCLOB, then the buffer must contain NCHAR data. Conversely, if the input LOB parameter is of type CLOB, then the buffer must contain CHAR data.

https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_LOB.html#GUID-9D283F23-287D-4227-8EA7-38338AF823B3

skullnobrains

this is an obvious misuse of views due to a poor table structure. one way or anther, the queries run against the views should hit an index. in this case, unless i am missing something they never do so you mostly read the whole table any time you query any of these views by field or value rather than id ( assuming id is indeed indexed )

Alex [***Alex140181***]

Also what is the 32767,1 number means in this line --> TO_CHAR(dbms_lob.substr(qrf.value,32767,1)) value According to the official docs, it means:

This function returns amount bytes or characters of a LOB, starting from an absolute offset from the beginning of the LOB.

https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_LOB.html#GUID-F0F5D13A-C86C-4BC2-8394-8CBA3344D5CE

Alex [***Alex140181***]

Btw, I think you shouldn't perform these kinds of operations against tables consisting of just CLOBS ;-)
Is this some kind of staging table?!

WAS

ASKER

Below is the output of the view

Alex i will try my views with to_nchar instead of to_char, will see if that improves the performance, this is physical table, application updates these tables directly, i created these views which will extract data from the tables for reporting purpose, i was surprised why these views are using 32GB of temp space where table's itself weigh less then 1mb, there is some leak, looking alternative ways to extract data from nclob data type. Thank you Alex for helping me.

skullnobrains

maybe turn those views into actual tables, add required indexes, and update them using triggers. you likely need to index values, or possibly use a clustered variable+values index.

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

slightwv (䄆 Netminder)

>>and update them using triggers.

If that is what is necessary (shouldn't be necessary), Materialized Views. Depending on the activity of the base table, make it Fast Refresh.

skullnobrains

my guess is the query comes with joins on the "value" fields which would produce a temporary table per join.

+1 for materialized views if they come with indexes and the volume of inserts is not too huge

johnsone

I doubt that this is the core problem, but you have to use TO_NCHAR for NCLOB!

I will throw it right back at you. Why is that the solution? What proof do you have that TO_NCHAR vs TO_CHAR is the reason for eating up TEMP? You keep telling us to prove our background, so right back at you.

Alex [***Alex140181***]

I pointed it out: "I doubt that this is the core problem,..." This implies, that I do NOT know the answer yet!
Furthermore, with the "you have to use TO_NCHAR for NCLOB", I refer to the official docs, nothing more.

Tomas Helgi Johannsson

Hi,

Execute explain on your query as slightwv mentions above. That will show you how your query is performing and most likely what part of it is causing the TEMP space to fill up.

Regards,
Tomas Helgi

skullnobrains

+1... and also post the query and relevant information regarding tables structures ( whatever you deem non obvious ). knowing how you query a table and how it is structured will help us provide actual answers rather than answering based on our respective ( and hopefully respected ) hunches.

Tomas Helgi Johannsson

From the above query you can calculate the amount of TEMP space it needs by the formula

Total number of KB used in TEMPSPACE = 32K * 3 * N

Open in new window

Where 32KB is the Number of bytes (for BLOBs) or characters (for CLOBs) to be read
3 is the number of dbms_lob.substr functions you use
and N is the number of rows the query reads through (you will see that number using Explain)

Regards,
Tomas Helgi