oracle --管理 ASH (The Active Session History)

管理 ASH (The Active Session History)
每五到十分鐘執行一次
在v$active_session_history有一個欄位session_state,
session_state 有兩個值ON CPU或WAITING, 但值是WAITING又要看event 欄位是否有值, 有值才是真正IDLE TIME

oracle --小技巧, 查物件用法

desc 加物件名稱

E:\Documents and Settings\Administrator>sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on 星期四 10月 11 23:41:46 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> conn / as sysdba
已連線.
SQL> desc dbms_server_alert
FUNCTION EXPAND_MESSAGE RETURNS VARCHAR2
引數名稱 類型 In/Out 預設值？
------------------------------ ----------------------- ------ --------
USER_LANGUAGE VARCHAR2 IN
MESSAGE_ID NUMBER IN
ARGUMENT_1 VARCHAR2 IN
ARGUMENT_2 VARCHAR2 IN
ARGUMENT_3 VARCHAR2 IN
ARGUMENT_4 VARCHAR2 IN
ARGUMENT_5 VARCHAR2 IN
PROCEDURE GET_THRESHOLD
引數名稱 類型 In/Out 預設值？
------------------------------ ----------------------- ------ --------
METRICS_ID BINARY_INTEGER IN
WARNING_OPERATOR BINARY_INTEGER OUT
WARNING_VALUE VARCHAR2 OUT
CRITICAL_OPERATOR BINARY_INTEGER OUT
CRITICAL_VALUE VARCHAR2 OUT
OBSERVATION_PERIOD BINARY_INTEGER OUT
CONSECUTIVE_OCCURRENCES BINARY_INTEGER OUT
INSTANCE_NAME VARCHAR2 IN
OBJECT_TYPE BINARY_INTEGER IN
OBJECT_NAME VARCHAR2 IN
PROCEDURE SET_THRESHOLD
引數名稱 類型 In/Out 預設值？
------------------------------ ----------------------- ------ --------
METRICS_ID BINARY_INTEGER IN
WARNING_OPERATOR BINARY_INTEGER IN
WARNING_VALUE VARCHAR2 IN
CRITICAL_OPERATOR BINARY_INTEGER IN
CRITICAL_VALUE VARCHAR2 IN
OBSERVATION_PERIOD BINARY_INTEGER IN
CONSECUTIVE_OCCURRENCES BINARY_INTEGER IN
INSTANCE_NAME VARCHAR2 IN
OBJECT_TYPE BINARY_INTEGER IN
OBJECT_NAME VARCHAR2 IN
FUNCTION VIEW_THRESHOLDS RETURNS THRESHOLD_TYPE_SET

SQL>

oracle 10G -- UNDO manager

UNDO manager 可分手動及自動兩種
Automatic Undo Management 下列三個參數控制
UNDO_MANAGEMENT 可設 MANUAL 或 AUTO
UNDO_TABLESPACE
UNDO_RETENTION 預設值是900秒, 如果tablespace 空間不夠, 新資料會覆蓋舊資料.
如果tablespace 空間夠, 資料最少保持900秒


(1)create undo tablespace UNDOTABS1 datafile 'c:\oracle\product\10.1.0\oradata\undotbs_01.dbf'
size 100m autoextend on retention guarantee;

Using the Retention Guarantee Option期限內UNDO資料保證不會消失
(2)alter tablespace UNDOTABS1 retention guarantee;

由scn 查time 或由 time 查 scn
select current_scn,scn_to_timestamp(current_scn) ,timestamp_to_scn(sysdate)
from v$database

select tablespace_name ,retention from dba_tablespaces
OEＭ介面可以使用The Undo Advisor 提供 undo tablespace size 建議值, undo retention time 建議值

oarcle 10G -- analyze data (手動收集)

gather_table_stats 收集所有table statistics
gather_index_stats 收集所有index statistics
gather_schema_stats 收集所有schema statistics
gather_database_stats 收集所有database statistics
gather_dictionary_stats 收集所有dictionary statistics
gather_system_stats 收集所有system statistics

dbms_stats另外的指令
dbms_stats.delete_*_stats
dbms_stats.update_*_stats
dbms_stats.import_*_stats
dbms_stats.restore_*_stats
dbms_stats.set_*_stats
dbms_stats.lock_*_stats
dbms_stats.unlock_*_stats

sample:
exec dbms_stats.gather_schema_stats('scott',dbms_stats.auto_sample_size);
exec dbms_stats.delete_table_stats('scott','dept');
相關view dba_optstat_operations,dba_tab_stats_history

oracle 10G -- 讓oracle automatic Optimizer Statistics Collection Worksble

1.Taanalyze data
2.Column statistics
3.Index statistics
4.System statistics
在oracle 8i 以前版本使用analyze table 收集, 在oracle 8i後使用 dbms_stats 收集
在oracle 10G oracle server 自行收集(defaule cost base)
5.讓參數statistics_level 為typical 或 ALL
oracle 會使用dbms_stats 運作
6.Oracle defined automatic optimizer statistics collection job
select * from dba_scheduler_jobs
GATHER_STATS_JOB

enable job
SQL > begin
dbms_scheduler.enable('gather_stats_job');
end;

disable job
SQL > begin
dbms_scheduler.disable('gather_stats_job');
end;
7.session level statistics
wait statistics
segment usage statistics

oracle10G -- 使用automatic memory management 功能

<自動管理SGA>(ASMM)
Background process : MMAN
Enable Automatic SGA:
設statistics_level to typical 或 all (同時也啟動 The Automatic Workload Repository AWR功能)
設sga_target 大於 0
1.oracle 建議不要設sga_max_size參數

2.SGA包含下列兩種記憶體 : oracle 管理記憶體, user自定記憶體
2.1 oracle 建議不要更改 下列四個auto-tuned 參數, oracle server 會自行管理
Automatically Managed SGA Components
Buffer cache(DB_CACHE_SIZE)
Shared pool(SHARED_POOL_SIZE)
Large pool(LARGE_POOL_SIZE)
Java pool(JAVA_POOL_SIZE)

2.2 Manually Sized SGA Components
You are using automatic shared memory management configuration.
Which five initialization parameters will take their memory from the SGA_TARGET paramete
(1)LOG_BUFFER
(2)DB_nk_CACHE_SIZE
(3)STREAMS_POOL_SIZE
(4)DB_KEEP_CACHE_SIZE
(5)DB_RECYCLE_CACHE_SIZE

限定使用總sga記憶體 SGA_MAX_SIZE

3.動態修改sga_target 的值
alter system set sga_target = 600M

相關VIEW
v$vgastat, v$sga

<自動管理PGA>
Enable Automatic PGA:
workarea_size_policy 設auto
PGA_AGGREGATE_TARGET > 0

ASP -- WebService連接語法

WebService 第一版語法
Set WS = Server.CreateObject("cpec.ws1")
Set RS = WS.URL2RS("http://www.yam.com.tw")
Set WS = Nothing
WebService 第二版語法
Set WS = Server.CreateObject("cpec.ws2")
WS.SetURL("http://www.yam.com.tw")
Set RS1 = WS.GO
Set WS = Nothing