How do we optimize oracle database ?

Question

Hello,

We have a Oracle 9i Database with 7 tables each with 15+ Million records. There is no relation between the table i.e. no foreign keys.

Here is an example of one of the tables

CREATE TABLE GSS.SHOWPD
(
  INSERVID          VARCHAR2(7 CHAR)            NOT NULL,
  CAGEPOS           VARCHAR2(8 CHAR)            DEFAULT NULL,
  DETAILEDSTATE     VARCHAR2(100 CHAR)          DEFAULT NULL,
  FAILEDMB          NUMBER                      DEFAULT NULL,
  FREECHUNK         NUMBER                      DEFAULT NULL,
  FREEMB            NUMBER                      DEFAULT NULL,
  FWREV             VARCHAR2(100 CHAR)          DEFAULT NULL,
  FWSTATUS          VARCHAR2(100 CHAR)          DEFAULT NULL,
  AID               NUMBER                      DEFAULT NULL,
  LDA               VARCHAR2(100 CHAR)          DEFAULT NULL,
  MANUF             VARCHAR2(100 CHAR)          DEFAULT NULL,
  AMODEL            VARCHAR2(4000 CHAR)         DEFAULT NULL,
  NODEWWN           VARCHAR2(64 CHAR)           DEFAULT NULL,
  NRMUNUSEDFAIL     VARCHAR2(100 CHAR)          DEFAULT NULL,
  NRMUNUSEDFREE     VARCHAR2(100 CHAR)          DEFAULT NULL,
  NRMUNUSEDUNAVAIL  VARCHAR2(100 CHAR)          DEFAULT NULL,
  NRMUSEDFAIL       VARCHAR2(100 CHAR)          DEFAULT NULL,
  NRMUSEDOK         VARCHAR2(100 CHAR)          DEFAULT NULL,
  AORDER            VARCHAR2(100 CHAR)          DEFAULT NULL,
  PATHA0            VARCHAR2(100 CHAR)          DEFAULT NULL,
  PATHA1            VARCHAR2(100 CHAR)          DEFAULT NULL,
  PATHB0            VARCHAR2(100 CHAR)          DEFAULT NULL,
  PATHB1            VARCHAR2(100 CHAR)          DEFAULT NULL,
  PORTA0            VARCHAR2(100 CHAR)          DEFAULT NULL,
  PORTA1            VARCHAR2(100 CHAR)          DEFAULT NULL,
  PORTB0            VARCHAR2(100 CHAR)          DEFAULT NULL,
  PORTB1            VARCHAR2(100 CHAR)          DEFAULT NULL,
  RDCERR            VARCHAR2(100 CHAR)          DEFAULT NULL,
  REUERR            VARCHAR2(100 CHAR)          DEFAULT NULL,
  SERIAL            VARCHAR2(100 CHAR)          DEFAULT NULL,
  SIZEMB            NUMBER                      DEFAULT NULL,
  SPARECHUNK        VARCHAR2(100 CHAR)          DEFAULT NULL,
  SPAREMB           NUMBER                      DEFAULT NULL,
  SPEEDKRPM         VARCHAR2(100 CHAR)          DEFAULT NULL,
  SPRUNUSEDFAIL     VARCHAR2(100 CHAR)          DEFAULT NULL,
  SPRUNUSEDFREE     VARCHAR2(100 CHAR)          DEFAULT NULL,
  SPRUNUSEDUNINIT   VARCHAR2(100 CHAR)          DEFAULT NULL,
  SPRUSEDFAIL       VARCHAR2(100 CHAR)          DEFAULT NULL,
  SPRUNUSEDOK       VARCHAR2(100 CHAR)          DEFAULT NULL,
  STATE             VARCHAR2(100 CHAR)          DEFAULT NULL,
  TEMPDEGC          NUMBER                      DEFAULT NULL,
  TOTALCHUNK        VARCHAR2(100 CHAR)          DEFAULT NULL,
  ATYPE             VARCHAR2(100 CHAR)          DEFAULT NULL,
  UNAVAILABLEMB     NUMBER                      DEFAULT NULL,
  VOLUMEMB          NUMBER                      DEFAULT NULL,
  WRCERR            VARCHAR2(100 CHAR)          DEFAULT NULL,
  WRUERR            VARCHAR2(100 CHAR)          DEFAULT NULL,
  COMMANDTIMESTAMP  TIMESTAMP(6)                DEFAULT NULL  NOT NULL,
  FETCHTIMESTAMP    TIMESTAMP(6)                DEFAULT NULL  NOT NULL
)

