课程第38/39次（2018-09-14星期五，2018-09-21星期五）

botang

Skillset 4
Section2：

Set the resultcache size to 15 MB.：

1. alter system set RESULT_CACHE_MAX_SIZE=15m;

复制代码

1. SQL> alter system set result_cache_mode=force;
   
2. 
   
3. System altered.
   
4. 
   
5. SQL> select  department_id , avg(salary)   
   
6.   2   from hr.employees
   
7.   3   group by department_id;
   
8. 
   
9. DEPARTMENT_ID AVG(SALARY)
   
10. ------------- -----------
    
11.           100             8607
    
12.            30             4157
    
13.                      7007
    
14.            90  24673.6667
    
15.            20             9507
    
16.            70            10007
    
17.           110            10157
    
18.            50  3482.55556
    
19.            80  8962.88235
    
20.            40             6507
    
21.            60             5767
    
22. 
    
23. DEPARTMENT_ID AVG(SALARY)
    
24. ------------- -----------
    
25.            10             4407
    
26. 
    
27. 12 rows selected.
    
28. 
    
29. SQL> /
    
30. 
    
31. DEPARTMENT_ID AVG(SALARY)
    
32. ------------- -----------
    
33.           100             8607
    
34.            30             4157
    
35.                      7007
    
36.            90  24673.6667
    
37.            20             9507
    
38.            70            10007
    
39.           110            10157
    
40.            50  3482.55556
    
41.            80  8962.88235
    
42.            40             6507
    
43.            60             5767
    
44. 
    
45. DEPARTMENT_ID AVG(SALARY)
    
46. ------------- -----------
    
47.            10             4407
    
48. 
    
49. 12 rows selected.
    
50. 
    
51. SQL> exec dbms_result_cache.memory_report
    
52. R e s u l t   C a c h e   M e m o r y        R e p o r t
    
53. [Parameters]
    
54. Block Size            = 1K bytes
    
55. Maximum Cache Size  = 4512K bytes (4512 blocks)
    
56. Maximum Result Size = 225K bytes (225 blocks)
    
57. [Memory]
    
58. Total Memory = 107404 bytes [0.021% of the Shared Pool]
    
59. ... Fixed Memory = 5132 bytes [0.001% of the Shared Pool]
    
60. ... Dynamic Memory = 102272 bytes [0.020% of the Shared Pool]
    
61. ....... Overhead = 69504 bytes
    
62. ....... Cache Memory = 32K bytes (32 blocks)
    
63. ........... Unused Memory = 21 blocks
    
64. ........... Used Memory = 11 blocks
    
65. ............... Dependencies = 3 blocks (3 count)
    
66. ............... Results = 8 blocks
    
67. ................... SQL     = 8 blocks (8 count)
    
68. 
    
69. PL/SQL procedure successfully completed.
    
70. 
    
71. SQL> show parameter db_name
    
72. 
    
73. NAME                                     TYPE         VALUE
    
74. ------------------------------------ ----------- ------------------------------
    
75. db_name                              string         EMREP
    
76. SQL>      
    

复制代码

验证：

1. select  * from V$RESULT_CACHE_objects;
   

复制代码

In the query, the columns CUST_CITY, CUST_STATE_PROVINCE, and COUNTRY_ID  are used together as the predicates.：

1. select  dbms_stats.create_extended_stats(ownname => 'SH',
   
2. tabname => 'CUSTOMERS', extension => '(CUST_CITY, CUST_STATE_PROVINCE,COUNTRY_ID)')
   
3.   from dual;

复制代码

	OWNER	TABLE_NAME	EXTENSION_NAME	EXTENSION	CREATOR	DROPPABLE
1	SH	CUSTOMERS	SYS_STUMZ$C3AIHLPBROI#SKA58H_N	<CLOB>	USER	YES

1. select dbms_stats.create_extended_stats('SH','CUSTOMERS','(CUST_CITY,CUST_STATE_PROVINCE,COUNTRY_ID)') from dual;

复制代码

关于直方图：

1. select  tcs.num_distinct, tcs.low_value, tcs.high_value,
   
2.       tcs.num_buckets ,tcs.HISTOGRAM
   
3.    from dba_tab_col_statistics tcs
   
4.    where tcs.owner='SH' and tcs.table_name='CUSTOMERS'
   
5.    and  tcs.num_buckets > 1;

复制代码

	NUM_DISTINCT	LOW_VALUE	HIGH_VALUE	NUM_BUCKETS	HISTOGRAM
1	620	C3060B29	C3061A20	254	HEIGHT BALANCED
2	145	C3061A22	C3061C48	145	FREQUENCY
3	19	C3061C46	C3061C5C	19	FREQUENCY

1.    select   h.column_name, count(* ) from dba_histograms h
   
2.     where h.owner='SH' and  h.table_name='CUSTOMERS'
   
3.     group by h.column_name
   
4.     having count(*)  > 2;

复制代码

	COLUMN_NAME	COUNT(*)
