Performance Issue

Yes survey _id is indexed in both the tables. Survey_id is foregn key. Survey_id is indexed in base table and foregn table

Can you provide the explain plan of the query?

You might need to EXECUTE DBMS_STATS FOR responses and submissions tables

Also provide the information
select index_name, column_name, position from user_ind_columns where table_name='RESPONSE';

This output is from dev database

SQL> set autotrace traceonly explain

SQL> SELECT QUESTION_ID, OPTION_ID, COUNT (OPTION_ID) COUNT FROM RESPONSES WHERE SURVEY_ID = 200 GROUP BY QUESTION_ID, OPTION_ID ORDER BY QUESTION_ID ;

Execution Plan
----------------------------------------------------------
Plan hash value: 1063528983

--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     2 |    20 |     4  (25)| 00:00:01 |
|   1 |  SORT GROUP BY     |           |     2 |    20 |     4  (25)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| RESPONSES |    10 |   100 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("SURVEY_ID"=200)

SQL> select SURVEY_ID, count(*) from RESPONSES group by SURVEY_ID;

Execution Plan
----------------------------------------------------------
Plan hash value: 3565624577

--------------------------------------------------------------------------------
-------

| Id  | Operation            | Name           | Rows  | Bytes | Cost (%CPU)| Tim
e     |

--------------------------------------------------------------------------------
-------

|   0 | SELECT STATEMENT     |                |     1 |     3 |     1   (0)| 00:
00:01 |

|   1 |  SORT GROUP BY NOSORT|                |     1 |     3 |     1   (0)| 00:
00:01 |

|   2 |   INDEX FULL SCAN    | SURVEY_ID_RESP |    10 |    30 |     1   (0)| 00:
00:01 |

--------------------------------------------------------------------------------
-------


SQL> select ini_trans from user_tables where table_name='RESPONSES'
  2  ;

Execution Plan
----------------------------------------------------------
Plan hash value: 1430774513

--------------------------------------------------------------------------------
----------------------

| Id  | Operation                           | Name           | Rows  | Bytes | C
ost (%CPU)| Time     |

--------------------------------------------------------------------------------
----------------------

|   0 | SELECT STATEMENT                    |                |     1 |   162 |
   8  (13)| 00:00:01 |

|   1 |  NESTED LOOPS OUTER                 |                |     1 |   162 |
   8  (13)| 00:00:01 |

|   2 |   NESTED LOOPS OUTER                |                |     1 |   158 |
   7  (15)| 00:00:01 |

|   3 |    NESTED LOOPS OUTER               |                |     1 |   150 |
   6  (17)| 00:00:01 |

|   4 |     NESTED LOOPS OUTER              |                |     1 |   145 |
   6  (17)| 00:00:01 |

|   5 |      NESTED LOOPS                   |                |     1 |   134 |
   5  (20)| 00:00:01 |

|   6 |       NESTED LOOPS                  |                |     1 |   131 |
   4  (25)| 00:00:01 |

|   7 |        MERGE JOIN CARTESIAN         |                |     1 |    98 |
   3  (34)| 00:00:01 |

|*  8 |         HASH JOIN                   |                |     1 |    68 |
   1 (100)| 00:00:01 |

|*  9 |          FIXED TABLE FULL           | X$KSPPI        |     1 |    55 |
   0   (0)| 00:00:01 |

|  10 |          FIXED TABLE FULL           | X$KSPPCV       |   100 |  1300 |
   0   (0)| 00:00:01 |

|  11 |         BUFFER SORT                 |                |     1 |    30 |
   3  (34)| 00:00:01 |

|* 12 |          TABLE ACCESS BY INDEX ROWID| OBJ$           |     1 |    30 |
   2   (0)| 00:00:01 |

|* 13 |           INDEX RANGE SCAN          | I_OBJ2         |     1 |       |
   1   (0)| 00:00:01 |

|* 14 |        TABLE ACCESS CLUSTER         | TAB$           |     1 |    33 |
   1   (0)| 00:00:01 |

|* 15 |         INDEX UNIQUE SCAN           | I_OBJ#         |     1 |       |
   0   (0)| 00:00:01 |

|  16 |       TABLE ACCESS CLUSTER          | TS$            |     1 |     3 |
   1   (0)| 00:00:01 |

|* 17 |        INDEX UNIQUE SCAN            | I_TS#          |     1 |       |
   0   (0)| 00:00:01 |

|  18 |      TABLE ACCESS CLUSTER           | SEG$           |     1 |    11 |
   1   (0)| 00:00:01 |

|* 19 |       INDEX UNIQUE SCAN             | I_FILE#_BLOCK# |     1 |       |
   0   (0)| 00:00:01 |

|* 20 |     INDEX UNIQUE SCAN               | I_OBJ1         |     1 |     5 |
   0   (0)| 00:00:01 |

