Submit a ticketCall us

Webinar: Web Help Desk for HR, Facilities and Accounting Departments
This webinar will focus on use cases for HR, Facilities and Accounting.

Having a unified ticketing and asset management system for all the departments in your company can provide end-users with a seamless experience and make things easier for your IT team. Yet, with different business tasks and objectives, many departments don’t fully understand the capabilities of Web Help Desk and how the software can be customized for effective use in their departments.
Register Now.

Home > Success Center > Database Performance Analyzer (DPA) > Data truncation errors in Oracle plan poll job on SQL Server repositories

Data truncation errors in Oracle plan poll job on SQL Server repositories

Table of contents

Overview

Early releases of DPA had insufficiently sized some numeric columns for the Repository tables that store explain plan text for monitored Oracle database instances (SQL Server Repositories only). The names of these tables are prefixed with 'CONPT_' and end with the database instance's ID (as specified in the COND table).

This issue may cause data truncation errors when numeric values retrieved from the explain plan exceed the space allotted in the corresponding column in the Repository table. The error that is seen in the log is:

    Monitor for database [DATABASE_NAME] failed in job [PlanPollJob] due to [Data truncation]

If this error is observed for a database instance, the plan table (CONPT_<databaseid>) will need to be resized. Due to the potential size of these tables and the restriction of SQL Server to only allow modification of a single column at a time, resizing the necessary columns is best done by recreating the table.

Environment

  • All versions of DPA

Steps

  1. Determine the database affected.
    • The database name is evident in the error message.
    • The database ID is in the SQL embedded in error message (ID is number following CONPT_)
  2. Stop the monitor for this database instance (??? stop monitor, disable plan poll ???).
  3. Rename the original CONPT table using sp_rename (inserting the correct database ID).
    • sp_rename 'CONPT_<database_id>','CONPT_<database_id>_OLD'</database_id></database_id>
  4. Create the new table using the following script  (inserting the correct database ID):

    CREATE TABLE CONPT_<databaseid> (</databaseid>

      [PLAN_HASH_VALUE]    Numeric(38,0) NOT NULL,

      [ID]                 Numeric(38,0) NOT NULL,

      [TIMESTAMP]          Datetime,

      [OPERATION]          Varchar(50),

      [OPTIONS]            Varchar(50),

      [OBJECT_NODE]        Varchar(50),

      [OBJECT#]            Numeric(38,0),

      [OBJECT_OWNER]       Varchar(50),

      [OBJECT_NAME]        Varchar(50),

      [OBJECT_ALIAS]       Varchar(100),

      [OBJECT_TYPE]        Varchar(50),

      [OPTIMIZER]          Varchar(50),

      [PARENT_ID]          Numeric(38,0),

      [DEPTH]              Numeric(38,0),

      [POSITION]           Numeric(38,0),

      [SEARCH_COLUMNS]     Numeric(38,0),

      [COST]               Numeric(38,0),

      [CARDINALITY]        Numeric(38,0),

      [BYTES]              Numeric(38,0),

      [OTHER_TAG]          Varchar(50),

      [PARTITION_START]    Varchar(64),

      [PARTITION_STOP]     Varchar(64),

      [PARTITION_ID]       Numeric(38,0),

      [OTHER]              Varchar(4000),

      [DISTRIBUTION]       Varchar(50),

      [CPU_COST]           Numeric(38,0),

      [IO_COST]            Numeric(38,0),

      [TEMP_SPACE]         Numeric(38,0),

      [ACCESS_PREDICATES]  Varchar(4000),

      [FILTER_PREDICATES]  Varchar(4000),

      [PROJECTION]         Varchar(4000),

      [TIME]               Numeric(38,0),

      [QBLOCK_NAME]        Varchar(50),

      [REMARKS]            Varchar(4000),

      PRIMARY KEY ([PLAN_HASH_VALUE], [ID])

    )

  5. Insert existing data from the original table into the new table using the following statement:
    • insert into CONPT_<database_id> select * from CONPT_<database_id>_OLD</database_id></database_id>
  6. Restart the monitor for the database instance.

    Once it is determined that the error has been resolved and plan text is being successfully collected (evidenced by new rows appearing in the new CONPT table), the old table can be dropped.

Last modified
16:45, 13 Jan 2016

Tags

Classifications

Public