Note that INSERVID can be one of the 1400 types. So it's possible to have say 1400 tables each dedicated to one inserv. Would that be insane thing to do ? I wonder.

We iterate over the no of inservs and run all our queries against them. Right now we are purging data so we don't expect to overshoot 15+ million records.

1) We have distinct in the queries so it takes full table scan. Oracle Execution plan shows we are doing quite full table scan.

select distinct(inservid),commandtimestamp from statpd order by commandtimestamp desc;

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |   665 | 13300 | 79488 |
|   1 |  SORT UNIQUE         |             |   665 | 13300 | 69088 |
|   2 |   TABLE ACCESS FULL  | STATPD      |  4128K|    78M| 19406 |
--------------------------------------------------------------------
Note: cpu costing is off
10 rows selected.

2) Every table has an index on inserv id. It has helped in reducing the cost of queries, what can we do further ? Any tips/tricks to make things faster ?

3) Does it make sense if we make our sql queries that run against each table as parallel scripts running against each table. Would this result in faster completion ?

4) We have enough RAM to load the whole DB in memory. Is it possible to do that with ORACLE.

Thanks in Advance

Answer 1

1-2) Having an index on (inservid,commandtimestamp) will replace the FULL TABLE SCAN with a (FAST) FULL INDEX SCAN since one of the two columns is NOT NULL (hence the DB can use the index instead of the table). This should be faster than the full table scan but all 15+ M rows index entries will be read.

You could get a faster response time with a precomputed table (for example a Fast Refresh Materialized View), in that case you will probably take a performance hit on DML operations on the table (insert/update/delete will be slower)

3) Having the queries run in parallel will give you some benefit if you have some IO bandwidth left. Right now your full table scan will probably read a lot of rows from the disks. Unless your tables reside on physically separated devices, the gain by going parallel will be minimal.

4) loading in RAM is mainly done automatically in Oracle: i.e. in most case Oracle does a good job in placing frequently accessed data in memory.

Answer 2

Part 4, this is usually not nececary. In 9i you can setup a special "keep" buffer pool to try to keep your indexes in memory. (but since there is no support avaliable from Oracle now, it might not be a good time to start experimenting with features you have not used so far)

Are your clients on the local machine, if not the client may benefit from the 11g feature Client (side" result cache, to minimize database server round trips.

Excerpt from Oracle® Database Performance Tuning Guide 11g Release 2 (11.2) Part Number E16638-03

7.6.2.1 How the Client Result Cache Works The client result cache stores the results of the outermost query, which are the columns defined by the OCI application. Subqueries and query blocks are not cached.

Figure 7-4 shows a client process with a database login session. This client process has one client result cache shared among multiple application sessions running in the client process. If the first application session runs a query, then it retrieves rows from the database and caches them in the client result cache. If other application sessions run the same query, then they also retrieve rows from the client result cache.

Answer 3

Hi Ankur! by 1),4) i agree with Vincent.

by 2) you have a long rows - so it may be good to have a bigger db_block_size - 16Kb or 32Kb and it may be worth to try compress on your table. also check your db_file_multiblock_read_count. may be partiton it - distribute it on as many drives as you have.

3) you can play with parallel hint to see how it would be.

Answer 4

Your data model is broken. Can you provide more detail on the queries you run? Adding indexes will only help you to a point.