|  21 |    TABLE ACCESS BY INDEX ROWID      | OBJ$           |     1 |     8 |
   1   (0)| 00:00:01 |

|* 22 |     INDEX UNIQUE SCAN               | I_OBJ1         |     1 |       |
   0   (0)| 00:00:01 |

|  23 |   TABLE ACCESS CLUSTER              | USER$          |     1 |     4 |
   1   (0)| 00:00:01 |

|* 24 |    INDEX UNIQUE SCAN                | I_USER#        |     1 |       |
   0   (0)| 00:00:01 |

--------------------------------------------------------------------------------
----------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   8 - access("KSPPI"."INDX"="KSPPCV"."INDX")
   9 - filter("KSPPI"."KSPPINM"='_dml_monitoring_enabled')
  12 - filter(BITAND("O"."FLAGS",128)=0)
  13 - access("O"."OWNER#"=USERENV('SCHEMAID') AND "O"."NAME"='RESPONSES')
  14 - filter(BITAND("T"."PROPERTY",1)=0)
  15 - access("O"."OBJ#"="T"."OBJ#")
  17 - access("T"."TS#"="TS"."TS#")
  19 - access("T"."TS#"="S"."TS#"(+) AND "T"."FILE#"="S"."FILE#"(+) AND
              "T"."BLOCK#"="S"."BLOCK#"(+))
  20 - access("T"."BOBJ#"="CO"."OBJ#"(+))
  22 - access("T"."DATAOBJ#"="CX"."OBJ#"(+))
  24 - access("CX"."OWNER#"="CU"."USER#"(+))

The explain plan indicates there is FULL TABLE scan on RESPONSES.

Try gathering statistics on RESPONSES by following query

<USER> should be the schema name

exec dbms_stats.gather_table_stats(<USER>, 'RESPONSES');

can you provide the output of
set autotrace off
select ini_trans from user_tables where table_name='RESPONSES';

select index_name, column_name, position where table_name='RESPONSE';

SQL> set autotrace off
SQL> select ini_trans from user_tables where table_name='RESPONSES';

 INI_TRANS
----------
         1
 
can you please check second sql syntax

how many rows do you have on those tables?

Its ok to do full tablescan if the table are small.

Do you have a job scheduled to calculate table statistics regularly? (Usually weekly is enough.)
Which version of Oracle do you have?
What is your initialization parameter: OPTIMIZER_MODE set to?

I am sorry here are the queries.

select index_name, column_name, COLUMN_POSITION from user_ind_columns where table_name='RESPONSE';

select count(*) from RESPONSE;

Here is output from last_analyzed. Do you think we still run dbms_stats on this tables??

select table_name,num_rows,avg_row_len, last_analyzed from dba_tables where table_name in ('RESPONSES')

TABLE_NAME     NUM_ROWS    AVG_ROW_LEN          LAST_ANALYZED
RESPONSES      120780         19                            10/15/2009 10:00:05 PM
RESPONSES       0                 0                              10/5/2009  10:01:23 PM
RESPONSES     176680         19                           10/28/2009 10:01:39 PM
RESPONSES     2159746       20                            2/10/2009 10:00:54 PM


TABLE_NAME         NUM_ROWS       AVG_ROW_LEN           LAST_ANALYZED
SUBMISSIONS          120660               22                           10/15/2009 10:00:08 PM
SUBMISSIONS           0                      0                               10/5/2009 10:01:23 PM
SUBMISSIONS        176280                 22                           10/28/2009 10:01:44 PM
SUBMISSIONS       2172426                23                           2/10/2009 10:02:08 PM




OPTIONS_ID_RESP OPTION_ID 1
IDX_SUBMISSION_ID_REP SUBMISSION_ID 1
SURVEY_ID_RESP SURVEY_ID 1
QUESTION_ID_RESP QUESTION_ID 1
SYS_C004910 RESPONSE_ID 1


total records
191957

Four rows returned for each table?  That surprises me!

Can you run that query this way (to include the table owner):

select owner, table_name,num_rows,avg_row_len, last_analyzed from dba_tables where table_name in ('RESPONSES','SUBMISSIONS')

Here you go

OWNER         TABLE_NAME      NUM_ROWS      AVG_ROW_LEN            LAST_ANALYZED
DX_ATLAS         SUBMISSIONS      176280                           22                                    10/28/2009 22:01
DX_ATLAS    RESPONSES      176680                        19                                    10/28/2009 22:01

Why is there only one row for each table now, but four rows earlier?  Do these two tables exist in other schemas in your database also (as the earlier results indicate, with different values for each of the rows)?

Which owner/schema does your applciation use?  Does it always use the same one?  Or, is it possible that some users connect to one schema, but other users connect to different one?

this is correct data and this is the only schema used in production. You can ignore preview one. This is only schema used

Try that first query like this:

