LoadRunner 9.0
創(chuàng)新互聯(lián)公司專注于邵東網(wǎng)站建設(shè)服務(wù)及定制,我們擁有豐富的企業(yè)做網(wǎng)站經(jīng)驗。 熱誠為您提供邵東營銷型網(wǎng)站建設(shè),邵東網(wǎng)站制作、邵東網(wǎng)頁設(shè)計、邵東網(wǎng)站官網(wǎng)定制、微信平臺小程序開發(fā)服務(wù),打造邵東網(wǎng)絡(luò)公司原創(chuàng)品牌,更為您提供邵東網(wǎng)站排名全網(wǎng)營銷落地服務(wù)。
Sitescope 9.0
Windows 2003
Oracle database 10g
1. 使用LR自帶的監(jiān)控引擎
1.1.在LR的controller上安裝oracle客戶端
這一步就不用說了,安裝直接Setup,安裝就OK了。
1,安裝完后,先配置一下Net Configuration Assistant。記住配置的服務(wù)名。
配置成功會顯示:正在連接...測試成功。
2,用sqlplus連接一下,看是否可以連接成功,打開sqlplus輸入oracle用戶名密碼和主機字符串。
查看是否登錄成功。
1.2.添加oracle計數(shù)器
3,登錄成功后,打開LR的controller.,在可用圖中選擇oracle,點擊add measurements,再點擊Advanced,如下所示:
這里我們用LR native monitors。
4,在Monitored Server Machines區(qū)域,添加oracle服務(wù)器所在的IP。
5,再在Resource Measurements on:IP區(qū)域點擊添加,彈出對話框如下:
6,輸入相應(yīng)的信息,這里的orcl就是前面在Net Configuration Assistant配置的服務(wù)名。
7,點擊OK,這里我們應(yīng)該可以看到可以添加oracle的計數(shù)器了,如下所示:
2. 使用Sitescope引擎
不需要配置Net Configuration Assistant。
1,在第一個圖choose monitor engine中選擇sitescope,然后在在Monitored Server Machines區(qū)域點擊Add如下所示:
在這里可以選擇本地或者其他機器的sitescope,如果sitescope啟用了aclearcase/" target="_blank" ccount的驗證,也要寫上相應(yīng)的用戶名密碼。
2,在Resource Measurements on:IP區(qū)域點擊添加,彈出對話框如下:
3,輸入信息如圖。點擊OK,如下:
至此就可以監(jiān)控oracle了。
在數(shù)據(jù)庫管理與維護中,我們總會遇到一個問題:我們創(chuàng)建的索引是否會被某些SQL語句使用呢?換個通俗表達方式:我創(chuàng)建的索引是否是未使用的索引(unused Indexes),是否有價值呢?如果創(chuàng)建的某個索引是Unused Indexes,尤其是沒有合理規(guī)劃索引的系統(tǒng)或那些管理控制不規(guī)范的系統(tǒng)。有可能建立了N個索引,其實有些索引都是沒有任何SQL會使用,那么此時這些多余的索引其實會帶來兩個問題:1:浪費存儲空間,尤其是大表的索引,浪費的存儲空間尤其可觀; 2:加重DML操作(UPDATE、INSERT、DELETE)的開銷。
ORACLE其實提供了監(jiān)控索引使用情況的功能。ALTER INDEX index_name MONITORING USAGE; 我們下面來測試驗證一下吧。
創(chuàng)建一個表TEST作為實驗測試驗證的樣例
CREATE TABLE TEST
(
ID NUMBER(10),
NAME VARCHAR2(32)
);
CREATE INDEX IDX_TEST_ID ON TEST(ID);
INSERT INTO TEST
SELECT 1001, 'Kerry' FROM DUAL UNION ALL
SELECT 1002, 'Ken' FROM DUAL UNION ALL
SELECT 1003, 'Jimmy' FROM DUAL UNION ALL
SELECT 1004, 'Jack' FROM DUAL;
COMMIT;
execute dbms_stats.gather_table_stats(ownname = 'ETL', tabname ='TEST', estimate_percent =DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt = 'FOR ALL COLUMNS SIZE AUTO');啟用對索引IDX_TEST_ID的監(jiān)控
ALTER INDEX IDX_TEST_ID MONITORING USAGE;此時觀察V$OBJECT_USAGE表數(shù)據(jù)的變化,如下所示,MONITORIN字段值變?yōu)閅ES,表示索引IDX_TEST_ID已經(jīng)被置于監(jiān)控狀態(tài)。USED字段為NO表示暫時沒有SQL使用該索引SQL COL INDEX_NAME FOR A20
SQL COL TABLE_NAME FOR A10
SQL COL MONITORING FOR A10
SQL COL USED FOR A10
SQL COL START_MONITORING FOR A20
SQL COL END_MONITORING FOR A20
SQL SELECT * FROM V$OBJECT_USAGE;
INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING------------ ---------- ---------- ---- -------------------- ----------------IDX_TEST_ID TEST YES NO 11/28/2015 14:57:41此時我們執(zhí)行下面SQL,因為此時使用全表掃描,那么索引IDX_TEST_ID依然沒有被使用,此時可以查看V$OBJECT_USAGE進行驗證。
SQL SET AUTOTRACE ON;
SQL SELECT * FROM TEST WHERE ID =1001;
ID NAME
---------- --------------------------------1001 Kerry
Execution Plan
--------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost |----------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 9 | 2 || 1 | TABLE ACCESS FULL| TEST | 1 | 9 | 2 |----------------------------------------------------------Note
-----
- 'PLAN_TABLE' is old version
Statistics
----------------------------------------------------------1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
578 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
clip_image001
如下所示,此時索引IDX_TEST_ID依然沒有被使用。
clip_image002
我們使用索引提示強制下面SQL使用索引IDX_TEST_IDSELECT /*+ INDEX(TEST IDX_TEST_ID) */* FROM TEST WHERE ID =1001;此時你就會發(fā)現(xiàn)USED的值變?yōu)榱薡ES了。
clip_image003
ALTER INDEX IDX_TEST_ID NOMONITORING USAGE;執(zhí)行上面命令后,在V$OBJECT_USAGE表中,就會更新表TEST記錄的END_MONITORING、MONITORING的值。
clip_image004
如果你又啟用監(jiān)控索引使用情況,那么系統(tǒng)會更新START_MONITORING、END_MONITORING字段的值(END_MONITORING的值更新為NULL)。如果刪除表TEST,此時你會發(fā)現(xiàn)V$OBJECT_USAGE對象中關(guān)于表TEST的記錄也不見了。
注意:SELECT * FROM V$OBJECT_USAGE; 只能查看當(dāng)前用戶下被監(jiān)控的索引信息。即使sys、system用戶也不能查看其它用戶的信息。
在測試過程中有個小疑問,就是在準備測試環(huán)境時,如果不對表收集統(tǒng)計信息的話,那么即使SQL走全表掃描,你依然發(fā)現(xiàn)V$OBJECT_USAGE中索引被標記使用了。如下所示DROP TABLE TEST PURGE;
CREATE TABLE TEST
(
ID NUMBER(10),
NAME VARCHAR2(32)
);
CREATE INDEX IDX_TEST_ID ON TEST(ID);
INSERT INTO TEST
SELECT 1001, 'Kerry' FROM DUAL UNION ALL
SELECT 1002, 'Ken' FROM DUAL UNION ALL
SELECT 1003, 'Jimmy' FROM DUAL UNION ALL
SELECT 1004, 'Jack' FROM DUAL;
COMMIT;
ALTER INDEX IDX_TEST_ID MONITORING USAGE;SQL SET AUTOTRACE ON;
SQL SELECT * FROM TEST WHERE ID =1001;
ID NAME
---------- --------------------------------1001 Kerry
Execution Plan
--------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost |----------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 31 | 2 || 1 | TABLE ACCESS FULL| TEST | 1 | 31 | 2 |----------------------------------------------------------Note
-----
- 'PLAN_TABLE' is old version
Statistics
----------------------------------------------------------7 recursive calls
0 db block gets
10 consistent gets
0 physical reads
0 redo size
578 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL SELECT * FROM V$OBJECT_USAGE;
INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING------------ ---------- ---------- ------ ----------- -------------------IDX_TEST_ID TEST YES YES 11/28/2015 15:11:46那么為什么呢? 猜測是在解析生成執(zhí)行計劃時,用到了索引的一些信息,導(dǎo)致V$OBJECT_USAGE表中的字段USED被標記為YES。
如果我們想在系統(tǒng)中監(jiān)控所有的索引,那么我們可以通過下面腳本實現(xiàn)監(jiān)控數(shù)據(jù)庫所有的索引。注意我們要排除一些系統(tǒng)表的索引、以及LOB indexes。原因有下面兩個:
1:LOB indexes不能修改,否則會報ORA-22864錯誤(ORA-22864: cannot ALTER or DROP LOB indexes)。
2:ORA-00701: object necessary for warmstarting database cannot be alteredORA-00701: object necessary for warmstarting database cannot be altered00701. 00000 - "object necessary for warmstarting database cannot be altered"*Cause: Attempt to alter or drop a database object (table, cluster, orindex) which are needed for warmstarting the database.
*Action: None.
SET PAGES 999;
SET HEADING OFF;
SPOOL run_monitor.sql
SELECT
'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' MONITORING USAGE;'
FROM
DBA_INDEXES
WHERE
INDEX_TYPE != 'LOB' AND OWNER NOT IN ('SYS', 'SYSMAN', 'SYSTEM', 'MDSYS', 'WMSYS', 'TSMSYS', 'DBSNMP', 'OUTLN');
SPOOL OFF;
@run_monitor.sql
此時使用下面腳本就能查出那些索引是未使用索引,當(dāng)然監(jiān)控索引時長非常重要,太短的話有可能導(dǎo)致查詢出來的數(shù)據(jù)有問題,一般建議監(jiān)控一周后即可,OLAP系統(tǒng)則需要適當(dāng)延長監(jiān)控的時間。
SELECT I.TABLE_OWNER,
T.TABLE_NAME,
I.INDEX_NAME,
U.USED,
U.START_MONITORING,
U.END_MONITORING
FROM USER_TABLES T
INNER JOIN USER_INDEXES I
ON T.TABLE_NAME = I.TABLE_NAME
INNER JOIN V$OBJECT_USAGE U
ON U.TABLE_NAME = I.TABLE_NAME
AND I.INDEX_NAME = U.INDEX_NAME
WHERE I.TABLE_OWNER=SYS_CONTEXT('USERENV','CURRENT_USER')另外,博客Oracle - Find unused Indexes中介紹了一個查找沒有使用索引的SQL語句。如下所示statspack_unused_indexes.sqlcol owner heading "Index Owner" format a30col index_name heading "Index Name" format a30set linesize 95 trimspool on pagesize 80
select *
from
(select
owner,
index_name
from
dba_indexes di
where
di.index_type != 'LOB'
and
owner not in ('SYS', 'SYSMAN', 'SYSTEM', 'MDSYS', 'WMSYS', 'TSMSYS', 'DBSNMP', 'OUTLN')minus
select
index_owner owner,
index_name
from
dba_constraints dc
where
index_owner not in ('SYS', 'SYSMAN', 'SYSTEM', 'MDSYS', 'WMSYS', 'TSMSYS', 'DBSNMP', 'OUTLN')minus
select
p.object_owner owner,
p.object_name index_name
from
stats$snapshot sn,
stats$sql_plan p,
stats$sql_summary st,
stats$sql_plan_usage spu
where
st.sql_id = spu.sql_id
and
spu.plan_hash_value = p.plan_hash_value
and
st.hash_value = p.plan_hash_value
and
sn.snap_id = st.snap_id
and
sn.dbid = st.dbid
and
sn.instance_number = st.instance_number
and
sn.snap_id = spu.snap_id
and
sn.dbid = spu.snap_id
and
sn.instance_number = spu.instance_number
and
sn.snap_id between begin_snap and end_snapand
p.object_type = 'INDEX'
)
where owner not in ('SYS', 'SYSMAN', 'SYSTEM', 'MDSYS', 'WMSYS', 'TSMSYS', 'DBSNMP', 'OUTLN')order by 1, 2
/
這里是另一個腳本用來跟蹤未使用的索引并展示給所有索引的調(diào)用計數(shù)。最重要的是,這個腳本顯示了多列索引引用的列(這個腳本執(zhí)行時間較長,資源開銷較大。)col c1 heading 'Begin|Interval|time' format a20col c2 heading 'Search Columns' format 999col c3 heading 'Invocation|Count' format 99,999,999break on c1 skip 2
accept idxname char prompt 'Enter Index Name: '
ttitle 'Invocation Counts for index|idxname'
select
to_char(sn.begin_interval_time,'yy-mm-dd hh24') c1,p.search_columns c2,count(*) c3from
dba_hist_snapshot sn,
dba_hist_sql_plan p,
dba_hist_sqlstat st
where
st.sql_id = p.sql_id
and
sn.snap_id = st.snap_id
and
p.object_name = 'idxname'
group by
begin_interval_time,search_columns;
通過 監(jiān)控數(shù)據(jù)庫索引的使用,釋放那些未被使用的索引,從而節(jié)省維護索引的開銷,優(yōu)化性能。 1、在oracle8i中,確定使用了那個索引的方法意味著要對存在語共享SQL區(qū)中的所有語句運行EXPLIAN PALN,然后查詢計劃表中的OPERATION列,從而識別有OBJECT_OWNER和OBJECT_NAME列所確定的那個索引上的索引訪問。 下面是一個監(jiān)控索引使用的腳本,這個腳本僅僅是一個樣品,在某種條件下成立: 條件: 運行這個腳本的用戶擁有權(quán)限解釋所有的v$sqlarea中的sql,除了不是被SYS裝載的。plan_table.remarks能夠別用來決定與特權(quán)習(xí)慣的錯誤。對所有的共享池中SQL,參數(shù)OPTIMIZER_GOAL是一個常量,無視v$sqlarea.optimizer_mode。兩次快照之間,統(tǒng)計資料被再次分析過。沒有語句別截斷。所有的對象都是局部的。所有被引用的表或視圖或者是被運行腳本的用戶所擁有,或者完全有資格的名字或同義詞被使用。自從上次快照以來,沒有不受"歡迎"的語句被沖洗出共享池(例如,在裝載)。 腳本: Code: [Copy to clipboard] set echo off Rem Drop and recreate PLAN_TABLE for EXPLAIN PLAN drop table plan_table; create table PLAN_TABLE ( statement_id varchar2(30), timestamp date, remarks varchar2(80), operation varchar2(30), options varchar2(255), object_node varchar2(128), object_owner varchar2(30), object_name varchar2(30), object_instance numeric, object_type varchar2(30), optimizer varchar2(255), search_columns number, id numeric, parent_id numeric, position numeric, cost numeric, cardinality numeric, bytes numeric, other_tag varchar2(255), partition_start varchar2(255), partition_stop varchar2(255), partition_id numeric, other long, distribution varchar2(30), cpu_cost numeric, io_cost numeric, temp_space numeric, access_predicates varchar2(4000), filter_predicates varchar2(4000)); Rem Drop and recreate SQLTEMP for taking a snapshot of the SQLAREA drop table sqltemp; create table sqltemp ( ADDR VARCHAR2 (16), SQL_TEXT VARCHAR2 (2000), DISK_READS NUMBER, EXECUTIONS NUMBER, PARSE_CALLS NUMBER); set echo on Rem Create procedure to populate the plan_table by executing Rem explain plan...for 'sqltext' dynamically create or replace procedure do_explain ( addr IN varchar2, sqltext IN varchar2) as dummy varchar2 (1100); mycursor integer; ret integer; my_sqlerrm varchar2 (85); begin dummy:='EXPLAIN PLAN SET STATEMENT_ID=' ; dummy:=dummy||''''||addr||'''' ||' FOR '||sqltext; mycursor := dbms_sql.open_cursor; dbms_sql.parse(mycursor,dummy,dbms_sql.v7); ret := dbms_sql.execute(mycursor); dbms_sql.close_cursor(mycursor); commit; exception -- Insert errors into PLAN_TABLE... when others then my_sqlerrm := substr(sqlerrm,1,80); insert into plan_table(statement_id, remarks) values (addr,my_sqlerrm); -- close cursor if exception raised on EXPLAIN PLAN dbms_sql.close_cursor(mycursor); end; / Rem Start EXPLAINing all S/I/U/D statements in the shared pool declare -- exclude statements with v$sqlarea.parsing_schema_id = 0 (SYS) cursor c1 is select address, sql_text, DISK_READS, EXECUTIONS, PARSE_CALLS from v$sqlarea where command_type in (2,3,6,7) and parsing_schema_id != 0; cursor c2 is select addr, sql_text from sqltemp; addr2 varchar(16); sqltext v$sqlarea.sql_text%type; dreads v$sqlarea.disk_reads%type; execs v$sqlarea.executions%type; pcalls v$sqlarea.parse_calls%type; begin open c1; fetch c1 into addr2,sqltext, dreads,execs,pcalls; while (c1%found) loop insert into sqltemp values (addr2,sqltext,dreads,execs,pcalls); commit; fetch c1 into addr2, sqltext,dreads,execs,pcalls; end loop; close c1; open c2; fetch c2 into addr2, sqltext; while (c2%found) loop do_explain(addr2,sqltext); fetch c2 into addr2, sqltext; end loop; close c2; end; / Rem Generate a report of index usage based on the number of times Rem a SQL statement using that index was executed select p.owner, p.name, sum(s.executions) totexec from sqltemp s, (select distinct statement_id stid, object_owner owner, object_name name from plan_table where operation = 'INDEX') p where s.addr = p.stid group by p.owner, p.name order by 2 desc; Rem Perform cleanup on exit (optional) delete from plan_table where statement_id in ( select addr from sqltemp ); drop table sqltemp;關(guān)于這個腳本,有幾個重要的地方需要注意,即它可能一起明顯的開銷,因此,應(yīng)該在仔細地進行 權(quán)衡后才把它應(yīng)用到繁忙的生產(chǎn)應(yīng)用系統(tǒng)中區(qū)。