Oracle Database Working Notes

Friday, February 10, 2012

Using PL/SQL To Load WFScapabilitiesTemplate.xml Directly

-- Run as MDSYS, SYSTEM or SYS

-- Load the capabilities from a PL/SQL variable
declare
capabilitiesXML CLOB :=
'<WFS_Capabilities version="1.0.0" xmlns="http://www.opengis.net/wfs" xmlns:ogc="http://www.opengis.net/ogc" >
<Service>
<Name> Oracle WFS </Name>
<Title> Oracle Web Feature Service </Title>
<Abstract> Web Feature Service maintained by Oracle </Abstract>
<OnlineResource> http://www.someserver.com/wfs/cwwfs.cgi? </OnlineResource>
</Service>
<Capability>
<Request>
<GetCapabilities>
<DCPType>
<HTTP>
<Get onlineResource="http://www.myserver.com/get?"/>
</HTTP>
</DCPType>
<DCPType>
<HTTP>
<Post onlineResource="http://www.myserver.com/post?"/>
</HTTP>
</DCPType>
</GetCapabilities>
<DescribeFeatureType>
<SchemaDescriptionLanguage>
<XMLSCHEMA/>
</SchemaDescriptionLanguage>
<DCPType>
<HTTP>
<Post onlineResource="http://www.myserver.com/post?"/>
</HTTP>
</DCPType>
</DescribeFeatureType>
<GetFeature>
<ResultFormat>
<GML2/>
</ResultFormat>
<DCPType>
<HTTP>
<Post onlineResource="http://www.myserver.com/post?"/>
</HTTP>
</DCPType>
</GetFeature>
<GetFeatureWithLock>
<ResultFormat>
<GML2/>
</ResultFormat>
<DCPType>
<HTTP>
<Post onlineResource="http://www.myserver.com/post?"/>
</HTTP>
</DCPType>
</GetFeatureWithLock>
<Transaction>
<DCPType>
<HTTP>
<Post onlineResource="http://www.myserver.com/post?"/>
</HTTP>
</DCPType>
</Transaction>
<LockFeature>
<DCPType>
<HTTP>
<Post onlineResource="http://www.myserver.com/post?"/>
</HTTP>
</DCPType>
</LockFeature>
</Request>
</Capability>
<FeatureTypeList>
<Operations>
<Insert/>
<Update/>
<Delete/>
<Query/>
<Lock/>
</Operations>
</FeatureTypeList>
<ogc:Filter_Capabilities>
<ogc:Spatial_Capabilities>
<ogc:Spatial_Operators>
<ogc:BBOX/>
<ogc:Equals/>
<ogc:Disjoint/>
<ogc:Intersect/>
<ogc:Touches/>
<ogc:Crosses/>
<ogc:Within/>
<ogc:Contains/>
<ogc:Overlaps/>
<ogc:Beyond/>
<ogc:DWithin/>
</ogc:Spatial_Operators>
</ogc:Spatial_Capabilities>
<ogc:Scalar_Capabilities>
<ogc:Logical_Operators/>
<ogc:Comparison_Operators>
<ogc:Simple_Comparisons/>
<ogc:Like/>
<ogc:Between/>
<ogc:NullCheck/>
</ogc:Comparison_Operators>
<ogc:Arithmetic_Operators>
<ogc:Simple_Arithmetic/>
</ogc:Arithmetic_Operators>
</ogc:Scalar_Capabilities>
</ogc:Filter_Capabilities>
</WFS_Capabilities>
';
begin
SDO_WFS_PROCESS.insertCapabilitiesInfo (xmltype(capabilitiesXML));
end;
/

-- Commit the change
commit;

WFScapabilitiesTemplate.xml

