???SQL Profile????SQL???????
???????????? ???????[ 2014/8/12 10:28:48 ] ????????SQL ?????
?????????????????????SQL ??????
????SQL> execute dbms_sqltune.accept_sql_profile(task_name =>'my_sql_tuning_task_2014080804'?? replace => TRUE??force_match => TRUE);
????PL/SQL procedure successfully completed.
????????????????
SQL> select * from v_zzzd_ylbx_ylfymxcx where aac002='430703198202280017';
AAC001 AAC002 AAB301 AKF008 AKF010 AKF011 AKF012 AKF013 AKF014
------- -------------------- ------- ----------- -------- ------- -------- -------- -------
44499 430703198202280017 430701 4307000305 18000304 ??? 19 19 0
44499 430703198202280017 430701 4307030186 14200513 ??? 34 34 0
44499 430703198202280017 430701 4307000070 11535710 ??? 7 7 0
44499 430703198202280017 430701 4307000211 13157523 ??? 10 10 0
44499 430703198202280017 430701 4307000178 10504509 ??? 37.2 37.2 0
44499 430703198202280017 430701 4307000025 14186783 ??? 6.5 6.5 0
44499 430703198202280017 430701 4307000211 18855092 ??? 51 51 0
44499 430703198202280017 430701 4307000025 23298689 ??? 32 32 0
44499 430703198202280017 430701 4307000305 17251025 ??? 20 20 0
44499 430703198202280017 430701 4307000211 11246538 ??? 10.5 10.5 0
44499 430703198202280017 430701 4307000011 20015343 ???? 20 20 0
44499 430703198202280017 430701 4307000135 13248044 ??? 103.2 103.2 0
44499 430703198202280017 430701 4307000070 17745955 ??? 20 20 0
44499 430703198202280017 430701 4307000011 23548511 ???? 94.2 94.2 0
44499 430703198202280017 430701 4307000305 18000319 ??? 16 16 0
44499 430703198202280017 430701 4307000025 20291585 ??? 374 374 0
44499 430703198202280017 430701 4307000075 11425923 ??? 11.8 11.8 0
44499 430703198202280017 430701 4307000089 23298593 ??? 170.8 170.8 0
44499 430703198202280017 430701 4307000110 11548588 ??? 28.5 28.5 0
44499 430703198202280017 430701 4307000011 18454938 ???? 105.8 105.8 0
44499 430703198202280017 430701 4307000075 11757756 ??? 282.7 282.7 0
44499 430703198202280017 430701 4307000025 10545113 ??? 340.8 340.8 0
44499 430703198202280017 430701 4307000285 17325032 ??? 67.5 67.5 0
44499 430703198202280017 430701 4307000070 17341126 ??? 87 87 0
44499 430703198202280017 430701 4307000211 17655418 ??? 20 20 0
44499 430703198202280017 430701 4307000011 19042114 ???? 127.2 127.2 0
44499 430703198202280017 430701 4307000211 18070864 ??? 6 6 0
44499 430703198202280017 430701 4307000011 23547574 ???? 36 36 0
28 rows selected.
Elapsed: 00:00:00.01
SQL> select * from table(dbms_xplan.display_cursor(null??null??'advanced'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------
SQL_ID 1n2t3u0q0gmhz?? child number 0
-------------------------------------
select * from v_zzzd_ylbx_ylfymxcx where aac002='430703198202280017'
Plan hash value: 484693682
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 25 (100)| |
| 1 | HASH GROUP BY | | 251 | 53965 | 25 (4)| 00:00:01 |
|* 2 | TABLE ACCESS BY INDEX ROWID | MT_PAY_RECORD_FIN | 1 | 44 | 1 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 251 | 53965 | 24 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 28 | 4788 | 19 (6)| 00:00:01 |
| 5 | NESTED LOOPS | | 28 | 4284 | 18 (6)| 00:00:01 |
| 6 | NESTED LOOPS | | 28 | 4116 | 17 (6)| 00:00:01 |
| 7 | NESTED LOOPS | | 28 | 4060 | 16 (7)| 00:00:01 |
| 8 | NESTED LOOPS | | 28 | 3108 | 10 (0)| 00:00:01 |
|* 9 | HASH JOIN | | 28 | 2968 | 9 (0)| 00:00:01 |
| 10 | TABLE ACCESS FULL | BS_BIZTYPE | 96 | 1728 | 3 (0)| 00:00:01 |
| 11 | INLIST ITERATOR | | | | | |
| 12 | TABLE ACCESS BY INDEX ROWID| MT_BIZ_FIN | 79 | 6952 | 6 (0)| 00:00:01 |
|* 13 | INDEX RANGE SCAN | IDX$$_429C0001 | 27 | | 1 (0)| 00:00:01 |
|* 14 | INDEX UNIQUE SCAN | PK_BS_CORP | 1 | 5 | 1 (0)| 00:00:01 |
| 15 | TABLE ACCESS BY INDEX ROWID | BS_HOSPITAL | 1 | 34 | 1 (0)| 00:00:01 |
|* 16 | INDEX UNIQUE SCAN | PK_BS_HOSPITAL | 1 | | 1 (0)| 00:00:01 |
|* 17 | INDEX UNIQUE SCAN | PK_BS_HOSP_LEVEL | 1 | 2 | 1 (0)| 00:00:01 |
|* 18 | INDEX UNIQUE SCAN | PK_BS_INSURED | 1 | 6 | 1 (0)| 00:00:01 |
|* 19 | INDEX RANGE SCAN | INX_BS_DISEASE_01 | 1 | 18 | 1 (0)| 00:00:01 |
|* 20 | INDEX RANGE SCAN | IDX$$_429C0002 | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$F5BB74E1
2 - SEL$F5BB74E1 / B@SEL$2
10 - SEL$F5BB74E1 / F@SEL$2
12 - SEL$F5BB74E1 / A@SEL$2
13 - SEL$F5BB74E1 / A@SEL$2
14 - SEL$F5BB74E1 / G@SEL$2
15 - SEL$F5BB74E1 / D@SEL$2
16 - SEL$F5BB74E1 / D@SEL$2
17 - SEL$F5BB74E1 / E@SEL$2
18 - SEL$F5BB74E1 / H@SEL$2
19 - SEL$F5BB74E1 / C@SEL$2
20 - SEL$F5BB74E1 / B@SEL$2
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
OPT_PARAM('optimizer_index_cost_adj' 20)
OPT_PARAM('optimizer_index_caching' 90)
ALL_ROWS
OUTLINE_LEAF(@"SEL$F5BB74E1")
MERGE(@"SEL$2")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
FULL(@"SEL$F5BB74E1" "F"@"SEL$2")
INDEX_RS_ASC(@"SEL$F5BB74E1" "A"@"SEL$2" ("MT_BIZ_FIN"."IDCARD" "MT_BIZ_FIN"."VALID_FLAG"
"MT_BIZ_FIN"."PERS_TYPE" "MT_BIZ_FIN"."BIZ_TYPE"))
NUM_INDEX_KEYS(@"SEL$F5BB74E1" "A"@"SEL$2" "IDX$$_429C0001" 3)
INDEX(@"SEL$F5BB74E1" "G"@"SEL$2" ("BS_CORP"."CORP_ID"))
INDEX_RS_ASC(@"SEL$F5BB74E1" "D"@"SEL$2" ("BS_HOSPITAL"."HOSPITAL_ID"))
INDEX(@"SEL$F5BB74E1" "E"@"SEL$2" ("BS_HOSP_LEVEL"."HOSP_LEVEL"))
INDEX(@"SEL$F5BB74E1" "H"@"SEL$2" ("BS_INSURED"."INDI_ID"))
INDEX(@"SEL$F5BB74E1" "C"@"SEL$2" ("BS_DISEASE"."CENTER_ID" "BS_DISEASE"."ICD"))
INDEX(@"SEL$F5BB74E1" "B"@"SEL$2" ("MT_PAY_RECORD_FIN"."HOSPITAL_ID"
"MT_PAY_RECORD_FIN"."SERIAL_NO"))
LEADING(@"SEL$F5BB74E1" "F"@"SEL$2" "A"@"SEL$2" "G"@"SEL$2" "D"@"SEL$2" "E"@"SEL$2" "H"@"SEL$2"
"C"@"SEL$2" "B"@"SEL$2")
USE_HASH(@"SEL$F5BB74E1" "A"@"SEL$2")
USE_NL(@"SEL$F5BB74E1" "G"@"SEL$2")
USE_NL(@"SEL$F5BB74E1" "D"@"SEL$2")
USE_NL(@"SEL$F5BB74E1" "E"@"SEL$2")
USE_NL(@"SEL$F5BB74E1" "H"@"SEL$2")
USE_NL(@"SEL$F5BB74E1" "C"@"SEL$2")
USE_NL(@"SEL$F5BB74E1" "B"@"SEL$2")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("B"."VALID_FLAG"='1')
9 - access("A"."BIZ_TYPE"="F"."BIZ_TYPE" AND "A"."CENTER_ID"="F"."CENTER_ID")
13 - access("A"."IDCARD"='430703198202280017' AND "A"."VALID_FLAG"='1' AND (("A"."PERS_TYPE"='1'
OR "A"."PERS_TYPE"='2')))
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'))
14 - access("A"."CORP_ID"="G"."CORP_ID")
16 - access("A"."HOSPITAL_ID"="D"."HOSPITAL_ID")
17 - access("D"."HOSP_LEVEL"="E"."HOSP_LEVEL")
18 - access("H"."INDI_ID"="A"."INDI_ID")
19 - access("A"."CENTER_ID"="C"."CENTER_ID" AND "A"."FIN_DISEASE"="C"."ICD")
20 - access("A"."HOSPITAL_ID"="B"."HOSPITAL_ID" AND "A"."SERIAL_NO"="B"."SERIAL_NO")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "A"."INDI_ID"[NUMBER??22]?? "A"."IDCARD"[VARCHAR2??25]?? "A"."CENTER_ID"[VARCHAR2??10]??
"A"."NAME"[VARCHAR2??20]?? "A"."HOSPITAL_ID"[VARCHAR2??20]?? "D"."HOSPITAL_NAME"[VARCHAR2??70]??
"A"."SERIAL_NO"[VARCHAR2??16]?? "F"."BIZ_NAME"[VARCHAR2??20]?? "A"."FIN_DATE"[DATE??7]??
"A"."IN_DAYS"[NUMBER??22]?? SUM("B"."REAL_PAY")[22]?? SUM(CASE "B"."FUND_ID" WHEN '003' THEN
"B"."REAL_PAY" ELSE 0 END )[22]?? SUM(CASE "B"."FUND_ID" WHEN '001' THEN "B"."REAL_PAY" ELSE 0 END
)[22]?? SUM(CASE WHEN (("B"."FUND_ID"='999' OR "B"."FUND_ID"='003') AND
("B"."POLICY_ITEM_CODE"='S00' OR "B"."POLICY_ITEM_CODE"='S01' OR "B"."POLICY_ITEM_CODE"='C001' OR
"B"."POLICY_ITEM_CODE"='C004''C006')) THEN "B"."REAL_PAY" ELSE 0 END )[22]?? SUM(CASE "B"."FUND_ID"
WHEN '003' THEN "B"."REAL_PAY" WHEN '999' THEN "B"."REAL_PAY" ELSE 0 END )[22]?? SUM(CASE
"B"."FUND_ID" WHEN '001' THEN "B"."REAL_PAY" WHEN '201' THEN "B"."REAL_PAY" WHEN '301' THEN
"B"."REAL_PAY" ELSE 0 END )[22]
2 - "B"."POLICY_ITEM_CODE"[VARCHAR2??20]?? "B"."FUND_ID"[VARCHAR2??3]?? "B"."REAL_PAY"[NUMBER??22]
3 - "A"."CENTER_ID"[VARCHAR2??10]?? "F"."BIZ_NAME"[VARCHAR2??20]?? "A"."HOSPITAL_ID"[VARCHAR2??20]??
"A"."SERIAL_NO"[VARCHAR2??16]?? "A"."INDI_ID"[NUMBER??22]?? "A"."NAME"[VARCHAR2??20]??
"A"."IDCARD"[VARCHAR2??25]?? "A"."IN_DAYS"[NUMBER??22]?? "A"."FIN_DATE"[DATE??7]??
"D"."HOSPITAL_NAME"[VARCHAR2??70]?? "B".ROWID[ROWID??10]
4 - "A"."CENTER_ID"[VARCHAR2??10]?? "F"."BIZ_NAME"[VARCHAR2??20]?? "A"."HOSPITAL_ID"[VARCHAR2??20]??
"A"."SERIAL_NO"[VARCHAR2??16]?? "A"."INDI_ID"[NUMBER??22]?? "A"."NAME"[VARCHAR2??20]??
"A"."IDCARD"[VARCHAR2??25]?? "A"."IN_DAYS"[NUMBER??22]?? "A"."FIN_DATE"[DATE??7]??
"D"."HOSPITAL_NAME"[VARCHAR2??70]
5 - "A"."CENTER_ID"[VARCHAR2??10]?? "F"."BIZ_NAME"[VARCHAR2??20]?? "A"."HOSPITAL_ID"[VARCHAR2??20]??
"A"."SERIAL_NO"[VARCHAR2??16]?? "A"."INDI_ID"[NUMBER??22]?? "A"."NAME"[VARCHAR2??20]??
"A"."IDCARD"[VARCHAR2??25]?? "A"."IN_DAYS"[NUMBER??22]?? "A"."FIN_DISEASE"[VARCHAR2??20]??
"A"."FIN_DATE"[DATE??7]?? "D"."HOSPITAL_NAME"[VARCHAR2??70]
6 - "A"."CENTER_ID"[VARCHAR2??10]?? "F"."BIZ_NAME"[VARCHAR2??20]?? "A"."HOSPITAL_ID"[VARCHAR2??20]??
"A"."SERIAL_NO"[VARCHAR2??16]?? "A"."INDI_ID"[NUMBER??22]?? "A"."NAME"[VARCHAR2??20]??
"A"."IDCARD"[VARCHAR2??25]?? "A"."IN_DAYS"[NUMBER??22]?? "A"."FIN_DISEASE"[VARCHAR2??20]??
"A"."FIN_DATE"[DATE??7]?? "D"."HOSPITAL_NAME"[VARCHAR2??70]
7 - "A"."CENTER_ID"[VARCHAR2??10]?? "F"."BIZ_NAME"[VARCHAR2??20]?? "A"."HOSPITAL_ID"[VARCHAR2??20]??
"A"."SERIAL_NO"[VARCHAR2??16]?? "A"."INDI_ID"[NUMBER??22]?? "A"."NAME"[VARCHAR2??20]??
"A"."IDCARD"[VARCHAR2??25]?? "A"."IN_DAYS"[NUMBER??22]?? "A"."FIN_DISEASE"[VARCHAR2??20]??
"A"."FIN_DATE"[DATE??7]?? "D"."HOSPITAL_NAME"[VARCHAR2??70]?? "D"."HOSP_LEVEL"[CHARACTER??1]
8 - "A"."CENTER_ID"[VARCHAR2??10]?? "F"."BIZ_NAME"[VARCHAR2??20]?? "A"."HOSPITAL_ID"[VARCHAR2??20]??
"A"."SERIAL_NO"[VARCHAR2??16]?? "A"."INDI_ID"[NUMBER??22]?? "A"."NAME"[VARCHAR2??20]??
"A"."IDCARD"[VARCHAR2??25]?? "A"."IN_DAYS"[NUMBER??22]?? "A"."FIN_DISEASE"[VARCHAR2??20]??
"A"."FIN_DATE"[DATE??7]
9 - (#keys=2) "A"."CENTER_ID"[VARCHAR2??10]?? "F"."BIZ_NAME"[VARCHAR2??20]??
"A"."HOSPITAL_ID"[VARCHAR2??20]?? "A"."SERIAL_NO"[VARCHAR2??16]?? "A"."INDI_ID"[NUMBER??22]??
"A"."NAME"[VARCHAR2??20]?? "A"."IDCARD"[VARCHAR2??25]?? "A"."CORP_ID"[NUMBER??22]??
"A"."IN_DAYS"[NUMBER??22]?? "A"."FIN_DISEASE"[VARCHAR2??20]?? "A"."FIN_DATE"[DATE??7]
10 - "F"."CENTER_ID"[VARCHAR2??10]?? "F"."BIZ_TYPE"[CHARACTER??2]?? "F"."BIZ_NAME"[VARCHAR2??20]
11 - "A"."HOSPITAL_ID"[VARCHAR2??20]?? "A"."SERIAL_NO"[VARCHAR2??16]?? "A"."BIZ_TYPE"[VARCHAR2??2]??
"A"."INDI_ID"[NUMBER??22]?? "A"."NAME"[VARCHAR2??20]?? "A"."IDCARD"[VARCHAR2??25]??
"A"."CORP_ID"[NUMBER??22]?? "A"."IN_DAYS"[NUMBER??22]?? "A"."FIN_DISEASE"[VARCHAR2??20]??
"A"."FIN_DATE"[DATE??7]?? "A"."CENTER_ID"[VARCHAR2??10]
12 - "A"."HOSPITAL_ID"[VARCHAR2??20]?? "A"."SERIAL_NO"[VARCHAR2??16]?? "A"."BIZ_TYPE"[VARCHAR2??2]??
"A"."INDI_ID"[NUMBER??22]?? "A"."NAME"[VARCHAR2??20]?? "A"."PERS_TYPE"[VARCHAR2??3]??
"A"."IDCARD"[VARCHAR2??25]?? "A"."CORP_ID"[NUMBER??22]?? "A"."IN_DAYS"[NUMBER??22]??
"A"."FIN_DISEASE"[VARCHAR2??20]?? "A"."FIN_DATE"[DATE??7]?? "A"."CENTER_ID"[VARCHAR2??10]
13 - "A".ROWID[ROWID??10]?? "A"."IDCARD"[VARCHAR2??25]?? "A"."PERS_TYPE"[VARCHAR2??3]??
"A"."BIZ_TYPE"[VARCHAR2??2]
15 - "D"."HOSPITAL_NAME"[VARCHAR2??70]?? "D"."HOSP_LEVEL"[CHARACTER??1]
16 - "D".ROWID[ROWID??10]
20 - "B".ROWID[ROWID??10]
Note
-----
- SQL profile "SYS_SQLPROF_0151ed60f3d28000" used for this statement
163 rows selected.
??????SQL profile "SYS_SQLPROF_0151ed60f3d28000" used for this statement ???????????????????SQL??????
????????????????0.1????
???????????????????????漰???????????????????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