专栏首页小麦苗的DB宝专栏【DB笔试面试610】在Oracle中,SPM的使用有哪些步骤?

【DB笔试面试610】在Oracle中,SPM的使用有哪些步骤?

题目部分

在Oracle中,SPM的使用有哪些步骤?

答案部分

取消自动捕获,也可以不取消自动捕捉:

show parameter baselines
ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=FALSE;

执行:

[ZHLHRSPMDB2:oracle]:/oracle>ORACLE_SID=dlhr
[ZHLHRSPMDB2:oracle]:/oracle>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu May 26 15:47:55 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SYS@dlhr> conn lhr/lhr
Connected.
LHR@dlhr> 
LHR@dlhr> 
LHR@dlhr> 
LHR@dlhr> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

LHR@dlhr> show parameter baselines

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean     TRUE
optimizer_use_sql_plan_baselines     boolean     TRUE

LHR@dlhr> ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=FALSE;

System altered.

--创建表并插入数据,脚本:

CREATE TABLE tb_spm_test_lhr (
 id           NUMBER,
  description  VARCHAR2(50)
);

DECLARE
  TYPE t_tab IS TABLE OF tb_spm_test_lhr%ROWTYPE;
  l_tab t_tab := t_TAB();
BEGIN
  FOR i IN 1 .. 10000 LOOP
    l_tab.extend;
    l_tab(l_tab.last).id := i;
   l_tab(l_tab.last).description := 'Description for ' || i;
  END LOOP;

  FORALL i IN l_tab.first .. l_tab.last
    INSERT INTO tb_spm_test_lhr VALUES l_tab(i);

  COMMIT;
 END;
 /


EXEC DBMS_STATS.gather_table_stats(USER, 'tb_spm_test_lhr', cascade=>TRUE);

set autot trace
SELECT description FROM   tb_spm_test_lhr WHERE  id = 100;

开始执行:

LHR@dlhr> CREATE TABLE tb_spm_test_lhr (
  2   id           NUMBER,
  3    description  VARCHAR2(50)
  4  );

Table created.

LHR@dlhr>  
LHR@dlhr> DECLARE
  2    TYPE t_tab IS TABLE OF tb_spm_test_lhr%ROWTYPE;
  3    l_tab t_tab := t_TAB();
  4  BEGIN
  5    FOR i IN 1 .. 10000 LOOP
  6      l_tab.extend;
  7      l_tab(l_tab.last).id := i;
  8     l_tab(l_tab.last).description := 'Description for ' || i;
  9    END LOOP;
 10    
 11    FORALL i IN l_tab.first .. l_tab.last
 12      INSERT INTO tb_spm_test_lhr VALUES l_tab(i);
 13    
 14    COMMIT;
 15   END;
 16   /

PL/SQL procedure successfully completed.


LHR@dlhr> set autot trace
LHR@dlhr> SELECT description FROM   tb_spm_test_lhr WHERE  id = 100;


Execution Plan
----------------------------------------------------------
Plan hash value: 2196561629

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

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

   1 - filter("ID"=100)

Note
-----
   - dynamic sampling used for this statement (level=2)


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

获取刚才查询的SQL_ID:

set autot off
col SQL_TEXT format a100
select distinct a.SQL_ID,a.SQL_TEXT from v$sql a 
WHERE a.SQL_TEXT like '%SELECT description FROM   tb_spm_test_lhr WHERE  id = 100%' 
and a.SQL_TEXT not like '%v$sql%' 
AND    sql_text NOT LIKE '%EXPLAIN%';

LHR@dlhr> set autot off
LHR@dlhr> col SQL_TEXT format a100
LHR@dlhr> select distinct a.SQL_ID,a.SQL_TEXT from v$sql a 
  2  WHERE a.SQL_TEXT like '%SELECT description FROM   tb_spm_test_lhr WHERE  id = 100%' 
  3  and a.SQL_TEXT not like '%v$sql%' 
  4  AND    sql_text NOT LIKE '%EXPLAIN%';

SQL_ID        SQL_TEXT
------------- ----------------------------------------------------------------------------------------------------
garkwg3yy2ram SELECT description FROM   tb_spm_test_lhr WHERE  id = 100

----使用SQL_ID 从cursor cache中手工捕获执行计划:
SET SERVEROUTPUT ON
DECLARE
 l_plans_loaded  PLS_INTEGER;
BEGIN
 l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(
   sql_id => '&sql_id');  
 DBMS_OUTPUT.put_line('Plans Loaded: ' || l_plans_loaded);
