Solved

How do I fix 2 identical schemas where 1 query executes quickly and the other takes a minute and a half

Posted on 2011-02-17
43
397 Views
Last Modified: 2012-05-11
I have 2 identical schemas (i.e. same indexes, tables, records, etc). when I execute the following query in schema 1 it is instantaneous. However when I execute it in schema 2 it takes about a minute. how do I resolve this issue so that execution of the query is instantaneous in schema 2?
Here is the query:

select
    *
from
    ( select
        STI_RECORD.ID,
        STI_RECORD.ABSTRACT
    from
        STI_RECORD
    order by
        STI_RECORD.db_updated DESC )
where
    rownum <= 25;
0
Comment
Question by:sikyala
  • 17
  • 10
  • 6
  • +4
43 Comments
 
LVL 11

Expert Comment

by:Akenathon
ID: 34920232
Same object definition and same data does not mean same performance.

You can have many other things which influence performance... for instance, Oracle may choose a different execution plan because:

- The statistics are outdated or differ between both schemas
- Physical data distribution and ordering might be different
- The indexes on one schema might be more sparse than in the other

Please post the output of the query ran against each of your two schemas after doing SET AUTOTRACE ON to investigate the issue further.
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 34920247
add index on the second one for db_updated

:) analyze tables if you have already indexes on both tables...

make sure number of records are same in both tables, if one has 10 million rows and the other has 100, of course they will run different...

0
 

Author Comment

by:sikyala
ID: 34920313
yes they both have the same number of records and the indexes are the same on both tables. I attached the explain plan
clip-image001.jpg
clip-image002.jpg
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 34920373
analyze the tables... explain plans are different... are they running on same machine/db?
0
 

Author Comment

by:sikyala
ID: 34920442
yes it is the same machine and same database
0
 

Author Comment

by:sikyala
ID: 34920463
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34920555
As HainKurt suggested:  Update the statistics on the slow one.

Then if you get different plans, we'll look elsewhere.

0
 
LVL 23

Expert Comment

by:wdosanjos
ID: 34920738
It could be some concurrency issue.  Are there other processes locking STI_RECORD rows on schema2?

If that's the case, you can try the following:
SET TRANSACTION READ ONLY;
select
    *
from
    ( select
        STI_RECORD.ID,
        STI_RECORD.ABSTRACT
    from
        STI_RECORD
    order by
        STI_RECORD.db_updated DESC )
where
    rownum <= 25;

Open in new window




0
 
LVL 15

Accepted Solution

by:
Devinder Singh Virdi earned 200 total points
ID: 34920919
Observation:
1. Slower query is using parallel automatically, which indicates object was created with parallel degree > 1. Please use select degree from user_tables where table_name = 'WS_STI_RECORD '; to see parallel degree.
2. Please post parallel degree value.
3. Physical reads are >> as compare to faster one.
    This may be table has lots of fragmentation.
    compare the following:
select segment_name, sum(bytes)/(1024*1024) MB from user_segments where segment_name like 'WS_STI_RECORD%' group by segment_name.


0
 
LVL 18

Expert Comment

by:sventhan
ID: 34921534
Fast

Less no of rows

Slow

More rows

You do not have the same no of rows on both the schema thus you've different exec. plan.
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 34922743
but he said same number of data!!!!
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 34922750
0
 

Author Comment

by:sikyala
ID: 34923211
virdi_ds for both schemas

select degree from user_tables where table_name = 'WS_STI_RECORD ';
no rows selected
0
 
LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 34923265
Sorry my bad, please remove space after table name.

select table_name, degree from user_tables where table_name like '%STI_RECORD%';

Confusion: What is the name of your table? what is the relationship between STI_RECORD  and WS_STI_RECORD.

your query was using STI_RECORD whereas execution plan shows WS_STI_RECORD.
0
 

Author Comment

by:sikyala
ID: 34923268
virdi_ds

slow schema select segment result is:
 
MB 4378.25

fast schema select segment result is:

MB 1167.5
0
 
LVL 15

Assisted Solution

by:Devinder Singh Virdi
Devinder Singh Virdi earned 200 total points
ID: 34923293
This indicate that your slow segment is very fragmented and you need to rebuild the table.
This may be happening because of lots of delete operation on that table happened.
You can either reorganize the table online/offline.
That also means your indexes are also fragmented.
Also you can use segment advisor to see how many blocks are empty 75-100% before high water mark etc.
0
 

