Oracle Database version 11g.
I set up Oracle CBO parameters OPTIMIZER_MODE with FIRST_ROWS_10. If i use Hint ALL_ROWS in SQL, what will effect FIRST_ROWS_10 or ALL_ROWS. And will all the sql effect with FIRST_ROWS_10 or depend on situation Oracle DB will auto choes use FIRST_ROWS_10 or ALL_ROWS.
Statement hints override session settings, and session settings override instance parameters. It's important to understand this precedence because you want to solve problems as specifically as possible. Do not change the OPTIMIZER_MODE for the entire database if you're only having problems with one session or one statement. Otherwise you might create more problems than you solve.
You can test the precedence yourself by running different combinations of these commands:
alter system set optimizer_mode=first_rows_10;
alter session set optimizer_mode=first_rows_10;
select /*+ first_rows_10 */ * from dual;
alter system set optimizer_mode=all_rows;
alter session set optimizer_mode=all_rows;
select /*+ all_rows */ * from dual;
And then compare the execution plans for these statements and pay special attention to the hints listed in the "Outline Data" section:
explain plan for select * from dual;
select * from table(dbms_xplan.display(format => '+outline'));
explain plan for select /*+ all_rows */ * from dual;
select * from table(dbms_xplan.display(format => '+outline'));
explain plan for select /*+ first_rows(10) */ * from dual;
select * from table(dbms_xplan.display(format => '+outline'));