END;
/ 
-- --使用DBA_SQL_PLAN_BASELINES视图查看SPM 信息:
col sql_handle for a35
col plan_name for a35
set lin 300
SELECT SQL_HANDLE,plan_name,origin,enabled,accepted,fixed
FROM   dba_sql_plan_baselines
WHERE  sql_text LIKE '%tb_spm_test_lhr%'
AND    sql_text NOT LIKE'%dba_sql_plan_baselines%';

--刷新Share Pool,使下次SQL 执行时必须进行硬解析:
ALTER SYSTEM FLUSH SHARED_POOL;

LHR@dlhr> SET SERVEROUTPUT ON
LHR@dlhr> DECLARE
  2   l_plans_loaded  PLS_INTEGER;
  3  BEGIN
  4   l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(
  5     sql_id => '&sql_id');  
  6   DBMS_OUTPUT.put_line('Plans Loaded: ' || l_plans_loaded);
  7  END;
  8  / 
Enter value for sql_id: garkwg3yy2ram
old   5:    sql_id => '&sql_id');
new   5:    sql_id => 'garkwg3yy2ram');
Plans Loaded: 1

PL/SQL procedure successfully completed.


LHR@dlhr> col sql_handle for a35
LHR@dlhr> col plan_name for a35
LHR@dlhr> set lin 300
LHR@dlhr> SELECT sql_handle, plan_name,enabled, accepted
  2  FROM   dba_sql_plan_baselines
  3  WHERE  sql_text LIKE '%tb_spm_test_lhr%'
  4  AND    sql_text NOT LIKE'%dba_sql_plan_baselines%';

SQL_HANDLE                          PLAN_NAME                           ENA ACC
----------------------------------- ----------------------------------- --- ---
SQL_4f19d3cf57be7303                SQL_PLAN_4y6fmtxbvwws3184920d2      YES YES

LHR@dlhr> ALTER SYSTEM FLUSH SHARED_POOL;

System altered.

LHR@dlhr> set autot trace
SELECT description FROM   tb_spm_test_lhr WHERE  id = 100;
LHR@dlhr> 

Execution Plan
----------------------------------------------------------
Plan hash value: 2196561629

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

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

   1 - filter("ID"=100)

Note
-----
   - dynamic sampling used for this statement (level=2)
   - SQL plan baseline "SQL_PLAN_4y6fmtxbvwws3184920d2" used for this statement


Statistics
----------------------------------------------------------
        555  recursive calls
         16  db block gets
        667  consistent gets
          0  physical reads
       3056  redo size
        546  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         32  sorts (memory)
          0  sorts (disk)
          1  rows processed


--创建索引,收集统计信息,并查询相同的SQL:
CREATE INDEX spm_test_tab_idx ON tb_spm_test_lhr(id);
EXEC DBMS_STATS.gather_table_stats(USER,'tb_spm_test_lhr', cascade=>TRUE);

set autot trace
SELECT description FROM   tb_spm_test_lhr WHERE  id = 100;


LHR@dlhr> CREATE INDEX spm_test_tab_idx ON tb_spm_test_lhr(id);
Index created.

LHR@dlhr> EXEC DBMS_STATS.gather_table_stats(USER,'tb_spm_test_lhr', cascade=>TRUE);


PL/SQL procedure successfully completed.

LHR@dlhr> 
LHR@dlhr> 
LHR@dlhr> 
LHR@dlhr> set autot trace
LHR@dlhr> SELECT description FROM   tb_spm_test_lhr WHERE  id = 100;



Execution Plan
----------------------------------------------------------
Plan hash value: 2196561629

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

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

   1 - filter("ID"=100)

Note
-----
   - SQL plan baseline "SQL_PLAN_4y6fmtxbvwws3184920d2" used for this statement


Statistics
----------------------------------------------------------
        640  recursive calls
         39  db block gets
        493  consistent gets
          2  physical reads
      12268  redo size
        546  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         10  sorts (memory)
          0  sorts (disk)
          1  rows processed


--这里我们创建了索引,但是这里还是走的全表扫描,这里使用索引明显才是最优的方案。
--查看SPM 视图:
set autot off
col sql_handle for a35
col plan_name for a35
set lin 300
SELECT SQL_HANDLE,plan_name,origin,enabled,accepted,fixed
FROM   dba_sql_plan_baselines
WHERE  sql_text LIKE '%tb_spm_test_lhr%'
AND    sql_text NOT LIKE'%dba_sql_plan_baselines%';

