As title, today when writing SQL met a very strange problem, a total of only 4000 records of a query execution for 12 minutes, this is unbearable! So specially to explain the results below I was surprised to find the picture in the meger join outer and time-consuming at about 16 k, again carefully look inside is found that the main reason for the time-consuming buffer sort, baidu, Google the answer after my own heart, no have eldest brother had encountered similar problems, give me the answer, don't appreciate, for 11.0.4.0 oracle versions

CodePudding user response:

See a lot of operating cost is 0, the first involves statistics collection of tables and indexes of again see plan again,

CodePudding user response:

1, can't see the whole plan of the whole building Lord, therefore, also can't detailed identification and analysis, but personally think buffer sort of cost might be wrong, the node's cost should be low;
2, the entire SQL may contain multiple child union all SQL formation, the main cost should be in the first two SQL, while the second is the cost of SQL in one of the main consumption NL node, rather than the buffer sort node, concrete as shown in figure the red line,

CodePudding user response:

Sorry, I don't know how to edit the post already issued, can again hair on the first floor, simply said that the first point is the bottleneck of SQL, and the second is similar to SQL in the above reply, is also a NL node, concrete as shown in figure in red line,