ORALCE优化

2024-02-28 19:32

文章标签 优化 oralce

本文主要是介绍ORALCE优化，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

1、PL/SQL执行Explain

Plan Hash Value : 1703851322
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 715 | 92950 | 4498 | 00:00:54 |
| * 1 | HASH JOIN | | 715 | 92950 | 4498 | 00:00:54 |
| * 2 | TABLE ACCESS FULL | SMALLTABLE | 4 | 260 | 104 | 00:00:02 |
| * 3 | TABLE ACCESS FULL | BIGTABLE | 176 | 11440 | 4394 | 00:00:53 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 1 - access("A"."ID"="B"."ID")
* 2 - filter("B"."ID"=40000)
* 3 - filter("A"."ID"=40000)

Note
-----
- dynamic sampling used for this statement

2、SQL PLUS（物理读）

执行set autotrace traceonly;然后输入语句

0 recursive calls
0 db block gets
25065 consistent gets
792 physical reads
649988 redo size
740 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

3、SQL Tuning Advisor

SQL> create user bamboo identified by bamboo;
User created.
SQL> grant connect,resource to bamboo;
Grant succeeded.
SQL> grant advisor to bamboo;
Grant succeeded.

SQL> create table bigtable (id number(10),name varchar2(100));
Table created.

SQL> begin
2 for i in 1..5000000 loop
3 insert into bigtable values(i,'test'||i);
4 end loop;
5 end;
6 /

PL/SQL procedure successfully completed.

SQL> commti;

SQL> create table smalltable (id number(10),name varchar2(100));
Table created.

SQL> begin
2 for i in 1..100000 loop
3 insert into smalltable values(i,'test'||i);
4 end loop;
5 end;
6 /

PL/SQL procedure successfully completed.
SQL> commit;

3.然后对bigtable和smalltable做一个等连接查询，然后跟踪其执行计划
SQL> select a.id,a.name,b.id,b.name from bigtable a,smalltable b where a.id=b.id and a.id>1;

ID NAME ID NAME
---------- ---------------------------------------- ---------- ----------------------------------------
.................................................
...............................................

Execution Plan
----------------------------------------------------------
Plan hash value: 1703851322

---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 839 | 106K| 3656 (5)| 00:00:44 |
|* 1 | HASH JOIN | | 839 | 106K| 3656 (5)| 00:00:44 |
|* 2 | TABLE ACCESS FULL| SMALLTABLE | 5 | 325 | 71 (3)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| BIGTABLE | 173 | 11245 | 3584 (5)| 00:00:44 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("A"."ID"="B"."ID")
2 - filter("B"."ID"=40000)
3 - filter("A"."ID"=40000)

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
9 recursive calls
0 db block gets
16151 consistent gets
11469 physical reads
0 redo size
588 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed
熟悉执行计划的就可以看出，这个sql执行是很慢的，2个表都做的是全表扫描，并且其物理读是11469，按照优化的经验，给2个表的id创建索引，减少查询时候的物理读,下面我们就看看通过优化器，oracle能我们什么样的建议呢？

4.下面就通过在sql/plus里DBMS_SQLTUNE包的CREATE_TUNING_TASK来创建一个优化任务，然后通过DBMS_SQLTUNE.EXECUTE_TUNING_TASK来执行调优任务，生成调优建议

DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
BEGIN
my_sqltext := 'select a.id,a.name,b.id,b.name from bigtable a,smalltable b where a.id=b.id and a.id>1';
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => my_sqltext,
user_name => 'WMS',
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name => 'tuning_sql_test_2',
description => 'Task to tune a query on a specified table');
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'tuning_sql_test_2');
END;
/
5、执行任务
exec dbms_sqltune.execute_tuning_task('tuning_sql_test_2');

6、查询运行状态是否完成

SELECT task_name,status FROM USER_ADVISOR_TASKS WHERE task_name='tuning_sql_test_2';

7、查看优化结果(PL/SQL)然后点开那个点点查看
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'tuning_sql_test_2') from DUAL;