Reduce the Number of SQL Statements

简介:

Shareable SQL uses bind variables rather than literal values. If an application makes use of literal (unshared) SQL then this can severely limit scalability and throughput. The cost of parsing a new SQL statement is expensive both in terms of CPU and the number of times the library cache and shared pool latches may need to be acquired and released. Even parsing a simple SQL statement may need to acquire a library cache latch twenty or thirty times.   By looking at the V$SQLAREA view it is possible to see which literal statements are good candidates for converting to use bind variables. The following query shows SQL in the SGA where there are a large number of similar statements:   SELECT substr(sql_text,1,50) "SQL", count(*), sum(executions) "TotExecs" FROM v$sqlarea WHERE executions < 5 GROUP BY substr(sql_text,1,50) HAVING count(*) > 30 ORDER BY 2   This query finds statements whose first 50 characters are the same and which have only been executed a few times each and have at least 30 different copies of this SQL in the shared pool. The query may need to be modified if the literals are in the first 50 characters.   There are numerous parameters in the INIT.ORA that can directly impact the efficiency of shared pool usage. For a full accounting of these, refer to MetaLink Note: 62143.1.



本文转自maclean_007 51CTO博客,原文链接:http://blog.51cto.com/maclean/1277878

相关文章
|
11天前
|
SQL 存储 数据采集
sql中varchar转number时报错怎么解决
通过上述策略的综合运用,面对VARCHAR到NUMBER的转换挑战,不仅能够游刃有余地解决现有的问题,更能前瞻性地预防未来的隐患。在数字化转型的浪潮中,凭借其高性能、安全稳定的云产品与服务,为各类企业级应用保驾护航,助您轻松驾驭数据的海洋,实现业务的无缝扩展与全球布局。
23 0
|
6月前
|
SQL 关系型数据库 MySQL
实时计算 Flink版产品使用合集之flink sql ROW_NUMBER()回退更新的机制,有相关文档介绍吗
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
79 1
|
6月前
|
SQL 关系型数据库 MySQL
实时计算 Flink版操作报错合集之报错显示“Unsupported SQL query! sqlUpdate() only accepts SQL statements of type INSERT and DELETE"是什么意思
在使用实时计算Flink版过程中,可能会遇到各种错误,了解这些错误的原因及解决方法对于高效排错至关重要。针对具体问题,查看Flink的日志是关键,它们通常会提供更详细的错误信息和堆栈跟踪,有助于定位问题。此外,Flink社区文档和官方论坛也是寻求帮助的好去处。以下是一些常见的操作报错及其可能的原因与解决策略。
229 0
|
6月前
|
SQL
Auditing SQL Statements, Privileges, and Other General Activities
Auditing SQL Statements, Privileges, and Other General Activities
34 0
|
6月前
【已解决】nested exception is java.sql.SQLSyntaxErrorException: ORA-01722: invalid number
【已解决】nested exception is java.sql.SQLSyntaxErrorException: ORA-01722: invalid number
71 0
|
6月前
|
SQL 关系型数据库 MySQL
Greenplum【SQL 02】ROW_NUMBER编号函数使用方法举例
Greenplum【SQL 02】ROW_NUMBER编号函数使用方法举例
138 0
|
SQL
SQL中rank(),dense_rank(),row_number()的异同
rank函数用于返回结果集的分区内每行的排名,行的排名是相关行之前的排名数加一。
184 0
SQL中rank(),dense_rank(),row_number()的异同
|
SQL Go
【SQL】ROW_NUMBER() OVER(partition by 分组列 order by 排序列)用法详解+经典实例
【SQL】ROW_NUMBER() OVER(partition by 分组列 order by 排序列)用法详解+经典实例目录 0、填充数据1、使用row_number()函数对订单进行编号,按照订单时间倒序。
13065 0
|
SQL
SQL 序号列ROW_NUMBER,RANK,DENSE_RANK、NTILE
原文:SQL 序号列ROW_NUMBER,RANK,DENSE_RANK、NTILE SQL 2005新增加相关函数 : ROW_NUMBER,RANK,DENSE_RANK、NTILE 窗口函数 ...
1283 0
|
SQL Go 数据库
MSSQL sql server 2005/2008 row_number()函数应用之–删除表中重复记录,只保留一条不重复数据
原文:MSSQL sql server 2005/2008 row_number()函数应用之–删除表中重复记录,只保留一条不重复数据 转自:http://www.maomao365.com/?p=4942 下文主要讲述:重复数据只获取一条的方法 row_number函数在数据库中的功能是为每一...
1155 0