Home » RDBMS Server » Performance Tuning » advice to rewrite a query with a bad desing
advice to rewrite a query with a bad desing [message #631220] Sat, 10 January 2015 10:39 Go to next message
jojoyk
Messages: 5
Registered: January 2015
Location: Romania
Junior Member
Hi all,

I have the below query and the explain plan:


SELECT BAM_EVENT.EVENT_ID,
      BAM_EVENT.EVENT_NAME,
      BAM_EVENT.EVENT_SRC_OBJECT_ID,
      BAM_EVENT.EVENT_SRC_OBJECT_NAME,
      BAM_EVENT.EVENT_SRC_OBJECT_START_TIME,
      BAM_EVENT.EVENT_OCCURRENCE_TIME,
      BAM_EVENT.START_TIME_PIM,
      BAM_EVENT.END_TIME_PIM,
      BAM_EVENT.STATUS,
      BAM_EVENT.ORGANIZATION,
      BAM_EVENT.ACTIVITY_ID,
      BAM_EVENT.ACTIVITY_NAME,
      BAM_EVENT.CREATED_BY,
      BAM_EVENT.READ_STATUS,
      BAM_EVENT.IS_UPLOAD,
      BAM_EVENT.PROCESS_TYPE,
      BAM_EVENT.PROCESSING_ITERATION
FROM
            (SELECT BAM_EVENT.EVENT_ID,
            BAM_EVENT.EVENT_NAME,
            BAM_EVENT.EVENT_SRC_OBJECT_ID,
            BAM_EVENT.EVENT_SRC_OBJECT_NAME,
            BAM_EVENT.EVENT_SRC_OBJECT_START_TIME,
            BAM_EVENT.EVENT_OCCURRENCE_TIME,
            BAM_EVENT.START_TIME_PIM,
            BAM_EVENT.END_TIME_PIM,
            BAM_EVENT.STATUS,
            BAM_EVENT.ORGANIZATION,
            BAM_EVENT.ACTIVITY_ID,
            BAM_EVENT.ACTIVITY_NAME,
            BAM_EVENT.CREATED_BY,
            BAM_EVENT.READ_STATUS,
            BAM_EVENT.IS_UPLOAD,
            BAM_EVENT.PROCESS_TYPE,
            BAM_EVENT.PROCESSING_ITERATION ,
            ROW_NUMBER() OVER (ORDER BY EVENT_SRC_OBJECT_ID, END_TIME_PIM) RID
      FROM CORDYS_BAMMDM_OWNER.BAM_EVENT
      WHERE ORGANIZATION = :1 AND
            READ_STATUS = :2 AND
                  EVENT_SRC_OBJECT_ID NOT IN
                  (SELECT EVENT_SRC_OBJECT_ID
            FROM CORDYS_BAMMDM_OWNER.BAM_EVENT_SOURCE_INFO)
      ORDER BY EVENT_SRC_OBJECT_ID, END_TIME_PIM) BAM_EVENT

  
============== EXPLAIN PLAN ========
 
 
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
 
 
Plan hash value: 2746979244
-----------------------------------------------------------------------------------------------------
| Id  | Operation                | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                          |  1896K|  1642M| 10079   (1)| 00:03:14 |
|*  1 |  VIEW                    |                          |  1896K|  1642M| 10079   (1)| 00:03:14 |
|*  2 |   WINDOW SORT PUSHED RANK|                          |  1896K|   542M| 10079   (1)| 00:03:14 |
|*  3 |    HASH JOIN RIGHT ANTI  |                          |  1896K|   542M| 10079   (1)| 00:03:14 |
|   4 |     INDEX FULL SCAN      | PK_BAM_EVENT_SOURCE_INFO |     1 |    27 |     0   (0)| 00:00:01 |
|*  5 |     TABLE ACCESS FULL    | BAM_EVENT                |  1896K|   493M| 10074   (1)| 00:03:14 |
 
 
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------
 
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
 
   1 - filter("RID"<=30)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY "BAM_EVENT"."EVENT_SRC_OBJECT_ID","BAM_EVENT"."END
              _TIME_PIM")<=30)
   3 - access("EVENT_SRC_OBJECT_ID"="EVENT_SRC_OBJECT_ID")
   5 - filter("ORGANIZATION"=:1 AND "READ_STATUS"=:2)
 
 
21 rows selected.



Instance version is 11.2.0.3 Standard Edition.
21 rows are returned from 1.8 milion rows that has the table.
The number of the distinct values for the columns in the where clause, organization is 1 and for the read_status 2, so a full table scan I think can not be avoid.
Every time this query is executed is reading almost 1,7 GB of data from the disks, 570Mb for sorting data (the same order by conditions in the query and his subquery).

Please advice how to rewrite the query in order to reduce the cots.

Thank you,
Best Regards,
Joey

--mod update: added [code] tags

[Updated on: Sat, 10 January 2015 10:49] by Moderator

Report message to a moderator

Re: advice to rewrite a query with a bad desing [message #631222 is a reply to message #631220] Sat, 10 January 2015 10:49 Go to previous messageGo to next message
BlackSwan
Messages: 25792
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/

post CREATE TABLE CORDYS_BAMMDM_OWNER.BAM_EVENT statement along with CREATE INDEX for every index on this table.
Re: advice to rewrite a query with a bad desing [message #631223 is a reply to message #631220] Sat, 10 January 2015 10:50 Go to previous messageGo to next message
John Watson
Messages: 7212
Registered: January 2010
Location: Global Village
Senior Member
On this occasion, I have added the [code] tags to your post for you. Please do it yourself in future.

Your execution plan includes a predicate
RID <=30
Can you explain from where that is coming?
Re: advice to rewrite a query with a bad desing [message #631224 is a reply to message #631222] Sat, 10 January 2015 10:53 Go to previous messageGo to next message
jojoyk
Messages: 5
Registered: January 2015
Location: Romania
Junior Member
CORDYS_BAMMDM_OWNER.BAM_EVENT DEFINITION + INDEXES + CONSTRAINTS


CREATE TABLE CORDYS_BAMMDM_OWNER.BAM_EVENT
(
EVENT_ID NUMBER(18) NOT NULL,
EVENT_NAME VARCHAR2(150 BYTE) NOT NULL,
EVENT_SRC_OBJECT_ID VARCHAR2(100 BYTE) NOT NULL,
EVENT_SRC_OBJECT_NAME VARCHAR2(255 BYTE),
EVENT_SRC_OBJECT_START_TIME DATE DEFAULT SYSDATE NOT NULL,
EVENT_OCCURRENCE_TIME DATE DEFAULT SYSDATE NOT NULL,
START_TIME_PIM NUMBER(16),
END_TIME_PIM NUMBER(16),
STATUS VARCHAR2(50 BYTE),
ORGANIZATION VARCHAR2(240 BYTE) NOT NULL,
ACTIVITY_ID VARCHAR2(255 BYTE),
ACTIVITY_NAME VARCHAR2(255 BYTE),
CREATED_BY VARCHAR2(250 BYTE),
READ_STATUS VARCHAR2(10 BYTE) DEFAULT 'false' NOT NULL,
IS_UPLOAD NUMBER(1) DEFAULT 1 NOT NULL,
UNPROCESSED_BO VARCHAR2(500 BYTE),
PROCESS_TYPE NUMBER(1) DEFAULT 0 NOT NULL,
PROCESSING_ITERATION NUMBER(1)
)
TABLESPACE CORDYS_BAMMDMDSM
RESULT_CACHE (MODE DEFAULT)
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 13096K
NEXT 13096K
MAXSIZE UNLIMITED
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;


CREATE INDEX CORDYS_BAMMDM_OWNER.IND_001_BAM_EVENT ON CORDYS_BAMMDM_OWNER.BAM_EVENT
(ORGANIZATION, READ_STATUS)
LOGGING
TABLESPACE CORDYS_BAMMDMDSM
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 13096K
NEXT 13096K
MAXSIZE UNLIMITED
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
)
NOPARALLEL;


CREATE INDEX CORDYS_BAMMDM_OWNER.IND_003_BAM_EVENT ON CORDYS_BAMMDM_OWNER.BAM_EVENT
(EVENT_SRC_OBJECT_ID, EVENT_NAME)
LOGGING
TABLESPACE CORDYS_BAMMDMDSM
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 13096K
NEXT 13096K
MAXSIZE UNLIMITED
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
)
NOPARALLEL;


CREATE INDEX CORDYS_BAMMDM_OWNER.IND_ENDTIME_PIM ON CORDYS_BAMMDM_OWNER.BAM_EVENT
(END_TIME_PIM)
LOGGING
TABLESPACE CORDYS_BAMMDMDSM
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 13096K
NEXT 13096K
MAXSIZE UNLIMITED
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
)
NOPARALLEL;


CREATE UNIQUE INDEX CORDYS_BAMMDM_OWNER.PK_BAM_EVENT ON CORDYS_BAMMDM_OWNER.BAM_EVENT
(EVENT_ID)
LOGGING
TABLESPACE CORDYS_BAMMDMDSM
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 13096K
NEXT 13096K
MAXSIZE UNLIMITED
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
)
NOPARALLEL;


