Oracle 11g Automatic Optimization and oracle11g Optimization

Oracle 11g Automatic Optimization and oracle11g Optimization
--: Automatic Plan Optimization
Begin
Dbms_workload_repository.create_snapshot ();
End;


Select/* + result_cache */count (*) from (select * from HJ. dbtan where object_type = 'table' order by 1 DESC, 2 ASC, 3 DESC, 4 ASC );


Begin
Dbms_workload_repository.create_snapshot ();
End;


-- Ft8s1pfmz9ph0
SET SERVEROUTPUT ON
DECLARE
Rochelle SQL _tune_task_id VARCHAR2 (100 );
BEGIN
L_ SQL _tune_task_id: = DBMS_SQLTUNE.create_tuning_task (
Begin_snap => 21,
End_snap => 22,
SQL _id => 'ft8s1pfmz9ph0 ',
Scope => DBMS_SQLTUNE.scope_comprehensive,
Time_limit = & gt; 300,
Task_name => 'ft8s1pfmz9ph0d _ AWR_tuning_task ',
Description => 'tuning task for statement f3hc7r4trnn1d in AWR .');
DBMS_OUTPUT.put_line ('L _ SQL _tune_task_id: '| l_ SQL _tune_task_id );
END;


Begin DBMS_SQLTUNE.execute_tuning_task (task_name => 'ft8s1pfmz9ph0d _ AWR_tuning_task '); end;
SELECT DBMS_SQLTUNE.report_tuning_task ('ft8s1pfmz9ph0d _ AWR_tuning_task ') AS recommendations FROM dual;


GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name: ft8s1pfmz9ph0d_AWR_tuning_task
Tuning Task Owner: SYS
Workload Type: Single SQL Statement
Scope: COMPREHENSIVE
Time Limit (seconds): 300
Completion Status: COMPLETED
Started at: 14:55:32
Completed at: 09/01/2014 14:55:44


-------------------------------------------------------------------------------
Schema Name: SYS
SQL ID: ft8s1pfmz9ph0
SQL Text: select/* + result_cache */count (*) from (select * from
HJ. dbtan where object_type = 'table' order by 1 DESC, 2 ASC, 3
DESC, 4 ASC)


-------------------------------------------------------------------------------
Findings section (2 findings)
-------------------------------------------------------------------------------


1-Statistics Finding
---------------------
The table "HJ". "DBTAN" has not been analyzed ".
Recommendation
--------------
-Collect the program statistics for the table.
Execute dbms_stats.gather_table_stats (ownname => 'Hj ', tabname =>
'Dbtan ', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
Method_opt => 'for all columns size auto ');
Rationale
---------
To select a good execution plan, the optimizer requires the latest statistics for this table.
2-Index Finding (see explain plans section below)
--------------------------------------------------
You can improve the execution plan of this statement by creating one or more indexes.
Recommendation (estimated benefit: 95.98%)
------------------------------------------
-Consider running access guides that can improve the design of physical solutions or create recommended indexes.
Create index HJ. IDX $ _ 00360001 on HJ. DBTAN ("OBJECT_TYPE ");
Rationale
---------
Creating recommended indexes can significantly improve the execution plan of this statement. However, run the "access guide" with a typical SQL workload"
It may be more desirable than a single statement. Using this method, you can obtain a comprehensive index proposal, including the overhead of index maintenance and additional space consumption.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1-Original
-----------
Plan hash value: 1782547706
Bytes --------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes --------------------------------------------------------------------------------------------------
| 0 | select statement | 1 | 11 | 4706 (1) | 00:00:57 |
| 1 | result cache | 81z3k6zbauk9s2c83c03s270ja |
| 2 | sort aggregate | 1 | 11 |
| * 3 | table access full | DBTAN | 40006 | 429K | 4706 (1) | 00:00:57 |
Bytes --------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
3-filter ("OBJECT_TYPE" = 'table ')
Result Cache Information (identified by operation id ):
------------------------------------------------------
1-column-count = 1; dependencies = (HJ. DBTAN); attributes = (single-row); parameters = (nls); name = "select/* + result_cache */count (*) from (select * from HJ. dbtan where object_type = 'table' order by 1 DESC, 2 ASC, 3 DESC, 4
2-Using New Indices
--------------------
Plan hash value: 2810514733
Bytes -------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes -------------------------------------------------------------------------------------------------
| 0 | select statement | 1 | 11 | 189 (1) | 00:00:03 |
| 1 | result cache | 27yjysxpdun18b2utun82bynny |
| 2 | sort aggregate | 1 | 11 |
| * 3 | index range scan | IDX $ _ 00360001 | 40006 | 429K | 189 (1) | 00:00:03 |
Bytes -------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
3-access ("OBJECT_TYPE" = 'table ')
Result Cache Information (identified by operation id ):
------------------------------------------------------
1-column-count = 1; dependencies = (HJ. DBTAN); attributes = (single-row, ordered); parameters = (nls); name = "select/* + result_cache */count (*) from (select * from HJ. dbtan where object_type = 'table' order by 1 DESC, 2 ASC, 3 DESC, 4"
-------------------------------------------------------------------------------


