<<Oracle数据库性能优化艺术（第五期）>> 第5周 Hints

最新推荐文章于 2023-09-28 14:00:32 发布
最新推荐文章于 2023-09-28 14:00:32 发布
阅读量1.2k 收藏
点赞数
分类专栏： Oracle - 认证/杂项
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/t0nsha/article/details/19285473
版权

1.写一条SQL，使它通过全表扫描方式的效率优于索引访问，分别给出各自的执行计划。 

SQL> drop table t purge;

Table dropped.

SQL> create table t as select * from dba_objects;

Table created.

SQL> update t set object_id=1;

393493 rows updated.

SQL> update t set object_id=99 where rownum=1;

1 row updated.

SQL> commit;

Commit complete.

SQL> select object_id,count(*) from t group by object_id;

 OBJECT_ID   COUNT(*)
---------- ----------
         1     393492
        99          1

SQL> create index idx_t_objid on t(object_id);

Index created.

SQL> exec dbms_stats.gather_table_stats(user,'t',cascade=>true);

PL/SQL procedure successfully completed.

SQL> set autotrace traceonly;
SQL> select * from t where object_id=1;

393492 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   393K|    35M|  1559   (2)| 00:00:19 |
|*  1 |  TABLE ACCESS FULL| T    |   393K|    35M|  1559   (2)| 00:00:19 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID"=1)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      31435  consistent gets
          0  physical reads
          0  redo size
   20835397  bytes sent via SQL*Net to client
     288902  bytes received via SQL*Net from client
      26234  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     393492  rows processed

SQL> select /*+ index(t) */ * from t where object_id=1;

393492 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3638283050

-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |   393K|    35M|  6352   (1)| 00:01:17 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T           |   393K|    35M|  6352   (1)| 00:01:17 |
|*  2 |   INDEX RANGE SCAN          | IDX_T_OBJID |   393K|       |   782   (2)| 00:00:10 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"=1)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      58343  consistent gets
          0  physical reads
          0  redo size
   20846694  bytes sent via SQL*Net to client
     288902  bytes received via SQL*Net from client
      26234  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     393492  rows processed

SQL>

--EOF--


2.自己构造三条关联查询的SQL，分别适用于nested loop，hash join,merge join 关联,对于每条sql语句，分别通过hint产生其它两种关联方式的执行计划，并比较性能差异。 


=====================================

nested loop (一大表一小表,仅大表连接字段有索引)

SQL> drop table t purge;

Table dropped.

SQL> drop table t1 purge;

Table dropped.

SQL> create table t as select * from dba_objects;

Table created.

SQL> create table t1 as select * from dba_objects where rownum<100;

Table created.

SQL> create index idx_t_objid on t(object_id);

Index created.

SQL> exec dbms_stats.gather_table_stats(user,'t',cascade=>true);

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats(user,'t1',cascade=>true);

PL/SQL procedure successfully completed.

SQL> set autotrace traceonly
SQL> select t.* from t,t1 where t.object_id=t1.object_id;

99 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1724985652

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |    99 |  9999 |   300   (0)| 00:00:04 |
|   1 |  NESTED LOOPS                |             |       |       |            |          |
|   2 |   NESTED LOOPS               |             |    99 |  9999 |   300   (0)| 00:00:04 |
|   3 |    TABLE ACCESS FULL         | T1          |    99 |   396 |     3   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | IDX_T_OBJID |     1 |       |     2   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| T           |     1 |    97 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("T"."OBJECT_ID"="T1"."OBJECT_ID")


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         46  consistent gets
          0  physical reads
          0  redo size
       4556  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         99  rows processed

SQL> select /*+ use_hash(t t1) */ t.* from t,t1 where t.object_id=t1.object_id;

99 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1444793974

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    99 |  9999 |  1565   (3)| 00:00:19 |
|*  1 |  HASH JOIN         |      |    99 |  9999 |  1565   (3)| 00:00:19 |
|   2 |   TABLE ACCESS FULL| T1   |    99 |   396 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T    |   393K|    36M|  1556   (2)| 00:00:19 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T"."OBJECT_ID"="T1"."OBJECT_ID")


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       5592  consistent gets
          0  physical reads
          0  redo size
       4556  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         99  rows processed

SQL> select /*+ use_merge(t t1) */ t.* from t,t1 where t.object_id=t1.object_id;

