Yes, only profiles need to be "accepted".
Yes but...? "only profiles need to be accepted"... it's not so clear for me.
I execute the following statement:
execute dbms_sqltune.create_sql_plan_baseline(task_name => 'TASK_XXXXX', object_id => 2, task_owner => 'SYS', plan_hash => xxxxxxx);
Then this sql plan baseline which belongs to TASK_XXXXX will be automatically accepted after creation?
So if I make a query from DBA_SQL_PLAN_BASELINES then at this baseline will be a "Y" in the column "ACCEPTED"?
Thanks in advance.
When you create baseline it's "accepted", but as more plans are added, Oracle automatically will mark the best (or none) as "accepted".
I have experienced the case where an sql query has baseline and none of the plans are accepted due to the default plan is best.
Profiles on the other hand need to be recorded as "accepted" with DBMS_SQLTUNE.ACCEPT_SQL_PROFILE() for the optimizer to use them.
But you haven't answered my question again
I do not want to accept an sql-profile, it goes about a "create_sql_plan_baseline" with dbms_sqltune!
Please answer my question only if you know the answer.
I thought I had answered...
Ok, Here is what Oracle says:
The optimizer marks the initial plan generated for a SQL statement as accepted for use, and represents both the plan history and SQL plan baseline. The plan history includes all subsequent plans. During the SQL plan baseline evolution phase, the database adds plans to the baseline that have been verified not to cause performance regressions.
Hello, no, you don't.
1.) Manually loaded sql plan baselines will be accepted automatically.
2.) if you set the optimizer_capture_sql_plan_baseline to TRUE then the first plan will be also automatically accepted but any subsequent plans captured automatically will be only enabled but non-accepted! You have to evolve the newly captured plan if you want oracle use them.
But what if I do NOT LOAD but CREATE with the dbms_sqltune -> This is the question!
When you execute sqltune, it will create the necessary statements to either:
a) Create a sql profile -- or
b) Create a baseline
None of these are "accepted" until you actually execute the generated SQL.
You do not know what "accepted" means, don't?
Of course, the Sql Tuning Advisor generates recommendations -> you can also configure it, that he can accept and creates the recommended sql profile.
A sql plan baseline can be accepted or non-accepted in the sql plan management.
If there is already an accepted plan and the sql tuning advisor is set to be able to create a new baseline for the same statement in this case what happens?
In the Documentation stands: "If SQL plan management is used and there is already an existing plan baseline for the SQL statement, a new plan baseline will be added when a SQL profile is created."
But will this newly created plan baseline ACCEPTED or NON-ACCEPTED?
a I " " I this.
Will the baseline automatically be accepted in the sql plan management if I have already an other accepted plan baseline or it will be taken up as non-accepted plan and I have to evolve it manually?