1	COUNTRY_ID	19
2	CUST_STATE_PROVINCE_ID	145
3	CUST_CITY_ID	212

1. begin
   
2.   dbms_stats.gather_table_stats(ownname => 'SH',
   
3.   tabname => 'CUSTOMERS',
   
4.   method_opt =>
   
5.    'for all columns size auto  for columns (CUST_CITY, CUST_STATE_PROVINCE,COUNTRY_ID) size 254');
   
6. end;   

复制代码

2.3                                                        Gather statistics for the SALES table in SH schema. Ensure that the cursors pertaining to the object in the cache are never invalidated.:

1. select  * from dba_tab_stat_prefs;
   
2. 
   
3. begin
   
4.    dbms_stats.set_table_prefs(ownname => 'SH',
   
5.    tabname => 'SALES',
   
6.    pname => 'PUBLISH',
   
7.    pvalue => 'FALSE');
   
8. end;  
   
9. 
   
10. begin
    
11.    dbms_stats.set_table_prefs(ownname => 'SH',
    
12.    tabname => 'SALES',
    
13.    pname => 'NO_INVALIDATE',
    
14.    pvalue => 'TRUE');
    
15. end;  

复制代码

10g 全局哈希索引考题：

1. 4.11 create an index on USER_ID column on table transaction in sh schema. The column will be inserted with values generated by a sequence named transaction_seq in sh schema. During bulk insertion into the table, the index should be suitable by causing as low shared pool contention as possible.

复制代码

11g  全局哈希索引考题：

1. There are performance problems during inserts into the PRODUCT_INFORMATION_PART table in the SH schema in the PROD1 database. A sequence is used to generate the values for the PRODUCT_ID column in this table . Create an index, PROD_IDX on the PRODUCT_ID column which overcomes the performance problems during inserts into the table. Range scans are also frequently done on the table.

复制代码

:

1. create table sh.PRODUCT_INFORMATION_PART  
   
2. as select  * from sh.products;
   
3. 
   
4. CREATE INDEX PROD_IDX on sh.PRODUCT_INFORMATION_PART (PROD_ID,prod_name)
   
5. GLOBAL PARTITION BY HASH (PROD_ID) PARTITIONS 4 ;

复制代码

11g标准2天会考（11g升级考试不会考）：

1. 4.3 Create two new tables named STUDENTS and ATTENDEES in the OLTP_USER schema. This STUDENTS table will contain three columns. STUD_ID will be a number and primary key. FNAME and LNAME will be the other two columns and may vary in length with a maximum of 20 characters. ATTENDEES will be an intersection table in a many-to-many relationship between the STUDENTS and CLASS tables also in the OLTP_USER schema. The ATTENDEES table will contain the primary keys of each of the other tables as its primary key. Create the ATTENDEES table so what the primary key index and the table itself are the same object.

复制代码

1. create table SH.STUDENTS
   
2.    (STUD_ID  number constraint  pk_students  primary key  ,
   
3.      FNAME  varchar2(20),
   
4.      LNAME  varchar2(20) );
   
5.      
   
6. create table sh.CLASS
   
7. (  class_id  number  constraint pk_class primary key,
   
8.     class_name varchar2(20)
   
9.   );
   
10. 
    
11.    
    
12. create table SH.ATTENDEES
    
13. (STUD_ID  number   constraint  fk_stud_id   references  SH.STUDENTS  ,
    
14.   class_id  number   constraint  fk_class_id  references sh.class ,
    
15.   constraint pk_ATTENDEES  primary key (STUD_ID,class_id ) )
    
16. organization  index ;

复制代码

Section4：
只要传3个参数：

1. impdp system/oracle dumpfile=dump_dir:appsstg.dmp full=y
   
2. 
   
3. begin
   
4.         dbms_sqltune.unpack_stgtab_sqlset(
   
5.                 replace=>true,
   
6.                 staging_table_name=>'STS_PS_TAB',
   
7.                 staging_schema_owner=>'APPS'
   
8.         );
   
9. end;
   
10. /
    

复制代码

1. conn apps/Apps1234
   
2. @?/rdbms/admin/utlxplan.sql

复制代码

考试的时候不用：

1. dbms_sqltune.create_tuning_task
   
2. dbms_sqltune.execute_tuning_task('sql_tuning_test');
   
3. USER_ADVISOR_TASKS
   
4. dbms_sqltune.report_tning_task
   
5. 
   
6. --SQL Performance Analyzer
   
7. alter system set optimizer_features_enable='10.2.0.1';
   
8. var tname varchar2(30);
   
9. exec :tname:= dbms_sqlpa.create_analysis_task(sqlset_name => 'STS_PS', task_name => 'MYSPA');
   
10. exec dbms_sqlpa.execute_analysis_task(task_name => :tname,execution_type => 'TEST EXECUTE', execution_name => 'before');
    
11. select dbms_sqlpa.report_analysis_task(task_name => :tname,type=>'text', section=>'summary') FROM dual;
    
12. alter system set optimizer_features_enable='11.1.0.7';
    
