Bu paketi kullanarak sql_id bazında advisor’ i kullanarak bir takım öneriler edinebiliriz. Bu paket kullanılarak alınabilecek önerileri aşağıdaki şekilde kategorize edebiliriz;
• Istatistiği eski veya eksikse,
• Daha iyi bir execution planı varsa,
• Objeye erişimde daha hızlı olması açısından index, materialized view önerisi,
• SQL’ in yapısı ile ilgili öneriler
sunar.
SQL Tuning Advisor’ ı Enterprise Manager üzerinden çalıştırabilir, sql tuning advisor dbms_sqltune paketinden kontrol edilebilir.
Bu paketleri çalıştırabilmek için, çalıştıracak olan userın DBA yetkisi ile ADVISOR yetkisine sahip olması gerekmektedir. OEM üserinden sql tuning advisor’ ı çalıştıracaksanız o durumda da userın SELECT_CATALOG_ROLE yetkisine sahip olması gerekmektedir.
DBMS_SQLTUNE ile sql tuning 2 stepden oluşmaktadır.
1. Create a SQL tuning task
2. Execute a SQL tuning task
Komut satırından sql tuning advisor’ ı çalıştırmak istediğimizde ;
SQL> @$ORACLE_HOME/rdbms/admin/sqltrpt.sql
Sql satırından aşağıdaki script çalıştırıldığında oracle tarafından cache içerisinde var olan ve sisteme maliyeti en fazla olan 15 sorgu ile workload reporitory içerisinde olupda yine sisteme maliyeti en fazla olan 15 sorguyu burada direkt olarak görebilrisiniz. Eğer aradığınız script/sql_id burada var ise scriptin hemen altında yer alan alana buradan sql_id ‘ i kopyalamanız yeterli olacaktır.
SQL> @$ORACLE_HOME/rdbms/admin/sqltrpt.sql
15 Most expensive SQL in the cursor cache
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
00jf55hxwdnf3 2,167.51 SELECT cvalcdgo,TDESCDGO,CDGO,cstatdgo, GCDGO.ROWID fro
5ustsms1730x1 1,809.88 select null, i.owner, i.table_name, decode (i.uniquenes
772s25v1y0x8k 1,766.14 select shared_pool_size_for_estimate s, shared
c0zmapjdb6x0r 1,200.97 SELECT COUNT(*) FROM TB_CENFORMUSERS WHERE TTYPEUSER=:B
6v7n0y2bq89n8 1,005.17 BEGIN EMDW_LOG.set_context(MGMT_JOB_ENGINE.MODULE_NAME,
4yffxmsh6n03q 975.76 BEGIN SYS.DBMS_DESCRIBE.DESCRIBE_PROCEDURE(:object_name
7q7rv0y2knax9 810.63 select to_char(null), ac.owner, ac.table_name, acc.colu
2b064ybzkwf1y 419.47 BEGIN EMD_NOTIFICATION.QUEUE_READY(:1, :2, :3); END;
aykvshm7zsabd 328.02 select size_for_estimate, size_fac
0s93dmwpfst5v 273.24 SELECT * FROM ( SELECT ROWIDTOCHAR(rowid) rowid_c,
81ky0n97v4zsg 223.95 /* OracleOEM */ select s.sid, s.serial# from v$session
1ww3djus8wz1z 188.54 SELECT CTYPECMP,TDESCDGO,NCRLMACT,NMRC,NBIN,NCLS,DEXPCC
4mjcgscdj5zv1 159.13 INSERT INTO tb_appinqlog(program,
bfdhmxck26sc1 156.21 SELECT * FROM tb_secconnection
8t43xdhf4d9x2 141.86 SELECT CONTEXT_TYPE_ID,CONTEXT_TYPE,TRACE_LEVEL,NULL,NU
15 Most expensive SQL in the workload repository
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
7q7rv0y2knax9 60,342.62 select to_char(null), ac.owner, ac.table_name, acc.colu
2qjpda5ppq06h 48,926.41 UPDATE tb_cenformlcaf SET hesap_kart_sube_kodu = :h
gnhmafttynqqu 48,624.69 BEGIN PA_DBOSSUBEDEVRI.PR_DBOSSUBEDEVRI ; COMMIT;
5ustsms1730x1 46,428.04 select null, i.owner, i.table_name, decode (i.uniquenes
btj75ac9kqfgh 45,585.52 UPDATE tb_cenformlcaf SET dda_account_number_tl_1 =
bqknmuc55tnrf 44,420.50 UPDATE tb_cenformlcaf SET dda_account_number_usd_1
1218zpf5y7nkv 44,377.40 UPDATE tb_cenformcicaf SET hesap_sube = :dda_accoun
a20b0hzn6gn66 44,317.71 UPDATE tb_cenformicaf SET dda_account_number_tl_1 =
fxj5gkqqgy4m4 43,179.72 UPDATE tb_cenformcicaf SET usd_subekodu = :dda_acco
4kvy47b9v017n 43,011.64 UPDATE tb_cenformicaf SET dda_account_number_usd_1
10c2pmcf3z0av 36,751.66 UPDATE tb_cenformcicaf SET kd_007_branch_code = :kd
0tmvfd2dgrbbv 35,129.96 UPDATE tb_cenformicaf SET kd_007_branch_code = :kd_
gs4zxdj6mw1jn 34,956.83 UPDATE tb_cenformtcaf SET kd_007_branch_code = :kd_
ct9px2b7b9kpg 31,196.59 UPDATE tb_cenformicaf SET nlockusr = NULL,
bg6gqbwrvthfm 30,948.83 UPDATE tb_cenformcicaf SET nlockusr = NULL,
Sql Id specified: 00jf55hxwdnf3 (seçilen sql_id)
Tune the sql
~~~~~~~~~~~~
GENERAL INFORMATION SECTION
——————————————————————————-
Tuning Task Name : TASK_4379
Tuning Task Owner : SYS
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status : COMPLETED
Started at : 05/02/2014 14:15:42
Completed at : 05/02/2014 14:15:43
——————————————————————————-
Schema Name: C993
SQL ID : 00jf55hxwdnf3
SQL Text : SELECT cvalcdgo,TDESCDGO,CDGO,cstatdgo, GCDGO.ROWID from GCDGO
ORDER BY cdgo,TDESCDGO
——————————————————————————-
There are no recommendations to improve the statement.
——————————————————————————-
Peki biz öncesinde tespit ettiğimiz bir sql_id’ yi veya script’ i tune etmek istersek ne yapabiliriz ;
SQL Tuning task create etmek istediğimiz de aşağıdaki kriterlerde tasklar create edebiliriz ,
• Cache’ deki sql’ ler arasında sql_id kullanarak,
• Workload repository içerisindeki bir sql_id ‘ yi kullanarak,
• Sql tuning set kullanarak ,
• Select cümleciğini kullanarak,
Sql tuning task’ ları create edebiliriz.
Örnek bir sql cümleciği kullanarak bir sql task create etmeye çalışalım ;
Öncesinde aşağıdaki scriptleri kullanarak üzerinde çalışabileceğimiz sample bir tablo oluşturalım ;
drop table kamil.tuning_test_table ;
create table kamil.tuning_test_table as select * from dba_objects ;
CREATE or replace PUBLIC SYNONYM TUNING_TEST_TABLE FOR KAMIL.TUNING_TEST_TABLE;
insert into tuning_test_table select * from tuning_test_table ;
insert into tuning_test_table select * from tuning_test_table ;
insert into tuning_test_table select * from tuning_test_table ;
insert into tuning_test_table select * from tuning_test_table ;
insert into tuning_test_table select * from tuning_test_table ;
insert into tuning_test_table select * from tuning_test_table ;
commit ;
Çıktılarımız ;
===> drop table kamil.tuning_test_table
Table dropped.
===> create table kamil.tuning_test_table as select * from dba_objects
Table created.
===> CREATE or replace PUBLIC SYNONYM TUNING_TEST_TABLE FOR KAMIL.TUNING_TEST_TABLE
Synonym created.
===> insert into tuning_test_table select * from tuning_test_table
75041 rows created.
===> insert into tuning_test_table select * from tuning_test_table
150082 rows created.
===> insert into tuning_test_table select * from tuning_test_table
300164 rows created.
===> insert into tuning_test_table select * from tuning_test_table
600328 rows created.
===> insert into tuning_test_table select * from tuning_test_table
1200656 rows created.
===> insert into tuning_test_table select * from tuning_test_table
2401312 rows created.
===> commit
Commit complete.
Sql tuning taskımızı artık create edebiliriz ;
===> DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
BEGIN
my_sqltext := ‘select * from tuning_test_table where object_id = 3 ‘;
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => my_sqltext,
user_name => ‘KAMIL’,
scope => ‘COMPREHENSIVE’,
time_limit => 30,
task_name => ‘TUNE_MY_SCRIPT’,
description => ‘Tuning Test Calismasi’);
END;
PL/SQL procedure successfully completed.
Sql tuning task create ederken önemli 3 tane parametremiz bulunmaktadır, Bunlar;
Username = Create edilen task hangi user altında oluşturulacak,
Scope = COMPREHENSIVE, task execute edildiğinde sql profile analizi yapılmasını sağlar,
Time_limit = Job’ ın çalışacağı süre aralığını saniye cinsinden belirler.
Sql tuning task’ ımız create olduğunu teyit edelim;
SELECT owner own,
task_id id,
task_name name,
execution_start exec_start,
execution_end exec_end,
status stat
FROM dba_advisor_log
WHERE owner = ‘SYS’ AND task_name = ‘TUNE_MY_SCRIPT’ ;
OWN ID NAME EXEC_START EXEC_END STAT
SYS 5061 TUNE_MY_SCRIPT INITIAL
Statüsü INITIAL olarak beklediğini görmekteyiz. Oluşturmuş olduğumuz bu task’ ımızı run edelim ;
execute dbms_sqltune.Execute_tuning_task (task_name => ‘TUNE_MY_SCRIPT’);
===> execute dbms_sqltune.Execute_tuning_task (task_name => ‘TUNE_MY_SCRIPT’)
PL/SQL procedure successfully completed.
Sqlplus ‘ dan run edecekseniz outputu düzgün görebilmek için aşağıdaki settingleri yapmanız gerekmektedir. Toad vs gibi toollar üzerinden çalıştıracaksanız buna gerek olmayacaktır;
set long 65536
set longchunksize 65536
set linesize 100
Tuning sonucunu görmek içinse;
select dbms_sqltune.report_tuning_task(‘TUNE_MY_SCRIPT’) from dual;
komutunu kullanabilrisiniz. Bizim örneğimizde kullandığımız scripti oracle tune ederek 3 tane öneride bulunmaktadır ;
GENERAL INFORMATION SECTION
——————————————————————————-
Tuning Task Name : TUNE_MY_SCRIPT
Tuning Task Owner : SYS
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 30
Completion Status : COMPLETED
Started at : 05/02/2014 16:43:10
Completed at : 05/02/2014 16:43:27
——————————————————————————-
Schema Name: KAMIL
SQL ID : 3s9naj7p4rf3v
SQL Text : select * from tuning_test_table where object_id = 3
——————————————————————————-
FINDINGS SECTION (3 findings)
——————————————————————————-
1- Statistics Finding
———————
Table “KAMIL”.”TUNING_TEST_TABLE” was not analyzed.
Recommendation
————–
– Consider collecting optimizer statistics for this table.
execute dbms_stats.gather_table_stats(ownname => ‘KAMIL’, tabname =>
‘TUNING_TEST_TABLE’, estimate_percent =>
DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => ‘FOR ALL COLUMNS SIZE
AUTO’);
Rationale
———
The optimizer requires up-to-date statistics for the table in order to
select a good execution plan.
2- SQL Profile Finding (see explain plans section below)
——————————————————–
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 86.12%)
——————————————
– Consider accepting the recommended SQL profile to use parallel execution
for this statement.
execute dbms_sqltune.accept_sql_profile(task_name => ‘TUNE_MY_SCRIPT’,
task_owner => ‘SYS’, replace => TRUE, profile_type =>
DBMS_SQLTUNE.PX_PROFILE);
Executing this query parallel with DOP 8 will improve its response time
86.13% over the original plan. However, there is some cost in enabling
parallel execution. It will increase the statement’s resource consumption by
an estimated 10.97% which may result in a reduction of system throughput.
Also, because these resources are consumed over a much smaller duration, the
response time of concurrent statements might be negatively impacted if
sufficient hardware capacity is not available.
The following data shows some sampled statistics for this SQL from the past
week and projected weekly values when parallel execution is enabled.
Past week sampled statistics for this SQL
—————————————–
Number of executions 0
Percent of total activity 0
Percent of samples with #Active Sessions > 2*CPU 0
Weekly DB time (in sec) 0
Projected statistics with Parallel Execution
——————————————–
Weekly DB time (in sec) 0
3- Index Finding (see explain plans section below)
————————————————–
The execution plan of this statement can be improved by creating one or more
indices.
Recommendation (estimated benefit: 99.98%)
——————————————
– Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
create index KAMIL.IDX$$_13C50001 on KAMIL.TUNING_TEST_TABLE(“OBJECT_ID”);
Rationale
———
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 single statement. This
will allow to get comprehensive index recommendations which takes into
account index maintenance overhead and additional space consumption.
——————————————————————————-
EXPLAIN PLANS SECTION
——————————————————————————-
1- Original
———–
Plan hash value: 2266226955
—————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————————
| 0 | SELECT STATEMENT | | 761 | 153K| 18832 (1)| 00:03:46 |
|* 1 | TABLE ACCESS FULL| TUNING_TEST_TABLE | 761 | 153K| 18832 (1)| 00:03:46 |
—————————————————————————————
Predicate Information (identified by operation id):
—————————————————
1 – filter(“OBJECT_ID”=3)
2- Using New Indices
——————–
Plan hash value: 2511906253
————————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————————————-
| 0 | SELECT STATEMENT | | 64 | 13248 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TUNING_TEST_TABLE | 64 | 13248 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX$$_13C50001 | 64 | | 1 (0)| 00:00:01 |
————————————————————————————————-
Predicate Information (identified by operation id):
—————————————————
2 – access(“OBJECT_ID”=3)
3- Using Parallel Execution
—————————
Plan hash value: 2323061478
———————————————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
———————————————————————————————————————–
| 0 | SELECT STATEMENT | | 761 | 153K| 2612 (1)| 00:00:32 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 761 | 153K| 2612 (1)| 00:00:32 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 761 | 153K| 2612 (1)| 00:00:32 | Q1,00 | PCWC | |
|* 4 | TABLE ACCESS FULL| TUNING_TEST_TABLE | 761 | 153K| 2612 (1)| 00:00:32 | Q1,00 | PCWP | |
———————————————————————————————————————–
Predicate Information (identified by operation id):
—————————————————
4 – filter(“OBJECT_ID”=3)
——————————————————————————-
Tablomuzu yeni create ettiğimiz için istatistiğini bulamadı ve öncelikle istatistikleri güncellememizi isteyerek scriptinide hazırlayıp bize verdi. İkinci olarak daha hızlı çalışacağını düşündüğü yeni bir profile buldu ve bu sql’ e profile’ i nasıl atayacağımızı belirten scriptide hazırlayıp verdi. Son olarak select sonucunda where koşulu ile belirtmiş olduğumuz alanın üzerinde bir index olmadığını ve create edilmesi durumunda %99 oranında bir iyileşme olacağını belirtti ve yine scriptinide hazırlayıp vermiş oldu.
Son durumda taskımızın durumuna bakalım ;
SELECT owner own,
task_id id,
task_name name,
execution_start exec_start,
execution_end exec_end,
status stat
FROM dba_advisor_log
WHERE owner = ‘SYS’ AND task_name = ‘TUNE_MY_SCRIPT’ ;
OWN ID NAME EXEC_START EXEC_END STAT
SYS 5061 TUNE_MY_SCRIPT 02.05.2014 16:43:10 02.05.2014 16:43:27 COMPLETED
Bu paketi kullanarak performansını beğenmediğiniz sorgularınızı bide oracle tarafından tune edilmesini sağlayabilirsiniz.
