Oracle10g use SQL to obtain ADDM reports and use ADDM to monitor dml of tables

I. Oracle10g provides the ADDM function. It is naturally easy to use em to view the ADDM report. The following two methods are available if you use SQL:

1. The following SQL statement can be used to obtain the latest report.

Set long 1000000 PAGESIZE 0 LONGCHUNKSIZE 1000

COLUMN get_clob FORMAT a80

SELECT dbms_advisor.GET_TASK_REPORT (task_name)

FROM dba_advisor_tasks

WHERE task_id = (

SELECT max (t. task_id)

FROM dba_advisor_tasks t,

Dba_advisor_log l

WHERE t. task_id = l. task_id AND

T. advisor_name = 'addm' AND

L. status = 'completed ');

Or query SELECT * FROM dba_advisor_tasks;

Obtain the task_name of the time point, and then execute it separately.

Set long 1000000 PAGESIZE 0 LONGCHUNKSIZE 1000

COLUMN get_clob FORMAT a80

SELECT dbms_advisor.get_task_report ('Scott _ addm', 'text', 'typical | all ')

FROM sys. dual;

You can get the report at the required time.

2. The following script can obtain reports between two snapshots (similar to statspack ):

SQL> @? /Rdbms/admin/addmrpt

2. Using ADDM, you can also automatically monitor the number of tables added, deleted, and modified in the database (similar to the previous alter table... monitoring). The method is as follows:

1. Enable ADDM. Of course, statistics_level must be TYPICAL or ALL.

2. check whether a new table test. test has been modified. If the record is inserted:

SQL> select * from dba_tab_modifications where table_owner = 'test' and table_name = 'test ';

No rows selected

SQL> INSERT INTO TEST. TEST VALUES (1, 1 );

1 row created.

SQL> COMMIT;

Commit complete.

3. Manually push the modified table information of sga to the data dictionary (otherwise, it will take 15 minutes) and view the situation in the data dictionary:

SQL> exec dbms_stats.FLUSH_DATABASE_MONITORING_INFO ();

PL/SQL procedure successfully completed.

SQL> select * from dba_tab_modifications where table_owner = 'test ';

TABLE_OWNER TABLE_NAME

------------------------------------------------------------

PARTITION_NAME SUBPARTITION_NAME INSERTS

----------------------------------------------------------------------

Updates deletes timestamp tru DROP_SEGMENTS

---------------------------------------------

TEST

1

0 0 04-SEP-07 NO 0

We can see that inserts has changed to 1.

4. What if I re-collect the table information?

SQL> execute DBMS_STATS.GATHER_TABLE_STATS ('test', 'test ');

PL/SQL procedure successfully completed.

SQL> select * from dba_tab_modifications where table_owner = 'test' and

Table_name = 'test ';

No rows selected

Oh, no. This is because after re-statistics, oracle considers the information to be old, so it is gone. Pay attention to this.