No question at this time
DBA Top 10
1 M. Cadot 13000
2 B. Vroman 11900
3 A. Kavsek 11200
4 J. Schnackenberg 5600
4 M. Hidayathullah ... 5600
6 T. Boles 5400
7 P. Wisse 3200
8 T. P 700
9 A. Rai 600
10 R. Wauben 500
10 H. Steijntjes 500
About
DBA-Village
Download PLATO
The free tool for auditing and tuning your database
Version 55 now available
Sep 02, 2016
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48147
Total active users1697
Act. users last 24h6
Act. users last hour1
Registered user hits last week261
Registered user hits last month1385
Go up

performance issues
Next thread: 11g Agent De-install
Prev thread: Migration of apps and database to cloud.

Message Score Author Date
Hi Guys I have a table that have 318 rows, my d...... Tso P Feb 02, 2018, 09:16
Hi Tso, I'd say you are missing something (migh...... Score: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 Pts Jan Schnackenberg Feb 02, 2018, 10:03
Thanks Jan Below is the desc of the table... ...... Tso P Feb 02, 2018, 10:46
Hi Tso, well, that was a guess, or more a "ment...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Jan Schnackenberg Feb 02, 2018, 13:05
Thanks Jan SELECT dl.owner, dl.table_na...... Tso P Feb 02, 2018, 13:50
Hi Tso First: Whatever you executed, it was not...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Jan Schnackenberg Feb 02, 2018, 14:27
Thanks for the help Jan <pre> COLUMN_NAME ...... Tso P Feb 02, 2018, 14:37
</pre> <pre> TRNCUSSTR008 NV...... Tso P Feb 02, 2018, 14:39
<pre> SYS_NC00146$ CLOB ...... Tso P Feb 02, 2018, 14:40
<pre> TRANCODE NVARCHAR2 ...... Tso P Feb 02, 2018, 14:42
<pre> AGENCYID NVARCHAR2 ...... Tso P Feb 02, 2018, 14:42
<pre> TRNAPPINT004 NUMBER ...... Tso P Feb 02, 2018, 14:42
I had post the results in batches, NB I had an ...... Tso P Feb 02, 2018, 14:50
My apologies with the first query results, I did m...... Tso P Feb 02, 2018, 14:56
Ok, obviously, yes, the LOB segment for column "SY...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Jan Schnackenberg Feb 02, 2018, 17:11
Thanks Jan Below please find the results: <p...... Tso P Feb 05, 2018, 09:01
What should I do with this LOB that is giving issu...... Tso P Feb 06, 2018, 07:20
You (no, your application developers) should take ...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Jan Schnackenberg Feb 08, 2018, 08:45
Thanks Jan Much appreciated !!!... Tso P Feb 13, 2018, 16:20

Follow up by mail Click here


Subject: performance issues
Author: Tso P, South Africa
Date: Feb 02, 2018, 09:16, 19 days ago
Os info: solaris 10
Oracle info: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
Message: Hi Guys

I have a table that have 318 rows, my developers are telling that there are performance issues on the table...they are saying it takes 37 seconds to get the results.

I did the trace on the sql just to see and below is the results.


SQL> SET AUTOTRACE TRACEONLY
SQL> select * from dmkr_asline.trns

318 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 420793294

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 317 | 251K| 13 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| TRNS | 317 | 251K| 13 (0)| 00:00:01 |
--------------------------------------------------------------------------

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
239906 consistent gets
29849 physical reads
0 redo size
295484359 bytes sent via SQL*Net to client
61575063 bytes received via SQL*Net from client
239897 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
318 rows processed

SQL> exit


To me this looks fine, or am I missing something.

I would appreciate experts opinion.

Thanks in advance...

Please help...

Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: performance issues
Author: Jan Schnackenberg, Germany
Date: Feb 02, 2018, 10:03, 19 days ago
Score:   Score: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 Pts
Message: Hi Tso,

