1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

The first reading of data is carried out very slowly

Discussion in 'SQL PL/SQL' started by jesthr@gmail.com, May 30, 2012.

  1. jesthr@gmail.com

    jesthr@gmail.com Active Member

    Messages:
    23
    Likes Received:
    0
    Trophy Points:
    80
    Dear

    It might be better question how to keep the execution plan for the upgrade Oracle from 10g to 11g

    I made uprade Oracle from 1og to 11g on HP-UX system.

    ALL the first readeing of data carried out very slowly

    The selected table have 10,892,769 records.
    I'm executing SQL SELECT COUNT (*) FROM MyTABLE;

    The first implementation of this SQL statement carried 8 min and 12 sec
    >> COUNT (*) 10,892,769 1 row selected. 8:12

    The another implementation of the same SQL statement carried 5 sec.
    >> COUNT (*) 10,892,769 1 row selected. 00:05

    What can I do to make the first implementation of the SQL statement carried only 5 sec.

    Regards!


    Hine
     
  2. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Have you tried to implement same query as select count(*) from MyTABLE; or any other query at another implementation.
     
  3. jesthr@gmail.com

    jesthr@gmail.com Active Member

    Messages:
    23
    Likes Received:
    0
    Trophy Points:
    80
    I made upgrade Oracle from 10g to 11g.
    After upgrade I don' have execution plan!

    All first access to any tables carried out very slowly, all other accesses are o.k.

    Regards
     
  4. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Dear Hine,

    Check out whether those tables having proper indexes or not and also whether they enabled or not.
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    An index won't cause the second query to run in no tiime at all when the first query took such a long time. I've answered this question before in another thread and explained that 11g implements a results cache which definitely speeds up repeated access to a table given that no changes were made between the times of the two or more queries. The cache must be populated before the benefits are seen; for a first run of a query it will take as long as it takes. Yes, an index can help speed access to the data but a count(*) query will do a full table scan in the absence of a primary key index and if there are no unique keys in the data a primary key cannot be created outside of adding a column to the table, populating it with unique values and using it for the key.
     
    Bharat likes this.