Re: Estimations are as good as it can be, what else can be done here?

Mohamed,
Thanks for reminding me that I ought to check the manuals occasionally.  (v$sq_monitor in Database Reference 19c: https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/V-SQL_MONITOR.html )

It's worth pointing out, however, that I made the comment I did only because I got DONE (FIRST N ROWS) after hitting Ctrl-C.

Off-hand I can think two possible explanations

1. I'm running command line SQL*Plus in Unix on the server; if you are running Windows O/S as a network client the handling of ctrl-c may be different.
2. You hit ctrl-c while Oracle was in a wait for PL/SQL timer, I had "set pause on" and hit ctrl-C while Oracle was waiting for "SQL*Net message from client".

Bottom line: SQL*Plus sessions can give different ending values for status in response to the same client action.

Regards
Jonathan Lewis

On Fri, 10 Nov 2023 at 08:39, Mohamed Houri <mohamed.houri_at_gmail.com> wrote:

> Jonathan,
> *I've learned something new today - I hadn't realised that the SQL Monitor
> would report "DONE (FIRST N ROWS)" if you interrupted a query after the
> first fetch, I would have guessed that a ctrl-C from SQL*Plus would have
> resulted in "DONE (ERROR)". So the 'first N rows' really could be the
> first N rows fetched out of 2M. Given that it's a sort unique (due to the
> distinct combined with the "order by" and the optimizer getting clever) the
> preceding hash join and entire 2M row sort must have completed before the
> first row was sent to the user.*
>
> You’re right to say that ctrl-C from SQL*Plus would have resulted in
> "DONE (ERROR)”. The DONE (FIRST N ROWS) status is different. It appears
> not, when the query itself is abruptly stopped, but when the client that
> activated the query terminates the query before all rows are fetched. The
> Oracle definition is crystal clear
>
> · DONE (FIRST N ROWS) - Execution terminated by the application
> before all rows were fetched
>
>
>
> Simply put, if I launch a query from SQLPLUS, wait until a couple of lines
> are fetched, and then:
>
> · Issue a Ctrl-C --> I will get DONE (ERROR)
>
> · Close the SQL*PLus window --> I will get DONE (FIRST N ROWS)
>
>
>

--
http://www.freelists.org/webpage/oracle-l