Re: How to tune SQL to avoid ORA-03232 ?

Jan Gelbrich wrote:

>
> 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

Hmm.. a nice little one from Hades I see. :-)

Why the FIRST_ROWS hint when dealing with a batch report?

The first problem is using that many conversions on the P.SPECAIL_NR. Firstly, when using a function on a column, it requires Oracle to do a physical read on that row to get that row into buffer so that the column value is accessible for the required conversion. Thus, a lot of physical rows are read that fails the criteria which is not great. Ideally you only want to do a physical row retrieval when that row matches the criteria.

Secondly, every function is an overhead. 3 functions are executed by Oracle on every physical row read from BigTable1. The TO_CHAR, the SUBSTR and the TO_NUMBER. These are overheads that quickly becomes noticable on large data sets - the time spend on these functions can add hours of runtime on VLT's. Instead of doing a TO_NUMBER on P.SPECIAL_NR, why not do a TO_CHAR instead on S.VENDOR_ID? Put the function on the least amount of data and decrease the overheads for that function.

The first suggestion is why not do away all together with those functions? Try a function based index. Or add an additional column to the table, and populated that as per the function (implement a trigger for this). This btw makes sense to me as RDBMS design rules do state that you should not store a complex value in a single column (data must be normalised properly). As soon as I need to go substringing like that, it raises alarms bells for me regarding how well the database was normalised and designed.

Second suggestion if the above is not possible - use Parallel Query. If you are going to hit a big table doing a lot of reads, better do that in parallel. This way at least you distribute the load of reading a row, applying the functions and checking the criteria, across more than one process.

Third suggestion. Look closely at _what_ is needed on the report and not at the above SQL. There just may be a better way to satisfy the report. For example, the above SUB SELECTS can likely be replaced with joins. Doing a lot of joins are not necessarily bad. If you can start of with the smallest data set first, using nested loop joins to the larger data sets via indexes (unique indexes prefered as these will be faster to use than index range scanning secondary indexes), you can have pretty good performance.

I had one similar like yours above a few months ago - only looking a _lot_ worse though with at SUB SELECTS and UNIONS. The printout of the SQL spanned 3 A4 pages of printout! I simply reversed the logic (perfoming all the unwanted row elimination up front, thus dealing with a very small dats set throughout the rest of the pretty complex SQL) - the query went from running over 3 minutes to running in 12 seconds.

Last suggestion. Use EXPLAIN PLAN. Use TKPROF. Look at the optimiser manual and play around with hints - Oracle's CBO does not always get it right. But that does not that you should too.. :-) IMO these type of nested sub SELECTS are usually an indication of a lack of understanding on how to effectively use Oracle and retrieve data via SQL.

--
Billy