<WFS_Capabilities version="1.0.0" xmlns="http://www.opengis.net/wfs" xmlns:ogc="http://www.opengis.net/ogc" >
<Service>
<Name> Oracle WFS </Name>
<Title> Oracle Web Feature Service </Title>
<Abstract> Web Feature Service maintained by Oracle </Abstract>
<OnlineResource> http://www.someserver.com/wfs/cwwfs.cgi? </OnlineResource>
</Service>
<Capability>
<Request>
<GetCapabilities>
<DCPType>
<HTTP>
<Get onlineResource="http://www.myserver.com/get?"/>
</HTTP>
</DCPType>
<DCPType>
<HTTP>
<Post onlineResource="http://www.myserver.com/post?"/>
</HTTP>
</DCPType>
</GetCapabilities>
<DescribeFeatureType>
<SchemaDescriptionLanguage>
<XMLSCHEMA/>
</SchemaDescriptionLanguage>
<DCPType>
<HTTP>
<Post onlineResource="http://www.myserver.com/post?"/>
</HTTP>
</DCPType>
</DescribeFeatureType>
<GetFeature>
<ResultFormat>
<GML2/>
</ResultFormat>
<DCPType>
<HTTP>
<Post onlineResource="http://www.myserver.com/post?"/>
</HTTP>
</DCPType>
</GetFeature>
<GetFeatureWithLock>
<ResultFormat>
<GML2/>
</ResultFormat>
<DCPType>
<HTTP>
<Post onlineResource="http://www.myserver.com/post?"/>
</HTTP>
</DCPType>
</GetFeatureWithLock>
<Transaction>
<DCPType>
<HTTP>
<Post onlineResource="http://www.myserver.com/post?"/>
</HTTP>
</DCPType>
</Transaction>
<LockFeature>
<DCPType>
<HTTP>
<Post onlineResource="http://www.myserver.com/post?"/>
</HTTP>
</DCPType>
</LockFeature>
</Request>
</Capability>
<FeatureTypeList>
<Operations>
<Insert/>
<Update/>
<Delete/>
<Query/>
<Lock/>
</Operations>
</FeatureTypeList>
<ogc:Filter_Capabilities>
<ogc:Spatial_Capabilities>
<ogc:Spatial_Operators>
<ogc:BBOX/>
<ogc:Equals/>
<ogc:Disjoint/>
<ogc:Intersect/>
<ogc:Touches/>
<ogc:Crosses/>
<ogc:Within/>
<ogc:Contains/>
<ogc:Overlaps/>
<ogc:Beyond/>
<ogc:DWithin/>
</ogc:Spatial_Operators>
</ogc:Spatial_Capabilities>
<ogc:Scalar_Capabilities>
<ogc:Logical_Operators/>
<ogc:Comparison_Operators>
<ogc:Simple_Comparisons/>
<ogc:Like/>
<ogc:Between/>
<ogc:NullCheck/>
</ogc:Comparison_Operators>
<ogc:Arithmetic_Operators>
<ogc:Simple_Arithmetic/>
</ogc:Arithmetic_Operators>
</ogc:Scalar_Capabilities>
</ogc:Filter_Capabilities>
</WFS_Capabilities>

建立 Oracle Spatial WFS Part I

環境

Oracle Database 11g Release 2 (11.2.0.2 以上)

Oraacle Weblogic 11g Release 1 (10.3.5)+
Oracle Spatial Web Service 架構圖如下：

不同於 Oracle WMS的架構是Spatial Web Service在Middleware上提供WS Server API，實際WS的處理則是在Oracle Database內；管理與設定工具也是建置於資料庫內(PL/SQL API)。
建立Oracle Spatial Web Service的主要步驟：
1. Deploy SDOWS Web 應用程式
2. 設定相關資料庫使用者帳號
3. 設定Middleware連接資料庫的data sources
4. 設定一般Web Service的參數
5. 重新啓動 SDO web Service

Deploy Spatial Web Service
實際上將"sdows.ear"佈署到middleware中間件應用伺服器。我們使用的middleware是WebLogic 10.3.5；建議使用最新的sdows.ear，sdows.ear會跟著資料庫軟體的安裝，放置於$ORACLE_HOME/md/jlib的目錄下。
我是將$ORACLE_HOME/md/jlib/sdows.ear複製至安裝的目錄"/home/oracle"
[以Linux為範例，先解開sdows.ear]
$ cp $ORACLE_HOME/md/jlib/sdows.ear /home/oracle/.
$ cd /home/oracle
[oracle@localhost ~]$ mv sdows.ear sdows.ear1
[oracle@localhost ~]$ mkdir sdows.ear
[oracle@localhost ~]$ cd sdows.ear
[oracle@localhost sdows.ear]$ /usr/java/default/bin/jar xvf ../sdows.ear1
[oracle@localhost sdows.ear]$ mv sdows.war sdows.war1
[oracle@localhost sdows.ear]$ mkdir sdows.war
[oracle@localhost sdows.ear]$ cd sdows.war
[oracle@localhost sdows.war]$ /usr/java/default/bin/jar xvf ../sdows.war1

