Bo's Oracle Station

查看: 5400|回复: 0

课程第38/39次(2018-09-14星期五,2018-09-21星期五)

[复制链接]

1005

主题

1469

帖子

1万

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
12012
发表于 2018-9-14 20:18:55 | 显示全部楼层 |阅读模式
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. System altered.

  3. SQL> select  department_id , avg(salary)   
  4.   2   from hr.employees
  5.   3   group by department_id;

  6. DEPARTMENT_ID AVG(SALARY)
  7. ------------- -----------
  8.           100             8607
  9.            30             4157
  10.                      7007
  11.            90  24673.6667
  12.            20             9507
  13.            70            10007
  14.           110            10157
  15.            50  3482.55556
  16.            80  8962.88235
  17.            40             6507
  18.            60             5767

  19. DEPARTMENT_ID AVG(SALARY)
  20. ------------- -----------
  21.            10             4407

  22. 12 rows selected.

  23. SQL> /

  24. DEPARTMENT_ID AVG(SALARY)
  25. ------------- -----------
  26.           100             8607
  27.            30             4157
  28.                      7007
  29.            90  24673.6667
  30.            20             9507
  31.            70            10007
  32.           110            10157
  33.            50  3482.55556
  34.            80  8962.88235
  35.            40             6507
  36.            60             5767

  37. DEPARTMENT_ID AVG(SALARY)
  38. ------------- -----------
  39.            10             4407

  40. 12 rows selected.

  41. SQL> exec dbms_result_cache.memory_report
  42. R e s u l t   C a c h e   M e m o r y        R e p o r t
  43. [Parameters]
  44. Block Size            = 1K bytes
  45. Maximum Cache Size  = 4512K bytes (4512 blocks)
  46. Maximum Result Size = 225K bytes (225 blocks)
  47. [Memory]
  48. Total Memory = 107404 bytes [0.021% of the Shared Pool]
  49. ... Fixed Memory = 5132 bytes [0.001% of the Shared Pool]
  50. ... Dynamic Memory = 102272 bytes [0.020% of the Shared Pool]
  51. ....... Overhead = 69504 bytes
  52. ....... Cache Memory = 32K bytes (32 blocks)
  53. ........... Unused Memory = 21 blocks
  54. ........... Used Memory = 11 blocks
  55. ............... Dependencies = 3 blocks (3 count)
  56. ............... Results = 8 blocks
  57. ................... SQL     = 8 blocks (8 count)

  58. PL/SQL procedure successfully completed.

  59. SQL> show parameter db_name

  60. NAME                                     TYPE         VALUE
  61. ------------------------------------ ----------- ------------------------------
  62. db_name                              string         EMREP
  63. 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;
复制代码
   OWNERTABLE_NAMEEXTENSION_NAMEEXTENSIONCREATORDROPPABLE
1SHCUSTOMERSSYS_STUMZ$C3AIHLPBROI#SKA58H_N<CLOB>USERYES


  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_DISTINCTLOW_VALUEHIGH_VALUENUM_BUCKETSHISTOGRAM
1620C3060B29C3061A20254HEIGHT BALANCED
2145C3061A22C3061C48145FREQUENCY
319C3061C46C3061C5C19FREQUENCY

  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_NAMECOUNT(*)
1COUNTRY_ID19
2CUST_STATE_PROVINCE_ID145
3CUST_CITY_ID212

  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. begin
  3.    dbms_stats.set_table_prefs(ownname => 'SH',
  4.    tabname => 'SALES',
  5.    pname => 'PUBLISH',
  6.    pvalue => 'FALSE');
  7. end;  

  8. begin
  9.    dbms_stats.set_table_prefs(ownname => 'SH',
  10.    tabname => 'SALES',
  11.    pname => 'NO_INVALIDATE',
  12.    pvalue => 'TRUE');
  13. 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. CREATE INDEX PROD_IDX on sh.PRODUCT_INFORMATION_PART (PROD_ID,prod_name)
  4. 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. create table SH.ATTENDEES
  12. (STUD_ID  number   constraint  fk_stud_id   references  SH.STUDENTS  ,
  13.   class_id  number   constraint  fk_class_id  references sh.class ,
  14.   constraint pk_ATTENDEES  primary key (STUD_ID,class_id ) )
  15. organization  index ;
