手工生成HTML格式AWR遇到Bug 13527323解决实例-Mysql Tutorial-php.cn

Home

Database

Mysql Tutorial

手工生成HTML格式AWR遇到Bug 13527323解决实例

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 07, 2016 pm 04:49 PM

Oracle中的AWR报告是目前官方提供的最好的性能分析诊断工具。借助对于一个连续snapshot的分析，我们可以快速的获取到整体性能分析

Oracle中的AWR报告是目前官方提供的最好的性能分析诊断工具。借助对于一个连续snapshot的分析，我们可以快速的获取到整体性能分析指标，综合定位问题发生点。

任何Oracle版本中，我们都可能会遇到各种类型的Bug。发现Bug之后，和官方公布内容进行匹配确认，找到解决或者规避方法，是数据库使用者应该具备的一种能力。

1、环境介绍和故障发生

笔者在11.2.0.3环境下，生成巡检AWR报告，想查看业务高峰期作业负载情况。

SQL> select * from v$version;

BANNER

------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

PL/SQL Release 11.2.0.3.0 - Production

CORE 11.2.0.3.0 Production

TNS for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production

NLSRTL Version 11.2.0.3.0 – Production

使用sqlplus命令行来进行报告生成过程，调用awrrpt.sql。

D:\>sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0Production on 星期四 2月 27 09:07:02 2014

SQL> @?/rdbms/admin/awrrpt.sql

Current Instance

~~~~~~~~~~~~~~~~

DB Id DB Name Inst Num Instance

----------- ------------ -------- ------------

1083126127 COGDB 1 cogdb

Specify the Report Type

~~~~~~~~~~~~~~~~~~~~~~~

Would you like an HTML report, or a plain text report?

Enter 'html' for an HTML report, or 'text' for plain text

AWR目前支持两个格式，html和text格式。text格式是从AWR的前身statspack中延续而来，格式简单、生成负载小，但是可读性不强。html格式是我们通常的选择。

但是，本次报告出错。

SELECT dbin.instance_number, d...

ERROR:

ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小

ORA-06512: 在 "SYS.DBMS_WORKLOAD_REPOSITORY", line 919

ORA-06512: 在 line 1

Report written to awrrpt_1_9583_9584.html

报告生成失败，在目录上虽然有报告文件、也可以打开，但是生成文件不完整。

2、问题检查和确定

一般而言，客户端脚本和服务器端程序不匹配，通常是造成脚本执行的一个很重要的原因。比如：我们使用10g客户端的旧脚本，，调用11g服务器的新程序，是可能引起故障的。比较典型的就是还原数据字典的过程。

AWR生成脚本awrrpt.sql虽然是在客户端，但是只是负责参数数据收集和传入。生成AWR报告的关键程序是dbms_workload_repository包，awrrpt.sql并不直接和AWR数据字典打交道。所以，由于版本差异引起的问题，在这个案例上可能性是很低的。

为了预防万一，我们在服务器端进行报告生成。

[oracle@MISDB:~]$cd /tmp

[oracle@MISDB:/tmp]$sqlplus /nolog

SQL*Plus: Release 11.2.0.3.0 Production on Thu Feb 27 09:13:23 2014

SQL> conn / as sysdba

Connected.

SQL> @?/rdbms/admin/awrrpt.sql

Current Instance

~~~~~~~~~~~~~~~~

DB Id DB Name Inst Num Instance

----------- ------------ -------- ------------

1083126127 COGDB 1 cogdb

Specify the Report Type

~~~~~~~~~~~~~~~~~~~~~~~

Would you like an HTML report, or a plain text report?

Enter 'html' for an HTML report, or 'text' for plain text

Defaults to 'html'

Enter value for report_type:

要求生成html格式报告，依然报错。

SELECT dbin.instance_number, d...

ERROR:

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 919

ORA-06512: at line 1

Report written to awrrpt_1_9583_9584.html

这种场景的确是不正常的，而且由于代码加密的原因，我们也不能真的跟踪到line 919行。这个时候可能就需要求助于官方经验集合MOS。

经过查询，在MOS中有一篇对于Bug13527323的介绍，文章编号为： ORA-6502 generating HTML AWR report using awrrpt.sql in Multibyte characterset database (文档 ID 13527323.8)。

文章中，Oracle说在一些时候，使用awrrpt.sql脚本生成报告是会抛出错误ora-6502的。对应的影响版本为11.2.0.3，而且Oracle认为在12.1一下都可能发生错误。这个描述与当前笔者遇到的情况相同。

对于故障原因，Oracle解释是：In case of multibyte characters in SQL text, sometime AWR report generation fails with error ORA-6502. Also sending more than 4000 bytes of single varchar data to JDBC api's causes truncation of data.

Rediscovery Notes:

If html AWR report generation fails with ORA-6502 or generating

AWR report using JDBC api's causes truncation of data then this bug

as probably been rediscovered.

如果在SQL text中存在多种语言表示，那么在数据输出的时候，就会抛出异常ora-6502。

问题基本上就可以确定是这个了。那么怎么解决呢？对于策略，Oracle没有明确说补丁集合之类，只说这种情况比较偶然。

3、问题解决

