上次试验的是通过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 without
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 to continue, otherwise enter an alternative.
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/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/51862/viewspace-180617/
901
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
