Oracle 11 g maintenance window period, can automatically collect for the query optimizer USES statistical information,
Automatic Optimizer Statistics Collection:
The maintenance Windows default Monday to Friday, 10 PM every day, last 4 hours. On Saturday to Sunday, six o 'clock in the morning, for 20 hours,
During the maintenance window will cause the business system database query operation and modification of the operation, by blocking all hangs,
Has unified the maintenance window period is adjusted for every day at 3 o 'clock in the morning, lasts 2 hours. But because the business systems were used for 24 hours, so want to advance a reduce data during the maintenance window blocking the impact on the business systems, have the following questions, please answer!

There are several problems as follows:
1, why is blocked, the top of the maintenance window do cause relevant operation blocks?
2, the data statistics task close, have any influence on the performance of the business system?
3, if not to close the data statistical information task, how to reduce their impact on the business systems?

Oracle, please expert guidance, many thanks!

CodePudding user response:

1, why is blocked, the top of the maintenance window do cause relevant operation blocks?
Does not block table to collect statistics, add and delete fields, even delete field will not be blocked, only collect statistics comparison of IO, so are generally configuration database when idle running maintenance tasks,
2, the data statistics task close, have any influence on the performance of the business system?
Oracle is now the CBO optimizer to optimize the SQL, rely on the collection of statistics analysis it is concluded that the optimal execution plan, if you put the statistical information maintenance tasks is closed, the business tables of statistics are outdated inaccurate, the CBO concludes that the execution of the plan would not be the best, the index such as go, execution plan go fulfill table, SQL performance will be affected,
3, if not to close the data statistical information task, how to reduce their impact on the business systems?
Statistics task or it is necessary to open, transferred to the business business executive,

CodePudding user response:

The building in detail below, how do you collect, such as how much is the proportion of the collection,

Two Suggestions:
1, if the proportion set too high, can proper cut once, 10% 20%,
2, different business modules were collected, collecting financial Monday, Tuesday, collected in the supply chain, on Wednesday to collect other modules,

CodePudding user response:

If without the support of statistical information application system is running pretty huan, so don't collect statistics also nothing important, but, if you have been open to collect statistics job before, had better not stopped, because as the growth of the data, the original statistics will be more and more do not conform to the actual situation, the CBO generate lower risk will become more and more efficient execution plan, one day, you may want to encounter the resulting SQL performance mutation, serious when there may be the whole library performance problems, or even downtime...

It's not what kind of consequences, actually is a probability problem, don't talk with a good, or closed must be bad, have the latest detailed statistics, the CBO tends to generate more efficient execution plan, but does not mean that it can make all the SQL execution plan better (less) likely also, at least not sure good control to perform before the application of high frequency SQL execution plan, suggest that open the job of automatic collecting statistics, have encountered even use system bring job to collect statistics also do not have access to appropriate SQL execution plan, to do special processing, such as collecting more detailed, or accurate statistics, use the profile method such as fixed execution plan, etc.,

CodePudding user response:

reference 1st floor liuzhijian2008x response:

1, why clog, maintenance window to do the above things will cause the related operation blocks?
Does not block table to collect statistics, add and delete fields, even delete field will not be blocked, only collect statistics comparison of IO, so are generally configuration database when idle running maintenance tasks,
2, the data statistics task close, have any influence on the performance of the business system?
Oracle is now the CBO optimizer to optimize the SQL, rely on the collection of statistics analysis it is concluded that the optimal execution plan, if you put the statistical information maintenance tasks is closed, the business tables of statistics are outdated inaccurate, the CBO concludes that the execution of the plan would not be the best, the index such as go, execution plan go fulfill table, SQL performance will be affected,
3, if not to close the data statistical information task, how to reduce their impact on the business systems?
Statistics task or it is necessary to open, transferred to the business business executive,

The reality is that during the maintenance window, there will be a query stuck situation; After maintenance window of time, such as automatic recovery again,
What may cause jam,
The query statements only 1 ms, the execution efficiency of the actual cost and only 1, disk and only a few bytes, how to explain?

CodePudding user response:

Aichitudou

reference 4 floor response:

Quote: refer to 1st floor liuzhijian2008x response:

1, why is blocked, the top of the maintenance window do cause relevant operation blocks?
Does not block table to collect statistics, add and delete fields, even delete field will not be blocked, only collect statistics comparison of IO, so are generally configuration database when idle running maintenance tasks,
2, the data statistics task close, have any influence on the performance of the business system?
Oracle is now the CBO optimizer to optimize the SQL, rely on the collection of statistics analysis it is concluded that the optimal execution plan, if you put the statistical information maintenance tasks is closed, the business tables of statistics are outdated inaccurate, the CBO concludes that the execution of the plan would not be the best, the index such as go, execution plan go fulfill table, SQL performance will be affected,
3, if not to close the data statistical information task, how to reduce their impact on the business systems?
Statistics task or it is necessary to open, transferred to the business business executive,

The reality is that during the maintenance window, there will be a query stuck situation; After maintenance window of time, such as automatic recovery again,
What may cause jam,
The query statements only 1 ms, the execution efficiency of the actual cost and only 1, disk and only a few bytes, how to explain?

Where did you see the cost of only 1, disk bytes these information? In the execution plan?
If you get stuck, monitor the session in what wait for events, such as concrete analysis again,

CodePudding user response:

Aichitudou

reference 4 floor response:

Quote: refer to 1st floor liuzhijian2008x response:

1, why is blocked, the top of the maintenance window do cause relevant operation blocks?
Does not block table to collect statistics, add and delete fields, even delete field will not be blocked, only collect statistics comparison of IO, so are generally configuration database when idle running maintenance tasks,
2, the data statistics task close, have any influence on the performance of the business system?
Oracle is now the CBO optimizer to optimize the SQL, rely on the collection of statistics analysis it is concluded that the optimal execution plan, if you put the statistical information maintenance tasks is closed, the business tables of statistics are outdated inaccurate, the CBO concludes that the execution of the plan would not be the best, the index such as go, execution plan go fulfill table, SQL performance will be affected,
3, if not to close the data statistical information task, how to reduce their impact on the business systems?
Statistics task or it is necessary to open, transferred to the business business executive,

The reality is that during the maintenance window, there will be a query stuck situation; After maintenance window of time, such as automatic recovery again,
What may cause jam,
The query statements only 1 ms, the execution efficiency of the actual cost and only 1, disk and only a few bytes, how to explain?

1 to see if the operating system level, resource is in short supply;
2, take a look at your statement what is stuck, how execution plan;
3, when your statement is stuck, check it waiting for events

Provide this information to do further investigation

CodePudding user response:

In addition, the so-called "the query statements only 1 ms, the execution efficiency of the actual cost and only 1, disk and only a few bytes", probably is the result of statistics has a problem, in fact is not,

CodePudding user response:

http://blog.csdn.net/liuzhijian2008x/article/details/79267814
Card when you refer to my blog under the monitoring of what wait for events,