LHR@dlhr> set autot off
LHR@dlhr> col sql_handle for a35
LHR@dlhr> col plan_name for a35
LHR@dlhr> set lin 300
LHR@dlhr> SELECT sql_handle, plan_name,enabled, accepted
  2  FROM   dba_sql_plan_baselines
  3  WHERE  sql_text LIKE '%tb_spm_test_lhr%'
  4  AND    sql_text NOT LIKE'%dba_sql_plan_baselines%';

SQL_HANDLE                          PLAN_NAME                           ENA ACC
----------------------------------- ----------------------------------- --- ---
SQL_4f19d3cf57be7303                SQL_PLAN_4y6fmtxbvwws3184920d2      YES YES
SQL_4f19d3cf57be7303                SQL_PLAN_4y6fmtxbvwws38b725570      YES NO

--通过BASELINES查询的结果,可以看到SQL产生了两条执行计划,但是最优的执行计划并没有被标记为ACCEPT,所以没有使用。
--下边我们演化执行计划:演化就是将cost低的执行计划标记为accept:
LHR@dlhr> SET LONG 10000
LHR@dlhr> SELECT DBMS_SPM.evolve_sql_plan_baseline(sql_handle => '&sql_handle') FROM dual;
Enter value for sql_handle: SQL_4f19d3cf57be7303
old   1: SELECT DBMS_SPM.evolve_sql_plan_baseline(sql_handle => '&sql_handle') FROM dual
new   1: SELECT DBMS_SPM.evolve_sql_plan_baseline(sql_handle => 'SQL_4f19d3cf57be7303') FROM dual

DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SQL_4F19D3CF57BE7303')
--------------------------------------------------------------------------------

-------------------------------------------------------------------------------
                        Evolve SQL Plan Baseline Report
-------------------------------------------------------------------------------

Inputs:
-------
  SQL_HANDLE = SQL_4f19d3cf57be7303
  PLAN_NAME  =
  TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
  VERIFY     = YES
  COMMIT     = YES

Plan: SQL_PLAN_4y6fmtxbvwws38b725570
------------------------------------
  Plan was verified: Time used .018 seconds.
  Plan passed performance criterion: 15 times better than baseline plan.
  Plan was changed to an accepted plan.

                            Baseline Plan      Test Plan       Stats Ratio
                            -------------      ---------       -----------
  Execution Status:              COMPLETE       COMPLETE
  Rows Processed:                       1              1
  Elapsed Time(ms):                  .308           .025             12.32
  CPU Time(ms):                      .164           .015             10.93
  Buffer Gets:                         45              3                15
  Physical Read Requests:               0              0
  Physical Write Requests:              0              0
  Physical Read Bytes:                  0              0
  Physical Write Bytes:                 0              0
  Executions:                           1              1

-------------------------------------------------------------------------------
                                 Report Summary
-------------------------------------------------------------------------------
Number of plans verified: 1
Number of plans accepted: 1


--再次查看DBA_SQL_PLAN_BASELINES视图:
set autot off
col sql_handle for a35
col plan_name for a35
set lin 300
SELECT SQL_HANDLE,plan_name,origin,enabled,accepted,fixed
FROM   dba_sql_plan_baselines
WHERE  sql_text LIKE '%tb_spm_test_lhr%'
AND    sql_text NOT LIKE'%dba_sql_plan_baselines%';

LHR@dlhr> set autot off
LHR@dlhr> col sql_handle for a35
LHR@dlhr> col plan_name for a35
LHR@dlhr> set lin 300
LHR@dlhr> SELECT sql_handle, plan_name,enabled, accepted
  2  FROM   dba_sql_plan_baselines
  3  WHERE  sql_text LIKE '%tb_spm_test_lhr%'
  4  AND    sql_text NOT LIKE'%dba_sql_plan_baselines%';

SQL_HANDLE                          PLAN_NAME                           ENA ACC
----------------------------------- ----------------------------------- --- ---
SQL_4f19d3cf57be7303                SQL_PLAN_4y6fmtxbvwws3184920d2      YES YES
SQL_4f19d3cf57be7303                SQL_PLAN_4y6fmtxbvwws38b725570      YES YES

--再次执行SQL:
set autot trace
SELECT description FROM   tb_spm_test_lhr WHERE  id = 100;

LHR@dlhr> set autot trace
LHR@dlhr> SELECT description FROM   tb_spm_test_lhr WHERE  id = 100;


Execution Plan
----------------------------------------------------------
Plan hash value: 2587945646

------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |     1 |    25 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TB_SPM_TEST_LHR  |     1 |    25 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | SPM_TEST_TAB_IDX |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

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

   2 - access("ID"=100)

