Sql*plus using Autotrace, SQL Advisor, real-time SQL monitoring

Advanced SQL Optimization (iii) Common optimization tools--12 Senior DBA teaches you Oracle development and optimization--

Advanced SQL Optimization (i)--12 Senior DBA teaches you Oracle development and optimization-Performance optimization section

1 autotrace

Set Autotrace on

2 Using SQL Tuning Advisor

Remove all indexes on Bigtab and Smalltab:

Declare

My_task_name VARCHAR2 (30);

My_sqltext Clob;

Begin

My_sqltext: = ' SELECT count (*) from BigTable A, smalltable b where a.object_name=b.table_name ';

My_task_name: = Dbms_sqltune.create_tuning_task (

Sql_text = My_sqltext,

user_name = ' ZJ ',

Scope = ' comprehensive ',

Time_limit = 60,

Task_name = ' Bigtable_jointo_smalltab_sql ',

Description = ' Optimizing examples of smalltable and bigtable Connections ');

Dbms_sqltune.execute_tuning_task (task_name = ' bigtable_jointo_smalltab_sql ');

End

Verify completion after creation is complete:

Select Task_name,status from user_advisor_log where task_name = ' bigtable_jointo_smalltab_sql ';

You can also view the results by using SQL statements, which is our favorite method:

Select Dbms_sqltune.report_tuning_task (' Bigtable_jointo_smalltab_sql ') from dual;

/*  create indexes based on optimization results */

Create INDEX Idx_smalltable_tablename on smalltable (table_name) tablespace users;

Create INDEX Idx_bigtable_tablename on bigtable (object_name) tablespace users;

3 real-time SQL monitoring

Real-time SQL monitoring (Real-time SQL Monitorning) is another new feature of Oracle 11g that allows users to monitor the SQL being executed. By default, it starts automatically when a parallel query is used, or when the CPU or I/O executed by SQL is longer than 5 seconds.

You can also use the optimization hints to force the use of real-time SQL monitoring functionality, as follows:

Select /*+ Monitor * /

COUNT (*)

From Bigtab A, Smalltab b

where a.object_name = B.table_name

If you want to force the use of real-time SQL monitoring, you can also use optimization tips:

Select /*+ no_monitor * /

COUNT (*)

From Bigtab A, Smalltab b

where a.object_name = B.table_name

The system views related to real-time SQL monitoring include:

Uv$sql_monitor

Uv$sql_plan_monitor

Uv$active_session_history

Uv$session

Uv$session_longops

Uv$sql

Uv$sql_plan

For the results that have just been monitored, you can use the DBMS package to read:

Select Dbms_sqltune.report_sql_monitor from dual;

Sql*plus using Autotrace, SQL Advisor, real-time SQL monitoring