Author Comment

by:sikyala
ID: 34923321
I don't know if someone changed the number of records in both schemas but now i get two different number counts

slow has 1476722 records

fast has 5150 records
0
 
LVL 51

Assisted Solution

by:HainKurt
HainKurt earned 200 total points
ID: 34923346
:) yes thats the reason... any way... just rebuild/analyze the tables and indexes and it should be fine after...
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 34923356
adding /*+ first_rows */ hint to select query may help

select *
from  (
  select  /*+ first_rows */ STI_RECORD.ID, STI_RECORD.ABSTRACT
    from  STI_RECORD
   order by STI_RECORD.db_updated DESC
   )
where rownum <= 25;
0
 

Author Comment

by:sikyala
ID: 34923388
HainKurt the timing remained the same.

how would I rebuild the table
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 34923469
do you have any trigger on table? constraints? foreign keys?

if not, try this:

create backup_table as
select * from myTable;

truncate myTable;

insertinto myTable
select * from backup_table;

0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 51

Expert Comment

by:HainKurt
ID: 34923473
did you analyze the table?

Analyze Table myTable Compute Statistics
0
 

Author Comment

by:sikyala
ID: 34923475
ok
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 34923487
here is a rebuild script for my products table (basically same as above)
--  ********************************************************************** 
--  Note: This rebuild script is not meant to be used when a possibility * 
--        exists that someone might try to access the table while it is  * 
--        being rebuilt!  If you need online table rebuilding and you    * 
--        are on Oracle 10g or newer, use the dbms_redfinition wizard    * 
--        under database -> optimize. (Requires DB Admin module)         * 
--                                                                       * 
--        Locks are released when the first DDL, COMMIT or ROLLBACK is   * 
--        performed, so adding a "Lock table" command at the top of this * 
--        script will not prevent others from accessing the table for    * 
--        the duration of the script.                                    * 
--                                                                       * 
--   One more important note:                                            * 
--        This script will cause the catalog in replicated environments  * 
--        to become out of sync.                                         * 
--  ********************************************************************** 

--  Table Rebuild script generated by TOAD  
--  
--  Original table: PRODUCTS 
--  Backup of table: PRODUCTS_X 
--  Date: 2/18/2011 12:11:18 AM 
--  
SET LINESIZE 200
--  
--  Make backup copy of original table 
ALTER TABLE HainKurt.PRODUCTS RENAME TO PRODUCTS_X ; 
  
 

-- Drop all user named constraints
ALTER TABLE HainKurt.PRODUCTS_X DROP CONSTRAINT SYS_C0011499 ;

--  Recreate original table 
CREATE TABLE HainKurt.PRODUCTS
(
  PID    NUMBER(9),
  PNAME  VARCHAR2(200 BYTE),
  ID     NUMBER(9)
)
TABLESPACE ISS_SYS
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOLOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;
 
--  Copy the data from the renamed table  
INSERT /*+ APPEND */
INTO HainKurt.PRODUCTS INS_TBL
(PID, PNAME, ID)
SELECT 
PID, PNAME, ID
FROM HainKurt.PRODUCTS_X SEL_TBL ; 
  
Commit ; 
  

-- Drop all other user named indexes 
-- (none) 




--  Recreate Indexes, Constraints, and Grants 

ALTER TABLE HainKurt.PRODUCTS ADD (
  PRIMARY KEY
 (PID)
    USING INDEX 
    TABLESPACE ISS_INDX
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          1M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
               ));


ALTER TABLE HainKurt.PRODUCTS ADD (
  FOREIGN KEY (ID) 
 REFERENCES HainKurt.CATEGORY (ID));


GRANT SELECT ON HainKurt.PRODUCTS TO ISS_READONLY;
 
--  There are no FKeys that reference the new table to recreate. 
 
 
--  There are no dependent objects to recompile. 
 
--  There are no triggers in this schema to rebuild.

Open in new window

0
 
LVL 51

Assisted Solution

by:HainKurt
HainKurt earned 200 total points
ID: 34923495
rebuild index

Alter index indexName rebuild
--tablespace INDEX_TABLESPACE_NAME
0
 

Author Comment

by:sikyala
ID: 34923534
analyzing right now
0
 

Author Comment

by:sikyala
ID: 34923655
analyze didn't increase the speed
0
 

Author Comment

by:sikyala
ID: 34923677
virdi_ds the

