课程第31次（2018-08-20星期一）
selectsubstr(t.table_name, 3 ,1 ), count(*)from dba_tables t
where t.owner='SYS' and t.table_namelike 'WR_$%'
group by substr(t.table_name, 3 ,1 );
SUBSTR(T.TABLE_NAME,3,1)COUNT(*)
1H121
2R20
3I88
4M9
H类有对应的V$视图
selecttable_name
from dba_tables t
where t.owner='SYS' and t.table_namelike 'WR_$%'
andsubstr(t.table_name, 3 ,1 )='H';
TABLE_NAME
1WRH$_WAITSTAT_BL
2WRH$_WAITSTAT
3WRH$_WAITCLASSMETRIC_HISTORY
4WRH$_UNDOSTAT
5WRH$_TOPLEVELCALL_NAME
6WRH$_THREAD
7WRH$_TEMPSTATXS
8WRH$_TEMPFILE
9WRH$_TABLESPACE_STAT_BL
10WRH$_TABLESPACE_STAT
11WRH$_TABLESPACE_SPACE_USAGE
12WRH$_SYS_TIME_MODEL_BL
13WRH$_SYS_TIME_MODEL
14WRH$_SYSTEM_EVENT_BL
15WRH$_SYSTEM_EVENT
16WRH$_SYSSTAT_BL
17WRH$_SYSSTAT
18WRH$_SYSMETRIC_SUMMARY
19WRH$_SYSMETRIC_HISTORY
20WRH$_STREAMS_POOL_ADVICE
21WRH$_STREAMS_CAPTURE
22WRH$_STREAMS_APPLY_SUM
23WRH$_STAT_NAME
24WRH$_SQL_WORKAREA_HISTOGRAM
25WRH$_SQL_SUMMARY
26WRH$_SQL_PLAN
27WRH$_SQL_BIND_METADATA
28WRH$_SQLTEXT
29WRH$_SQLSTAT_BL
30WRH$_SQLSTAT
31WRH$_SQLCOMMAND_NAME
32WRH$_SHARED_SERVER_SUMMARY
33WRH$_SHARED_POOL_ADVICE
34WRH$_SGA_TARGET_ADVICE
35WRH$_SGASTAT_BL
36WRH$_SGASTAT
37WRH$_SGA
38WRH$_SESS_TIME_STATS
39WRH$_SESSMETRIC_HISTORY
40WRH$_SERVICE_WAIT_CLASS_BL
41WRH$_SERVICE_WAIT_CLASS
42WRH$_SERVICE_STAT_BL
43WRH$_SERVICE_STAT
44WRH$_SERVICE_NAME
45WRH$_SEG_STAT_OBJ
46WRH$_SEG_STAT_BL
47WRH$_SEG_STAT
48WRH$_RULE_SET
49WRH$_RSRC_PLAN
50WRH$_RSRC_CONSUMER_GROUP
51WRH$_ROWCACHE_SUMMARY_BL
52WRH$_ROWCACHE_SUMMARY
53WRH$_RESOURCE_LIMIT
54WRH$_PROCESS_MEMORY_SUMMARY
55WRH$_PLAN_OPTION_NAME
56WRH$_PLAN_OPERATION_NAME
57WRH$_PGA_TARGET_ADVICE
58WRH$_PGASTAT
59WRH$_PERSISTENT_SUBSCRIBERS
60WRH$_PERSISTENT_QUEUES
61WRH$_PARAMETER_NAME
62WRH$_PARAMETER_BL
63WRH$_PARAMETER
64WRH$_OSSTAT_NAME
65WRH$_OSSTAT_BL
66WRH$_OSSTAT
67WRH$_OPTIMIZER_ENV
68WRH$_MUTEX_SLEEP
69WRH$_MTTR_TARGET_ADVICE
70WRH$_METRIC_NAME
71WRH$_MEM_DYNAMIC_COMP
72WRH$_MEMORY_TARGET_ADVICE
73WRH$_MEMORY_RESIZE_OPS
74WRH$_LOG
75WRH$_LIBRARYCACHE
76WRH$_LATCH_PARENT_BL
77WRH$_LATCH_PARENT
78WRH$_LATCH_NAME
79WRH$_LATCH_MISSES_SUMMARY_BL
80WRH$_LATCH_MISSES_SUMMARY
81WRH$_LATCH_CHILDREN_BL
82WRH$_LATCH_CHILDREN
83WRH$_LATCH_BL
84WRH$_LATCH
85WRH$_JAVA_POOL_ADVICE
86WRH$_IOSTAT_FUNCTION_NAME
87WRH$_IOSTAT_FUNCTION
88WRH$_IOSTAT_FILETYPE_NAME
89WRH$_IOSTAT_FILETYPE
90WRH$_IOSTAT_DETAIL
91WRH$_INTERCONNECT_PINGS_BL
92WRH$_INTERCONNECT_PINGS
93WRH$_INST_CACHE_TRANSFER_BL
94WRH$_INST_CACHE_TRANSFER
95WRH$_INSTANCE_RECOVERY
96WRH$_IC_DEVICE_STATS
97WRH$_IC_CLIENT_STATS
98WRH$_FILESTATXS_BL
99WRH$_FILESTATXS
100WRH$_FILEMETRIC_HISTORY
101WRH$_EVENT_NAME
102WRH$_EVENT_HISTOGRAM_BL
103WRH$_EVENT_HISTOGRAM
104WRH$_ENQUEUE_STAT
105WRH$_DYN_REMASTER_STATS
106WRH$_DLM_MISC_BL
107WRH$_DLM_MISC
108WRH$_DISPATCHER
109WRH$_DB_CACHE_ADVICE_BL
110WRH$_DB_CACHE_ADVICE
111WRH$_DATAFILE
112WRH$_CURRENT_BLOCK_SERVER
113WRH$_CR_BLOCK_SERVER
114WRH$_COMP_IOSTAT
115WRH$_CLUSTER_INTERCON
116WRH$_BUFFER_POOL_STATISTICS
117WRH$_BUFFERED_SUBSCRIBERS
118WRH$_BUFFERED_QUEUES
119WRH$_BG_EVENT_SUMMARY
120WRH$_ACTIVE_SESSION_HISTORY_BL
121WRH$_ACTIVE_SESSION_HISTORY
过去基线：
DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE ( -
start_snap_id IN NUMBER,
end_snap_id IN NUMBER,
baseline_name IN VARCHAR2);
未来基线：
BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE (
start_time=> to_date('21-JUN-2010','DD-MON-YYYY'),
end_time => to_date('21-SEP-2010','DD-MON-YYYY'),
baseline_name=> 'FALL10',
template_name=> 'FALL10',
expiration => NULL ) ;
END;
未来重复基线：
BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE ( day_of_week => 'SATURDAY',
hour_in_day => 6,
duration => 20,
start_time => to_date('21-JUN-2007','DD-MON-YYYY'),
end_time => to_date('21-JUN-2008','DD-MON-YYYY'),
baseline_name_prefix=> 'SAT_MAINT_WIN'
template_name => 'SAT_MAINT_WIN',
expiration => 90,
dbid => NULL );
END;
select* from dba_rsrc_io_calibrate;
START_TIMEEND_TIMEMAX_IOPSMAX_MBPSMAX_PMBPSLATENCYNUM_PHYSICAL_DISKS
118-AUG-18 08.51.29.805428 PM18-AUG-18 09.02.01.264990 PM51258221260401
selectt.degree
from dba_tables t
where t.owner='SH' and t.table_name='SALES';
alter table sh.sales parallel ;
alter user sh identified by oracle_4U account unlock;
select* from dba_rsrc_io_calibrate;
Execution Plan
----------------------------------------------------------
Plan hash value: 2049827507
--------------------------------------------------------------------------------
-------------------------------------------------------------------
| Id| Operation | Name | Rows| Bytes | C
ost (%CPU)| Time | Pstart| Pstop | TQ|IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7059 | 35295 |
252 (6)| 00:00:04 | | | | | |
| 1 |PX COORDINATOR | | | |
| | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 7059 | 35295 |
252 (6)| 00:00:04 | | | Q1,01 | P->S | QC (RAND)|
| 3 | HASH GROUP BY | | 7059 | 35295 |
252 (6)| 00:00:04 | | | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | 7059 | 35295 |
252 (6)| 00:00:04 | | | Q1,01 | PCWP | |
| 5 | PX SEND HASH | :TQ10000 | 7059 | 35295 |
252 (6)| 00:00:04 | | | Q1,00 | P->P | HASH |
| 6 | HASH GROUP BY | | 7059 | 35295 |
252 (6)| 00:00:04 | | | Q1,00 | PCWP | |
| 7 | PX BLOCK ITERATOR | | 918K| 4486K|
238 (0)| 00:00:03 | 1 | 28 | Q1,00 | PCWC | |
| 8 | BITMAP CONVERSION COUNT | | 918K| 4486K|
238 (0)| 00:00:03 | | | Q1,00 | PCWP | |
| 9 | BITMAP INDEX FAST FULL SCAN| SALES_CUST_BIX | | |
| | 1 | 28 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------
-------------------------------------------------------------------
Note
-----
- automatic DOP: Computed Degree of Parallelism is 2
Statistics
----------------------------------------------------------
66recursive calls
0db block gets
655consistent gets
503physical reads
0redo size
93523bytes sent via SQL*Net to client
5586bytes received via SQL*Net from client
472SQL*Net roundtrips to/from client
0sorts (memory)
0sorts (disk)
7059rows processed
SQL>
页:
[1]