Solved

How to Improve Oracle View Performance?

Posted on 2006-06-20
25
4,934 Views
Last Modified: 2008-02-26
We have an application written by a now defunc company a few years back. It uses a few synonyms which are views from another schema.

The problem is the speed. It takes almost 20 secs to get some values out of these views.

I know views can make use of indexes on the base tables, but how do I construct the indexes on the base tables then? Would anybody be able to do that for the examples I posted below? That's where I got stuck

I am wondering if there is anyway to improve the speed? It's in Oracle 8.1.6


-------- View 1 -------
SELECT job_outputs.company_code,
       job_outputs.factory,
       job_outputs.job_number,
       job_outputs.process_stage,
       job_outputs.process_spec,
       job_outputs.warehouse,
       job_outputs.part_code,
       job_outputs.output_unit,
       job_outputs.temp_count_unit,
       jobs.description
FROM   jobs,
       job_outputs
WHERE  job_outputs.company_code = jobs.company_code
       AND job_outputs.factory = jobs.factory
       AND job_outputs.job_number = jobs.job_number
       AND job_outputs.destination_type = 'I'
       AND jobs.status_flag = 'R'



-------View 2 -------
SELECT jobs.company_code,
       jobs.factory,
       jobs.job_number,
       jobs.description,
       job_stage.process_stage,
       jobs.std_process_spec,
       job_stage_lines.source_type,
       job_stage_lines.pm_close_line
FROM   jobs,
       job_stage,
       job_stage_lines
WHERE  Rtrim(jobs.status_flag,
             ' ') = 'R'
       AND jobs.company_code = job_stage.company_code
       AND jobs.factory = job_stage.factory
       AND jobs.job_number = job_stage.job_number
       AND jobs.company_code = job_stage_lines.company_code
       AND jobs.factory = job_stage_lines.factory
       AND jobs.job_number = job_stage_lines.job_number
0
Comment
Question by:PaperTiger
  • 12
  • 8
  • 2
  • +2
25 Comments
 
LVL 47

Expert Comment

by:schwertner
ID: 16943100
The simpliest way is to create indexes on the join and 'where' conditions of the select.
Only  Rtrim(jobs.status_flag,
             ' ') = 'R'
is complex because in this case you have to create a function based index.
It is very important to execute the statistics (see the question below for how do)
in order to allow the Cost based Optimizer to chose a good execution plan.
0
 
LVL 8

Author Comment

by:PaperTiger
ID: 16943134
which question below?
0
 
LVL 7

Expert Comment

by:ExpertAdmin
ID: 16944183
I am not sure if using JOIN syntax (instead of WHERE syntax) to join your tables would help, but try this one and see if your time improves:



SELECT job_outputs.company_code,
       job_outputs.factory,
       job_outputs.job_number,
       job_outputs.process_stage,
       job_outputs.process_spec,
       job_outputs.warehouse,
       job_outputs.part_code,
       job_outputs.output_unit,
        job_outputs.temp_count_unit,
       jobs.description
FROM   jobs
           INNER JOIN  job_outputs
        ON job_outputs.company_code = jobs.company_code
       AND job_outputs.factory = jobs.factory
       AND job_outputs.job_number = jobs.job_number
       AND job_outputs.destination_type = 'I'
       AND jobs.status_flag = 'R'

Also, creating indexes on the following columns will help:

jobs.company_code
jobs.job_number
 jobs.factory


job_outputs.company_code
job_outputs.factory
job_outputs.job_number


CREATE INDEX idx_jobs_company_codes ON jobs(company_code);
CREATE INDEX idx_jobs_job_number ON jobs(job_number);
CREATE INDEX idx_jobs_factory ON jobs(factory);


CREATE INDEX idx_job_outputs_company_codes ON job_outputs(company_code);
CREATE INDEX idx_job_outputs_job_number ON job_outputs(job_number);
CREATE INDEX idx_job_outputs_factory ON job_outputs(factory);

M@

0
 
LVL 34

Accepted Solution

by:
Mark Geerlings earned 400 total points
ID: 16944377
For the first view, both the jobs and job_outputs tables should be indexed on job_number.  Whether additional indexes on one or both of those tables will help or not, we cannot determine from the amount of information that you have given us.  Indexes on factory or company_code will not help *IF* the records that match on job_number have the same values for both factory and company_code anyway.  If there are different company_code or factory values though for the same job number, then an individual index on either of those columns, or a composite index on job_number plus one or both of those columns *MAY* help, but that depends on multiple factors.

