I was recently in an interview and i have been asked a question that was : After one year of publishing your application the data in the database became massive , so what is the best way to optimize the DB performance in the DB side not in the coding side whether database is Oracle or SQL server ... i just want to know what is the best answer for this question ?
我最近在接受采访时被问到一个问题：在发布应用程序一年后，数据库中的数据变得非常庞大，那么在数据库方面优化数据库性能的最佳方法是什么？无论数据库是Oracle还是SQL服务器......我只是想知道这个问题的最佳答案是什么？
2 个解决方案
#1
4
I can give you an answer, but can't guarantee that an interviewer would like it.
我可以给你一个答案，但不能保证面试官会喜欢它。
The best way to optimise the performance is to understand what your application does, and the data structures that the system provides. You must understand the business so that you can understand the data, and when you do that you'll know whether the SQL submitted to the system is "asking the correct question", and doing so in a way that makes sense for the data and it's distribution.
优化性能的最佳方法是了解应用程序的功能以及系统提供的数据结构。您必须了解业务，以便您可以理解数据，并且当您这样做时，您将知道提交给系统的SQL是否“询问正确的问题”，并且以对数据有意义的方式这样做它的分布。
Furthermore, you should measure and document what the normal behaviour of the system is, and the cycles it might go through on a daily, weekly, monthly, quarterly and annual basis. You should be prepared to be able to quantify any deviation from normal performance.
此外，您应该测量并记录系统的正常行为，以及每天，每周，每月，每季度和每年可能经历的周期。您应该准备好能够量化与正常表现的任何偏差。
You must understand the database technology itself. The concepts, the memory structures and processing, REDO, UNDO, index and table types, and maybe partitioning, parallelism, and RAC. The upsides and the downsides.
您必须了解数据库技术本身。概念，内存结构和处理，REDO，UNDO，索引和表类型，以及分区，并行和RAC。好处和缺点。
You must know SQL extremely well, and be completely up to date on its capabilities in your DB version, and any new ones now available. You must be able to read a raw execution plan straight from DBMS_XPlan(). Tracing query execution must be within your skill set.
您必须非常了解SQL，并且在数据库版本中完全了解其功能，并且现在可以使用任何新功能。您必须能够直接从DBMS_XPlan（）读取原始执行计划。跟踪查询执行必须在您的技能集中。
You must understand query transformation and optimisation, the use of bind variables, and statistics.
您必须了解查询转换和优化，绑定变量的使用以及统计信息。
If I had to choose only one of the above, it would be that you must have measured and documented historical performance, and be able to quantify deviations from it, because without that you will never know where to start.
如果我只选择上述其中一项，那就是你必须测量并记录历史表现，并能够量化与它的偏差，因为没有它你就永远不知道从哪里开始。
#2
0
I'm pretty sure the point of the question was to see how you deal with vague, overly broad questions. One thing you did that was pretty positive, was to seek out authoritative answers on SO. Don't know if that's going to help you now that the interview is done.
我很确定问题的关键在于如何处理模糊，过于宽泛的问题。你做的一件非常积极的事情就是在SO上找到权威的答案。现在不知道面试是否会对你有所帮助。
So - how do you respond to such a question? An "I have no way of knowing" is probably not the approach to take - even if it the correct answer.
那么 - 你如何回答这样的问题？ “我无法知道”可能不是采取的方法 - 即使它是正确的答案。
Maybe something like, "I'm not entirely sure what you're asking - so let me try to understand with a couple of questions. Are we talking about query performance or update performance? Are there indexes to support the workload? What makes you feel optimization is necessary?"
也许类似的事情，“我不完全确定你在问什么 - 所以让我试着理解几个问题。我们是在谈论查询性能还是更新性能？是否有支持工作量的索引？是什么让你感觉优化是必要的吗？“
I think it is as much about your approach to problem solving as any particular tech.
我认为解决问题的方法与任何特定技术一样重要。
But, then on the other hand, maybe I'm wrong. Maybe the first answer is always "Index the hell out of it!" :-D
但是，另一方面，也许我错了。也许第一个答案总是“将它归咎于它！” :-D
Interviewing is a nightmare, isn't it?
面试是一场噩梦，不是吗？
#1
4
I can give you an answer, but can't guarantee that an interviewer would like it.
我可以给你一个答案，但不能保证面试官会喜欢它。
The best way to optimise the performance is to understand what your application does, and the data structures that the system provides. You must understand the business so that you can understand the data, and when you do that you'll know whether the SQL submitted to the system is "asking the correct question", and doing so in a way that makes sense for the data and it's distribution.
优化性能的最佳方法是了解应用程序的功能以及系统提供的数据结构。您必须了解业务，以便您可以理解数据，并且当您这样做时，您将知道提交给系统的SQL是否“询问正确的问题”，并且以对数据有意义的方式这样做它的分布。
Furthermore, you should measure and document what the normal behaviour of the system is, and the cycles it might go through on a daily, weekly, monthly, quarterly and annual basis. You should be prepared to be able to quantify any deviation from normal performance.
此外，您应该测量并记录系统的正常行为，以及每天，每周，每月，每季度和每年可能经历的周期。您应该准备好能够量化与正常表现的任何偏差。
You must understand the database technology itself. The concepts, the memory structures and processing, REDO, UNDO, index and table types, and maybe partitioning, parallelism, and RAC. The upsides and the downsides.
您必须了解数据库技术本身。概念，内存结构和处理，REDO，UNDO，索引和表类型，以及分区，并行和RAC。好处和缺点。
You must know SQL extremely well, and be completely up to date on its capabilities in your DB version, and any new ones now available. You must be able to read a raw execution plan straight from DBMS_XPlan(). Tracing query execution must be within your skill set.
您必须非常了解SQL，并且在数据库版本中完全了解其功能，并且现在可以使用任何新功能。您必须能够直接从DBMS_XPlan（）读取原始执行计划。跟踪查询执行必须在您的技能集中。
You must understand query transformation and optimisation, the use of bind variables, and statistics.
您必须了解查询转换和优化，绑定变量的使用以及统计信息。
If I had to choose only one of the above, it would be that you must have measured and documented historical performance, and be able to quantify deviations from it, because without that you will never know where to start.
如果我只选择上述其中一项，那就是你必须测量并记录历史表现，并能够量化与它的偏差，因为没有它你就永远不知道从哪里开始。
#2
0
I'm pretty sure the point of the question was to see how you deal with vague, overly broad questions. One thing you did that was pretty positive, was to seek out authoritative answers on SO. Don't know if that's going to help you now that the interview is done.
我很确定问题的关键在于如何处理模糊，过于宽泛的问题。你做的一件非常积极的事情就是在SO上找到权威的答案。现在不知道面试是否会对你有所帮助。
So - how do you respond to such a question? An "I have no way of knowing" is probably not the approach to take - even if it the correct answer.
那么 - 你如何回答这样的问题？ “我无法知道”可能不是采取的方法 - 即使它是正确的答案。
Maybe something like, "I'm not entirely sure what you're asking - so let me try to understand with a couple of questions. Are we talking about query performance or update performance? Are there indexes to support the workload? What makes you feel optimization is necessary?"
也许类似的事情，“我不完全确定你在问什么 - 所以让我试着理解几个问题。我们是在谈论查询性能还是更新性能？是否有支持工作量的索引？是什么让你感觉优化是必要的吗？“
I think it is as much about your approach to problem solving as any particular tech.
我认为解决问题的方法与任何特定技术一样重要。
But, then on the other hand, maybe I'm wrong. Maybe the first answer is always "Index the hell out of it!" :-D
但是，另一方面，也许我错了。也许第一个答案总是“将它归咎于它！” :-D
Interviewing is a nightmare, isn't it?
面试是一场噩梦，不是吗？