99 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3123282277

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |    99 |  9999 |  6844   (1)| 00:01:23 |
|   1 |  MERGE JOIN                  |             |    99 |  9999 |  6844   (1)| 00:01:23 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T           |   393K|    36M|  6840   (1)| 00:01:23 |
|   3 |    INDEX FULL SCAN           | IDX_T_OBJID |   393K|       |   911   (2)| 00:00:11 |
|*  4 |   SORT JOIN                  |             |    99 |   396 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL         | T1          |    99 |   396 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("T"."OBJECT_ID"="T1"."OBJECT_ID")
       filter("T"."OBJECT_ID"="T1"."OBJECT_ID")


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         23  consistent gets
          0  physical reads
          0  redo size
       4556  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         99  rows processed

SQL>



=====================================


hash join (一大表一小表, 两表连接字段都没有索引)


SQL> drop table t;

Table dropped.

SQL> create table t as select * from dba_objects;

Table created.

SQL> drop table t1;

Table dropped.

SQL> create table t1 as select * from dba_objects where rownum<100;

Table created.

SQL> exec dbms_stats.gather_table_stats(user,'t',cascade=>true);

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats(user,'t1',cascade=>true);

PL/SQL procedure successfully completed.

SQL> set autotrace traceonly;
SQL> select t.* from t,t1 where t.object_id=t1.object_id;

99 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1444793974

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    99 |  9999 |  1565   (3)| 00:00:19 |
|*  1 |  HASH JOIN         |      |    99 |  9999 |  1565   (3)| 00:00:19 |
|   2 |   TABLE ACCESS FULL| T1   |    99 |   396 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T    |   393K|    36M|  1556   (2)| 00:00:19 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T"."OBJECT_ID"="T1"."OBJECT_ID")


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       5592  consistent gets
          0  physical reads
          0  redo size
       4556  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         99  rows processed

SQL> select /*+ use_nl(t t1) */ t.* from t,t1 where t.object_id=t1.object_id;

99 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2196473728

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    99 |  9999 |   153K  (2)| 00:30:47 |
|   1 |  NESTED LOOPS      |      |    99 |  9999 |   153K  (2)| 00:30:47 |
|   2 |   TABLE ACCESS FULL| T1   |    99 |   396 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| T    |     1 |    97 |  1554   (2)| 00:00:19 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("T"."OBJECT_ID"="T1"."OBJECT_ID")


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
     552537  consistent gets
          0  physical reads
          0  redo size
       4556  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         99  rows processed

SQL> select /*+ use_merge(t t1) */ t.* from t,t1 where t.object_id=t1.object_id;

99 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1822342538

------------------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |    99 |  9999 |       | 10312   (1)| 00:02:04 |
|   1 |  MERGE JOIN         |      |    99 |  9999 |       | 10312   (1)| 00:02:04 |
|   2 |   SORT JOIN         |      |    99 |   396 |       |     4  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| T1   |    99 |   396 |       |     3   (0)| 00:00:01 |
|*  4 |   SORT JOIN         |      |   393K|    36M|   100M| 10308   (1)| 00:02:04 |
|   5 |    TABLE ACCESS FULL| T    |   393K|    36M|       |  1556   (2)| 00:00:19 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("T"."OBJECT_ID"="T1"."OBJECT_ID")
       filter("T"."OBJECT_ID"="T1"."OBJECT_ID")


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       5585  consistent gets
          0  physical reads
          0  redo size
       4556  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
         99  rows processed

SQL>


=====================================

merge join (两大表, 两大表连接字段都有索引, 并对最终结果集作排序)


SQL> drop table t purge;

Table dropped.

SQL> drop table t1 purge;

Table dropped.

SQL> create table t as select * from dba_objects;

Table created.

SQL> create table t1 as select * from dba_objects;

Table created.

SQL> create index idx_t_objid on t(object_id);

Index created.

SQL> create index idx_t1_objid on t1(object_id);

Index created.

SQL> exec dbms_stats.gather_table_stats(user,'t',cascade=>true);

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats(user,'t1',cascade=>true);

PL/SQL procedure successfully completed.

SQL> set autotrace traceonly
SQL> select t.* from t,t1 where t.object_id=t1.object_id and t.object_id<100 order by t.object_id;

97 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2064909832

---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |    11 |  1133 |     8  (13)| 00:00:01 |
|   1 |  MERGE JOIN                  |              |    11 |  1133 |     8  (13)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T            |    12 |  1164 |     4   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IDX_T_OBJID  |    12 |       |     3   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |              |    12 |    72 |     4  (25)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | IDX_T1_OBJID |    12 |    72 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("T"."OBJECT_ID"<100)
   4 - access("T"."OBJECT_ID"="T1"."OBJECT_ID")
       filter("T"."OBJECT_ID"="T1"."OBJECT_ID")
   5 - access("T1"."OBJECT_ID"<100)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         22  consistent gets
          2  physical reads
          0  redo size
       4508  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         97  rows processed