You could consider an index on job_outputs.destination_type and jobs.status_flag, since values for those are both provided in the "where" clause of the view *BUT* Oracle's b-tree indexes usually do not help much for columns that have only a few distinct values, and they can make performance worse.  In a data warehouse, you could consider a bit-mapped index for these two columns, but don't try a bit-mapped index if your application supports multiple users entering transactions into these tables.

For the second view you certainly want job_number to be indexes in all three tables.  The other join columns have the same issues as the first view, so indexes on them may or may not help depending on how the values are distributed and on how distinct (or not) those values are.

Apart from indexes, are you sure that you have Oracle optimized to use the server's RAM as efficiently as possible?  You may be able to increase the value for db_block_buffers in the init*.ora file and get a significant performance boost, but before you make this change you must monitor the current memory use in the O/S and make sure that the server is not already using virtual memory.

Does the application allow records to be deleted from these tables?  If so, you may benefit from an export, truncate and reload of the tables since there may be a lot of fragmented free space in the tables then.  Or does the application do updates that add signifacnt amounts of data per record?  If so, this may have caused chained rows, and again, an export, truncate and reload of the tables may give a significant performance improvement.
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 16944402
To ExpertAdmin:
I don't think that (ANSI standard) join syntax is supported in Oracle8.1, and if it is, I certainly wouldn't expect that to improve performance, since the the syntax in those views is Oracle's native syntax.
0
 
LVL 7

Expert Comment

by:ExpertAdmin
ID: 16944564
It does support it, and I have seen it improve execution times in some cases. But most Oracle folks don't like it and it probably won't help in this case. I just threw it out there as something to try.

M@
0
 
LVL 8

Author Comment

by:PaperTiger
ID: 16944643
Thank you all for the inputs.

1. index should be on each individual value or should I combine them?

For example, in the first view, should I index job_number, company_code etc. individually or put all of them in one index?

2. The company_code, factory, job_number belong to one combo primary key and already indexed.

0
 
LVL 8

Author Comment

by:PaperTiger
ID: 16944843
I indexed job_number, status_flag, destination_type and factory individually, but it didn't seem to help anything.
0
 
LVL 16

Expert Comment

by:MohanKNair
ID: 16944853
Get the output of EXPLAIN PLAN for both queries. It is possible to see which tables are doing FULL TABLE SCAN.

Analyze the explain plan output and then create appropriate  index.
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 16945350
"index should be on each individual value or should I combine them?"

That depends.  Are there other views, queries or reports that retrieve information from these tables based on company_code or factory, but not job_number?  If so, they should be separate indexes.  If not, then a composite index starting with job_number is best, as long as the queries or reports always provide a job_number.

If you already have a composite index that includes these three columns, then you most likely won't benefit from additional indexes on just one or two of these columns.

Did you see my other questions from almost three hours ago about optimizing Oracle's use of your server's RAM and about deletes or updates in the application?
0
 
LVL 8

Author Comment

by:PaperTiger
ID: 16945618
How do I use the EXPLAIN PLAN?

Yes, I have seen the other question. I already run some performance tunning scripts. As per the scripts, I do not need to increase the db_block_buffer size.

SQL-Statement #3

.   The Hit Ratio should be over 90% with the Star_Base Application...
.   If it is not than do the following...

.   Increase the "INIT" parameters "DB_BLOCK_BUFFERS"


NAME                                                                  VALUE    
---------------------------------------------------------------- ----------    
db block gets                                                       7526776    
consistent gets                                                  3850251392    
physical reads                                                     41624512    

.   Hit Ratio = 1 - (physical reads / (db block gets + consistent gets))

 Hit Ratio                                                                      
----------                                                                      
    98.921              
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 16945943
This hit ratio by itself is meaningless!  You also have to know what your server's CPU and memory utilization rates are.  It is possible to set your Oracle db_block_buffers high enough to guarantee a 90% (or higher) hit ratio using this formula, but force your server to be terribly slow because it is busy swapping memory out to its paging file on disk, and this I/O can be worse than the I/O that Oracle would do with a smaller db_block_buffer setting.

The other big factor that affects hit ration is how busy (or not) the database has been lately with other users and/or other reports/queries.  If this is a test system that no one else is using, you may get high hit ratios, if you run this report multiple times, and it is the only report you run.  But if this is a busy production system that you are sharing with 50 or more other users, all doing a variety of things involving a variety of tables, it is less likely that you will see a high hit ratio.

