Re: Query optimization
Date: Fri, 10 Jan 2014 08:24:45 -0800 (PST)
Message-ID: <01385457-fc08-422e-8bde-e1ee6dd963ab_at_googlegroups.com>
[Quoted] On Friday, January 10, 2014 6:14:23 AM UTC-7, Mark wrote:
> I have a question about index usage in table queries. We need to do
>
> fast queries on new tables. Therefore the standard method of
>
> generating table statistics does not work. We have verified that
>
> Oracle is not using the indexes.
>
>
>
> Assuming that the tables are less than 1 day old and have 1-2Mio
>
> records/hour written to them what is the best way to achieve this?
>
>
>
> An added complication is that the tables are referred to by synonyms
>
> as the user has no way to predict the actual table name.
>
> --
>
> (\__/) M.
>
> (='.'=) If a man stands in a forest and no woman is around
>
> (")_(") is he still wrong?
Let's look at (hopefully) an example similar to the situation you almost described:
SQL> SQL> -- SQL> -- Create an empty table with no indexes SQL> -- SQL> create table emp2 as select * from emp where 0=1;
Table created.
SQL> SQL> -- SQL> -- Create an index on the empty table SQL> -- SQL> create index empno_idx on emp2(empno);
Index created.
SQL> SQL> -- SQL> -- Create another index on that same table SQL> -- SQL> create index sal_idx on emp2(sal);
Index created.
SQL> SQL> -- SQL> -- Create yet another index on this same table SQL> -- SQL> create index ename_job_idx on emp2(ename, job);
Index created.
SQL> SQL> -- SQL> -- Create one more index SQL> -- SQL> create index mgr_idx on emp2(nvl(mgr,0000));
Index created.
SQL> SQL> -- SQL> -- Populate the table (will insert 1,800,000 rows) SQL> -- SQL> SQL> begin 2 for i in 1..100000 loop 3 insert into emp2 4 select * from emp; 5 end loop; 6 7 commit;
8 end;
9 /
PL/SQL procedure successfully completed.
SQL> SQL> -- SQL> -- Select from emp2 and see if any indexes are used SQL> -- SQL> set autotrace on SQL> SQL> select *
2 from emp2
3 where job = 'CLERK';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
...
7900 JAMES CLERK 7698 03-DEC-81 950 30
7934 MILLER CLERK 7782 23-JAN-82 1300 10
7369 SMITH CLERK 7902 17-DEC-80 800 20
400000 rows selected.
Execution Plan
Plan hash value: 2941272003
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 413K| 34M| 3029 (1)| 00:00:01 | |* 1 | TABLE ACCESS FULL| EMP2 | 413K| 34M| 3029 (1)| 00:00:01 |
Predicate Information (identified by operation id):
1 - filter("JOB"='CLERK')
Note
- dynamic sampling used for this statement (level=2)
- automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold
Statistics
6 recursive calls
2 db block gets
47465 consistent gets
10582 physical reads
4408 redo size
17793420 bytes sent via SQL*Net to client
293845 bytes received via SQL*Net from client
26668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
400000 rows processed
SQL>
SQL> select *
2 from emp2
3 where nvl(mgr,0000)=0000;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7839 KING PRESIDENT 17-NOV-81 5000 10
7939 DUKE CEO 17-NOV-81 5000 10
7839 KING PRESIDENT 17-NOV-81 5000 10
...
7939 DUKE CEO 17-NOV-81 5000 10
7949 PRINCE CFO 17-NOV-81 5000 10
7959 QUEEN CIO 17-NOV-81 5000 10
7869 JACK PRESIDENT 17-NOV-81 5000 10
500000 rows selected.
Execution Plan
Plan hash value: 1077700836
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 18634 | 1819K| 1 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| EMP2 | 18634 | 1819K| 1 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | MGR_IDX | 7454 | | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
2 - access(NVL("MGR",0000)=0000)
Note
- dynamic sampling used for this statement (level=2)
- automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold
Statistics
6 recursive calls
1 db block gets
88814 consistent gets
9613 physical reads
818960 redo size
15553193 bytes sent via SQL*Net to client
367182 bytes received via SQL*Net from client
33335 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
500000 rows processed
SQL>
SQL> set autotrace off
SQL>
SQL> --
SQL> -- Generate statistics
SQL> --
SQL> exec dbms_stats.gather_table_stats('GRIBNAUT', 'EMP2', estimate_percent=>100, cascade=>true)
PL/SQL procedure successfully completed.
SQL> SQL> -- SQL> -- Try the queries again SQL> -- SQL> set autotrace on SQL> SQL> select *
2 from emp2
3 where job = 'CLERK';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
...
7900 JAMES CLERK 7698 03-DEC-81 950 30
7934 MILLER CLERK 7782 23-JAN-82 1300 10
7369 SMITH CLERK 7902 17-DEC-80 800 20
400000 rows selected.
Execution Plan
Plan hash value: 2941272003
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 400K| 14M| 3029 (1)| 00:00:01 | |* 1 | TABLE ACCESS FULL| EMP2 | 400K| 14M| 3029 (1)| 00:00:01 |
Predicate Information (identified by operation id):
1 - filter("JOB"='CLERK')
Note
- automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold
Statistics
0 recursive calls
1 db block gets
37029 consistent gets
10365 physical reads
0 redo size
17793420 bytes sent via SQL*Net to client
293845 bytes received via SQL*Net from client
26668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
400000 rows processed
SQL>
SQL> select *
2 from emp2
3 where nvl(mgr,0000)=0000;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7839 KING PRESIDENT 17-NOV-81 5000 10
7939 DUKE CEO 17-NOV-81 5000 10
7949 PRINCE CFO 17-NOV-81 5000 10
...
7939 DUKE CEO 17-NOV-81 5000 10
7949 PRINCE CFO 17-NOV-81 5000 10
7959 QUEEN CIO 17-NOV-81 5000 10
7869 JACK PRESIDENT 17-NOV-81 5000 10
500000 rows selected.
Execution Plan
Plan hash value: 2941272003
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 500K| 18M| 3043 (1)| 00:00:01 | |* 1 | TABLE ACCESS FULL| EMP2 | 500K| 18M| 3043 (1)| 00:00:01 |
Predicate Information (identified by operation id):
1 - filter(NVL("MGR",0000)=0000)
Note
- automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold
Statistics
1 recursive calls
1 db block gets
43720 consistent gets
10365 physical reads
0 redo size
15553193 bytes sent via SQL*Net to client
367182 bytes received via SQL*Net from client
33335 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
500000 rows processed
SQL> [Quoted] If Oracle determines that more than 30% of the table data will be returned then the cost of an index access path is far greater than the cost for a table scan which is why the second run used full table scans rather than the indexes. Note that when statistics were NOT gathered dynamic sampling caused Oracle to use the indexes. This example was run using 11.2.0.3; you may be running an older release (a fact you did not present in your original post) so please report which release of Oracle you are using.
[Quoted] [Quoted] Also, please report how you determined that the indexes are not being used; you posted no execution plans to prove your assertion.
David Fitzjarrell Received on Fri Jan 10 2014 - 17:24:45 CET