SQL> select /*+ use_nl(t t1) */ t.* from t,t1 where t.object_id=t1.object_id and t.object_id<100 order by t.object_id;

97 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1376088001

---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |    11 |  1133 |    17   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |              |    11 |  1133 |    17   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T            |    12 |  1164 |     4   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IDX_T_OBJID  |    12 |       |     3   (0)| 00:00:01 |
|*  4 |   INDEX RANGE SCAN           | IDX_T1_OBJID |     1 |     6 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("T"."OBJECT_ID"<100)
   4 - access("T"."OBJECT_ID"="T1"."OBJECT_ID")
       filter("T1"."OBJECT_ID"<100)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         45  consistent gets
          0  physical reads
          0  redo size
       4508  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         97  rows processed

SQL> select /*+ use_hash(t t1) */ t.* from t,t1 where t.object_id=t1.object_id and t.object_id<100 order by t.object_id;

97 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2830098221

----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |    11 |  1133 |     9  (23)| 00:00:01 |
|   1 |  SORT ORDER BY                |              |    11 |  1133 |     9  (23)| 00:00:01 |
|*  2 |   HASH JOIN                   |              |    11 |  1133 |     8  (13)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T            |    12 |  1164 |     4   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | IDX_T_OBJID  |    12 |       |     3   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN           | IDX_T1_OBJID |    12 |    72 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T"."OBJECT_ID"="T1"."OBJECT_ID")
   4 - access("T"."OBJECT_ID"<100)
   5 - access("T1"."OBJECT_ID"<100)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
       4508  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         97  rows processed

SQL>

=====================================

总结: NL适合一大表join一小表,并且以快速返回优先的情况;HASH适合一大表join一小表,也适合两大表或两小表,并且以吞吐量为优先的情况;MERGE适合已做排序的情况.


--EOF--


3.通过append hint来插入数据，演示它和普通插入数据的性能比较。 

SQL> create table t as select * from dba_objects;

Table created.

SQL> insert into t select * from t;

393491 rows created.

SQL> /

786982 rows created.

SQL> /

1573964 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from t;

  COUNT(*)
----------
   3147928

SQL> create table t1 as select * from t where 1=2;

Table created.

SQL> create table t2 as select * from t where 1=2;

Table created.

SQL> set timing on
SQL> set autotrace on
SQL> insert into t1 select * from t;

3147928 rows created.

Elapsed: 00:02:02.97

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

---------------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |      |    25M|  5078M| 13461  (11)| 00:02:42 |
|   1 |  LOAD TABLE CONVENTIONAL | T1   |       |       |            |          |
|   2 |   TABLE ACCESS FULL      | T    |    25M|  5078M| 13461  (11)| 00:02:42 |
---------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
     244694  recursive calls
     651915  db block gets
     487870  consistent gets
      44070  physical reads
  370873068  redo size
        556  bytes sent via SQL*Net to client
        474  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
    3147928  rows processed

SQL> insert /*+ append */ into t2 select * from t;

3147928 rows created.

Elapsed: 00:00:15.35

Execution Plan
----------------------------------------------------------
ERROR:
ORA-12838: cannot read/modify an object after modifying it in parallel


SP2-0612: Error generating AUTOTRACE EXPLAIN report

Statistics
----------------------------------------------------------
     248361  recursive calls
     278429  db block gets
     382410  consistent gets
      43884  physical reads
    5829716  redo size
        544  bytes sent via SQL*Net to client
        488  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
    3147928  rows processed

SQL>


--EOF--


4.验证Oracle在没有使用hint DRIVING_SITE时，是否会将远程的数据拉到本地执行。 

SQL> create database link tm12.rmt connect to apps identified by apps using '(DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=prcsgidb1.us.oracle.com)(PORT=1561)) (CONNECT_DATA= (SID=TM12)))';

Database link created.

SQL> set autotrace traceonly;
SQL> select * from dept d, emp@tm12.rmt e where d.deptno=e.deptno;

14 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 122474654

-------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  1307 |   149K|     8  (13)| 00:00:01 |        |      |
|*  1 |  HASH JOIN         |      |  1307 |   149K|     8  (13)| 00:00:01 |        |      |
|   2 |   TABLE ACCESS FULL| DEPT |     4 |   120 |     3   (0)| 00:00:01 |        |      |
|   3 |   REMOTE           | EMP  |  1307 |   111K|     4   (0)| 00:00:01 |   TM12 | R->S |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("D"."DEPTNO"="E"."DEPTNO")

