- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Re: Select statement causing ORA-1652
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-07-2004 09:49 PM
тАО04-07-2004 09:49 PM
Re: Select statement causing ORA-1652
This is the SQL
select distinct l.box_no, l.box_prd, l.box_mfno, l.box_cno, l.wtest_date, l.pts_date, w.wip_date, w.wip_route || '/' || w.wip_oper step_name, w.wip_opcode, eq.eq_name ||'/'||eq.eq_code eqp, wt.wtest_waf disk from wip_data w, box_data l, eq_data eq, wtest_data wt where l.box_prd in ('255') AND l.wtest_date <= to_date '4/6/04 4:16:04
PM', 'MM/DD/RR HH:MI:SS AM') AND l.wtest_date >= to_date('3/24/04 4:16:04 PM', 'MM/DD/RR HH:MI:SS AM') AND w.wip_box = l.box_no AND w.wip_eqcode = eq.eq_code AND l.box_no = wt.wtest_box
when I remove the distinct keyword, the SQL returned > 1 million lines.
If I put the distinct keyword, Oracle will have to sort > 1 million lines
I have already set the sort area to 750MB. The server has 1.5G RAM
The tablespace TAB1 still has 900MB free space
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-07-2004 10:12 PM
тАО04-07-2004 10:12 PM
Re: Select statement causing ORA-1652
To get the actual size of the sort operation, you can use this rq :
select
SID ,
OPERATION_TYPE ,
POLICY ,
SUM(ROUND(WORK_AREA_SIZE/1024/1024)) WA_SIZE_MO,
SUM(ROUND(ACTUAL_MEM_USED /1024/1024)) ACTUAL_MEM_USED_MO ,
SUM(ROUND(MAX_MEM_USED /1024/1024)) MAX_MEM_USED_MO ,
SUM(NUMBER_PASSES) PASSES,
SUM(ROUND(TEMPSEG_SIZE /1024/1024)) DISQUE_MO
FROM
v$sql_workarea_active
GROUP BY
SID ,
OPERATION_TYPE,
POLICY
ORDER BY
OPERATION_TYPE,
SID
/
BUT YOU DID NOT ANSWER THE FISRT QUESTION :
Have you check your user temporary tablespace setting ?
It not normal that for a select statement you get ORA-1652 on the user TS.
Cheers
Nicolas
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-08-2004 01:42 AM
тАО04-08-2004 01:42 AM
Re: Select statement causing ORA-1652
> when I remove the distinct keyword, the SQL returned > 1 million lines.
> If I put the distinct keyword, Oracle will have to sort > 1 million lines
> I have already set the sort area to 750MB. The server has 1.5G RAM
Sounds like indeed you have to be serious about the sort setup.
And sounds like you basically did all you could for trying to stick to a memory sort.
Btw... I believe that once you are forced to go to disk you might as well trim back the sort_size dramatically to a couple of MB or whatever the rest of the application tends to be happy with.
Do you suppose you could come up with a nested query where the distinct becomes a much earlier filter, reducing the number of rows to work on earlier? You would have to poke at rowcounts to see if this makes sense.
Almost there....
Good luck,
Hein.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-11-2004 01:12 PM
тАО04-11-2004 01:12 PM
Re: Select statement causing ORA-1652
Anyway, the query
select temporary_tablespace
from dba_users
where username=
TEMPORARY_TABLESPACE
------------------------------
TAB1_TEMP
That is the temporary tablespace for the instance
>Do you suppose you could come up with a >nested query where the distinct becomes a >much earlier filter, reducing the number of >rows to work on earlier? You would have to >poke at rowcounts to see if this makes sense.
->nested query
You mean a select distinct statement inside a select statement? The query will then have small and multiple sorts, compared to now which is one big sort. I will look into this..
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-13-2004 09:21 PM
тАО04-13-2004 09:21 PM
Re: Select statement causing ORA-1652
ex :
SELECT /*+ ordered use_hash(TSLDMRST) parallel(TSLDMRST,4) */
...fields...
FROM TSLDMRST;
Beware that hints can provide immediate imporvement but DO REMEMBER that it is also dangerous to have many because when the time goes by, the condition may be very different and the improvement hints become a stupid directive. Bypass Oracle optimizer is not alway a good idea.
Another option is to look why Oracle choose this explanation plan and what you can do to correct it if don't feel that's the best way to procede.
- create index
- build and maintain statistics and historgram
- use materialized view if applicable
- ...
Check DBMS_STATS standard package :
PROCEDURE GATHER_TABLE_STATS
ownname VARCHAR2,
tabname VARCHAR2,
partname VARCHAR2 DEFAULT NULL,
estimate_percent NUMBER DEFAULT NULL,
block_sample BOOLEAN DEFAULT FALSE,
method_opt VARCHAR2 DEFAULT `FOR ALL COLUMNS SIZE 1',
degree NUMBER DEFAULT NULL,
granularity VARCHAR2 DEFAULT `DEFAULT',
cascade BOOLEAN DEFAULT FALSE,
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
statown VARCHAR2 DEFAULT NULL);
PROCEDURE GATHER_INDEX_STATS(
ownname VARCHAR2,
indname VARCHAR2,
partname VARCHAR2 DEFAULT NULL,
estimate_percent NUMBER DEFAULT NULL,
stattab VARCHAR2 DEFAULT NULL,
statid VARCHAR2 DEFAULT NULL,
statown VARCHAR2 DEFAULT NULL);
Cheers
Nicolas
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-16-2004 09:58 AM
тАО04-16-2004 09:58 AM
Re: Select statement causing ORA-1652
sort_area_size is 750 MB ????
I doubt that. Check V$PARAMETER, if it really has taken this value after the instance has started!
This is a PGA parameter in Oracle 8 !
Each server process needs to allocate it's own (private) sort area. So if you have i.e. 10 server-processes, you will have 7,5GB of sort-memory....
65K to 256K will be a reasonable sort_area_size.
If you really have 750MB to sort, go for an Index to support the sort and "hint" the statement.
Hope this helps
Volker
- « Previous
-
- 1
- 2
- Next »