ALTER TABLE CORDYS_BAMMDM_OWNER.BAM_EVENT ADD (
CONSTRAINT PK_BAM_EVENT
PRIMARY KEY
(EVENT_ID)
USING INDEX CORDYS_BAMMDM_OWNER.PK_BAM_EVENT
ENABLE VALIDATE);

GRANT SELECT ON CORDYS_BAMMDM_OWNER.BAM_EVENT TO CORDYS_READ_ONLY;


CREATE TABLE CORDYS_BAMMDM_OWNER.BAM_EVENT_SOURCE_INFO
(
EVENT_SRC_OBJECT_ID VARCHAR2(50 BYTE) NOT NULL,
SERVICE_CONTAINER_DN VARCHAR2(500 BYTE) NOT NULL
)
TABLESPACE CORDYS_BAMMDMDSM
RESULT_CACHE (MODE DEFAULT)
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 13096K
NEXT 13096K
MAXSIZE UNLIMITED
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
Re: advice to rewrite a query with a bad desing [message #631225 is a reply to message #631224] Sat, 10 January 2015 11:04 Go to previous messageGo to next message
jojoyk
Messages: 5
Registered: January 2015
Location: Romania
Junior Member
Predicate RID <=30 comes from the name of the subquery.
Re: advice to rewrite a query with a bad desing [message #631227 is a reply to message #631225] Sat, 10 January 2015 11:36 Go to previous messageGo to next message
BlackSwan
Messages: 25792
Registered: January 2009
Location: SoCal
Senior Member
post full results from SQL below

SELECT * FROM V$VERSION;

Do all tables & indexes involved with this query have current & accurate statistics?
Re: advice to rewrite a query with a bad desing [message #631229 is a reply to message #631225] Sat, 10 January 2015 12:07 Go to previous messageGo to next message
John Watson
Messages: 7212
Registered: January 2010
Location: Global Village
Senior Member
jojoyk wrote on Sat, 10 January 2015 17:04
Predicate RID <=30 comes from the name of the subquery.
Well, yes, the RID comes from the subquery. What about the 30 ?

You need to post the execution pan for the query you provided, not for some other query. Otherwise, there is nothing one can do. As BS has suggested, the statistics make no sense: the exec plan anticipates nearly two million rows returned, but you say you get twenty one rows. So either your stats are massively wrong (is there really only one row in BAM_EVENT_SOURCE_INFO ?) or you are confusing things.
Re: advice to rewrite a query with a bad desing [message #631231 is a reply to message #631229] Sat, 10 January 2015 12:51 Go to previous messageGo to next message
jojoyk
Messages: 5
Registered: January 2015
Location: Romania
Junior Member
SQL> SELECT * FROM V$VERSION;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
Re: advice to rewrite a query with a bad desing [message #631232 is a reply to message #631231] Sat, 10 January 2015 12:56 Go to previous messageGo to next message
jojoyk
Messages: 5
Registered: January 2015
Location: Romania
Junior Member
I run now the explain plan. It changed:


PLAN_TABLE_OUTPUT
----------------------------------------
Plan hash value: 1872136733

---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1978K| 1688M| 10518 (1)| 00:03:22 |
| 1 | VIEW | | 1978K| 1688M| 10518 (1)| 00:03:22 |
| 2 | WINDOW SORT | | 1978K| 573M| 10517 (1)| 00:03:22 |
|* 3 | HASH JOIN RIGHT ANTI| | 1978K| 573M| 10517 (1)| 00:03:22 |
| 4 | INDEX FULL SCAN | PK_BAM_EVENT_SOURCE_INFO | 1 | 27 | 0 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | BAM_EVENT | 1978K| 522M| 10512 (1)| 00:03:22 |

PLAN_TABLE_OUTPUT
---------------------------------

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

3 - access("EVENT_SRC_OBJECT_ID"="EVENT_SRC_OBJECT_ID")
5 - filter("ORGANIZATION"=:1 AND "READ_STATUS"=:2)

18 rows selected.



Statistics run today:


SQL> set lines 200;
SQL> set pages 200;
SQL> select * FROM DBA_TAB_STATS_HISTORY where owner = 'CORDYS_BAMMDM_OWNER' and table_name = 'BAM_EVENT';

OWNER TABLE_NAME PARTITION_NAME SUBPARTITION_NAME STATS_UPDATE_TIME
------------------------------ ------------------------------ ------------
CORDYS_BAMMDM_OWNER BAM_EVENT 11-JUL-14 10.02.45.216326 AM -04:00
CORDYS_BAMMDM_OWNER BAM_EVENT 17-SEP-14 03.33.45.352368 PM -04:00
CORDYS_BAMMDM_OWNER BAM_EVENT 24-SEP-14 04.36.19.829078 PM -04:00
CORDYS_BAMMDM_OWNER BAM_EVENT 01-OCT-14 03.30.08.367525 PM -04:00
CORDYS_BAMMDM_OWNER BAM_EVENT 08-OCT-14 04.38.09.778235 PM -04:00
CORDYS_BAMMDM_OWNER BAM_EVENT 15-OCT-14 04.38.30.554403 PM -04:00
CORDYS_BAMMDM_OWNER BAM_EVENT 22-OCT-14 03.43.55.398566 PM -04:00
CORDYS_BAMMDM_OWNER BAM_EVENT 29-OCT-14 03.30.10.624826 PM -04:00
CORDYS_BAMMDM_OWNER BAM_EVENT 05-NOV-14 03.30.08.848283 PM -05:00
CORDYS_BAMMDM_OWNER BAM_EVENT 12-NOV-14 03.44.05.579994 PM -05:00
CORDYS_BAMMDM_OWNER BAM_EVENT 19-NOV-14 03.47.26.197728 PM -05:00
CORDYS_BAMMDM_OWNER BAM_EVENT 26-NOV-14 03.31.08.154955 PM -05:00
CORDYS_BAMMDM_OWNER BAM_EVENT 10-DEC-14 03.32.54.478640 PM -05:00
CORDYS_BAMMDM_OWNER BAM_EVENT 17-DEC-14 03.31.45.388869 PM -05:00
CORDYS_BAMMDM_OWNER BAM_EVENT 24-DEC-14 03.41.05.776134 PM -05:00
CORDYS_BAMMDM_OWNER BAM_EVENT 31-DEC-14 03.41.39.612942 PM -05:00
CORDYS_BAMMDM_OWNER BAM_EVENT 07-JAN-15 03.36.17.642497 PM -05:00
CORDYS_BAMMDM_OWNER BAM_EVENT 10-JAN-15 05.32.47.735819 AM -05:00
CORDYS_BAMMDM_OWNER BAM_EVENT 10-JAN-15 05.36.45.789817 AM -05:00
CORDYS_BAMMDM_OWNER BAM_EVENT 10-JAN-15 05.39.25.126086 AM -05:00
CORDYS_BAMMDM_OWNER BAM_EVENT 10-JAN-15 05.56.31.161466 AM -05:00
CORDYS_BAMMDM_OWNER BAM_EVENT 10-JAN-15 05.57.52.922624 AM -05:00

22 rows selected.
Re: advice to rewrite a query with a bad desing [message #631234 is a reply to message #631232] Sat, 10 January 2015 14:59 Go to previous message
John Watson
Messages: 7212
Registered: January 2010
Location: Global Village
Senior Member
This is very difficult, because you appear to have trouble telling the truth.
In your original post, you gave a query with a plan from a different query. This was obvious, because the predicates were different.
In your last post, you have done the same thing: your query against DBA_TAB_STATS_HISTORY cannot possibly give the result you posted. This is obvious because there are columns missing.

What's going on?
Previous Topic: redo buffer allocation retries not near 0, how do I set _log_parallelism_max
Next Topic: table lock in past
Goto Forum:
  


Current Time: Tue Jan 16 02:43:22 CST 2018

Total time taken to generate the page: 0.01445 seconds