Oracle 10g

Oracle - . V$OBJECT_USAGE "Yes" "No".

:
- ,
-
- .

- ( , ).

AWR, "ORACLE INDEX USAGE TRACKING".
- (.. ). - , AWR .

:

1. .
2. , ( - )
3. .
4. JOB.

:

1.1. . sys V$SQL, V$SQL_PLAN, V$SQL_BIND_CAPTURE ( , V$SQL, V$SQL_PLAN .. ):

grant select on V_$SQL to schema_name;
grant select on V_$SQL_PLAN to schema_name;
grant select on V$SQL_BIND_CAPTURE to schema_name;

schema_name- .

1.2. :

-- V$SQL
CREATE TABLE monitoring_index_usage_table as
SELECT *
FROM v$sql s
WHERE s.hash_value IN
(SELECT v.hash_value FROM v$sql_plan v WHERE v.object_name = 'XXX');
-- Add/modify columns
-- Add/modify columns
ALTER TABLE monitoring_index_usage_table ADD what_mon VARCHAR2(100);
ALTER TABLE monitoring_index_usage_table add dt_mon date;
-- Add comments to the columns
COMMENT ON COLUMN monitoring_index_usage_table.what_mon
is ' ';
COMMENT ON COLUMN monitoring_index_usage_table.dt_mon
is ' ';
-- Create/Recreate indexes
CREATE INDEX idx_MONITORING_INDEX_USAGE_TABLE on MONITORING_INDEX_USAGE_TABLE (sql_id);

-- v$sql_plan -
CREATE TABLE monitoring_index_plans AS SELECT * FROM v$sql_plan WHERE ROWNUM = 0;

-- ( )
CREATE TABLE monitoring_sql_bind_capture as
SELECT sql_id,
name,
position,
datatype_string,
was_captured,
last_captured,
value_string
FROM v$sql_bind_capture
WHERE sql_id = '-----';

XXX - , , object_name

2.1. :

CREATE OR REPLACE PROCEDURE monitoring_sql_plans IS
BEGIN
-- ,
-- object_name

--

-- monitoring object_name on schema_name
INSERT INTO monitoring_index_usage_table
SELECT s.*, 'object_name usage', SYSDATE
FROM v$sql s
WHERE s.last_active_time > '14.02.2012 19:20'
AND s.parsing_schema_name = 'schema_name'
AND (s.address, s.hash_value) IN
(SELECT v.address, v.hash_value
FROM v$sql_plan v
WHERE v.object_name IN ('object_name')
AND v.object_owner = 'schema_name')

AND (address, hash_value) NOT IN
(SELECT address, hash_value FROM monitoring_index_usage_table);

FOR v_i IN (SELECT DISTINCT address, hash_value
FROM v$sql_plan
WHERE object_name IN ('object_name')
AND (address, hash_value) NOT IN
(SELECT address, hash_value FROM monitoring_index_plans)) LOOP
INSERT INTO monitoring_index_plans
SELECT *
FROM v$sql_plan v
WHERE v.hash_value = v_i.hash_value
AND v.address = v_i.address;
END LOOP;

---------------------------------------------------------------------------------
-- bind
FOR v_i IN (SELECT sql_id,
NAME,
position,
datatype_string,
was_captured,
last_captured,
value_string
FROM v$sql_bind_capture
WHERE sql_id IN
(SELECT DISTINCT sql_id
FROM monitoring_index_usage_table)
AND (sql_id, last_captured) NOT IN
(SELECT DISTINCT sql_id, last_captured
FROM monitoring_sql_bind_capture)) LOOP
INSERT INTO monitoring_sql_bind_capture
VALUES
(v_i.sql_id,
v_i.name,
v_i.position,
v_i.datatype_string,
v_i.was_captured,
v_i.last_captured,
v_i.value_string);
END LOOP;

END monitoring_sql_plans;

schema_name -

2.2. JOB ( ):
BEGIN
sys.dbms_job.submit(job => :job,
what => 'begin monitoring_sql_plans; end;',
next_date => SYSDATE + 1 / 24 / 60 / 60,
INTERVAL => 'SYSDATE+1/48');
COMMIT;
END;
/

3. . :

SELECT round(t.cpu_time / 1000000, 2) AS time_seq,
t.loads,
t.executions,
decode(nvl(t.executions, 0), 0, 0,
round(t.cpu_time / (1000000 * t.executions), 2)) AS time_per_load,
t.*
FROM monitoring_index_usage_table t
WHERE what_mon = 'UK_OBJ_DOC_OBJ_PROD_PART_BIRT usage'
ORDER BY time_per_load DESC;

-- BIND VARIABLE
SELECT *
FROM monitoring_sql_bind_capture
WHERE sql_id -- = '6pdbd2w2nd9w9'
IN (SELECT sql_id
FROM (SELECT decode(nvl(t.executions, 0), 0, 0,
round(t.cpu_time / (1000000 * t.executions), 2)) AS time_per_load,
t.*
FROM monitoring_index_usage_table t -- 22 -- 71 12 -- 116 13
WHERE what_mon = 'object_name usage'
ORDER BY time_per_load DESC)
WHERE rownum = 1);

V$SQL . .
V$SQL_PLAN . .
V$SQL_BIND_CAPTURE . .

.

- .

distinct clob- SQL_FULLTEXT:
CREATE OR REPLACE PROCEDURE mon_index_usage_get_only_sql IS
n NUMBER;
BEGIN
-- for getting distinct sqls to table mon_index_usage_sqls
-- from monitoring_index_usage_table

DELETE FROM mon_index_usage_sqls;
FOR v_i IN (SELECT * FROM monitoring_index_usage_table t) LOOP
SELECT COUNT(*)
INTO n
FROM mon_index_usage_sqls s
WHERE dbms_lob.compare(s.sql_fulltext, v_i.sql_fulltext) = 0;

IF (n = 0) THEN
INSERT INTO mon_index_usage_sqls
(sql_text, sql_fulltext)
VALUES
(v_i.sql_text, v_i.sql_fulltext);
END IF;
END LOOP;
END;

:
BEGIN
-- Call the procedure
mon_index_usage_get_only_sql;
END;
/

SELECT * FROM mon_index_usage_sqls;

4. :
EXECUTE DBMS_JOB.REMOVE(:jobno);
DROP TABLE monitoring_index_usage_table ;
DROP TABLE monitoring_index_plans ;
DROP TABLE monitoring_sql_bind_capture ;
DROP PROCEDURE monitoring_sql_plans ;
DROP PROCEDURE mon_index_usage_get_only_sql ;

: )))


 http://www.interface.ru
 http://www.interface.ru/home.asp?artId=28226