Note
-----
   - SQL plan baseline "SQL_PLAN_4y6fmtxbvwws38b725570" used for this statement


Statistics
----------------------------------------------------------
         13  recursive calls
         14  db block gets
         18  consistent gets
          0  physical reads
       3048  redo size
        553  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

这次正确的使用了索引。因为只有标记为ENABLE和ACCEPT的plan才可以被使用。

下面示例将我们的第一个走全表扫描的执行计划标记为fixed。标记为fixed的执行计划会被优先使用。FIXED表示优化程序仅考虑标记为FIXED的计划,而不考虑其它计划。例如,如果有10个基线计划,其中的三个计划被标记为FIXED,则优化程序将仅使用这三个计划中的最佳计划,而忽略其它所有计划。如果某个SQL计划基线至少包含一个已启用的已修复计划,则该SQL计划基线就是FIXED的。如果在修复的SQL计划基线中添加了新计划,则在手动将这些新计划声明为FIXED之前,无法使用这些新计划。

set autot off
select * from table(dbms_xplan.display_sql_plan_baseline (sql_handle => '&sql_handle', format => 'basic'));

SET SERVEROUTPUT ON
DECLARE
 l_plans_altered  PLS_INTEGER;
BEGIN
 l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(
   sql_handle      => '&sql_handle',
   plan_name       => '&plan_name',
   attribute_name  => 'fixed',
   attribute_value => 'YES');

 DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered);
END;
/

LHR@dlhr> SET SERVEROUTPUT ON
LHR@dlhr> DECLARE
  2   l_plans_altered  PLS_INTEGER;
  3  BEGIN
  4   l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(
  5     sql_handle      => '&sql_handle',
  6     plan_name       => '&plan_name',
  7     attribute_name  => 'fixed',
  8     attribute_value => 'YES');
  9   
 10   DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered);
 11  END;
 12  /
Enter value for sql_handle: SQL_4f19d3cf57be7303
old   5:    sql_handle      => '&sql_handle',
new   5:    sql_handle      => 'SQL_4f19d3cf57be7303',
Enter value for plan_name: SQL_PLAN_4y6fmtxbvwws3184920d2
old   6:    plan_name       => '&plan_name',
new   6:    plan_name       => 'SQL_PLAN_4y6fmtxbvwws3184920d2',
Plans Altered: 1

PL/SQL procedure successfully completed.

--验证:
set autot off
col sql_handle for a35
col plan_name for a35
set lin 300
SELECT SQL_HANDLE,plan_name,origin,enabled,accepted,fixed
FROM   dba_sql_plan_baselines
WHERE  sql_text LIKE '%tb_spm_test_lhr%'
AND    sql_text NOT LIKE'%dba_sql_plan_baselines%';

LHR@dlhr> set autot off
LHR@dlhr> select * from table(dbms_xplan.display_sql_plan_baseline (sql_handle => '&sql_handle', format => 'basic'));
Enter value for sql_handle: SQL_4f19d3cf57be7303
old   1: select * from table(dbms_xplan.display_sql_plan_baseline (sql_handle => '&sql_handle', format => 'basic'))
new   1: select * from table(dbms_xplan.display_sql_plan_baseline (sql_handle => 'SQL_4f19d3cf57be7303', format => 'basic'))

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------

--------------------------------------------------------------------------------
SQL handle: SQL_4f19d3cf57be7303
SQL text: SELECT description FROM   tb_spm_test_lhr WHERE  id = 100
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_4y6fmtxbvwws3184920d2         Plan id: 407445714
Enabled: YES     Fixed: YES     Accepted: YES     Origin: MANUAL-LOAD
--------------------------------------------------------------------------------

Plan hash value: 2196561629

---------------------------------------------
| Id  | Operation         | Name            |
---------------------------------------------
|   0 | SELECT STATEMENT  |                 |
|   1 |  TABLE ACCESS FULL| TB_SPM_TEST_LHR |
---------------------------------------------

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_4y6fmtxbvwws38b725570         Plan id: 2339526000
Enabled: YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------

Plan hash value: 2587945646

--------------------------------------------------------
| Id  | Operation                   | Name             |
--------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |
|   1 |  TABLE ACCESS BY INDEX ROWID| TB_SPM_TEST_LHR  |
|   2 |   INDEX RANGE SCAN          | SPM_TEST_TAB_IDX |
--------------------------------------------------------

34 rows selected.

