Shulou(Shulou.com)06/01 Report--

1. The SQL problem of "cousin" Today, "cousin" has a more urgent job. There is a historical expense report that must be queried before leaving work. It has been 2 hours since the end of the day. The number of concurrent queries has been increased to 40, but the report still cannot be queried. Moreover, this report will torture my cousin every month. I am about to get off work, and the time is approaching. Emergency help. Often harassed by the "cousins" of the unit, but also very urgent. The bitterness of DBA, all right, let go of the work at hand and help my cousin deal with the problem first. 2. Problem analysis DBA: can I have a look at your SQL? Cousin: select / * + parallel (T 40) * / * FROM ht.ht_tab_fy T WHERE EXISTS (SELECT 1 FROM ht2.ht_DEPT M WHERE T.DEPT_ID = M.DEPT_ID AND M.DEPT_TREE LIKE'% 611002%') AND T.DATA_MONTH = '201709'; DBA: send me this query result? Explain plan for select * FROM ht.ht_tab_fy T WHERE EXISTS (SELECT 1 FROM ht2.ht_DEPT M WHERE T.DEPT_ID = M.DEPT_ID AND M.DEPT_TREE LIKE'% 611002%') AND T.DATA_MONTH = '201709'; select * from table (dbms_xplan.display ()) Cousin:-| Id | Operation | Name | Rows | Bytes | Cost (% CPU) |- | 0 | SELECT STATEMENT | | 25466 | 3531K | 80186 (2) | | 1 | HASH JOIN RIGHT SEMI | | 25466 | 3531K | 80186 (2) | | 2 | TABLE ACCESS FULL | ht_DEPT | 58 | 2494 | 8 (0) | | 3 | TABLE ACCESS FULL | ht | _ tab_fy | 118K | 11m | 80176 (2) |-DBA: give me the select version from v$instance of these two query results Select name,value from v$parameter where name like'% multiblock%'; SQL > select name,value from v$parameter where name like'% multiblock%'; SQL > select version from v$instance VERSION-10.2.0.5.0 NAME VALUE- -db_file_multiblock_read_count 163, Optimization solution DBA: collect statistics from new sources: exec dbms_stats.gather_table_stats (ownname= > 'ht2') Tabname= > 'ht_DEPT',estimate_percent= > 100 methodological opt = >' for all columns size repeat',no_invalidate= > false,degree= > 8 true cascade8) Exec dbms_stats.gather_table_stats (ownname= > 'ht',tabname= >' ht_tab_fy',estimate_percent= > 100 false,degree= methodological opt = > 'for all columns size repeat',no_invalidate= > false,degree= > 8 recorder cascade= > true); then execute your statement: alter session set db_file_multiblock_read_count=128 -- add a select * FROM ht.ht_tab_fy T WHERE EXISTS (SELECT 1 FROM ht2.ht_DEPT M WHERE T.DEPT_ID = M.DEPT_ID AND M.DEPT_TREE LIKE'% 611002%') AND T.DATA_MONTH = '201709'; 4. Optimization result cousin: the result is out in seconds, thank you so much, tears burst into tears! 5. What's the problem now? Why is my cousin's sentence optimized? What is the principle?

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.