Oracle execution plan-all_rows and first_rows (n) optimizer Modes

0. Create an environment

SQL> create usertest identified by test  2 default tablespace users  3 temporary tablespace temp  4 quota unlimited on users;User created.SQL> grant createsession, resource, alter session to test;Grant succeeded.SQL> conntest/test;Connected.SQL> create tableemp(id number, name varchar2(10));Table created.SQL> insert intoemp values(100, 'tom');1 row created.SQL> insert intoemp values(200, 'mike');1 row created.SQL> insert intoemp values(300, 'jack');1 row created.SQL> insert intoemp values(400, 'rose');1 row created.SQL> commit;Commit complete.

1. Low data volume 1.1 first_rows_10

SQL> setautotrace traceonly;SQL> executedbms_stats.gather_table_stats('TEST', 'emp', cascade=>true);PL/SQL proceduresuccessfully completed.SQL> altersession set optimizer_mode=first_rows_10;Session altered.

1.2 all_rows

SQL> alter sessionset optimizer_mode=all_rows;Session altered.

1.3 Analysis

By comparing the above simple examples, we can see that there is no index in the table. When the data volume is small and the value is unique, the two modes have the same performance.

2. Low data volume

SQL> create indexemp_idx on emp(name);Index created.SQL> setautotrace offSQL> insert intoemp select * from emp;4 rows created.SQL> insert intoemp select * from emp;8 rows created.SQL> /16 rows created.SQL> /32 rows created.SQL> /64 rows created.SQL> /128 rows created.SQL> /256 rows created.SQL> /512 rows created.SQL> /1024 rows created.SQL> /2048 rows created.SQL> /4096 rows created.SQL> /8192 rows created.SQL> /16384 rows created.SQL> /32768 rows created.SQL> commit;Commit complete.SQL> executedbms_stats.gather_table_stats('TEST', 'emp', cascade=>true);PL/SQL proceduresuccessfully completed.

2.1 first_rows_10

SQL> setautotrace traceonlySQL> altersession set optimizer_mode=first_rows_10;Session altered.

2.2 all_rows

SQL> setautotrace traceonlySQL> altersession set optimizer_mode=all_rows;Session altered.

2.3 Analysis

We can see that fisrt_rows has gone through the index, which is obviously not an ideal result. all_rows has gone through the full table scan, and we can see that the cost is significantly lower.