自己管理型データベース: アプリケーションおよびSQLチューニング・ガイド

Size: px

Start display at page:

Download "自己管理型データベース: アプリケーションおよびSQLチューニング・ガイド"

みそらながだき
5 years ago
Views:

1 : SQL

2 : SQL... 3 SQL SQL :... 9 SQL SQL SQL TUNING SET SQL ADDM SQL SQL STS SQL DBMS_SQLTUNE SQL SQL Tuning Set : SQL 2

3 : SQL 10 2 a b 1 SQL SQL 3 1. SQL V$SQL SQL 2. SQL 3. SQL 3 SQL DBA SQL optimizer_mode first_rows_10 3. SQL SQL UNION UNION-ALL 4. : SQL 3

4 5. SQL INDEX full table scan index range scan SQL SQL 1000 SQL SQL 1 SQL 1: SQL DBA SQL : SQL 4

5 Oracle Database 10g Oracle Database 10g: Automatic Workload Repository AWR AWR 60 7 AWR CPU SQL Automatic Database Diagnostics Monitor ADDM : SQL ADDM SQL Oracle Database 10g Automatic Statistics Collection Oracle Database 10g SQL SQL OLTP SQL SQL SQL Tuning Advisor SQL Tuning Advisor 1 SQL SQL ADDM AWR SQL SQL Oracle Database 10g SQL Tuning Set STS STS SQL 2 Oracle Database 10g SQL : SQL 5

6 2: SQL SQL SQL SQL Tuning Set Oracle Enterprise Manager SQL SQL Tuning Advisor API SQL DBMS_SQLTUNE SQL Tuning Set SQL Oracle9i Database Oracle Database 10g SQL SQL a SQL b c SQL d SQL Oracle Database 10g SQL SQL : SQL 6

7 1 SQL SQL : SQL Tuning Advisor SQL Tuning Advisor SQL 4 1. : Automatic Tuning Optimizer 2. SQL : SQL SQL SQL 3. : 4. SQL : SQL SQL SQL Tuning Advisor 1 SQL : SQL 7

8 SQL Tuning Advisor Oracle SQL 3 SQL SQL Tuning Advisor SQL Oracle 1 Oracle 3: SQL SQL SQL Tuning Advisor SQL SQL SQL SQL SQL SQL : SQL 8

9 SQL Automatic SQL Advisor SQL SQL : SQL a b c d e : SQL 9

10 SQL all_rows first_rows SQL SQL SQL SQL first_rows SQL SQL SQL SQL SQL SQL Tuning Advisor SQL SQL SQL SQL SQL SQL SQL SQL SQL SQL SQL SQL SQL SQL DBMS_SQLTUNE 4 SQL 2 SQL SQL DBA SQL Enterprise Manager GUI SQL DBMS_SQLTUNE SQL Tuning Advisor : SQL 10

11 SQL Tuning Advisor SQL SQL DBA SQL SQL SQL SQL SQL SQL 4: SQL SQL SQL SQL SQL SQL SQL SQL SQL SQL SQL SQL 1 SQL : SQL 11

12 SQL SQL SQL Access Advisor Access Advisor SQL SQL Oracle Database 10g SQL Access Advisor SQL SQL UNION UNION-ALL SQL UNION UNION-ALL NOT EXIST NOT IN NULL SQL SQL SQL SQL SQL SQL SQL Tuning Advisor SQL SQL SQL SQL 3 : NOT IN NOT EXISTS NULL : SQL 12

13 UNION UNION ALL : SQL col = :bnd col :bnd : func(col) = :bnd, col + 1 = :bnd : 1 SQL SQL TUNING SET ADDM SQL AWR SQL SQL SQL SQL SQL AWR SQL SQL SQL Tuning Set STS Oracle Database 10g STS SQL SQL Tuning Set 1 SQL SQL SQL SQL Tuning Set SQL Automatic Workload Repository AWR SQL 5 SQL Tuning Set SQL Tuning Set SQL SQL SQL Tuning Set SQL : SQL 13

5: SQL Tuning Set SQL CPU SQL SQL SQL Tuning Set SQL Tuning Set STS DBMS_SQLTUNE SQL Tuning Set SQL Tuning Advisor SQL Tuning Advisor SQL SQL Tuning Set SQL SQL SQL

14 5: SQL Tuning Set SQL CPU SQL SQL SQL Tuning Set SQL Tuning Set STS DBMS_SQLTUNE SQL Tuning Set SQL Tuning Advisor SQL Tuning Advisor SQL SQL Tuning Set SQL SQL SQL Enterprise Manager EM EM SQL SQL Tuning Set SQL Tuning Advisor ADDM SQL EM Automatic Database Diagnostics Monitor ADDM SQL SQL CPU 1 SQL SQL Tuning Advisor 6: ADDM SQL : SQL 14

Set STS SQL Automatic Workload Repository AWR SQL SQL 8

15 SQL 1 SQL Enterprise Manager 7 SQL SQL SQL CPU SQL ID 1 SQL SQL Tuning Advisor 7: SQL STS Enterprise Manager SQL Tuning Set STS SQL Automatic Workload Repository AWR SQL SQL 8 Enterprise Manager STS SQL Tuning Advisor 8: SQL Tuning Set : SQL 15

SQL Tuning Advisor ADVISOR Oracle Enterprise Manager 9 Oracle Enterprise Manager SQL Tuning Options 1 SQL Tuning Advisor SQL SQL SQL Tuning Advisor