I'd say you are missing something (might be me, though)

You say, you have a table with 318 Rows. The Plan tells you that you'll fetch 317 rows with a 251KB data.

The statistics block tells me something completely different, though:


...
295484359 bytes sent via SQL*Net to client
61575063 bytes received via SQL*Net from client
239897 SQL*Net roundtrips to/from client
...
318 rows processed


The database sends 295MEGABYTE to the client and processes 318 lines? Is this a table with LOBs? Did someone put a broken (extremely inflated) LOB into the last line (which didn't get analyzed yet, resulting in the one-off number of rows)?

Regards,
Jan
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: performance issues
Author: Tso P, South Africa
Date: Feb 02, 2018, 10:46, 19 days ago
Message: Thanks Jan

Below is the desc of the table...

Name Null Type
------------- -------- ---------------
JOB_ID NOT NULL NUMBER(38)
TRN_ID NOT NULL NUMBER(38)
UNIQUE_ID NOT NULL NVARCHAR2(47)
INUSE NVARCHAR2(5)
CUSTOMIZED NUMBER(5)
CREATETIME TIMESTAMP(6)
BEGINTIME TIMESTAMP(6)
TRNSTARTTIME TIMESTAMP(6)
MODIFYTIME TIMESTAMP(6)
TRNMODIFYTIME TIMESTAMP(6)
ARCTIME TIMESTAMP(6)
RETENTION TIMESTAMP(6)
TRNENDTIME TIMESTAMP(6)
ENDTIME TIMESTAMP(6)
APPDATA RAW(2000 BYTE)
JURISDICTN NVARCHAR2(30)
LOCID NVARCHAR2(30)
QUEUEID NVARCHAR2(30)
SUBLOCID NVARCHAR2(30)
AGENCYID NVARCHAR2(30)
FROMUSER NVARCHAR2(64)
FROMTIME TIMESTAMP(6)
FROMGROUP NVARCHAR2(64)
TOTIME TIMESTAMP(6)
TOUSER NVARCHAR2(64)
TOGROUP NVARCHAR2(64)
ROUTEDESC NVARCHAR2(256)
PROCESSNAME NVARCHAR2(256)
TRNAPPDATE001 TIMESTAMP(6)
TRNAPPDATE002 TIMESTAMP(6)
TRNAPPDATE003 TIMESTAMP(6)
TRNAPPDATE004 TIMESTAMP(6)
TRNAPPDATE005 TIMESTAMP(6)
TRNAPPDEC001 NUMBER(19,4)
TRNAPPDEC002 NUMBER(19,4)
TRNAPPDEC003 NUMBER(19,4)
TRNCUSINT005 NUMBER(20)
TRNCUSSTR001 NVARCHAR2(256)
TRNCUSSTR020 NVARCHAR2(256)
ACTION NUMBER(20)
REASON_ID NVARCHAR2(6)
TRNSTATUS NUMBER(10)
TRNRCPTOTAL NUMBER(38)
TRNDATATYPE NUMBER(10)
TRNDATAREF NVARCHAR2(512)
TRNDATAXML SYS.XMLTYPE
TRNDATABLOB BLOB
TRNDATASIZE NUMBER(20)
TRNNAPOLTYPE NUMBER(10)
TRNNAPOLREF NVARCHAR2(512)
TRNNAPOLXML SYS.XMLTYPE
TRNNAPOLBLOB BLOB
TRNNAPOLSIZE NUMBER(20)
TRNPRTLOGNAME NVARCHAR2(256)
TRNDOLOG NUMBER(5)
TRNRETHOLD NUMBER(5)
TRNHISTORY NUMBER(5)
TRNHISTORICAL NUMBER(5)
SRCTRN_ID NUMBER(38)

How do I see the row that is broken (or extremely inflated) LOB which is not yet analyzed ?

Thanks
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: performance issues
Author: Jan Schnackenberg, Germany
Date: Feb 02, 2018, 13:05, 19 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message: Hi Tso,

well, that was a guess, or more a "mentioning a possibility".

Since there are LOBs in the table the possibility stills stands, and you could always gather statistics for the table to see what changes.

Also, to see the total size of the LOB segments for the table, try (typed on the fly, I didn't test the statement):


SELECT dl.owner,
dl.table_name,
dl.column_name,
dl.segment_name,
dl.tablespace_name,
ds.bytes
FROM dba_lobs dl
INNER JOIN dba_segments ds
ON dl.owner = ds.owner
AND dl.segment_name = ds.segment_name
WHERE owner = 'DMKR_ASLINE'
AND table_name = 'TRNS'


If this should show you the size of the lob-segments associated with the LOB-columns of the table.

Regards,
Jan
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: performance issues
Author: Tso P, South Africa
Date: Feb 02, 2018, 13:50, 19 days ago
Message: Thanks Jan

SELECT dl.owner,
dl.table_name,
dl.column_name,
dl.segment_name,
dl.tablespace_name,
ds.bytes
FROM dba_lobs dl
INNER JOIN dba_segments ds
ON dl.owner = ds.owner
AND dl.segment_name = ds.segment_name
WHERE dl.owner = 'DMKR_ASLINE'
AND dl.table_name = 'TRNS';

Below is the results of the query:

  

DMKR_ASLINE TRNS TRNNAPOLBLOB SYS_LOB0000091439C00147$$ DMKR_ASLINE 0.125
DMKR_ASLINE TRNS SYS_NC00146$ SYS_LOB0000091439C00146$$ DMKR_ASLINE 256.1875
DMKR_ASLINE TRNS TRNDATABLOB SYS_LOB0000091439C00141$$ DMKR_ASLINE 0.125
DMKR_ASLINE TRNS SYS_NC00140$ SYS_LOB0000091439C00140$$ DMKR_ASLINE 5.125


Does the SYS_LOB0000091439C00146$$ be the one that might be giving us the issues?

Thanks
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: performance issues
Author: Jan Schnackenberg, Germany
Date: Feb 02, 2018, 14:27, 19 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message: Hi Tso

First: Whatever you executed, it was not the statement posted. I am 110% sure, that you do not have a segment with 0.125 bytes (one bit) as size.

Then: is there a function based index on this table?

Can you please show the output of


SELECT column_name,
data_type,
num_distinct,
density,
histogram,
hidden_column,
virtual_column
FROM dba_tab_cols
WHERE owner = 'DMKR_ASLINE'
AND table_name = 'TRNS';
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: performance issues
Author: Tso P, South Africa
Date: Feb 02, 2018, 14:37, 19 days ago
Message: Thanks for the help Jan


COLUMN_NAME DATA_TYPE NUM_DISTINCT DENSITY HISTOGRAM HID VIR
------------------------------ ----------------------------------- ------------ ---------- --------------- --- ---
SYS_NC00155$ RAW 3 .001577287 FREQUENCY YES YES
SYS_NC00156$ RAW 317 .003154574 NONE YES YES
TRNCUSDATE005 TIMESTAMP(6) 0 0 NONE NO NO
TRNCUSDATE006 TIMESTAMP(6) 0 0 NONE NO NO
TRNCUSDATE007 TIMESTAMP(6) 0 0 NONE NO NO
TRNCUSDATE008 TIMESTAMP(6) 0 0 NONE NO NO
TRNCUSDATE009 TIMESTAMP(6) 0 0 NONE NO NO
TRNCUSDATE010 TIMESTAMP(6) 0 0 NONE NO NO
TRNCUSDATE011 TIMESTAMP(6) 0 0 NONE NO NO
TRNCUSDATE012 TIMESTAMP(6) 0 0 NONE NO NO
TRNCUSDATE013 TIMESTAMP(6) 0 0 NONE NO NO
TRNCUSDATE014 TIMESTAMP(6) 0 0 NONE NO NO
TRNCUSDATE015 TIMESTAMP(6) 0 0 NONE NO NO
TRNCUSDEC001 NUMBER 0 0 NONE NO NO
TRNCUSDEC002 NUMBER 0 0 NONE NO NO
TRNCUSDEC003 NUMBER 0 0 NONE NO NO
TRNCUSDEC004 NUMBER 0 0 NONE NO NO
TRNCUSDEC005 NUMBER 0 0 NONE NO NO
TRNCUSINT001 NUMBER 1 1 NONE NO NO
TRNCUSINT002 NUMBER 1 1 NONE NO NO
TRNCUSINT003 NUMBER 1 1 NONE NO NO
TRNCUSINT004 NUMBER 1 1 NONE NO NO
TRNCUSINT005 NUMBER 1 1 NONE NO NO
TRNCUSSTR001 NVARCHAR2 3 .333333333 NONE NO NO
TRNCUSSTR002 NVARCHAR2 5 .2 NONE NO NO
TRNCUSSTR003 NVARCHAR2 1 1 NONE NO NO
TRNCUSSTR004 NVARCHAR2 8 .125 NONE NO NO
TRNCUSSTR005 NVARCHAR2 6 .166666667 NONE NO NO
TRNCUSSTR006 NVARCHAR2 106 .009433962 NONE NO NO
TRNCUSSTR007 NVARCHAR2 0 0 NONE NO NO



Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: performance issues
Author: Tso P, South Africa
Date: Feb 02, 2018, 14:39, 19 days ago
Message:


TRNCUSSTR008 NVARCHAR2 0 0 NONE NO NO
TRNCUSSTR009 NVARCHAR2 0 0 NONE NO NO
TRNCUSSTR010 NVARCHAR2 1 1 NONE NO NO
TRNCUSSTR011 NVARCHAR2 2 .5 NONE NO NO
TRNCUSSTR012 NVARCHAR2 3 .333333333 NONE NO NO
TRNCUSSTR013 NVARCHAR2 64 .015625 NONE NO NO
TRNCUSSTR014 NVARCHAR2 6 .166666667 NONE NO NO
TRNCUSSTR015 NVARCHAR2 6 .166666667 NONE NO NO
TRNCUSSTR016 NVARCHAR2 65 .015384615 NONE NO NO
TRNCUSSTR017 NVARCHAR2 0 0 NONE NO NO
TRNCUSSTR018 NVARCHAR2 4 .25 NONE NO NO
TRNCUSSTR019 NVARCHAR2 2 .5 NONE NO NO
TRNCUSSTR020 NVARCHAR2 0 0 NONE NO NO
ACTION NUMBER 5 .2 NONE NO NO
REASON_ID NVARCHAR2 0 0 NONE NO NO
TRNSTATUS NUMBER 7 .001577287 FREQUENCY NO NO
TRNRCPTOTAL NUMBER 2 .5 NONE NO NO
TRNRCPERR NUMBER 2 .5 NONE NO NO
TRNRCPSCH NUMBER 1 1 NONE NO NO
TRNRCPPROC NUMBER 2 .5 NONE NO NO
TRNBCHTOTAL NUMBER 2 .5 NONE NO NO
TRNBCHERR NUMBER 2 .5 NONE NO NO
TRNBCHSCH NUMBER 1 1 NONE NO NO
TRNBCHPROC NUMBER 2 .5 NONE NO NO
TRNDATATYPE NUMBER 1 1 NONE NO NO
TRNDATAREF NVARCHAR2 0 0 NONE NO NO
TRNDATAXML XMLTYPE NONE NO YES
SYS_NC00140$ CLOB 0 0 NONE YES NO
TRNDATABLOB BLOB 0 0 NONE NO NO
TRNDATASIZE NUMBER 103 .009708738 NONE NO NO
TRNNAPOLTYPE NUMBER 1 1 NONE NO NO
TRNNAPOLREF NVARCHAR2 0 0 NONE NO NO
TRNNAPOLXML XMLTYPE NONE NO YES
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: performance issues
Author: Tso P, South Africa
Date: Feb 02, 2018, 14:40, 19 days ago
Message:

SYS_NC00146$ CLOB 0 0 NONE YES NO
TRNNAPOLBLOB BLOB 0 0 NONE NO NO
TRNNAPOLSIZE NUMBER 96 .010416667 NONE NO NO
TRNPRTLOGNAME NVARCHAR2 0 0 NONE NO NO
TRNDOLOG NUMBER 2 .5 NONE NO NO
TRNRETHOLD NUMBER 1 1 NONE NO NO
TRNHISTORY NUMBER 1 1 NONE NO NO
TRNHISTORICAL NUMBER 1 1 NONE NO NO
SRCTRN_ID NUMBER 1 1 NONE NO NO
JOB_ID NUMBER 317 .003154574 NONE NO NO
TRN_ID NUMBER 317 .003154574 NONE NO NO
UNIQUE_ID NVARCHAR2 317 .003154574 NONE NO NO
KEY1 NVARCHAR2 2 .00209205 FREQUENCY NO NO
KEY2 NVARCHAR2 2 .00209205 FREQUENCY NO NO
KEY3 NVARCHAR2 0 0 NONE NO NO
KEYID NVARCHAR2 106 .00209205 FREQUENCY NO NO
FORMSETID NVARCHAR2 317 .003154574 NONE NO NO
ARCKEY NVARCHAR2 0 0 NONE NO NO
RECTYPE NVARCHAR2 1 1 NONE NO NO
DESCR NVARCHAR2 7 .142857143 NONE NO NO
TRNNAME NVARCHAR2 0 0 NONE NO NO
DOCTYPE NVARCHAR2 0 0 NONE NO NO
DOCSUBTYPE NVARCHAR2 0 0 NONE NO NO
ORIGUSER NVARCHAR2 0 0 NONE NO NO
CURRUSER NVARCHAR2 0 0 NONE NO NO
CURRGROUP NVARCHAR2 0 0 NONE NO NO
CURRROLE NVARCHAR2 0 0 NONE NO NO
CURRSUPER NVARCHAR2 0 0 NONE NO NO
CURRAPP_ID NUMBER 1 .001577287 FREQUENCY NO NO
STATUSCODE NVARCHAR2 3 .001577287 FREQUENCY NO NO
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: performance issues
Author: Tso P, South Africa
Date: Feb 02, 2018, 14:42, 19 days ago
Message:

TRANCODE NVARCHAR2 0 0 NONE NO NO
APPROVALSTATE NVARCHAR2 3 .001577287 FREQUENCY NO NO
SECLEVEL NUMBER 1 1 NONE NO NO
TRNERR_ID NUMBER 43 .023255814 NONE NO NO
INUSE NVARCHAR2 0 0 NONE NO NO
CUSTOMIZED NUMBER 1 1 NONE NO NO
CREATETIME TIMESTAMP(6) 314 .003154574 HEIGHT BALANCED NO NO
BEGINTIME TIMESTAMP(6) 0 0 NONE NO NO
TRNSTARTTIME TIMESTAMP(6) 317 .003154574 NONE NO NO
MODIFYTIME TIMESTAMP(6) 317 .003154574 NONE NO NO
TRNMODIFYTIME TIMESTAMP(6) 317 .003154574 HEIGHT BALANCED NO NO
ARCTIME TIMESTAMP(6) 0 0 NONE NO NO
RETENTION TIMESTAMP(6) 0 0 NONE NO NO
TRNENDTIME TIMESTAMP(6) 168 .005952381 NONE NO NO
ENDTIME TIMESTAMP(6) 0 0 NONE NO NO
APPDATA RAW 0 0 NONE NO NO
JURISDICTN NVARCHAR2 0 0 NONE NO NO
LOCID NVARCHAR2 0 0 NONE NO NO
QUEUEID NVARCHAR2 0 0 NONE NO NO
SUBLOCID NVARCHAR2 0 0 NONE NO NO
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: performance issues
Author: Tso P, South Africa
Date: Feb 02, 2018, 14:42, 19 days ago
Message:

AGENCYID NVARCHAR2 0 0 NONE NO NO
FROMUSER NVARCHAR2 0 0 NONE NO NO
FROMTIME TIMESTAMP(6) 0 0 NONE NO NO
FROMGROUP NVARCHAR2 0 0 NONE NO NO
TOTIME TIMESTAMP(6) 0 0 NONE NO NO
TOUSER NVARCHAR2 0 0 NONE NO NO
TOGROUP NVARCHAR2 0 0 NONE NO NO
ROUTEDESC NVARCHAR2 0 0 NONE NO NO
PROCESSNAME NVARCHAR2 3 .333333333 NONE NO NO
TRNAPPDATE001 TIMESTAMP(6) 0 0 NONE NO NO
TRNAPPDATE002 TIMESTAMP(6) 0 0 NONE NO NO
TRNAPPDATE003 TIMESTAMP(6) 0 0 NONE NO NO
TRNAPPDATE004 TIMESTAMP(6) 0 0 NONE NO NO
TRNAPPDATE005 TIMESTAMP(6) 0 0 NONE NO NO
TRNAPPDEC001 NUMBER 0 0 NONE NO NO
TRNAPPDEC002 NUMBER 0 0 NONE NO NO
TRNAPPDEC003 NUMBER 0 0 NONE NO NO
TRNAPPDEC004 NUMBER 0 0 NONE NO NO
TRNAPPDEC005 NUMBER 0 0 NONE NO NO
TRNAPPINT001 NUMBER 1 1 NONE NO NO
TRNAPPINT002 NUMBER 1 1 NONE NO NO
TRNAPPINT003 NUMBER 1 1 NONE NO NO
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: performance issues
Author: Tso P, South Africa
Date: Feb 02, 2018, 14:42, 19 days ago
Message:

TRNAPPINT004 NUMBER 1 1 NONE NO NO
TRNAPPINT005 NUMBER 1 1 NONE NO NO
TRNAPPSTR001 NVARCHAR2 0 0 NONE NO NO
TRNAPPSTR002 NVARCHAR2 0 0 NONE NO NO
TRNAPPSTR003 NVARCHAR2 0 0 NONE NO NO
TRNAPPSTR004 NVARCHAR2 0 0 NONE NO NO
TRNAPPSTR005 NVARCHAR2 0 0 NONE NO NO
TRNAPPSTR006 NVARCHAR2 0 0 NONE NO NO
TRNAPPSTR007 NVARCHAR2 0 0 NONE NO NO
TRNAPPSTR008 NVARCHAR2 0 0 NONE NO NO
TRNAPPSTR009 NVARCHAR2 0 0 NONE NO NO
TRNAPPSTR010 NVARCHAR2 0 0 NONE NO NO
TRNAPPSTR011 NVARCHAR2 0 0 NONE NO NO
TRNAPPSTR012 NVARCHAR2 0 0 NONE NO NO
TRNAPPSTR013 NVARCHAR2 0 0 NONE NO NO
TRNAPPSTR014 NVARCHAR2 0 0 NONE NO NO
TRNAPPSTR015 NVARCHAR2 0 0 NONE NO NO
TRNCUSDATE001 TIMESTAMP(6) 86 .011627907 NONE NO NO
TRNCUSDATE002 TIMESTAMP(6) 0 0 NONE NO NO
TRNCUSDATE003 TIMESTAMP(6) 0 0 NONE NO NO
TRNCUSDATE004 TIMESTAMP(6) 0 0 NONE NO NO
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: performance issues
Author: Tso P, South Africa
Date: Feb 02, 2018, 14:50, 19 days ago
Message: I had post the results in batches,

NB I had an error with an ambiguity when I ran the query,

I changed the aliases to be as follows:

SELECT dl.owner,
dl.table_name,
dl.column_name,
dl.segment_name,
dl.tablespace_name,
ds.bytes
FROM dba_lobs dl
INNER JOIN dba_segments ds
ON dl.owner = ds.owner
AND dl.segment_name = ds.segment_name
WHERE ds.owner = 'DMKR_ASLINE'
AND dl.table_name = 'TRNS';

Then results:


DMKR_ASLINE TRNS TRNNAPOLBLOB SYS_LOB0000091439C00147$$ DMKR_ASLINE 131072
DMKR_ASLINE TRNS SYS_NC00146$ SYS_LOB0000091439C00146$$ DMKR_ASLINE 268632064
DMKR_ASLINE TRNS TRNDATABLOB SYS_LOB0000091439C00141$$ DMKR_ASLINE 131072
DMKR_ASLINE TRNS SYS_NC00140$ SYS_LOB0000091439C00140$$ DMKR_ASLINE 5373952


Thanks


Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: performance issues
Author: Tso P, South Africa
Date: Feb 02, 2018, 14:56, 19 days ago
Message: My apologies with the first query results, I did my thing by trying to convert bytes to megabytes...
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: performance issues
Author: Jan Schnackenberg, Germany
Date: Feb 02, 2018, 17:11, 18 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message: Ok, obviously, yes, the LOB segment for column "SYS_NC00146$" is the culprit here. This segment is 256MB in size and this is probably fetched by the "SELECT * FROM ...".


What does


SELECT *
FROM dba_tab_cols
WHERE owner = 'DMKR_ASLINE'
AND table_name = 'TRNS'
AND column_name LIKE 'SYS_NC%';


show?
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: performance issues
Author: Tso P, South Africa
Date: Feb 05, 2018, 09:01, 16 days ago
Message: Thanks Jan

Below please find the results:

 

DMKR_ASLINE TRNS SYS_NC00155$ RAW 31 Y 12 "STATUSCODE" 3 FEFBAFFF FEFBBDFF 0.00154320987654321 0 3 03/FEB/18 324 YES NO 5 0 NO YES YES YES 155 FREQUENCY SYS_NC00155$
DMKR_ASLINE TRNS SYS_NC00156$ RAW 18 Y 12 "MODIFYTIME" 324 8789FDFCFACCD8FF 878AF3F4F0C8DDFF 0.00308641975308642 0 1 03/FEB/18 324 YES NO 10 0 NO YES YES YES 156 NONE SYS_NC00156$
DMKR_ASLINE TRNS SYS_NC00140$ CLOB 4000 Y 139 0 0 0 0 03/FEB/18 324 CHAR_CS 4000 YES NO 87 0 NO YES YES NO 139 140 NONE SYS_NC00140$
DMKR_ASLINE TRNS SYS_NC00146$ CLOB 4000 Y 144 0 0 79 0 03/FEB/18 245 CHAR_CS 4000 YES NO 67 0 NO YES YES NO 144 146 NONE SYS_NC00146$


Thanks for your help...
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: performance issues
Author: Tso P, South Africa
Date: Feb 06, 2018, 07:20, 15 days ago
Message: What should I do with this LOB that is giving issues?
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: performance issues
Author: Jan Schnackenberg, Germany
Date: Feb 08, 2018, 08:45, 13 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message: You (no, your application developers) should take a look whats saved in these LOBs. And if there's something wrong in there.

I just showed you why your select returns so many rows and so much data.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: performance issues
Author: Tso P, South Africa
Date: Feb 13, 2018, 16:20, 7 days ago
Message: Thanks Jan

Much appreciated !!!
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here