How do you use the EXPLAIN PLAN?
That explains the access path that Oracle used to answer the SQL question that it was asked.  It can be used to identify the problem parts of the SQL statement.  The best plans have "index scans" and "table access by rowid".  If you see "full table scans", those are generally bad, unless the table(s) are small, or if the query is retrieving more than 15% of the rows in the table anyway.

If you are not familiar with reading the EXPLAIN PLAN output, can you post it here?  That may help us make some suggestions.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 16

Assisted Solution

by:MohanKNair
MohanKNair earned 100 total points
ID: 16948308
For getting Explain Plan Output
---------------------------------

1) Create PLAN TABLE
create table PLAN_TABLE (
      statement_id       varchar2(30),
      timestamp          date,
      remarks            varchar2(80),
      operation          varchar2(30),
      options             varchar2(255),
      object_node        varchar2(128),
      object_owner       varchar2(30),
      object_name        varchar2(30),
      object_instance numeric,
      object_type     varchar2(30),
      optimizer       varchar2(255),
      search_columns  number,
      id            numeric,
      parent_id      numeric,
      position      numeric,
      cost            numeric,
      cardinality      numeric,
      bytes            numeric,
      other_tag       varchar2(255),
      partition_start varchar2(255),
        partition_stop  varchar2(255),
        partition_id    numeric,
      other            long,
      distribution    varchar2(30),
      cpu_cost      numeric,
      io_cost            numeric,
      temp_space      numeric,
        access_predicates varchar2(4000),
        filter_predicates varchar2(4000));

2) Run Explain Plan
This statement will populate plan_table. The PK will be statement_id. For the given example statement_id='1'. Before taking explain plan delete records with statement_id='1'

SQL> delete plan_table where statement_id='1';
SQL> explain plan set statement_id='1' for SELECT job_outputs.company_code,
       job_outputs.factory,
       job_outputs.job_number,
       job_outputs.process_stage,
       job_outputs.process_spec,
       job_outputs.warehouse,
       job_outputs.part_code,
       job_outputs.output_unit,
       job_outputs.temp_count_unit,
       jobs.description
FROM jobs,job_outputs
WHERE  
              job_outputs.company_code = jobs.company_code
       AND job_outputs.factory = jobs.factory
       AND job_outputs.job_number = jobs.job_number
       AND job_outputs.destination_type = 'I'
       AND jobs.status_flag = 'R';

3) Get formatted output
SQL> set linesize 120
SQL> SELECT LPAD(' ',2*(LEVEL-1))||operation||'    '||options||'    '||object_name||'    '||position
    FROM plan_table
    START WITH id = 0 AND statement_id = '1'
    CONNECT BY PRIOR id = parent_id AND
    statement_id = '1';



0
 
LVL 8

Author Comment

by:PaperTiger
ID: 16951050
Here's the Explain Plan results

view 1

LPAD('',2*(LEVEL-1))||OPERATION||''||OPTIONS||''||OBJECT_NAME||''||POSITION
--------------------------------------------------------------------------------
----------------------------------------
SELECT STATEMENT
  NESTED LOOPS            1
    TABLE ACCESS    FULL    JOB_OUTPUTS    1
    TABLE ACCESS    BY INDEX ROWID    JOBS    2
      INDEX    UNIQUE SCAN    JOBS_2    1

****************************************************
LPAD('',2*(LEVEL-1))||OPERATION||''||OPTIONS||''||OBJECT_NAME||''||POSITION
--------------------------------------------------------------------------------
----------------------------------------
SELECT STATEMENT
  NESTED LOOPS            1
    NESTED LOOPS            1
      TABLE ACCESS    FULL    JOB_STAGE_LINES    1
      TABLE ACCESS    BY INDEX ROWID    JOBS    2
        INDEX    UNIQUE SCAN    JOBS    1
    TABLE ACCESS    BY INDEX ROWID    JOB_STAGE    2
      INDEX    RANGE SCAN    IP_JOB_STAGE_1    1

8 rows selected.
0
 
LVL 8

Author Comment

by:PaperTiger
ID: 16951117
Sorry, my mistake. Ran the previous one on my TEST server.

View 1

