43 积分	0 好友	1 主题

发消息

AWR 乱码

1^#

发表于 2012-7-25 09:47:01 | 查看: 7373| 回复: 4

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

Linux localhost.localdomain 2.6.18-128.el5 #1 SMP Wed Dec 17 11:41:38 EST 2008 x86_64 x86_64 x86_64 GNU/Linux

环境变量：
export NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16GBK"
export LANG=zh_CN.GB18030
OR：
export NLS_LANG=American_America.ZHS16GBK

AWR乱码，估计是字符集的问题，但不知道该怎么去改，麻烦大家支个招，谢谢了！

awr_orc11g_2012-07-25_2468_2469.zip

78.68 KB, 下载次数: 743

分享0

收藏0 回复只看该作者道具举报

Maclean Liu(刘相兵

2^#

发表于 2012-7-25 09:50:38

SQL 语句里有中文乱码属于正常现象

如果你确实需要知道该SQL的文本的话，可以用 SQL_ID 到 DBA_HIST_SQLTEXT这个视图里查一下获得

回复只看该作者道具举报

Maclean Liu(刘相兵

3^#

发表于 2012-7-25 10:05:50

版本11.2.0.1.0 ，症状 v$SQL 中可以看到正常的SQL语句，而 DBA_HIST_SQLTEXT和 AWR中显示乱码

按照以上症状就可以找到相关的bug note:

ODM FINDING:

Hdr: 10136485 11.1.0.7 RDBMS 11.1.0.7 LOB ACCESS PRODID-5 PORTID-197
Abstract: GARBLED SQL TEXT IN AWR REPORT AND DBA_HIST_SQLTEXT


  BUG TYPE CHOSEN
  ===============
  Code

  SubComponent: LOB Access
  ========================
  DETAILED PROBLEM DESCRIPTION
  ============================
  When generating AWR report, Some of the SQL text looks garbled. Sometime
  it's opposite question marks and sometime other garbles characters like this
  :
  DIAGNOSTIC ANALYSIS
  ===================
  1. Searching for the same SQL ID in our knowledge base / Google shows the
  real SQL.
  2. Querying base table shows the following:

  SQL> select * from DBA_HIST_SQLTEXT where sql_id='dwypdxsjg4juq';

   DBID SQL_ID
  ---------- -------------
  SQL_TEXT
  -----------------------------------------------------------------------------
  ---
  COMMAND_TYPE
  ------------
  587877268 dwypdxsjg4juq
  ?????????????????????????????????????????????????????????????????????????????
  ???

  3. This also appear in AWR SQL report (awrsqrpt.sql)

  4. SQL_TEXT for the same SQL ID if found in V$SQL shows readable statement.

  5. On that server, there are 10 RAC databases (10 x 2 nodes). Only 2 out of
  10 database has this issue.

  6. DBA_REGISTRY shows all components are 11.1.0.7 (I made that check based
  on other older bugs)

  7. In one of the AWR SQL Reports, I see the following:

  Complete List of SQL Text

  ERROR: ORA-6502: PL/SQL: numeric or value error: character string buffer
  too small ORA-6512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 834 ORA-6512:
  at line 1

  Not sure if it's coming from the same root cause.

  WORKAROUND?
  ===========
  No

  TECHNICAL IMPACT
  ================
  Queries appear in the AWR are not readable.

  RELATED ISSUES (bugs, forums, RFAs)
  ===================================
  This is similar to bug 6458801 only it should have been fixed in 11.1.0.7

  HOW OFTEN DOES THE ISSUE REPRODUCE AT CUSTOMER SITE?
  ====================================================
  Always

  DOES THE ISSUE REPRODUCE INTERNALLY?
  ====================================
  Not attempted

  EXPLAIN WHY THE ISSUE WAS NOT TESTED INTERNALLY.
  ================================================
  Some of the queries that show garbled characters are standard internal
  oracle queries, So this is not reproducible outside ct's configuration.

  IS A TESTCASE AVAILABLE?
  ========================
  No

  Link to IPS Package:
  ====================
  not available

回复只看该作者道具举报