OraFAQ Forum: Performance Tuning » Tuning in Hierarchical queries

Home » RDBMS Server » Performance Tuning » Tuning in Hierarchical queries (Oracle 10.2.0.2.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Tuning in Hierarchical queries [message #531812]

Thu, 17 November 2011 15:19

praveenkumarsr
Messages: 3
Registered: September 2011

Junior Member

Hi All,

We have a big hierarchical query which is now running for a long time (around 6 hours. earlier it was running for 3 hours). We have to tune this query so that we run the jobs within a stipulated time frame.

The query below inserts around 42 million records in to the table WK_ACCT_WSTORE. Please suggest/help in tuning this query. I have attached in the text file.

Thanks,
Praveen.

Attachment: Query.txt
(Size: 5.84KB, Downloaded 1661 times)

Report message to a moderator

Re: Tuning in Hierarchical queries [message #531814 is a reply to message #531812]

Thu, 17 November 2011 15:30

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

http://www.orafaq.com/forum/m/433888/136107/#msg_433888
Please refer to URL above & be sure to provide the details requested:

Report message to a moderator

Re: Tuning in Hierarchical queries [message #531960 is a reply to message #531812]

Fri, 18 November 2011 15:27

praveenkumarsr
Messages: 3
Registered: September 2011

Junior Member

I have prepared all the details in the text file in the order listed below and attached.

Please let me know for further information.

Attachment: Details.txt
(Size: 172.70KB, Downloaded 1515 times)

Report message to a moderator

Re: Tuning in Hierarchical queries [message #531974 is a reply to message #531960]

Fri, 18 November 2011 18:48

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

EXplain Plan
------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2356927141
-------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                      |                     |   285 | 22515 |   266K  (1)| 00:12:24

above shows duration 12 minutes 24 seconds & returned 285 rows
I can not reconcile what exists above with what exists below.
below shows duration 127.66 seconds (2+ minutes) & returned 0 zero

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.03       0.06          0          0          0           0

Execute      1     81.28     127.60      10227      50159          0           0

Fetch        0      0.00       0.00          0          0          0           0

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total        2     81.31     127.66      10227      50159          0           0

>(around 6 hours. earlier it was running for 3 hours).
You report hours & all I see a low number of minutes.
Why the discrepancy?

Report message to a moderator

Re: Tuning in Hierarchical queries [message #531975 is a reply to message #531974]

Fri, 18 November 2011 19:12

praveenkumarsr
Messages: 3
Registered: September 2011

Junior Member

These details were gathered from the Testing Environment. Because it is very difficult for us to gather such details in Production as the Production environment will be busy 24x7.

Will this information work or the exact data from Production environment is required? Both Explain Plan and Trace Results?

Report message to a moderator

Previous Topic:	Need help for query tunning the below query
Next Topic:	Definition between 2 metrics "Physical Reads"

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 02:55:39 CDT 2024