Re: Spd 19c adaptive statistics

So? What are you trying to achieve? To fix one particular plan? To fix several plans? You cannot create plan directives. You can only allow or disallow the instance optimizer to use them. From my experience, the most frequently encountered directive is "dynamic sampling is usable",. Unfortunately, the vast majority of my databases are OLTP databases, with OPTIMIZER_DYNAMIC_SAMPLING=0 (disabled). That means that the optimizer directive that dynamic sampling is usable, which usually happens when the row source cardinality is incorrectly estimated, is meaningless since the dynamic sampling has been deactivated by the administrator. If the table is being changed extremely rapidly and if the statistics becomes stale in less than a day, I usually use DYNAMIC_SAMPLING hint. However, I am usually not researching history, I am usually trying to fix one particular plan. More often than not, a rewrite solves the problem. Slice and dice (the WITH clause) usually solves the problem. I even had a developer asking me to optimize a query with SQL text consuming 73K. My first question is always: what are you trying to do? I accused that particular developer of trying to murder me in an unusual and cruel fashion. More often than not, the monstrosity gets rewritten into something manageable and performing well. The special problem are ORM (Object-Relational Mappers) which produce ghastly queries, awkward objects and memory leaks. The primary purpose of the ORM is to speed up aging of the DBA. The solution is usually to forgo the ORM and write a decent piece of code. That's all folks!

File:Thats all folks.svg - Wikimedia Commons

Happy New Year!

On 1/5/21 7:53 PM, Moustafa Ahmed wrote:
> My understanding is sql ppm directives are not sql/sql_id based rather
> expression based..
> A spd can be used by more than one sql..
>

-- 
Mladen Gogala
Database Consultant
http://mgogala.byethost5.com


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