Re: First_rows and optimization

scthomp_at_ibm.net (Simon Thompson) wrote:

>I have found, using explain plan, that using FIRST_ROWS as the optimizer
>goal gives the fasted execution path in our environment. Often by a
>considerable amount. Oracle is v7.1.
 

>(1) I know I can change it for a session, but can I change it for all users
>as they logon? The are using a variety of tools, including ODBC, so I
>don't think I can use use alter session as they logon.

You can set OPTIMIZER_MODE=FIRST_ROWS in init.ora to make it automatic.

>(2) If I have the goal set to FIRST_ROWS when I analyse the table to create
>the statistics, does the optimiser always use the OPTIMIZER_GOAL of
>FIRST_ROWS for those tables, no matter what it is set to for the sessions?

It's only for the session doing the EXPLAIN PLAN and others that have explicitly set the optimizer goal unless you've changed the init parameter.

>This raises issues of how good is the cost based optimiser? The execution
>path it was choosing was clearly not the quickest path. Is the optimiser any
>better in 7.2? Or is FIRST_ROWS always better than ALL_ROWS?

FIRST_ROWS *is* the cost based optimizer. So is ALL_ROWS. It just changes how the cost is computed.

Our environment is a data warehouse and I've found the same thing to be true here. I recently changed the OPTIMIZER_MODE parameter from it's default which was CHOOSE, to FIRST_ROWS. CHOOSE was always yielding slower queries. It may have been causing the overall throughput to increase, but the application "feel" was slower because it took so much longer for the first rows to come back. In addition, our front-end query tool is Windows 3.1 based and until the first row comes back it causes the PC to lock up. FIRST_ROWS gained us the additional benefit of smoother multitasking (or at least the appearance of it to the users) under Windows 3.1 as well.

--
Chuck Hamilton
chuckh_at_ix.netcom.com

Never share a foxhole with anyone braver than yourself!