程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle數據庫基礎 >> Oracle性能調整與優化(三)

Oracle性能調整與優化(三)

編輯:Oracle數據庫基礎
正如在第1和第2部分中提到那樣,有幾個相對容易的改善性能的步驟你可以采取,這些步驟其中一個涉及到使用自動化工具“指導”你編寫SQL語句,有許多生產性能分析或性能調整工具的廠家,在接下來的文章中,我們將對它們中的一個進行考察。

Quest Software

許多DBA和開發者使用一個名叫Toad的工具,它出自Quest Software公司,按照Quest Software網站上說法,Toad用戶社區大約有500,000數量的用戶,Toad的一個特色就是它有能力優化SQL查詢,換句話說,Oralce公司還沒有占領調整顧問類型工具的市場。

關於顧問工具你有多種選擇,理解它們是做什麼的,如果你沒有工作在生產或開發環境中你使用它們做什麼?可能僅僅是喜歡,即使你處於一個開發環境,但你可能也沒有使用到非常大的數據,本文的焦點集中在如何產生大量的數據,我們將介紹Quest Software的另一個產品:DataFactory for Oracle。

DataFactory

DataFactory的用途是為多種數據庫平台快速創建有意義的測試數據,包括Oracle、DB2、Sybase以及ODBC兼容的數據庫,正常情況下,每服務器零售價為595美元,在Quest Software的網站有一個可免費使用30天的版本可下載。

要獲得這個軟件(目前是5.5.0版本),你必須用真實的電子郵件地址注冊,hotmail和gmail會被拒絕的,一旦你注冊成功了,你將收到一封包含密鑰的郵件,這個密鑰用於解鎖應用程序,開始30天的試用。

安裝過程是相當簡單的,如果你運行了微軟的AntiSpyware,你可能會收到一個或多個錯誤,禁用掉實時保護後重新安裝。

創建輔助對象

了解應用程序較好的方法是使用它的輔助對象,一般的過程有:

1、創建一個項目

2、在方案中創建表

3、運行腳本載入數據

不幸的是,在禁用了系統名參考的約束上獲取刷新最好的方法是使用內置的輔助對象,使用一個反復的處理方法,可以禁用掉所有約束直到載入腳本運行不出現錯誤為止,然而,當Quest運行時修復了這個bug時,我可以標識和禁用這個約束。

啟動DataFactory後,你可以選擇啟動自學教材,關於如何載入自學教材對象的指令(也就是所有的幫助)都在Html文件中。

幫助系統中的指令規定了使用哪些表,但那是不准確的,下一步我會提到要用到哪些表(總共15個,都是以DF_前綴開頭的)。

要創建輔助對象,安裝下列步驟做:

1、選擇工具(Tools)創建輔助對象(Create Tutorial Objects),顯示輔助對象創建向導。

2、從選擇連接方法的下拉列表中選擇合適的數據庫類型。

3、點擊下一步(Next)。

4、輸入連接參數。

5、點擊下一步(Next),顯示完成頁面。

6、點擊完成(Finish),DataFactory輔助表就創建好了。

在創建表之前,你應該在數據庫中先創建一個單獨的方案對象,使用Oracle 10g,我創建了一個quest用戶對象/方案(授予它connect和resource權限即可)。

點擊Tools Create Tutorial Objects

輔助設置向導顯示它自己的版本和Oracle的logo

在完成頁顯示一列表名

DataFactory告訴你創建成功了

在左邊框架中顯示了項目文件夾

在主菜單上點擊運行按鈕(Run),立即報出ORA-02291錯誤,這是違反了完整性約束的錯誤提示(某些表上還不止一次),因為在一個外鍵關聯的列上載入的數據沒有同時往主表插入對應的數據,幾乎所有的約束命名都采取SYS_Cxxxxxx命名結構,意味著它們不是明確的名字。

為了解決完整性約束問題,你可以禁用掉約束(只要你知道要修改哪個表),下面的查詢和ALTER TABLE命令顯示識別並禁用有問題的約束:

SQL> select owner, constraint_name, table_name, column_name
2  from all_cons_columns
3  where constraint_name like '%9814%';
OWNER CONSTRAINT_NAME                TABLE_NAME           COLUMN_NAME   
----- ------------------------------ -------------------- ------------
QUEST SYS_C009814                    DF_ORDERS            CUSTID        
SQL> alter table df_orders
2  disable constraint sys_c009814;
Table altered.

結果(Results)窗口顯示你的腳本完全成功執行了,意味著你可以看看究竟創建了些什麼。

不是一次分析一個表,使用內置的DBMS_STATS(它是Oracle推薦的分析工具包),如果你使用的是Oracle 10g,你應該添加一個WHERE dropped='NO'條件阻止在顯示查詢結果時顯示被刪掉的表。