13. exec dbms_sqlpa.execute_analysis_task(task_name => :tname,execution_type => 'TEST EXECUTE', execution_name => 'after');
    
14. select dbms_sqlpa.report_analysis_task(task_name => :tname,type=>'text', section=>'summary') FROM dual;
    
15. exec dbms_sqlpa.execute_analysis_task(task_name => :tname,execution_type => 'COMPARE PERFORMANCE');
    
16. select dbms_sqlpa.report_analysis_task(task_name => :tname,type=>'text', section=>'summary') FROM dual;
    
17. 
    
18. EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name =>:tname,execution_type => 'compare performance', execution_params =>
    
19. dbms_advisor.arglist( 'execution_name1', 'before','execution_name2', 'after', 'comparison_metric', ‘elapse_time'));

复制代码

性特性第一册P431：DBA_ADVISOR_SQLPLANS: See the plans encountered during test-execute

1. insert into plan_table 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,OTHER,DISTRIBUTION,CPU_COST,IO_COST,TEMP_SPACE,ACCESS_PREDICATES,FILTER_PREDICATES,PROJECTION,TIME,QBLOCK_NAME,OTHER_XML
   
2. from DBA_ADVISOR_SQLPLANS where task_name='MYSPA' and execution_name='SECOND_SQL_TRIAL' and sql_id in('',''

复制代码

1. [root@station90 桌面]# vim 1.sql
   
2. [root@station90 桌面]# cut -f 1 1.sql
   
3. STATEMENT_ID
   
4. PLAN_ID
   
5. TIMESTAMP
   
6. REMARKS
   
7. OPERATION
   
8. OPTIONS
   
9. OBJECT_NODE
   
10. OBJECT_OWNER
    
11. OBJECT_NAME
    
12. OBJECT_ALIAS
    
13. OBJECT_INSTANCE
    
14. OBJECT_TYPE
    
15. OPTIMIZER
    
16. SEARCH_COLUMNS
    
17. ID
    
18. PARENT_ID
    
19. DEPTH
    
20. POSITION
    
21. COST
    
22. CARDINALITY
    
23. BYTES
    
24. OTHER_TAG
    
25. PARTITION_START
    
26. PARTITION_STOP
    
27. PARTITION_ID
    
28. OTHER
    
29. DISTRIBUTION
    
30. CPU_COST
    
31. IO_COST
    
32. TEMP_SPACE
    
33. ACCESS_PREDICATES
    
34. FILTER_PREDICATES
    
35. PROJECTION
    
36. TIME
    
37. QBLOCK_NAME
    
38. OTHER_XML
    
39. 
    
40. 
    
41. [root@station90 桌面]# cut -f 1 1.sql | tr '\n' ','
    
42. 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, OTHER, DISTRIBUTION, CPU_COST, IO_COST, TEMP_SPACE, ACCESS_PREDICATES, FILTER_PREDICATES, PROJECTION, TIME, QBLOCK_NAME, OTHER_XML,,,[root@station90 桌面]#
    

复制代码

重演：

1.   wrc  scott/tiger@myserver REPLAYDIR=.
   

复制代码

1. 5.1
   
2. alter system set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=true;
   
3. 
   
4. @4_5_1.sql
   
5. 
   
6. select sql_handle, plan_name, enabled, accepted, fixed from dba_sql_plan_baselines;
   
7. 
   
8. declare
   
9. test binary_integer;
   
10. begin
    
11. test := dbms_spm.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id=>'')
    
12. end;
    
13. /
    
14. 
    
15. variable ret clob
    
16. declare
    
17. begin
    
18.         :ret :=
    
19.                 dbms_spm.evolve_sql_plan_baseline(
    
20.                         'SYS_SQL_f6cb7f742ef93547',
    
21.                         'SYS_SQL_PLAN_2ef9354754bc8843'
    
22.                 );
    
23. end;
    
24. /
    
25. print ret
    
26. 
    
27. 
    
28. 
    
29. select sql_handle, plan_name, enabled, accepted, fixed from dba_sql_plan_baselines;
    

复制代码

Section 6: Capturing Performance Statistics

1. BEGIN
   
2.     DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE (
   
3.                    start_time => '2010-07-31 18:00:00',
   
4.                    end_time => '2010-08-01 18:00:00',
   
5.                    baseline_name => 'WEEKEND',
   
6.                    template_name => 'WEEKEND');
   
7. END;

复制代码

---

1. select  * from DBA_HIST_BASELINE_TEMPLATE;

复制代码

DBID

TEMPLATE_ID

TEMPLATE_NAME

TEMPLATE_TYPE

BASELINE_NAME_PREFIX

START_TIME

END_TIME

DAY_OF_WEEK

HOUR_IN_DAY

DURATION

EXPIRATION

REPEAT_INTERVAL

LAST_GENERATED

1

2095419667

1

WEEKEND

SINGLE

WEEKEND

9/22/2018 6:00:00 PM

9/23/2018 6:00:00 PM