the degree on the slow schema is 4

the degree on fast schema is 1


0
 
LVL 11

Expert Comment

by:Akenathon
ID: 34923847
"Rebuild" the table means ALTER TABLE xxx MOVE, followed by ALTER INDEX idxNNN REBUILD (the indexes will be rendered invalid after you MOVE the segment. That will "shrink" the data so that it (hopefully) occupies less blocks.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34925681
Why rebuild/analyze/???

Unless I am missing here?

You posted:
slow has 1476722 records
fast has 5150 records

Is that the row count form the tables: select count(*) from STI_RECORD?

Then HainKurt was correct in http:#a34923346.  That is why one is fast and one is 'slow'er.
0
 
LVL 11

Expert Comment

by:Akenathon
ID: 34926330
I also don't expect any improvement after reorganizing... I was just giving the correct syntax :-)

Regarding performance: the rowcount is different enough to explain the performance difference, but only in the current scenario. The query will be instantaneous after he creates an index on STI_RECORD.db_updated, no matter how many rows the table has (another expert already suggested such an index early in the discussion).

BTW: The execution plans the author posted don't show the order by on db_updated. It looks like it's pushing the rownum<=25 predicate into the subquery, and therefore returning 25 rows but not necessarily the ones with lowest db_updated. That ought to be confirmed by the author, before and after creating the index.
0
 
LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 34926650
>>slow schema select segment result is:
>>MB 4378.25
>>fast schema select segment result is:
>>MB 1167.5

Operation: Full Table Scan (Expected as no Index can be used)

Slower database has lots of rows. Table size is 4 times bigger.
Theoretically if you use parallel degree 4, you should get result = faster query.
Practically its not happening.
Reason: When you use parallel,  4 parallel processes are created (depends on table's degree as well until specified), and they have to talk with each other and are requesting blocks back and forth.
In order to gain performance for this talk, you can use PARALLEL_EXECUTION_MESSAGE_SIZE.
This parameter can also degrade performance, because it may create fragmentation in Shared Pool.
0
 

Author Comment

by:sikyala
ID: 34926933
slightw yes the record count is from the tables
0
 

Author Comment

by:sikyala
ID: 34926966
yes there is an index on db_updated. It has always been there.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34926973
>>slightw yes the record count is from the tables

OK then, all other things being equal, that's the reason for the performance difference.
0
 
LVL 11

Expert Comment

by:Akenathon
ID: 34933127
Try this version

select --+index_desc(sti_record name_of_the_index_by_db_updated)
        STI_RECORD.ID,
        STI_RECORD.ABSTRACT
    from
        STI_RECORD
where
    rownum <= 25;

Not that I recommend you leave it like that, but I'd just like to verify if the index by that field can be used normally.
0
 

Author Comment

by:sikyala
ID: 34948370
I keep getting an error missing expression

select --+index_desc(sti_record I_DB_UPDATED) STI_RECORD.ID, STI_RECORD.ABSTRACT from STI_RECORD where rownum <= 25;

ERROR at line 1:
ORA-00936: missing expression
0
 
LVL 11

Assisted Solution

by:Akenathon
Akenathon earned 100 total points
ID: 34951584
You don't have to put it on a single line, or else you're commenting out everything after "select --". Either copy/paste it as I provided, or use the other syntax for comments/hints:

select /*+index_desc(sti_record I_DB_UPDATED)*/ STI_RECORD.ID, STI_RECORD.ABSTRACT from STI_RECORD where rownum <= 25;

Please try it after doing SET AUTOTRACE ON and paste the execution plan and the statistics.
0
 

Author Comment

by:sikyala
ID: 34952058
it was instant when i ran you command. but i don't understand what was different this time
0
 
LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 34953394
There is missing order by STI_RECORD.db_updated DESC clause, resulting in mismatch of records.
0
 
LVL 11

Expert Comment

by:Akenathon
ID: 34954164
Please post both the original and my suggested query output WITH SET AUTOTRACE ON to help you understand why. It's really hard to help you further if you still won't provide the data needed...
0
 

Author Comment

by:sikyala
ID: 34954302
ok but if i could hold off for a moment I am in the midst of another crisis
0
 

Author Closing Comment

by:sikyala
ID: 34981112
Dropped & recreated the index, removed the parallel setting on the table
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

743 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

13 Experts available now in Live!

Get 1:1 Help Now