SQL> execute dbms_stats.gather_schema_stats('QUEST');
PL/SQL procedure successfully completed.
SQL> select table_name, num_rows
2  from user_tables
3  where dropped='NO';
TABLE_NAME             NUM_ROWS
-------------------- ----------
DF_TITLES                   100
DF_MOVIE_CUSTOMER          1100
DF_MOVIE_EMPLOYEE           900
DF_DUMMY                   1100
DF_AUTHORS_TITLES          1100
DF_MOVIE_RENTAL             700
DF_PRODUCTS                 100
DF_MOVIE_TAPE               400
DF_CUSTOMERS               1100
DF_AUTHORS                 1100
DF_MOVIE_DISTRICT          1100
DF_ORDERS                   101
DF_MOVIE_MOVIE              900
DF_ORDERDETAILS             200
DF_MOVIE_STORE              500
15 rows selected.

返回項目或列表,在列表中選擇一個表,將會顯示它的列和列的數據類型,你可能需要在結果(Results)和子段(Children)之間使用固定按鈕功能。

使用DF_MOVIE_CUSTOMER作為一個例子,如何查看它的數據?隨機字符串選項剛好准確地用於這裡。

輔助表上更多的內容

在外鍵列上有索引嗎?

SQL> select index_name, table_name, column_name, column_position
2  from user_ind_columns;
INDEX_NAME   TABLE_NAME           COLUMN_NAME          COLUMN_POSITION
------------ -------------------- -------------------- ---------------
SYS_C009823  DF_MOVIE_DISTRICT    DISTRICTID                         1
SYS_C009827  DF_MOVIE_STORE       STOREID                            1
SYS_C009830  DF_MOVIE_EMPLOYEE    EMPID                              1
SYS_C009837  DF_MOVIE_CUSTOMER    CUSTID                             1
SYS_C009841  DF_MOVIE_MOVIE       MOVIEID                            1
SYS_C009845  DF_MOVIE_TAPE        TAPEID                             1
SYS_C009850  DF_MOVIE_RENTAL      TAPEID                             1
SYS_C009850  DF_MOVIE_RENTAL      CUSTID                             2
SYS_C009850  DF_MOVIE_RENTAL      RENTDATE                           3
SYS_C009810  DF_CUSTOMERS         CUSTID                             1
SYS_C009813  DF_ORDERS            ORDERID                            1
SYS_C009816  DF_PRODUCTS          PRODUCTID                          1
SYS_C009819  DF_ORDERDETAILS      ORDERID                            1
SYS_C009819  DF_ORDERDETAILS      PRODUCTID                          2
14 rows selected.

輸出內容意味著什麼?你可以立即判斷出這樣一個事實,不是所有表上都有主鍵,有15個表,但這裡只返回了14行(或如果使用distinct的話,只返回11行),為什麼我們會知道這個結果?因為創建主鍵時會自動創建一個索引,如果你在載入腳本中禁用了所有的引用完整性約束,你還會懷疑什麼呢?

Oracle推薦將索引列作為經常訪問的關聯列,常見的規則是索引列用於where子句,缺少索引,你應該懷疑“create  table”部分沒有創建外鍵索引列。

下面的查詢顯示了帶外鍵的表名/列名:

SQL> select a.constraint_name, b.constraint_type, 
2  a.table_name, a.column_name
3  from user_cons_columns a, all_constraints b
4  where a.constraint_name=b.constraint_name
5  and constraint_type = 'R';
CONSTRAINT_NAME  C TABLE_NAME           COLUMN_NAME
---------------- - -------------------- -------------
SYS_C009831      R DF_MOVIE_EMPLOYEE    SUPERVISORID
SYS_C009828      R DF_MOVIE_STORE       DISTRICTID
SYS_C009821      R DF_ORDERDETAILS      PRODUCTID
SYS_C009820      R DF_ORDERDETAILS      ORDERID
DFMOVIESTOREFK2  R DF_MOVIE_STORE       MANAGERID
SYS_C009852      R DF_MOVIE_RENTAL      TAPEID
SYS_C009851      R DF_MOVIE_RENTAL      CUSTID
SYS_C009838      R DF_MOVIE_CUSTOMER    STOREID
SYS_C009814      R DF_ORDERS            CUSTID
SYS_C009846      R DF_MOVIE_TAPE        MOVIEID
DFMOVIEEMPFK2    R DF_MOVIE_EMPLOYEE    STOREID
11 rows selected.

這是最終結果嗎?懷疑被證實了,外鍵沒有被索引。

從管理和維護角度來看,為什麼只有兩個約束是明確命名的而剩下的都是系統命名的?實際上是這個方案總共有51個約束,這裡只是發生了兩個命名的約束。

小結

從這次探索來看,工具如DataFactory或你自己編寫的腳本最關鍵的地方是能夠產生百萬計的測試樣本數據,如果不能保證引用完整性,或在數據建模方面的最佳實踐,它有什麼好處?如果你打算在應用程序上調試查詢,測試數據需要反應應用程序如何使用它。如果你依賴於數據完整性,你的測試數據需要支持並遵從父表/子表關聯關系。

從設計的立場來看,有兩個最佳實踐容易被違背,其一是外鍵列無索引,其二是3個主要項目(主鍵、外鍵和索引)沒有明確的名字。第三個可能是在表上無主鍵,難道在每個表上都需要主鍵嗎?不是!但大多數情況下,每個表都需要主鍵,即使沒有主鍵,你也要知道為什麼沒有設置主鍵,換句話說,不規范的表應該是有意識的決定,而不是失敗。

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved