使用sql tunig advisor(1)

,B.PAY_TYPE,B.RECV_OPENACC_DEPT,D.OPENACC_DEPT FROM
bb_bargainchk_tmp A,bb_log_bargain B,bb_customer C,bb_account D
WHERE A.cust_id='P263767#04' AND A.check_id='999977'
COMPLETED
3. 查看结果
SQL> SET LONG 10000
SQL> SET LONGCHUNKSIZE 10000
SET LINESIZE 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'task_0714') FROM DUAL;SQL> SQL>
' AND (A.tran_type=''0'' OR A.tran_type=''1'' OR A.tran_type=''2'' OR A.tran_type=''3'' OR A.tran_type=''A'') '||
' AND A.credit_no=B.credit_no '||
1.创建task
SQL> DECLARE
2 my_task_name VARCHAR2(30);
3 my_sqltext CLOB;
4 BEGIN
5 my_sqltext :='SELECT A.credit_no,A.is_cur_level,B.recv_depart_name,B.recv_accno,C.deptname,B.pay_accno,B.amount,'||
select a good execution plan.
2- Statistics Finding
---------------------
Optimizer statistics for index "B2C20"."BB_LOG_BARGAIN_PAYACCNO" are stale.
Number of SQL Profile Findings : 1
-------------------------------------------------------------------------------
Schema Name: B2C20
SQL ID : 126bg15xwbxnn
Started at : 07/14/2010 12:46:15
Completed at : 07/14/2010 12:46:31
Number of Statistic Findings : 2
AND (B.status='0' OR B.status='1') AND
B.pay_accno=D.acc_no AND B.pay_custid=D.cust_id
AND A.is_cur_level='1' AND A.is_done='0' AND
SQL Text : SELECT A.credit_no,A.is_cur_level,B.recv_depart_name,B.recv_accno
,C.deptname,B.pay_accno,B.amount,eof,B.oper_name,B.
status,D.acc_alias,A.is_done,B.recv_acc_name,D.acc_name,B.BALANCE
20 ' AND B.pay_custid=D.cust_id '||
21 ' AND A.is_cur_level=''1'' '||
22 ' AND A.is_done=''0'' 'm<5000 '||
' AND B.pay_custid=C.cust_id '||
' AND (B.status=''0'' OR B.status=''1'') '||
10 11 12 13 14 15 16 17 18 19 ' AND B.pay_accno=D.acc_no '||
Recommendation
--------------
- Consider collecting optimizer statistics for this index.
execute dbms_stats.gather_index_stats(ownname => 'B2C20', indname =>
24 'ORDER BY B.mak_date DESC,A.credit_no ASC';
25 dbms_sqltune.drop_tuning_task ('task_0714');
26 my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
-------------------------------------------------------------------------------
1- Statistics Finding
---------------------
Optimizer statistics for index "B2C20"."BB_LOG_BARGAINPK" are stale.
AND (A.tran_type='0' OR A.tran_type='1' OR
A.tran_type='2' OR A.tran_type='3' OR A.tran_type='A')
AND A.credit_no=B.credit_no AND B.pay_custid=C.cust_id
27 sql_text => my_sqltext,
28 -- bind_list => sql_binds(anydata.ConvertNumber(100)),
29 user_name => 'B2C20',
30 scope => 'COMPREHENSIVE',
Recommendation
--------------
- Consider collecting optimizer statistics for this index.
execute dbms_stats.gather_index_stats(ownname => 'B2C20', indname =>
rownum<5000 ORDER BY B.mak_date DESC,A.credit_no ASC
-------------------------------------------------------------------------------
FINDINGS SECTION (3 findings)
8 'FROM '||
9 ' bb_bargainchk_tmp A,'||
'bb_log_bargain B,'||
'bb_customer C,'||
'bb_account D '||
' WHERE '||
' A.cust_id=''P263767#04'' AND A.check_id=''999977'' '||
END;
/ 32 33 34 35 36
PL/SQL procedure successfully completed.
2.执行task
SQL> BEGIN
2 DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'task_0714' );
Tuning Task Owner : SYS
Scope : COMPREHENSIVE
Time Limit(seconds) : 60
Completion Status : COMPLETED
SQL> set pages 10000
SQL> /
DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_0714')
----------------------------------------------------------------------------------------------------
3 END;
4 /
PL/SQL procedure successfully completed.
SQL> SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = 'task_0714';