设为首页收藏本站

Botang唐波's Oracle Station

查看: 1357|回复: 0

课程第83/84/85次(2017-12-15星期五,2017-12-17星期天上下午)课程结束

[复制链接]

745

主题

1118

帖子

8120

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
8120
发表于 2017-12-16 16:25:30 | 显示全部楼层 |阅读模式
  1. select  * from dba_advisor_sqlplans;
  2. select  count(*)  from hr.plan_table;

  3. insert into hr.plan_table (statement_id,plan_id,timestamp,remarks,OPERATION,OPTIONS,OBJECT_NODE,OBJECT_OWNER,OBJECT_NAME,OBJECT_ALIAS,OBJECT_INSTANCE,OBJECT_TYPE,OPTIMIZER,SEARCH_COLUMNS,ID,PARENT_ID,DEPTH,POSITION,COST,CARDINALITY,BYTES,OTHER_TAG,PARTITION_START,PARTITION_STOP,PARTITION_ID,DISTRIBUTION,CPU_COST,IO_COST,TEMP_SPACE,ACCESS_PREDICATES,FILTER_PREDICATES,PROJECTION,TIME,QBLOCK_NAME,OTHER_XML
  4. )
  5. select statement_id,plan_id,timestamp,remarks,OPERATION,OPTIONS,OBJECT_NODE,OBJECT_OWNER,OBJECT_NAME,OBJECT_ALIAS,OBJECT_INSTANCE,OBJECT_TYPE,OPTIMIZER,SEARCH_COLUMNS,ID,PARENT_ID,DEPTH,POSITION,COST,CARDINALITY,BYTES,OTHER_TAG,PARTITION_START,PARTITION_STOP,PARTITION_ID,DISTRIBUTION,CPU_COST,IO_COST,TEMP_SPACE,ACCESS_PREDICATES,FILTER_PREDICATES,PROJECTION,TIME,QBLOCK_NAME,OTHER_XML

  6. from DBA_ADVISOR_SQLPLANS ;

  7. commit;
复制代码
  1. select  * from plan_table1;

  2. truncate table plan_table1;

  3. select  id,   lpad(' ', depth)|| operation  operation,
  4. options, object_name, optimizer ,cost
  5. from hr.plan_table1 p
  6. start with id=0
  7. connect by   prior id=  parent_id  
  8. ;

  9. select  id,   lpad(' ', depth)|| operation  operation,
  10. options, object_name, optimizer ,cost,cardinality,time,qblock_name
  11. from v$sql_plan
  12. where  hash_value='2776053402'
  13.   and address='00000001ED882F38'
  14. start with id=0
  15. connect by (  prior id=  parent_id  
  16. and  prior hash_value=hash_value
  17. and prior child_number=child_number
  18. )
  19. ;

  20. select s.HASH_VALUE, s.ADDRESS , s.SQL_FULLTEXT   from v_$sql s
  21.   where  s.SQL_FULLTEXT like '%employees%departments%';


复制代码
  1. select s.HASH_VALUE, s.ADDRESS , s.SQL_FULLTEXT,s.SQL_ID   from v_$sql s
  2.   where  s.SQL_FULLTEXT like '%employees%departments%';
  3.   
  4.   select   * from table(dbms_xplan.display_cursor('cfz0cdukrfdnu',
  5.   format => 'ADVANCED +ALIAS'));
复制代码
  1. select  * from v$sql_plan
  2. where sql_id='99qa3zyarxvms';

  3. select   * from table(dbms_xplan.display_awr('99qa3zyarxvms',
  4.   format => 'ADVANCED +ALIAS'));
