-
公开(公告)号:US09189522B2
公开(公告)日:2015-11-17
申请号:US12188678
申请日:2008-08-08
申请人: Dinesh Das , Cetin Ozbutun , Hong Su , Mohamed Zait , Yali Zhu , Mohamed Ziauddin , Peter Belknap , Khaled Yagoub
发明人: Dinesh Das , Cetin Ozbutun , Hong Su , Mohamed Zait , Yali Zhu , Mohamed Ziauddin , Peter Belknap , Khaled Yagoub
CPC分类号: G06F17/30469 , G06F17/30463
摘要: Approaches, techniques, and mechanisms are disclosed for maintaining a set of baseline query plans for a database command. Except in rare circumstances, a database server may only execute a command according to a baseline plan, even if the database server predicts that a different plan has a lower cost. The set of baseline plans are plans that, for one reason or another, have been determined to provide acceptable actual performance in at least one execution context. When the database server receives a request to execute a particular command, the database server, if possible, always executes the command according to the lowest predicted cost baseline plan. The database server may evolve the plan baseline to include additional plans by generating and testing new plans in response to new requests to execute the database command, or as part of a query optimization or tuning process.
摘要翻译: 披露了用于维护数据库命令的一组基线查询计划的方法,技术和机制。 除极少数情况下,数据库服务器只能根据基准线执行命令,即使数据库服务器预测不同的计划成本较低。 一组基准计划是由于某种原因决定在至少一个执行环境中提供可接受的实际绩效的计划。 当数据库服务器接收到执行特定命令的请求时,如果可能,数据库服务器总是根据最低预测成本基线计划执行该命令。 数据库服务器可以通过生成和测试新计划以响应执行数据库命令的新请求,或作为查询优化或调整过程的一部分,来演进计划基准以包括额外的计划。
-
公开(公告)号:US07805411B2
公开(公告)日:2010-09-28
申请号:US10935908
申请日:2004-09-07
申请人: Mohamed Ziauddin , Benoit Dageville , Mohamed Zait , Dinesh Das
发明人: Mohamed Ziauddin , Benoit Dageville , Mohamed Zait , Dinesh Das
CPC分类号: G06F17/30474 , G06F17/30306 , Y10S707/99932 , Y10S707/99934 , Y10S707/99944
摘要: Auto-tuning can be performed by receiving a database query language statement and performance information related to the statement, determining whether one or more performance statistics of the statement are available or missing in the performance information, and determining an auto-tuning hint for each missing statistic.
摘要翻译: 可以通过接收与该语句相关的数据库查询语言语句和性能信息来确定语句的一个或多个性能统计信息是否在性能信息中可用或丢失来执行自动调整,以及确定每个缺失的自动调整提示 统计。
-
公开(公告)号:US07664778B2
公开(公告)日:2010-02-16
申请号:US10936449
申请日:2004-09-07
申请人: Khaled Yagoub , Benoit Dageville , Mohamed Ziauddin , Karl Dias
发明人: Khaled Yagoub , Benoit Dageville , Mohamed Ziauddin , Karl Dias
CPC分类号: G06F17/30474 , G06F17/30306 , Y10S707/99932 , Y10S707/99934 , Y10S707/99944
摘要: A method and system for storing one or more database query language statements and performance information for each query language statement as a persistent database object. In one or more embodiments, the method and system stores the resource intensive statements and the performance information for each resource intensive statement as a second persistent database object.
摘要翻译: 用于将每个查询语言语句的一个或多个数据库查询语言语句和性能信息存储为持久数据库对象的方法和系统。 在一个或多个实施例中,该方法和系统将资源密集型语句和每个资源密集型语句的性能信息存储为第二持久数据库对象。
-
公开(公告)号:US20090106219A1
公开(公告)日:2009-04-23
申请号:US12188683
申请日:2008-08-08
申请人: Peter Belknap , Benoit Dageville , Dinesh Das , Khaled Yagoub , Mohamed Zait , Mohamed Ziauddin
发明人: Peter Belknap , Benoit Dageville , Dinesh Das , Khaled Yagoub , Mohamed Zait , Mohamed Ziauddin
IPC分类号: G06F17/30
CPC分类号: G06F17/30469 , G06F17/30463
摘要: Approaches, techniques, and mechanisms are disclosed for ensuring that a database command is executed according to a query plan that has been verified to be actually optimal. Except in rare circumstances, a database server does not execute a query plan unless it is first verified by the database server. The database server receives a request to execute a database command. The database server determines an unverified plan is the best plan for satisfying the request. Rather than risk the unknown behavior of an unverified plan, the database server instead satisfies the request according to a verified plan. Subsequently—for example as part of a scheduled job—the database server executes the unverified plan to determine performance statistics. Based at least on the performance statistics, the database server determines whether or not to verify the unverified plan. Techniques for concurrent and optimistic verifications are also disclosed.
摘要翻译: 公开了方法,技术和机制,以确保根据已被验证为实际最佳的查询计划来执行数据库命令。 除非在极少数情况下,数据库服务器不执行查询计划,除非数据库服务器首先进行了验证。 数据库服务器接收执行数据库命令的请求。 数据库服务器确定未验证的计划是满足请求的最佳方案。 而不是冒险未验证的计划的未知行为,数据库服务器而是根据验证的计划满足请求。 随后 - 例如作为计划作业的一部分 - 数据库服务器执行未验证的计划以确定性能统计信息。 至少基于性能统计,数据库服务器确定是否验证未验证的计划。 还公开了并发和乐观验证的技术。
-
公开(公告)号:US20050125427A1
公开(公告)日:2005-06-09
申请号:US10936778
申请日:2004-09-07
申请人: Benoit Dageville , Mohamed Ziauddin , Khaled Yagoub , Mohamed Zait , Dinesh Das , Karl Dias , Mark Ramacher , Leng Tan
发明人: Benoit Dageville , Mohamed Ziauddin , Khaled Yagoub , Mohamed Zait , Dinesh Das , Karl Dias , Mark Ramacher , Leng Tan
CPC分类号: G06F17/30474 , G06F17/30306 , Y10S707/99932 , Y10S707/99934 , Y10S707/99944
摘要: A method for receiving a database query language statement and performance information about the statement at an optimizer and generating one or more tuning actions for the statement with the performance information is disclosed.
摘要翻译: 公开了一种用于在优化器处接收数据库查询语言语句和关于语句的性能信息的方法,并且针对具有性能信息的语句生成一个或多个调优操作。
-
公开(公告)号:US5899986A
公开(公告)日:1999-05-04
申请号:US796779
申请日:1997-02-10
申请人: Mohamed Ziauddin
发明人: Mohamed Ziauddin
IPC分类号: G06F17/30
CPC分类号: G06F17/30595 , G06F17/30463 , G06F17/30536 , Y10S707/99932 , Y10S707/99933
摘要: Methods for collecting query workload based statistics within a relational database management system (RDBMS) and for identifying columns for which statistics collection is to be performed. The novel system collects workload statistics that are dependent on multiple columns, rather than merely single columns. Multi-column statistic generation provides more accurate results for columns having correlated data, and therefore leads to better estimated cost analysis by an RDBMS optimizer. In one embodiment, a column duplicity factor is based on an analysis of distinct data rows, e.g., combinations of values within multiple columns, rather than rows of single columns. The novel system also collects separate statistics regarding the presence of null data within the rows of a column group. Separate null data statistics improve the determined result carnality used by the RDBMS optimizer because the cardinality of a relational operation's result is generally determined by the number of input rows with non-null data. The novel system includes an RDBMS optimizer that automatically identifies column groups and column groups on which workload statistics are to be generated. The parameters within a query (e.g., equi-joins, equi-selections, and projections) are analyzed by the optimizer to automatically identify the column groups. The identified columns are then registered within in a system catalog. The registered column groups are read by statistics generation procedures to identify those column groups for which workload statistics are to be collected.
-
公开(公告)号:US09058356B2
公开(公告)日:2015-06-16
申请号:US12188678
申请日:2008-08-08
申请人: Dinesh Das , Cetin Ozbutun , Hong Su , Mohamed Zait , Yali Zhu , Mohamed Ziauddin , Peter Belknap , Khaled Yagoub
发明人: Dinesh Das , Cetin Ozbutun , Hong Su , Mohamed Zait , Yali Zhu , Mohamed Ziauddin , Peter Belknap , Khaled Yagoub
摘要: Approaches, techniques, and mechanisms are disclosed for maintaining a set of baseline query plans for a database command. Except in rare circumstances, a database server may only execute a command according to a baseline plan, even if the database server predicts that a different plan has a lower cost. The set of baseline plans are plans that, for one reason or another, have been determined to provide acceptable actual performance in at least one execution context. When the database server receives a request to execute a particular command, the database server, if possible, always executes the command according to the lowest predicted cost baseline plan. The database server may evolve the plan baseline to include additional plans by generating and testing new plans in response to new requests to execute the database command, or as part of a query optimization or tuning process.
-
公开(公告)号:US07664730B2
公开(公告)日:2010-02-16
申请号:US10936205
申请日:2004-09-07
CPC分类号: G06F17/30474 , G06F17/30306 , Y10S707/99932 , Y10S707/99934 , Y10S707/99944
摘要: A method, apparatus, and computer program product with a persistent data structure for storing a profile of tuning information for a query statement separately from the query statement. The profile in the persistent data structure includes a statement signature to identify the query statement, and an action to tune the statement.
摘要翻译: 一种具有持久数据结构的方法,装置和计算机程序产品,用于与查询语句分开地存储用于查询语句的调整信息的简档。 持久性数据结构中的配置文件包括用于标识查询语句的语句签名,以及调整语句的操作。
-
公开(公告)号:US20050120001A1
公开(公告)日:2005-06-02
申请号:US10936426
申请日:2004-09-07
申请人: Khaled Yagoub , Benoit Dageville , Mohamed Ziauddin , Mohamed Zait , Karl Dias
发明人: Khaled Yagoub , Benoit Dageville , Mohamed Ziauddin , Mohamed Zait , Karl Dias
CPC分类号: G06F17/30474 , G06F17/30306 , Y10S707/99932 , Y10S707/99934 , Y10S707/99944
摘要: A method to addresses structural performance problems of a database query language statement is provided. The method includes receiving a database query language statement at an optimizer, evaluating choices in a search space to generate an execution plan for the statement, and producing annotations to record one or more reasons for selecting each choice while generating the execution plan.
摘要翻译: 提供了解决数据库查询语言语句结构性能问题的方法。 该方法包括在优化器处接收数据库查询语言语句,评估搜索空间中的选择以生成语句的执行计划,以及生成注释以记录在生成执行计划时选择每个选择的一个或多个原因。
-
公开(公告)号:US06581055B1
公开(公告)日:2003-06-17
申请号:US09658349
申请日:2000-09-11
申请人: Mohamed Ziauddin , Ahmed Alomari
发明人: Mohamed Ziauddin , Ahmed Alomari
IPC分类号: G06F1730
CPC分类号: G06F17/30463 , G06F17/30471 , G06F17/30474 , Y10S707/99933 , Y10S707/99934
摘要: A method is provided for optimizing data queries using switch predicates to determine which query execution plan or sub-plan (generated by a query optimizer) is executed. The method may be implemented for a query that has multiple possible forms of execution or outcomes depending on a run-time condition or element (e.g., the value associated with a variable). The query may be expanded, and the different possibilities may be addressed in different sub-queries of the expanded query (e.g., with the sub-queries being joined through a UNION operation). Switch predicates are added to one or more sub-queries to determine which one(s) will execute, based on the run-time condition. A separate query execution sub-plan may be generated for each sub-query and the sub-plans may be combined to form a master or overall plan that is executed at run-time. For example, a NULLIF predicate returns one set of data (e.g., a single row of a database table) if an associated variable has a non-null value and another set of data (e.g., all rows having a value within a particular column) if the variable has a null value. Switch predicates comprising Boolean operations to test whether the variable has a null or non-null value may be applied to sub-queries corresponding to the different run-time possibilities. Optimization sub-plans may be generated and only a sub-plan associated with a switch predicate that evaluates to true will execute. Because the NULLIF predicate has been reduced to its different forms of operation, each possibility can be optimized to form a very efficient execution sub-plan.
摘要翻译: 提供了一种使用交换机谓词来优化数据查询以确定执行哪个查询执行计划或子计划(由查询优化器生成)的方法。 可以根据运行时间条件或元素(例如,与变量相关联的值)针对具有多种可能形式的执行或结果的查询实现该方法。 可以扩展查询,并且可以在扩展查询的不同子查询中(例如,通过UNION操作连接子查询)来解决不同的可能性。 交换机谓词被添加到一个或多个子查询中,以根据运行时间条件确定哪个子查询执行。 可以为每个子查询生成单独的查询执行子计划,并且子计划可以被组合以形成在运行时执行的主计划或整体计划。 例如,如果相关联的变量具有非空值和另一组数据(例如,所有行具有特定列中的值),则NULLIF谓词将返回一组数据(例如,数据库表的单行) 如果变量具有空值。 包含布尔运算以测试变量是空值还是非空值的切换谓词可以应用于与不同运行时可能性相对应的子查询。 可以生成优化子计划,并且仅执行与计算为真的交换机谓词相关联的子计划。 因为NULLIF谓词已经减少到其不同的操作形式,所以可以优化每种可能性以形成非常有效的执行子计划。
-
-
-
-
-
-
-
-
-