SELECT /*+RULE */QUESTION_ID, OPTION_ID, COUNT (OPTION_ID) COUNT FROM RESPONSES WHERE SURVEY_ID = ? GROUP BY QUESTION_ID, OPTION_ID ORDER BY QUESTION_ID

This will instruct Oracle to use the older rule-based optimizer, instead of the (newer, more-complex, but usually better) cost-based optimizer.  The cost-based optimizer requires up-to-date table statistics, but you have those.  Are you sure that the survey_id column is indexed in that table, and that it is either the only or the first column in the index?

Since there are nearly .17 milions record in RESPONSE table, full table scan is not good for Online.
Please provide the output of
select owner, index_name, column_name, COLUMN_POSITION from user_ind_columns where table_name='RESPONSE';

Sorry, there should be space between "*/" and "QUESTION_ID" in that query.

OUTPUT

INDEX_NAME               COLUMN_NAME               COLUMN_POSITION      
OPTIONS_ID_RESP                    OPTION_ID                                1      
IDX_SUBMISSION_ID_REP    SUBMISSION_ID                1      
SURVEY_ID_RESP                   SURVEY_ID                               1      
QUESTION_ID_RESP      QUESTION_ID              1      
SYS_C004910                RESPONSE_ID                             1      

Here is create table script for responses table

