Oracle
快来分享你的内容吧～
2024-07-26·后端- oracle分页+排序查询的结果不符合预期问题描述在oracle数据库中，使用分页查询并对某个字段进行了排序，查询出来的结果中stock_quantity字段的值跟预期的不一样具体疑问这是什么原因造成的，要怎么解决sqlSELECT ROWNUM ROW_ID, TMP_PAGE.*FROM ( select g.id as goods_id, g.name, g.category, g.details, g....查看全文程序员鱼皮：首先问题不够明确，既没有说这个 SQL 要解决的问题和介绍，又没有说自己预期的结果，再加上我们也没有测试数据，其实别人帮你看这个其实会挺累的哈，只能用肉眼裸看。。因为我本身对 Oracle 接触的也并不多，你试着先在子查询中排序，再用 Rownum 分页试试呢？SELECT ROW_ID, TMP_PAGE.*FROM ( SELECT ROWNUM ROW_ID,
2022-12-26
Oracle ASM存储优化与性能提升机制分析
## Oracle ASM存储优化与性能提升机制分析 Oracle自动存储管理(ASM)是Oracle数据库10g版本引入的一项革命性存储管理技术，它以平台无关的方式提供文件系统、逻辑卷管理器和软件RAID服务，简化了存储配置并显著提升了性能。ASM通过创新的数据分布和负载均衡机制，结合冗余管理与故障恢复功能，构建了一个高效、可靠且易于维护的存储解决方案，特别适合高可用性、高并发和大规模数据处理的场景。**ASM的核心优势在于自动化管理存储资源，使数据库管理员能够专注于业务逻辑而非底层存储细节，同时通过智能化的存储布局和I/O调度大幅优化存储性能。** ### 一、ASM架构与数据分布机制 ASM实例是Oracle数据库的一个特殊实例，专门负责管理存储资源，它由系统全局区(SGA)和后台进程组成。ASM实例的SGA包含共享池，用于存储磁盘组的元数据和文件布局信息(Extent Map)。后台进程包括LGWR(日志写入进程)、SMON(系统监控进程)、PMON(进程监控进程)、DBWn(数据库写入进程)和CKPT(检查点进程)等，这些进程与普通数据库实例类似，但ASM特有的RBAL(Rebalance协调)和ARBn(Rebalance执行)进程负责规划和执行数据再平衡操作，确保磁盘组内数据分布均匀。**数据库实例通过ASMB进程与ASM实例通信，交换磁盘组元数据信息，这使得数据库实例能够直接访问ASM磁盘组而无需经过传统文件系统，显著提高了I/O效率。** 磁盘组是ASM管理的基本单位，由多个物理磁盘组成逻辑存储池。每个磁盘被划分为固定大小的分配单元(AU)，默认大小为1MB，但11g及以后版本允许自定义AU大小。**ASM通过两种条带化方式优化数据分布：粗粒度条带化将整个AU作为一个条带单元，适用于数据文件、归档日志等大文件；细粒度条带化将AU进一步拆分为128KB的Stripe Chunk，适用于控制文件、联机重做日志等小文件，实现更细粒度的负载均衡**。**这种条带化技术使ASM能够将数据均匀分布在磁盘组的所有磁盘上，最大化利用I/O带宽并避免热点**。 在Oracle 11g版本中，ASM引入了动态调整AU和Extent大小的机制。随着文件大小的增加，ASM自动扩展Extent大小，从初始的1MB逐步增加到8MB、64MB甚至更大。这种动态调整策略使ASM能够适应不同规模的数据需求，优化存储效率。例如，在AU=1MB的情况下，文件大小在20GB以内时Extent为1MB，20GB到160GB时扩展到8MB，超过160GB则扩展到64MB，这样既保持了数据分布的均匀性，又减少了元数据管理的开销。 ### 二、负载均衡与再平衡机制 ASM的负载均衡机制是其性能优化的核心。**通过条带化技术，ASM将数据分散到多个磁盘上，确保每个磁盘的读写负载相对均衡，避免单一磁盘成为性能瓶颈**。**当磁盘组结构发生变化（如添加或移除磁盘）时，ASM自动执行再平衡操作，重新分布数据以保持负载均衡。这种再平衡过程由RBAL进程规划，ARBn进程并行执行，管理员可以通过`asm_power_limit`参数控制再平衡的并行度，从而平衡再平衡速度与系统资源消耗**。例如，设置`asm_power_limit=10`会启动10个ARBn进程同时工作，加速再平衡过程但可能增加系统负载。 ASM的再平衡算法基于哈希计算，能够智能地确定数据迁移的路径和优先级。在Oracle 11.2.0.2及以后版本中，`asm_power_limit`参数的取值范围扩展为0~1024，使管理员能够更精细地控制再平衡过程。**再平衡操作通常分为三个阶段：规划(Planning)、数据迁移(Extent Relocation)和数据重组(Compacting)。规划阶段计算需要移动的数据块及其目标位置，数据迁移阶段执行实际的数据移动，而数据重组阶段则优化磁盘空间使用，减少碎片**。**这种机制确保了在磁盘组结构变化后，数据能够迅速重新分布到所有可用磁盘，维持最佳性能**。 值得注意的是，ASM的再平衡并非实时响应I/O负载波动，而是主要在磁盘组结构变化时触发。**动态负载均衡更多依赖于条带化本身的设计，而非实时调整数据分布**。**然而，通过合理设计磁盘组结构（如AU大小、条带宽度）和选择合适的故障组划分策略，ASM能够有效避免I/O热点并维持高性能**。管理员还可以通过`V$ASM_FILESTAT`等动态性能视图监控ASM的I/O活动，识别潜在的性能瓶颈并进行针对性优化。 ### 三、冗余管理与故障恢复功能 ASM提供了三种冗余管理策略，分别为外部冗余(External)、正常冗余(Normal)和高冗余(High)。外部冗余不使用软件层面的镜像，而是依赖硬件RAID技术实现数据保护，适合已经具备硬件冗余的环境；正常冗余为每个数据块创建两个副本，分布在不同的故障组中，可容忍单个磁盘或整个故障组的失效，但有效存储容量仅为总磁盘空间的一半；高冗余则创建三个副本，可容忍两个磁盘或故障组同时失效，有效存储容量约为总磁盘空间的三分之一。 **故障组(Failure Group)是ASM冗余管理的基础逻辑单元，用于将共享相同物理风险的磁盘（如同一机架、同一存储控制器）分组管理**。**合理的故障组划分确保冗余副本分布在独立的物理位置，避免单点故障导致数据丢失**。例如，在Normal冗余模式下，至少需要两个故障组；在High冗余模式下，至少需要三个故障组。如果未显式定义故障组，ASM会为每个磁盘自动创建一个独立的故障组，这在单节点环境中是可行的，但在多节点RAC环境中可能无法充分利用集群的高可用性优势。 当磁盘发生故障时，ASM自动将其标记为OFFLINE状态，并等待`DISK_REPAIR_TIME`参数指定的时间（默认3.6小时）进行修复。如果磁盘在等待期内恢复，ASM会自动重新同步磁盘上的数据；如果未能恢复，ASM会将其从磁盘组中移除并触发再平衡操作，重新分布数据以恢复冗余保护。**Oracle 11g引入的快速镜像同步(Fast Mirror Resync)技术是故障恢复的重要增强，它记录磁盘离线期间的所有数据变更，重新联机时仅同步变更部分而非整个磁盘，大幅缩短了恢复时间。** 在集群环境中，ASM的高可用性与Oracle RAC紧密集成。**ASM实例本身可以集群化部署（Flex ASM），当某个ASM实例失效时，集群软件会自动在其他节点上启动新的ASM实例，确保存储管理服务的连续性**。**这种设计使ASM能够与RAC协同工作，实现数据库存储的自动故障转移。例如，当RAC集群中的一个节点失效时，ASM确保其他节点能够无缝访问存储在磁盘组中的数据库文件，维持服务的可用性**。 ### 四、ASM性能优化的具体措施 ASM提供了一系列优化措施，使数据库存储能够适应不同工作负载和硬件环境。**磁盘组模板(Disk Group Template)是ASM性能优化的关键工具，它定义了文件的条带宽度、冗余级别等属性。**通过创建自定义模板，管理员可以针对不同类型文件(如数据文件、临时文件、控制文件)应用不同的条带化策略。例如，可以为控制文件创建细粒度条带化模板，为数据文件创建粗粒度条带化模板，以满足不同文件类型的I/O需求。 ASM的`asm_power_limit`参数是控制再平衡操作速度的重要工具。该参数决定了同时执行再平衡操作的ARBn进程数量，值越高，再平衡速度越快但资源消耗也越大。**合理设置`asm_power_limit`值可以在保持数据库性能的同时快速完成存储结构调整。**例如，在业务低峰期可以设置较高的`asm_power_limit`值加速再平衡，而在业务高峰期则设置较低的值以减少对数据库操作的影响。此外，ASM 11g引入的`EXPLAIN WORK FOR`语句能够帮助管理员评估再平衡操作的工作量，为参数设置提供数据支持。 **ASM的I/O优先级控制机制也是性能优化的重要手段**。**通过`ASM_POWERS`参数，管理员可以为不同类型的存储操作分配不同的优先级，确保关键操作（如数据库写入）获得足够的I/O带宽**。这种细粒度的控制使ASM能够适应不同的工作负载模式，优化整体存储性能。在Oracle 19c版本中，ASM引入了智能再平衡算法，能够更精确地识别需要迁移的数据块并优化迁移路径，进一步提高了再平衡效率。 在磁盘组设计方面，**合理的故障组划分和磁盘容量平衡对于ASM性能至关重要**。**每个故障组内的磁盘数量和容量应尽量均衡，避免数据分布倾斜。例如，在一个由多个存储阵列组成的环境，可以为每个存储阵列创建一个独立的故障组，确保冗余副本分布在不同的物理设备上，提高数据可用性的同时优化I/O性能。此外，11g版本之后，ASM支持动态调整AU大小，允许管理员根据实际数据访问模式优化存储布局**。 ### 五、ASM在实际应用场景中的表现 ASM在多种实际应用场景中表现出色，特别是在需要高可用性、可扩展性和性能优化的环境中。**在Oracle Real Application Clusters(RAC)环境中，ASM是实现共享存储的关键组件。**它使多个数据库实例能够安全地共享同一组磁盘资源，同时通过冗余副本和故障组隔离提供高可用性。例如，在用友NC财务系统上云案例中，通过将原有Oracle数据库迁移到阿里云ESC RAC环境并使用ASM管理存储，实现了几乎零停机时间的迁移，且存储性能得到显著提升，特别是在使用阿里云ESSD共享存储时。 在高并发数据库场景中，**ASM的条带化和负载均衡机制能够有效分散I/O请求，提高吞吐量。**电商订单处理系统通常面临大量并发的读写操作，ASM通过将数据文件条带化到多个磁盘，使得数据库能够同时从多个磁盘读取或写入数据，大幅提高I/O性能。例如，在某个电商系统的迁移过程中，使用ASM管理的磁盘组将订单数据分散存储在多个物理磁盘上，使得系统的订单处理能力提升了30%以上。 **存储扩展需求是ASM的另一重要应用场景**。**传统存储管理方式在需要扩展存储容量时通常需要停机或复杂的操作，而ASM允许管理员在数据库运行的情况下动态添加或移除磁盘，自动执行再平衡操作以重新分布数据。例如，在一个银行系统的归档日志存储中，随着业务增长，归档日志数量不断增加，管理员可以通过简单的`ALTER DISKGROUP add disk`命令在线添加新磁盘，ASM自动将数据重新分布到所有磁盘，避免了I/O热点并维持了系统性能**。 在混合云和容器化部署场景中，ASM也展现出强大的适应性。**通过将ASM与云存储服务（如阿里云ESSD）结合使用，企业可以构建灵活的存储架构，既保留本地存储的高性能，又利用云存储的弹性扩展能力。**例如，在一个混合云部署中，核心业务数据存储在本地ASM磁盘组，而备份和归档数据存储在云存储上的ASM磁盘组，实现了高性能与成本效益的平衡。同时，ASM的元数据保护机制（通过多副本存储）确保了磁盘组信息的可靠性，即使在硬件故障情况下也能快速恢复。 ### 六、ASM版本演进与性能增强 ASM随着Oracle数据库版本的演进不断优化，引入了多项性能增强特性。**Oracle 11g版本是ASM发展的重要里程碑，它引入了快速镜像同步(Fast Mirror Resync)和动态再平衡优化等关键功能。**快速镜像同步技术使ASM能够在磁盘恢复后仅同步离线期间的变更数据，而非整个磁盘，大幅缩短了故障恢复时间。这一功能需要`compatible.asm`参数设置为11.1或更高版本，是ASM高可用性的重要组成部分。 **Oracle 12c版本进一步优化了ASM的集群支持，引入了Flex ASM特性**。**Flex ASM改变了传统集群中每个节点都有独立ASM实例的架构，允许在集群中配置少数ASM实例，当某个ASM实例失效时，集群软件会自动在其他节点上启动替代实例，提高了ASM实例本身的可用性。这种设计减少了集群的复杂度和管理开销，使ASM能够更灵活地适应不同规模的集群环境**。 **Oracle 19c版本在ASM性能优化方面取得了显著进展，引入了智能再平衡算法和动态I/O优先级调整功能**。**智能再平衡算法能够更精确地识别需要迁移的数据块，优化迁移路径，减少不必要的数据移动。动态I/O优先级调整则允许ASM根据实时负载情况自动调整不同存储操作的优先级，确保关键业务操作获得足够的I/O资源**。 在Oracle 20c版本中，**ASM进一步整合了人工智能技术，实现了更高级的自动化存储优化**。**例如，ASM能够根据历史I/O模式预测未来负载，提前进行数据分布调整；同时，通过分析存储设备性能数据，ASM可以自动选择最优的存储路径进行数据访问，提高整体存储效率。这些AI驱动的优化使ASM能够更智能地适应复杂的存储环境，为数据库提供持续的高性能存储支持**。 ### 七、ASM最佳实践与配置建议 在实际应用中，**合理的ASM配置是发挥其性能优势的关键**。**首先，磁盘组的设计应考虑业务需求和硬件环境。对于需要高可用性的关键业务系统，应选择Normal或High冗余策略，并确保故障组划分符合物理隔离原则（如同一机架的磁盘应属于同一故障组）。对于存储空间敏感的环境，可以选择External冗余策略，充分利用硬件RAID提供的数据保护能力**。 其次，**AU大小和条带宽度的设置应基于数据库块大小和工作负载特点**。**AU大小通常是数据库块大小的整数倍，例如当数据库块大小为8KB时，AU可以设置为1MB或64MB。条带宽度则决定了数据分布在多少个磁盘上，对于大型数据文件，较宽的条带能够充分利用多个磁盘的I/O带宽；而对于小型文件，较窄的条带能够避免数据分布过于分散导致的元数据管理开销。在Oracle 11g之后的版本中，AU大小可以自定义，使ASM能够更好地适应不同的工作负载模式**。 再平衡操作的参数设置也需要谨慎考虑。**`asm_power_limit`参数决定了再平衡操作的并行度，应根据系统资源情况和业务需求进行调整**。**在业务高峰期，建议设置较低的`asm_power_limit`值（如1-5），减少再平衡操作对数据库性能的影响；而在业务低峰期或计划维护期间，可以设置较高的值（如10-20）以加速再平衡过程**。**此外，再平衡操作的优先级（`power`参数）也应根据操作的紧急程度进行调整，例如，添加磁盘时可以设置较低的优先级，而修复磁盘组冗余时则可以设置较高的优先级**。 **故障组的合理划分对于ASM的高可用性至关重要**。**在RAC环境中，应确保不同节点的磁盘分布在不同的故障组中，这样即使某个节点完全失效，数据仍然可以通过其他节点访问。同时，故障组的数量和大小应与冗余策略匹配，例如，在Normal冗余模式下，至少需要两个故障组，且每个故障组的容量应尽量均衡，避免冗余副本分布不均导致的数据保护能力下降。在存储扩展时，应考虑将新磁盘添加到现有的故障组或创建新的故障组，以维持最佳的数据分布和冗余保护**。 ### 八、ASM与传统存储管理的对比优势 与传统存储管理方式相比，**ASM在多个方面展现出显著优势**。**首先，在存储配置和管理复杂度方面，ASM提供了统一的管理接口，使数据库管理员能够通过简单的SQL命令或图形界面工具（如ASMCA）创建和管理磁盘组，无需深入理解操作系统级别的存储配置**。例如，创建一个Normal冗余的磁盘组只需执行： ```sql CREATE DISKGROUP data NORMAL REDUNDANCY
亲测！解决Error: ORA-12170: TNS:Connect timeout occurred连接超时
### 案例 > [Nest] 7696 - 2024/07/26 11:30:31 [TypeOrmModule] Unable to connect to the database. Retrying (3)... +8ms Error: ORA-12170: TNS:Connect timeout occurred 今天遇到一个Oracle的报错`Error: ORA-12170: TNS:Connect timeout occurred`，连接超时，项目是TS作为后端开发语言，使用的持久层框架是[TypeORM](https://typeorm.bootcss.com/)。 分析原因： - 网络问题 - 防火墙（端口） 我出现这个问题是因为开了vpn，关掉后再次运行项目就好了 ### 小结 针对以上可能查阅资料，简单总结一下解决方式： **1.检查网络** a. ping ip地址，如果ping不通的话，就是网络问题 b. tnsping ip地址（或者服务器的实例名SID），如果报TNS-12535:操作超时，可能是服务器端防火墙原因 c. netstat -na，查看对应端口是否关闭 **2.防火墙问题** a. 关闭防火墙：`chkconfig iptables off;`（重启失效），`/etc/init.d/iptables stop;`（立即失效） b. 执行`vim /etc/sysconfig/iptables`命令编辑防火墙配置，在默认的22端口下一行添加`-A INPUT -m state --state NEW -m tcp -p tcp --dport 1521 -j ACCEPT`，保存配置文件，执行`/etc/init.d/iptables restart`重启防火墙 不过现在有很多可视化界面工具，操作起来也很方便，也不用一条条命令的排错。
如何查询Oracle数据库一周内每天的SQL执行次数
#打卡 #写文章 一、问题： ----- 今天引入的问题是：oracle数据库怎么查询一周内，每天的查询次数？ 正好这周在学数据库的调优工作，我记得数据库的AWR报告会记录SQL的执行情况，可以从DBA\_HIST\_SNAPSHOT里面找出7天的快照ID，在DBA\_HIST\_SQLSTAT收集一下。 先来了解一下这两个相关视图 二、相关视图： ------- dba\_hist\_sqlstat 和dba\_hist\_snapshot视图是 Oracle AWR（Automatic Workload Repository）的一部分。其中， ### 1、DBA\_HIST\_SQLSTAT dba\_hist\_sqlstat是Oracle数据库中的历史SQL统计信息视图，用于提供有关SQL语句执行的历史性能信息。它记录了SQL语句的执行计划、执行时间、消耗的资源等统计数据。dba\_hist\_sqlstat可以用于监控和分析数据库中的SQL性能问题。 **常用字段：** SQL\_ID：SQL语句的唯一标识符。 SNAP\_ID：快照ID，表示采样的时间点。 DBID：数据库ID。 INSTANCE\_NUMBER：实例编号。 PLAN\_HASH\_VALUE：SQL执行计划的哈希值。 ### 2、DBA\_HIST\_SNAPSHOT dba\_hist\_snapshot是Oracle数据库中的动态视图，用于提供有关历史性能快照的信息。它记录了数据库在不同时间点的性能指标和统计数据。dba\_hist\_snapshot可以用于分析数据库的性能变化和趋势，帮助管理员进行性能监控和故障排查。 **常用字段：** SNAP\_ID：快照的唯一标识符。 BEGIN\_INTERVAL\_TIME：快照的开始时间。 END\_INTERVAL\_TIME：快照的结束时间。 DBID：数据库的唯一标识符。 INSTANCE\_NUMBER：实例的编号。 ### 3、v$SQLTEXT 用于提供有关共享SQL区域中SQL语句文本的信息。它记录了数据库中执行过的SQL语句的文本。vsqltext可以用于查看和分析数据库中执行过的SQL语句的具体文本内容。 **常用字段：** SQL\_ID：SQL语句的唯一标识符。 SQL\_TEXT：SQL语句的文本。 ### 4、v$SQL⭐ 用于提供有关SQL语句执行的统计信息和执行计划 SQL\_TEXT：SQL语句的文本，最多1000个字符。 SQL\_FULLTEXT：SQL语句的完整文本，以CLOB（Character Large Object）形式存储。SQL\_ID：SQL语句的唯一标识符，最多13个字符。 SHARABLE\_MEM：共享内存的大小，以字节为单位。 PERSISTENT\_MEM：持久内存的大小，以字节为单位。 RUNTIME\_MEM：运行时内存的大小，以字节为单位。 SORTS：排序操作的次数。 **EXECUTIONS：SQL语句的执行次数。** PARSE\_CALLS：解析调用的次数。 DISK\_READS：磁盘读取的次数。 DIRECT\_WRITES：直接写入的次数。 DIRECT\_READS：直接读取的次数。 BUFFER\_GETS：缓冲区获取的次数。 APPLICATION\_WAIT\_TIME：应用程序等待的时间。 CONCURRENCY\_WAIT\_TIME：并发等待的时间。 CLUSTER\_WAIT\_TIME：集群等待的时间。 USER\_IO\_WAIT\_TIME：用户I/O等待的时间。 PLSQL\_EXEC\_TIME：PL/SQL执行的时间。 JAVA\_EXEC\_TIME：Java执行的时间。 ROWS\_PROCESSED：处理的行数。 COMMAND\_TYPE：命令类型的编号。 OPTIMIZER\_MODE：优化器模式。 OPTIMIZER\_COST：优化器成本。 OPTIMIZER\_ENV：优化器环境。 三、SQL语句 ------- 将这两个视图通过快照 id join一下得到SQL，记录了系统七天内Oracle数据库的SQL执行次数 ### 1、七天内Oracle数据库的SQL执行次数 `SELECT SUM(ss.executions_delta) AS total_executions FROM DBA_HIST_SQLSTAT ss JOIN DBA_HIST_SNAPSHOT sn ON ss.snap_id = sn.snap_id WHERE sn.begin_interval_time BETWEEN SYSDATE - 7 AND SYSDATE; --结果 TOTAL_EXECUTIONS ---------------- 122282` 注意： **1、AWR收集**：该查询依赖于AWR数据，因此AWR必须启用，需要确保系统快照的保留策略为7天以上。详情请**看扩展1**，平时如何管理AWR报告的收集时间，默认是1小时收集一次，保留策略怎么设置。 **2、 executions\_delta**参数指的是该查询计算的过去7天内所有 SQL 语句的**总执行次数变化**。⭐ Oracle给出的定义是：自将此对象引入库缓存以来，对该对象执行的增量执行次数 参考文档：[https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/DBA\_HIST\_SQLSTAT.html](https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/DBA_HIST_SQLSTAT.html) ### 2、优化：按天分组按次数降序排列 现在想要了解在特定时间范围内 SQL 语句的执行频率。 所以更改一下SQL查询每天的SQL执行次数，通过TRUN 截断日期时间值 ，**按日期分组并按照每天总执行次数降序排序**。 `SELECT TRUNC(sn.begin_interval_time) AS query_date, SUM(ss.executions_delta) AS total_executions FROM DBA_HIST_SQLSTAT ss JOIN DBA_HIST_SNAPSHOT sn ON ss.snap_id = sn.snap_id WHERE sn.begin_interval_time BETWEEN SYSDATE - 7 AND SYSDATE GROUP BY TRUNC(sn.begin_interval_time) ORDER BY total_executions desc; QUERY_DATE TOTAL_EXECUTIONS ------------------ ---------------- 16-JUL-24 59989 18-JUL-24 21884 17-JUL-24 21523 12-JUL-24 12903 15-JUL-24 10264` 在项目上大神开发的可视化界面首页应该就是用的这条SQL，可以很明显看出哪一天的SQL执行量，如果有异常，再去分析问题。 如果某一条SQL执行异常，需要做分析，怎么找出这条SQL文本。 ### 3、根据sql\_id查询SQL语句 `SELECT SQL_TEXT FROM v$sqltext WHERE SQL_ID = 'cmhz88h821m04';` 有时SQL文本过长v$sqltext中SQL\_TEXT字段最多1000个字符，可能放不下，我们可以看v$sql中的SQL\_FULLTEXT字段保留了SQL语句的完整文本，以CLOB形式存储。 ### 4、根据sql\_id查询完整的SQL `SELECT SQL_FULLTEXT FROM v$sql WHERE SQL_ID = 'cmhz88h821m04';` 拿到SQL之后可以看看**sql的执行计划**分析问题 ### 5、根据sql\_id查看执行计划 `select * from table(dbms_xplan.display_cursor('cmhz88h821m04',0,'ALLSTATS LAST'));` **扩展2**，查看执行计划的方法 **可以先从v$sql中查出sql\_id，再通过dbms\_xplan\_display\_cursor( sql\_id,child\_number,format)查指定的sql执行计划** **sql\_id：**指定位于库缓存执行计划中 SQL 语句的父游标。默认值为 null，表示最后一条语句的执行计划，可以换成想要查看SQL的sql\_id。通过查询V$SQL 或V$SQLAREA的SQL\_ID列来获得SQL语句的SQL\_ID。 **child\_number：**指定父游标下子游标的序号，默认值为 0，不返回子游标的执行计划，null则全部返回。 **format：**控制 SQL 语句执行计划的输出部分。常用的有：BASIC: 显示最少的信息、TYPICAL: 默认值。SERIAL、ALL: 显示最多的信息、**IOSTATS、MEMSTATS、ALLSTATS等。** ### 6、查询指定SQL语句的历史执行计划 `SELECT SQL_ID, PLAN_HASH_VALUE, OPERATION, OPTIONS, OBJECT_NAME, OBJECT_TYPE, COST, CARDINALITY, BYTES, PARTITION_START, PARTITION_STOP FROM dba_hist_sql_plan WHERE SQL_ID = 'cmhz88h821m04';` 这里可以再做一个**扩展3**，快照的管理，手工创建快照。再找出SQL之后，我们还可能会用到AWR和ADDM等工具帮助我们去分析问题，AWR报告默认是一个小时收集一次快照，我们可以在想要分析的SQL语句执行前后手工创建或者建一个基线，方面分析报告。 工作中往往会遇到紧急案例CPU、IO、内存飙升、甚至宕机，下一篇我可能会写关于紧急案例-CPU飙升如何快速定位到SQL语句，数据库hang住，杀会话等问题的处理。 下面扩展2应该是重点部分，之前在天津实习的时候在那用不到，只学到了备份恢复那一块，来河北刚进组就听什么慢SQL处理，性能优化，压力很大啊。后面可能会开一个优化专题，关注博主，哈哈！ 扩展1：AWR性能报告收集的管理 ---------------- #### 1\. 查看AWR的收集时间和保留时间 可以通过dba\_hist\_wr\_control视图进行AWR的设置控制，包括快照间隔、保留时间和TOPNSQL设置。 `select * from dba_hist_wr_control; DBID SNAP_INTERVAL ---------- --------------------------------------------------------------------------- RETENTION TOPNSQL --------------------------------------------------------------------------- ---------- 1696225019 +00000 01:00:00.0 +00008 00:00:00.0 DEFAULT` **DBID：**数据库实例的唯一标识 **SNAP\_INTERVAL：**快照间隔，每一小时收集一次快照。格式天，时分秒， **RETENTION：**快照保留时间，这里为8天，所以可以完全可以收集七天内的信息。格式，天，时分秒 **TOPNSQL：**在每个AWR快照期间收集的SQL语句数量 #### 2\. 修改AWR收集时间和保留策略 awr 默认通过 mmon 及 mmnl 进程来**每小自动收集一次**，为了节省空间，采集的数据在在保留一定时间后自动清除。上面看到的是11g Oracle默认保留8天，10g为7天。 可以用PL/SQL中的dbms\_workload\_repository.modify\_snapshot\_settings设置 **案例：设置AWR的收集时间为60min，保留时间为30day。** `SYS@orcl> begin dbms_workload_repository.modify_snapshot_settings(interval => 60, retention => 30*24*60); end; / PL/SQL procedure successfully completed. SYS@orcl> SYS@orcl> select * from dba_hist_wr_control; DBID SNAP_INTERVAL ---------- --------------------------------------------------------------------------- RETENTION TOPNSQL --------------------------------------------------------------------------- ---------- 1696225019 +00000 01:00:00.0 +00030 00:00:00.0` 扩展2：查看执行计划的方法 ------------- **SQLPLUS AUTOTRACE** --计划执行不真实 **Explain Plan For SQL** \--计划执行不真实 **使用 DBMS\_XPLAN 包** --计划执行真实⭐ **statistics\_level=all;** \--计划执行真实 **sql\_trace 与 10046** \--计划执行真实⭐ 扩展3：快照的管理 --------- ### 1、场景： Ⅰ、**我不想要一个小时的，只想要看目标语句、性能测试、压力测试那一段的报告。** Ⅱ、数据库出现过异常，无法生成AWR，检查能否创建快照。 ### 2、手工创建快照： 用 create\_snapshot 存储过程手动创建快照： `begin dbms_workload_repository.create_snapshot(); end; /` ### 3、查看快照信息 **select \* from dba\_hist\_snapshot;** ### 4、手工删除快照 **指定要删除的快照id范围** `begin dbms_workload_repository.drop_snapshot_range(low_snap_id => 30,high_snap_id => 31); end; /` 如果有删不掉的快照，可能是创建了基线，需要先把极限删除，再删除快照。 ### 5、查看基线的视图 **select \* from dba\_hist\_baseline;** ### 6、删除基线及其快照 也可以在删除基线的时候连快照一起删掉： `begin dbms_workload_repository.drop_baseline(baseline_name => 'xxx', cascade => true); end; /` **cascade 默认false不删除快照，true删除快照。**
oracle分页+排序查询的结果不符合预期
### 问题描述 在oracle数据库中，使用分页查询并对某个字段进行了排序，查询出来的结果中stock_quantity字段的值跟预期的不一样 ### 具体疑问 这是什么原因造成的，要怎么解决 ### sql ```sql SELECT ROWNUM ROW_ID, TMP_PAGE.* FROM ( select g.id as goods_id, g.name, g.category, g.details, g.recommendation_flag, g.inaccurate_stock_quantity, g.inaccurate_exchange_quantity, g.stock_quantity, g.exchange_quantity, g.createtime, gcr.id as goods_change_record_id, gcr.listing_time, gcr.de_listing_time, gcr.valid_flag, gcr.exchange_method, gcr.integral1, gcr.integral2, gcr.amount, gp.id as goods_photo_id, gp.url from goods g right join goods_change_record gcr on g.id = gcr.goods_id left join goods_photo gp on g.id = gp.goods_id where 1 = 1 order by goods_id desc) TMP_PAGE WHERE ROWNUM <= 10 ``` ### sql运行截图 单独运行子查询 <img src="https://pic.code-nav.cn/post_picture/1813782025129394177/vpPCbQ8W-WechatIMG34.webp" alt="WechatIMG34.jpg" width="100%" /> 运行整个查询 <img src="https://pic.code-nav.cn/post_picture/1813782025129394177/63TOkr6e-WechatIMG35.webp" alt="WechatIMG35.jpg" width="100%" />
《收获，不止Oracle》
密码：qwer 地址：https://pan.baidu.com/share/init?surl=XrMO2HGg9KECKvaEX4eGtw
PL/SQL教程
1、PL/SQL简介 2、PL/SQL块 3、PL/SQL数据类型 4、PL/SQL控制结构 5、PL/SQL动态执行DDL语句 6、PL/SQL异常处理 7、Oracle创建函数 8、Oracle存储过程 9、Oracle游标 10、Oracle触发器 11、Oracle DML类型触发器 12、Oracle DDL类型触发器 13、Oracle事物 14、Oracle锁 地址：https://www.oraclejsq.com/plsql/010200446.html
free教程
地址：https://www.oraclejsq.com/
Oracle
地址：https://www.bilibili.com/video/BV1kx411s71n?from=search&seid=11606470669726723621