Remote SQL Information (identified by operation id):
----------------------------------------------------

   3 - SELECT "EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO" FROM
       "EMP" "E" (accessing 'TM12.RMT' )


Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
       1973  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         14  rows processed

SQL> select /*+ driving_site(e) */ * from dept d, emp@tm12.rmt e where d.deptno=e.deptno;

14 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2911175915

-----------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT REMOTE|      |  1526 |   174K|     9  (12)| 00:00:01 |        |      |
|*  1 |  HASH JOIN             |      |  1526 |   174K|     9  (12)| 00:00:01 |        |      |
|   2 |   TABLE ACCESS FULL    | EMP  |    15 |  1305 |     6   (0)| 00:00:01 |   TM12 |      |
|   3 |   REMOTE               | DEPT |   327 |  9810 |     2   (0)| 00:00:01 |      ! | R->S |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("A2"."DEPTNO"="A1"."DEPTNO")

Remote SQL Information (identified by operation id):
----------------------------------------------------

   3 - SELECT "DEPTNO","DNAME","LOC" FROM "DEPT" "A2" (accessing '!' )


Note
-----
   - fully remote statement
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
         19  recursive calls
          0  db block gets
         16  consistent gets
          0  physical reads
          0  redo size
       1770  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         14  rows processed

SQL>


driving_site即把其他表的数据发送到driving_site指定的db,并在该db上进行连接和执行查询等操作.

参考链接:

DRIVING_SITE Hint

http://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements006.htm#BABEGIJC


--EOF--


5.用cardinality hint来模拟表中的数据，写一条SQL语句并给出它的执行计划。

SQL> drop table t purge;

Table dropped.

SQL> create table t(id int);

Table created.

SQL> select * from t;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    13 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T    |     1 |    13 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
         24  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        237  bytes sent via SQL*Net to client
        339  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL> select /*+ cardinality(t 10000) */ * from t;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 10000 |   126K|     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T    | 10000 |   126K|     2   (0)| 00:00:01 |
--------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        237  bytes sent via SQL*Net to client
        339  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL>


--EOF--

