课程第25/26次（2018-08-12星期日上下午）
数据泵：SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
$ expdp system/oracle_4U directory=dira dumpfile=dira:'dira_%U.dmp',dirb:'dirb_%U.dmp' filesize=100Mparallel=4full=y
Export: Release 12.1.0.2.0 - Production on Sun Aug 12 09:43:35 2018
Copyright (c) 1982, 2014, Oracle and/or its affiliates.All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_FULL_01":system/******** directory=dira dumpfile=dira:dira_%U.dmp,dirb:dirb_%U.dmp filesize=100M parallel=4 full=y
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 509.0 MB
. . exported "WMSYS"."WM$CONSTRAINTS_TABLE[ DISCUZ_CODE_0 ]quot; 0 KB 0 rows
. . exported "WMSYS"."WM$LOCKROWS_INFO[ DISCUZ_CODE_0 ]quot; 0 KB 0 rows
. . exported "WMSYS"."WM$UDTRIG_INFO[ DISCUZ_CODE_0 ]quot; 0 KB 0 rows
. . exported "SYS"."FGA_LOG$FOR_EXPORT" 0 KB 0 rows
. . exported "SYSTEM"."SCHEDULER_JOB_ARGS" 0 KB 0 rows
. . exported "LBACSYS"."OLS$AUDIT_ACTIONS" 5.757 KB 8 rows
. . exported "LBACSYS"."OLS$DIP_EVENTS" 5.539 KB 2 rows
. . exported "LBACSYS"."OLS$INSTALLATIONS" 6.960 KB 2 rows
. . exported "LBACSYS"."OLS$PROPS" 6.234 KB 5 rows
. . exported "SYS"."DAM_CLEANUP_EVENTS[ DISCUZ_CODE_0 ]quot; 0 KB 0 rows
. . exported "SYS"."DAM_CLEANUP_JOBS[ DISCUZ_CODE_0 ]quot; 0 KB 0 rows
. . exported "SYS"."DAM_CONFIG_PARAM[ DISCUZ_CODE_0 ]quot; 6.531 KB 14 rows
. . exported "SYS"."TSDP_ASSOCIATION[ DISCUZ_CODE_0 ]quot; 0 KB 0 rows
. . exported "SYS"."TSDP_CONDITION[ DISCUZ_CODE_0 ]quot; 0 KB 0 rows
. . exported "SYS"."TSDP_FEATURE_POLICY[ DISCUZ_CODE_0 ]quot; 0 KB 0 rows
Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
. . exported "SYS"."TSDP_PARAMETER[ DISCUZ_CODE_0 ]quot; 5.953 KB 1 rows
. . exported "SYS"."TSDP_POLICY[ DISCUZ_CODE_0 ]quot; 5.921 KB 1 rows
. . exported "SYS"."TSDP_PROTECTION[ DISCUZ_CODE_0 ]quot; 0 KB 0 rows
. . exported "SYS"."TSDP_SENSITIVE_DATA[ DISCUZ_CODE_0 ]quot; 0 KB 0 rows
. . exported "SYS"."TSDP_SENSITIVE_TYPE[ DISCUZ_CODE_0 ]quot; 0 KB 0 rows
. . exported "SYS"."TSDP_SOURCE[ DISCUZ_CODE_0 ]quot; 0 KB 0 rows
. . exported "SYS"."TSDP_SUBPOL[ DISCUZ_CODE_0 ]quot; 6.328 KB 1 rows
. . exported "SYSTEM"."REDO_DB" 25.59 KB 1 rows
. . exported "SYSTEM"."REDO_LOG" 0 KB 0 rows
Processing object type DATABASE_EXPORT/TABLESPACE
. . exported "WMSYS"."WM$BATCH_COMPRESSIBLE_TABLES[ DISCUZ_CODE_0 ]quot; 0 KB 0 rows
. . exported "WMSYS"."WM$CONS_COLUMNS[ DISCUZ_CODE_0 ]quot; 0 KB 0 rows
. . exported "WMSYS"."WM$ENV_VARS[ DISCUZ_CODE_0 ]quot; 6.015 KB 3 rows
Processing object type DATABASE_EXPORT/PASSWORD_VERIFY_FUNCTION
. . exported "WMSYS"."WM$EVENTS_INFO[ DISCUZ_CODE_0 ]quot; 5.812 KB 12 rows
Processing object type DATABASE_EXPORT/PROFILE
. . exported "WMSYS"."WM$HINT_TABLE[ DISCUZ_CODE_0 ]quot; 9.453 KB 75 rows
. . exported "WMSYS"."WM$MODIFIED_TABLES[ DISCUZ_CODE_0 ]quot; 0 KB 0 rows
. . exported "WMSYS"."WM$MP_GRAPH_WORKSPACES_TABLE[ DISCUZ_CODE_0 ]quot; 0 KB 0 rows
. . exported "WMSYS"."WM$MP_PARENT_WORKSPACES_TABLE[ DISCUZ_CODE_0 ]quot; 0 KB 0 rows
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/SCHEMA/USER
. . exported "WMSYS"."WM$NESTED_COLUMNS_TABLE[ DISCUZ_CODE_0 ]quot; 0 KB 0 rows
. . exported "WMSYS"."WM$NEXTVER_TABLE[ DISCUZ_CODE_0 ]quot; 6.375 KB 1 rows
. . exported "WMSYS"."WM$REMOVED_WORKSPACES_TABLE[ DISCUZ_CODE_0 ]quot; 0 KB 0 rows
Processing object type DATABASE_EXPORT/ROLE
. . exported "WMSYS"."WM$RESOLVE_WORKSPACES_TABLE[ DISCUZ_CODE_0 ]quot; 0 KB 0 rows
Processing object type DATABASE_EXPORT/RADM_FPTM
. . exported "WMSYS"."WM$RIC_LOCKING_TABLE[ DISCUZ_CODE_0 ]quot; 0 KB 0 rows
. . exported "WMSYS"."WM$RIC_TABLE[ DISCUZ_CODE_0 ]quot; 0 KB 0 rows
. . exported "WMSYS"."WM$RIC_TRIGGERS_TABLE[ DISCUZ_CODE_0 ]quot; 0 KB 0 rows
. . exported "WMSYS"."WM$UDTRIG_DISPATCH_PROCS[ DISCUZ_CODE_0 ]quot; 0 KB 0 rows
. . exported "WMSYS"."WM$VERSION_HIERARCHY_TABLE[ DISCUZ_CODE_0 ]quot; 5.984 KB 1 rows
. . exported "WMSYS"."WM$VERSION_TABLE[ DISCUZ_CODE_0 ]quot; 0 KB 0 rows
. . exported "WMSYS"."WM$VT_ERRORS_TABLE[ DISCUZ_CODE_0 ]quot; 0 KB 0 rows
. . exported "WMSYS"."WM$WORKSPACES_TABLE[ DISCUZ_CODE_0 ]quot; 12.10 KB 1 rows
. . exported "WMSYS"."WM$WORKSPACE_PRIV_TABLE[ DISCUZ_CODE_0 ]quot; 7.054 KB 10 rows
. . exported "WMSYS"."WM$WORKSPACE_SAVEPOINTS_TABLE[ DISCUZ_CODE_0 ]quot; 0 KB 0 rows
. . exported "LBACSYS"."OLS$AUDIT" 0 KB 0 rows
. . exported "LBACSYS"."OLS$COMPARTMENTS" 0 KB 0 rows
. . exported "LBACSYS"."OLS$DIP_DEBUG" 0 KB 0 rows
. . exported "LBACSYS"."OLS$GROUPS" 0 KB 0 rows
. . exported "LBACSYS"."OLS$LAB" 0 KB 0 rows
. . exported "LBACSYS"."OLS$LEVELS" 0 KB 0 rows
. . exported "LBACSYS"."OLS$POL" 0 KB 0 rows
. . exported "LBACSYS"."OLS$POLICY_ADMIN" 0 KB 0 rows
. . exported "LBACSYS"."OLS$POLS" 0 KB 0 rows
. . exported "LBACSYS"."OLS$POLT" 0 KB 0 rows
. . exported "LBACSYS"."OLS$PROFILE" 0 KB 0 rows
. . exported "LBACSYS"."OLS$PROFILES" 0 KB 0 rows
. . exported "LBACSYS"."OLS$PROG" 0 KB 0 rows
. . exported "LBACSYS"."OLS$SESSINFO" 0 KB 0 rows
. . exported "LBACSYS"."OLS$USER" 0 KB 0 rows
. . exported "LBACSYS"."OLS$USER_COMPARTMENTS" 0 KB 0 rows
. . exported "LBACSYS"."OLS$USER_GROUPS" 0 KB 0 rows
. . exported "LBACSYS"."OLS$USER_LEVELS" 0 KB 0 rows
ORA-31693: Table data object "SYS"."AUD[ DISCUZ_CODE_0 ]quot; failed to load/unload and is being skipped due to error:
ORA-00376: file 8 cannot be read at this time
ORA-01111: name for data file 8 is unknown - rename to correct file
ORA-01110: data file 8: '/u01/app/oracle/product/12.1.0/dbhome_2/dbs/MISSING00008'
. . exported "SYS"."AUD[ DISCUZ_CODE_0 ]quot; 0 KB 0 rows
Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
Processing object type DATABASE_EXPORT/SCHEMA/ON_USER_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA
Processing object type DATABASE_EXPORT/RESOURCE_COST
. . exported "HR"."T_BIG" 41.99 MB326513 rows
Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK
Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE
. . exported "PM"."ONLINE_MEDIA" 7.855 MB 9 rows
. . exported "SH"."CUSTOMERS" 10.27 MB 55500 rows
. . exported "HR"."T05207_B" 0 KB 0 rows
. . exported "SH"."COSTS":"COSTS_Q1_1998" 139.6 KB 4411 rows
. . exported "SH"."COSTS":"COSTS_Q1_1999" 183.7 KB 5884 rows
. . exported "SH"."COSTS":"COSTS_Q1_2000" 120.7 KB 3772 rows
. . exported "SH"."COSTS":"COSTS_Q1_2001" 228.0 KB 7328 rows
. . exported "SH"."COSTS":"COSTS_Q2_1998" 79.68 KB 2397 rows
. . exported "SH"."COSTS":"COSTS_Q2_1999" 132.7 KB 4179 rows
. . exported "SH"."COSTS":"COSTS_Q2_2000" 119.1 KB 3715 rows
. . exported "SH"."COSTS":"COSTS_Q2_2001" 184.7 KB 5882 rows
. . exported "SH"."COSTS":"COSTS_Q3_1998" 131.3 KB 4129 rows
. . exported "SH"."COSTS":"COSTS_Q3_1999" 137.5 KB 4336 rows
. . exported "SH"."COSTS":"COSTS_Q3_2000" 151.6 KB 4798 rows
. . exported "SH"."COSTS":"COSTS_Q3_2001" 234.6 KB 7545 rows
. . exported "SH"."COSTS":"COSTS_Q4_1998" 144.8 KB 4577 rows
. . exported "SH"."COSTS":"COSTS_Q4_1999" 159.2 KB 5060 rows
Export> stop_job
Are you sure you wish to stop this job (/no): yes
$ expdp system/oracle_4U attach=SYS_EXPORT_FULL_01
Export: Release 12.1.0.2.0 - Production on Sun Aug 12 09:46:42 2018
Copyright (c) 1982, 2014, Oracle and/or its affiliates.All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Job: SYS_EXPORT_FULL_01
Owner: SYSTEM
Operation: EXPORT
Creator Privs: TRUE
GUID: 73341AEAF7710573E0535A00A8C0BDFB
Start Time: Sunday, 12 August, 2018 9:46:44
Mode: FULL
Instance: orcl
Max Parallelism: 4
Timezone: +00:00
Timezone version: 18
Endianness: LITTLE
NLS character set: AL32UTF8
NLS NCHAR character set: AL16UTF16
EXPORT Job Parameters:
Parameter Name Parameter Value:
CLIENT_COMMAND system/******** directory=dira dumpfile=dira:dira_%U.dmp,dirb:dirb_%U.dmp filesize=100M parallel=4 full=y
State: IDLING
Bytes Processed: 92,746,792
Percent Done: 67
Current Parallelism: 4
Job Error Count: 0
Dump File: /home/oracle/dira/dira_%u.dmp
size: 104,857,600
Dump File: /home/oracle/dira/dira_01.dmp
size: 104,857,600
bytes written: 92,852,224
Dump File: /home/oracle/dirb/dirb_01.dmp
size: 104,857,600
bytes written: 4,096
Dump File: /home/oracle/dirb/dirb_%u.dmp
size: 104,857,600
Dump File: /home/oracle/dira/dira_02.dmp
size: 104,857,600
bytes written: 12,288
Worker 1 Status:
Instance ID: 1
Instance name: orcl
Host name: station90.example.com
Process Name: DW00
State: UNDEFINED
Object Schema: HR
Object Name: SCHEDULER_TEST_SEQ
Object Type: DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE
Completed Objects: 7
Worker Parallelism: 1
Worker 2 Status:
Instance ID: 1
Instance name: orcl
Host name: station90.example.com
Process Name: DW01
State: UNDEFINED
Object Schema: ORDDATA
Object Name: ORDDCM_DOCS
Object Type: DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
Completed Objects: 2
Total Objects: 64
Worker Parallelism: 1
Worker 3 Status:
Instance ID: 1
Instance name: orcl
Host name: station90.example.com
Process Name: DW02
State: UNDEFINED
Worker 4 Status:
Instance ID: 1
Instance name: orcl
Host name: station90.example.com
Process Name: DW03
State: UNDEFINED
Object Schema: SYSTEM
Object Name: SCHEDULER_PROGRAM_ARGS
Object Type: DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Completed Objects: 3
Total Objects: 10
Worker Parallelism: 1
Worker 5 Status:
Instance ID: 1
Instance name: orcl
Host name: station90.example.com
Process Name: DW04
State: UNDEFINED
Object Schema: OE
Object Name: ORDERS_SEQ
Object Type: DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE
Completed Objects: 7
Worker Parallelism: 1
Export> status
Job: SYS_EXPORT_FULL_01
Operation: EXPORT
Mode: FULL
State: IDLING
Bytes Processed: 92,746,792
Percent Done: 67
Current Parallelism: 4
Job Error Count: 0
Dump File: /home/oracle/dira/dira_%u.dmp
size: 104,857,600
Dump File: /home/oracle/dira/dira_01.dmp
size: 104,857,600
bytes written: 92,852,224
Dump File: /home/oracle/dirb/dirb_01.dmp
size: 104,857,600
bytes written: 4,096
Dump File: /home/oracle/dirb/dirb_%u.dmp
size: 104,857,600
Dump File: /home/oracle/dira/dira_02.dmp
size: 104,857,600
bytes written: 12,288
Worker 1 Status:
Instance ID: 1
Instance name: orcl
Host name: station90.example.com
Process Name: DW00
State: UNDEFINED
Object Schema: HR
Object Name: SCHEDULER_TEST_SEQ
Object Type: DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE
Completed Objects: 7
Worker Parallelism: 1
Worker 2 Status:
Instance ID: 1
Instance name: orcl
Host name: station90.example.com
Process Name: DW01
State: UNDEFINED
Object Schema: ORDDATA
Object Name: ORDDCM_DOCS
Object Type: DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
Completed Objects: 2
Total Objects: 64
Worker Parallelism: 1
Worker 3 Status:
Instance ID: 1
Instance name: orcl
Host name: station90.example.com
Process Name: DW02
State: UNDEFINED
Worker 4 Status:
Instance ID: 1
Instance name: orcl
Host name: station90.example.com
Process Name: DW03
State: UNDEFINED
Object Schema: SYSTEM
Object Name: SCHEDULER_PROGRAM_ARGS
Object Type: DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Completed Objects: 3
Total Objects: 10
Worker Parallelism: 1
Worker 5 Status:
Instance ID: 1
Instance name: orcl
Host name: station90.example.com
Process Name: DW04
State: UNDEFINED
Object Schema: OE
Object Name: ORDERS_SEQ
Object Type: DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE
Completed Objects: 7
Worker Parallelism: 1
Export> start_job
Export>
TDE的表变成外部表时，要：
CREATE TABLE emp_ext (
first_name, last_name, empID,
salary ENCRYPT IDENTIFIED BY "xIcf3T9u" )
ORGANIZATION EXTERNAL
( TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY "D_DIR"
LOCATION('emp_ext.dat') )
REJECT LIMIT UNLIMITED
as select * from employees;
selects.SQL_TEXT,
s.SQL_FULLTEXT,
s.IS_BIND_SENSITIVE,
s.IS_BIND_AWARE
from v_$sql s
where s.SQL_FULLTEXTlike 'update%t04209_uname%';
SQL_TEXTSQL_FULLTEXTIS_BIND_SENSITIVEIS_BIND_AWARE
1update hr.t04209_uname set uvalue=32825 where uname='a32824'<CLOB>NN
2update hr.t04209_uname set uvalue=34492 where uname='a34491'<CLOB>NN
3update hr.t04209_uname set uvalue=32736 where uname='a32735'<CLOB>NN
4update hr.t04209_uname set uvalue=32281 where uname='a32280'<CLOB>NN
5update hr.t04209_uname set uvalue=34716 where uname='a34715'<CLOB>NN
6update hr.t04209_uname set uvalue=33362 where uname='a33361'<CLOB>NN
7update hr.t04209_uname set uvalue=34650 where uname='a34649'<CLOB>NN
......
selectsa.SQL_TEXT,
sa.SQL_FULLTEXT,
sa.IS_BIND_SENSITIVE,
sa.IS_BIND_AWARE
from v_$sqlarea sa
where sa.SQL_FULLTEXT like 'update%t04209_uname%';
SQL_TEXTSQL_FULLTEXTIS_BIND_SENSITIVEIS_BIND_AWARE
1update hr.t04209_uname set uvalue=41856 where uname='a41855'<CLOB>NN
2update hr.t04209_uname set uvalue=39357 where uname='a39356'<CLOB>NN
3update hr.t04209_uname set uvalue=39205 where uname='a39204'<CLOB>NN
4update hr.t04209_uname set uvalue=40822 where uname='a40821'<CLOB>NN
......
alter session set cursor_sahring=similar;上面两个查询语句的输出分别是：
SQL_TEXTSQL_FULLTEXTIS_BIND_SENSITIVEIS_BIND_AWAREEXECUTIONS
1update hr.t04209_uname set uvalue=:"SYS_B_0" where uname=:"SYS_B_1"<CLOB>NN1
2update hr.t04209_uname set uvalue=:"SYS_B_0" where uname=:"SYS_B_1"<CLOB>NN1
3update hr.t04209_uname set uvalue=:"SYS_B_0" where uname=:"SYS_B_1"<CLOB>NN1
4update hr.t04209_uname set uvalue=:"SYS_B_0" where uname=:"SYS_B_1"<CLOB>NN1
5update hr.t04209_uname set uvalue=:"SYS_B_0" where uname=:"SYS_B_1"<CLOB>NN1
......
selectsa.SQL_TEXT,
sa.SQL_FULLTEXT,
sa.IS_BIND_SENSITIVE,
sa.IS_BIND_AWARE,
sa.EXECUTIONS
from v_$sqlarea sa
where sa.SQL_FULLTEXT like 'update%t04209_uname%'
and sa.EXECUTIONS > 10 ;
SQL_TEXTSQL_FULLTEXTIS_BIND_SENSITIVEIS_BIND_AWAREEXECUTIONS
1update hr.t04209_uname set uvalue=:"SYS_B_0" where uname=:"SYS_B_1"<CLOB>NN7312
把cursor_sharing改成force后，v$sql也只剩下一行（与v$sqlarea输出一样）。
---------------------------------------下面把表倾斜化并加上一个索引
SQL> update hr.t04209_unameset uname='a1' where uname < 'a99900';
99891 rows updated.
SQL> commit;
Commit complete.
SQL> selectcount(*) from hr.t04209_uname where uname ='a1';
COUNT(*)
----------
99891
1 row selected.
SQL> selectcount(*) from hr.t04209_uname where uname <>'a1';
COUNT(*)
----------
109
1 row selected.
SQL> create index i04209_uname on t04209_uname(uname ) ;
Index created.
SQL> conn / assysdba
Connected.
SQL> shutdown immediate
selects.SQL_TEXT,
s.SQL_FULLTEXT,
s.IS_BIND_SENSITIVE,
s.IS_BIND_AWARE,
s.EXECUTIONS
from v_$sql s
where s.SQL_FULLTEXTlike 'update%t04209_uname%SYS_%'
and s.IS_BIND_SENSITIVE='Y'
;
SQL_TEXTSQL_FULLTEXTIS_BIND_SENSITIVEIS_BIND_AWAREEXECUTIONS
1update hr.t04209_uname set uvalue=:"SYS_B_0" where uname=:"SYS_B_1"<CLOB>YN1
2update hr.t04209_uname set uvalue=:"SYS_B_0" where uname=:"SYS_B_1"<CLOB>YN1
3update hr.t04209_uname set uvalue=:"SYS_B_0" where uname=:"SYS_B_1"<CLOB>YN1
selectsa.SQL_TEXT,
sa.SQL_FULLTEXT,
sa.IS_BIND_SENSITIVE,
sa.IS_BIND_AWARE,
sa.EXECUTIONS
from v_$sqlarea sa
where sa.SQL_FULLTEXT like 'update%t04209_uname%'
and sa.EXECUTIONS > 10 ;
SQL_TEXTSQL_FULLTEXTIS_BIND_SENSITIVEIS_BIND_AWAREEXECUTIONS
1update hr.t04209_uname set uvalue=:"SYS_B_0" where uname=:"SYS_B_1"<CLOB>YN6774
select* from V$SQL_CS_HISTOGRAM where sql_id='dh73w3ss300hp';
ADDRESSHASH_VALUESQL_IDCHILD_NUMBERBUCKET_IDCOUNT
1335E0BB8808452629dh73w3ss300hp000
2335E0BB8808452629dh73w3ss300hp010
3335E0BB8808452629dh73w3ss300hp020
select distinct plan_hash_valuefrom v$sql_planwhere sql_id='dh73w3ss300hp';同一个语句，会有两个不同的计划。其中一个是全表扫，一个是索引。
-------------------------------
结果级高速缓存，要注意1. maxsize有没有为0。 2.即使mode为force，所有会话仍旧可以bypass。
SQL> alter system set result_cache_mode=force;
System altered.
Elapsed: 00:00:00.05
SQL> @query2
SQL> select count(*)
2from cachejfv c1,cachejfv c2,cachejfv c3,cachejfv c4,cachejfv c5
3where c1.c='b' and c2.c='b' and c3.c='b' and c4.c='b' and c5.c='b';
COUNT(*)
----------
1
Elapsed: 00:00:00.00
SQL>
SQL> @query1
SQL> select /*+ result_cache */ count(*)
2from cachejfv c1,cachejfv c2,cachejfv c3,cachejfv c4,cachejfv c5
3where c1.c='b' and c2.c='b' and c3.c='b' and c4.c='b' and c5.c='b';
COUNT(*)
----------
1
Elapsed: 00:00:00.02
SQL>
SQL>exec DBMS_RESULT_CACHE.BYPASS(bypass_mode=>true);
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
SQL> @query1
SQL> select /*+ result_cache */ count(*)
2from cachejfv c1,cachejfv c2,cachejfv c3,cachejfv c4,cachejfv c5
3where c1.c='b' and c2.c='b' and c3.c='b' and c4.c='b' and c5.c='b';
COUNT(*)
----------
1
Elapsed: 00:00:00.63
SQL>
SQL> @query2
SQL> select count(*)
2from cachejfv c1,cachejfv c2,cachejfv c3,cachejfv c4,cachejfv c5
3where c1.c='b' and c2.c='b' and c3.c='b' and c4.c='b' and c5.c='b';
COUNT(*)
----------
1
Elapsed: 00:00:00.64
SQL>
SQL>exec DBMS_RESULT_CACHE.BYPASS(bypass_mode=>false);
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
SQL>
v$result_cache_objects
dbms_result_cache.memory_report
---------------------------------------------------
页:
[1]