Computing statistics and query plans

Computing statistics and query plans

Post by wtgr.. » Sun, 31 Dec 1899 09:00:00



A question for you Oracle gurus out there...

When an "alter table... compute statistics" is issued, does this
automatically cause Oracle to recompute the stored query plans that it
already has in memory?  I come from the Sybase world where is was
necessary to issue a recompile command on a table or index after
statistics were updated in order to assure that all the in-memory query
plans would be recalculated using the new statistics data.  I can't
seem to find any mention of this in the Oracle documentation, and
therefore I am hoping that no such step is necessary.  But does anyone
know the answer to this definitively?

Thanks

--
Tim Green
ADP, Inc

Sent via Deja.com http://www.deja.com/
Before you buy.

 
 
 

Computing statistics and query plans

Post by Sybrand Bakke » Sun, 31 Dec 1899 09:00:00


query plans are not stored, not even in memory. The associated sql is simply
invalidated and reparsed, when you compute statistictiscs, AFAIK

Hth,

--
Sybrand Bakker, Oracle DBA


> A question for you Oracle gurus out there...

> When an "alter table... compute statistics" is issued, does this
> automatically cause Oracle to recompute the stored query plans that it
> already has in memory?  I come from the Sybase world where is was
> necessary to issue a recompile command on a table or index after
> statistics were updated in order to assure that all the in-memory query
> plans would be recalculated using the new statistics data.  I can't
> seem to find any mention of this in the Oracle documentation, and
> therefore I am hoping that no such step is necessary.  But does anyone
> know the answer to this definitively?

> Thanks

> --
> Tim Green
> ADP, Inc

> Sent via Deja.com http://www.deja.com/
> Before you buy.


 
 
 

Computing statistics and query plans

Post by Jonathan Lewi » Sun, 31 Dec 1899 09:00:00


I think you have a terminology mix-up there.

A 'pre-parsed' SQL statement in the shared_pool
is loosely speaking an in-memory stored execution
plan.  With this interpretation the answer to the
original question is that such statements are
invalidated and a new path evaluated automatically.

--

Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk


>query plans are not stored, not even in memory. The associated sql is
simply
>invalidated and reparsed, when you compute statistictiscs, AFAIK

>Hth,

>--
>Sybrand Bakker, Oracle DBA

>> A question for you Oracle gurus out there...

>> When an "alter table... compute statistics" is issued, does this
>> automatically cause Oracle to recompute the stored query plans that it
>> already has in memory?  I come from the Sybase world where is was
>> necessary to issue a recompile command on a table or index after
>> statistics were updated in order to assure that all the in-memory query
>> plans would be recalculated using the new statistics data.  I can't
>> seem to find any mention of this in the Oracle documentation, and
>> therefore I am hoping that no such step is necessary.  But does anyone
>> know the answer to this definitively?

>> Thanks

>> --
>> Tim Green
>> ADP, Inc

>> Sent via Deja.com http://www.deja.com/
>> Before you buy.

 
 
 

Computing statistics and query plans

Post by wtgr.. » Sun, 31 Dec 1899 09:00:00


Thanks, this was exactly what I was looking for.  Excuse my use of the
term "query plan" - my Sybase roots are showing. : )  Do you know by
the way in the documentation where this is stated, however?  While I
have suspected as much, I'd like to know where to point others here
that question the accuracy of this statement, which up until this
point, has really just been an assumption of mine.



> I think you have a terminology mix-up there.

> A 'pre-parsed' SQL statement in the shared_pool
> is loosely speaking an in-memory stored execution
> plan.  With this interpretation the answer to the
> original question is that such statements are
> invalidated and a new path evaluated automatically.

> --

> Jonathan Lewis
> Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk



> >query plans are not stored, not even in memory. The associated sql is
> simply
> >invalidated and reparsed, when you compute statistictiscs, AFAIK

> >Hth,

> >--
> >Sybrand Bakker, Oracle DBA



- Show quoted text -

Quote:> >> A question for you Oracle gurus out there...

> >> When an "alter table... compute statistics" is issued, does this
> >> automatically cause Oracle to recompute the stored query plans
that it
> >> already has in memory?  I come from the Sybase world where is was
> >> necessary to issue a recompile command on a table or index after
> >> statistics were updated in order to assure that all the in-memory
query
> >> plans would be recalculated using the new statistics data.  I can't
> >> seem to find any mention of this in the Oracle documentation, and
> >> therefore I am hoping that no such step is necessary.  But does
anyone
> >> know the answer to this definitively?

> >> Thanks

> >> --
> >> Tim Green
> >> ADP, Inc

> >> Sent via Deja.com http://www.deja.com/
> >> Before you buy.

--
Tim Green
ADP, Inc

Sent via Deja.com http://www.deja.com/
Before you buy.

 
 
 

Computing statistics and query plans

Post by Jonathan Lewi » Sun, 31 Dec 1899 09:00:00


My biggest problem with the manuals is trying to
find something that I know exists.

You might look in:
    SQL reference under Anaylze
    Oracle Concepts under Shared Pool
    Oracle Tuning under Tuning Memory and Optimization

