Home » RDBMS Server » Server Administration » Temp grow 1TB ( x86_64 x86_64 x86_64 GNU/Linux )
Temp grow 1TB [message #649839] Fri, 08 April 2016 14:48 Go to next message
ceciaide
Messages: 8
Registered: April 2016
Location: mexico
Junior Member
when im execute a query with join

the temp is grown up to much

SELECT PROFILE.DATA.CELLCODE, PROFILE.DATA.CODE1, PROFILE.MATRIX.CUSTOMER_ID, PROFILE.MATRIX.SCORE
FROM (( PROFILE.UAC INNER JOIN PROFILE.DATA ON PROFILE.UAC.CELLCODE= PROFILE.DATA.CELLCODE AND
PROFILE.UAC.CODE1 = PROFILE.DATA.CODE1)
INNER JOIN PROFILE.MATRIX ON (PROFILE.DATA.CODE1= PROFILE.MATRIX.CODE))
ORDER BY PROFILE.DATA.CELLCODE,PROFILE.DATA.CODE1;

How can I resolve this?

its perfomance?


the temp its autoextend off.


but if i recreate Tb temp to small size
error
ORA-1652: Unable to extend temp segment by 128 in tablespace TEMP

Re: Temp grow 1TB [message #649840 is a reply to message #649839] Fri, 08 April 2016 15:44 Go to previous messageGo to next message
BlackSwan
Messages: 25848
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum.

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

ORAFAQ tuning below -
Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/mv/msg/84315/433888/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof
Re: Temp grow 1TB [message #649849 is a reply to message #649839] Sat, 09 April 2016 04:05 Go to previous messageGo to next message
John Watson
Messages: 7255
Registered: January 2010
Location: Global Village
Senior Member
If you format your code (as BS has asked) it is easier to understand:
SELECT PROFILE.data.cellcode,
       PROFILE.data.code1,
       PROFILE.matrix.customer_id,
       PROFILE.matrix.score
FROM   (( PROFILE.uac
          inner join PROFILE.data
                  ON PROFILE.uac.cellcode = PROFILE.data.cellcode
                     AND PROFILE.uac.code1 = PROFILE.data.code1)
        inner join PROFILE.matrix
                ON ( PROFILE.data.code1 = PROFILE.matrix.code ))
ORDER  BY PROFILE.data.cellcode,
          PROFILE.data.code1 
I do not undertand how people can work with un-formatted code.

Do you really need the join to UAC ? Is it removing any rows? Could you get the same effect with a filter or a constraint?
Re: Temp grow 1TB [message #649974 is a reply to message #649849] Mon, 11 April 2016 18:24 Go to previous messageGo to next message
ceciaide
Messages: 8
Registered: April 2016
Location: mexico
Junior Member
hello john

its my first time of here

yes, we need to join UAC.


im send the complete script to the table

DROP TABLE PROFILEDB.MATRIX CASCADE CONSTRAINTS;

CREATE TABLE PROFILEDB.MATRIX
(
  CUSTOMER_ID  NUMBER(19)                        NOT NULL,
  CODE   VARCHAR2(192 BYTE)                NOT NULL,
  SCORE       FLOAT(126)                        NOT NULL
)
TABLESPACE USERS
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


CREATE UNIQUE INDEX PROFILEDB.MATRIX_PK ON PROFILEDB.MATRIX
(CUSTOMER_ID, CODE)
LOGGING
TABLESPACE USERS
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;



DROP TABLE PROFILEDB.UAC CASCADE CONSTRAINTS;

CREATE TABLE PROFILEDB.UAC
(
  CODE    VARCHAR2(192 BYTE),
  CODE1  VARCHAR2(192 BYTE)
)
TABLESPACE USERS
RESULT_CACHE (MODE DEFAULT)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOLOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;
Re: Temp grow 1TB [message #649980 is a reply to message #649974] Tue, 12 April 2016 00:44 Go to previous messageGo to next message
John Watson
Messages: 7255
Registered: January 2010
Location: Global Village
Senior Member
Quote:
yes, we need to join UAC.
Why?
Re: Temp grow 1TB [message #649989 is a reply to message #649980] Tue, 12 April 2016 03:39 Go to previous messageGo to next message
cookiemonster
Messages: 13011
Registered: September 2008
Location: Rainy Manchester
Senior Member
Also we need the table script for data and the explain plan of the query.
Re: Temp grow 1TB [message #653588 is a reply to message #649989] Mon, 11 July 2016 16:30 Go to previous messageGo to next message
ceciaide
Messages: 8
Registered: April 2016
Location: mexico
Junior Member
hello,

the solution of temp grow 1TB

im running the stadistic, im recreate the temp 10gb
only full 2 -3 gb.



Re: Temp grow 1TB [message #653594 is a reply to message #653588] Tue, 12 July 2016 00:32 Go to previous messageGo to next message
Michel Cadot
Messages: 65377
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

How did you fit 1TB in 10GB or 2-3GB?
Or where does 1TB come from?

Re: Temp grow 1TB [message #653614 is a reply to message #649839] Tue, 12 July 2016 09:11 Go to previous message
bpeasland
Messages: 51
Registered: February 2015
Location: United States
Member

Are you sure stats are up-to-date on these tables? What is the Explain Plan.

If you're doing a Cartesian Product, and these tables have a good amount of rows, the result set may be very, very large, which could explain the high temp ts usage.

Cheers,
Brian
Previous Topic: AMM memory allocation with Huge amount of System Memory
Next Topic: What is the INSTANT_RESTORE parameter used for?
Goto Forum:
  


Current Time: Sat Feb 17 14:30:07 CST 2018

Total time taken to generate the page: 0.01024 seconds