设为首页收藏本站

Botang唐波's Oracle Station

查看: 27|回复: 0

课程第38次(2018-09-14星期五)

[复制链接]

719

主题

1081

帖子

7868

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
7868
发表于 6 天前 | 显示全部楼层 |阅读模式
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 ;
复制代码







回复

使用道具 举报

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

本版积分规则

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

GMT+8, 2018-9-20 12:45 , Processed in 0.192763 second(s), 24 queries .

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