Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 409
  • Last Modified:

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

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
sikyala
Asked:
sikyala
  • 17
  • 10
  • 6
  • +4
5 Solutions
 
AkenathonCommented:
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
 
HainKurtSr. System AnalystCommented:
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
 
sikyalaSenior Database AdministratorAuthor Commented:
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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
HainKurtSr. System AnalystCommented:
analyze the tables... explain plans are different... are they running on same machine/db?
0
 
sikyalaSenior Database AdministratorAuthor Commented:
yes it is the same machine and same database
0
 
sikyalaSenior Database AdministratorAuthor Commented:
0
 
slightwv (䄆 Netminder) Commented:
As HainKurt suggested:  Update the statistics on the slow one.

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

0
 
wdosanjosCommented:
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
 
Devinder Singh VirdiCommented:
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
 
sventhanCommented:
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
 
HainKurtSr. System AnalystCommented:
but he said same number of data!!!!
0
 
HainKurtSr. System AnalystCommented:
0
 
sikyalaSenior Database AdministratorAuthor Commented:
virdi_ds for both schemas

select degree from user_tables where table_name = 'WS_STI_RECORD ';
no rows selected
0
 
Devinder Singh VirdiCommented:
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
 
sikyalaSenior Database AdministratorAuthor Commented:
virdi_ds

slow schema select segment result is:
 
MB 4378.25

fast schema select segment result is:

MB 1167.5
0
 
Devinder Singh VirdiCommented:
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
 
sikyalaSenior Database AdministratorAuthor Commented:
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
 
HainKurtSr. System AnalystCommented:
:) yes thats the reason... any way... just rebuild/analyze the tables and indexes and it should be fine after...
0
 
HainKurtSr. System AnalystCommented:
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
 
sikyalaSenior Database AdministratorAuthor Commented:
HainKurt the timing remained the same.

how would I rebuild the table
0
 
HainKurtSr. System AnalystCommented:
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
 
HainKurtSr. System AnalystCommented:
did you analyze the table?

Analyze Table myTable Compute Statistics
0
 
sikyalaSenior Database AdministratorAuthor Commented:
ok
0
 
HainKurtSr. System AnalystCommented:
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
 
HainKurtSr. System AnalystCommented:
rebuild index

Alter index indexName rebuild
--tablespace INDEX_TABLESPACE_NAME
0
 
sikyalaSenior Database AdministratorAuthor Commented:
analyzing right now
0
 
sikyalaSenior Database AdministratorAuthor Commented:
analyze didn't increase the speed
0
 
sikyalaSenior Database AdministratorAuthor Commented:
virdi_ds the

the degree on the slow schema is 4

the degree on fast schema is 1


0
 
AkenathonCommented:
"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
 
slightwv (䄆 Netminder) Commented:
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
 
AkenathonCommented:
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
 
Devinder Singh VirdiCommented:
>>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
 
sikyalaSenior Database AdministratorAuthor Commented:
slightw yes the record count is from the tables
0
 
sikyalaSenior Database AdministratorAuthor Commented:
yes there is an index on db_updated. It has always been there.
0
 
slightwv (䄆 Netminder) Commented:
>>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
 
AkenathonCommented:
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
 
sikyalaSenior Database AdministratorAuthor Commented:
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
 
AkenathonCommented:
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
 
sikyalaSenior Database AdministratorAuthor Commented:
it was instant when i ran you command. but i don't understand what was different this time
0
 
Devinder Singh VirdiCommented:
There is missing order by STI_RECORD.db_updated DESC clause, resulting in mismatch of records.
0
 
AkenathonCommented:
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
 
sikyalaSenior Database AdministratorAuthor Commented:
ok but if i could hold off for a moment I am in the midst of another crisis
0
 
sikyalaSenior Database AdministratorAuthor Commented:
Dropped & recreated the index, removed the parallel setting on the table
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 17
  • 10
  • 6
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now