設定相關資料庫者帳號
Oracle資料庫預設管理者的帳號都是被鎖定的，需要被解開且重新設定其密碼
以資料庫系統管理者 (system)登入，執行下列SQL

-- 解除鎖定帳號
alter user mdsys account unlock;
alter user spatial_csw_admin_usr account unlock;
alter user spatial_wfs_admin_usr account unlock;

-- 設定管理者帳號的密碼(需要記住密碼，待會在設定WebLogic data sources時需要用到)
alter user mdsys identified by <password>;
alter user spatial_csw_admin_usr identified by <password>;
alter user spatial_wfs_admin_usr identified by <password>;

接下來要設定"匿名使用者"，預設資料庫使用者名稱"SpatialWsXmlUser"。該使用者會處理所有傳入XML/HTTP的要求，透過MDSYS進行"proxy authentication"，因此SpatialWsXmlUser的密碼跟本不重要(因為完全用不到)，但不要設成很好猜到。若要修改"匿名者"的預設使用者名稱，則要一起修改"WSConfig.xml"所對應的使用者。
-- 建立 "匿名使用者"使用者proxy authentication
create user spatialwsxmluser identified by whatever;
-- 給予"匿名使用者"連接資料庫的系統權限
grant create session to spatialwsxmluser;
-- 透過MDSYS資料庫使用者進行 proxy authentication
alter user spatialwsxmluser grant connect through mdsys;
-- 給予"匿名使用者"接受處理WFS與CSW外部要求的角色能力
grant wfs_usr_role to spatialwsxmluser;
grant csw_usr_role to spatialwsxmluser;

移除不必要角色密碼的設定(選項)

alter role WFS_USR_ROLE not identified;
alter role CSW_USR_ROLE not identified;
alter role SPATIAL_WFS_ADMIN not identified;
alter role SPATIAL_CSW_ADMIN not identified;

設定Middleware連接資料庫的data sources
這裏我們使用WebLogic 10.3.5 做為應用伺服器(middleware application server)。以下們要建立的data sources與相關資訊：

Data Source Name	JNDI Name	Database User	Usage
WfsProxyConnection	jdbc/WfsProxyConnectionCoreDS	mdsys	WFS User connections
WFS_ADMIN_CONN_NAME	jdbc/WFS_ADMIN_CONN_NAME	spatial_wfs_admin_usr	WFS Administration
CatalogProxyConnection	jdbc/CatalogProxyConnectionCoreDS	mdsys	CSW User connections
CSW_ADMIN_CONN_NAME	jdbc/CSW_ADMIN_CONN_NAME	spatial_csw_admin_usr	CSW Administration
OpenLsProxyConnection	jdbc/OpenLsProxyConnectionCoreDS	mdsys	OpenLS User connections