LHR@dlhr> set autot off
LHR@dlhr> col sql_handle for a35
LHR@dlhr> col plan_name for a35
LHR@dlhr> set lin 300
LHR@dlhr> SELECT SQL_HANDLE,plan_name,origin,enabled,accepted,fixed
  2  FROM   dba_sql_plan_baselines
  3  WHERE  sql_text LIKE '%tb_spm_test_lhr%'
  4  AND    sql_text NOT LIKE'%dba_sql_plan_baselines%';

SQL_HANDLE                          PLAN_NAME                           ORIGIN         ENA ACC FIX
----------------------------------- ----------------------------------- -------------- --- --- ---
SQL_4f19d3cf57be7303                SQL_PLAN_4y6fmtxbvwws3184920d2      MANUAL-LOAD    YES YES YES
SQL_4f19d3cf57be7303                SQL_PLAN_4y6fmtxbvwws38b725570      AUTO-CAPTURE   YES YES NO



--再次查看我们之前的SQL:
set autot trace
SELECT description FROM   tb_spm_test_lhr WHERE  id = 100;

LHR@dlhr> set autot trace
LHR@dlhr> SELECT description FROM   tb_spm_test_lhr WHERE  id = 100;


Execution Plan
----------------------------------------------------------
Plan hash value: 2196561629

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

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

   1 - filter("ID"=100)

Note
-----
   - SQL plan baseline "SQL_PLAN_4y6fmtxbvwws3184920d2" used for this statement


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

这里已经走了全表扫描,根据前边的示例,我们知道这里走索引会更优,但因为我们将走全表扫描的执行计划设置为fixed,所以优先使用这个执行计划。

& 说明:

有关固定执行计划的具体过程及更多的知识可以参考我的BLOG:http://blog.itpub.net/26736162/viewspace-2107604/

本文选自《Oracle程序员面试笔试宝典》,作者:李华荣。

本文分享自微信公众号 - DB宝(xiaomaimiaolhr),作者:小麦苗best

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2019-08-05

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 【DB笔试面试597】在Oracle中,获取执行计划有哪几种方法?

    AUTOTRACE是Oracle自带的客户端工具SQL*Plus的一个特性。启用AUTOTRACE后,SQL*Plus会自动收集执行过的SQL语句的执行计划、性...

    小麦苗DBA宝典
  • 【故障处理】ORA-31600和ORA-04063错误

    有朋友在执行drop操作时,报了如下的错误,经过查询mos文档和远程协助最后终于处理了,记录下。

    小麦苗DBA宝典
  • 【DB笔试面试670】在Oracle中,什么是SQL实时监控?

    在Oracle 11g中,V$SESSION视图增加了一些新的字段,这其中包括SQL_EXEC_START和SQL_EXEC_ID,这两个字段实际上代表了Ora...

    小麦苗DBA宝典
  • 【自然框架】QuickPagerSQL——专门生成分页用的SQL的类库

       分享一个生成分页用SQL的函数库   一般一提到分页,大家就会想到存储过程,而大多数情况都是在存储过程里面拼接SQL,我觉得与其在存储过程里面拼接,还不如...

    用户1174620
  • 联动设置

    用户1174620
  • 揭秘:深入解读针对苹果Mac电脑的ROM级恶意软件“雷击(Thunderstrike)”

    众所周知苹果Mac电脑很安全,但是随着恶意软件的发展,凭借现有Mac安全机制想要完全对抗恶意软件的感染可能会变得更加艰难。近期一种新型恶意软件的问世,使得黑客通...

    FB客服
  • SDN与NFV技术对运营商后续网络的影响

    随着云计算、大数据等新兴信息技术业务应用的规模落地,新业务应用对网络的需求越来越高,运营商的通信网络已进入快速变革时期。灵活性、易扩展和简单易用将成为运营商未来...

    SDNLAB
  • Confluence 6 删除一个空间 原

    删除一个空间将会完全删除空间和空间的所有内容,包括有关这个空间的所有日历,和链接到这个空间中的问题。只有具有空间管理员权限的用户才能够完全删除一个空间。

    HoneyMoose
  • python 连接oracle(cx_O

    使用python连接oracle数据库; 1.安装python3.0; 2.安装cx_Oracle模块; 3.安装ORACLE_CLIENT ; (ORACLE...

    py3study
  • SAP CRM WebClient UI里如何找到Assignment block和settype的映射关系

    SAP CRM WebClient UI 产品主数据里的assignment block,要么对应一个settype,要么对应一个relationship。

    Jerry Wang

扫码关注云+社区

领取腾讯云代金券