复制代码
  1. SQL> ed
  2. Wrote file afiedt.buf

  3.   1  SELECT /*+ USE_HASH(e,d)  leading(e)  */
  4.   2  d.deptno,d.dname,e.empno,e.ename
  5.   3  FROM
  6.   4  emp e, dept d
  7.   5* WHERE e.deptno(+)=d.deptno
  8. SQL> /

  9.     DEPTNO DNAME               EMPNO ENAME
  10. ---------- -------------- ---------- ----------
  11.         20 RESEARCH                7369 SMITH
  12.         30 SALES                7499 ALLEN
  13.         30 SALES                7521 WARD
  14.         20 RESEARCH                7566 JONES
  15.         30 SALES                7654 MARTIN
  16.         30 SALES                7698 BLAKE
  17.         10 ACCOUNTING                7782 CLARK
  18.         20 RESEARCH                7788 SCOTT
  19.         10 ACCOUNTING                7839 KING
  20.         30 SALES                7844 TURNER
  21.         20 RESEARCH                7876 ADAMS

  22.     DEPTNO DNAME               EMPNO ENAME
  23. ---------- -------------- ---------- ----------
  24.         30 SALES                7900 JAMES
  25.         20 RESEARCH                7902 FORD
  26.         10 ACCOUNTING                7934 MILLER
  27.         40 OPERATIONS

  28. 15 rows selected.


  29. Execution Plan
  30. ----------------------------------------------------------
  31. Plan hash value: 3713469723

  32. ---------------------------------------------------------------------------
  33. | Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time          |
  34. ---------------------------------------------------------------------------
  35. |   0 | SELECT STATEMENT   |          |    14 |   364 |        7  (15)| 00:00:01 |
  36. |*  1 |  HASH JOIN OUTER   |          |    14 |   364 |        7  (15)| 00:00:01 |
  37. |   2 |   TABLE ACCESS FULL| DEPT |        4 |    52 |        3   (0)| 00:00:01 |
  38. |   3 |   TABLE ACCESS FULL| EMP  |    14 |   182 |        3   (0)| 00:00:01 |
  39. ---------------------------------------------------------------------------

  40. Predicate Information (identified by operation id):
  41. ---------------------------------------------------

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


  43. Statistics
  44. ----------------------------------------------------------
  45.           8  recursive calls
  46.           0  db block gets
  47.          15  consistent gets
  48.           0  physical reads
  49.           0  redo size
  50.        1137  bytes sent via SQL*Net to client
  51.         520  bytes received via SQL*Net from client
  52.           2  SQL*Net roundtrips to/from client
  53.           0  sorts (memory)
  54.           0  sorts (disk)
  55.          15  rows processed

  56. SQL> ed
  57. Wrote file afiedt.buf

  58.   1  SELECT /*+ USE_HASH(e,d)  leading(e)  */
  59.   2  d.deptno,d.dname,e.empno,e.ename
  60.   3  FROM
  61.   4  emp e, dept d
  62.   5* WHERE e.deptno=d.deptno
  63. SQL> /

  64.     DEPTNO DNAME               EMPNO ENAME
  65. ---------- -------------- ---------- ----------
  66.         10 ACCOUNTING                7934 MILLER
  67.         10 ACCOUNTING                7839 KING
  68.         10 ACCOUNTING                7782 CLARK
  69.         20 RESEARCH                7902 FORD
  70.         20 RESEARCH                7876 ADAMS
  71.         20 RESEARCH                7788 SCOTT
  72.         20 RESEARCH                7566 JONES
  73.         20 RESEARCH                7369 SMITH
  74.         30 SALES                7900 JAMES
  75.         30 SALES                7844 TURNER
  76.         30 SALES                7698 BLAKE

  77.     DEPTNO DNAME               EMPNO ENAME
  78. ---------- -------------- ---------- ----------
  79.         30 SALES                7654 MARTIN
  80.         30 SALES                7521 WARD
  81.         30 SALES                7499 ALLEN

  82. 14 rows selected.


  83. Execution Plan
  84. ----------------------------------------------------------
  85. Plan hash value: 1123238657

  86. ---------------------------------------------------------------------------
  87. | Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time          |
  88. ---------------------------------------------------------------------------
  89. |   0 | SELECT STATEMENT   |          |    14 |   364 |        7  (15)| 00:00:01 |
  90. |*  1 |  HASH JOIN           |          |    14 |   364 |        7  (15)| 00:00:01 |
  91. |   2 |   TABLE ACCESS FULL| EMP  |    14 |   182 |        3   (0)| 00:00:01 |
  92. |   3 |   TABLE ACCESS FULL| DEPT |        4 |    52 |        3   (0)| 00:00:01 |
  93. ---------------------------------------------------------------------------

  94. Predicate Information (identified by operation id):
  95. ---------------------------------------------------

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


  97. Statistics
  98. ----------------------------------------------------------
  99.          10  recursive calls
  100.           0  db block gets
  101.          16  consistent gets
  102.           0  physical reads
  103.           0  redo size
  104.        1051  bytes sent via SQL*Net to client
  105.         520  bytes received via SQL*Net from client
  106.           2  SQL*Net roundtrips to/from client
  107.           0  sorts (memory)
  108.           0  sorts (disk)
  109.          14  rows processed

  110. SQL>
复制代码
  1. CREATE CLUSTER calls_cluster
  2. ( origin_number  NUMBER
  3. , call_timestamp NUMBER SORT
  4. , call_duration  NUMBER SORT)
  5. HASHKEYS 10000
  6. SINGLE TABLE HASH IS origin_number
  7. SIZE 50;
复制代码
  1. CREATE TABLE calls
  2. ( origin_number  NUMBER
  3. , call_timestamp NUMBER
  4. , call_duration  NUMBER
  5. , other_info     VARCHAR2(30))
  6. CLUSTER calls_cluster(
  7. origin_number,call_timestamp,call_duration
  8. );
复制代码


回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|手机版|Botang唐波's Oracle Station   

GMT+8, 2018-12-13 16:28 , Processed in 0.087541 second(s), 24 queries .

快速回复 返回顶部 返回列表