I normally deal with SQL Server, but have the occasion to deal with our PeopleSoft student system which runs on Oracle. I always find the experience frustrating... and today is no exception, so I'm hoping someone can help me shed some light on my problems.

I am working on a query that works fine, but need to make some enhancements. The query currently runs in 10-15 seconds and returns only about 12,000 rows.

The first modification I have made is to add row numbers so that the client can process them more efficiently. All I did was add this to the select list:

ROW_NUMBER() OVER (
PARTITION BY PS.EMPLID, CASE WHEN APT1.ACAD_PLAN_TYPE = 'MIN' THEN 'Y' ELSE 'N' END
ORDER BY CASE WHEN APT1.ACAD_PLAN_TYPE = 'MIN' THEN 'Y' ELSE 'N' END, AP1.STDNT_CAR_NBR, APL1.PLAN_SEQUENCE) AS PLAN_SEQUENCE,

I understand that this might require some decent computation, but in this case the query does not complete...even after 10 minutes! Mind you, the result set here is still only 12K rows. Am I doing something wrong? Are there optimizations I can do?

The second thing I tried (which I think will work better for me in the long run) was to turn the query into a CTE so I could do some more aggregate functions. The first step was to just wrap the query as a CTE with NO ADDITIONAL PROCESSING. Even this, which should require no additional computation, never completes. Again I cancel it after about 10 minutes. I've used CTEs, elsewhere without problems. Am I missing something?

Either it is you: too lazy to ask the DBA for a fundamental tool-of-trade like Explain Plan so that you can do your job.

Or it is the DBA: too anal-retentive to allow programmers to do their jobs.

Seriously though, imagine you took your car to the mechanic (we looooove car metaphors here) and asked him to tell you what was wrong with it, but you wouldn't let him open the bonnet ("hood", for you North-Americans).

I'm not over-dramatising this just to make a point. It really is THAT important.

LOL...yes I understand. I have asked for permission to create objects in my schema, but I'm not positive I will get it, and I do not have recourse if I do not. Though I don't see that the would have a problem with it on our test system.

This is not my data, and I am not even a member of the division that owns it. I am a guest, and is something that could be taken away at their whim. It is in their best interest to give me access because I can generally get what I need without increasing their workload, but they could just as easily decide that I am no longer welcome.

So I can see the problem at operation 53 where a HASH GROUP BY is being generated. I won't waste space by executing the version with the ROW_NUMBER column inserted, because the exact same bottleneck is being generated in that version as well.

Can verify that your statistics are up-to-date? Can you verify that you have current system statistics? The last point may seem pedantic, but, the CBO needs accurate statistics for your system to know when to try a hash join or group by, and when not to. With 9i, one typically used "typical" settings for the system values; with 10gR2 one can have Oracle calculate the values.

More importantly, what are you trying to accomplish by adding row numbers? Using the row_number analytic function may not be the best way to do what you want to do. For example, please consider what row_number gives you that the rownum pseudo column would not.

The query is too complex to give you specific advice, so all I can offer is general advice - it's just to difficult to work through all of the possibilities with you.

So here's the general advice:

- I see that the only significant FILTERING of rows that occurs is on the table PS_STDNT_CAR_TERM (SCT). Yet this table is not the first one accessed. This may not be a problem if the filter clauses eliminate a small proportion of the table, however if these conditions eliminate more than 10% of the table, this table needs to be the first accessed. You can use a /*+LEADING(SCT)*/ hint to enforce this. See here for more.

- You have a mix of indexed NESTED LOOP joins and full scan / HASH joins. If your final result set includes less than 1% of the rows from a single table, then usually an indexed nested loops will be the best approach. More than 10%, a FTS / Hash join will be the best approach. I don't know the distribution of your data, so I cannot advise further, but you should ensure that each of the base tables in the join are using the right access method.

- Your Effective Date sub-queries may be suffering from Sub-query blowout. See here for more, and the Range Table Single-row Lookup section of this article.

- You have a lot of INDEX RANGE SCANs. Unless you know these to be returning a small number of rows, you need to be careful that they are not blowing out. See here for more. To be sure, you really need to run a trace and check the row counts in TKPROF.

- Check ALL of your NESTED LOOPS joins. The second child of a NL join is executed separately for each row returned from the first child. So if the second child is inefficient (eg. RANGE SCAN returning lots of rows, FULL table scan, or FULL index scan) then performance will blow out. See this article for more.Ross Leishman