LPAD('',2*(LEVEL-1))||OPERATION||''||OPTIONS||''||OBJECT_NAME||''||POSITION
--------------------------------------------------------------------------------
SELECT STATEMENT
  NESTED LOOPS            1

    TABLE ACCESS    BY INDEX ROWID    JOB_OUTPUTS    1
      INDEX    RANGE SCAN IP_ADC_JOB_OUTPUTS_DESTIN    1
    TABLE ACCESS    BY INDEX ROWID    JOBS    2
      INDEX    UNIQUE SCAN    JOBS_2    1

6 rows selected.

***************************************************
View 2

LPAD('',2*(LEVEL-1))||OPERATION||''||OPTIONS||''||OBJECT_NAME||''||POSITION
---------------------------------------------------------------------------
SELECT STATEMENT
  NESTED LOOPS            1
    NESTED LOOPS            1
      TABLE ACCESS    FULL    JOB_STAGE_LINES    1
      TABLE ACCESS    BY INDEX ROWID    JOBS    2
        INDEX    UNIQUE SCAN    JOBS    1
    TABLE ACCESS    BY INDEX ROWID    JOB_STAGE    2
      INDEX    RANGE SCAN    IP_JOB_STAGE_1    1
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 16951122
This kind of line:
"TABLE ACCESS    FULL  ..."
is what you don't want to see in EXPLAIN PLAN outputs when the table is a large table (in this case, JOB_OUTPUTS, which I'm guessing is a large table in your database).

Sometimes that cannot be avoided though if the "where" clause does not provide an indexed value to narrow the search.

You still haven't responsed to my questions about the size of your Oracle SGA compared to the amount of RAM in the server, and about whether the application allows deletes or does updates that may cause chained rows.  Have you analyzed the tables to check for chained rows or data blocks with lots of free space?
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 16951164
OK, now the EXPLAIN PLAN looks better for view1, but there still could be performance problems with it due to: SGA size, chained rows or lightly-filled data blocks.

For views2, it is starting with a full-table scan of JOB_STAGE_LINES.  Maybe based on the values provided in the "where" clause, that is the best (or least worst) option available.  Does your query from view2 provide values for columns in JOB_STAGE_LINES that are not indexed?
0
 
LVL 8

Author Comment

by:PaperTiger
ID: 16951192
shared Pool: 308 MB
Buffer Cache: 976 MB
Large Pool: 600 KB
Sort Area Size: 1024 KB
Java Pool: 32 KB (we don't use Java)
Total SGA: 1285.65 MB

I have 3GB memory and Oracle is using 1.4GB. I still have 1.2 left.
0
 
LVL 8

Author Comment

by:PaperTiger
ID: 16951230
In View 2, factory, company_code and some other parameters unlisted here are in a combo index. Nothing is individually indexed except for Job_Number.



WHERE  Rtrim(jobs.status_flag,
             ' ') = 'R'
       AND jobs.company_code = job_stage.company_code
       AND jobs.factory = job_stage.factory
       AND jobs.job_number = job_stage.job_number
       AND jobs.company_code = job_stage_lines.company_code
       AND jobs.factory = job_stage_lines.factory
       AND jobs.job_number = job_stage_lines.job_number
0
 
LVL 8

Author Comment

by:PaperTiger
ID: 16951240
markgeer, would you mind giving me you email address?
0
 
LVL 8

Author Comment

by:PaperTiger
ID: 16951261
job_stage_lines has 180652 lines of records
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 16951271
With under 4GB of RAM , if your server O/S is Windows, that may be be about the best you can do in terms of tuning the SGA (with the Oracle SGA being close to 50% of the total RAM, and the buffer cache being 2/3 of the SGA).  That assumes that this server is dedicated to Oracle, that you use (the default) dedicated connections for users (not Oracle's shared server configuration) and that you typically have multiple user connections to support.
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 16951308
I don't normally give out my e-mail address and I have to leave now for a meeting for an hour.  I may be out most of the rest of the day, depending on how this meeting goes.  If you post your e-mail address here, I can contact you when I get a chance.
0
 
LVL 8

Author Comment

by:PaperTiger
ID: 16951374
Yes, it's a dedicated Windows 2000 server for Oracle.

I can add another GB RAM if you think it's needed.

my email is chinabee at yahoo period com.
0
 
LVL 8

Author Comment

by:PaperTiger
ID: 16959671
It is my great pleasure to be here and getting help from you guys.

Thank you so much.

The final solution was to do an analyze on job_stage_lines table with 2000 rows, which then turned a 1 minute work into a few seconds. (markgeer has provided this answer).

0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now