Home » RDBMS Server » Performance Tuning » Query tunning required
Query tunning required [message #299133] Sat, 09 February 2008 18:01 Go to next message
gksenthilkumar
Messages: 23
Registered: November 2007
Location: india
Junior Member
I have the following query to be tuned.. I have attached the query in the post

How do we tune / re-write this query ? because its taking more than 3 hours & disconnecting the connection. Have huge no of records for most of the table & taking such a quict long long time......Please helm me with tuned/re-write one if possible.....?




moderator edit: I have provided the contents of the attached file separately below. Many members cannot download such files due to security restrictions. So, in the future, please include the contents as part of the post instead.

CREATE TABLE ER
(
ROLL_ENT VARCHAR2(4 BYTE) NOT NULL,
ROLL_SUB_ENT VARCHAR2(3 BYTE) NOT NULL,
ROLL_ENT_DESCR VARCHAR2(50 BYTE),
ENT VARCHAR2(4 BYTE) NOT NULL,
SUB_ENT VARCHAR2(3 BYTE) NOT NULL,
ENT_DESCR VARCHAR2(50 BYTE)
);


CREATE UNIQUE INDEX REL_ER_IDX_PK ON ER
(ROLL_ENT, ROLL_SUB_ENT, ENT, SUB_ENT);


ALTER TABLE ER ADD (
CONSTRAINT REL_ER_IDX_PK
PRIMARY KEY
(ROLL_ENT, ROLL_SUB_ENT, ENT, SUB_ENT);


TOTAL NUMBER OF RECORDS FOR TABLE ER : 123542


CREATE TABLE CR
(
ACCT VARCHAR2(9 BYTE) NOT NULL,
ACCT_LVL VARCHAR2(2 BYTE) NOT NULL,
ACCT_ID VARCHAR2(9 BYTE) NOT NULL,
REL_TYPE VARCHAR2(10 BYTE) NOT NULL,
ACCT_TYPE VARCHAR2(2 BYTE) NOT NULL,
ACCT_DESCR VARCHAR2(43 BYTE),
POST_ACCT VARCHAR2(9 BYTE) NOT NULL,
POST_ACCT_TYPE VARCHAR2(2 BYTE),
POST_ACCT_DESCR VARCHAR2(43 BYTE),
SIGN_REVRSL NUMBER
);


CREATE INDEX CR_IDX_01 ON CR
(ACCT_ID, REL_TYPE, POST_ACCT);


CREATE UNIQUE INDEX CR_IDX_PK ON CR
(ACCT_ID, ACCT, REL_TYPE, POST_ACCT);


TOTAL NUMBER OF RECORDS FOR TABLE CR : 4721918


CREATE TABLE CHR
(
ENT VARCHAR2(4 BYTE) NOT NULL,
SUB_ENT VARCHAR2(3 BYTE) NOT NULL,
HIER_TBL_NUM VARCHAR2(3 BYTE) NOT NULL,
HIER_ROLL VARCHAR2(14 BYTE) NOT NULL,
HIER_CODE VARCHAR2(14 BYTE) NOT NULL,
SUM_FLAG VARCHAR2(14 BYTE) NOT NULL,
CTR_OR_HIER VARCHAR2(14 BYTE) NOT NULL,
CTR_DETAIL VARCHAR2(14 BYTE) NOT NULL,
CTR_DESCR VARCHAR2(50 BYTE)
);


CREATE INDEX CHR_IDX_01 ON CHR
(HIER_TBL_NUM, HIER_ROLL, SUM_FLAG);


CREATE UNIQUE INDEX CHR_IDX_PK ON CHR
(ENT, SUB_ENT, HIER_TBL_NUM, HIER_ROLL, SUM_FLAG, 
CTR_DETAIL, CTR_OR_HIER, HIER_CODE);


CREATE INDEX CHR_IDX_02 ON CHR
(ENT, SUB_ENT, HIER_TBL_NUM, CTR_OR_HIER, SUM_FLAG, CTR_DETAIL);


TOTAL NUMBER OF RECORDS FOR TABLE CHR : 24151811


CREATE TABLE TAC
(
ENT VARCHAR2(4 BYTE),
SUB_ENT VARCHAR2(3 BYTE),
POST_ACCT VARCHAR2(9 BYTE),
CTR VARCHAR2(7 BYTE),
POST_DATE DATE,
EFF_DATE DATE,
TXN_CODE VARCHAR2(2 BYTE),
TXN_TYPE VARCHAR2(1 BYTE),
TXN_AMOUNT NUMBER(17,2),
TXN_DESCR VARCHAR2(46 BYTE),
TXN_SOURCE VARCHAR2(1 BYTE)
);


CREATE INDEX TAC_IDX_01 ON TAC
(ENT, SUB_ENT, POST_ACCT, POST_DATE, EFF_DATE, TXN_AMOUNT);


CREATE INDEX TAC_IDX_PK ON TAC
(ENT, SUB_ENT, CTR, POST_ACCT, POST_DATE, EFF_DATE, TXN_AMOUNT);


TOTAL NUMBER OF RECORDS FOR TABLE TAC : 111042301

===================================================================
I have the following query to be tuned..

SELECT SUBSTR(chr.hier_code, 1, 5),
      tmp_txm.post_acct,
      SUBSTR(tmp_txm.txn_descr, 1, 5),
      SUM
      (
        CASE
          WHEN tmp_txm.txn_code IN ('01', '10') THEN 1
                WHEN tmp_txm.txn_code IN ('02', '20') THEN -1
                ELSE 0
        END
        *
        CASE
          WHEN cr.acct_type IN ('01', '02', '03', '04', '05', '06') THEN 1
                WHEN cr.acct_type IN ('11', '12', '13', '14') THEN -1
                ELSE 0
        END
        *
        cr.signal_revert
        *
        tmp_txm.txn_amount
      )
    FROM
      tac. txn
        INNER JOIN
      chr
        ON
          tmp_txm.ent = chr.ent AND
          tmp_txm.sub_ent = chr.sub_ent AND
          tmp_txm.ctr = chr.ctr_detail
        INNER JOIN
      cr
        ON tmp_txm.post_acct = cr.post_acct
        INNER JOIN
      er
        ON
          chr.ent = er.ent AND
          chr.sub_ent = er.sub_ent
    WHERE
      tmp_txm.eff_date BETWEEN TO_DATE('2005'||'01', 'YYYYMM') AND LAST_DAY(TO_DATE('2005'||'01', 'YYYYMM')) AND
      chr.hier_tbl_num = '11111' AND
      chr.sum_flag = 'DEL AND
      chr.ctr_or_hier = chr.ctr_detail AND
      chr.hier_code BETWEEN 'A' AND 'Z' AND
      cr.rel_type = ' ' AND
      cr.acct_id = 'MGT' AND
      cr.acct_lvl = '9' AND
      er.roll_ent = '123' AND
      er.roll_sub_ent = '111'
    GROUP BY
      SUBSTR(chr.hier_code, 1, 5),
      tmp_txm.post_acct,
      SUBSTR(tmp_txm.txn_descr, 1, 5)
    HAVING
      SUM
      (
        CASE
          WHEN tmp_txm.txn_code IN ('01', '10') THEN 1
                WHEN tmp_txm.txn_code IN ('02', '20') THEN -1
                ELSE 0
        END
        *
        CASE
          WHEN cr.acct_type IN ('01', '02', '03', '04', '05', '06') THEN 1
                WHEN cr.acct_type IN ('11', '12', '13', '14') THEN -1
                ELSE 0
        END
        *
        cr.signal_revert
        *
        tmp_txm.txn_amount
      ) <> 0

How do we tune / re-write this query ? because its not at all returning results & have huge no of records for most of the table & taking such a quict long long time......


[moderator-2: formatted the pasted code, which, stragely, moderator-1 did not do.]





[Updated on: Sun, 10 February 2008 20:04] by Moderator

Report message to a moderator

Re: Query tunning required [message #299139 is a reply to message #299133] Sun, 10 February 2008 00:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code.
Use the "Preview Message" button to verify.
This has been told you in your previous topic.
Now read the section concerning Performances question and provide the requested information.
Also read How to Identify Performance Problem and Bottleneck .

Regards
Michel
Re: Query tunning required [message #299145 is a reply to message #299133] Sun, 10 February 2008 01:23 Go to previous messageGo to next message
moshea
Messages: 51
Registered: February 2008
Location: Dublin, Ireland
Member
Also : For posts like this one, Try and ensure that posted SQL statements are actually complete and valid.

When I tried to have a look at the statement, I hit 3 errors, before giving up ..

chr.sum_flag = 'DEL AND

tac. txn -- tac tmp_txm?

cr.signal_revert  -- cr doesn't have a signal_revert col


Performance Tuning can seem initially like somewhat of a daunting black art, however with a little bit of effort you should be able to get under the hood and understand many of the basics.

Read the links Michel has directed you towards, and then "have a play". I'd guess that the plethora of functions/cases etc in your statement are likely meaning that the Optimiser is unable to use (m)any indices. But you'll need to look at the explain plan to start to understand whats going on.

On the general point, I always find that starting by decomposing your statement into something much simpler (perhaps just starting with a couple of the tables), and gradually building your statement back up, and checking the explain plans along the way, is a great way to start to understand what the optimiser is doing and what the impact of each part of your statement actually is.

Michael

PS : I see from your post here, that you don't yet know how to generate an explain plan. I'd suggest that that would be a great place to start.

[Updated on: Sun, 10 February 2008 01:28]

Report message to a moderator

Re: Query tunning required [message #299183 is a reply to message #299145] Sun, 10 February 2008 08:43 Go to previous messageGo to next message
gksenthilkumar
Messages: 23
Registered: November 2007
Location: india
Junior Member
I have mistakenly entered that one, sorry for ....

below is the correct one for that...

chr.sum_flag = 'DEL' AND

tac. txn -- tac tmp_txm? yeah... tmp_txm is correct one..

cr.signal_revert -- this field is there in the table
Re: Query tunning required [message #299184 is a reply to message #299145] Sun, 10 February 2008 08:44 Go to previous messageGo to next message
gksenthilkumar
Messages: 23
Registered: November 2007
Location: india
Junior Member
And the Explain plan for the query is....
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
An uncaught error happened in fetching the records : ORA-30929: ORDER SIBLINGS B
Y clause not allowed here

ORA-30929: ORDER SIBLINGS BY clause not allowed here

SQL>
Re: Query tunning required [message #299186 is a reply to message #299184] Sun, 10 February 2008 09:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You must have a plan_table that is appropriate for your version (which one you didn't post althought it is requested).
You also must have, in this case, a client that is not in greater version than your server.

Regards
Michel
Re: Query tunning required [message #300682 is a reply to message #299145] Sun, 17 February 2008 16:18 Go to previous messageGo to next message
dee0704
Messages: 1
Registered: February 2008
Junior Member
I am junior And have very less experience.
I think it is cr.SIGN_REVRSL
Re: Query tunning required [message #300685 is a reply to message #300682] Sun, 17 February 2008 17:34 Go to previous message
gksenthilkumar
Messages: 23
Registered: November 2007
Location: india
Junior Member
yeah, exactly...
Previous Topic: Different partitioned table import
Next Topic: Query to be tuned which uses a table hint /*+ USE_CONCAT */ in Oracle 10g.
Goto Forum:
  


Current Time: Wed Dec 07 16:15:19 CST 2016

Total time taken to generate the page: 0.28810 seconds