Home » RDBMS Server » Performance Tuning » A SQL query with self join and analytical function is running slow (Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi)
A SQL query with self join and analytical function is running slow [message #642916] Wed, 23 September 2015 00:33 Go to next message
toitdoctor
Messages: 6
Registered: September 2015
Location: USA
Junior Member
Hi All,

I am facing a self join query with lot of complexity. We are trying to rewrite the query because we are seeing the CPU 100% so DBA has to kill the session.

We have very limited access so I have only explain of the query.

Oracle version :

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

Query :

SELECT DISTINCT ID1,
  ID2,
  TRUNC(AC_DATE)
FROM
  (SELECT TAB1A.TABLE1_C3                                                             AS ID1,
    TAB1C.TABLE1_C3                                                                 AS IDall,
    Prty_a.PARTY_ID                                                                           AS ID2,
    TAB1A.TABLE1_C6                                         AS AC_DATE,
    MAX (TAB1C.TABLE1_C4) over (partition BY TAB1C.TABLE1_C3) AS MAXALL
  FROM TABLE1 TAB1A,
    TABLE2 TAB2A,
    TABLE3 TAB3A,
    TABLE1 TAB1B,
    TABLE2 TAB2B,
    TABLE3 TAB3B,
    TABLE1 TAB1C
  WHERE TAB2A.TABLE2_C1         =to_date('12/31/9999', 'MM/DD/YYYY')
  AND TAB1A.TABLE1_C1      =TAB2A.TABLE2_C2
  AND TAB1A.TABLE1_C2       =TAB3A.TABLE3_C2
  AND TAB3A.TABLE3_C1                 IN ('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P')
  AND TAB2B.TABLE2_C1           =to_date('12/31/9999', 'MM/DD/YYYY')
  AND TAB1B.TABLE1_C5                 =TAB2B.TABLE2_C2
  AND TAB1B.TABLE1_C2       =TAB3B.TABLE3_C2
  AND TAB3B.TABLE3_C1                  ='A'
  AND LENGTH(TAB1A.TABLE1_C3(+))    =5
  AND TAB1A.TABLE1_C3               =TAB1B.TABLE1_C3
  AND TAB1C.TABLE1_C3             = TAB1A.TABLE1_C3
  AND to_number(TAB1A.TABLE1_C3(+))>=10000
  )
WHERE AC_DATE= MAXALL
AND ID1    = IDALL


Find the explain plan as attachment.

Can you pleas guide us to where to start in query rewrite ?

Thanks in advance.
Re: A SQL query with self join and analytical function is running slow [message #642934 is a reply to message #642916] Wed, 23 September 2015 02:35 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Really we need the explain plan in a more readable format, use this method:
SQL> explain plan for select * from dual;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3543395131

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

8 rows selected.

SQL> 

And then copy and paste here in code tags.

Also the table structures, including all indexes.
How many rows in each table?
How many rows does the query return?
Re: A SQL query with self join and analytical function is running slow [message #642935 is a reply to message #642934] Wed, 23 September 2015 02:49 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
I very much doubt that outer-join is actually being treated as an outer-join. For outer-joins using the (+) syntax to work every column from the outer-joined table in the where clause needs (+) next to it. If you don't do that it gets treated as an inner-join.
So I doubt the query currently does what you think it does and you should fix that before you worry about performance.
Using the ansi syntax would probably make it more readable
Re: A SQL query with self join and analytical function is running slow [message #642936 is a reply to message #642935] Wed, 23 September 2015 02:51 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Plus I'd replace these:
LENGTH(TAB1A.TABLE1_C3(+))    =5
  AND to_number(TAB1A.TABLE1_C3(+))>=10000

with
to_number(TAB1A.TABLE1_C3(+)) BETWEEN 10000 AND 99999

Re: A SQL query with self join and analytical function is running slow [message #642937 is a reply to message #642936] Wed, 23 September 2015 03:22 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
And finally - if you're going to rename tables before posting please do so consistently. I've no idea which of the tables Prty_a is.
Re: A SQL query with self join and analytical function is running slow [message #642965 is a reply to message #642934] Wed, 23 September 2015 09:20 Go to previous messageGo to next message
toitdoctor
Messages: 6
Registered: September 2015
Location: USA
Junior Member
Thanks for the reply...

I am working on a slow DW application and this is an example of one query.

We have lot of query which are running slow so may I know any initial steps which we need to go through to improve the performance of a query.

I don't have permission to run dbms_xplan in our database due to some security but I have asked my DBA to run.

But Can you please let me know the initial steps and starting point in SQL performance tuning which you follow so I can also looking into right direction after explain plan ?

Thanks in advance..

Re: A SQL query with self join and analytical function is running slow [message #642968 is a reply to message #642965] Wed, 23 September 2015 09:28 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
There's really no quick answer to that question that isn't incredibly vague.
You look at the operations that are taking the most time and see if they can be replaced by other, quicker operations.
Full table scans replaced by index scans for example, but even then, depending on the nature of the query, full table scan may actually be the correct approach.
Performance tuning is big complicated subject. I would suggest reading the performance tunning guide in the oracle docs if you haven'r already.

But, I'm going to re-iterate this, your current query is wrong.
You've got an attempt at an outer-join that never actually function as one.
Either that outer-join should just be inner-join, in which case the (+) can simple be removed, or a bigger rewrite is required to get the correct answer.
Tuning a query that gives the wrong results is a waste of time.
Re: A SQL query with self join and analytical function is running slow [message #643030 is a reply to message #642968] Fri, 25 September 2015 23:56 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
cookiemonster is so right (as usual), it will be very difficult to help tune a query that is not a real query. And there is no quick fix to tuning. If there were, the CBO would do it right the first time.

If however, you are desperate to try something quick, I would suggest you go with the tried and true method of "QUERY DECOMPOSITION AND RECONSTRUCTION". This strategy is basically to break down the query into smaller pieces to see how long each takes, in order to determine which piece is causing you the most time. It is not a fast method since you will have to run many iterations of the query to get anywhere but it will eventually help you to progress. It is the "brute force" approach when either all else fails, or for whatever reasons mode educated and theoretical methods don't work or cannot be applied.

For example

1. you point out you are using an analytic
2. cookiemonster points out you have a bad outer-join
3. you said you have a "self-join" although that does not really matter.
4. I see a distinct at the top

These are all (what do we call them) complex? unusual? interesting? different?

But this query

create table cookiemonster_test_1
nologging
as
SELECT         TAB1A.table1_c3                   AS ID1, 
               TAB1C.table1_c3                   AS IDall, 
               prty_a.party_id                   AS ID2, 
               TAB1A.table1_c6                   AS AC_DATE
        FROM   table1 TAB1A, 
               table2 TAB2A, 
               table3 TAB3A, 
               table1 TAB1B, 
               table2 TAB2B, 
               table3 TAB3B, 
               table1 TAB1C 
        WHERE      TAB2A.table2_c1 = To_date('12/31/9999', 'MM/DD/YYYY') 
               AND TAB1A.table1_c1 = TAB2A.table2_c2 
               AND TAB1A.table1_c2 = TAB3A.table3_c2 
               AND TAB3A.table3_c1 IN ( 'A', 'B', 'C', 'D', 
                                        'E', 'F', 'G', 'H', 
                                        'I', 'J', 'K', 'L', 
                                        'M', 'N', 'O', 'P' ) 
               AND TAB2B.table2_c1 = To_date('12/31/9999', 'MM/DD/YYYY') 
               AND TAB1B.table1_c5 = TAB2B.table2_c2 
               AND TAB1B.table1_c2 = TAB3B.table3_c2 
               AND TAB3B.table3_c1 = 'A' 
               AND Length(TAB1A.table1_c3) = 5 
               AND TAB1A.table1_c3 = TAB1B.table1_c3 
               AND TAB1C.table1_c3 = TAB1A.table1_c3 
               AND To_number(TAB1A.table1_c3) >= 10000
/

This query is your base query. Notice what has been removed.

Creating this table will let you get a row count, and a timing to use for comparison purposes, and to see what the basic query plan is like. Then you can go from there, adding things back in to see what hurts the most. Or if the time is still mostly consumed then the problem was not any of what was removed.

Give it a try and let us know what you find out.

If you are interested in SQL Tuning in general, I would point you to my book currently on sale on Amazon. Here are the free promotional items that will help you decide if the book is right for you before you buy it.

Please find attached:

Chapter #1 of the book (Oracle SQL Performance Tuning and Optimization: It's all about the Cardinalities)
Scripts from the book.
A new organization tool for doing SQL Tuning sessions (The SQL Tuning Worksheet)
A brief discussion on what information is needed to tune a query.

Let us know what happens. Kevin



Re: A SQL query with self join and analytical function is running slow [message #643773 is a reply to message #643030] Sat, 17 October 2015 16:23 Go to previous messageGo to next message
toitdoctor
Messages: 6
Registered: September 2015
Location: USA
Junior Member
Hi All,

Thanks for your reply and comments..Soory for late reply... I was busy with some other task.

Now again I need to work on the same query which I had posted earlier.

I have tried to rewrite the query in many ways but the situtation is same so help me if you can in rewrite...


Find the details of the query in details:

Main Query :

SELECT DISTINCT ID1,
  ID2,
  TRUNC(AC_DATE)
FROM
  (SELECT TAB1A.TABLE1_C3                                     AS ID1,
    TAB1C.TABLE1_C3                                           AS IDall,
    TAB2A.TABLE2_C3                                           AS ID2,
    TAB1A.TABLE1_C6                                           AS AC_DATE,
    MAX (TAB1C.TABLE1_C4) over (partition BY TAB1C.TABLE1_C3) AS MAXALL
  FROM TABLE1 TAB1A,
    TABLE2 TAB2A,
    TABLE3 TAB3A,
    TABLE1 TAB1B,
    TABLE2 TAB2B,
    TABLE3 TAB3B,
    TABLE1 TAB1C
  WHERE TAB2A.TABLE2_C1             =to_date('12/31/9999', 'MM/DD/YYYY')
  AND TAB1A.TABLE1_C1               =TAB2A.TABLE2_C2
  AND TAB1A.TABLE1_C2               =TAB3A.TABLE3_C2
  AND TAB3A.TABLE3_C1              IN ('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P')
  AND TAB2B.TABLE2_C1               =to_date('12/31/9999', 'MM/DD/YYYY')
  AND TAB1B.TABLE1_C5               =TAB2B.TABLE2_C2
  AND TAB1B.TABLE1_C2               =TAB3B.TABLE3_C2
  AND TAB3B.TABLE3_C1               ='A'
  AND LENGTH(TAB1A.TABLE1_C3(+))    =7
  AND TAB1A.TABLE1_C3               =TAB1B.TABLE1_C3
  AND TAB1A.TABLE1_C3               =TAB1C.TABLE1_C3 
  AND to_number(TAB1A.TABLE1_C3(+))>=10000
  )
WHERE AC_DATE= MAXALL
AND ID1      = IDALL



Explain of the Query :

Execution Plan
----------------------------------------------------------
Plan hash value: 195783687

----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name                         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                              |     1 |    48 |  8851   (2)| 00:02:04 |       |       |
|   1 |  HASH UNIQUE                            |                              |     1 |    48 |  8851   (2)| 00:02:04 |       |       |
|*  2 |   VIEW                                  |                              |     1 |    48 |  8850   (2)| 00:02:04 |       |       |
|   3 |    WINDOW SORT                          |                              |     1 |   241 |  8850   (2)| 00:02:04 |       |       |
|   4 |     NESTED LOOPS                        |                              |     1 |   241 |  8849   (2)| 00:02:04 |       |       |
|   5 |      NESTED LOOPS                       |                              |     1 |   224 |  8847   (2)| 00:02:04 |       |       |
|   6 |       NESTED LOOPS                      |                              |     1 |   201 |  8844   (2)| 00:02:04 |       |       |
|   7 |        NESTED LOOPS                     |                              |     2 |   352 |  8842   (2)| 00:02:04 |       |       |
|   8 |         NESTED LOOPS                    |                              |     1 |   156 |  8840   (2)| 00:02:04 |       |       |
|*  9 |          HASH JOIN                      |                              |    49 |  6125 |  8693   (2)| 00:02:02 |       |       |
|* 10 |           TABLE ACCESS FULL             | TABLE3                       |     7 |   700 |   804   (1)| 00:00:12 |       |       |
|  11 |           INDEX FAST FULL SCAN          | IDX_TABLE1_UNIQUE_UK         |  6979K|   166M|  7846   (1)| 00:01:50 |       |       |
|  12 |          TABLE ACCESS BY INDEX ROWID    | TABLE1                       |     1 |    31 |     3   (0)| 00:00:01 |       |       |
|* 13 |           INDEX RANGE SCAN              | IDX_TABLE1_C3                |     1 |       |     2   (0)| 00:00:01 |       |       |
|  14 |         TABLE ACCESS BY INDEX ROWID     | TABLE1                       |     9 |   180 |     2   (0)| 00:00:01 |       |       |
|* 15 |          INDEX RANGE SCAN               | IDX_TABLE1_C3                |     9 |       |     2   (0)| 00:00:01 |       |       |
|* 16 |        TABLE ACCESS BY INDEX ROWID      | TABLE3                       |     1 |    25 |     1   (0)| 00:00:01 |       |       |
|* 17 |         INDEX UNIQUE SCAN               | TABLE3_PK                    |     1 |       |     0   (0)| 00:00:01 |       |       |
|  18 |       PARTITION RANGE SINGLE            |                              |     1 |    23 |     3   (0)| 00:00:01 |     2 |     2 |
|  19 |        TABLE ACCESS BY LOCAL INDEX ROWID| TABLE2                       |     1 |    23 |     3   (0)| 00:00:01 |     2 |     2 |
|* 20 |         INDEX RANGE SCAN                | TABLE2_C2_EFF_UK             |     1 |       |     2   (0)| 00:00:01 |     2 |     2 |
|  21 |      PARTITION RANGE SINGLE             |                              |     1 |    17 |     2   (0)| 00:00:01 |     2 |     2 |
|* 22 |       INDEX RANGE SCAN                  | TABLE2_C2_EFF_UK             |     1 |    17 |     2   (0)| 00:00:01 |     2 |     2 |
----------------------------------------------------------------------------------------------------------------------------------------

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

   2 - filter("AC_DATE"="MAXALL" AND "ID1"="IDall")
   9 - access("TAB1B"."TABLE1_C2"="TAB3B"."TABLE3_C2")
  10 - filter("TAB3B"."TABLE3_C1"='A')
  13 - access("TAB1A"."TABLE1_C3"="TAB1B"."TABLE1_C3")
       filter(LENGTH("TAB1A"."TABLE1_C3")=7 AND TO_NUMBER("TAB1A"."TABLE1_C3")>=10000)
  15 - access("TAB1C"."TABLE1_C3"="TAB1A"."TABLE1_C3")
  16 - filter("TAB3A"."TABLE3_C1"='A' OR "TABLE3A"."TABLE3_C1"='B' OR
              "TABLE3A"."TABLE3_C1"='C' OR "TABLE3A"."TABLE3_C1"='D' OR "TABLE3A"."TABLE3_C1"='E' OR "TABLE3A"."TABLE3_C1"='F' OR
 "TABLE3A"."TABLE3_C1"='G' OR "TABLE3A"."TABLE3_C1"='H' OR "TABLE3A"."TABLE3_C1"='J' OR "TABLE3A"."TABLE3_C1"='NK' OR
              "TABLE3A"."TABLE3_C1"='L' OR "TABLE3A"."TABLE3_C1"='M' OR
              "TABLE3A"."TABLE3_C1"='N' OR "TABLE3A"."TABLE3_C1"='O' OR
              "TABLE3A"."TABLE3_C1"='P' OR "TABLE3A"."TABLE3_C1"='Q' OR
              "TABLE3A"."TABLE3_C1"='R' OR "TABLE3A"."TABLE3_C1"='S' OR
              "TABLE3A"."TABLE3_C1"='T' OR "TABLE3A"."TABLE3_C1"='U' OR
              "TABLE3A"."TABLE3_C1"='V' OR "TABLE3A"."TABLE3_C1"='W' OR
              "TABLE3A"."TABLE3_C1"='X' OR "TABLE3A"."TABLE3_C1"='Y')
  17 - access("TAB1A"."TABLE1_C2"="TAB3A"."TABLE3_C2")
  20 - access("TAB1A"."TABLE1_C1"="TAB2A"."TABLE2_C2" AND "TAB2A"."TABLE2_C1"=TIMESTAMP'9999-12-31 00:00:00')
  22 - access("TAB1B"."TABLE1_C5"="TAB2B"."TABLE2_C2" AND "TAB2B"."TABLE2_C1"=TIMESTAMP' 9999-12-31
              00:00:00')



The no of records in the tables :

Table Name	Object Type	Rows	Filtered Rows	     Filtered Rows %
Table3	         TABLE	        162730	        29	         100
Table1	         TABLE	        6979158	        133496	         98
Table2	         TABLE	        69345664	10780159	 84



The Tables and Index scripts :


CREATE TABLE TABLE1
  (
    TABLE1_C6  NUMBER NOT NULL,
    TABLE1_C7  TIMESTAMP(6) NOT NULL,
    TABLE1_C5  INTEGER NOT NULL,
    TABLE1_C8  TIMESTAMP(6) NOT NULL,
    TABLE1_C1  INTEGER NOT NULL,
    TABLE1_C4  TIMESTAMP(6) NOT NULL,
    TABLE1_C9  INTEGER NOT NULL,
    TABLE1_C2  INTEGER NOT NULL,
    TABLE1_C10 INTEGER NOT NULL,
    TABLE1_C3  VARCHAR2(20 CHAR) NOT NULL,
    TABLE1_C11 INTEGER NOT NULL
  )
  TABLESPACE TBS_DATA PCTUSED 0 PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE
  (
    INITIAL 80K MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT
  )
  NOLOGGING NOCOMPRESS NOCACHE NOPARALLEL MONITORING;



CREATE UNIQUE INDEX TABLE1_UNIQUE_UK ON TABLE1
(TABLE1_C5, TABLE1_C8, TABLE1_C9, TABLE1_C2, TABLE1_C10,TABLE1_C3, TABLE1_C11)
NOLOGGING
TABLESPACE tbs_datax
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          80K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE INDEX IDX_TABLE1_C3 ON TABLE1
(TABLE1_C3)
NOLOGGING
TABLESPACE TBS_DATAX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;

ALTER TABLE TABLE1 ADD (
  CONSTRAINT TABLE1_PK
 PRIMARY KEY
 (TABLE1_C6, TABLE1_C7)
    USING INDEX 
    TABLESPACE TBS_DATAX
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          80K
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
               ),
  CONSTRAINT IDX_TABLE1_UNIQUE_UK
 UNIQUE (TABLE1_C5, TABLE1_C8, TABLE1_C9, TABLE1_C2, TABLE1_C10,TABLE1_C3, TABLE1_C11)
    USING INDEX 
    TABLESPACE TBS_DATAX
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          80K
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
               ));


*************************************************8

CREATE TABLE2
(
  TABLE2_C2                  INTEGER       NOT NULL,
  TABLE2_C4                  TIMESTAMP(6)  NOT NULL,
  TABLE2_C1                  TIMESTAMP(6)  NOT NULL,
  TABLE2_C3                  INTEGER       NOT NULL
  )
TABLESPACE TBS_DATA
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
PARTITION BY RANGE (TABLE2_C1)
(  
  PARTITION PART_HIST VALUES LESS THAN (TIMESTAMP' 9999-12-31 00:00:00')
    NOLOGGING
    NOCOMPRESS
    TABLESPACE TBS_DATA_HIST
    PCTFREE    10
    INITRANS   1
    MAXTRANS   255
    STORAGE    (
                INITIAL          80K
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                BUFFER_POOL      DEFAULT
               ),  
  PARTITION PART_CURR VALUES LESS THAN (MAXVALUE)
    NOLOGGING
    NOCOMPRESS
    TABLESPACE TBS_DATA_CURR
    PCTFREE    10
    INITRANS   1
    MAXTRANS   255
    STORAGE    (
                INITIAL          80K
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                BUFFER_POOL      DEFAULT
               )
)
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING
ENABLE ROW MOVEMENT;


CREATE UNIQUE INDEX TABLE2_C2_EFF_UK ON TABLE2
(TABLE2_C2, TABLE2_C1, TABLE2_C4)
  INITRANS   2
  MAXTRANS   255
NOLOGGING
LOCAL (  
  PARTITION PART_HIST
    NOLOGGING
    NOCOMPRESS
    TABLESPACE TBS_DATA_HIST
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          80K
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                BUFFER_POOL      DEFAULT
               ),  
  PARTITION PART_CURR
    NOLOGGING
    NOCOMPRESS
    TABLESPACE TBS_DATA_CURR
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          80K
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                BUFFER_POOL      DEFAULT
               )
)
NOPARALLEL;

CREATE UNIQUE INDEX TABLE2_PK ON TABLE2
(TABLE2_C2, TABLE2_C4)
NOLOGGING
TABLESPACE TBS_DATAX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          80K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


ALTER TABLE TABLE2 ADD (
  CONSTRAINT TABLE2_PK
 PRIMARY KEY
 (TABLE2_C2, TABLE2_C4)
    USING INDEX 
    TABLESPACE TBS_DATAX
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          80K
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
               ),
  CONSTRAINT TABLE2_C2_EFF_UK
 UNIQUE (TABLE2_C2, TABLE2_C1, TABLE2_C4)
 USING INDEX LOCAL);

*************************************************

CREATE TABLE TABLE3
(
  TABLE3_C2               INTEGER          NOT NULL,
  TABLE3_C1               VARCHAR2(255 CHAR)
    )
TABLESPACE TBS_DATA
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          80K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      KEEP
           )
NOLOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;



CREATE UNIQUE INDEX TABLE3_PK ON TABLE3
(TABLE3_C2)
NOLOGGING
TABLESPACE TBS_DATAX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          80K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      KEEP
           )
NOPARALLEL;


ALTER TABLE TABLE3 ADD (
  CONSTRAINT TABLE3_PK
 PRIMARY KEY
 (TABLE3_C2)
    USING INDEX 
    TABLESPACE TBS_DATAX
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          80K
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
               ));
  • Attachment: QUERY.txt
    (Size: 12.87KB, Downloaded 1766 times)

[Updated on: Sat, 17 October 2015 16:36]

Report message to a moderator

Re: A SQL query with self join and analytical function is running slow [message #643774 is a reply to message #643773] Sat, 17 October 2015 16:36 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Much is as good as can be expected.

Consider to rebuild index IDX_TABLE1_UNIQUE_UK.
Realize that at some point in time SQL is as efficient as it can be.
In other words, You can't push a string.
Re: A SQL query with self join and analytical function is running slow [message #643775 is a reply to message #642916] Sat, 17 October 2015 17:51 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Read up on GATHER_PLAN_STATITICS hint and then use it to get both the actual and real cardinalities of plan steps. From this you can see where there is a problem.

Kevin
Re: A SQL query with self join and analytical function is running slow [message #643789 is a reply to message #643774] Sun, 18 October 2015 10:33 Go to previous messageGo to next message
toitdoctor
Messages: 6
Registered: September 2015
Location: USA
Junior Member
"Consider to rebuild index IDX_TABLE1_UNIQUE_UK."

We have tried also but nothing impacting.

This is the query from data warehouse application and we are getting lot of duplicate records and the no of records are very less.

We are getting only 34K records as output.

Re: A SQL query with self join and analytical function is running slow [message #643790 is a reply to message #643789] Sun, 18 October 2015 10:36 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
toitdoctor wrote on Sun, 18 October 2015 08:33
"Consider to rebuild index IDX_TABLE1_UNIQUE_UK."

We have tried also but nothing impacting.

This is the query from data warehouse application and we are getting lot of duplicate records and the no of records are very less.

We are getting only 34K records as output.




why does posted EXPLAIN PLAN show only 1 row returned?
Re: A SQL query with self join and analytical function is running slow [message #643813 is a reply to message #642935] Mon, 19 October 2015 03:16 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
cookiemonster wrote on Wed, 23 September 2015 08:49
I very much doubt that outer-join is actually being treated as an outer-join. For outer-joins using the (+) syntax to work every column from the outer-joined table in the where clause needs (+) next to it. If you don't do that it gets treated as an inner-join.
So I doubt the query currently does what you think it does and you should fix that before you worry about performance.
Using the ansi syntax would probably make it more readable


Seriously, you need to fix that before worrying about anything else.
Re: A SQL query with self join and analytical function is running slow [message #643817 is a reply to message #642916] Mon, 19 October 2015 05:53 Go to previous message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
I see one possibility, though I have not studied the problem enough to know if it actually possible.

This filter looks odd:
AND LENGTH(TAB1A.TABLE1_C3(+))    =7
AND to_number(TAB1A.TABLE1_C3(+))>=10000

depending on your data (decimal values?) there may be no correlation between the length of the string and whether it is over 10000. Better check that! Does the filter really make sense?

Furthermore, it might help to apply that filter earlier. Can you run it with a /*+ leading(tab1a) */ hint in the subquery?
Previous Topic: Memory issue with PGA and AMM (merged)
Next Topic: Unexplained SQL Slowness (AWR Supplied)
Goto Forum:
  


Current Time: Fri Mar 29 08:57:39 CDT 2024