复制代码

Section4:
只要传3个参数:
  1. impdp system/oracle dumpfile=dump_dir:appsstg.dmp full=y

  2. begin
  3.         dbms_sqltune.unpack_stgtab_sqlset(
  4.                 replace=>true,
  5.                 staging_table_name=>'STS_PS_TAB',
  6.                 staging_schema_owner=>'APPS'
  7.         );
  8. end;
  9. /
复制代码
  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. --SQL Performance Analyzer
  6. alter system set optimizer_features_enable='10.2.0.1';
  7. var tname varchar2(30);
  8. exec :tname:= dbms_sqlpa.create_analysis_task(sqlset_name => 'STS_PS', task_name => 'MYSPA');
  9. exec dbms_sqlpa.execute_analysis_task(task_name => :tname,execution_type => 'TEST EXECUTE', execution_name => 'before');
  10. select dbms_sqlpa.report_analysis_task(task_name => :tname,type=>'text', section=>'summary') FROM dual;
  11. alter system set optimizer_features_enable='11.1.0.7';
  12. exec dbms_sqlpa.execute_analysis_task(task_name => :tname,execution_type => 'TEST EXECUTE', execution_name => 'after');
  13. select dbms_sqlpa.report_analysis_task(task_name => :tname,type=>'text', section=>'summary') FROM dual;
  14. exec dbms_sqlpa.execute_analysis_task(task_name => :tname,execution_type => 'COMPARE PERFORMANCE');
  15. select dbms_sqlpa.report_analysis_task(task_name => :tname,type=>'text', section=>'summary') FROM dual;

  16. EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name =>:tname,execution_type => 'compare performance', execution_params =>
  17. dbms_advisor.arglist( 'execution_name1', 'before','execution_name2', 'after', 'comparison_metric', ‘elapse_time'));
复制代码
Screenshot.png

性特性第一册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. [root@station90 桌面]# cut -f 1 1.sql | tr '\n' ','
  40. 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=.
复制代码

a.png

  1. 5.1
  2. alter system set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=true;

  3. @4_5_1.sql

  4. select sql_handle, plan_name, enabled, accepted, fixed from dba_sql_plan_baselines;

  5. declare
  6. test binary_integer;
  7. begin
  8. test := dbms_spm.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id=>'')
  9. end;
  10. /

  11. variable ret clob
  12. declare
  13. begin
  14.         :ret :=
  15.                 dbms_spm.evolve_sql_plan_baseline(
  16.                         'SYS_SQL_f6cb7f742ef93547',
  17.                         'SYS_SQL_PLAN_2ef9354754bc8843'
  18.                 );
  19. end;
  20. /
  21. print ret



  22. 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;
复制代码
Screenshot-1.png

---
Screenshot-1.png

  1. select  * from DBA_HIST_BASELINE_TEMPLATE;
复制代码
   DBIDTEMPLATE_IDTEMPLATE_NAMETEMPLATE_TYPEBASELINE_NAME_PREFIXSTART_TIMEEND_TIMEDAY_OF_WEEKHOUR_IN_DAYDURATIONEXPIRATIONREPEAT_INTERVALLAST_GENERATED
120954196671WEEKENDSINGLEWEEKEND9/22/2018 6:00:00 PM9/23/2018 6:00:00 PM




回复

使用道具 举报

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

本版积分规则

QQ|手机版|Bo's Oracle Station   

GMT+8, 2024-3-29 23:49 , Processed in 0.038692 second(s), 27 queries .

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