USR master'da yapildi.
create table a1 as select * from dba_objects;
select object_name from a1 where object_name='EVENT';
select sql_id,substr(sql_text,1,40) from v$sql where sql_text like '%select object_name from a1%';
21z70uk6vu3wy
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('21z70uk6vu3wy',0));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 21z70uk6vu3wy, child number 0
-------------------------------------
select object_name from a1 where object_name='EVENT'
Plan hash value: 3500360281
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 228 (100)| |
|* 1 | TABLE ACCESS FULL| A1 | 13 | 858 | 228 (2)| 00:00:03 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME"='EVENT')
Note
-----
- dynamic sampling used for this statement (level=2)
select sql_id,plan_hash_value from v$sql where sql_id='21z70uk6vu3wy';
SQL_ID PLAN_HASH_VALUE
------------- ---------------
21z70uk6vu3wy 3500360281
DECLARE
l_plans_loaded PLS_INTEGER;
BEGIN
l_plans_loaded :=DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE ( sql_id=>'21z70uk6vu3wy',plan_hash_value =>3500360281);
end;
/
SELECT sql_handle,sql_text,enabled,accepted,fixed FROM dba_sql_plan_baselines where sql_handle='SQL_9f67def84efc8a66';
SQL_HANDLE SQL_TEXT ENABLED ACCEPTED FIXED
SQL_9f67def84efc8a66 select object_name from a1 where object_name='EVENT'YES YES NO
SQL> create index xxx on a1(object_name);
Index created.
SQL_ID PLAN_HASH_VALUE SQL_TEXT CHILD_NUMBER BUFFER_GETS EXECUTIONS TRUNC(BUFFER_GETS/EXECUTIONS)
2ysg1zwhcx86c 3924418374 select * from v$sql where sql_text like '%select object_name from a1%' 0 43 1 43
9mv9mar4b5hj4 3924418374 select sql_id from v$sql where sql_text like '%select object_name from a1%' 0 89 1 89
7bvxhvmpkk0s7 3924418374 select sql_id,substr(sql_text,1,40) from v$sql where sql_text like '%select object_name from a1%' 0 3 3 1
931nzh21mf3fj 3670124693 select object_name from a1 where object_name='CABL' 0 132 1 132
21z70uk6vu3wy 3500360281 select object_name from a1 where object_name='EVENT' 0 1347 1 1347
21z70uk6vu3wy 3500360281 select object_name from a1 where object_name='EVENT' 2 1347 1 1347
dt2qa1cygyyx1 3924418374 select sql_id,plan_hash_value,sql_text,child_number,buffer_gets,executions,trunc(buffer_gets/executions) from v$sql where sql_text like '%select object_name from a1%' 0 3 1 3
4pngumv8czhv5 3924418374 select sql_id,plan_hash_value,sql_text from v$sql where sql_text like '%select object_name from a1%' 0 3 1 3
select object_name from a1 where object_name='CABL';
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('931nzh21mf3fj',0));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 931nzh21mf3fj, child number 0
-------------------------------------
select object_name from a1 where object_name='CABL'
Plan hash value: 3670124693
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | INDEX RANGE SCAN| XXX | 6 | 396 | 3 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OBJECT_NAME"='CABL')
Note
-----
- dynamic sampling used for this statement (level=2)
22 rows selected.
select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SQL_9f67def84efc8a66'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SQL handle: SQL_9f67def84efc8a66
SQL text: select object_name from a1 where object_name='EVENT'
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_9ytyyz17gt2m66b488122 Plan id: 1799913762
Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD
--------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3500360281
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 945 | 62370 | 228 (2)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| A1 | 945 | 62370 | 228 (2)| 00:00:03 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - filter("OBJECT_NAME"='EVENT')
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_9ytyyz17gt2m6d98c5d8a Plan id: 3649854858
Enabled: YES Fixed: NO Accepted: NO Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------
Plan hash value: 3670124693
-------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 945 | 62370 | 3 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| XXX | 945 | 62370 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OBJECT_NAME"='EVENT')
43 rows selected.
SELECT sql_handle,sql_text,enabled,accepted,fixed FROM dba_sql_plan_baselines where sql_handle='SQL_9f67def84efc8a66';
SQL_HANDLE SQL_TEXT ENABLED ACCEPTED FIXED
SQL_9f67def84efc8a66 select object_name from a1 where object_name='EVENT'YES YES NO
SQL_9f67def84efc8a66 select object_name from a1 where object_name='EVENT'YES NO NO
DECLARE
l_plans_loaded PLS_INTEGER;
BEGIN
l_plans_loaded :=DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE
( sql_id=>'931nzh21mf3fj',plan_hash_value =>'3924418374',SQL_HANDLE => 'SQL_9f67def84efc8a66');
end;
SQL PLAN BASELINE'da iki tane plan var. Bu durumda evaluate edilmesi gerekiyor. Index yaratildiktan sonra geleb SQL ler SQLPLANBASELINE'da
ACCEPTED NO olan kayıt ekliyor. bu durumda ise evaluate edilerek plan düzeltiliyor.
SET LONG 10000
SELECT DBMS_SPM.evolve_sql_plan_baseline(SQL_HANDLE => 'SQL_9f67def84efc8a66')
FROM dual;
DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SQL_9F67DEF84EFC8A66')
--------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Evolve SQL Plan Baseline Report
-------------------------------------------------------------------------------
Inputs:
-------
SQL_HANDLE = SQL_9f67def84efc8a66
PLAN_NAME =
TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
VERIFY = YES
DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SQL_9F67DEF84EFC8A66')
--------------------------------------------------------------------------------
COMMIT = YES
Plan: SQL_PLAN_9ytyyz17gt2m6d98c5d8a
------------------------------------
Plan was verified: Time used 1.879 seconds.
Plan passed performance criterion: 406.67 times better than baseline plan.
Plan was changed to an accepted plan.
Baseline Plan Test Plan Stats Ratio
------------- --------- -----------
Execution Status: COMPLETE COMPLETE
DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SQL_9F67DEF84EFC8A66')
--------------------------------------------------------------------------------
Rows Processed: 3 3
Elapsed Time(ms): 13.779 .034 405.26
CPU Time(ms): 20 0
Buffer Gets: 1218 3 406
Physical Read Requests: 0 0
Physical Write Requests: 0 0
Physical Read Bytes: 0 0
Physical Write Bytes: 0 0
Executions: 1 1
-------------------------------------------------------------------------------
DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SQL_9F67DEF84EFC8A66')
--------------------------------------------------------------------------------
Report Summary
-------------------------------------------------------------------------------
Number of plans verified: 1
Number of plans accepted: 1
SELECT sql_handle,sql_text,enabled,accepted,fixed FROM dba_sql_plan_baselines where sql_handle='SQL_9f67def84efc8a66';
SQL_HANDLE SQL_TEXT ENABLED ACCEPTED FIXED
SQL_9f67def84efc8a66 select object_name from a1 where object_name='EVENT'YES YES NO
SQL_9f67def84efc8a66 select object_name from a1 where object_name='EVENT'YES YES NO
SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('21z70uk6vu3wy',0));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 21z70uk6vu3wy, child number 0
-------------------------------------
select object_name from a1 where object_name='EVENT'
Plan hash value: 3670124693
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | INDEX RANGE SCAN| XXX | 3 | 198 | 3 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OBJECT_NAME"='EVENT')
Note
-----
- dynamic sampling used for this statement (level=2)
- SQL plan baseline SQL_PLAN_9ytyyz17gt2m6d98c5d8a used for this statement
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
23 rows selected.
Wednesday, January 18, 2012
Monday, January 16, 2012
SQL tuning için Outline ve SQL plan baseline kullanımı
/* Öncelikle SQL 'in SQL_id si bulunur */
/* Sorunlu SQL i çalıştıran sessionlar incelenir */
SELECT sid,
username,
action,
event,
sql_exec_start,
sql_child_number,
plsql_object_id
FROM v$session
WHERE sql_id = :hsid;
/* SQL hakkında genel bilgi ve Full text alınır */
select * from v$sqlarea where sql_id=:sql;
SELECT sql_id,
child_number,
buffer_gets,
executions,
TRUNC (buffer_gets / executions),
plan_hash_value
FROM v$sql
WHERE sql_id = :hsid
ORDER BY executions DESC;
/* Child number'e göre exec planlar teker teker incelenir */
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(:sql_id,:child_number));
/* Eğer childları varsa , exec basina buffer get leri düşükse ve bunlar kullanılmıyor ise
Bu exec planlar kullanıma alınır.
Eğer 10g de iseniz buffer get i düşük olan SQL plan OUTLINE ile fix edilir.
Burada daha önce'den
aşağıdaki iki adımın yapılış olması lazımdır.
*/
GRANT CREATE ANY OUTLINE TO username;
Alter system set USE_STORED_OUTLINES=FIXPLAN;
/* Outline yaratarak Planın fixlenmesi */
BEGIN
DBMS_OUTLN.create_outline(
hash_value => 3909283366,
child_number => 0,
category => 'FIXPLAN');
END;
/
/*
Eğer 11g de iseniz SQL Plan Baseline ile Fix etmeniz daha iyidir.
İster Cursor cache 'deki exec planı isterseniz SQL 'in AWR history sindeki planını yükliyebilirisinz
CURSOR CACHA'den SQL plan baseline ile fixleme
Cursor cache'deki SQL_id nin biraz önce bakılan exec basina buffer get'i az olan plan_hash_value seçilerek
SQL plan baseline fixlenir.
*/
DECLARE
l_plans_loaded PLS_INTEGER;
BEGIN
l_plans_loaded :=DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE ( sql_id=>'88hcbmn31jt4v',plan_hash_value =>2213722297 , FIXED=>'YES');
end ;
/*
eğer Cursor Cache'de Child lar yoksa AWR history'de SQL 'in exec başına buffer get' ine bakilir.
Eğer buradan değer dönerse buradadan alinan SQL plan ile önce SQL tuning Set (STS) oluşturulur sonra
bu STS içindeki plan SQL Plan baseline içine fixlenir.
*/
-- AWR rapor içinde SQL i arama
SELECT a.snap_id,
b.begin_interval_time,
a.plan_hash_value,
BUFFER_GETS_DELTA,
EXECUTIONS_DELTA ,
TRUNC (buffer_gets_delta / executions_delta) gets_per_exec,
ROWS_PROCESSED_DELTA
FROM dba_hist_sqlstat a, dba_hist_snapshot b
WHERE a.sql_id = :sql_id
AND a.snap_id = b.snap_id
AND EXECUTIONS_DELTA > 0
ORDER BY a.snap_id desc;
-- SQL ler ile historic olarak exec plan incelenir. Kullanılacak SQL_ID ve SQL_PLAN_HASH_VALUE seçilmelidir
select * from dba_hist_sql_plan where sql_id = :sql_id ;
SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR(:sql_id));
SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR(sql_id =>'bpb99ctkcw5zx' ,plan_hash_value =>3385879679 ));
select plan_hash_value , count(*) from dba_hist_sqlstat where sql_id = :sql_id group by plan_hash_value order by 2 desc;
-- uygun exec planı STS içine yüklemek için önce bir STS yaratılır.
BEGIN
DBMS_SQLTUNE.create_sqlset (
sqlset_name => 'STS_2',
description => 'STS_for_plan_change');
END;
/
-- STS içine yükleme yapılır
DECLARE
l_cursor DBMS_SQLTUNE.sqlset_cursor;
BEGIN
OPEN l_cursor FOR
SELECT VALUE(p)
FROM TABLE (DBMS_SQLTUNE.select_workload_repository (
begin_snap => 19075,
end_snap => 19290,
basic_filter => 'sql_id='||CHR(39)||'87xfz4gm9c0ms'||CHR(39)||' and plan_hash_value=3359181701',
attribute_list => 'ALL')
) p;
DBMS_SQLTUNE.load_sqlset (
sqlset_name => 'STS_2',
populate_cursor => l_cursor);
END;
/
-- Yukleme yapılmışmı diye buradan bakılır
select * from DBA_SQLSET where name ='STS_1';
Select * from DBA_SQLSET_STATEMENTS where sqlset_name ='STS_2';
select * from DBA_SQLSET_plans where sqlset_name ='STS_2';
SELECT * FROM table(DBMS_XPLAN.DISPLAY_SQLSET('STS_2',:sql_id));
-- SQL tuning Set içinden Sql Plan baseline alınıp SQL in fix edilmesi
DECLARE
l_plans_loaded PLS_INTEGER;
BEGIN
l_plans_loaded := DBMS_SPM.LOAD_PLANS_FROM_SQLSET (sqlset_name=>'STS_2',basic_filter => 'sql_id='||CHR(39)||'87xfz4gm9c0ms'||CHR(39)||' and plan_hash_value=3359181701',FIXED=>'YES');
END;
/
-- SQL plan baseline a bakılır
SELECT sql_handle,sql_text,enabled,accepted,fixed FROM dba_sql_plan_baselines;
/* Sorunlu SQL i çalıştıran sessionlar incelenir */
SELECT sid,
username,
action,
event,
sql_exec_start,
sql_child_number,
plsql_object_id
FROM v$session
WHERE sql_id = :hsid;
/* SQL hakkında genel bilgi ve Full text alınır */
select * from v$sqlarea where sql_id=:sql;
SELECT sql_id,
child_number,
buffer_gets,
executions,
TRUNC (buffer_gets / executions),
plan_hash_value
FROM v$sql
WHERE sql_id = :hsid
ORDER BY executions DESC;
/* Child number'e göre exec planlar teker teker incelenir */
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(:sql_id,:child_number));
/* Eğer childları varsa , exec basina buffer get leri düşükse ve bunlar kullanılmıyor ise
Bu exec planlar kullanıma alınır.
Eğer 10g de iseniz buffer get i düşük olan SQL plan OUTLINE ile fix edilir.
Burada daha önce'den
aşağıdaki iki adımın yapılış olması lazımdır.
*/
GRANT CREATE ANY OUTLINE TO username;
Alter system set USE_STORED_OUTLINES=FIXPLAN;
/* Outline yaratarak Planın fixlenmesi */
BEGIN
DBMS_OUTLN.create_outline(
hash_value => 3909283366,
child_number => 0,
category => 'FIXPLAN');
END;
/
/*
Eğer 11g de iseniz SQL Plan Baseline ile Fix etmeniz daha iyidir.
İster Cursor cache 'deki exec planı isterseniz SQL 'in AWR history sindeki planını yükliyebilirisinz
CURSOR CACHA'den SQL plan baseline ile fixleme
Cursor cache'deki SQL_id nin biraz önce bakılan exec basina buffer get'i az olan plan_hash_value seçilerek
SQL plan baseline fixlenir.
*/
DECLARE
l_plans_loaded PLS_INTEGER;
BEGIN
l_plans_loaded :=DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE ( sql_id=>'88hcbmn31jt4v',plan_hash_value =>2213722297 , FIXED=>'YES');
end ;
/*
eğer Cursor Cache'de Child lar yoksa AWR history'de SQL 'in exec başına buffer get' ine bakilir.
Eğer buradan değer dönerse buradadan alinan SQL plan ile önce SQL tuning Set (STS) oluşturulur sonra
bu STS içindeki plan SQL Plan baseline içine fixlenir.
*/
-- AWR rapor içinde SQL i arama
SELECT a.snap_id,
b.begin_interval_time,
a.plan_hash_value,
BUFFER_GETS_DELTA,
EXECUTIONS_DELTA ,
TRUNC (buffer_gets_delta / executions_delta) gets_per_exec,
ROWS_PROCESSED_DELTA
FROM dba_hist_sqlstat a, dba_hist_snapshot b
WHERE a.sql_id = :sql_id
AND a.snap_id = b.snap_id
AND EXECUTIONS_DELTA > 0
ORDER BY a.snap_id desc;
-- SQL ler ile historic olarak exec plan incelenir. Kullanılacak SQL_ID ve SQL_PLAN_HASH_VALUE seçilmelidir
select * from dba_hist_sql_plan where sql_id = :sql_id ;
SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR(:sql_id));
SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR(sql_id =>'bpb99ctkcw5zx' ,plan_hash_value =>3385879679 ));
select plan_hash_value , count(*) from dba_hist_sqlstat where sql_id = :sql_id group by plan_hash_value order by 2 desc;
-- uygun exec planı STS içine yüklemek için önce bir STS yaratılır.
BEGIN
DBMS_SQLTUNE.create_sqlset (
sqlset_name => 'STS_2',
description => 'STS_for_plan_change');
END;
/
-- STS içine yükleme yapılır
DECLARE
l_cursor DBMS_SQLTUNE.sqlset_cursor;
BEGIN
OPEN l_cursor FOR
SELECT VALUE(p)
FROM TABLE (DBMS_SQLTUNE.select_workload_repository (
begin_snap => 19075,
end_snap => 19290,
basic_filter => 'sql_id='||CHR(39)||'87xfz4gm9c0ms'||CHR(39)||' and plan_hash_value=3359181701',
attribute_list => 'ALL')
) p;
DBMS_SQLTUNE.load_sqlset (
sqlset_name => 'STS_2',
populate_cursor => l_cursor);
END;
/
-- Yukleme yapılmışmı diye buradan bakılır
select * from DBA_SQLSET where name ='STS_1';
Select * from DBA_SQLSET_STATEMENTS where sqlset_name ='STS_2';
select * from DBA_SQLSET_plans where sqlset_name ='STS_2';
SELECT * FROM table(DBMS_XPLAN.DISPLAY_SQLSET('STS_2',:sql_id));
-- SQL tuning Set içinden Sql Plan baseline alınıp SQL in fix edilmesi
DECLARE
l_plans_loaded PLS_INTEGER;
BEGIN
l_plans_loaded := DBMS_SPM.LOAD_PLANS_FROM_SQLSET (sqlset_name=>'STS_2',basic_filter => 'sql_id='||CHR(39)||'87xfz4gm9c0ms'||CHR(39)||' and plan_hash_value=3359181701',FIXED=>'YES');
END;
/
-- SQL plan baseline a bakılır
SELECT sql_handle,sql_text,enabled,accepted,fixed FROM dba_sql_plan_baselines;
Saturday, December 24, 2011
Overview of SQL Plan management
Overview of SQL Plan Management
SQL plan management is a preventative mechanism that records and evaluates the execution plans of SQL statements over time. This mechanism can build aSQL plan baseline, which is a set of accepted plans for a SQL statement. The accepted plans have been proven to perform well.
The plan history is the set of plans, both accepted and not accepted, that the optimizer generates for a SQL statement over time Because only accepted plans are in the SQL plan baseline, the plans in the baseline form a susbset of the plan history. For example, after the optimizer generates the first acceptable plan for a SQL plan baseline, subsequent plans are part of the plan history but not part of the plan baseline.
OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES initialization parameter to TRUE. By default, this parameter is FALSE.To load plans from a SQL tuning set, use the
LOAD_PLANS_FROM_SQLSET function of the DBMS_SPM package. The following example loads the plans stored in the SQL tuning set named tset1:DECLARE my_plans PLS_INTEGER; BEGIN my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET( sqlset_name => 'tset1'); END; /
To load plans from Automatic Workload Repository (AWR), load the plans stored in AWR snapshots into a SQL tuning set before using theLOAD_PLANS_FROM_SQLSET function as described in this section.
like this
BEGIN DBMS_SQLTUNE.CREATE_SQLSET( sqlset_name => 'my_sql_tuning_set', description => 'I/O intensive workload'); END; /
-- Load the SQL set from the Automatic Workload Repository (AWR). DECLARE l_cursor DBMS_SQLTUNE.sqlset_cursor; BEGIN OPEN l_cursor FOR SELECT VALUE(p) FROM TABLE (DBMS_SQLTUNE.select_workload_repository ( 765, -- begin_snap 766, -- end_snap NULL, -- basic_filter NULL, -- object_filter NULL, -- ranking_measure1 NULL, -- ranking_measure2 NULL, -- ranking_measure3 NULL, -- result_percentage 10) -- result_limit ) p; DBMS_SQLTUNE.load_sqlset ( sqlset_name => 'my_sql_tuning_set', populate_cursor => l_cursor); END; /
To load plans from the shared SQL area, use the LOAD_PLANS_FROM_CURSOR_CACHE function of the DBMS_SPM package. In the following example, Oracle Database loads the plans located in the shared SQL area for the SQL statement identified by its sql_id:
DECLARE my_plans PLS_INTEGER; BEGIN my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE( sql_id => '99twu5t2dn5xd'); END;/
To view the plans stored in the SQL plan baseline for a given statement, use the
DISPLAY_SQL_PLAN_BASELINE function of the DBMS_XPLAN package. The following example displays one or more execution plans for the specified SQL statement, specified by the handle (sql_handle):SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE( sql_handle=>'SYS_SQL_209d10fabbedc741', format=>'basic'));
Alternatively, you can display a single plan by supplying a plan name (plan_name).
You can also display SQL plan baseline information using a SELECT statement directly on the DBA_SQL_PLAN_BASELINES view, as shown in the following example:
SELECT SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, FIXED FROM DBA_SQL_PLAN_BASELINES; SQL_HANDLE PLAN_NAME ENA ACC FIX ------------------------------------------------------------------------ SYS_SQL_209d10fabbedc741 SYS_SQL_PLAN_bbedc741a57b5fc2 YES NO NO SYS_SQL_209d10fabbedc741 SYS_SQL_PLAN_bbedc741f554c408 YES YES NO
ENABLED : Bu SQL için yeni planlar oluşturulduğunda Baseline 'a alınır.
ACCEPTED : Kullanılan exec plan'ı tutan Baseline
FIXED : Artık bu SQL için yeni plan aranmaz. Yeni plan oluştuğunda base line'a alınmazThe optimizer does not add new plans to a fixed SQL plan baseline. Because the optimizer does not automatically add new plans, the database does not evolve a fixed SQL plan baseline when you execute
DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE. However, you can evolve a fixed SQL plan baseline by manually loading new plans into it from the shared SQL area or a SQL tuning set.During the SQL plan baseline selection phase, Oracle Database detects plan changes based on the stored plan history, and selects plans to avoid potential performance regressions for a set of SQL statements.
Each time the database compiles a SQL statement, the optimizer does the following:
Uses a cost-based search method to build a best-cost plan
Tries to find a matching plan in the SQL plan baseline
Does either of the following depending on whether a match is found:
If found, then the optimizer proceeds using the matched plan
If not found, then the optimizer evaluates the cost of each accepted plan in the SQL plan baseline and selects the plan with the lowest cost
Sunday, December 4, 2011
SQL MONITORING
Oracle 11g automatically monitors SQL statements if they are run in parallel, or consume 5 or more seconds of CPU or I/O in a single execution. This allows resource intensive SQL to be monitored as it is executing, as well as giving access to detailed information about queries once they are complete.
SQL monitoring requires the STATISTICS_LEVEL parameter to be set to 'TYPICAL' or 'ALL', and the CONTROL_MANAGEMENT_PACK_ACCESS parameter set to 'DIAGNOSTIC+TUNING'.
The MONITOR hint switches on SQL monitoring for statements that would not otherwise initiate it.
select /*+ monitor */. ....
Monitored statements can be identified using the V$SQL_MONITOR view.
V$SQL_MONITOR displays SQL statements whose execution have been (or are being) monitored by Oracle. An entry is created in V$SQL_MONITOR every time the execution of a SQL statement is being monitored. SQL monitoring is automatically started when a SQL statement runs parallel or when it has consumed at least 5 seconds of CPU or I/O time.
When the SQL statement being monitored is executing, statistics in V$SQL_MONITOR are generally refreshed in near real time, once every second. Once the execution ends, monitoring information is not deleted immediately. Instead, it is kept in V$SQL_MONITOR for at least one minute. The entry will eventually be deleted to reclaim its space as new statements are monitored.
Column Datatype Description
KEY NUMBER Artificial join key to efficiently join V$SQL_MONITOR with its corresponding plan level monitoring statistics stored in V$SQL_PLAN_MONITOR
STATUS VARCHAR2(19) SQL execution status:
QUEUED - SQL statement is queued
EXECUTING - SQL statement is still executing
DONE (ERROR) - Execution terminated with an error
DONE (FIRST N ROWS) - Execution terminated by the application before all rows were fetched
DONE (ALL ROWS) - Execution terminated and all rows were fetched
DONE - Execution terminated (parallel execution)
USER# NUMBER User ID of the database user who issued the SQL being monitored
USERNAME VARCHAR2(30) User name of the database user who issued the SQL being monitored
MODULEFoot 1 VARCHAR2(48) Name of the executing module when sampled, as set by the DBMS_APPLICATION_INFO.SET_MODULE procedure
ACTIONFootref 1 VARCHAR2(32) Name of the executing action when sampled, as set by the DBMS_APPLICATION_INFO.SET_ACTION procedure
SERVICE_NAME VARCHAR2(64) Service name of the user session
CLIENT_IDENTIFIER VARCHAR2(64) Client identifier from the user session
CLIENT_INFO VARCHAR2(64) Client information for the user session
PROGRAM VARCHAR2(48) Name of the OS program that issued the monitored SQL
PLSQL_ENTRY_OBJECT_ID NUMBER Object ID of the top-most PL/SQL subprogram on the stack; NULL if there is no PL/SQL subprogram on the stack
PLSQL_ENTRY_SUBPROGRAM_ID NUMBER Subprogram ID of the top-most PL/SQL subprogram on the stack; NULL if there is no PL/SQL subprogram on the stack
PLSQL_OBJECT_ID NUMBER Object ID of the currently executing PL/SQL subprogram; NULL if executing SQL
PLSQL_SUBPROGRAM_ID NUMBER Subprogram ID of the currently executing PL/SQL object; NULL if executing SQL
FIRST_REFRESH_TIME DATE Time when monitoring of the SQL statement started, generally a few seconds after execution start time
LAST_REFRESH_TIME DATE Time when statistics in V$SQL_MONITOR were last updated for the SQL statement. Statistics are generally refreshed every second when the statement executes.
REFRESH_COUNT NUMBER Number of times V$SQL_MONITOR statistics have been refreshed (generally once every second when the SQL statement executes)
SID NUMBER Session identifier executing (or having executed) the SQL statement being monitored
PROCESS_NAME VARCHAR2(5) Process name identifier executing (or having executed)the statement; ora if the process is foreground, else the background process name (for example, p001 for PX server p001)
SQL_ID VARCHAR2(13) SQL identifier of the statement being monitored
SQL_TEXT VARCHAR2(2000) Up to the first 2000 characters of the text of the SQL being monitored
IS_FULL_SQLTEXT VARCHAR2(1) Indicates whether the SQL_TEXT column has the entire SQL text (Y) or not (N)
SQL_EXEC_START DATE Time when the execution started
SQL_EXEC_ID NUMBER Execution identifier. Together, the three columns SQL_ID, SQL_EXEC_START, and SQL_EXEC_ID represent the execution key. The execution key is used to uniquely identify one execution of the SQL statement.
SQL_PLAN_HASH_VALUE NUMBER SQL Plan hash value
EXACT_MATCHING_SIGNATURE NUMBER Signature calculated on the normalized SQL text. The normalization includes the removal of white space and the uppercasing of all non-literal strings.
FORCE_MATCHING_SIGNATURE NUMBER Same as EXACT_MATCHING_SIGNATURE but literals in the SQL text are replaced by binds
SQL_CHILD_ADDRESS RAW(4 | 8) Address of the child cursor (can be used with SQL_ID to join with V$SQL)
SESSION_SERIAL# NUMBER Session serial number executing the statement being monitored
PX_IS_CROSS_INSTANCE VARCHAR2(1) Indicates whether the SQL statement ran parallel across multiple instances (Y) or not (N)
PX_MAXDOP NUMBER Maximum degree of parallelism for any plan operation executed on behalf of the monitored SQL
PX_MAXDOP_INSTANCES NUMBER Number of database instances touched at the maximum degree of parallelism
PX_SERVERS_REQUESTED NUMBER Total number of parallel execution servers requested to execute the monitored SQL
PX_SERVERS_ALLOCATED NUMBER Actual number of parallel execution servers allocated to execute the query
PX_SERVER# NUMBER Logical parallel execution server process number executing (or having executed) the statement being monitored; NULL if this monitoring entry is not associated with an execution server. This is a logical number within the parallel server set (see SERVER# in V$PX_SESSION).
PX_SERVER_GROUP NUMBER Logical parallel execution server group number to which PX_SERVER# belongs (see SERVER_GROUP in V$PX_SESSION); NULL if this monitoring entry is not associated with a parallel execution server. This value is generally 1 unless the SQL statement has one or more parallel sub-queries.
PX_SERVER_SET NUMBER Number (1 or 2) of the logical set of parallel execution servers to which PX_SERVER# belongs (see SERVER_SET in V$PX_SESSION); NULL if this monitoring entry is not associated with a parallel execution server
PX_QCINST_ID NUMBER Instance identifier where the parallel execution coordinator runs; NULL if PX_SERVER# is NULL
PX_QCSID NUMBER Session identifier for the parallel execution coordinator; NULL if PX_SERVER# is NULL
ERROR_NUMBER VARCHAR2(40) Error number encountered in case a SQL fails to execute successfully (for example, 932 in case of ORA-00932)
ERROR_FACILITY VARCHAR2(4) Error facility in case a SQL fails to execute successfully (for example, ORA in case of ORA-00932)
ERROR_MESSAGE VARCHAR2(256) Detailed error message displayed corresponding to the error number and error facility when a SQL fails to execute successfully
BINDS_XML CLOB Information about bind variables used with the SQL, such as name, position, value, data type, and so on (stored in XML format)
OTHER_XML CLOB Additional information about SQL execution stored in XML format
ELAPSED_TIME NUMBER Elapsed time (in microseconds); updated as the statement executes
QUEUING_TIME NUMBER Duration of time (in microseconds) spent by SQL in the statement queue
CPU_TIME NUMBER CPU time (in microseconds); updated as the statement executes
FETCHES NUMBER Number of fetches associated with the SQL statement; updated as the statement executes
BUFFER_GETS NUMBER Number of buffer get operations; updated as the statement executes
DISK_READS NUMBER Number of disk reads; updated as the statement executes
DIRECT_WRITES NUMBER Number of direct writes; updated as the statement executes
IO_INTERCONNECT_BYTES NUMBER Number of I/O bytes exchanged between Oracle Database and the storage system
PHYSICAL_READ_REQUESTS NUMBER Number of physical read I/O requests issued by the monitored SQL
PHYSICAL_READ_BYTES NUMBER Number of bytes read from disks by the monitored SQL
PHYSICAL_WRITE_REQUESTS NUMBER Number of physical write I/O requests issued by the monitored SQL
PHYSICAL_WRITE_BYTES NUMBER Number of bytes written to disks by the monitored SQL
APPLICATION_WAIT_TIME NUMBER Application wait time (in microseconds); updated as the statement executes
CONCURRENCY_WAIT_TIME NUMBER Concurrency wait time (in microseconds); updated as the statement executes
CLUSTER_WAIT_TIME NUMBER Cluster wait time (in microseconds); updated as the statement executes
-------------------------
SQL monitoring requires the STATISTICS_LEVEL parameter to be set to 'TYPICAL' or 'ALL', and the CONTROL_MANAGEMENT_PACK_ACCESS parameter set to 'DIAGNOSTIC+TUNING'.
The MONITOR hint switches on SQL monitoring for statements that would not otherwise initiate it.
select /*+ monitor */. ....
Monitored statements can be identified using the V$SQL_MONITOR view.
V$SQL_MONITOR displays SQL statements whose execution have been (or are being) monitored by Oracle. An entry is created in V$SQL_MONITOR every time the execution of a SQL statement is being monitored. SQL monitoring is automatically started when a SQL statement runs parallel or when it has consumed at least 5 seconds of CPU or I/O time.
When the SQL statement being monitored is executing, statistics in V$SQL_MONITOR are generally refreshed in near real time, once every second. Once the execution ends, monitoring information is not deleted immediately. Instead, it is kept in V$SQL_MONITOR for at least one minute. The entry will eventually be deleted to reclaim its space as new statements are monitored.
Column Datatype Description
KEY NUMBER Artificial join key to efficiently join V$SQL_MONITOR with its corresponding plan level monitoring statistics stored in V$SQL_PLAN_MONITOR
STATUS VARCHAR2(19) SQL execution status:
QUEUED - SQL statement is queued
EXECUTING - SQL statement is still executing
DONE (ERROR) - Execution terminated with an error
DONE (FIRST N ROWS) - Execution terminated by the application before all rows were fetched
DONE (ALL ROWS) - Execution terminated and all rows were fetched
DONE - Execution terminated (parallel execution)
USER# NUMBER User ID of the database user who issued the SQL being monitored
USERNAME VARCHAR2(30) User name of the database user who issued the SQL being monitored
MODULEFoot 1 VARCHAR2(48) Name of the executing module when sampled, as set by the DBMS_APPLICATION_INFO.SET_MODULE procedure
ACTIONFootref 1 VARCHAR2(32) Name of the executing action when sampled, as set by the DBMS_APPLICATION_INFO.SET_ACTION procedure
SERVICE_NAME VARCHAR2(64) Service name of the user session
CLIENT_IDENTIFIER VARCHAR2(64) Client identifier from the user session
CLIENT_INFO VARCHAR2(64) Client information for the user session
PROGRAM VARCHAR2(48) Name of the OS program that issued the monitored SQL
PLSQL_ENTRY_OBJECT_ID NUMBER Object ID of the top-most PL/SQL subprogram on the stack; NULL if there is no PL/SQL subprogram on the stack
PLSQL_ENTRY_SUBPROGRAM_ID NUMBER Subprogram ID of the top-most PL/SQL subprogram on the stack; NULL if there is no PL/SQL subprogram on the stack
PLSQL_OBJECT_ID NUMBER Object ID of the currently executing PL/SQL subprogram; NULL if executing SQL
PLSQL_SUBPROGRAM_ID NUMBER Subprogram ID of the currently executing PL/SQL object; NULL if executing SQL
FIRST_REFRESH_TIME DATE Time when monitoring of the SQL statement started, generally a few seconds after execution start time
LAST_REFRESH_TIME DATE Time when statistics in V$SQL_MONITOR were last updated for the SQL statement. Statistics are generally refreshed every second when the statement executes.
REFRESH_COUNT NUMBER Number of times V$SQL_MONITOR statistics have been refreshed (generally once every second when the SQL statement executes)
SID NUMBER Session identifier executing (or having executed) the SQL statement being monitored
PROCESS_NAME VARCHAR2(5) Process name identifier executing (or having executed)the statement; ora if the process is foreground, else the background process name (for example, p001 for PX server p001)
SQL_ID VARCHAR2(13) SQL identifier of the statement being monitored
SQL_TEXT VARCHAR2(2000) Up to the first 2000 characters of the text of the SQL being monitored
IS_FULL_SQLTEXT VARCHAR2(1) Indicates whether the SQL_TEXT column has the entire SQL text (Y) or not (N)
SQL_EXEC_START DATE Time when the execution started
SQL_EXEC_ID NUMBER Execution identifier. Together, the three columns SQL_ID, SQL_EXEC_START, and SQL_EXEC_ID represent the execution key. The execution key is used to uniquely identify one execution of the SQL statement.
SQL_PLAN_HASH_VALUE NUMBER SQL Plan hash value
EXACT_MATCHING_SIGNATURE NUMBER Signature calculated on the normalized SQL text. The normalization includes the removal of white space and the uppercasing of all non-literal strings.
FORCE_MATCHING_SIGNATURE NUMBER Same as EXACT_MATCHING_SIGNATURE but literals in the SQL text are replaced by binds
SQL_CHILD_ADDRESS RAW(4 | 8) Address of the child cursor (can be used with SQL_ID to join with V$SQL)
SESSION_SERIAL# NUMBER Session serial number executing the statement being monitored
PX_IS_CROSS_INSTANCE VARCHAR2(1) Indicates whether the SQL statement ran parallel across multiple instances (Y) or not (N)
PX_MAXDOP NUMBER Maximum degree of parallelism for any plan operation executed on behalf of the monitored SQL
PX_MAXDOP_INSTANCES NUMBER Number of database instances touched at the maximum degree of parallelism
PX_SERVERS_REQUESTED NUMBER Total number of parallel execution servers requested to execute the monitored SQL
PX_SERVERS_ALLOCATED NUMBER Actual number of parallel execution servers allocated to execute the query
PX_SERVER# NUMBER Logical parallel execution server process number executing (or having executed) the statement being monitored; NULL if this monitoring entry is not associated with an execution server. This is a logical number within the parallel server set (see SERVER# in V$PX_SESSION).
PX_SERVER_GROUP NUMBER Logical parallel execution server group number to which PX_SERVER# belongs (see SERVER_GROUP in V$PX_SESSION); NULL if this monitoring entry is not associated with a parallel execution server. This value is generally 1 unless the SQL statement has one or more parallel sub-queries.
PX_SERVER_SET NUMBER Number (1 or 2) of the logical set of parallel execution servers to which PX_SERVER# belongs (see SERVER_SET in V$PX_SESSION); NULL if this monitoring entry is not associated with a parallel execution server
PX_QCINST_ID NUMBER Instance identifier where the parallel execution coordinator runs; NULL if PX_SERVER# is NULL
PX_QCSID NUMBER Session identifier for the parallel execution coordinator; NULL if PX_SERVER# is NULL
ERROR_NUMBER VARCHAR2(40) Error number encountered in case a SQL fails to execute successfully (for example, 932 in case of ORA-00932)
ERROR_FACILITY VARCHAR2(4) Error facility in case a SQL fails to execute successfully (for example, ORA in case of ORA-00932)
ERROR_MESSAGE VARCHAR2(256) Detailed error message displayed corresponding to the error number and error facility when a SQL fails to execute successfully
BINDS_XML CLOB Information about bind variables used with the SQL, such as name, position, value, data type, and so on (stored in XML format)
OTHER_XML CLOB Additional information about SQL execution stored in XML format
ELAPSED_TIME NUMBER Elapsed time (in microseconds); updated as the statement executes
QUEUING_TIME NUMBER Duration of time (in microseconds) spent by SQL in the statement queue
CPU_TIME NUMBER CPU time (in microseconds); updated as the statement executes
FETCHES NUMBER Number of fetches associated with the SQL statement; updated as the statement executes
BUFFER_GETS NUMBER Number of buffer get operations; updated as the statement executes
DISK_READS NUMBER Number of disk reads; updated as the statement executes
DIRECT_WRITES NUMBER Number of direct writes; updated as the statement executes
IO_INTERCONNECT_BYTES NUMBER Number of I/O bytes exchanged between Oracle Database and the storage system
PHYSICAL_READ_REQUESTS NUMBER Number of physical read I/O requests issued by the monitored SQL
PHYSICAL_READ_BYTES NUMBER Number of bytes read from disks by the monitored SQL
PHYSICAL_WRITE_REQUESTS NUMBER Number of physical write I/O requests issued by the monitored SQL
PHYSICAL_WRITE_BYTES NUMBER Number of bytes written to disks by the monitored SQL
APPLICATION_WAIT_TIME NUMBER Application wait time (in microseconds); updated as the statement executes
CONCURRENCY_WAIT_TIME NUMBER Concurrency wait time (in microseconds); updated as the statement executes
CLUSTER_WAIT_TIME NUMBER Cluster wait time (in microseconds); updated as the statement executes
SET LINESIZE 200 COLUMN sql_text FORMAT A80 SELECT sql_id, status, sql_text FROM v$sql_monitor WHERE username = 'SCOTT';
SET LONG 1000000 SET LONGCHUNKSIZE 1000000 SET LINESIZE 1000 SET PAGESIZE 0 SET TRIM ON SET TRIMSPOOL ON SET ECHO OFF SET FEEDBACK OFF SPOOL /host/report_sql_monitor.htm SELECT DBMS_SQLTUNE.report_sql_monitor( sql_id => '526mvccm5nfy4', type => 'HTML', report_level => 'ALL') AS report FROM dual; SPOOL OFF
REPORT_SQL_MONITOR_LIST
The REPORT_SQL_MONITOR_LIST function was added in Oracle 11g Release 2 to generate a summary screen, similar to that on the "Monitored SQL Executions" page of Enterprise Manager.
SET LONG 1000000 SET LONGCHUNKSIZE 1000000 SET LINESIZE 1000 SET PAGESIZE 0 SET TRIM ON SET TRIMSPOOL ON SET ECHO OFF SET FEEDBACK OFF SPOOL /host/report_sql_monitor_list.htm SELECT DBMS_SQLTUNE.report_sql_monitor_list( type => 'HTML', report_level => 'ALL') AS report FROM dual; SPOOL OFF
Thursday, November 10, 2011
Getting the value of hidden parameters
for example the value of _smm_max_size
select a.ksppinm name, b.ksppstvl value, a.ksppdesc description
from sys.x$ksppi a, sys.x$ksppcv b
where a.indx = b.indx
and a.ksppinm = '_smm_max_size';
select a.ksppinm name, b.ksppstvl value, a.ksppdesc description
from sys.x$ksppi a, sys.x$ksppcv b
where a.indx = b.indx
and a.ksppinm = '_smm_max_size';
Monday, November 7, 2011
oracle 9i shared server
Oracle 9i Shared Server nasıl çalışıyor.
1. Dispatcher tanımı gerekiyor.
Sessionlar öncelikle bir listener’a bağlanıyorlar. Burada client’ın tnsnames.ora sında SERVER=shared olmalı.
If shared server is configured and a client connection request arrives when no
dispatchers are registered, the requests can be handled by a dedicated server
process (configured in the listener.ora file). If you want a particular client
always to use a dispatcher, configure (SERVER=shared) in the connect data
portion of the connect descriptor. For example:
Configuring Shared Server 14-9 – net service qadministrator guide
sales=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=sales-server)(PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=sales.us.acme.com)
(SERVER=shared)))
Bu listener’da bu connection’ı Dispacther’a yönlendiriyor.
Dispatcher’da istekleri Shared serverlara circuit olarak koyuyor. Boş bir shared server işi alıp yapıp tekrar sonucu dispatcher’a dönüyor. Dispatcher’da client’a dönüyor.
Tanımlar şöyle oluyor.
Registering Information with a Nondefault Listener
If you want PMON to register with a local listener that does not use TCP/IP, port
1521, configure the LOCAL_LISTENER parameter in the initialization parameter file
to locate the local listener.
For a shared server environment, you can alternatively use the LISTENER attribute
of the DISPATCHERS parameter in the initialization parameter file to register the
dispatchers with a nondefault local listener. Because both the LOCAL_LISTENER
parameter and the LISTENER attribute enable PMON to register dispatcher
information with the listener, it is not necessary to specify both the parameter and
the attribute if the listener values are the same.
See Also: Oracle9i Database Reference for further information about
the SERVICE_NAMES and INSTANCE_NAME parameters
Configuring Service Registration
12-16 Oracle9i Net Services Administrator’s Guide
Set the LOCAL_LISTENER parameter as follows:
LOCAL_LISTENER=listener_alias
Set the LISTENER attribute as follows:
DISPATCHERS="(PROTOCOL=tcp)(LISTENER=listener_alias)"
listener_alias is then resolved to the listener protocol addresses through a
naming method, such as a tnsnames.ora file on the database server.
For example, if the listener is configured to listen on port 1421 rather than port 1521,
you can set the LOCAL_LISTENER parameter in the initialization parameter file as
follows:
LOCAL_LISTENER=listener1
Using the same listener example, you can set the LISTENER attribute as follows:
DISPATCHERS="(PROTOCOL=tcp)(LISTENER=listener1)"
You can then resolve listener1 in the local tnsnames.ora as follows:
listener1=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=sales-server)(PORT=1421)))
Notes:
n To dynamically update the LOCAL_LISTENER parameter, use
the SQL statement ALTER SYSTEM SET:
ALTER SYSTEM SET LOCAL_LISTENER=’listener_alias’
If you set the parameter to null with the statement that follows,
then the default local address of TCP/IP, port 1521 is assumed.
ALTER SYSTEM SET LOCAL_LISTENER=’’
See the Oracle9i SQL Reference for further information about the
ALTER SYSTEM SET statement.
n The LISTENER attribute overrides the LOCAL_LISTENER
parameter. As a result, the SQL statement ALTER SYSTEM SET
LOCAL_LISTENER does not affect the setting of this attribute.
Configuring Service Registration
Configuring and Administering the Listener 12-17
To register information with another local listener:
1. Configure the listener.ora file with the protocol address of the local
listener.
2. Configure the LOCAL_LISTENER parameter in the initialization parameter file
to locate the local listener. If you are using shared server, you can also use the
LISTENER attribute of the DISPATCHERS parameter in the initialization
parameter file.
3. Resolve the listener name alias for the LOCAL_LISTENER or the LISTENER
setting through a tnsnames.ora file or an Oracle Names server.
DB açıldığı zaman DISPATCHER’daki listener parametresine bakıyor. Buradaki değeri alıp tnsnames.ora ‘ya bakıyor . Buradaki IP ve PORT ‘u alıyor. Sonra bu IP+PORT’u dinleyen listener’ı buluyor. Bu listener’a diyorki. Kardeş ben dispatcher lar açtım. Bunların parametreleri falan falan
Artık sana SERVER=shared gelenleri bu dispatcher’a gönder diyor.
Bu listener’ın özelliği listener.ora’da tanımlı olması ama sadece TCP/IP protokol adreslerinin olması.
Service vereceği yerlerin olmamasıdır. Service vereceği yer Dynamic registration ile halledilmektedir.
Bu kısmına gerek yoktur.
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=sales.us.acme.com)
(ORACLE_HOME=/oracle9i)
(SID_NAME=sales))
(SID_DESC=
(SID_NAME=plsextproc)
(ORACLE_HOME=/oracle9i)
(PROGRAM=extproc)))
A listener is configured with one or more listening protocol addresses, information
about supported services, and parameters that control its runtime behavior. The
listener configuration is stored in a configuration file named listener.ora.
Because all of the configuration parameters have default values, it is possible to
start and use a listener with no configuration. This default listener has a name of
LISTENER, supports no services upon startup, and listens on the following TCP/IP
protocol address:
(ADDRESS=(PROTOCOL=tcp)(HOST=host_name)(PORT=1521))
Supported services, that is, the services to which the listener forwards client
requests, can be configured in the listener.ora file or this information can be
dynamically registered with the listener. This dynamic registration feature is called
service registration and is used by Oracle9i or Oracle8i instances. The registration is
performed by the PMON process—an instance background process—of each
database instance that has the necessary configuration in the database initialization
parameter file. Dynamic service registration does not require any configuration in
the listener.ora file.
Note: A version 9 listener is required for an Oracle9i database.
Previous versions of the listener are not supported for use with an
Oracle9i database. However, it is possible to use a version 9 listener
with previous versions of the Oracle database.
See Also: "Listener Architecture" on page 4-10
Listener Configuration Overview
Configuring and Administering the Listener 12-3
Service registration offers the following benefits:
n Simplified configuration
Service registration reduces the need for the SID_LIST_listener_name
parameter setting, which specifies information about the databases served by
the listener, in the listener.ora file.
n Connect-time failover
Because the listener always knows the state of the instances, service registration
facilitates automatic failover of the client connect request to a different instance
if one instance is down.
In a static configuration model, a listener would start a dedicated server upon
receiving a client request. The server would later find out that the instance is
not up, causing an "Oracle not available" error message.
n Connection load balancing
Service registration enables the listener to forward client connect requests to the
least loaded instance and dispatcher or dedicated server. Service registration
balances the load across the service handlers and nodes.
Oracle Enterprise Manager tools require static service configuration in the
listener.ora file.
Shraed Server ile igili tanımları DB admin guiden da ve Net service admin guiden da detaylı bulabilirsin.
Bunları okuman sadece sana hatırlatma yapar.
1. Dispatcher tanımı gerekiyor.
Sessionlar öncelikle bir listener’a bağlanıyorlar. Burada client’ın tnsnames.ora sında SERVER=shared olmalı.
If shared server is configured and a client connection request arrives when no
dispatchers are registered, the requests can be handled by a dedicated server
process (configured in the listener.ora file). If you want a particular client
always to use a dispatcher, configure (SERVER=shared) in the connect data
portion of the connect descriptor. For example:
Configuring Shared Server 14-9 – net service qadministrator guide
sales=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=sales-server)(PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=sales.us.acme.com)
(SERVER=shared)))
Bu listener’da bu connection’ı Dispacther’a yönlendiriyor.
Dispatcher’da istekleri Shared serverlara circuit olarak koyuyor. Boş bir shared server işi alıp yapıp tekrar sonucu dispatcher’a dönüyor. Dispatcher’da client’a dönüyor.
Tanımlar şöyle oluyor.
Registering Information with a Nondefault Listener
If you want PMON to register with a local listener that does not use TCP/IP, port
1521, configure the LOCAL_LISTENER parameter in the initialization parameter file
to locate the local listener.
For a shared server environment, you can alternatively use the LISTENER attribute
of the DISPATCHERS parameter in the initialization parameter file to register the
dispatchers with a nondefault local listener. Because both the LOCAL_LISTENER
parameter and the LISTENER attribute enable PMON to register dispatcher
information with the listener, it is not necessary to specify both the parameter and
the attribute if the listener values are the same.
See Also: Oracle9i Database Reference for further information about
the SERVICE_NAMES and INSTANCE_NAME parameters
Configuring Service Registration
12-16 Oracle9i Net Services Administrator’s Guide
Set the LOCAL_LISTENER parameter as follows:
LOCAL_LISTENER=listener_alias
Set the LISTENER attribute as follows:
DISPATCHERS="(PROTOCOL=tcp)(LISTENER=listener_alias)"
listener_alias is then resolved to the listener protocol addresses through a
naming method, such as a tnsnames.ora file on the database server.
For example, if the listener is configured to listen on port 1421 rather than port 1521,
you can set the LOCAL_LISTENER parameter in the initialization parameter file as
follows:
LOCAL_LISTENER=listener1
Using the same listener example, you can set the LISTENER attribute as follows:
DISPATCHERS="(PROTOCOL=tcp)(LISTENER=listener1)"
You can then resolve listener1 in the local tnsnames.ora as follows:
listener1=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=sales-server)(PORT=1421)))
Notes:
n To dynamically update the LOCAL_LISTENER parameter, use
the SQL statement ALTER SYSTEM SET:
ALTER SYSTEM SET LOCAL_LISTENER=’listener_alias’
If you set the parameter to null with the statement that follows,
then the default local address of TCP/IP, port 1521 is assumed.
ALTER SYSTEM SET LOCAL_LISTENER=’’
See the Oracle9i SQL Reference for further information about the
ALTER SYSTEM SET statement.
n The LISTENER attribute overrides the LOCAL_LISTENER
parameter. As a result, the SQL statement ALTER SYSTEM SET
LOCAL_LISTENER does not affect the setting of this attribute.
Configuring Service Registration
Configuring and Administering the Listener 12-17
To register information with another local listener:
1. Configure the listener.ora file with the protocol address of the local
listener.
2. Configure the LOCAL_LISTENER parameter in the initialization parameter file
to locate the local listener. If you are using shared server, you can also use the
LISTENER attribute of the DISPATCHERS parameter in the initialization
parameter file.
3. Resolve the listener name alias for the LOCAL_LISTENER or the LISTENER
setting through a tnsnames.ora file or an Oracle Names server.
DB açıldığı zaman DISPATCHER’daki listener parametresine bakıyor. Buradaki değeri alıp tnsnames.ora ‘ya bakıyor . Buradaki IP ve PORT ‘u alıyor. Sonra bu IP+PORT’u dinleyen listener’ı buluyor. Bu listener’a diyorki. Kardeş ben dispatcher lar açtım. Bunların parametreleri falan falan
Artık sana SERVER=shared gelenleri bu dispatcher’a gönder diyor.
Bu listener’ın özelliği listener.ora’da tanımlı olması ama sadece TCP/IP protokol adreslerinin olması.
Service vereceği yerlerin olmamasıdır. Service vereceği yer Dynamic registration ile halledilmektedir.
Bu kısmına gerek yoktur.
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=sales.us.acme.com)
(ORACLE_HOME=/oracle9i)
(SID_NAME=sales))
(SID_DESC=
(SID_NAME=plsextproc)
(ORACLE_HOME=/oracle9i)
(PROGRAM=extproc)))
A listener is configured with one or more listening protocol addresses, information
about supported services, and parameters that control its runtime behavior. The
listener configuration is stored in a configuration file named listener.ora.
Because all of the configuration parameters have default values, it is possible to
start and use a listener with no configuration. This default listener has a name of
LISTENER, supports no services upon startup, and listens on the following TCP/IP
protocol address:
(ADDRESS=(PROTOCOL=tcp)(HOST=host_name)(PORT=1521))
Supported services, that is, the services to which the listener forwards client
requests, can be configured in the listener.ora file or this information can be
dynamically registered with the listener. This dynamic registration feature is called
service registration and is used by Oracle9i or Oracle8i instances. The registration is
performed by the PMON process—an instance background process—of each
database instance that has the necessary configuration in the database initialization
parameter file. Dynamic service registration does not require any configuration in
the listener.ora file.
Note: A version 9 listener is required for an Oracle9i database.
Previous versions of the listener are not supported for use with an
Oracle9i database. However, it is possible to use a version 9 listener
with previous versions of the Oracle database.
See Also: "Listener Architecture" on page 4-10
Listener Configuration Overview
Configuring and Administering the Listener 12-3
Service registration offers the following benefits:
n Simplified configuration
Service registration reduces the need for the SID_LIST_listener_name
parameter setting, which specifies information about the databases served by
the listener, in the listener.ora file.
n Connect-time failover
Because the listener always knows the state of the instances, service registration
facilitates automatic failover of the client connect request to a different instance
if one instance is down.
In a static configuration model, a listener would start a dedicated server upon
receiving a client request. The server would later find out that the instance is
not up, causing an "Oracle not available" error message.
n Connection load balancing
Service registration enables the listener to forward client connect requests to the
least loaded instance and dispatcher or dedicated server. Service registration
balances the load across the service handlers and nodes.
Oracle Enterprise Manager tools require static service configuration in the
listener.ora file.
Shraed Server ile igili tanımları DB admin guiden da ve Net service admin guiden da detaylı bulabilirsin.
Bunları okuman sadece sana hatırlatma yapar.
Thursday, October 27, 2011
DBMS_XPLAN package
:The DBMS_XPLAN package supplies five table functions
DISPLAY - to format and display the contents of a plan table.
DISPLAY_AWR - to format and display the contents of the execution plan of a stored SQL statement in the AWR.
DISPLAY_CURSOR - to format and display the contents of the execution plan of any loaded cursor.
DISPLAY_SQL_PLAN_BASELINE - to display one or more execution plans for the SQL statement identified by SQL handle
DISPLAY_SQLSET - to format and display the contents of the execution plan of statements stored in a SQL tuning set.
----------------- SAMPLES. -------------
Using a View to Display Last Explain Plan
EXPLAIN PLAN FOR
SELECT * FROM emp e, dept d
WHERE e.deptno = d.deptno
AND e.ename='benoit';
Display the plan using the DBMS_XPLAN.DISPLAY table function
SET LINESIZE 130
SET PAGESIZE 0
SELECT * FROM table(DBMS_XPLAN.DISPLAY);
Display the execution plan for the cursor:
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(('gwp663cqh5qbf',0));
Display the execution plan of all cursors matching the string 'TOTO':
SELECT t.*
FROM v$sql s, table(DBMS_XPLAN.DISPLAY_CURSOR(s.sql_id, s.child_number)) t WHERE sql_text LIKE '%TOTO%';
------------------------
DISPLAY Function
This table function displays the contents of the plan table.
Syntax
DBMS_XPLAN.DISPLAY(
table_name IN VARCHAR2 DEFAULT 'PLAN_TABLE',
statement_id IN VARCHAR2 DEFAULT NULL,
format IN VARCHAR2 DEFAULT 'TYPICAL',
filter_preds IN VARCHAR2 DEFAULT NULL);
To display the result of the last EXPLAIN PLAN command stored in the plan table:
SELECT * FROM table (DBMS_XPLAN.DISPLAY);
To display from other than the default plan table, "my_plan_table":
SELECT * FROM table (DBMS_XPLAN.DISPLAY('my_plan_table'));
To display the minimum plan information:
SELECT * FROM table (DBMS_XPLAN.DISPLAY('plan_table', null, 'basic'));
------------------------------------------
DISPLAY_AWR Function
This table function displays the contents of an execution plan stored in the AWR.
Syntax
DBMS_XPLAN.DISPLAY_AWR(
sql_id IN VARCHAR2,
plan_hash_value IN NUMBER DEFAULT NULL,
db_id IN NUMBER DEFAULT NULL,
format IN VARCHAR2 DEFAULT TYPICAL);
To display the different execution plans associated with the SQL ID 'atfwcg8anrykp':
SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR('atfwcg8anrykp'));
-----------------------------------------------------------------------------
DISPLAY_CURSOR Function
This table function displays the explain plan of any cursor loaded in the cursor cache. In addition to the explain plan, various plan statistics (such as. I/O, memory and timing) can be reported (based on the V$SQL_PLAN_STATISTICS_ALL VIEWS).
Syntax
DBMS_XPLAN.DISPLAY_CURSOR(
sql_id IN VARCHAR2 DEFAULT NULL,
child_number IN NUMBER DEFAULT NULL,
format IN VARCHAR2 DEFAULT 'TYPICAL');
child_number
Child number of the cursor to display. If not supplied, the execution plan of all cursors matching the supplied sql_id parameter are displayed. The child_number can be specified only if sql_id is specified.
To display the execution plan of the last SQL statement executed by the current session:
SELECT * FROM table (
DBMS_XPLAN.DISPLAY_CURSOR);
To display the execution plan of all children associated with the SQL ID 'atfwcg8anrykp':
SELECT * FROM table (
DBMS_XPLAN.DISPLAY_CURSOR('atfwcg8anrykp'));
with children 1
SELECT * FROM table (
DBMS_XPLAN.DISPLAY_CURSOR('atfwcg8anrykp',1));
--------------------------------------------------
DISPLAY_SQL_PLAN_BASELINE Function
This table function displays one or more execution plans for the specified SQL handle of a SQL plan baseline.
Syntax
DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE (
sql_handle IN VARCHAR2 := NULL,
plan_name IN VARCHAR2 := NULL,
format IN VARCHAR2 := 'TYPICAL')
RETURN dbms_xplan_type_table;
plan_name
Plan name. It identifies a specific plan. Default NULL means all plans associated with identified SQL statement are explained and displayed.
Display all plans of a SQL statement identified by the SQL handle 'SYS_SQL_b1d49f6074ab95af' using TYPICAL format
SET LINESIZE 150
SET PAGESIZE 2000
SELECT t.*
FROM TABLE(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(
'SYS_SQL_b1d49f6074ab95af')) t;
-----------------------------------------------------
DISPLAY_SQLSET Function
DBMS_XPLAN.DISPLAY_SQLSET(
sqlset_name IN VARCHAR2,
sql_id IN VARCHAR2,
plan_hash_value IN NUMBER := NULL,
format IN VARCHAR2 := 'TYPICAL',
sqlset_owner IN VARCHAR2 := NULL)
RETURN DBMS_XPLAN_TYPE_TABLE PIPELINED;
This table function displays the execution plan of a given statement stored in a SQL tuning set.
plan_hash_value
Optional parameter. Identifies a specific stored execution plan for a SQL statement. If suppressed, all stored execution plans are shown.
To display all execution plans of the SQL ID 'atfwcg8anrykp' stored in the SQL tuning set:
SELECT * FROM table (
DBMS_XPLAN.DISPLAY_SQLSET(
'OLTP_optimization_0405','gwp663cqh5qbf'));
To display the execution plan for the SQL statement associated with SQL ID 'gwp663cqh5qbf' and PLAN HASH 3693697075 in the SQL Tuning Set called 'OLTP_optimization_0405":
SELECT * FROM table (
DBMS_XPLAN.DISPLAY_SQLSET(
'OLTP_optimization_0405','gwp663cqh5qbf', 3693697075));
DISPLAY - to format and display the contents of a plan table.
DISPLAY_AWR - to format and display the contents of the execution plan of a stored SQL statement in the AWR.
DISPLAY_CURSOR - to format and display the contents of the execution plan of any loaded cursor.
DISPLAY_SQL_PLAN_BASELINE - to display one or more execution plans for the SQL statement identified by SQL handle
DISPLAY_SQLSET - to format and display the contents of the execution plan of statements stored in a SQL tuning set.
----------------- SAMPLES. -------------
Using a View to Display Last Explain Plan
EXPLAIN PLAN FOR
SELECT * FROM emp e, dept d
WHERE e.deptno = d.deptno
AND e.ename='benoit';
Display the plan using the DBMS_XPLAN.DISPLAY table function
SET LINESIZE 130
SET PAGESIZE 0
SELECT * FROM table(DBMS_XPLAN.DISPLAY);
Display the execution plan for the cursor:
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(('gwp663cqh5qbf',0));
Display the execution plan of all cursors matching the string 'TOTO':
SELECT t.*
FROM v$sql s, table(DBMS_XPLAN.DISPLAY_CURSOR(s.sql_id, s.child_number)) t WHERE sql_text LIKE '%TOTO%';
------------------------
DISPLAY Function
This table function displays the contents of the plan table.
Syntax
DBMS_XPLAN.DISPLAY(
table_name IN VARCHAR2 DEFAULT 'PLAN_TABLE',
statement_id IN VARCHAR2 DEFAULT NULL,
format IN VARCHAR2 DEFAULT 'TYPICAL',
filter_preds IN VARCHAR2 DEFAULT NULL);
To display the result of the last EXPLAIN PLAN command stored in the plan table:
SELECT * FROM table (DBMS_XPLAN.DISPLAY);
To display from other than the default plan table, "my_plan_table":
SELECT * FROM table (DBMS_XPLAN.DISPLAY('my_plan_table'));
To display the minimum plan information:
SELECT * FROM table (DBMS_XPLAN.DISPLAY('plan_table', null, 'basic'));
------------------------------------------
DISPLAY_AWR Function
This table function displays the contents of an execution plan stored in the AWR.
Syntax
DBMS_XPLAN.DISPLAY_AWR(
sql_id IN VARCHAR2,
plan_hash_value IN NUMBER DEFAULT NULL,
db_id IN NUMBER DEFAULT NULL,
format IN VARCHAR2 DEFAULT TYPICAL);
To display the different execution plans associated with the SQL ID 'atfwcg8anrykp':
SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR('atfwcg8anrykp'));
-----------------------------------------------------------------------------
DISPLAY_CURSOR Function
This table function displays the explain plan of any cursor loaded in the cursor cache. In addition to the explain plan, various plan statistics (such as. I/O, memory and timing) can be reported (based on the V$SQL_PLAN_STATISTICS_ALL VIEWS).
Syntax
DBMS_XPLAN.DISPLAY_CURSOR(
sql_id IN VARCHAR2 DEFAULT NULL,
child_number IN NUMBER DEFAULT NULL,
format IN VARCHAR2 DEFAULT 'TYPICAL');
child_number
Child number of the cursor to display. If not supplied, the execution plan of all cursors matching the supplied sql_id parameter are displayed. The child_number can be specified only if sql_id is specified.
To display the execution plan of the last SQL statement executed by the current session:
SELECT * FROM table (
DBMS_XPLAN.DISPLAY_CURSOR);
To display the execution plan of all children associated with the SQL ID 'atfwcg8anrykp':
SELECT * FROM table (
DBMS_XPLAN.DISPLAY_CURSOR('atfwcg8anrykp'));
with children 1
SELECT * FROM table (
DBMS_XPLAN.DISPLAY_CURSOR('atfwcg8anrykp',1));
--------------------------------------------------
DISPLAY_SQL_PLAN_BASELINE Function
This table function displays one or more execution plans for the specified SQL handle of a SQL plan baseline.
Syntax
DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE (
sql_handle IN VARCHAR2 := NULL,
plan_name IN VARCHAR2 := NULL,
format IN VARCHAR2 := 'TYPICAL')
RETURN dbms_xplan_type_table;
plan_name
Plan name. It identifies a specific plan. Default NULL means all plans associated with identified SQL statement are explained and displayed.
Display all plans of a SQL statement identified by the SQL handle 'SYS_SQL_b1d49f6074ab95af' using TYPICAL format
SET LINESIZE 150
SET PAGESIZE 2000
SELECT t.*
FROM TABLE(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(
'SYS_SQL_b1d49f6074ab95af')) t;
-----------------------------------------------------
DISPLAY_SQLSET Function
DBMS_XPLAN.DISPLAY_SQLSET(
sqlset_name IN VARCHAR2,
sql_id IN VARCHAR2,
plan_hash_value IN NUMBER := NULL,
format IN VARCHAR2 := 'TYPICAL',
sqlset_owner IN VARCHAR2 := NULL)
RETURN DBMS_XPLAN_TYPE_TABLE PIPELINED;
This table function displays the execution plan of a given statement stored in a SQL tuning set.
plan_hash_value
Optional parameter. Identifies a specific stored execution plan for a SQL statement. If suppressed, all stored execution plans are shown.
To display all execution plans of the SQL ID 'atfwcg8anrykp' stored in the SQL tuning set:
SELECT * FROM table (
DBMS_XPLAN.DISPLAY_SQLSET(
'OLTP_optimization_0405','gwp663cqh5qbf'));
To display the execution plan for the SQL statement associated with SQL ID 'gwp663cqh5qbf' and PLAN HASH 3693697075 in the SQL Tuning Set called 'OLTP_optimization_0405":
SELECT * FROM table (
DBMS_XPLAN.DISPLAY_SQLSET(
'OLTP_optimization_0405','gwp663cqh5qbf', 3693697075));
Subscribe to:
Posts (Atom)
