课程第38/39次（2018-09-14星期五，2018-09-21星期五）
Skillset 4Section2：
Set the resultcache size to 15 MB.：
alter system set RESULT_CACHE_MAX_SIZE=15m;
SQL> alter system set result_cache_mode=force;
System altered.
SQL> selectdepartment_id , avg(salary)
2 from hr.employees
3 group by department_id;
DEPARTMENT_ID AVG(SALARY)
------------- -----------
100 8607
30 4157
7007
9024673.6667
20 9507
70 10007
110 10157
503482.55556
808962.88235
40 6507
60 5767
DEPARTMENT_ID AVG(SALARY)
------------- -----------
10 4407
12 rows selected.
SQL> /
DEPARTMENT_ID AVG(SALARY)
------------- -----------
100 8607
30 4157
7007
9024673.6667
20 9507
70 10007
110 10157
503482.55556
808962.88235
40 6507
60 5767
DEPARTMENT_ID AVG(SALARY)
------------- -----------
10 4407
12 rows selected.
SQL> exec dbms_result_cache.memory_report
R e s u l t C a c h e M e m o r y R e p o r t
Block Size = 1K bytes
Maximum Cache Size= 4512K bytes (4512 blocks)
Maximum Result Size = 225K bytes (225 blocks)
Total Memory = 107404 bytes
... Fixed Memory = 5132 bytes
... Dynamic Memory = 102272 bytes
....... Overhead = 69504 bytes
....... Cache Memory = 32K bytes (32 blocks)
........... Unused Memory = 21 blocks
........... Used Memory = 11 blocks
............... Dependencies = 3 blocks (3 count)
............... Results = 8 blocks
................... SQL = 8 blocks (8 count)
PL/SQL procedure successfully completed.
SQL> show parameter db_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string EMREP
SQL>
验证：
select* from V$RESULT_CACHE_objects;
In the query, the columns CUST_CITY, CUST_STATE_PROVINCE, and COUNTRY_IDare used together as the predicates.：
selectdbms_stats.create_extended_stats(ownname => 'SH',
tabname => 'CUSTOMERS', extension => '(CUST_CITY, CUST_STATE_PROVINCE,COUNTRY_ID)')
from dual;
OWNERTABLE_NAMEEXTENSION_NAMEEXTENSIONCREATORDROPPABLE
1SHCUSTOMERSSYS_STUMZ$C3AIHLPBROI#SKA58H_N<CLOB>USERYES
select dbms_stats.create_extended_stats('SH','CUSTOMERS','(CUST_CITY,CUST_STATE_PROVINCE,COUNTRY_ID)') from dual;关于直方图：
selecttcs.num_distinct, tcs.low_value, tcs.high_value,
tcs.num_buckets ,tcs.HISTOGRAM
from dba_tab_col_statistics tcs
where tcs.owner='SH' and tcs.table_name='CUSTOMERS'
andtcs.num_buckets > 1;
NUM_DISTINCTLOW_VALUEHIGH_VALUENUM_BUCKETSHISTOGRAM
1620C3060B29C3061A20254HEIGHT BALANCED
2145C3061A22C3061C48145FREQUENCY
319C3061C46C3061C5C19FREQUENCY
select h.column_name, count(* ) from dba_histograms h
where h.owner='SH' andh.table_name='CUSTOMERS'
group by h.column_name
having count(*)> 2;
COLUMN_NAMECOUNT(*)
1COUNTRY_ID19
2CUST_STATE_PROVINCE_ID145
3CUST_CITY_ID212
begin
dbms_stats.gather_table_stats(ownname => 'SH',
tabname => 'CUSTOMERS',
method_opt =>
'for all columns size autofor columns (CUST_CITY, CUST_STATE_PROVINCE,COUNTRY_ID) size 254');
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.:
select* from dba_tab_stat_prefs;
begin
dbms_stats.set_table_prefs(ownname => 'SH',
tabname => 'SALES',
pname => 'PUBLISH',
pvalue => 'FALSE');
end;
begin
dbms_stats.set_table_prefs(ownname => 'SH',
tabname => 'SALES',
pname => 'NO_INVALIDATE',
pvalue => 'TRUE');
end;
10g 全局哈希索引考题：
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全局哈希索引考题：
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.
:
create table sh.PRODUCT_INFORMATION_PART
as select* from sh.products;
CREATE INDEX PROD_IDX on sh.PRODUCT_INFORMATION_PART (PROD_ID,prod_name)
GLOBAL PARTITION BY HASH (PROD_ID) PARTITIONS 4 ;
11g标准2天会考（11g升级考试不会考）：
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.
create table SH.STUDENTS
(STUD_IDnumber constraintpk_studentsprimary key,
FNAMEvarchar2(20),
LNAMEvarchar2(20) );
create table sh.CLASS
(class_idnumberconstraint pk_class primary key,
class_name varchar2(20)
);
create table SH.ATTENDEES
(STUD_IDnumber constraintfk_stud_id referencesSH.STUDENTS,
class_idnumber constraintfk_class_idreferences sh.class ,
constraint pk_ATTENDEESprimary key (STUD_ID,class_id ) )
organizationindex ;
Section4：
只要传3个参数：
impdp system/oracle dumpfile=dump_dir:appsstg.dmp full=y
begin
dbms_sqltune.unpack_stgtab_sqlset(
replace=>true,
staging_table_name=>'STS_PS_TAB',
staging_schema_owner=>'APPS'
);
end;
/
conn apps/Apps1234
@?/rdbms/admin/utlxplan.sql考试的时候不用：
dbms_sqltune.create_tuning_task
dbms_sqltune.execute_tuning_task('sql_tuning_test');
USER_ADVISOR_TASKS
dbms_sqltune.report_tning_task
--SQL Performance Analyzer
alter system set optimizer_features_enable='10.2.0.1';
var tname varchar2(30);
exec :tname:= dbms_sqlpa.create_analysis_task(sqlset_name => 'STS_PS', task_name => 'MYSPA');
exec dbms_sqlpa.execute_analysis_task(task_name => :tname,execution_type => 'TEST EXECUTE', execution_name => 'before');
select dbms_sqlpa.report_analysis_task(task_name => :tname,type=>'text', section=>'summary') FROM dual;
alter system set optimizer_features_enable='11.1.0.7';
exec dbms_sqlpa.execute_analysis_task(task_name => :tname,execution_type => 'TEST EXECUTE', execution_name => 'after');
select dbms_sqlpa.report_analysis_task(task_name => :tname,type=>'text', section=>'summary') FROM dual;
exec dbms_sqlpa.execute_analysis_task(task_name => :tname,execution_type => 'COMPARE PERFORMANCE');
select dbms_sqlpa.report_analysis_task(task_name => :tname,type=>'text', section=>'summary') FROM dual;
EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name =>:tname,execution_type => 'compare performance', execution_params =>
dbms_advisor.arglist( 'execution_name1', 'before','execution_name2', 'after', 'comparison_metric', ‘elapse_time'));
性特性第一册P431：DBA_ADVISOR_SQLPLANS: See the plans encountered during test-execute
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
from DBA_ADVISOR_SQLPLANS where task_name='MYSPA' and execution_name='SECOND_SQL_TRIAL' and sql_id in('',''
# vim 1.sql
# cut -f 1 1.sql
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
# cut -f 1 1.sql | tr '\n' ','
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,,,#
重演：
wrcscott/tiger@myserver REPLAYDIR=.
5.1
alter system set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=true;
@4_5_1.sql
select sql_handle, plan_name, enabled, accepted, fixed from dba_sql_plan_baselines;
declare
test binary_integer;
begin
test := dbms_spm.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id=>'')
end;
/
variable ret clob
declare
begin
:ret :=
dbms_spm.evolve_sql_plan_baseline(
'SYS_SQL_f6cb7f742ef93547',
'SYS_SQL_PLAN_2ef9354754bc8843'
);
end;
/
print ret
select sql_handle, plan_name, enabled, accepted, fixed from dba_sql_plan_baselines;
Section 6: Capturing Performance Statistics
BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE (
start_time => '2010-07-31 18:00:00',
end_time => '2010-08-01 18:00:00',
baseline_name => 'WEEKEND',
template_name => 'WEEKEND');
END;
---
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
页:
[1]