監控索引的使用(轉)

zhouwf0726發表於2019-05-27
索引
如何監控索引的使用？　　研究發現，oracle資料庫使用的索引不會超過總數的25%，或者不易他們期望被使用的方式使用。通過 監控資料庫索引的使用，釋放那些未被使用的索引，從而節省維護索引的開銷，優化效能。
　　
　　1、在oracle8i中，確定使用了那個索引的方法意味著要對存在語共享SQL區中的所有語句執行EXPLIAN PALN，然後查詢計劃表中的OPERATION列，從而識別有OBJECT_OWNER和OBJECT_NAME列所確定的那個索引上的索引訪問。
　　下面是一個監控索引使用的指令碼，這個指令碼僅僅是一個樣品，在某種條件下成立：
　　
　　條件：
　　執行這個指令碼的使用者擁有許可權解釋所有的v$sqlarea中的sql，除了不是被SYS裝載的。
　　plan_table.remarks能夠別用來決定與特權習慣的錯誤。
　　對所有的共享池中SQL，引數OPTIMIZER_GOAL是一個常量，無視v$sqlarea.optimizer_mode。
　　兩次快照之間，統計資料被再次分析過。
　　沒有語句別截斷。
　　所有的物件都是區域性的。
　　所有被引用的表或檢視或者是被執行指令碼的使用者所擁有，或者完全有資格的名字或同義詞被使用。
　　自從上次快照以來，沒有不受"歡迎"的語句被沖洗出共享池(例如，在裝載)。
　　對於所有的語句， v$sqlarea.version_count = 1 (children)。
　　
　　指令碼:
　　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;
　　
　　
　　
　　關於這個指令碼，有幾個重要的地方需要注意，即它可能一起明顯的開銷，因此，應該在仔細地進行 權衡後才把它應用到繁忙的生產應用系統中區。
　　
　　2、oracle9i中如何確定索引的使用情況
　　在oracle9i中，情況會簡單得多，因為有一個新得字典檢視V$SQL_PLAN儲存了實際計劃，這些計劃用於執行共享SQL區中得語句。V$SQL_PLAN檢視很類似與計劃表，但V$SQL_PLAN使用ADDRESS和HASH_VALUE列 來識別語句， 而計劃表使用使用者提供得STATEMENT_ID來識別語句。下面的SQL顯示了在一個oracle9i資料庫中，由出現在共享SQL區中語句使用的所有索引
　　[/code]
　　select object_owner, object_name, options, count(*)
　　from　 v$sql_plan
　　where　operation='INDEX'
　　and　　object_owner!='SYS'
　　group　by　object_owner, object_name, operation, options
　　order　by count(*) desc;
　　[/code]
　　
　　所有基於共享SQL區中的信心來識別索引使用情況的方法， 都可能會收集到不完整的資訊。共享SQL區是一　個動態結構，除非能對它進行足夠頻繁的取樣， 否則在有關索引使用的情況的資訊被收集之前，SQL語句可 能就已經(因為老化)被移出快取了。oracle9i提供瞭解決這個問題的方案，即它為alter index提供了一個
　　monitoring usage子句。當啟用monitoring usage 時，oralce記錄簡單的yes或no值，以指出在監控間隔 期間某個索引是否被使用。
　　
　　為了演示這個新特性，你可以使用下面的例子:
　　(a) Create and populate a small test table
　　(b) Create Primary Key index on that table
　　(c) Query v$object_usage: the monitoring has not started yet
　　(d) Start monitoring of the index usage
　　(e) Query v$object_usage to see the monitoring in progress
　　(f) Issue the SELECT statement which uses the index
　　(g) Query v$object_usage again to see that the index has been used
　　(h) Stop monitoring of the index usage
　　(i) Query v$object_usage to see that the monitoring sDetailed steps:
　　
　　(a) Create and populate a small test table
　　create table products　　(
　　prod_id number(3),
　　prod_name_code varchar2(5));
　　
　　insert into products values(1,'aaaaa');
　　insert into products values(2,'bbbbb');
　　insert into products values(3,'ccccc');
　　insert into products values(4,'ddddd');
　　commit;
　　
　　(b) Create Primary Key index on that table
　　alter table products　　add (constraint products_pk primary key (prod_id));
　　
　　(c) Query v$object_usage: the monitoring has not started yet
　　column 如何監控索引的使用？　　研究發現，oracle資料庫使用的索引不會超過總數的25%，或者不易他們期望被使用的方式使用。通過 監控資料庫索引的使用，釋放那些未被使用的索引，從而節省維護索引的開銷，優化效能。
　　
　　1、在oracle8i中，確定使用了那個索引的方法意味著要對存在語共享SQL區中的所有語句執行EXPLIAN PALN，然後查詢計劃表中的OPERATION列，從而識別有OBJECT_OWNER和OBJECT_NAME列所確定的那個索引上的索引訪問。
　　下面是一個監控索引使用的指令碼，這個指令碼僅僅是一個樣品，在某種條件下成立：
　　
　　條件：
　　執行這個指令碼的使用者擁有許可權解釋所有的v$sqlarea中的sql，除了不是被SYS裝載的。
　　plan_table.remarks能夠別用來決定與特權習慣的錯誤。
　　對所有的共享池中SQL，引數OPTIMIZER_GOAL是一個常量，無視v$sqlarea.optimizer_mode。
　　兩次快照之間，統計資料被再次分析過。
　　沒有語句別截斷。
　　所有的物件都是區域性的。
　　所有被引用的表或檢視或者是被執行指令碼的使用者所擁有，或者完全有資格的名字或同義詞被使用。
　　自從上次快照以來，沒有不受"歡迎"的語句被沖洗出共享池(例如，在裝載)。
　　對於所有的語句， v$sqlarea.version_count = 1 (children)。
　　
　　指令碼:
　　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;
　　
　　
　　
　　關於這個指令碼，有幾個重要的地方需要注意，即它可能一起明顯的開銷，因此，應該在仔細地進行 權衡後才把它應用到繁忙的生產應用系統中區。
　　
　　2、oracle9i中如何確定索引的使用情況
　　在oracle9i中，情況會簡單得多，因為有一個新得字典檢視V$SQL_PLAN儲存了實際計劃，這些計劃用於執行共享SQL區中得語句。V$SQL_PLAN檢視很類似與計劃表，但V$SQL_PLAN使用ADDRESS和HASH_VALUE列 來識別語句， 而計劃表使用使用者提供得STATEMENT_ID來識別語句。下面的SQL顯示了在一個oracle9i資料庫中，由出現在共享SQL區中語句使用的所有索引
　　[/code]
　　select object_owner, object_name, options, count(*)
　　from　 v$sql_plan
　　where　operation='INDEX'
　　and　　object_owner!='SYS'
　　group　by　object_owner, object_name, operation, options
　　order　by count(*) desc;
　　[/code]
　　
　　所有基於共享SQL區中的信心來識別索引使用情況的方法， 都可能會收集到不完整的資訊。共享SQL區是一　個動態結構，除非能對它進行足夠頻繁的取樣， 否則在有關索引使用的情況的資訊被收集之前，SQL語句可 能就已經(因為老化)被移出快取了。oracle9i提供瞭解決這個問題的方案，即它為alter index提供了一個
　　monitoring usage子句。當啟用monitoring usage 時，oralce記錄簡單的yes或no值，以指出在監控間隔 期間某個索引是否被使用。
　　
　　為了演示這個新特性，你可以使用下面的例子:
　　(a) Create and populate a small test table
　　(b) Create Primary Key index on that table
　　(c) Query v$object_usage: the monitoring has not started yet
　　(d) Start monitoring of the index usage
　　(e) Query v$object_usage to see the monitoring in progress
　　(f) Issue the SELECT statement which uses the index
　　(g) Query v$object_usage again to see that the index has been used
　　(h) Stop monitoring of the index usage
　　(i) Query v$object_usage to see that the monitoring sDetailed steps:
　　
　　(a) Create and populate a small test table
　　create table products　　(
　　prod_id number(3),
　　prod_name_code varchar2(5));
　　
　　insert into products values(1,'aaaaa');
　　insert into products values(2,'bbbbb');
　　insert into products values(3,'ccccc');
　　insert into products values(4,'ddddd');
　　commit;
　　
　　(b) Create Primary Key index on that table
　　alter table products　　add (constraint products_pk primary key (prod_id));
　　
　　(c) Query v$object_usage: the monitoring has not started yet
　　column

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/756652/viewspace-242295/，如需轉載，請註明出處，否則將追究法律責任。

相關文章