1

Topic: Statistics repairing: a philosophical question

Let's assume, we in storage have requests which work unstable. The analysis showed that a problem in statistican under several tables which was not updated for a long time.
The decision looks logical: to collect the actual statistics. The analysis shows that it  these plans.
Problem in that the code is overflowed , on basis a heap of profiles and  (even automatic capture  is included). It means that theoretically we to ours  can refine a row of plans, but thus we can break a row of others among which there can be more critical. We break certainly correctly, but nevertheless, it would not be desirable night calls.
Whether there is a method to check up as our patch affects the general performance of system before its application?
Variant: to check up on UAT-bases certainly it is good. The problem that as you try to make UAT completely on 100 % equivalent  it does not turn out (for the administrative reasons in particular), and in performance things important there can be any detail. What variants are still? How you arrive in similar cases?
Let's assume, we derive from AWR all a little loaded requests which are connected to these tables. How to check up, what these requests begin to work not worse in all possible situations? To launch them is not so a variant since this storage and some of them work as hours. Whether it is possible to make some estimate of consumption of resources for request without launching it? What resources are necessary for considering thus? Whether there are tools including standard  for similar things?

2

Re: Statistics repairing: a philosophical question

Well I do not know, how it is possible to come to life such....
Without really operating basis of the last version/day/updating.
I at all do not represent.
If only volume strongly big.

3

Re: Statistics repairing: a philosophical question

Valergrad;
Probably it turns out to carry out the test using pending stat
Truth to publish the new statistics for the necessary session it will be necessary manually:
ALTER SESSION SET OPTIMIZER_USE_PENDING_STATISTICS=TRUE;

4

Re: Statistics repairing: a philosophical question

Whether

Valergrad wrote:

There are tools including standard  for similar things?

Database Replay
Not so clearly why it is impossible to have analog . If is sensitive info - adjust data masking.
If a problem with volumes, that, as far as I understand, speech about storage also it is possible to transfer not all sections, and a part.
And generally the narration looks in the spirit of: at us there a brothel, here crutches as all at once to correct thus safely and seamless.
It is possible to provide safety with that statistican can be rolled away reversely - resore%stats or export%stats-> import%stats - look details in dbms_stats.
Whether

Valergrad wrote:

It is possible to make some estimate of consumption of resources for request without launching it?

at the Plan to look. Tuning Advisor, possibly, can show something more precisely, but I would not waste time on it .
PS. Profiles and  it is only temporary and rather safe fix so if you long time did not do normal improvings as a result it is necessary to pay for laziness. Though in   in my opinion it is completely indifferent that you collect statistics. Both and from others it is necessary to get rid of those .

5

Re: Statistics repairing: a philosophical question

Valergrad;
>> we Assume, we in storage have requests which work unstable
Whether it is possible to rewrite request, what it without statistics would build the correct plan, for example using left join to reference manuals, instead of their listing through a comma in from?

6

Re: Statistics repairing: a philosophical question

essbase.ru wrote:

Valergrad;
>> we Assume, we in storage have requests which work unstable
Whether it is possible to rewrite request, what it without statistics would build the correct plan, for example using left join to reference manuals, instead of their listing through a comma in from?

What for you write these to dullness?

7

Re: Statistics repairing: a philosophical question

Valergrad;
Download course on performance tuning this question there it is uncovered

8

Re: Statistics repairing: a philosophical question

xtender wrote:

Valergrad;
Download course on performance tuning this question there it is uncovered

Which course?

9

Re: Statistics repairing: a philosophical question

Valergrad;
It is possible to try to make the following
With  to export:
- The circuit () dataless
- To statistican, including the system
- All requests
- Execution plans of requests
- Adjustments creations of requests influencing plans
On test basis  all that is possible . To generate plans for all requests and to be convinced that they coincide with .
Probably it is necessary   plans and to derive  variables.
If it works, it is possible to change statistics at own discretion. Anew to generate plans and to check as it affects plans.
I.e. some regression testing turns out.
Probably there will be difficulties with obtaining of plans if on  it is actively used dynamic_sampling (not everywhere statistics is collected)
Itself in implementation process of this idea. I hope it turns out.

10

Re: Statistics repairing: a philosophical question

Valergrad;
Look about dbms_sqlpa (SQL Performance Analyzer), for example, here: https://oracle-base.com/articles/11g/sq … alyzer_api
And generally would descend on course or downloaded it with  any, there this subject is well chewed.
. Though personally I such do not use...