???SQL Profile????SQL???????
???????????? ???????[ 2014/8/12 10:28:48 ] ????????SQL ?????
????????????????????????
SQL>select dbms_sqltune.report_tuning_task('my_sql_tuning_task_2014080803') from dual;
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : my_sql_tuning_task_2014080803
Tuning Task Owner : INSUR_CHANGDE
Scope : COMPREHENSIVE
Time Limit(seconds) : 60
Completion Status : COMPLETED
Started at : 08/08/2014 19:42:47
Completed at : 08/08/2014 19:43:49
Number of Index Findings : 1
Number of SQL Restructure Findings: 1
Number of Errors : 1
-------------------------------------------------------------------------------
Schema Name: INSUR_CHANGDE
SQL ID : 0rpt6bzp60cjm
SQL Text : select * from v_zzzd_ylbx_ylfymxcx where
aac002='430703198202280017'
-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------
1- Index Finding (see explain plans section below)
--------------------------------------------------
????????????????????????????????????м????
????Recommendation (estimated benefit: 99.98%)
????------------------------------------------
????- ???????п???????????????? Access Advisor ??????????????????
????create index INSUR_CHANGDE.IDX$$_429C0001 on
????INSUR_CHANGDE.MT_BIZ_FIN("IDCARD"??TO_NUMBER("VALID_FLAG")??"PERS_TYPE"??"BIZ_
????TYPE");
?????????????IDX$$_429C0001???????TO_NUMBER("VALID_FLAG")?????????MT_BIZ_FIN?е?valid_flag??varchar2???????????д????valid_flag=1?????
????- ???????п???????????????? Access Advisor ??????????????????
????create index INSUR_CHANGDE.IDX$$_429C0002 on
????INSUR_CHANGDE.MT_PAY_RECORD_FIN("HOSPITAL_ID"??"SERIAL_NO");
????Rationale
????---------
?????????????????????????????????????м????????? ??????? SQL ?????????? "Access Advisor"
????????????????????????????????????????????????鰸?? ?????????????????????????????????
????2- Restructure SQL finding (see plan 1 in explain plans section)
????----------------------------------------------------------------
????ν?? TO_NUMBER("A"."VALID_FLAG")=1 (????м?????? ID 9 ?????) ?????????? "VALID_FLAG"
???????????????????????????????????????????????????Ч?????? "INSUR_CHANGDE"."MT_BIZ_FIN" ????????
?????????????MT_BIZ_FIN?е?valid_flag??varchar2???????????д????valid_flag=1?????
????Recommendation
????--------------
????- ??ν????д???????????????????
????Rationale
????---------
???????ν???????????????????????????????е?????????????????????? ?????????????????????
-------------------------------------------------------------------------------
ERRORS SECTION
-------------------------------------------------------------------------------
- ?????????????ж???????????????????????????60??????
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original
-----------
Plan hash value: 3562745886
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 1505 | 127K (2)| 00:25:25 |
| 1 | HASH GROUP BY | | 7 | 1505 | 127K (2)| 00:25:25 |
| 2 | NESTED LOOPS | | 7 | 1505 | 127K (2)| 00:25:25 |
| 3 | NESTED LOOPS | | 7 | 1491 | 127K (2)| 00:25:25 |
| 4 | NESTED LOOPS | | 7 | 1253 | 127K (2)| 00:25:25 |
| 5 | NESTED LOOPS | | 7 | 1127 | 127K (2)| 00:25:25 |
| 6 | NESTED LOOPS | | 7 | 1085 | 127K (2)| 00:25:25 |
| 7 | NESTED LOOPS | | 14 | 1554 | 127K (2)| 00:25:25 |
| 8 | NESTED LOOPS | | 14 | 1484 | 127K (2)| 00:25:25 |
|* 9 | TABLE ACCESS FULL | MT_BIZ_FIN | 14 | 1232 | 127K (2)| 00:25:25 |
| 10 | TABLE ACCESS BY INDEX ROWID| BS_BIZTYPE | 1 | 18 | 1 (0)| 00:00:01 |
|* 11 | INDEX UNIQUE SCAN | PK_BS_BIZTYPE | 1 | | 1 (0)| 00:00:01 |
|* 12 | INDEX UNIQUE SCAN | PK_BS_CORP | 1 | 5 | 1 (0)| 00:00:01 |
|* 13 | TABLE ACCESS BY INDEX ROWID | MT_PAY_RECORD_FIN | 1 | 44 | 1 (0)| 00:00:01 |
|* 14 | INDEX RANGE SCAN | IDX_MT_PAY_RECORD_FIN_1 | 1 | | 1 (0)| 00:00:01 |
|* 15 | INDEX UNIQUE SCAN | PK_BS_INSURED | 1 | 6 | 1 (0)| 00:00:01 |
|* 16 | INDEX RANGE SCAN | INX_BS_DISEASE_01 | 1 | 18 | 1 (0)| 00:00:01 |
| 17 | TABLE ACCESS BY INDEX ROWID | BS_HOSPITAL | 1 | 34 | 1 (0)| 00:00:01 |
|* 18 | INDEX UNIQUE SCAN | PK_BS_HOSPITAL | 1 | | 1 (0)| 00:00:01 |
|* 19 | INDEX UNIQUE SCAN | PK_BS_HOSP_LEVEL | 1 | 2 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
9 - filter("A"."IDCARD"='430703198202280017' AND TO_NUMBER("A"."VALID_FLAG")=1 AND
("A"."PERS_TYPE"='1' OR "A"."PERS_TYPE"='2') AND ("A"."BIZ_TYPE"='10' OR "A"."BIZ_TYPE"='11' OR
"A"."BIZ_TYPE"='12' OR "A"."BIZ_TYPE"='13' OR "A"."BIZ_TYPE"='16' OR "A"."BIZ_TYPE"='17'))
11 - access("A"."CENTER_ID"="F"."CENTER_ID" AND "A"."BIZ_TYPE"="F"."BIZ_TYPE")
12 - access("A"."CORP_ID"="G"."CORP_ID")
13 - filter(TO_NUMBER("B"."VALID_FLAG")=1)
14 - access("A"."HOSPITAL_ID"="B"."HOSPITAL_ID" AND "A"."SERIAL_NO"="B"."SERIAL_NO")
15 - access("H"."INDI_ID"="A"."INDI_ID")
16 - access("A"."CENTER_ID"="C"."CENTER_ID" AND "A"."FIN_DISEASE"="C"."ICD")
18 - access("A"."HOSPITAL_ID"="D"."HOSPITAL_ID")
19 - access("D"."HOSP_LEVEL"="E"."HOSP_LEVEL")
???????????????鴴???????????????м??
2- Using New Indices
--------------------
Plan hash value: 2373509962
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 1505 | 14 (8)| 00:00:01 |
| 1 | HASH GROUP BY | | 7 | 1505 | 14 (8)| 00:00:01 |
| 2 | NESTED LOOPS | | 7 | 1505 | 13 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 7 | 1470 | 12 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 7 | 1428 | 11 (0)| 00:00:01 |
| 5 | NESTED LOOPS | | 7 | 1302 | 10 (0)| 00:00:01 |
| 6 | NESTED LOOPS | | 7 | 1288 | 9 (0)| 00:00:01 |
| 7 | NESTED LOOPS | | 7 | 1050 | 7 (0)| 00:00:01 |
| 8 | NESTED LOOPS | | 14 | 1484 | 4 (0)| 00:00:01 |
| 9 | INLIST ITERATOR | | | | | |
| 10 | TABLE ACCESS BY INDEX ROWID| MT_BIZ_FIN | 14 | 1232 | 2 (0)| 00:00:01 |
|* 11 | INDEX RANGE SCAN | IDX$$_429C0001 | 14 | | 1 (0)| 00:00:01 |
| 12 | TABLE ACCESS BY INDEX ROWID | BS_BIZTYPE | 1 | 18 | 1 (0)| 00:00:01 |
|* 13 | INDEX UNIQUE SCAN | PK_BS_BIZTYPE | 1 | | 1 (0)| 00:00:01 |
|* 14 | TABLE ACCESS BY INDEX ROWID | MT_PAY_RECORD_FIN | 1 | 44 | 1 (0)| 00:00:01 |
|* 15 | INDEX RANGE SCAN | IDX$$_429C0002 | 1 | | 1 (0)| 00:00:01 |
| 16 | TABLE ACCESS BY INDEX ROWID | BS_HOSPITAL | 1 | 34 | 1 (0)| 00:00:01 |
|* 17 | INDEX UNIQUE SCAN | PK_BS_HOSPITAL | 1 | | 1 (0)| 00:00:01 |
|* 18 | INDEX UNIQUE SCAN | PK_BS_HOSP_LEVEL | 1 | 2 | 1 (0)| 00:00:01 |
|* 19 | INDEX RANGE SCAN | INX_BS_DISEASE_01 | 1 | 18 | 1 (0)| 00:00:01 |
|* 20 | INDEX UNIQUE SCAN | PK_BS_INSURED | 1 | 6 | 1 (0)| 00:00:01 |
|* 21 | INDEX UNIQUE SCAN | PK_BS_CORP | 1 | 5 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
11 - access("A"."IDCARD"='430703198202280017' AND "MT_BIZ_FIN".???)
filter("A"."BIZ_TYPE"='10' OR "A"."BIZ_TYPE"='11' OR "A"."BIZ_TYPE"='12' OR
"A"."BIZ_TYPE"='13' OR "A"."BIZ_TYPE"='16' OR "A"."BIZ_TYPE"='17')
13 - access("A"."CENTER_ID"="F"."CENTER_ID" AND "A"."BIZ_TYPE"="F"."BIZ_TYPE")
14 - filter(TO_NUMBER("B"."VALID_FLAG")=1)
15 - access("A"."HOSPITAL_ID"="B"."HOSPITAL_ID" AND "A"."SERIAL_NO"="B"."SERIAL_NO")
17 - access("A"."HOSPITAL_ID"="D"."HOSPITAL_ID")
18 - access("D"."HOSP_LEVEL"="E"."HOSP_LEVEL")
19 - access("A"."CENTER_ID"="C"."CENTER_ID" AND "A"."FIN_DISEASE"="C"."ICD")
20 - access("H"."INDI_ID"="A"."INDI_ID")
21 - access("A"."CORP_ID"="G"."CORP_ID")
-------------------------------------------------------------------------------
?????????????????????????ж?????????ν???SQL?????????????????????????600??
SQL> declare
2 my_task_name varchar2(30);
3 my_sqltext clob;
4 begin
5 my_sqltext :='select * from v_zzzd_ylbx_ylfymxcx where aac002=''430703198202280017''';
6 my_task_name :=dbms_sqltune.create_tuning_task(
7 sql_text => my_sqltext??
8 user_name => 'INSUR_CHANGDE'??
9 scope=>'COMPREHENSIVE'??
10 time_limit=>600??
11 task_name => 'my_sql_tuning_task_2014080804'??
12 description=>'Task to tune a query on table v_zzzd_ylbx_ylfymxcx');
13 end;
14 /
PL/SQL procedure successfully completed.
SQL>
SQL> begin
2 dbms_sqltune.execute_tuning_task(task_name=>'my_sql_tuning_task_2014080804');
3 end;
4 /
PL/SQL procedure successfully completed.
???????????????????????漰???????????????????SPASVOС??(021-61079698-8054)?????????????????????????
??????
?????????????????????????Щ????????????????????TC???????????????Щ???????????????????????????????????????????????(java .net ?????)???mysql???????????????????ж????д???Python???????????????(DB2)??????BufferPool????????????????????????????????6??????????????????滮???????????????-????????SQL Server???????????????????λ?????PHP??SQL????????????????????Pythonд???NoSQL????????? SQL ?е????????????? SQL ?е?????????Java???????:?????MySQL???????
???·???
??????????????????
2023/3/23 14:23:39???д?ò??????????
2023/3/22 16:17:39????????????????????Щ??
2022/6/14 16:14:27??????????????????????????
2021/10/18 15:37:44???????????????
2021/9/17 15:19:29???·???????·
2021/9/14 15:42:25?????????????
2021/5/28 17:25:47??????APP??????????
2021/5/8 17:01:11????????
?????????App Bug???????????????????????Jmeter?????????QC??????APP????????????????app?????е????????jenkins+testng+ant+webdriver??????????????JMeter????HTTP???????Selenium 2.0 WebDriver ??????
sales@spasvo.com