but I can't give you an guaranteed location.
Sorry

--

Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk


>Thanks, this was exactly what I was looking for.  Excuse my use of the
>term "query plan" - my Sybase roots are showing. : )  Do you know by
>the way in the documentation where this is stated, however?  While I
>have suspected as much, I'd like to know where to point others here
>that question the accuracy of this statement, which up until this
>point, has really just been an assumption of mine.



>> I think you have a terminology mix-up there.

>> A 'pre-parsed' SQL statement in the shared_pool
>> is loosely speaking an in-memory stored execution
>> plan.  With this interpretation the answer to the
>> original question is that such statements are
>> invalidated and a new path evaluated automatically.

>> --

>> Jonathan Lewis
>> Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

 
 
 

Computing statistics and query plans

Post by wtgr.. » Sun, 31 Dec 1899 09:00:00


Hehe  That's one of my problems with the Oracle manuals too.  Thanks



> My biggest problem with the manuals is trying to
> find something that I know exists.

> You might look in:
>     SQL reference under Anaylze
>     Oracle Concepts under Shared Pool
>     Oracle Tuning under Tuning Memory and Optimization

> but I can't give you an guaranteed location.
> Sorry

> --

> Jonathan Lewis
> Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk


> >Thanks, this was exactly what I was looking for.  Excuse my use of
the
> >term "query plan" - my Sybase roots are showing. : )  Do you know by
> >the way in the documentation where this is stated, however?  While I
> >have suspected as much, I'd like to know where to point others here
> >that question the accuracy of this statement, which up until this
> >point, has really just been an assumption of mine.



> >> I think you have a terminology mix-up there.

> >> A 'pre-parsed' SQL statement in the shared_pool
> >> is loosely speaking an in-memory stored execution
> >> plan.  With this interpretation the answer to the
> >> original question is that such statements are
> >> invalidated and a new path evaluated automatically.

> >> --

> >> Jonathan Lewis
> >> Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

--
Tim Green
ADP, Inc

Sent via Deja.com http://www.deja.com/
Before you buy.

 
 
 

Computing statistics and query plans

Post by Robert Goo » Sun, 31 Dec 1899 09:00:00


Server Concepts 7.3 Chapter 9 Memory Structures and Processes p9-24

"...  When the ANALYZE command is used to update or delete the
statistics of a table, cluster, or index, all shared SQL areas that
contain statements that reference the analyzed object are flushed from
the shared pool. The next time a flushed statement is executed, the
statement is parsed in a new shared SQL area to reflect the new
statistics for the object. ..."

Regards

Robert

On Wed, 26 Jan 2000 07:13:29 -0000, "Jonathan Lewis"


>My biggest problem with the manuals is trying to
>find something that I know exists.

>You might look in:
>    SQL reference under Anaylze
>    Oracle Concepts under Shared Pool
>    Oracle Tuning under Tuning Memory and Optimization

>but I can't give you an guaranteed location.
>Sorry

>--

>Jonathan Lewis
>Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk


>>Thanks, this was exactly what I was looking for.  Excuse my use of the
>>term "query plan" - my Sybase roots are showing. : )  Do you know by
>>the way in the documentation where this is stated, however?  While I
>>have suspected as much, I'd like to know where to point others here
>>that question the accuracy of this statement, which up until this
>>point, has really just been an assumption of mine.

 
 
 

Computing statistics and query plans

Post by Jonathan Lewi » Sun, 31 Dec 1899 09:00:00


See what I mean - it took you 14 days to find it ! ;)

--

Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk


>Server Concepts 7.3 Chapter 9 Memory Structures and Processes p9-24

>"...  When the ANALYZE command is used to update or delete the
>statistics of a table, cluster, or index, all shared SQL areas that
>contain statements that reference the analyzed object are flushed from
>the shared pool. The next time a flushed statement is executed, the
>statement is parsed in a new shared SQL area to reflect the new
>statistics for the object. ..."

>Regards

>Robert

>On Wed, 26 Jan 2000 07:13:29 -0000, "Jonathan Lewis"

>>My biggest problem with the manuals is trying to
>>find something that I know exists.

 
 
 

1. query plan statistics

Hi All,

I'm wondering if anyone can suggest a way to return a T-SQL result set of
statistics of a query plan for a query without actually running the query
itself. Again, I would like to be able to access it in a result set so the
Query Analyzer's 'Show Execution Plan' is not really what I'm looking for...

Also, can SQL provide realtime performance statistics in a resultset?

Rein Petersen

2. Batch updates with duplicate primary keys

3. RedBrick: statistics and query plan

4. Native SQL error: 1911

5. COMPUTE STATISTICS Very slow, please advise!

6. How to generate file text trough stored procedure?

7. Compute statistics by different users

8. Inserts using plpgsql

9. Analyze Table Compute Statistics but slowed down performance ??

10. Analyze table compute statistics suddenly setting wrong values in user_tables

11. compute statistics bug

12. Analyze table compute statistics adversely affects optimizer?

13. Why is Stored Proc plan slower than query plan