16. Oracle Database Maintenance

16. Database Maintenance

1, Management Optimization program statistics

Preferences for collecting statistics

Dba-> Dbms_stats

Scope:statement level| Table level| Schema level| Database level| Global level

Preferences:cascade | degree| Estimate_percent|no_invalidate|metho_opt

Granularity | Incremental | publish| Stale_percent

Set| Get| delete| Export| Import

exec dbms_stats.set_table_prefs (' SH ', ' SALES ', ' stale_percent ', ' 13 ')

Desc dbms_stats;

One of the stored procedures is:

PROCEDURE Set_table_prefs

Argument Name Type in/out Default?

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

Ownname VARCHAR2 in

TabName VARCHAR2 in

PNAME VARCHAR2 in

PVALUE VARCHAR2 in

PROCEDURE Gather_table_stats

Argument Name Type in/out Default?

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

Ownname VARCHAR2 in

TabName VARCHAR2 in

PartName VARCHAR2 in DEFAULT

Estimate_percent number in DEFAULT

Block_sample BOOLEAN in DEFAULT

Method_opt VARCHAR2 in DEFAULT

Degree number in DEFAULT

Granularity VARCHAR2 in DEFAULT

CASCADE BOOLEAN in DEFAULT

Example 1:

(1) CREATE TABLE Hr.t1 as SELECT * from Dba_objects;

Because this table is just created, so it can not see the number of rows and block;

(2) Select Owner,table_name,tablespace_name,num_rows,blocks from dba_tables where owner= ' HR ' and table_name= ' T1 ';

OWNER table_name tablespace num_rowsBLOCKS

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

HR T1 USERS

(3) Collection of statistical information

exec dbms_stats.gather_table_stats (' HR ', ' T1 ');

sql> exec dbms_stats.gather_table_stats (' HR ', ' T1 ');

PL/SQL procedure successfully completed.

(4) Continue to query, results

Sql> Select Owner,table_name,tablespace_name,num_rows,blocks from dba_tables where owner= ' HR ' and T

Able_name= ' T1 ';

OWNER table_name tablespace num_rowsBLOCKS

HR T1 USERS ########## 1061

2. Manage automatic Workload Data archive

Automatic Workload Data Archive (AWR)

Built-in Performance information archive

Get a database snapshot every 60 minutes, reserved for 8 days

The foundation of all self-management functions

SGA---->mmon---> (60 minutes)---"AWR

Using the database Automatic diagnostics monitor

ADDM

Run after each awr snapshot is logged

Example of a supervisor

Store the results in the AWR.

Dbms_advisor Package

Case 2:

How does Oracle make recommendations for SQL optimization?

(1) CREATE TABLE Scott.test_advisor (ID varchar2, name varchar2 (128));

(2) Inserting data insert into Scott.test_advisor select Object_id,object_name from Dba_objects;

(3) CREATE index scott.idx_test_advisor_id on scott.test_advisor (ID);

(4) Obtaining statistical information

exec dbms_stats.gather_table_stats (' SCOTT ', ' test_advisor ', cascade=>true);

(5) View execution plan

Set Autotrace traceonly

Select Id,name from Scott.test_advisor where id=1000;

The result is a full table scan with the following contents

Sql> Select Id,name from Scott.test_advisor where id=1000;

Execution Plan

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

Plan Hash value:3695065845

| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time

|

| 0 | SELECT STATEMENT |     |    1 |   30 | 103 (1) | 00:00:02

|

|* 1 | TABLE ACCESS full|     Test_advisor |    1 |   30 | 103 (1) | 00:00:02

|

predicate information (identified by Operation ID):

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

1-filter (To_number ("ID") =1000)

Statistics

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

1 Recursive calls

0 db Block gets

375 consistent gets

0 physical Reads

0 Redo Size

596 Bytes sent via sql*net to client

524 Bytes received via sql*net from client

2 sql*net roundtrips To/from Client

0 Sorts (memory)

0 Sorts (disk)

1 rows processed

Define the tasks below to see what Oracle has to offer SQL for good advice?

(6) Defining tasks

Sql> Declare

2 l_task_id varchar2 (20);

3 L_sql varchar2 (2000);

4 begin

5 L_sql: = ' Select Id,name from scott.test_advisor where id=1000 ';

6 l_task_id: =dbms_sqltune.create_tuning_task (

7 Sql_text =>l_sql,

8 user_name = ' SCOTT ',

9 scope = ' comprehensive ',

Ten Time_limit =>30,

One task_name = ' manual_advisor ');

The end;

13/

PL/SQL procedure successfully completed.

(7) Perform tasks

Sql> begin

2 dbms_sqltune.execute_tuning_task (' Manual_advisor ');

3 END;

4/

PL/SQL procedure successfully completed.

(8) View suggestions

Set serveroutput on size 99999;

Set long 99999;

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

The suggested results are as follows:

Sql> Select Dbms_sqltune.report_tuning_task (' Manual_advisor ') from dual;

Dbms_sqltune. Report_tuning_task (' Manual_advisor ')

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

General Information Sections

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

Tuning Task Name:manual_advisor

Tuning Task Owner:sys

Workload Type : Single SQL Statement

Scope : Comprehensive

Time Limit (seconds): 30

Completion status:completed

Started at : 07/02/2017 21:21:56

Completed at : 07/02/2017 21:21:59

Dbms_sqltune. Report_tuning_task (' Manual_advisor ')

Schema Name:scott

SQL ID : 7gns85v297ncy

SQL Text:select id,name from Scott.test_advisor where id=1000

Findings section (2 findings)

1-index finding (see Explain plans section below)

Dbms_sqltune. Report_tuning_task (' Manual_advisor ')

The execution plan of this statement can is improved by creating one or more

Indices.

Recommendation (estimated benefit:98.04%)

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

-Consider running the Access Advisor to improve the physical schema design

or creating the recommended index.

CREATE INDEX SCOTT. idx$$_023a0001 on SCOTT. Test_advisor (To_number ("ID"));

Rationale

---------

Dbms_sqltune. Report_tuning_task (' Manual_advisor ')

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

Creating the recommended indices significantly improves the execution plan

of this statement. However, it might be preferable to run "Access Advisor"

Using a representative SQL workload as opposed to a and a single statement. This

Would allow-get comprehensive index recommendations which takes into

Account index maintenance overhead and additional space consumption.

2-restructure SQL Finding (see Plan 1 in explain plans section)

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

The predicate to_number ("Test_advisor". ID ") =1000 used at line ID 1 of the

Execution plan contains an implicit data type conversion on indexed column

"ID". This implicit data type conversion prevents the optimizer from

As you can see from the above, there are 2 suggestions that can improve the performance of 98.4% (recommendation (estimated benefit:98.04%)), respectively:

CREATE INDEX SCOTT. idx$$_023a0001 on SCOTT. Test_advisor (To_number ("ID"));

To_number ("Test_advisor". ID ") =1000

Let's take a look at the second piece of advice.

Select Id,name from Scott.test_advisor where To_number ("Test_advisor". " ID ") = 1000;

The result is a full-table scan cost of 103.

Looking at the first piece of advice

CREATE INDEX SCOTT. idx$$_023a0001 on SCOTT. Test_advisor (To_number ("ID"));

The cost is 3 for the query result to walk the index, which does improve the performance of 98.4%!!!!!!!

This article is from the "Liang blog" blog, make sure to keep this source http://7038006.blog.51cto.com/7028006/1946646

16. Oracle Database Maintenance