CREATE TABLE RESPONSES
(
  RESPONSE_ID    NUMBER(10)                     NOT NULL,
  SURVEY_ID      NUMBER(10)                     NOT NULL,
  SUBMISSION_ID  NUMBER(10)                     NOT NULL,
  QUESTION_ID    NUMBER(10)                     NOT NULL,
  OPTION_ID      NUMBER(10)
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;


CREATE INDEX OPTIONS_ID_RESP ON RESPONSES
(OPTION_ID)
LOGGING
NOPARALLEL;


CREATE INDEX IDX_SUBMISSION_ID_REP ON RESPONSES
(SUBMISSION_ID)
LOGGING
NOPARALLEL;


CREATE INDEX SURVEY_ID_RESP ON RESPONSES
(SURVEY_ID)
LOGGING
NOPARALLEL;


CREATE INDEX QUESTION_ID_RESP ON RESPONSES
(QUESTION_ID)
LOGGING
NOPARALLEL;


CREATE UNIQUE INDEX RESPONSES_PK ON RESPONSES
(RESPONSE_ID)
LOGGING
NOPARALLEL;


ALTER TABLE RESPONSES ADD (
  CONSTRAINT RESPONSES_PK
 PRIMARY KEY
 (RESPONSE_ID));


ALTER TABLE RESPONSES ADD (
  CONSTRAINT QUESTIONS_RESPONSESS_FK
 FOREIGN KEY (QUESTION_ID)
 REFERENCES QUESTIONS (QUESTION_ID));

ALTER TABLE RESPONSES ADD (
  CONSTRAINT OPTIONS_RESPONSES_FK
 FOREIGN KEY (OPTION_ID)
 REFERENCES OPTIONS (OPTION_ID));

ALTER TABLE RESPONSES ADD (
  CONSTRAINT SUBMISSION_RESPONSES_FK4
 FOREIGN KEY (SUBMISSION_ID)
 REFERENCES SUBMISSIONS (SUBMISSION_ID));

ALTER TABLE RESPONSES ADD (
  CONSTRAINT SURVEYS_RESPONSES_FK
 FOREIGN KEY (SURVEY_ID)
 REFERENCES SURVEYS (SURVEY_ID));

OK, that query indicates that you have a single-column index named: "SURVEY_ID_RESP' for the column: "SURVEY_ID" in the RESPONSES table.  For some reason though, the "explain plan" showed a full-table scan.  That is not good.  Did you try the query I suggested with the /*+RULE */ hint?  That way, it should use an index range scan.

No perforemance improvement even with below sql

SELECT /*+RULE */ QUESTION_ID, OPTION_ID, COUNT (OPTION_ID) COUNT FROM RESPONSES WHERE SURVEY_ID = 200 GROUP BY QUESTION_ID, OPTION_ID ORDER BY QUESTION_ID

I have another question. I want to know the highest count for each survery_id. Please provide the following query result. I don't want full output, but first 2 records.

set autotrace on
select survery_id, count(*) from response group by survery_id order by 2 desc;
select survery_id, count(*) from response group by survery_id order by 2 ;

Now we can see whats going on. First query will tell which survey_id has lots of records, and second query will tell least.
Is this query is running inside the loop?
Did you analyzed this table to collect histograms?

If you are using histograms, then possible, there is  one term called Bind Peaking, which may cause to choose wrong plan.
Just try to reanalyze the table without histograms ie by using 'column size 1' then retry executing this query.

Does adding some compound index for these columns improve performance ??

Since you are getting records from one table using one column, in this case, compound index will not help. I am interested in why oracle is not picking right index even if index is present.
There are many reason, why oracle is not picking an index, therefore I want you to provide survery_id with count(*).
Its not true that index will always improve performance. Therefore by calculating count on survery, we can understand why oracle is bypassing index.
secondly if you fire sql in pattern in such a way that query has to do full scan, then its possible next time oracle may not use hint. This feature is called bind peaking and you can do this experiment by creating procedure and trace your session.

If there are lots of records (like 15% of the total, or more) that have the same value for survey_id, and if these records are scattered physically into many different blocks in the table, then possibly a full-table scan is the most efficient path that Oracle can choose.  (The queries that virdi_vs gave you that include "count(*)" will tell us that.)

Pleae find below
select survery_id, count(*) from response group by survery_id order by 2 desc;
 
100;     120780
200;       74805

select survery_id, count(*) from response group by survery_id order by 2 ;

200;        74808
100;         120780

As per data, I believe you have only two survery_id 100 and 200. Using index on any of them will degrade performance because for each row of index, oracle has to scan table, therefore there are nearly 2 IOs involved for each record.
Since you want data in online, you can go for full table scan with DB_FILE_MULTIBLOCK_READ_COUNT=0 before running this query or set appropriate value for this parameter at session or use PARALLEL hint.
Third option is to reorganize your table and create two partitions. Then you can speedup the query by scaning only one partion.
Right now oracle has to scan all blocks.

This quries are called from java code. we have lot of data in this tables which we never deleted.  May this is reason it causing the issue.

If we delete old data from these tables which is no use will fix the issue??

Yes, less data will always give good performance. But if management wants to keep old data for 2 years, then you have to move this data either to some other table or just create another partition and move to that partition.
Syntax for partition scan is
select * from table_name partition(partition_name);
and this statement can be easily control from java code. Based on data, java can rewrite the query to use appropriate partition.

Survey id is for foregn key. You want to delete index in this table or primary table  for survey id??

Deleting foreign key index creates other problem. So do not delete any index, any point of time you may need index. For data consistency, keep PK and FK as well.
Do not change constraints/indexes. My point is first delete unwanted records from Parent and Child tables. if management doesn't allow you to delete, then second option is to reorganize primary/child table with 3 partitions. First for survey_id 100, second for 200 and third for old data. This way you can save Full table scan.
By doing this you all constraints/indexes can be maintained as well.

The survey_id table have only two records 100 and 200.  This is seed data. and table name is survey. So we dont have anyting to delete as it got only seed data

"If we delete old data from these tables which is no use will fix the issue??"
No.

A delete by itself will not improve performance at all.  But, if you then do an "alter table ... move...", or export the remaining data, then truncate the table, then import the data you exported, you will get better performance because those actions will reset the "high water mark" for the table.  That, is the number of blocks that have been used for data.  Simply doing a delete does not ever move this lower, so in a full-table scan, Oracle still has to read all of the blocks up to the high-water mark, even if some of them are now empty.

In 10g we have  shrink command which can do this operation instead of moving the data to duplicate table.

Now you have lots of tuning option for this query.
1. removal of old data, and (truncate or shrink or rebuild)
2. make partion on survey_id
3. use of parallel hint.

If i execute the query from toad in production server the cost is only 150 and query runs very fast. This sql query is coming from java code so we need to make code in java, it involved lot of process and approvals.

Do we have any option to fix at database level apart form truncate or shrink Also can you give some example for partion and parallel hit syntax statement

From belwo output we can see survey_id 100  is having more number of records compare to survey_id 200. Now we are not using this survey_id 100 and this data can be deleted from production. Once we delete this data and cleaning up water mark by running shink command. Does this improve performance of query ??
Pleae find below
select survery_id, count(*) from response group by survery_id order by 2 desc;
 
100;     120780
200;       74805

select survery_id, count(*) from response group by survery_id order by 2 ;

200;        74808
100;         120780

Remember if you are deleting records, it is possible that one of the required record is very near to HWM and therefore to avoide any internal fragmentation you have to rebuild the table using Move or Rebuild command of alter table.

Hey markqeer

Can you please tell me more about trigger approach. So u want trigger in place for inserting or something ??
Thanks

@ vadicherla:

Did you get what mark had said? Just make a copy of your db objects related to that table you're rebuilding. If it has triggers just make a copy of it, so it could help.

Just try to close the thread you've already got all you wanted.

No.  I just mentioned triggers along with constraints, default values, indexes and grants as things that you have to recreate manually *IF* you use the approach of creating a new table to hold the records for survey_id 200.  I don't know if your table has any of these or not, but if it does, you need to copy them to the new table.

I also see a mistake in that 5-step plan that I suggested.  Step 3 should be:
3. create a new table as "select * from [renamed_table] where survey_id = 200"