在WebLogic Console (http://localhost:7001/console, for example)，點選左邊選單Services->data Sources

在中間"Summary of JDBC Data Sources"，"Data Sources" 點選 'New"，"Generic Data Source"；分別設定 "Name"，"JNDI Name"，"Database Type"；Database Type 選 Oracle，按下一步

選擇Oracle JDBC Driver -- "Oracle's Driver (Thin) for Service connections: Versions: 9.0.1 and later"；按下一步

點掉(unchecked) "Supports Global Transactions"，按下一步

設定Oracle JDBC連線資訊，按下一步

按下"測試連線設定"(選項)

結果顯示連線成功，則表示資料庫連線設定正確，按下一步

設定Data Source至指定的 application servers，按下"完成"。

依次將要設定的Data Source 設定完成。

設定一般Web Service 的參數

如果都使用預設值，基本上沒有什麼要特別設定的。如果需要可以更改log的設定值，以方便追踪與除錯。WSConfig.xml位於 WEB-INF/conf/WSConfig.xml

<logging log_level="finest" log_thread_name="true"
log_time="true">
<log_output name="System.err" />
<log_output name="log/ws.log" />
</logging>

如果有變動"匿名使用者"的預設使用者，也在此一併修改。

重新啓動 SDO Web Service
停止SDO Web Service：按下Deployments -> 點選 sdows -> 點 "Stop" -> "Force Stop Now"

啓動SDO Web Service：按下Deployments -> 點選 sdows -> 點 "Start" -> "Servicing all requests"

Thursday, February 09, 2012

建立 Oracle Spatial/Location WMS

簡單地介紹如何將儲存在Oracle Spatial/Location 的空間資料發佈成WMS (Web Map Service)
需要具備的環境
1. Oracle Database (已經將空間資料載入於SDO_GEOMETRY欄位的表格)
2. Oracle Mapviewer (透過 Oracle Mabuilder 將空間資料表格已定義成BASE_MAP，THEME)
Oracle WMS 是由Oracle Mapviewer提供，架構圖簡化如下：

1. 啓動WMS的設定
進入Oracle Mapviewer的設定，並移除<wms_cnfig>的註解

修改成適當的主機名稱，Port，通訊協定 (protocol)，default_datasource (這個設定很重要)以及 public_datasource

<wms_config

host="www.oracle.com"

port="80"

protocol="http“

default_datasource="mvdemo"

public_datasources="mvdemo"

...

</wms_config>

在這裏用的data_source是"mvdemo"，應該在之前Mapviewer的設定上已經談過了，這裏就略過。附加說明：如果沒有設定default_datasource，則預設值為"wms"。

2. 設定其它在<wms_config>的內容
3. 設定SDO與EPSG座標參數對應表(選項，如果要使用EPSG座標代號就一定要設定)
修改<wms_config>的內容設定

<wms_config> ...

<sdo_epsg_mapfile>

../conf/epsg_srids.properties

</sdo_epsg_mapfile>

</wms_config>

epsg_srids.properties 的內容格式為 sdo_srid=epsg_srid

範例

82208=32601

82271=32602

81989=27700

27700-27700

這樣就大致設定完成

接下來就可以進行測試

GetCapabilities

http://127.0.0.1:7001/mapviewer/wms?VERSION=1.1.1&REQUEST=GetCapabilities&SERVICE=WMS

http://127.0.0.1:7001/mapviewer/wms?VERSION=1.3.0&REQUEST=GetCapabilities&SERVICE=WMS

(請注意URL並沒有換行)

GetMap

http://127.0.0.1:7001/mapviewer/wms?VERSION=1.1.0&REQUEST=GetMap&FORMAT=image/png&SRS=EPSG:4326&BBOX=-127,25,-78,40&WIDTH=800&HEIGHT=600&LAYERS=THEME_DEMO_STATES,THEME_DEMO_STATES_LINE,CUSTOMERS

http://127.0.0.1:7001/mapviewer/wms?VERSION=1.3.0&REQUEST=GetMap&FORMAT=image/png&CRS=EPSG:4326&BBOX=-127,25,-78,40&WIDTH=800&HEIGHT=600&LAYERS=THEME_DEMO_STATES,THEME_DEMO_STATES_LINE,CUSTOMERS

(BBOX 不要超出WMS可以提供的範圍)

以下為使用"BASE_MAP"再加套上"LAYERS"

http://127.0.0.1:7001/mapviewer/wms?VERSION=1.1.0&REQUEST=GetMap&FORMAT=image/jpeg&SRS=SDO:8307&BBOX=-127,25,-78,40&WIDTH=800&HEIGHT=600&BASEMAP=DEMO_MAP&DATASOURCE=mvdemo&LAYERS=CUSTOMERS

http://127.0.0.1:7001/mapviewer/wms?VERSION=1.3.0&REQUEST=GetMap&FORMAT=image/jpeg&CRS=SDO:8307&BBOX=-127,25,-78,40&WIDTH=800&HEIGHT=600&BASEMAP=DEMO_MAP&DATASOURCE=mvdemo&LAYERS=CUSTOMERS

GetFeatureInfo

http://127.0.0.1:7001/mapviewer/wms?VERSION=1.1.0&REQUEST=GetFeatureInfo&query_Layers=THEME_DEMO_STATES&SRS=SDO:8307&BBOX=-124,32,-116,41&WIDTH=800&HEIGHT=600&X=10&Y=10

http://127.0.0.1:7001/mapviewer/wms?VERSION=1.3.0&REQUEST=GetFeatureInfo&query_Layers=THEME_DEMO_STATES&CRS=SDO:8307&BBOX=-124,32,-116,41&WIDTH=800&HEIGHT=600&I=10&J=10

其結果

<GetFeatureInfo_Result>
<ROWSETname="THEME_DEMO_STATES">
<ROWnum="1">
<ROWID>

AAASeoAABAAAVghAAE
</ROWID>
</ROW>
</ROWSET>
</GetFeatureInfo_Result>

Thursday, February 02, 2012

SELECT COUNT(*)

最近在客戶那邊遇到了一個效能上的問題。客戶有一個程式會對資料表不斷地新增資料，有另外一支程式會來處理新增進入資料表格的資料，並對處理的結果異動其狀態；但最後不會將該資料刪除。也就是說該資料表格會愈長愈大。
客戶也會要知道，目前待處理的資料筆數還有多少，最簡單的方式就是 SELECT COUNT(*)....一開始當然速度是在可以接受的情況下，但隨著時間，資料筆數愈來愈大，每次 SELECT COUNT(*)的時間就愈來愈長...。然後加入自動更新(Refresh)，從5秒自動更新變成10秒, 30秒，....；再加上同時之間有許多使用者會同時上線，然後你會看到資料庫主機CPU都花在USER I/O，速度愈變愈慢....
很熟悉嗎? 如何改善執行效率呢?
我的想法是：沒辦法，除非改變程式 SELECT COUNT(*)的架構，不然 USER I/O是無法避免的。怎麼做--利用一資料表格來儲存計數 -- SELECT COUNT(*) -- 的結果，新增資料的程式則異動該值+1；處理異動的程式則異動該值-1；查詢筆數的程式則直接取得該值。

建立計數儲存表格
create table table_row_counts (table_name varchar2(40), row_counts number);

設定初始值、讀取數值、新增數值與異動...等
當然直接UPDATE是一種方法，在這我使用 stored procedure

create or replace
PACKAGE ROW_COUNTS AS
procedure init_rows;
procedure add_row;
procedure reduce_row;
function get_rows return number;
END ROW_COUNTS;
/

create or replace
package body row_counts as

function get_rows return number as
rowsn number;
begin
select row_counts into rowsn from table_row_counts where table_name = 'EMP';
return rowsn;
end;

procedure add_row as
begin
update table_row_counts set row_counts = row_counts + 1 where table_name = 'EMP';
commit;
end;

procedure init_rows as
begin
update table_row_counts set row_counts = (select count(*) from emp ) where table_name = 'EMP';
commit;
end;

procedure reduce_row as
begin
update table_row_counts set row_counts = row_counts - 1 where table_name = 'EMP';
commit;
end;

end;

/

Monday, January 02, 2012

Oracle 資料庫校能調校 -- 使用ADDM/SQL Tuning Advisor來解決SQL校能問題

By leverage "Oracle By Example - Chapter 10: Monitoring and Tuning the Database"

從Oracle DB Console, Performance 看效能，如下圖：

我們期望CPU的部分是佔主要的部分，其它顏色的部分是愈少愈好。上圖，很顯然CPU的部分所佔的比例太少，其它wait events太多，有調校的空間。
如果問題發生的時間夠長，ADDM自然會自動進行分析，其分析的結果可以在"Home"頁籤下的 "ADDM Performance Analysis" 找到；如果ADDM還尚未執行，則可以在 "Performance" -> "Average Active Sessions" 點選 "View ADDM Run" 圖示。
點選對資料庫時間造成最大影響的連結

可以看到對資料庫時間造成影響的原因。範例為"Top SQL Statements"

點選該SQL連結

再按下"Schedule SQL Tuning Advisor"按鈕；我們想省點力氣，讓Oracle ADDM告訴我們怎麼調整SQL

完成 SQL Tuning Advisor 的工作

這個時候Advisor會告訴我們該如何調整SQL。範例非常適用於套裝軟體(無法變更SQL指令)，但是Oracle 找到另一種更好執行的方式，因此建議使用"SQL Profile"來變動原執行計劃的方式。直接下實施"Implement"

按下"Yes"

SQL Profile 已經建立

再檢視OEM，應該就會看到校能改善

Oracle 資料庫校能調校 -- 使用ADDM來解決Row Lock問題

By leverage "Oracle By Example - Chapter 10: Monitoring and Tuning the Database"
Oracle Database10g以後，最簡單對資料庫進行校能診斷的方法就是使用ADDM(Automatic Database Diagnostics Monitor)
在進行OEM (DB Console)主畫面之後，直接點選"Performance"頁籤，就可以看到資料庫執行校能的狀態

由畫面上可以看到有等待的事件--"Application"

點選"Application"就會進入到"Active Sessions Waiting"。在圖示的地方就可以很清楚地看到"enq TX: row lock contention"。我們裝傻一下。

往下捲到底層，可以看到"Additional Monitoring Links"，點選"Snapshot"

接著按下"Create"

這時候會問你是否要手動建立snapshot(Are you sure you want to create a manual snapshot?)按下"Yes"。

當snapshot建立完成，按下右上角的"Database"頁籤

往下捲到"ADDM Performance Analysis"，應該至少會出現2個警示，其中一個就會是"Row Lock Waits"

ADDM會自動定期(預設大約是1小時一次)地去檢視資料庫的執行校能並主動找出有問題的地方，然後顯示在這個地方
點選"Row Lock Waits"連結，可以再看到更細部的說明

點選Session ID再看細部資訊。其中可以看到那一個SQL指令造成Row Lock

此時，可以非常確定造成Row Lock 的原因了。按下"Performance"頁籤，並往下捲，點選"Additional Monitoring Links"下的"Blocking Sessions"

點選造成Row Lock的Session ID，再按下"Kill Session"

確認要 "Kill Session"的ID，按下"Yes"
到此，問題已完全解決。

Oracle 資料庫監測

By leverage "Oracle By Example - Chapter 10: Monitoring and Tuning the Database"
身為Oracle Database 管理人員，在主動監測資料庫空間上至可以做到
為資料表格空間(Tablespace)設定警示上限(Threshold)
發送警示通知

為資料表格空間(Tablespace)設定警示上限(Threshold)
在資料表格空間清單下，選擇要設定的資料表格空間(範使用的是TBSALERT)，並按下"Edit"

點選"Thresholds"頁籤

在指定的Thresholds(Specified Thresholds)設定60%為警示，68%為嚴重，然後按下套用。這樣子就完成設定。

發送警示通知
在OEM的右上角按下"Setup"

接下來點選左邊選單上的"Notification Methods"

在Outgoing Mail (SMTP) Server 填入 SMTP 伺服器名稱，入SMTP認証的使用者名稱(User Name)與密碼(Password, Comfirm Password)，發信者名稱(Identify Sender As)與發信者電子郵件(Sender's E-mail Address)。填完後，按下"Apply"

接下來按下右上角的"Preference"

在電子郵件清單(E-mail Addresses)下，按下"Add Another Row)

填入收件人的電子郵件，然後按下"Apply"

Oracle Database Memory Management

在 Oracle Database 11g 以後，建議使用Automatic Memory Management功能。省下分別設定SGA (System Global Area)與PGA (Program Global Area)的大小。
下圖為 Enable Automatic Memory Management，設定 Total Memory Size 與 Maximum Memory Size。要注意的是 Total Memory Size < Maximum Memory Size.

Sunday, January 01, 2012

Oracle Enterprise Manager (DB Console) 啓動與設定

By leverage "Oracle By Example - Chapter 3: Getting Started with Oracle Enterprise Manager"

啓動 DB Console
在Windows環境下，可以直接從"服務主控台"上直接啓動。
在Linux/Unix環境下，執行 "emctl start dbconsole"

設定其它使用者使用 EM (Oracle Enterprise Manager)
打開流覽器，預設的網址 https://<hostname>:1158/em
以 sys 登入 DB Console (記得"Connect As" 要選 "SYSDBA")