Return-Path: Delivered-To: 2-oracle-l@orafaq.com Received: (qmail 7626 invoked from network); 10 Sep 2007 09:06:01 -0500 Received: from freelists-180.iquest.net (HELO turing.freelists.org) (206.53.239.180) by 69.64.49.119 with SMTP; 10 Sep 2007 09:06:01 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 53EE173C7B0; Mon, 10 Sep 2007 09:27:19 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 31688-02; Mon, 10 Sep 2007 09:27:19 -0400 (EDT) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id C41D773C741; Mon, 10 Sep 2007 09:27:18 -0400 (EDT) Received: with ECARTIS (v1.0.0; list oracle-l); Mon, 10 Sep 2007 08:42:48 -0400 (EDT) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 210BC73C9BD for ; Mon, 10 Sep 2007 08:42:48 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 22566-04 for ; Mon, 10 Sep 2007 08:42:48 -0400 (EDT) Received: from elm.usg.tufts.edu (elm.usg.tufts.edu [130.64.1.240]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id D8EEE73C9F2 for ; Mon, 10 Sep 2007 08:42:46 -0400 (EDT) Received: from dhcp251-198.medford.tufts.edu ([130.64.251.198]:4885 helo=tufts.edu) by elm.usg.tufts.edu with esmtps (TLSv1:AES256-SHA:256) (Exim 4.60) (envelope-from ) id 1IUjCc-0003t8-BC for ORACLE-L@freelists.org; Mon, 10 Sep 2007 09:21:22 -0400 Message-ID: <46E544D1.7070501@tufts.edu> Date: Mon, 10 Sep 2007 09:21:21 -0400 From: Joan Hsieh Organization: Tufts University User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.5) Gecko/20031007 X-Accept-Language: en-us, en MIME-Version: 1.0 To: oracle_l Subject: performance question Content-Type: text/plain; charset=us-ascii; format=flowed X-archive-position: 1375 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: joan.hsieh@tufts.edu Precedence: normal Reply-to: joan.hsieh@tufts.edu List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-subscribe: List-owner: List-post: List-archive: X-list: oracle-l X-Virus-Scanned: Debian amavisd-new at localhost.localdomain Dear list, Our peoplesoft HR system recently upgraded from 9i to 10.2.0.3. We did some statistics adjustment to solve one major sql statement. However, we had another performace issue last week for a newly sqr statement. I am confused after I had 10046 trace. Here is the story. QA and TRN two database, same release on same server. All the parameters are same, except QA has 200m sga target and TRN has 500m sqa target size. Same sql statement ran on QA without any problem, but failed on HRN with run out of pstemp temporary tablepspace error. I took 10046 trace on both databases, the excuecution plan are identical which puzzled me. Why the returned rows are so different from each other with the same plan. QA database has less rows than TRN, but not in a big gap. I hope someone can shed some light on this. Thanks,----Joan QA XCTEND rlbk=0, rd_only=1 STAT #2 id=1 cnt=8538 pid=0 pos=1 obj=0 op='SORT ORDER BY (cr=238458 pr=40823 pw=4941 time=57337179 us)' STAT #2 id=2 cnt=8538 pid=1 pos=1 obj=0 op='CONCATENATION (cr=238458 pr=40823 pw=4941 time=57349483 us)' STAT #2 id=3 cnt=0 pid=2 pos=1 obj=0 op='MERGE JOIN CARTESIAN (cr=21201 pr=17745 pw=0 time=15426348 us)' STAT #2 id=4 cnt=0 pid=3 pos=1 obj=0 op='MERGE JOIN CARTESIAN (cr=21201 pr=17745 pw=0 time=15426332 us)' STAT #2 id=5 cnt=0 pid=4 pos=1 obj=0 op='MERGE JOIN CARTESIAN (cr=21201 pr=17745 pw=0 time=15426321 us)' STAT #2 id=6 cnt=0 pid=5 pos=1 obj=61795 op='TABLE ACCESS FULL PS_JOB (cr=21201 pr=17745 pw=0 time=15426306 us)' STAT #2 id=7 cnt=0 pid=5 pos=2 obj=0 op='BUFFER SORT (cr=0 pr=0 pw=0 time=0 us)' STAT #2 id=8 cnt=0 pid=7 pos=1 obj=58001 op='TABLE ACCESS FULL PS_EMPLOYMENT (cr=0 pr=0 pw=0 time=0 us)' STAT #2 id=9 cnt=0 pid=4 pos=2 obj=0 op='BUFFER SORT (cr=0 pr=0 pw=0 time=0 us)' STAT #2 id=10 cnt=0 pid=9 pos=1 obj=106102 op='TABLE ACCESS FULL PS_TFTH_JOB (cr=0 pr=0 pw=0 time=0 us)' STAT #2 id=11 cnt=0 pid=3 pos=2 obj=0 op='BUFFER SORT (cr=0 pr=0 pw=0 time=0 us)' STAT #2 id=12 cnt=0 pid=11 pos=1 obj=64184 op='INDEX FAST FULL SCAN PS0PERSONAL_DATA (cr=0 pr=0 pw=0 time=0 us)' STAT #2 id=13 cnt=8538 pid=2 pos=2 obj=0 op='HASH JOIN (cr=217257 pr=23078 pw=4941 time=41906027 us)' TRN XCTEND rlbk=0, rd_only=1 STAT #1 id=1 cnt=0 pid=0 pos=1 obj=0 op='SORT ORDER BY (cr=0 pr=0 pw=0 time=59 us)' STAT #1 id=2 cnt=12229423 pid=1 pos=1 obj=0 op='CONCATENATION (cr=23627 pr=21331 pw=1321 time=134505041 us)' STAT #1 id=3 cnt=12229423 pid=2 pos=1 obj=0 op='MERGE JOIN CARTESIAN (cr=23627 pr=21331 pw=1321 time=110046190 us)' STAT #1 id=4 cnt=381 pid=3 pos=1 obj=0 op='MERGE JOIN CARTESIAN (cr=23466 pr=21331 pw=1321 time=72639302 us)' STAT #1 id=5 cnt=1 pid=4 pos=1 obj=0 op='MERGE JOIN CARTESIAN (cr=21322 pr=21296 pw=0 time=64436968 us)' STAT #1 id=6 cnt=1 pid=5 pos=1 obj=61795 op='TABLE ACCESS FULL PS_JOB (cr=20187 pr=20165 pw=0 time=60311025 us)' STAT #1 id=7 cnt=1 pid=5 pos=2 obj=0 op='BUFFER SORT (cr=1135 pr=1131 pw=0 time=4125923 us)' STAT #1 id=8 cnt=43121 pid=7 pos=1 obj=58001 op='TABLE ACCESS FULL PS_EMPLOYMENT (cr=1135 pr=1131 pw=0 time=6067916 us)' STAT #1 id=9 cnt=381 pid=4 pos=2 obj=0 op='BUFFER SORT (cr=2144 pr=35 pw=1321 time=8200036 us)' STAT #1 id=10 cnt=266950 pid=9 pos=1 obj=106513 op='TABLE ACCESS FULL PS_TFTH_JOB (cr=2144 pr=21 pw=0 time=9623843 us)' STAT #1 id=11 cnt=12229423 pid=3 pos=2 obj=0 op='BUFFER SORT (cr=161 pr=0 pw=0 time=25192169 us)' STAT #1 id=12 cnt=32141 pid=11 pos=1 obj=64184 op='INDEX FAST FULL SCAN PS0PERSONAL_DATA (cr=161 pr=0 pw=0 time=3164790 us)' STAT #1 id=13 cnt=0 pid=2 pos=2 obj=0 op='HASH JOIN (cr=0 pr=0 pw=0 time=0 us)' -- http://www.freelists.org/webpage/oracle-l