Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2003
    Posts
    6

    Question Unanswered: how can i know the DB size & How to speed up in searching

    hi everyone,
    i am using oracle 8i, how can i know the oracle database size already exists in the server & what is the way of increase the database size, & one other question i have a table MPI with Primary Key MRN & among the other columns CiviId is defined as Unique Key, now when i search record using CivilId (where clause) it's taking much time to search a record(from 300000 records are in table), how to speed up the searching,

    i created table as
    create table MPI
    (MRN Char(10),
    CivilId Char(10) not null,
    ......
    ......
    Constraint pk_mpi_mrn PRIMARY KEY(mrn),
    Constraint Uq_mpi_CvlId Unique (CivilId)
    ......
    ......
    );
    even though when i check unique value on both of above during validation (before inserting row into table) using Front End Developer 2000 CivilId takes much time where MRN's performance good, why?
    please guide, i m awaiting eagerly
    thanx in advance
    rahman

  2. #2
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Have you gathered statistics on the table?

    analyze table mpi compute statistics for all indexes;

    Find more info about it here..

    www.billmagee.co.uk/oracle/sqltune/index.html

    Hth
    Bill

  3. #3
    Join Date
    Aug 2003
    Location
    SW Ohio
    Posts
    198
    Originally posted by billm
    Have you gathered statistics on the table?

    analyze table mpi compute statistics for all indexes;

    Find more info about it here..

    www.billmagee.co.uk/oracle/sqltune/index.html

    Hth
    Bill
    One that also will help with speed and tuning is a script something similar to this one.

    -----------------------------
    SET ECHO ON
    SET TIMING ON
    SPOOL <%path%>\ANALYZE_RESULT.TXT
    CONNECT SYS/******
    EXECUTE DBMS_UTILITY.ANALYZE_schema ('SYS','DELETE');
    EXECUTE DBMS_UTILITY.ANALYZE_schema ('SYSADM','COMPUTE');
    EXECUTE DBMS_UTILITY.ANALYZE_schema ('SYSTEM','COMPUTE');
    EXECUTE DBMS_UTILITY.ANALYZE_schema ('PS','COMPUTE');
    EXECUTE DBMS_UTILITY.ANALYZE_schema ('OUTLN','COMPUTE');
    SPOOL OFF
    -------------------------

    You need to do the analyze for all your major schemas. A significant note in 8i (and possibly 9i) is that analyzing the 'SYS' schema will bring the server to it's knees! Of course I don't know this because my full dumps were taking 14 hours (huh?) until I discovered the little tidbit in Metalink. The other thing is that while the analyze is going on it will take significant resources. We have it scheduled for about 5AM so that it is out of the general work hours.

  4. #4
    Join Date
    Aug 2003
    Posts
    6

    Smile

    hi Bill & jimpen
    Thanks a lot for your response
    i'll do it accordingly
    thanks again
    Rahman

  5. #5
    Join Date
    Jul 2003
    Posts
    6

    Search taking time for fetch ..

    Your table doesen't seem to be very large .. 300000 rows ...
    are there indexes on your table ?
    if any just use the plan_table to see if the index is being used or not ...

    also if u are using CBO try analyzing the table so that the CBO can use statistics generated for it ...

    also see if any indexes created by you need to be rebuild ... because if the deleted entries in the index exceed 30% you may need to rebuild it ..

    Thanks

    Abhijit Kurlekar

  6. #6
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    I not sure of the total size of your table (avg row length), but
    if your query is not using an index and having to do a table scan,
    you may be reading MANY blocks to get your data.

    Your table has datatypes of CHAR instead of VARCHAR2... This
    will hold spaces for the even if only 1 character is stored in a column.

    You also have a nullable column above a column that is NOT NULL.
    This will also make each row longer since Oracle will store a "holder"
    for the not null columns.

    It may be that the row length is longer that an Oracle data block and you are scanning multiple blocks per row.

    Gregg

  7. #7
    Join Date
    Aug 2003
    Posts
    6

    Re: Search taking time for fetch ..

    Originally posted by kurlekar
    Your table doesen't seem to be very large .. 300000 rows ...
    are there indexes on your table ?
    if any just use the plan_table to see if the index is being used or not ...

    also if u are using CBO try analyzing the table so that the CBO can use statistics generated for it ...

    also see if any indexes created by you need to be rebuild ... because if the deleted entries in the index exceed 30% you may need to rebuild it ..

    Thanks

    Abhijit Kurlekar
    hi
    thanks to all for your help
    plz tell me what is CBO & its function & how can i use it
    any help warmly apreciated
    rahman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •