Slow query on 1 million record table

[sxs1]

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.

SELECT PR.PAT_SEQ,
       Q1.PROBLEM_DESCRIPTION,
       
FROM USR_TBL_SBART_B_PAT_RESULTS PR,  
(SELECT P.PAT_SEQ,
 HVS.PROBLEM_DESCRIPTION
  FROM hhs_problem_aud_vw HVS,
  USR_TBL_SBART_B_PATIENTS P
WHERE HVS.CPI_SEQ = P.CPI_SEQ ) Q1 
WHERE Q1.PAT_SEQ(+) = PR.PAT_SEQ GROUP BY PR.PAT_SEQ,
       Q1.PROBLEM_DESCRIPTION,
      ;

[magicwand]

sxs,

if you want help, post the execution plan and the table- and index structures

[sxs1]

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.

[magicwand]

Sorry, I took it seriously.
Didn't realize you were just kidding ...

[sxs1]

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.

[magicwand]

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).

[sxs1]

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.

[magicwand]

Last try:

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

there type:

sqlplus <your_usernam e>/<your_password> @<your_connect_ string> @sxs.sql

and attach the file sxs_output.out to your next post.

[sxs1]

Thank You. I got my issue resolved.