How to Improve Oracle View Performance?
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_uni
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_ty
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_typ
job_stage_lines.pm_close_l
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_co
AND jobs.factory = job_stage_lines.factory
AND jobs.job_number = job_stage_lines.job_number
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_uni
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_ty
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_typ
job_stage_lines.pm_close_l
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_co
AND jobs.factory = job_stage_lines.factory
AND jobs.job_number = job_stage_lines.job_number
ASKER
which question below?
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_uni
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_ty
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_co
CREATE INDEX idx_job_outputs_job_number
CREATE INDEX idx_job_outputs_factory ON job_outputs(factory);
M@
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_uni
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_ty
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_co
CREATE INDEX idx_job_outputs_job_number
CREATE INDEX idx_job_outputs_factory ON job_outputs(factory);
M@
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
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@
M@
ASKER
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.
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.
ASKER
I indexed job_number, status_flag, destination_type and factory individually, but it didn't seem to help anything.
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.
Analyze the explain plan output and then create appropriate index.
"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?
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?
ASKER
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
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
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Here's the Explain Plan results
view 1
LPAD('',2*(LEVEL-1))||OPER
--------------------------
--------------------------
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))||OPER
--------------------------
--------------------------
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.
view 1
LPAD('',2*(LEVEL-1))||OPER
--------------------------
--------------------------
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))||OPER
--------------------------
--------------------------
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.
ASKER
Sorry, my mistake. Ran the previous one on my TEST server.
View 1
LPAD('',2*(LEVEL-1))||OPER
--------------------------
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))||OPER
--------------------------
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
View 1
LPAD('',2*(LEVEL-1))||OPER
--------------------------
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))||OPER
--------------------------
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
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?
"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?
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?
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?
ASKER
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.
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.
ASKER
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_co
AND jobs.factory = job_stage_lines.factory
AND jobs.job_number = job_stage_lines.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_co
AND jobs.factory = job_stage_lines.factory
AND jobs.job_number = job_stage_lines.job_number
ASKER
markgeer, would you mind giving me you email address?
ASKER
job_stage_lines has 180652 lines of records
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.
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.
ASKER
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.
I can add another GB RAM if you think it's needed.
my email is chinabee at yahoo period com.
ASKER
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).
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).
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.