| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: How to tune SQL to avoid ORA-03232 ?
Jan
Apologies for the length of this mail - just that there are so many possibilities. Whilst we wait for the explain plan some things immediately look like they could be improved. You could try the following - removing one "IN" clause with a straight join replacement (since starting to type Billy has mailed about exaclty this).
SELECT
Per.Name,
P.Pers_ID,
P.special_NR
FROM
Bigtable1 P, --1 million rows
Bigtable2 Per --1 million rows
WHERE
To_Number(Substr(To_Char(P.Special_Nr),2,4)) in
(
SELECT distinct s.vendor_id
FROM
Bigtable3 s, --800,000 rows
Bigtable4 b, --1 million rows
And P.Pers_ID = Per.ID And P.Company = :P_Company And P.Dept_ID = :P_Dept_ID
Alternatively if there are many rows in Bigtable4 for a matching row in Bigtable3 for the date criteria try using an exists - the IN and the last AND clause in that sub-query are testing the same thing - namely that s.id = b.az_id
SELECT
Per.Name,
P.Pers_ID,
P.special_NR
FROM
Bigtable1 P, --1 million rows
Bigtable2 Per --1 million rows
WHERE
To_Number(Substr(To_Char(P.Special_Nr),2,4)) in
(
SELECT distinct s.vendor_id
FROM Bigtable3 s --800,000 rows
WHERE exists
(
SELECT 'x'
FROM Bigtable4 b --1 million rows
WHERE B.Fact_date Between :P_Date_From And :P_Date_to
AND s.id = b.az_id
And P.Pers_ID = Per.ID And P.Company = :P_Company And P.Dept_ID = :P_Dept_ID
The same could be tried for the first IN too - removing the need for the
function based index. Again this depends on which criteria are most
selective. This would work best with indexes on
1. P.Company, Dept_ID (one or both in one index - the order if using both
being dependant on use elsewhere)
2. Per.ID
3. b.az_id, fact_date (depending on selectivity of these columns - for this
query it's likely to be useful to have both in the order given)
4. s.id
SELECT
Per.Name,
P.Pers_ID,
P.special_NR
FROM
Bigtable1 P, --1 million rows
Bigtable2 Per --1 million rows
WHERE EXISTS
(
select 'x'
from Bigtable3 s, --800,000 rows
Bigtable4 b --1 million rows
where B.Fact_date Between :P_Date_From And :P_Date_to
and s.id = b.az_id
and s.vendor_id = To_Number(Substr(To_Char(P.Special_Nr),2,4))
)
And P.Pers_ID = Per.ID And P.Company = :P_Company And P.Dept_ID = :P_Dept_ID
"Jan Gelbrich" <j_gelbrich_at_westfalen-blatt.de> wrote in message
news:aoj2ri$n3o9s$1_at_ID-152732.news.dfncis.de...
> Hello, this time I have a tuning question.
>
> A member of my team has a query thas runs forever and then stops by
> messaging
> ORA-03232: next 15 extents could not be allocated in TS TEMP
>
> The query is:
>
> SELECT /*+ FIRST_ROWS */
> Per.Name,
> P.Pers_ID,
> P.special_NR
> FROM
> Bigtable1 P, --1 million rows
> Bigtable2 Per --1 million rows
> WHERE
> To_Number(Substr(To_Char(P.Special_Nr),2,4)) in
> (
> select distinct s.vendor_id
> from Bigtable3 s --800,000 rows
> where id in
> (
> select az_id
> from Bigtable4 b --1 million rows
> where B.Fact_date Between :P_Date_From And :P_Date_to
> and s.id = b.az_id
> )
> )
> And P.Pers_ID = Per.ID
> And P.Company = :P_Company
> And P.Dept_ID = :P_Dept_ID
>
> I increased initial and next extents of default storage to 32k in TS TEMP.
> TS TEMP is still a DMT (i would like to migrate it to LMT, but I am not
> allowed to for some reason ...),
> and it has 1G space. 600MB of it is not fragmentated.
>
> Parameters set:
> ----------------------------------------------------------------
> 471 hash_join_enabled
> TRUE
> 472 hash_area_size
> 1280000
> 473 hash_multiblock_io_count
> 0 (AFAIK that this parameters does not need to be set as it is calculated
by
> the optimizer)
>
> Oracle EE 8.1.7 on AIX 4.3.3
>
> What is interesting about it: it is an old query is embedded in a Report
> that ran fine before we migrated from 8.0.6. some months ago.
> The query is run just once a year, so we discovered the problem too late.
On
> the other hand, it runs in HORA, slowly (600s), but it runs at least.
>
> What is wrong with the query or my settings ? ?
> I would appreciate any advices.
>
> Jan
>
>
"Jan Gelbrich" <j_gelbrich_at_westfalen-blatt.de> wrote in message
news:aoj2ri$n3o9s$1_at_ID-152732.news.dfncis.de...
> Hello, this time I have a tuning question.
>
> A member of my team has a query thas runs forever and then stops by
> messaging
> ORA-03232: next 15 extents could not be allocated in TS TEMP
>
> The query is:
>
> SELECT /*+ FIRST_ROWS */
> Per.Name,
> P.Pers_ID,
> P.special_NR
> FROM
> Bigtable1 P, --1 million rows
> Bigtable2 Per --1 million rows
> WHERE
> To_Number(Substr(To_Char(P.Special_Nr),2,4)) in
> (
> select distinct s.vendor_id
> from Bigtable3 s --800,000 rows
> where id in
> (
> select az_id
> from Bigtable4 b --1 million rows
> where B.Fact_date Between :P_Date_From And :P_Date_to
> and s.id = b.az_id
> )
> )
> And P.Pers_ID = Per.ID
> And P.Company = :P_Company
> And P.Dept_ID = :P_Dept_ID
>
> I increased initial and next extents of default storage to 32k in TS TEMP.
> TS TEMP is still a DMT (i would like to migrate it to LMT, but I am not
> allowed to for some reason ...),
> and it has 1G space. 600MB of it is not fragmentated.
>
> Parameters set:
> ----------------------------------------------------------------
> 471 hash_join_enabled
> TRUE
> 472 hash_area_size
> 1280000
> 473 hash_multiblock_io_count
> 0 (AFAIK that this parameters does not need to be set as it is calculated
by
> the optimizer)
>
> Oracle EE 8.1.7 on AIX 4.3.3
>
> What is interesting about it: it is an old query is embedded in a Report
> that ran fine before we migrated from 8.0.6. some months ago.
> The query is run just once a year, so we discovered the problem too late.
On
> the other hand, it runs in HORA, slowly (600s), but it runs at least.
>
> What is wrong with the query or my settings ? ?
> I would appreciate any advices.
>
> Jan
>
>
Received on Wed Oct 16 2002 - 03:07:20 CDT
 |
 |