16 SQL Tuning Advisor ADVISOR Oracle Enterprise Manager 9 Oracle Enterprise Manager SQL Tuning Options 1 SQL Tuning Advisor SQL SQL SQL Tuning Advisor SQL SQL 60 9: SQL Tuning Advisor SQL SQL Tuning Advisor Oracle Enterprise Manager 1 SQL SQL Tuning Advisor SQL 1 SQL Oracle Enterprise Manager : SQL 16

17 10: SQL 11: SQL DBMS_SQLTUNE SQL Oracle Enterprise Manager DBMS_SQLTUNE SQL DBMS_SQLTUNE Oracle Dabase 10g SQL SQL Tuning Set SQL API SQL Tuning Advisor SQL Tuning Advisor : SQL 17

18 SQL SQL ADVISOR DBMS_SQLTUNE SQL create_tuning_task create_tuning_task SQL Tuning Set SQL SQL create_tuning_task CLOB create_tuning_task(sql_text => select * from emp where emp_id = :bnd, bind_list => sql_binds(anydata.convertnumber(100)), user_name => scott, scope => comprehensive, time_limit => 60, task_name => my_sql_tuning_task, description => task to tune a query on a specified employee ); bnd 100 SQL_BINDS SQL_BINDS Oracle Database 10g scott SQL Tuning Advisor SQL comprehensive 60 SQL 2 Automatic Workload Repository AWR SQL SQL SQL SQL_ID execute_tuning_task execute_tuning_task(task_name => my_sql_tuning_task ); : SQL 18

19 DBA_ADVISOR_LOG V$SESSIO_LONGOPS SQL Tuning Set report_tuning_task set long select report_tuning_task(task_name => my_sql_tuning_task )from dual; SELECT SQL SQL CLOB SQL DBA_ADVISOR_TASKS, DBA_ADVISOR_FINDINGS DBA_ADVISOR_RECOMMENDATIONS, DBA_ADVISOR_RATIONALE DBA Oracle Database 10g SQL SQL DBA_SQLTUNE_STATISTICS DBA_SQLTUNE_BINDS DBA_SQLTUNE_PLANS SQL SQL DBMS_SQLTUNE SQL Tuning Advisor SQL accept_sql_profile SQL SQL SQL CREATE ANY SQL PROFILE SQL SQL my_sql_tuning_task SQL SQL SQL my_sql_profile accept_sql_profile(task_name => my_sql_tuning_task, name => my_sql_profile ); SQL DBA_SQL_PROFILES alter_sql_profile SQL ALTER ANY SQL PROFILE SQL my_sql_profile disabled SQL SQL : SQL 19

20 alter_sql_profile(name => my_sql_profile, attribute_name => status, value => disabled ); SQL drop_sql_profile SQL DROP ANY SQL PROFILE SQL Tuning Set SQL Tuning Set sqlset DBMS_SQLTUNE SQL Tuning Set SQL SQL Tuning Advisor SQL Tuning Set SQL Tuning Advisor SQL Tuning Set create_sqlset my_sql_tuning_set SQL Tuning Set I/O SQL create_sqlset(sqlset_name => my_sql_tuning_set, description => I/O intensive workload ); SQL Tuning Set SQL Tuning Set ADMINISTER SQL TUNING SET ADMINISTER ANY SQL TUNING SET SQL Tuning Set load_sqlset SQL SQL Tuning Set Automatic Workload Repository AWR SQL Tuning Set SQL Tuning Set 10 50% / SQL peak baseline AWR my_sql_tuning_set SQL / 30 SQL -- open a ref cursor to select from the specified baseline open baseline_ref_cursor for select value(p) from table (dbms_sqltune.select_baseline( peak baseline, executions >= 10 and disk_reads/buffer_gets >= 0.5, null, : SQL 20

21 disk_reads/buffer_gets, null, null, null, 30)) p; -- load statements and their stats from the baseline into the STS dbms_sqltune.load_sqlset(sqlset_name => my_sql_tuning_set, populate_cursor => baseline_cur); SQL Tuning Set DBA select_sqlset SQL Tuning Set SQL SELECT * from TABLE(select_sqlset( my_sql_tuning_set, (disk_reads/buffer_gets) >= 0.75 )); SQL Tuning Set 75% / SQL SQL Tuning Set DBA_SQLSET DBA_SQLSET_STATEMENTS DBA_SQLSET_BINDS DBA SQL Tuning Set SQL delete_sqlset my_sql_tuning_set 50 SQL delete_sqlset(sqlset_name => my_sql_tuning_set, basic_filter => executions < 50 ); SQL Tuning Set drop_sqlse drop_sqlset(sqlset_name => my_sql_tuning_set ); Oracle Database 10g SQL SQL SQL SQL SQL SQL SQL SQL SQL Tuning Set SQL Oracle Enterprise Manager SQL SQL : SQL 21

22 SQL SQL SQL Oracle9i Oracle Database 10g SQL Oracle9i Oracle Database 10g 1 SQL Tuning Advisor 2 3 V$SQL Oracle Database 10g Oracle9i Database Oracle9i Database Oracle Database 10g SQL : SQL 22

23 : SQL : Mohammed Ziauddin Mohammed Zait Mughees Minhas Khaled Yagoub : Benoit Dageville Oracle Corporation World Headquarters 500 Oracle Parkway Redwood Shores, CA U.S.A. : : : Oracle Oracle Oracle Corporation Copyright 2005 Oracle Corporation All rights reserved.

Oracle Enterprise Manager 10g R2 Grid Control: データベース管理の新機能

Oracle Enterprise Manager 10g R2 Grid Control: 2005 8 Oracle Enterprise Manager 10g R2 Grid Control:... 3... 3 GRID CONTROL... 4... 4... 4... 5... 5 GRID CONTROL... 5... 5 SQL /... 6... 7 HANG ANLYSIS...