Begin DBMS_SQLTUNE.drop_tuning_task (task_name => 'ft8s1pfmz9ph0d _ AWR_tuning_task '); end;
What is the difference between Oracle 11g and 10g?

Introduction to new features of ORACLE 11g
Oracle 11g has now started beta testing. Like other products before, the new generation of oracle will add many exciting new features. The following describes some new features of 11g.

1. Database Management

◆ Database Replay)

This feature can capture the load of the entire data and pass it to a test database created from the backup or standby database, and then replays the role of testing the system after optimization.

◆ SQL Replay (SQL Replay)

Similar to the previous feature. However, it only captures the SQL load, rather than all the load.

◆ Plan Management)

This feature allows you to fix the query plan of a specific statement, regardless of the statistical data or database version changes, it will not change her query plan.

◆ Automatic Diagnostic knowledge base (Automatic Diagnostic Repository ADR)

When Oracle detects an important error, it will automatically create an event (incident), capture information related to the event, and automatically perform database health check and notify DBA. In addition, this information can be packaged and sent to the Oracle Support Team.

◆ Incident Packaging Service)

If you need to further test or retain relevant information, this feature can package information related to an event. You can also send the packaging information to the oracle Support Team.

◆ Feature-Based Patching)

During patching, this feature makes it easy for you to differentiate the features in the patch package that you are using and must play. Enterprise Manager (EM) enables you to subscribe to a feature-based patch service, so Enterprise Manager can automatically scan for the features you are using with patches.

◆ Auto SQL Tuning)

The 10 Gb automatic optimizer can write the optimization suggestions in the SQL profile. In the 11g environment, you can enable oracle to automatically apply the profile with three times the original performance to SQL statements. Performance Comparison is completed by a new management task in the maintenance window.

◆ Access Advisor)

The 11g access recommender can provide partition recommendations, including recommendations for the new interval partition (interval partitioning. The interval partition is equivalent to the automatic version of range partitioning. When necessary, she can automatically create a partition of the same size. Range partitions and interval partitions can exist in a table at the same time, and range partitions can be converted to interval partitions.

◆ Auto Memory Tuning)

In 9i, automatic PGA optimization is introduced; in 10g, automatic SGA optimization is introduced. By 11 GB, all memory can be automatically optimized by setting only one parameter. As long as you tell oracle How much memory is available, she can automatically specify how much memory is allocated to PGA, How much memory is allocated to SGA, and how much memory is allocated to operating system processes. You can also set the maximum and minimum thresholds.

◆ Resource Manager)

The 11g resource manager can manage both the CPU and IO. You can set the priority, type, and ASM disk of a specific file.

◆ ADDM

ADDM is introduced at 10 Gb. In 11g, ADDM not only provides suggestions for a single instance, but also provides suggestions for the entire RAC (that is, the database level. In addition, you can add some direve ve instructions to ADDM to ignore information that you don't care about.

◆ AWR Baselines)

The AWR baseline is extended. You can automatically create baselines for some other features used. A weekly baseline is created by default.

2. PL... the remaining full text>
 
Oracle 11g Query Optimization

What is the total data volume?