虽然定位了问题，但是终究还是需要解决的。html格式存在问题，那么更简单的text格式是否不会抛出异常呢？

SQL> @?/rdbms/admin/awrrpt.sql

Current Instance

~~~~~~~~~~~~~~~~

DB Id DB Name Inst Num Instance

----------- ------------ -------- ------------

1083126127 COGDB 1 cogdb

Specify the Report Type

~~~~~~~~~~~~~~~~~~~~~~~

Would you like an HTML report, or a plain text report?

Enter 'html' for an HTML report, or 'text' for plain text

Defaults to 'html'

输入 report_type 的值: text

Type Specified: text

最后结果成功生成：

Parameter Name Begin value (if different)

----------------------------- --------------------------------- --------------

control_files /home/oracle/controlfile/cogdb/co

/oradb/app/oracle/oradata/control

---------------------------------------------

Dynamic Remastering Stats DB/Inst: COGDB/cogdb Snaps: 9583-9584

No data exists for this section of the report.

-----------------------------------------

End of Report

Report written to awrrpt_1_9583_9584.txt

确认报告内容也是完整的。在SQL Text部分，我们也的确发现了“多种语言”的情况。

-> Captured PL/SQL account for 0.0% of Total DB Time (s): 3,165

Elapsed Elapsed Time

Time (s) Executions per Exec (s) %Total %CPU %IO SQL Id

Statement

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Explain the InnoDB Buffer Pool and its importance for performance.Apr 19, 2025 am 12:24 AM

InnoDBBufferPool reduces disk I/O by caching data and indexing pages, improving database performance. Its working principle includes: 1. Data reading: Read data from BufferPool; 2. Data writing: After modifying the data, write to BufferPool and refresh it to disk regularly; 3. Cache management: Use the LRU algorithm to manage cache pages; 4. Reading mechanism: Load adjacent data pages in advance. By sizing the BufferPool and using multiple instances, database performance can be optimized.

MySQL vs. Other Programming Languages: A ComparisonApr 19, 2025 am 12:22 AM

Compared with other programming languages, MySQL is mainly used to store and manage data, while other languages such as Python, Java, and C are used for logical processing and application development. MySQL is known for its high performance, scalability and cross-platform support, suitable for data management needs, while other languages have advantages in their respective fields such as data analytics, enterprise applications, and system programming.

Learning MySQL: A Step-by-Step Guide for New UsersApr 19, 2025 am 12:19 AM

MySQL is worth learning because it is a powerful open source database management system suitable for data storage, management and analysis. 1) MySQL is a relational database that uses SQL to operate data and is suitable for structured data management. 2) The SQL language is the key to interacting with MySQL and supports CRUD operations. 3) The working principle of MySQL includes client/server architecture, storage engine and query optimizer. 4) Basic usage includes creating databases and tables, and advanced usage involves joining tables using JOIN. 5) Common errors include syntax errors and permission issues, and debugging skills include checking syntax and using EXPLAIN commands. 6) Performance optimization involves the use of indexes, optimization of SQL statements and regular maintenance of databases.

MySQL: Essential Skills for Beginners to MasterApr 18, 2025 am 12:24 AM

MySQL is suitable for beginners to learn database skills. 1. Install MySQL server and client tools. 2. Understand basic SQL queries, such as SELECT. 3. Master data operations: create tables, insert, update, and delete data. 4. Learn advanced skills: subquery and window functions. 5. Debugging and optimization: Check syntax, use indexes, avoid SELECT*, and use LIMIT.

MySQL: Structured Data and Relational DatabasesApr 18, 2025 am 12:22 AM

MySQL efficiently manages structured data through table structure and SQL query, and implements inter-table relationships through foreign keys. 1. Define the data format and type when creating a table. 2. Use foreign keys to establish relationships between tables. 3. Improve performance through indexing and query optimization. 4. Regularly backup and monitor databases to ensure data security and performance optimization.

MySQL: Key Features and Capabilities ExplainedApr 18, 2025 am 12:17 AM

MySQL is an open source relational database management system that is widely used in Web development. Its key features include: 1. Supports multiple storage engines, such as InnoDB and MyISAM, suitable for different scenarios; 2. Provides master-slave replication functions to facilitate load balancing and data backup; 3. Improve query efficiency through query optimization and index use.

The Purpose of SQL: Interacting with MySQL DatabasesApr 18, 2025 am 12:12 AM

SQL is used to interact with MySQL database to realize data addition, deletion, modification, inspection and database design. 1) SQL performs data operations through SELECT, INSERT, UPDATE, DELETE statements; 2) Use CREATE, ALTER, DROP statements for database design and management; 3) Complex queries and data analysis are implemented through SQL to improve business decision-making efficiency.

MySQL for Beginners: Getting Started with Database ManagementApr 18, 2025 am 12:10 AM

The basic operations of MySQL include creating databases, tables, and using SQL to perform CRUD operations on data. 1. Create a database: CREATEDATABASEmy_first_db; 2. Create a table: CREATETABLEbooks(idINTAUTO_INCREMENTPRIMARYKEY, titleVARCHAR(100)NOTNULL, authorVARCHAR(100)NOTNULL, published_yearINT); 3. Insert data: INSERTINTObooks(title, author, published_year)VA

See all articles