Loading ...
Sorry, an error occurred while loading the content.

Tree, nVision and general database integrity problems

Expand Messages
  • Phillip Eaton
    Hello All, Sun Solaris 8 - Oracle 8.1.7.2-64 - PT8.41.20 - Financials 8.40.00 We ve had a couple of database issues on our PeopleSoft system, that I would
    Message 1 of 2 , Jul 1, 2005
    • 0 Attachment
      Hello All,

      Sun Solaris 8 - Oracle 8.1.7.2-64 - PT8.41.20 - Financials 8.40.00

      We've had a couple of database issues on our PeopleSoft system, that I would
      appreciate your views and comments on.

      ---

      The first problem was to do with our nVisions. Our users noticed that some
      of the values in them were coming back as zero's when they shouldn't have
      been.

      After looking into it, we eventually found that 3 of the PSTREESELECTnn
      tables had been updated, but the PSTREESELCTL table, which acts as a kind of
      header table for these, wasn't in sync.

      The CTL tables are updated when the trees are changed, and I think nVision
      then rebuilds the SELECTnn tables.

      However, the users tell me that no-one had changed the trees, and the fact
      that the CTL table hadn't changed confirmed this.

      I did a data comparison with a backup from a week ago, and that was what
      showed that the SELECTnn tables had been modified, but not the CTL table.

      The fix was to delete the CTL table rows that were out of sync and let them
      be automatically rebuilt when nVision accessed them.

      Has anyone else had problems with the SELECT tables for trees?

      ---

      The second problem came hot on the heels of the first problem, but is not
      obviously related.

      All the nVisions started running slowly. To cut a long story short, we
      needed to rebuild the statistics on the database to regain the speed.

      Our stats get fully rebuilt every Sunday, when the system is down, and
      whilst we were parallel running the system when we upgraded to it, we never
      rebuilt the stats for months, and had no problems.

      What could cause our stats to go wrong overnight, 2 days after we rebuilt
      them?

      ---

      Only one event springs to mind - we had 2 hard disk failures in the previous
      week, and our UNIX/DBA admins had to change them.

      They also told me that when the disk failures happened, our RAID level
      changed to a 'less good level', and we allowed them to shut down the server
      for them to install the new ones.

      We run a Sun V880 with a dedicated storage array. Our DBAs assure us that
      these disk failures would have no impact on the PeopleSoft database in any
      way, but they also have no explanation for the loss of our stats and the
      missing data in the SELECT tables.

      If anyone can share their experiences on these two issues, it would be
      greatly appreciated.

      Sun Solaris 8 - Oracle 8.1.7.2-64 - PT8.41.20 - Financials 8.40.00

      Phillip Eaton
      Business Applications Administration Group
      Gulf International Bank (UK) Ltd.,
      One Knightsbridge, London SW1X 7XS, U.K.
      +44 (0) 20 7259 3402
      phillip.eaton@...
      "Gulf International Bank (UK) Limited ("GIBUK") is regulated in the UK by
      the Financial Services Authority and have their office at One Knightsbridge,
      London, SW1X 7XS. Tel 0207 2593456.This e-mail message and any file
      transmitted with it is confidential to the intended recipient and may
      contain confidential and or legally privileged information. If you are not
      the intended recipient, you may not copy, distribute or disclose the
      contents to anyone, nor take any action in reliance on its contents. Should
      you receive this message in error, please delete it immediately and all
      copies of it from your system, destroying any hard copies and notifying the
      sender.Please note that any email sent to, or from GIBUK may be monitored
      for content. GIBUK does not accept any legal responsibility for the content
      of this message or any attachments. Virus checking is the responsibility of
      the recipient."
    • David Kurtz
      I have never seen PSTREESELCTL get out of sync. You do sometimes get debris collecting there after nVisions crash if you use dynamic selectors. I also prefer
      Message 2 of 2 , Jul 1, 2005
      • 0 Attachment
        I have never seen PSTREESELCTL get out of sync. You do sometimes get debris
        collecting there after nVisions crash if you use dynamic selectors. I also
        prefer to use static selectors.

        To produce good performane with nVision you require 4 elements

        . NVision performance options: This is a PeopleTools configuration option
        specified on the tree definition. It changes the SQL that is generated by
        the nVision reports. Always use literal values, static selectors and the
        between construction.
        . Indexing: The indexing on the PS_LEDGER and PS_LEDGER_BUDG tables should
        be organised to match the analysis criteria of the nVision reports
        . Partitioning: Most companies store several years of data on PS_LEDGER and
        PS_LEDGER_BUDG. NVision reports query specific fiscal years and sometimes
        specific accounting periods within those fiscal years. If the tables are
        range partitioned, Oracle is able to eliminate unnecessary partitions from
        the query at an early stage - the problem is that partitioning is a
        separately licensed option.
        . Oracle Configurtion: Parallel query should be disabled once partitioning
        is introduced

        regards
        _________________________
        David Kurtz
        Go-Faster Consultancy Ltd.
        tel: +44 (0)7771 760660
        fax: +44 (0)7092 348865
        web: www.go-faster.co.uk
        mailto:david.kurtz@...
        Book: PeopleSoft for the Oracle DBA: http://www.psftdba.com
        Next Go-Faster Seminar: PeopleSoft for the DBA, Manchester UK, 26-28 July
        PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba

        > -----Original Message-----
        > From: psftdba@yahoogroups.com [mailto:psftdba@yahoogroups.com]On Behalf
        > Of Phillip Eaton
        > Sent: 01 July 2005 12:46
        > To: 'psftdba@yahoogroups.com'
        > Subject: PeopleSoft DBA Forum Tree, nVision and general database
        > integrity problems
        >
        >
        > Hello All,
        >
        > Sun Solaris 8 - Oracle 8.1.7.2-64 - PT8.41.20 - Financials 8.40.00
        >
        > We've had a couple of database issues on our PeopleSoft system,
        > that I would
        > appreciate your views and comments on.
        >
        > ---
        >
        > The first problem was to do with our nVisions. Our users noticed that some
        > of the values in them were coming back as zero's when they shouldn't have
        > been.
        >
        > After looking into it, we eventually found that 3 of the PSTREESELECTnn
        > tables had been updated, but the PSTREESELCTL table, which acts
        > as a kind of
        > header table for these, wasn't in sync.
        >
        > The CTL tables are updated when the trees are changed, and I think nVision
        > then rebuilds the SELECTnn tables.
        >
        > However, the users tell me that no-one had changed the trees, and the fact
        > that the CTL table hadn't changed confirmed this.
        >
        > I did a data comparison with a backup from a week ago, and that was what
        > showed that the SELECTnn tables had been modified, but not the CTL table.
        >
        > The fix was to delete the CTL table rows that were out of sync
        > and let them
        > be automatically rebuilt when nVision accessed them.
        >
        > Has anyone else had problems with the SELECT tables for trees?
        >
        > ---
        >
        > The second problem came hot on the heels of the first problem, but is not
        > obviously related.
        >
        > All the nVisions started running slowly. To cut a long story short, we
        > needed to rebuild the statistics on the database to regain the speed.
        >
        > Our stats get fully rebuilt every Sunday, when the system is down, and
        > whilst we were parallel running the system when we upgraded to
        > it, we never
        > rebuilt the stats for months, and had no problems.
        >
        > What could cause our stats to go wrong overnight, 2 days after we rebuilt
        > them?
        >
        > ---
        >
        > Only one event springs to mind - we had 2 hard disk failures in
        > the previous
        > week, and our UNIX/DBA admins had to change them.
        >
        > They also told me that when the disk failures happened, our RAID level
        > changed to a 'less good level', and we allowed them to shut down
        > the server
        > for them to install the new ones.
        >
        > We run a Sun V880 with a dedicated storage array. Our DBAs assure us that
        > these disk failures would have no impact on the PeopleSoft database in any
        > way, but they also have no explanation for the loss of our stats and the
        > missing data in the SELECT tables.
        >
        > If anyone can share their experiences on these two issues, it would be
        > greatly appreciated.
        >
        > Sun Solaris 8 - Oracle 8.1.7.2-64 - PT8.41.20 - Financials 8.40.00
        >
        > Phillip Eaton
        > Business Applications Administration Group
        > Gulf International Bank (UK) Ltd.,
        > One Knightsbridge, London SW1X 7XS, U.K.
        > +44 (0) 20 7259 3402
        > phillip.eaton@...
        > "Gulf International Bank (UK) Limited ("GIBUK") is regulated in the UK by
        > the Financial Services Authority and have their office at One
        > Knightsbridge,
        > London, SW1X 7XS. Tel 0207 2593456.This e-mail message and any file
        > transmitted with it is confidential to the intended recipient and may
        > contain confidential and or legally privileged information. If
        > you are not
        > the intended recipient, you may not copy, distribute or disclose the
        > contents to anyone, nor take any action in reliance on its
        > contents. Should
        > you receive this message in error, please delete it immediately and all
        > copies of it from your system, destroying any hard copies and
        > notifying the
        > sender.Please note that any email sent to, or from GIBUK may be monitored
        > for content. GIBUK does not accept any legal responsibility for
        > the content
        > of this message or any attachments. Virus checking is the
        > responsibility of
        > the recipient."
        >
        >
        > PeopleSoft for the Oracle DBA is published by Apress - see
        http://www.psftdba.com
        The PeopleSoft DBA Forum is managed by http://www.go-faster.co.uk

        Yahoo! Groups Links
      Your message has been successfully submitted and would be delivered to recipients shortly.