t0nsha
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
Oracle PL/SQL高级编程（第五弹：性能优化：使用DBMS_PROFILER包）
艺术就是爆炸的专栏
08-13 1248
对于已经存在的代码，找出影响性能的问题点至关重要，Oracle提供的DBMS_PROFILER包可以方便地发现其瓶颈所在。 安装DBMS_PROFILER包 在使用DBMS_PROFILER之前，要以管理员身份进入数据库系统进行安装。 1. 使用管理员身份登录，使用DESC命令判断DBMS_PROFILER包是否存在：DESC DBMS_PROFILER; 2. 如果DESC命令提示DBM...
oracle create table select 语法,create table as select * from dba_objects为什么会有新建表的信息 | 信春哥，系统稳，闭眼上线不回滚...
weixin_42107165的博客
04-05 1925
上周五技术分享，nulldb(张中靖)兄提出了为什么在create table xx as select * from dba_objects时，新建的xx表会有新建的xx表的信息，也就是比如在操作前，dba_objects视图有10000条记录，在xx表创建完成时，xx表和dba_objects视图中都是10001条记录，在xx表中可以查询到xx表的信息，这在某种程度上违反了ORACLE的一致性...
oracle 谓词推入失效,Oracle查询转换之连接谓词推入
weixin_28699741的博客
04-05 204
连接谓词推入(Join Predicate Pushdown)是优化器处理带视图的目标SQL的一种优化手段，它是指虽然优化器会把该SQL中视图的定义SQL语句当作一个独立单元来单独执行，但此时优化器会把原本处于该视图外部查询中和该视图之间的连接条件推入到该视图的定义SQL语句内部，这样是为了能使用上该视图内部相关基表上的索引，进而能走出基于索引的嵌套循环连接。连接谓词推入所带来的基于索引的嵌套循...
oracle的hints,Oracle使用的hints调整机制
weixin_39574287的博客
04-08 74
10g数据库介绍：可以使用更多新的optimizer hints来控制优化行为。现在让我们快速解析一下这些强大的新hints:spread_min_analysis使用这一hint，你可以忽略一些关于如详细的关系依赖图分析等电子表格的编译时间优化规则。其他的一些优化，如创建过滤以有选择性的定位电子表格访问结构并限制修订规则等，得到了继续使用。由于在规则数非常大的情况下，电子表格分析会很长。这一提示...
关于加快INSERT语句执行速度和 HINT /*+ append*/及nologging的使用
u010586151的博客
01-23 2851
（非归档模式下）创建表T01： SQL> create table t01 as select * from dba_objects where 1=2; Table created. （非归档模式下）查看当前redo大小： SQL> select value 2 from vmystat,vmystat,vstatname 3 where vmystat.statistic#=vmy
<<Oracle数据库性能优化艺术（第五期）>> 第12周 10053事件
t0nsha's Blog
02-16 1283
1.验证全表扫描的成本计算公式，贴出执行计划和计算公式。 SQL> select * from v$version where rownum=1;BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Re
Oracle 数据库表性能优化
weixin_41645817的博客
04-15 1986
简单介绍一下实用的oracle 表优化技巧，包括数据库索引，表分区，统计信息，表收缩，并行查询，表重建几个部分的内容
Oracle SQL性能优化
weixin_36837739的博客
10-26 3662
oracle 性能优化
Oracle 数据库查询优化
最新发布
云满笔记
09-28 2970
Oracle 数据库查询优化
Oracle数据库表连接笔记.doc
06-03
总的来说，Oracle数据库的表连接涉及到复杂的性能优化和策略选择。理解各种连接方法的特性和适用场景，结合统计信息和索引使用，是提升数据库查询效率的关键。在实际操作中，应定期更新统计信息，监控性能，并在必要...
ORACLE_SQL性能优化(这个很全的).zip
07-29
Oracle SQL性能优化是一个复杂而深入的主题，涉及到数据库的多个层面，包括SQL语句设计、索引策略、表结构优化、数据库参数调整、查询计划分析等。以下是对这一主题的详细阐述： 1. **SQL语句设计**：优化SQL语句是...
Oracle数据库Sql性能调优
01-22
1.1 选用适合的ORACLE优化器 4 1.2 基础表的选择 4 1.3 访问TABLE的方式 5 1.4 共享SQL语句 5 1.5 执行路径. 5 1.6 选择最有效率的表名顺序(只在基于规则的优化器中有效) 6 1.7 WHERE子句中的连接顺序 6 1.8 SELECT...
oracle性能优化
05-24
为了优化Oracle数据库的性能，以下是一些建议： 1. 使用CBO而非RBO，因为CBO更先进，能够根据实际数据分布和系统资源状况做出决策。 2. 保持统计信息的最新，运行`DBMS_STATS.GATHER_TABLE_STATS`和`DBMS_STATS....
Oracle数据库实验操作
07-26
第五部分数据库的备份和恢复 174 Exp导出和imp导入 174 实验87：交互模式导出和导入数据 174 实验88：命令行模式导出和导入数据 175 实验89：参数文件模式导出和导入数据 175 实验90：导出和导入表的操作 176 实验91...
<<Oracle数据库性能优化艺术（第五期）>> 第13周 性能视图和性能参数
t0nsha's Blog
02-16 7236
1.设置memory_target参数，并通过 v$memory_target_advice分析数据库的最佳内存大小。SQL> show parameter memory_targetNAME                     TYPE     VALUE------------------------------------ ----------- --------------------
Oracle SQL Developer (Ver 1.5.5) 怎么识别 tnsnames.ora 里面配置的连接 ？
t0nsha's Blog
05-12 4668
要让 Oracle SQL Developer (Ver 1.5.5) 识别 tnsnames.ora 里面配置的连接只需两步：1.  设置TNS_ADMIN环境变量指向tnsnames.ora文件所在目录C:\>set TNS_ADMIN TNS_ADMIN=D:\instantclient_11_22. 在 Oracle SQL Developer 里面新建连接，连接类型选TNS，即可在网络别
<< Oracle高可用>>部分书面作业 - 第五课 RAC-故障定位和处理
t0nsha's Blog
10-13 4298
1.模拟两个节点内联网不通，观察RAC会出现什么现象？给出故障定位的整个过程。 现象：id小的节点会被驱逐出集群。故障定位过程如下：正常情況下：[root@rac3 ~]# crs_stat -tName           Type           Target    State     Host        ----------------------------------------
<<Oracle数据库性能优化艺术（第五期）>> 第14周 性能报告分析（AWR，ASH）
t0nsha's Blog
02-16 3684
1.分析群共享中发布的awr报告分析_作业.zip中的awr报告，贴出你认为能够支持自己观点的AWR报告中相应的部分，并给出分析说明，最后给出AWR的分析结论。==============================================================awr报告来自两个instance的rac,基于Solaris的11gR2,各具有32核心cpu(支持超线程)和12
oracle_sql性能优化.doc
05-07
1. 选用适合的Oracle优化器 - RULE（基于规则）：这种优化器根据预定义的规则来决定执行计划，例如，如果查询包含特定的操作符，如`IN`或`=`, Oracle会按照预定的策略执行。 - COST（基于成本）：CBO（Cost-Based ...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值