By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,688 Members | 1,233 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,688 IT Pros & Developers. It's quick & easy.

Slow query on 1 million record table

P: 5
I have performance issue with the query which takes 5-10 mins and has table has very less number of records something arround a million records.I need to insert the filtered data into a global temp table.

Expand|Select|Wrap|Line Numbers
  1. SELECT PR.PAT_SEQ,
  2.        Q1.PROBLEM_DESCRIPTION,
  3.  
  4. FROM USR_TBL_SBART_B_PAT_RESULTS PR,  
  5. (SELECT P.PAT_SEQ,
  6.  HVS.PROBLEM_DESCRIPTION
  7.   FROM hhs_problem_aud_vw HVS,
  8.   USR_TBL_SBART_B_PATIENTS P
  9. WHERE HVS.CPI_SEQ = P.CPI_SEQ ) Q1 
  10. WHERE Q1.PAT_SEQ(+) = PR.PAT_SEQ GROUP BY PR.PAT_SEQ,
  11.        Q1.PROBLEM_DESCRIPTION,
  12.       ;
Apr 5 '10 #1
Share this Question
Share on Google+
8 Replies


P: 41
sxs,

if you want help, post the execution plan and the table- and index structures
Apr 5 '10 #2

P: 5
The overall cost is just 111 and hhs_problem_aud_vw HVS is a view which has index on cpi_seq but it does not use the index..and the other 2 are global temperory tables which hardly has any data of some 1000 records.
Apr 5 '10 #3

P: 41
Sorry, I took it seriously.
Didn't realize you were just kidding ...
Apr 5 '10 #4

P: 5
Its true that the cost is only 111 and time taken for running the query is 10 mins..and i am using a view to fetch the data. hhs_problem_aud_vw is a view.
Apr 6 '10 #5

P: 41
O.K.,

cost is only 111
Even if the costs were true (which they are mostly not, because people very rarely run system- and dictionary statistics and only run database- and schema statistics):
Cost is a RELATIVE number that doesn't make any sense without comparing it to the cost of another query.
(I've seen queries with Oracle Spatial that showed costs of 48 and took 3 days on a 32 processor machine)

from your former posting:
hhs_problem_aud_vw HVS is a view which has index on cpi_seq
Views don't hold data and therefore can't have indexes (unless they are materialized views, which is something completly different).

In addition:

- you didn't post the Oracle version you are running on
- you didn't post the table structures and datatypes of the columns
- you didn't post the indexes on the underlying tables
- you didn't post which optimizer mode you are using
- you didn't post when the tables and indexes where analyzed the last time
and most important:
- you refuse to post the execution plan

You are just saying:
"My car uses just at a tenth of it's horsepower. But don't worry, the fuel gauge shows its half full. Please advise"

And, when asked to describe the sound the car makes, you say:
"Believe me, the fuel gauge really shows that it's half full. But it doesn't accelerate"

Unfortunately there is no parameter TUNE_QUERY which you can set to ON.

Performance tuning is hard work sometimes, and to succeed you need all information about a system you can get (If you were my customer, I would be very patient for my 1.800 Euro per day, though).
Apr 6 '10 #6

P: 5
I am using Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 .and the optimizer is CBO.In the last post when i mentioned indexes i meant to say that the underlying table has indexes on CPI_SEQ and CPI_PROBLEM_SEQ.Please find the Explain Plan Attached.
Attached Files
File Type: txt EX-PLAN.txt (1.7 KB, 422 views)
Apr 6 '10 #7

P: 41
Last try:

Save the attached file as sxs.sql and open a line mode window for your operating system.

there type:

sqlplus <your_username>/<your_password>@<your_connect_string> @sxs.sql

and attach the file sxs_output.out to your next post.
Attached Files
File Type: txt sxs.txt (1.7 KB, 562 views)
Apr 8 '10 #8

P: 5
Thank You. I got my issue resolved.
Apr 9 '10 #9

Post your reply

Sign in to post your reply or Sign up for a free account.