有關ADDM試驗總結:
上次試驗的是通過OEM介面管理的addm，操作的確很簡便，但是我的多數的資料庫都是不能通過OEM連線的，這讓我很是鬱悶，今天試驗的就是如何通過命令列實現相同的操作：
SQL> @$ORACLE_HOME/rdbms/admin/addmrpt.sql
Current Instance -----列出當前例項的基本資訊
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
3080786508 **** 1 ****
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
* 3080786508 1 ****** ****** ****.***.***
* 3080786508 1 ****** ****** localhost.localdomain
Using 3080786508 for database Id
Using 1 for instance number
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing
specifying a number lists all completed snapshots.
Listing the last 3 days of Completed Snapshots
Snap ----列出最近三天的snapshot以供選擇
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
****** ****** 1693 22 Mar 2005 00:00 1
1694 22 Mar 2005 01:00 1
1695 22 Mar 2005 02:00 1
1696 22 Mar 2005 03:01 1
1697 22 Mar 2005 04:00 1
1698 22 Mar 2005 05:00 1
1699 22 Mar 2005 06:00 1
1700 22 Mar 2005 07:00 1
1701 22 Mar 2005 08:00 1
1702 22 Mar 2005 09:00 1
1703 22 Mar 2005 10:01 1
1704 22 Mar 2005 11:00 1
1705 22 Mar 2005 12:00 1
1706 22 Mar 2005 13:00 1
1707 22 Mar 2005 14:00 1
1708 22 Mar 2005 15:00 1
1709 22 Mar 2005 16:00 1
1710 22 Mar 2005 17:01 1
1711 22 Mar 2005 18:00 1
1712 22 Mar 2005 19:00 1
1713 22 Mar 2005 20:00 1
1714 22 Mar 2005 21:00 1
1715 22 Mar 2005 22:00 1
1716 22 Mar 2005 23:00 1
1717 23 Mar 2005 00:01 1
1718 23 Mar 2005 01:00 1
1719 23 Mar 2005 02:00 1
1720 23 Mar 2005 03:00 1
1721 23 Mar 2005 04:00 1
1722 23 Mar 2005 05:00 1
1723 23 Mar 2005 06:01 1
1724 23 Mar 2005 07:00 1
1725 23 Mar 2005 08:00 1
1726 23 Mar 2005 09:00 1
1727 23 Mar 2005 10:00 1
1728 23 Mar 2005 11:00 1
1729 23 Mar 2005 12:01 1
1730 23 Mar 2005 13:00 1
1731 23 Mar 2005 14:00 1
1732 23 Mar 2005 15:00 1
1733 23 Mar 2005 16:00 1
1734 23 Mar 2005 17:00 1
1735 23 Mar 2005 18:00 1
1736 23 Mar 2005 19:00 1
1737 23 Mar 2005 20:00 1
1738 23 Mar 2005 21:00 1
1739 23 Mar 2005 22:00 1
1740 23 Mar 2005 23:00 1
1741 24 Mar 2005 00:00 1
1742 24 Mar 2005 01:00 1
1743 24 Mar 2005 02:00 1
1744 24 Mar 2005 03:00 1
1745 24 Mar 2005 04:01 1
1746 24 Mar 2005 05:00 1
1747 24 Mar 2005 06:00 1
Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
****** ****** 1748 24 Mar 2005 07:00 1
1749 24 Mar 2005 08:00 1
1750 24 Mar 2005 09:00 1
1751 24 Mar 2005 10:00 1
Specify the Begin and End Snapshot Ids ----定義起始快照
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 1733
Begin Snapshot Id specified: 1733
Enter value for end_snap: 1734
End Snapshot Id specified: 1734
Specify the Report Name ----定義輸出檔案的名稱，預設的路徑在當前路徑，檔名是addmrpt_1_beginsnap_endsnap
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is addmrpt_1_1733_1734.txt. To use this name,
press
Enter value for report_name:
Using the report name addmrpt_1_1733_1734.txt
Running the ADDM analysis on the specified pair of snapshots ...
Generating the ADDM report for this analysis ... -------輸出報告
DETAILED ADDM REPORT FOR TASK 'TASK_1745' WITH ID 1745
------------------------------------------------------
Analysis Period: 23-MAR-2005 from 16:00:16 to 17:00:51
Database ID/Instance: 3080786508/1
Database/Instance Names: ******/******
Host Name: ****.***.***
Database Version: 10.1.0.3.0
Snapshot Range: from 1733 to 1734
Database Time: 61452 seconds
Average Database Load: 16.9 active sessions
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
FINDING 1: 16% impact (10104 seconds) -----------只提到兩個消耗很大的SQL,其實是一個SQL,可能兩個快照都用到了.
-------------------------------------
Contention on buffer cache latches was consuming significant database time.
RECOMMENDATION 1: SQL Tuning, 16% benefit (10071 seconds)
ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID
"8n6hx2vpk7uuk".
RELEVANT OBJECT: SQL statement with SQL_ID 8n6hx2vpk7uuk
select count(*) as x0_0_ from ******** user0_,
.......
SYMPTOMS THAT LED TO THE FINDING:
Wait class "Concurrency" was consuming significant database time. (17%
impact [10417 seconds])
FINDING 2: 12% impact (7597 seconds)
------------------------------------
SQL statements consuming significant database time were found.
RECOMMENDATION 1: SQL Tuning, 12% benefit (7597 seconds)
ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID
"8n6hx2vpk7uuk".
RELEVANT OBJECT: SQL statement with SQL_ID 8n6hx2vpk7uuk and
PLAN_HASH 1725833874
select count(*) as x0_0_ from ******** user0_,
.......
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ADDITIONAL INFORMATION
----------------------
Wait class "Administrative" was not consuming significant database time.
Wait class "Application" was not consuming significant database time.
Wait class "Cluster" was not consuming significant database time.
Wait class "Commit" was not consuming significant database time.
Wait class "Configuration" was not consuming significant database time.
CPU was not a bottleneck for the instance.
Wait class "Network" was not consuming significant database time.
Wait class "Scheduler" was not consuming significant database time.
Wait class "Other" was not consuming significant database time.
Wait class "User I/O" was not consuming significant database time.
The analysis of I/O performance is based on the default assumption that the
average read time for one database block is 10000 micro-seconds.
An explanation of the terminology used in this report is available when you
run the report with the 'ALL' level of detail.
後面是對上面提到的sql呼叫DBMS_SQLTUNE的試驗：
開始我以為是要手工錄入SQL的：
先建立一個任務：
DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
BEGIN
my_sqltext := 'select count(*) ' ||
'from ******** a, ....... ';
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => my_sqltext,
bind_list => sql_binds(anydata.ConvertVarchar(100),anydata.ConvertVarchar(100)),
user_name => '****',
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name => 'my_sql_tuning_task',
description => 'tt');
END;
/
--開始分析
BEGIN
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task' );
END;
/
--顯示分析結果
SET LONG 1000
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_sql_tuning_task')
FROM DUAL;
--刪除分析任務
BEGIN
DBMS_SQLTUNE.DROP_TUNING_TASK ( task_name => 'my_sql_tuning_task' );
END;
/
--以下就是上面分析的SQL的結果：
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : my_sql_tuning_task
Scope : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status : COMPLETED
Started at : 03/24/2005 10:57:14
Completed at : 03/24/2005 10:57:14
-------------------------------------------------------------------------------
SQL ID : 0p1hhrqdhr6xy
SQL Text: select count(*) from ******** a,
......
-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 16.56%)
------------------------------------------
Consider accepting the recommended SQL profile.
execute :profile_name := dbms_sqltune.accept_sql_profile(task_name =>
'my_sql_tuning_task')
2- Restructure SQL finding (see plan 1 in explain plans section)
----------------------------------------------------------------
An expensive cartesian product operation was found at line ID 6 of the
execution plan.
Recommendation
--------------
Consider removing the disconnected table or view from this statement or
add a join condition which refers to it.
Rationale
---------
A cartesian product should be avoided whenever possible because it is an
expensive operation and might produce a large amount of data.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original With Adjusted Cost
------------------------------
Plan hash value: 3214013929
-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 50 | 6 (0)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 50 | | | | |
| 2 | FILTER | | | | | | | |
| 3 | TABLE ACCESS BY LOCAL INDEX ROWID| ******************** | 1 | 14 | 3 (0)| 00:00:01 | | |
| 4 | NESTED LOOPS | | 1 | 50 | 6 (0)| 00:00:01 | | |
| 5 | NESTED LOOPS | | 1 | 36 | 3 (0)| 00:00:01 | | |
| 6 | MERGE JOIN CARTESIAN | | 1 | 32 | 3 (0)| 00:00:01 | | |
| 7 | NESTED LOOPS | | 1 | 20 | 1 (0)| 00:00:01 | | |
| 8 | INDEX UNIQUE SCAN | PK_*********** | 1 | 6 | 0 (0)| 00:00:01 | | |
| 9 | TABLE ACCESS BY INDEX ROWID | ******** | 1 | 14 | 1 (0)| 00:00:01 | | |
| 10 | INDEX RANGE SCAN | ID_********_USERNAME | 1 | | 0 (0)| 00:00:01 | | |
| 11 | BUFFER SORT | | 1 | 12 | 2 (0)| 00:00:01 | | |
| 12 | TABLE ACCESS FULL | ********************* | 1 | 12 | 2 (0)| 00:00:01 | | |
| 13 | INDEX UNIQUE SCAN | PK_************* | 1 | 4 | 0 (0)| 00:00:01 | | |
| 14 | PARTITION RANGE ITERATOR | | 3 | | 2 (0)| 00:00:01 | KEY | KEY |
| 15 | INDEX RANGE SCAN | ID_******************** | 3 | | 2 (0)| 00:00:01 | KEY | KEY |
-------------------------------------------------------------------------------------------------------------------------------
2- Using SQL Profile
--------------------
Plan hash value: 110509970
------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 50 | 5 (0)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 50 | | | | |
| 2 | FILTER | | | | | | | |
| 3 | NESTED LOOPS | | 1 | 50 | 5 (0)| 00:00:01 | | |
| 4 | NESTED LOOPS | | 1 | 46 | 5 (0)| 00:00:01 | | |
| 5 | NESTED LOOPS | | 1 | 34 | 4 (0)| 00:00:01 | | |
| 6 | NESTED LOOPS | | 1 | 20 | 1 (0)| 00:00:01 | | |
| 7 | INDEX UNIQUE SCAN | PK_*********** | 1 | 6 | 0 (0)| 00:00:01 | | |
| 8 | TABLE ACCESS BY INDEX ROWID | ******** | 1 | 14 | 1 (0)| 00:00:01 | | |
| 9 | INDEX RANGE SCAN | ID_********_USERNAME | 1 | | 0 (0)| 00:00:01 | | |
| 10 | PARTITION RANGE ITERATOR | | 1 | 14 | 3 (0)| 00:00:01 | KEY | KEY |
| 11 | TABLE ACCESS BY LOCAL INDEX ROWID| ******************** | 1 | 14 | 3 (0)| 00:00:01 | KEY | KEY |
| 12 | INDEX RANGE SCAN | ID_******************** | 3 | | 2 (0)| 00:00:01 | KEY | KEY |
| 13 | TABLE ACCESS BY INDEX ROWID | ********************* | 1 | 12 | 1 (0)| 00:00:01 | | |
| 14 | INDEX UNIQUE SCAN | PK_********************* | 1 | | 0 (0)| 00:00:01 | | |
| 15 | INDEX UNIQUE SCAN | PK_************* | 1 | 4 | 0 (0)| 00:00:01 | | |
------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------
後來看到別人的例子中是可以把SQL_ID作為分析的輸入引數的,於是:
DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
BEGIN
my_task_name := DBMS_SQLTUNE.create_tuning_task(
begin_snap=>1726,
end_snap=>1736,
sql_id=>'8n6hx2vpk7uuk',
scope=>DBMS_SQLTUNE.scope_comprehensive,
time_limit=>60,
task_name=>'my_sql_tuning_task',
description => 'tt');
END;
/
--開始分析
BEGIN
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task' );
END;
/
---結果他居然說他沒有啥建議,我狂鬱悶,白折騰半天
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : my_sql_tuning_task
Scope : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status : COMPLETED
Started at : 03/24/2005 14:41:19
Completed at : 03/24/2005 14:41:22
-------------------------------------------------------------------------------
SQL ID : 8n6hx2vpk7uuk
SQL Text: select count(*) as x0_0_ from USER_TAB user0_,
......
-------------------------------------------------------------------------------
There are no recommendations to improve the statement.
-------------------------------------------------------------------------------
--刪除分析任務
BEGIN
DBMS_SQLTUNE.DROP_TUNING_TASK ( task_name => 'my_sql_tuning_task' );
END;
/
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/51862/viewspace-180617/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 有關lock的一點測試總結！
- 考試經驗總結
- 自己做oracle試驗的相關總結之二Oracle
- 兩年測試經驗總結
- 有關dblink有域名的總結：
- 有關Oracle role的總結Oracle
- 測試經驗總結：測試員的角色
- JMeter測試WebSocket的經驗總結JMeterWeb
- 關於SCN的總結測試
- 有關role的一點總結!
- 關於筆試和麵試的反思總結筆試
- ZooKeeper和Curator相關經驗總結
- Mask RCNN測試過程經驗總結CNN
- 自己做oracle試驗的總結之一Oracle
- INNODB 熱備工具試驗與總結 薦
- 有關用8i的客戶端匯出10g的資料庫的試驗總結客戶端資料庫
- 效能測試關注點整理總結
- 關於ios多年面試的經驗總結iOS面試
- Visual Studio Code 試用體驗總結
- DataGuard寫測試實驗步驟及總結
- 一定要掌握的CCNA試驗考試命令總結(轉)
- EasyUI實戰經驗總結，給有需要的人UI
- Google Test交流與測試開發經驗總結Go
- 影像庫PIL相關筆試題大總結筆試
- 【NUMBER】有關Oracle NUMBER型別定義中precision和scale的測試和總結Oracle型別
- 一位測試大神的軟體測試工作經驗總結
- 測試總結①
- 面試總結:Promise有關面試的知識點。面試Promise
- 有關temp表空間的一點總結！
- 工作經驗總結
- vue經驗總結Vue
- flex實驗總結Flex
- mysql經驗總結MySql
- 實驗一 總結
- Java經驗總結Java
- Storm經驗總結ORM
- Resin 經驗總結
- 有關連結串列的小技巧，我都給你總結好了