Home » SQL & PL/SQL » SQL & PL/SQL » masiv Query Blocks on Statement
icon9.gif  masiv Query Blocks on Statement [message #192532] Tue, 12 September 2006 14:38 Go to next message
flat
Messages: 4
Registered: September 2006
Junior Member
Hi !
The following Environments are given:
both RDBMS are Oracle 9.2.0.7 (Optimizer: RULE)

Problem:
On Statments runs on a Testenvironment 1 minute.
On Live it runs near 5 minutes.

SGA on Test-Env is set to 2 GB.
SGA on Live is 6 GB.

Unfortunately i cannot use the CBO because the Application is not support this. So we still forced to use the RBO.

A trace of the Statement returns the same Explainplan and nearly the same disk reads back.
The different between the trace-files are havy Query Blocks on the Live Environment.

Query-Blocks: 1422899 on Live
Query-Blocks: 422321 on Test

The Table which is responsible for that will be accessed by both environments with FTS (over 2.3 GB Data)

To reduces the Query Blocks in Live i created a copy of this bis table (create table as select) ans run the statment again with the new copy of the table. nothing changed on this.
The performance is still poor.

Both environments have set "db_file_multiblock_read_count" to 16

Can someone tell me why there are this masiv Query Block reads on Live environment.
How to prevent these ?

regards, flat

[Updated on: Wed, 13 September 2006 07:08]

Report message to a moderator

Re: masiv Query Blocks on Statement [message #192646 is a reply to message #192532] Wed, 13 September 2006 02:37 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Could you post the TK*Prof output of the offending statement in both Test and Prod. This will prove to us that the two queries return the same (or similar) number of rows, use the same plan, and will also demonstrate the differences in resources used.

Ross Leishman
Re: masiv Query Blocks on Statement [message #192678 is a reply to message #192532] Wed, 13 September 2006 04:19 Go to previous messageGo to next message
flat
Messages: 4
Registered: September 2006
Junior Member
HI !

The live and Test-Table are not the same.
Test-Env:
--3956171 rows
--2191 MB

Live:
--4177239 rows
--2319 MB
The HWM is nearly the same.

Here is the

Here is the TKProf output from Live:
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.36       0.34          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1     68.96     282.18     270091    1432217          0          13
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4     69.32     282.53     270091    1432217          0          13

Misses in library cache during parse: 1
Optimizer goal: RULE
Parsing user id: 605  (S)

Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: RULE
      0   NESTED LOOPS (OUTER)
      0    NESTED LOOPS (OUTER)
      0     NESTED LOOPS (OUTER)
      0      NESTED LOOPS (OUTER)
      0       NESTED LOOPS (OUTER)
      0        NESTED LOOPS (OUTER)
      0         NESTED LOOPS (OUTER)
      0          NESTED LOOPS (OUTER)
      0           NESTED LOOPS (OUTER)
      0            NESTED LOOPS (OUTER)
      0             NESTED LOOPS (OUTER)
      0              NESTED LOOPS (OUTER)
      0               NESTED LOOPS (OUTER)
      0                NESTED LOOPS
      0                 NESTED LOOPS (OUTER)
      0                  NESTED LOOPS (OUTER)
      0                   NESTED LOOPS (OUTER)
      0                    NESTED LOOPS (OUTER)
      0                     NESTED LOOPS (OUTER)
      0                      NESTED LOOPS (OUTER)
      0                       NESTED LOOPS (OUTER)
      0                        NESTED LOOPS (OUTER)
      0                         NESTED LOOPS (OUTER)

      0                          NESTED LOOPS 
                                     (OUTER)
      0                           NESTED LOOPS 
                                      (OUTER)
      0                            NESTED LOOPS 
                                       (OUTER)
      0                             NESTED LOOPS 
                                        (OUTER)
      0                              NESTED 
                                         LOOPS (OUTER)
      0                               NESTED 
                                          LOOPS (OUTER)
      0                                NESTED 
                                           LOOPS (OUTER)
      0                                 
                                            NESTED LOOPS (OUTER)
      0                                  
                                             NESTED LOOPS (OUTER)
      0                                   
                                            TABLE ACCESS (FULL) OF 
                                              'S_SRV_REQ'
      0                                   
                                            TABLE ACCESS (BY INDEX ROWID) 
                                              OF 'S_ORG_EXT'
      0                                   
                                              INDEX (UNIQUE SCAN) OF 
                                               'S_ORG_EXT_U3' (UNIQUE)
      0                                  
                                           TABLE ACCESS (BY INDEX ROWID) OF 
                                             'S_ASSET'
      0                                   
                                            INDEX (UNIQUE SCAN) OF 
                                              'S_ASSET_P1' (UNIQUE)
      0                                 
                                          TABLE ACCESS (BY INDEX ROWID) OF 
                                            'S_ASSET'
      0                                  
                                           INDEX (UNIQUE SCAN) OF 
                                             'S_ASSET_P1' (UNIQUE)
      0                                TABLE 
                                         ACCESS (BY INDEX ROWID) OF 
                                           'S_ADDR_PER'
      0                                 
                                          INDEX (UNIQUE SCAN) OF 
                                            'S_ADDR_PER_P1' (UNIQUE)
      0                               TABLE 
                                        ACCESS (BY INDEX ROWID) OF 
                                          'S_PRI_LST'
      0                                INDEX 
                                         (UNIQUE SCAN) OF 'S_PRI_LST_P1' 
                                           (UNIQUE)
      0                              TABLE 
                                       ACCESS (BY INDEX ROWID) OF 
                                         'S_PRI_LST'
      0                               INDEX 
                                        (UNIQUE SCAN) OF 'S_PRI_LST_P1' 
                                          (UNIQUE)
      0                             TABLE ACCESS 
                                        (BY INDEX ROWID) OF 'S_ENTLMNT'
      0                              INDEX 
                                       (UNIQUE SCAN) OF 'S_ENTLMNT_P1' 
                                         (UNIQUE)
      0                            TABLE ACCESS 
                                       (BY INDEX ROWID) OF 'S_ORG_EXT'
      0                             INDEX 
                                      (UNIQUE SCAN) OF 'S_ORG_EXT_U3' 
                                        (UNIQUE)
      0                           TABLE ACCESS (BY 
                                      INDEX ROWID) OF 'S_ORG_EXT'
      0                            INDEX (UNIQUE 
                                       SCAN) OF 'S_ORG_EXT_U3' (UNIQUE)
      0                          TABLE ACCESS   
                                   GOAL: ANALYZED (BY INDEX ROWID) OF 
                                     'S_PROD_INT'
      0                           INDEX   GOAL: 
                                    ANALYZED (UNIQUE SCAN) OF 
                                      'S_PROD_INT_P1' (UNIQUE)
      0                         TABLE ACCESS (BY 
                                    INDEX ROWID) OF 'S_ORG_EXT'
      0                          INDEX (UNIQUE SCAN)
                                      OF 'S_ORG_EXT_U3' (UNIQUE)
      0                        TABLE ACCESS (BY INDEX 
                                   ROWID) OF 'S_ADDR_PER'
      0                         INDEX (UNIQUE SCAN) 
                                    OF 'S_ADDR_PER_P1' (UNIQUE)
      0                       TABLE ACCESS (BY INDEX 
                                  ROWID) OF 'S_CONTACT'
      0                        INDEX   GOAL: ANALYZED 
                                   (UNIQUE SCAN) OF 'S_CONTACT_U2' (UNIQUE)
      0                      TABLE ACCESS (BY INDEX 
                                 ROWID) OF 'S_TIMEZONE'
      0                       INDEX (UNIQUE SCAN) OF 
                                  'S_TIMEZONE_P1' (UNIQUE)
      0                     TABLE ACCESS (BY INDEX ROWID)
                                 OF 'S_ORG_EXT'
      0                      INDEX (UNIQUE SCAN) OF 
                                 'S_ORG_EXT_U3' (UNIQUE)
      0                    TABLE ACCESS (BY INDEX ROWID) 
                               OF 'S_ADDR_PER'
      0                     INDEX (UNIQUE SCAN) OF 
                                'S_ADDR_PER_P1' (UNIQUE)
      0                   TABLE ACCESS (BY INDEX ROWID) OF 
                              'S_ORG_EXT'
      0                    INDEX (UNIQUE SCAN) OF 
                               'S_ORG_EXT_U3' (UNIQUE)
      0                  TABLE ACCESS (BY INDEX ROWID) OF 
                             'S_SRV_REQ'
      0                   INDEX (UNIQUE SCAN) OF 
                              'S_SRV_REQ_P1' (UNIQUE)
      0                 TABLE ACCESS   GOAL: ANALYZED (BY 
                            INDEX ROWID) OF 'S_USER'
      0                  INDEX   GOAL: ANALYZED (UNIQUE 
                             SCAN) OF 'S_USER_U2' (UNIQUE)
      0                TABLE ACCESS   GOAL: ANALYZED (BY 
                           INDEX ROWID) OF 'S_USER'
      0                 INDEX   GOAL: ANALYZED (UNIQUE SCAN) 
                            OF 'S_USER_U2' (UNIQUE)
      0               TABLE ACCESS   GOAL: ANALYZED (BY INDEX 
                          ROWID) OF 'S_USER'
      0                INDEX   GOAL: ANALYZED (UNIQUE SCAN) 
                           OF 'S_USER_U2' (UNIQUE)
      0              TABLE ACCESS (BY INDEX ROWID) OF 
                         'S_ORG_EXT'
      0               INDEX (UNIQUE SCAN) OF 'S_ORG_EXT_U3' 
                          (UNIQUE)
      0             TABLE ACCESS (BY INDEX ROWID) OF 
                        'S_SRV_REQ4_FNX'
      0              INDEX (RANGE SCAN) OF 'S_SRV_REQ4_FNX_U1' 
                         (UNIQUE)
      0            TABLE ACCESS (BY INDEX ROWID) OF 
                       'S_CONTACT_FNX'
      0             INDEX (RANGE SCAN) OF 'S_CONTACT_FNX_U1' 
                        (UNIQUE)
      0           TABLE ACCESS (BY INDEX ROWID) OF 'S_SRV_REQ_X'
      0            INDEX (RANGE SCAN) OF 'S_SRV_REQ_X_U1' (UNIQUE)

      0          TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF 
                     'S_USER'
      0           INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 
                      'S_USER_U2' (UNIQUE)
      0         TABLE ACCESS (BY INDEX ROWID) OF 'S_CONTACT'
      0          INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 
                     'S_CONTACT_U2' (UNIQUE)
      0        TABLE ACCESS (BY INDEX ROWID) OF 'S_PARTY'
      0         INDEX (UNIQUE SCAN) OF 'S_PARTY_P1' (UNIQUE)
      0       TABLE ACCESS (BY INDEX ROWID) OF 'S_POSTN'
      0        INDEX (UNIQUE SCAN) OF 'S_POSTN_U2' (UNIQUE)
      0      TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF 
                 'S_USER'
      0       INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 'S_USER_U2' 
                  (UNIQUE)
      0     TABLE ACCESS (BY INDEX ROWID) OF 'S_SRV_REQ3_FNX'
      0      INDEX (RANGE SCAN) OF 'S_SRV_REQ3_FNX_U1' (UNIQUE)
      0    TABLE ACCESS (BY INDEX ROWID) OF 'S_SRV_REQ1_FNX'
      0     INDEX (RANGE SCAN) OF 'S_SRV_REQ1_FNX_U1' (UNIQUE)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       3        0.00          0.00
  SQL*Net more data to client                    12        0.00          0.00
  SQL*Net message from client                     2        0.04          0.08
  SQL*Net more data from client                   3        0.00          0.00
  db file scattered read                      19653        0.28        213.39
  db file sequential read                      4646        0.17         13.87
  latch free                                      5        0.00          0.00





This is the output from TEST:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.42       0.41          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1     43.82      69.40     277108     422321          0          37
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4     44.24      69.82     277108     422321          0          37

Misses in library cache during parse: 1
Optimizer goal: RULE
Parsing user id: 605  (S)

Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: RULE
      0   NESTED LOOPS (OUTER)
      0    NESTED LOOPS (OUTER)
      0     NESTED LOOPS (OUTER)
      0      NESTED LOOPS (OUTER)
      0       NESTED LOOPS (OUTER)
      0        NESTED LOOPS (OUTER)
      0         NESTED LOOPS (OUTER)
      0          NESTED LOOPS (OUTER)
      0           NESTED LOOPS (OUTER)
      0            NESTED LOOPS (OUTER)
      0             NESTED LOOPS (OUTER)
      0              NESTED LOOPS (OUTER)
      0               NESTED LOOPS (OUTER)
      0                NESTED LOOPS
      0                 NESTED LOOPS (OUTER)
      0                  NESTED LOOPS (OUTER)
      0                   NESTED LOOPS (OUTER)
      0                    NESTED LOOPS (OUTER)
      0                     NESTED LOOPS (OUTER)
      0                      NESTED LOOPS (OUTER)
      0                       NESTED LOOPS (OUTER)
      0                        NESTED LOOPS (OUTER)
      0                         NESTED LOOPS (OUTER)

      0                          NESTED LOOPS 
                                     (OUTER)
      0                           NESTED LOOPS 
                                      (OUTER)
      0                            NESTED LOOPS 
                                       (OUTER)
      0                             NESTED LOOPS 
                                        (OUTER)
      0                              NESTED 
                                         LOOPS (OUTER)
      0                               NESTED 
                                          LOOPS (OUTER)
      0                                NESTED 
                                           LOOPS (OUTER)
      0                                 
                                            NESTED LOOPS (OUTER)
      0                                  
                                             NESTED LOOPS (OUTER)
      0                                   
                                            TABLE ACCESS (FULL) OF 
                                              'S_SRV_REQ'
      0                                   
                                            TABLE ACCESS (BY INDEX ROWID) 
                                              OF 'S_ORG_EXT'
      0                                   
                                              INDEX (UNIQUE SCAN) OF 
                                               'S_ORG_EXT_U3' (UNIQUE)
      0                                  
                                           TABLE ACCESS (BY INDEX ROWID) OF 
                                             'S_ASSET'
      0                                   
                                            INDEX (UNIQUE SCAN) OF 
                                              'S_ASSET_P1' (UNIQUE)
      0                                 
                                          TABLE ACCESS (BY INDEX ROWID) OF 
                                            'S_ASSET'
      0                                  
                                           INDEX (UNIQUE SCAN) OF 
                                             'S_ASSET_P1' (UNIQUE)
      0                                TABLE 
                                         ACCESS (BY INDEX ROWID) OF 
                                           'S_ADDR_PER'
      0                                 
                                          INDEX (UNIQUE SCAN) OF 
                                            'S_ADDR_PER_P1' (UNIQUE)
      0                               TABLE 
                                        ACCESS (BY INDEX ROWID) OF 
                                          'S_PRI_LST'
      0                                INDEX 
                                         (UNIQUE SCAN) OF 'S_PRI_LST_P1' 
                                           (UNIQUE)
      0                              TABLE 
                                       ACCESS (BY INDEX ROWID) OF 
                                         'S_PRI_LST'
      0                               INDEX 
                                        (UNIQUE SCAN) OF 'S_PRI_LST_P1' 
                                          (UNIQUE)
      0                             TABLE ACCESS 
                                        (BY INDEX ROWID) OF 'S_ENTLMNT'
      0                              INDEX 
                                       (UNIQUE SCAN) OF 'S_ENTLMNT_P1' 
                                         (UNIQUE)
      0                            TABLE ACCESS 
                                       (BY INDEX ROWID) OF 'S_ORG_EXT'
      0                             INDEX 
                                      (UNIQUE SCAN) OF 'S_ORG_EXT_U3' 
                                        (UNIQUE)
      0                           TABLE ACCESS (BY 
                                      INDEX ROWID) OF 'S_ORG_EXT'
      0                            INDEX (UNIQUE 
                                       SCAN) OF 'S_ORG_EXT_U3' (UNIQUE)
      0                          TABLE ACCESS   
                                   GOAL: ANALYZED (BY INDEX ROWID) OF 
                                     'S_PROD_INT'
      0                           INDEX   GOAL: 
                                    ANALYZED (UNIQUE SCAN) OF 
                                      'S_PROD_INT_P1' (UNIQUE)
      0                         TABLE ACCESS (BY 
                                    INDEX ROWID) OF 'S_ORG_EXT'
      0                          INDEX (UNIQUE SCAN)
                                      OF 'S_ORG_EXT_U3' (UNIQUE)
      0                        TABLE ACCESS (BY INDEX 
                                   ROWID) OF 'S_ADDR_PER'
      0                         INDEX (UNIQUE SCAN) 
                                    OF 'S_ADDR_PER_P1' (UNIQUE)
      0                       TABLE ACCESS (BY INDEX 
                                  ROWID) OF 'S_CONTACT'
      0                        INDEX   GOAL: ANALYZED 
                                   (UNIQUE SCAN) OF 'S_CONTACT_U2' (UNIQUE)
      0                      TABLE ACCESS (BY INDEX 
                                 ROWID) OF 'S_TIMEZONE'
      0                       INDEX (UNIQUE SCAN) OF 
                                  'S_TIMEZONE_P1' (UNIQUE)
      0                     TABLE ACCESS (BY INDEX ROWID)
                                 OF 'S_ORG_EXT'
      0                      INDEX (UNIQUE SCAN) OF 
                                 'S_ORG_EXT_U3' (UNIQUE)
      0                    TABLE ACCESS (BY INDEX ROWID) 
                               OF 'S_ADDR_PER'
      0                     INDEX (UNIQUE SCAN) OF 
                                'S_ADDR_PER_P1' (UNIQUE)
      0                   TABLE ACCESS (BY INDEX ROWID) OF 
                              'S_ORG_EXT'
      0                    INDEX (UNIQUE SCAN) OF 
                               'S_ORG_EXT_U3' (UNIQUE)
      0                  TABLE ACCESS (BY INDEX ROWID) OF 
                             'S_SRV_REQ'
      0                   INDEX (UNIQUE SCAN) OF 
                              'S_SRV_REQ_P1' (UNIQUE)
      0                 TABLE ACCESS   GOAL: ANALYZED (BY 
                            INDEX ROWID) OF 'S_USER'
      0                  INDEX   GOAL: ANALYZED (UNIQUE 
                             SCAN) OF 'S_USER_U2' (UNIQUE)
      0                TABLE ACCESS   GOAL: ANALYZED (BY 
                           INDEX ROWID) OF 'S_USER'
      0                 INDEX   GOAL: ANALYZED (UNIQUE SCAN) 
                            OF 'S_USER_U2' (UNIQUE)
      0               TABLE ACCESS   GOAL: ANALYZED (BY INDEX 
                          ROWID) OF 'S_USER'
      0                INDEX   GOAL: ANALYZED (UNIQUE SCAN) 
                           OF 'S_USER_U2' (UNIQUE)
      0              TABLE ACCESS (BY INDEX ROWID) OF 
                         'S_ORG_EXT'
      0               INDEX (UNIQUE SCAN) OF 'S_ORG_EXT_U3' 
                          (UNIQUE)
      0             TABLE ACCESS (BY INDEX ROWID) OF 
                        'S_SRV_REQ4_FNX'
      0              INDEX (RANGE SCAN) OF 'S_SRV_REQ4_FNX_U1' 
                         (UNIQUE)
      0            TABLE ACCESS (BY INDEX ROWID) OF 
                       'S_CONTACT_FNX'
      0             INDEX (RANGE SCAN) OF 'S_CONTACT_FNX_U1' 
                        (UNIQUE)
      0           TABLE ACCESS (BY INDEX ROWID) OF 'S_SRV_REQ_X'
      0            INDEX (RANGE SCAN) OF 'S_SRV_REQ_X_U1' (UNIQUE)

      0          TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF 
                     'S_USER'
      0           INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 
                      'S_USER_U2' (UNIQUE)
      0         TABLE ACCESS (BY INDEX ROWID) OF 'S_CONTACT'
      0          INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 
                     'S_CONTACT_U2' (UNIQUE)
      0        TABLE ACCESS (BY INDEX ROWID) OF 'S_PARTY'
      0         INDEX (UNIQUE SCAN) OF 'S_PARTY_P1' (UNIQUE)
      0       TABLE ACCESS (BY INDEX ROWID) OF 'S_POSTN'
      0        INDEX (UNIQUE SCAN) OF 'S_POSTN_U2' (UNIQUE)
      0      TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF 
                 'S_USER'
      0       INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 'S_USER_U2' 
                  (UNIQUE)
      0     TABLE ACCESS (BY INDEX ROWID) OF 'S_SRV_REQ3_FNX'
      0      INDEX (RANGE SCAN) OF 'S_SRV_REQ3_FNX_U1' (UNIQUE)
      0    TABLE ACCESS (BY INDEX ROWID) OF 'S_SRV_REQ1_FNX'
      0     INDEX (RANGE SCAN) OF 'S_SRV_REQ1_FNX_U1' (UNIQUE)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       3        0.00          0.00
  SQL*Net more data to client                    23        0.00          0.00
  SQL*Net message from client                     3       12.08         12.17
  SQL*Net more data from client                   3        0.00          0.00
  db file scattered read                      17599        0.03         37.66
  db file sequential read                        54        0.00          0.00
********************************************************************************




Re: masiv Query Blocks on Statement [message #192843 is a reply to message #192678] Wed, 13 September 2006 21:12 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Since there are a great many tables here, I guess you are talking about S_SRV_REQ - the one that is FULL scanned.

A single FULL scan generally results in high DISK (blocks read from disk) rather than QUERY (buffers from cache). Also, since you mention that you rebuilt the Live table, and the size of the tables is about the same, i think we can completely rule out any HWM problem.

A higher QUERY number on one environment means (most likely) that it was reading more blocks from an INDEX. There are two causes that come to mind:

- One of the other tables in your query that is read by an INDEX RANGE SCAN has some particular data in Live that is not in Test. Most of your Index Scans are UNIQUE - these would not be the cause. There are only a few RANGE scans:
: S_SRV_REQ4_FNX
: S_CONTACT_FNX
: S_SRV_REQ_X
: S_SRV_REQ3_FNX
: S_SRV_REQ1_FNX
so it shouldn't be too hard to check. The sort of thing that would cause such a problem is a skewed join key to (say) S_CONTACT_FNX. Say there was a special join-key of 0 where there was 100 matching rows in Test, but 1M matching rows in Prod.

- One of your (range scan) indexes may be fragmented or subtly corrupted, and needs to be rebuilt. I wrote a post on this in the Server Administration forum, but I can't find it now. You could just rebuild the 5 range-scan indexes to eliminate this likelihood.

Next step - your Explain Plans do not have row counts on them. It may be that you did not close the cursor before you generated the TK*Prof output. Could you ensure that you perform all of the fetches and close the cursor, and make sure that you get the row counts. If there is a significant difference in row count for one step in the plan, that will narrow down your search.

Ross Leishman
Re: masiv Query Blocks on Statement [message #193031 is a reply to message #192532] Thu, 14 September 2006 10:38 Go to previous messageGo to next message
flat
Messages: 4
Registered: September 2006
Junior Member
HI !

i have problems to "close" the cursor ?
after tracing i closed the session.
isn´t the cursor closed by this corretly ?

i can´t get any informations about the rows in the explain plan by tkprof.
but in raw tracefile i found the following interesting infos:

TEST:

STAT #3 id=1 cnt=37 pid=0 pos=1 obj=0 op='NESTED LOOPS OUTER '
STAT #3 id=2 cnt=37 pid=1 pos=1 obj=0 op='NESTED LOOPS OUTER '
STAT #3 id=3 cnt=37 pid=2 pos=1 obj=0 op='NESTED LOOPS OUTER '
STAT #3 id=4 cnt=37 pid=3 pos=1 obj=0 op='NESTED LOOPS OUTER '
STAT #3 id=5 cnt=37 pid=4 pos=1 obj=0 op='NESTED LOOPS OUTER '
STAT #3 id=6 cnt=37 pid=5 pos=1 obj=0 op='NESTED LOOPS OUTER '
STAT #3 id=7 cnt=37 pid=6 pos=1 obj=0 op='NESTED LOOPS OUTER '
STAT #3 id=8 cnt=37 pid=7 pos=1 obj=0 op='NESTED LOOPS OUTER '
STAT #3 id=9 cnt=37 pid=8 pos=1 obj=0 op='NESTED LOOPS OUTER '
STAT #3 id=10 cnt=37 pid=9 pos=1 obj=0 op='NESTED LOOPS OUTER '
STAT #3 id=11 cnt=37 pid=10 pos=1 obj=0 op='NESTED LOOPS OUTER '
STAT #3 id=12 cnt=37 pid=11 pos=1 obj=0 op='NESTED LOOPS OUTER '
STAT #3 id=13 cnt=37 pid=12 pos=1 obj=0 op='NESTED LOOPS OUTER '
STAT #3 id=14 cnt=37 pid=13 pos=1 obj=0 op='NESTED LOOPS '
STAT #3 id=15 cnt=24748 pid=14 pos=1 obj=0 op='NESTED LOOPS OUTER '
STAT #3 id=16 cnt=24748 pid=15 pos=1 obj=0 op='NESTED LOOPS OUTER '
STAT #3 id=17 cnt=24748 pid=16 pos=1 obj=0 op='NESTED LOOPS OUTER '
STAT #3 id=18 cnt=24748 pid=17 pos=1 obj=0 op='NESTED LOOPS OUTER '
STAT #3 id=19 cnt=24748 pid=18 pos=1 obj=0 op='NESTED LOOPS OUTER '
STAT #3 id=20 cnt=24748 pid=19 pos=1 obj=0 op='NESTED LOOPS OUTER '
STAT #3 id=21 cnt=24748 pid=20 pos=1 obj=0 op='NESTED LOOPS OUTER '
STAT #3 id=22 cnt=24748 pid=21 pos=1 obj=0 op='NESTED LOOPS OUTER '
STAT #3 id=23 cnt=24748 pid=22 pos=1 obj=0 op='NESTED LOOPS OUTER '
STAT #3 id=24 cnt=24748 pid=23 pos=1 obj=0 op='NESTED LOOPS OUTER '
STAT #3 id=25 cnt=24748 pid=24 pos=1 obj=0 op='NESTED LOOPS OUTER '
STAT #3 id=26 cnt=24748 pid=25 pos=1 obj=0 op='NESTED LOOPS OUTER '
STAT #3 id=27 cnt=24748 pid=26 pos=1 obj=0 op='NESTED LOOPS OUTER '
STAT #3 id=28 cnt=24748 pid=27 pos=1 obj=0 op='NESTED LOOPS OUTER '
STAT #3 id=29 cnt=24748 pid=28 pos=1 obj=0 op='NESTED LOOPS OUTER '
STAT #3 id=30 cnt=24748 pid=29 pos=1 obj=0 op='NESTED LOOPS OUTER '
STAT #3 id=31 cnt=24748 pid=30 pos=1 obj=0 op='NESTED LOOPS OUTER '
STAT #3 id=32 cnt=24748 pid=31 pos=1 obj=0 op='NESTED LOOPS OUTER '
STAT #3 id=33 cnt=24748 pid=32 pos=1 obj=121509 op='TABLE ACCESS FULL S_SRV_REQ '
STAT #3 id=34 cnt=1467 pid=32 pos=2 obj=118297 op='TABLE ACCESS BY INDEX ROWID S_ORG_EXT '
STAT #3 id=35 cnt=1467 pid=34 pos=1 obj=118347 op='INDEX UNIQUE SCAN S_ORG_EXT_U3 '
STAT #3 id=36 cnt=2328 pid=31 pos=2 obj=111462 op='TABLE ACCESS BY INDEX ROWID S_ASSET '
STAT #3 id=37 cnt=2328 pid=36 pos=1 obj=111484 op='INDEX UNIQUE SCAN S_ASSET_P1 '
STAT #3 id=38 cnt=2073 pid=30 pos=2 obj=111462 op='TABLE ACCESS BY INDEX ROWID S_ASSET '
STAT #3 id=39 cnt=2073 pid=38 pos=1 obj=111484 op='INDEX UNIQUE SCAN S_ASSET_P1 '
STAT #3 id=40 cnt=1356 pid=29 pos=2 obj=110986 op='TABLE ACCESS BY INDEX ROWID S_ADDR_PER '
STAT #3 id=41 cnt=1356 pid=40 pos=1 obj=110996 op='INDEX UNIQUE SCAN S_ADDR_PER_P1 '
STAT #3 id=42 cnt=0 pid=28 pos=2 obj=119336 op='TABLE ACCESS BY INDEX ROWID S_PRI_LST '
STAT #3 id=43 cnt=0 pid=42 pos=1 obj=119343 op='INDEX UNIQUE SCAN S_PRI_LST_P1 '
STAT #3 id=44 cnt=0 pid=27 pos=2 obj=119336 op='TABLE ACCESS BY INDEX ROWID S_PRI_LST '
STAT #3 id=45 cnt=0 pid=44 pos=1 obj=119343 op='INDEX UNIQUE SCAN S_PRI_LST_P1 '
STAT #3 id=46 cnt=0 pid=26 pos=2 obj=114868 op='TABLE ACCESS BY INDEX ROWID S_ENTLMNT '
STAT #3 id=47 cnt=0 pid=46 pos=1 obj=114878 op='INDEX UNIQUE SCAN S_ENTLMNT_P1 '
STAT #3 id=48 cnt=0 pid=25 pos=2 obj=118297 op='TABLE ACCESS BY INDEX ROWID S_ORG_EXT '
STAT #3 id=49 cnt=0 pid=48 pos=1 obj=118347 op='INDEX UNIQUE SCAN S_ORG_EXT_U3 '
STAT #3 id=50 cnt=0 pid=24 pos=2 obj=118297 op='TABLE ACCESS BY INDEX ROWID S_ORG_EXT '
STAT #3 id=51 cnt=0 pid=50 pos=1 obj=118347 op='INDEX UNIQUE SCAN S_ORG_EXT_U3 '
STAT #3 id=52 cnt=0 pid=23 pos=2 obj=119586 op='TABLE ACCESS BY INDEX ROWID S_PROD_INT '
STAT #3 id=53 cnt=0 pid=52 pos=1 obj=119619 op='INDEX UNIQUE SCAN S_PROD_INT_P1 '
STAT #3 id=54 cnt=0 pid=22 pos=2 obj=118297 op='TABLE ACCESS BY INDEX ROWID S_ORG_EXT '
STAT #3 id=55 cnt=0 pid=54 pos=1 obj=118347 op='INDEX UNIQUE SCAN S_ORG_EXT_U3 '
STAT #3 id=56 cnt=2224 pid=21 pos=2 obj=110986 op='TABLE ACCESS BY INDEX ROWID S_ADDR_PER '
STAT #3 id=57 cnt=2224 pid=56 pos=1 obj=110996 op='INDEX UNIQUE SCAN S_ADDR_PER_P1 '
STAT #3 id=58 cnt=2360 pid=20 pos=2 obj=112864 op='TABLE ACCESS BY INDEX ROWID S_CONTACT '
STAT #3 id=59 cnt=2360 pid=58 pos=1 obj=112893 op='INDEX UNIQUE SCAN S_CONTACT_U2 '
STAT #3 id=60 cnt=0 pid=19 pos=2 obj=121945 op='TABLE ACCESS BY INDEX ROWID S_TIMEZONE '
STAT #3 id=61 cnt=0 pid=60 pos=1 obj=121947 op='INDEX UNIQUE SCAN S_TIMEZONE_P1 '
STAT #3 id=62 cnt=0 pid=18 pos=2 obj=118297 op='TABLE ACCESS BY INDEX ROWID S_ORG_EXT '
STAT #3 id=63 cnt=0 pid=62 pos=1 obj=118347 op='INDEX UNIQUE SCAN S_ORG_EXT_U3 '
STAT #3 id=64 cnt=1356 pid=17 pos=2 obj=110986 op='TABLE ACCESS BY INDEX ROWID S_ADDR_PER '
STAT #3 id=65 cnt=1356 pid=64 pos=1 obj=110996 op='INDEX UNIQUE SCAN S_ADDR_PER_P1 '
STAT #3 id=66 cnt=0 pid=16 pos=2 obj=118297 op='TABLE ACCESS BY INDEX ROWID S_ORG_EXT '
STAT #3 id=67 cnt=0 pid=66 pos=1 obj=118347 op='INDEX UNIQUE SCAN S_ORG_EXT_U3 '
STAT #3 id=68 cnt=226 pid=15 pos=2 obj=121509 op='TABLE ACCESS BY INDEX ROWID S_SRV_REQ '
STAT #3 id=69 cnt=226 pid=68 pos=1 obj=121551 op='INDEX UNIQUE SCAN S_SRV_REQ_P1 '
STAT #3 id=70 cnt=37 pid=14 pos=2 obj=122204 op='TABLE ACCESS BY INDEX ROWID S_USER '
STAT #3 id=71 cnt=24748 pid=70 pos=1 obj=122209 op='INDEX UNIQUE SCAN S_USER_U2 '
STAT #3 id=72 cnt=37 pid=13 pos=2 obj=122204 op='TABLE ACCESS BY INDEX ROWID S_USER '
STAT #3 id=73 cnt=37 pid=72 pos=1 obj=122209 op='INDEX UNIQUE SCAN S_USER_U2 '
STAT #3 id=74 cnt=0 pid=12 pos=2 obj=122204 op='TABLE ACCESS BY INDEX ROWID S_USER '
STAT #3 id=75 cnt=0 pid=74 pos=1 obj=122209 op='INDEX UNIQUE SCAN S_USER_U2 '
STAT #3 id=76 cnt=13 pid=11 pos=2 obj=118297 op='TABLE ACCESS BY INDEX ROWID S_ORG_EXT '
STAT #3 id=77 cnt=13 pid=76 pos=1 obj=118347 op='INDEX UNIQUE SCAN S_ORG_EXT_U3 '
STAT #3 id=78 cnt=0 pid=10 pos=2 obj=121590 op='TABLE ACCESS BY INDEX ROWID S_SRV_REQ4_FNX '
STAT #3 id=79 cnt=0 pid=78 pos=1 obj=121591 op='INDEX RANGE SCAN S_SRV_REQ4_FNX_U1 '
STAT #3 id=80 cnt=37 pid=9 pos=2 obj=112949 op='TABLE ACCESS BY INDEX ROWID S_CONTACT_FNX '
STAT #3 id=81 cnt=37 pid=80 pos=1 obj=112950 op='INDEX RANGE SCAN S_CONTACT_FNX_U1 '
STAT #3 id=82 cnt=37 pid=8 pos=2 obj=121609 op='TABLE ACCESS BY INDEX ROWID S_SRV_REQ_X '
STAT #3 id=83 cnt=37 pid=82 pos=1 obj=121610 op='INDEX RANGE SCAN S_SRV_REQ_X_U1 '
STAT #3 id=84 cnt=37 pid=7 pos=2 obj=122204 op='TABLE ACCESS BY INDEX ROWID S_USER '
STAT #3 id=85 cnt=37 pid=84 pos=1 obj=122209 op='INDEX UNIQUE SCAN S_USER_U2 '
STAT #3 id=86 cnt=37 pid=6 pos=2 obj=112864 op='TABLE ACCESS BY INDEX ROWID S_CONTACT '
STAT #3 id=87 cnt=37 pid=86 pos=1 obj=112893 op='INDEX UNIQUE SCAN S_CONTACT_U2 '
STAT #3 id=88 cnt=37 pid=5 pos=2 obj=118742 op='TABLE ACCESS BY INDEX ROWID S_PARTY '
STAT #3 id=89 cnt=37 pid=88 pos=1 obj=118743 op='INDEX UNIQUE SCAN S_PARTY_P1 '
STAT #3 id=90 cnt=35 pid=4 pos=2 obj=119171 op='TABLE ACCESS BY INDEX ROWID S_POSTN '
STAT #3 id=91 cnt=35 pid=90 pos=1 obj=119182 op='INDEX UNIQUE SCAN S_POSTN_U2 '
STAT #3 id=92 cnt=35 pid=3 pos=2 obj=122204 op='TABLE ACCESS BY INDEX ROWID S_USER '
STAT #3 id=93 cnt=35 pid=92 pos=1 obj=122209 op='INDEX UNIQUE SCAN S_USER_U2 '
STAT #3 id=94 cnt=36 pid=2 pos=2 obj=121587 op='TABLE ACCESS BY INDEX ROWID S_SRV_REQ3_FNX '
STAT #3 id=95 cnt=36 pid=94 pos=1 obj=121588 op='INDEX RANGE SCAN S_SRV_REQ3_FNX_U1 '
STAT #3 id=96 cnt=36 pid=1 pos=2 obj=121571 op='TABLE ACCESS BY INDEX ROWID S_SRV_REQ1_FNX '
STAT #3 id=97 cnt=36 pid=96 pos=1 obj=121572 op='INDEX RANGE SCAN S_SRV_REQ1_FNX_U1 '



LIVE:
STAT #3 id=1 cnt=18 pid=0 pos=1 obj=0 op='NESTED LOOPS OUTER '
STAT #3 id=2 cnt=18 pid=1 pos=1 obj=0 op='NESTED LOOPS OUTER '
STAT #3 id=3 cnt=18 pid=2 pos=1 obj=0 op='NESTED LOOPS OUTER '
STAT #3 id=4 cnt=18 pid=3 pos=1 obj=0 op='NESTED LOOPS OUTER '
STAT #3 id=5 cnt=18 pid=4 pos=1 obj=0 op='NESTED LOOPS OUTER '
STAT #3 id=6 cnt=18 pid=5 pos=1 obj=0 op='NESTED LOOPS OUTER '
STAT #3 id=7 cnt=18 pid=6 pos=1 obj=0 op='NESTED LOOPS OUTER '
STAT #3 id=8 cnt=18 pid=7 pos=1 obj=0 op='NESTED LOOPS OUTER '
STAT #3 id=9 cnt=18 pid=8 pos=1 obj=0 op='NESTED LOOPS OUTER '
STAT #3 id=10 cnt=18 pid=9 pos=1 obj=0 op='NESTED LOOPS OUTER '
STAT #3 id=11 cnt=18 pid=10 pos=1 obj=0 op='NESTED LOOPS OUTER '
STAT #3 id=12 cnt=18 pid=11 pos=1 obj=0 op='NESTED LOOPS OUTER '
STAT #3 id=13 cnt=18 pid=12 pos=1 obj=0 op='NESTED LOOPS OUTER '
STAT #3 id=14 cnt=18 pid=13 pos=1 obj=0 op='NESTED LOOPS '
STAT #3 id=15 cnt=94088 pid=14 pos=1 obj=0 op='NESTED LOOPS OUTER '
STAT #3 id=16 cnt=94088 pid=15 pos=1 obj=0 op='NESTED LOOPS OUTER '
STAT #3 id=17 cnt=94088 pid=16 pos=1 obj=0 op='NESTED LOOPS OUTER '
STAT #3 id=18 cnt=94088 pid=17 pos=1 obj=0 op='NESTED LOOPS OUTER '
STAT #3 id=19 cnt=94088 pid=18 pos=1 obj=0 op='NESTED LOOPS OUTER '
STAT #3 id=20 cnt=94088 pid=19 pos=1 obj=0 op='NESTED LOOPS OUTER '
STAT #3 id=21 cnt=94088 pid=20 pos=1 obj=0 op='NESTED LOOPS OUTER '
STAT #3 id=22 cnt=94088 pid=21 pos=1 obj=0 op='NESTED LOOPS OUTER '
STAT #3 id=23 cnt=94088 pid=22 pos=1 obj=0 op='NESTED LOOPS OUTER '
STAT #3 id=24 cnt=94088 pid=23 pos=1 obj=0 op='NESTED LOOPS OUTER '
STAT #3 id=25 cnt=94088 pid=24 pos=1 obj=0 op='NESTED LOOPS OUTER '
STAT #3 id=26 cnt=94088 pid=25 pos=1 obj=0 op='NESTED LOOPS OUTER '
STAT #3 id=27 cnt=94088 pid=26 pos=1 obj=0 op='NESTED LOOPS OUTER '
STAT #3 id=28 cnt=94088 pid=27 pos=1 obj=0 op='NESTED LOOPS OUTER '
STAT #3 id=29 cnt=94088 pid=28 pos=1 obj=0 op='NESTED LOOPS OUTER '
STAT #3 id=30 cnt=94088 pid=29 pos=1 obj=0 op='NESTED LOOPS OUTER '
STAT #3 id=31 cnt=94088 pid=30 pos=1 obj=0 op='NESTED LOOPS OUTER '
STAT #3 id=32 cnt=94088 pid=31 pos=1 obj=0 op='NESTED LOOPS OUTER '
STAT #3 id=33 cnt=94088 pid=32 pos=1 obj=121509 op='TABLE ACCESS FULL S_SRV_REQ '
STAT #3 id=34 cnt=33952 pid=32 pos=2 obj=118297 op='TABLE ACCESS BY INDEX ROWID S_ORG_EXT '
STAT #3 id=35 cnt=33952 pid=34 pos=1 obj=118347 op='INDEX UNIQUE SCAN S_ORG_EXT_U3 '
STAT #3 id=36 cnt=40878 pid=31 pos=2 obj=111462 op='TABLE ACCESS BY INDEX ROWID S_ASSET '
STAT #3 id=37 cnt=40878 pid=36 pos=1 obj=111484 op='INDEX UNIQUE SCAN S_ASSET_P1 '
STAT #3 id=38 cnt=2800 pid=30 pos=2 obj=111462 op='TABLE ACCESS BY INDEX ROWID S_ASSET '
STAT #3 id=39 cnt=2800 pid=38 pos=1 obj=111484 op='INDEX UNIQUE SCAN S_ASSET_P1 '
STAT #3 id=40 cnt=2154 pid=29 pos=2 obj=110986 op='TABLE ACCESS BY INDEX ROWID S_ADDR_PER '
STAT #3 id=41 cnt=2154 pid=40 pos=1 obj=110996 op='INDEX UNIQUE SCAN S_ADDR_PER_P1 '
STAT #3 id=42 cnt=0 pid=28 pos=2 obj=119336 op='TABLE ACCESS BY INDEX ROWID S_PRI_LST '
STAT #3 id=43 cnt=0 pid=42 pos=1 obj=119343 op='INDEX UNIQUE SCAN S_PRI_LST_P1 '
STAT #3 id=44 cnt=0 pid=27 pos=2 obj=119336 op='TABLE ACCESS BY INDEX ROWID S_PRI_LST '
STAT #3 id=45 cnt=0 pid=44 pos=1 obj=119343 op='INDEX UNIQUE SCAN S_PRI_LST_P1 '
STAT #3 id=46 cnt=0 pid=26 pos=2 obj=114868 op='TABLE ACCESS BY INDEX ROWID S_ENTLMNT '
STAT #3 id=47 cnt=0 pid=46 pos=1 obj=114878 op='INDEX UNIQUE SCAN S_ENTLMNT_P1 '
STAT #3 id=48 cnt=0 pid=25 pos=2 obj=118297 op='TABLE ACCESS BY INDEX ROWID S_ORG_EXT '
STAT #3 id=49 cnt=0 pid=48 pos=1 obj=118347 op='INDEX UNIQUE SCAN S_ORG_EXT_U3 '
STAT #3 id=50 cnt=0 pid=24 pos=2 obj=118297 op='TABLE ACCESS BY INDEX ROWID S_ORG_EXT '
STAT #3 id=51 cnt=0 pid=50 pos=1 obj=118347 op='INDEX UNIQUE SCAN S_ORG_EXT_U3 '
STAT #3 id=52 cnt=0 pid=23 pos=2 obj=119586 op='TABLE ACCESS BY INDEX ROWID S_PROD_INT '
STAT #3 id=53 cnt=0 pid=52 pos=1 obj=119619 op='INDEX UNIQUE SCAN S_PROD_INT_P1 '
STAT #3 id=54 cnt=0 pid=22 pos=2 obj=118297 op='TABLE ACCESS BY INDEX ROWID S_ORG_EXT '
STAT #3 id=55 cnt=0 pid=54 pos=1 obj=118347 op='INDEX UNIQUE SCAN S_ORG_EXT_U3 '
STAT #3 id=56 cnt=2876 pid=21 pos=2 obj=110986 op='TABLE ACCESS BY INDEX ROWID S_ADDR_PER '
STAT #3 id=57 cnt=2876 pid=56 pos=1 obj=110996 op='INDEX UNIQUE SCAN S_ADDR_PER_P1 '
STAT #3 id=58 cnt=40904 pid=20 pos=2 obj=112864 op='TABLE ACCESS BY INDEX ROWID S_CONTACT '
STAT #3 id=59 cnt=40904 pid=58 pos=1 obj=112893 op='INDEX UNIQUE SCAN S_CONTACT_U2 '
STAT #3 id=60 cnt=0 pid=19 pos=2 obj=121945 op='TABLE ACCESS BY INDEX ROWID S_TIMEZONE '
STAT #3 id=61 cnt=0 pid=60 pos=1 obj=121947 op='INDEX UNIQUE SCAN S_TIMEZONE_P1 '
STAT #3 id=62 cnt=0 pid=18 pos=2 obj=118297 op='TABLE ACCESS BY INDEX ROWID S_ORG_EXT '
STAT #3 id=63 cnt=0 pid=62 pos=1 obj=118347 op='INDEX UNIQUE SCAN S_ORG_EXT_U3 '
STAT #3 id=64 cnt=2154 pid=17 pos=2 obj=110986 op='TABLE ACCESS BY INDEX ROWID S_ADDR_PER '
STAT #3 id=65 cnt=2154 pid=64 pos=1 obj=110996 op='INDEX UNIQUE SCAN S_ADDR_PER_P1 '
STAT #3 id=66 cnt=0 pid=16 pos=2 obj=118297 op='TABLE ACCESS BY INDEX ROWID S_ORG_EXT '
STAT #3 id=67 cnt=0 pid=66 pos=1 obj=118347 op='INDEX UNIQUE SCAN S_ORG_EXT_U3 '
STAT #3 id=68 cnt=16 pid=15 pos=2 obj=121509 op='TABLE ACCESS BY INDEX ROWID S_SRV_REQ '
STAT #3 id=69 cnt=16 pid=68 pos=1 obj=121551 op='INDEX UNIQUE SCAN S_SRV_REQ_P1 '
STAT #3 id=70 cnt=18 pid=14 pos=2 obj=122204 op='TABLE ACCESS BY INDEX ROWID S_USER '
STAT #3 id=71 cnt=94087 pid=70 pos=1 obj=122209 op='INDEX UNIQUE SCAN S_USER_U2 '
STAT #3 id=72 cnt=18 pid=13 pos=2 obj=122204 op='TABLE ACCESS BY INDEX ROWID S_USER '
STAT #3 id=73 cnt=18 pid=72 pos=1 obj=122209 op='INDEX UNIQUE SCAN S_USER_U2 '
STAT #3 id=74 cnt=0 pid=12 pos=2 obj=122204 op='TABLE ACCESS BY INDEX ROWID S_USER '
STAT #3 id=75 cnt=0 pid=74 pos=1 obj=122209 op='INDEX UNIQUE SCAN S_USER_U2 '
STAT #3 id=76 cnt=9 pid=11 pos=2 obj=118297 op='TABLE ACCESS BY INDEX ROWID S_ORG_EXT '
STAT #3 id=77 cnt=9 pid=76 pos=1 obj=118347 op='INDEX UNIQUE SCAN S_ORG_EXT_U3 '
STAT #3 id=78 cnt=0 pid=10 pos=2 obj=121590 op='TABLE ACCESS BY INDEX ROWID S_SRV_REQ4_FNX '
STAT #3 id=79 cnt=0 pid=78 pos=1 obj=121591 op='INDEX RANGE SCAN S_SRV_REQ4_FNX_U1 '
STAT #3 id=80 cnt=18 pid=9 pos=2 obj=112949 op='TABLE ACCESS BY INDEX ROWID S_CONTACT_FNX '
STAT #3 id=81 cnt=18 pid=80 pos=1 obj=112950 op='INDEX RANGE SCAN S_CONTACT_FNX_U1 '
STAT #3 id=82 cnt=18 pid=8 pos=2 obj=121609 op='TABLE ACCESS BY INDEX ROWID S_SRV_REQ_X '
STAT #3 id=83 cnt=18 pid=82 pos=1 obj=121610 op='INDEX RANGE SCAN S_SRV_REQ_X_U1 '
STAT #3 id=84 cnt=18 pid=7 pos=2 obj=122204 op='TABLE ACCESS BY INDEX ROWID S_USER '
STAT #3 id=85 cnt=18 pid=84 pos=1 obj=122209 op='INDEX UNIQUE SCAN S_USER_U2 '
STAT #3 id=86 cnt=18 pid=6 pos=2 obj=112864 op='TABLE ACCESS BY INDEX ROWID S_CONTACT '
STAT #3 id=87 cnt=18 pid=86 pos=1 obj=112893 op='INDEX UNIQUE SCAN S_CONTACT_U2 '
STAT #3 id=88 cnt=18 pid=5 pos=2 obj=118742 op='TABLE ACCESS BY INDEX ROWID S_PARTY '
STAT #3 id=89 cnt=18 pid=88 pos=1 obj=118743 op='INDEX UNIQUE SCAN S_PARTY_P1 '
STAT #3 id=90 cnt=18 pid=4 pos=2 obj=119171 op='TABLE ACCESS BY INDEX ROWID S_POSTN '
STAT #3 id=91 cnt=18 pid=90 pos=1 obj=119182 op='INDEX UNIQUE SCAN S_POSTN_U2 '
STAT #3 id=92 cnt=18 pid=3 pos=2 obj=122204 op='TABLE ACCESS BY INDEX ROWID S_USER '
STAT #3 id=93 cnt=18 pid=92 pos=1 obj=122209 op='INDEX UNIQUE SCAN S_USER_U2 '
STAT #3 id=94 cnt=18 pid=2 pos=2 obj=121587 op='TABLE ACCESS BY INDEX ROWID S_SRV_REQ3_FNX '
STAT #3 id=95 cnt=18 pid=94 pos=1 obj=121588 op='INDEX RANGE SCAN S_SRV_REQ3_FNX_U1 '
STAT #3 id=96 cnt=18 pid=1 pos=2 obj=121571 op='TABLE ACCESS BY INDEX ROWID S_SRV_REQ1_FNX '
STAT #3 id=97 cnt=18 pid=96 pos=1 obj=121572 op='INDEX RANGE SCAN S_SRV_REQ1_FNX_U1 '


can you analyse these raw trace ?
how to include the rows in tkprof output ?

regards
f.
Re: masiv Query Blocks on Statement [message #193129 is a reply to message #193031] Fri, 15 September 2006 00:16 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
OK, so this tells us that the Full Table scan returned 24748 rows in Test, and 94088 rows in Prod. Since there are a lot more rows in the table, I assume that you have a filter on the table (WHERE col = 'val').

Now, since it is a FTS, I would not expect any significant difference in the time of IO for this step. Both Test and Prod have to read the entire table - the number of rows retained for joins to subsequent steps does not affect anything at this point.

Next, you have 18 Nested Loop Outer Joins, followed by a Nested Loops Inner Join. That means in Prod, you are performing 94088*19 index lookups, and 24748*19 in Test. This part of the query reads nearly 4 times more QUERY buffers in Prod.

Because of the INNER join, the 94088 rows are filtered down to 18 in Prod, and down to 37 in Test. The remaining 13 Nested Loop joins have negligible IO (18*13 indexed lookups is nothing).

So, based on these stats, we would expect similar DISK in Test and Prod, and Prod QUERY to be around 3.8 times that of Test. In fact, the DISK figures are quite close, and the QUERY for Prod (1432217) is 3.4 times greater than Test (422321).

In fact, Prod is performing a bit BETTER than we can reasonably explain, not WORSE. So really, you don't have a problem at all.

I hope that helps.

Ross Leishman
Re: masiv Query Blocks on Statement [message #193188 is a reply to message #192532] Fri, 15 September 2006 03:57 Go to previous messageGo to next message
flat
Messages: 4
Registered: September 2006
Junior Member
wow.
this explanation was very clear to me.

can you you just explain me, how the rows appears in tkprof output ?
regards,
flat
Re: masiv Query Blocks on Statement [message #193371 is a reply to message #193188] Fri, 15 September 2006 22:16 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You're welcome. If you're that happy with the service, you can double my fee Wink

Normally the row counts just appear.

I noticed that your original TK*Prof output had 2 Pareses but only 1 Execute. It is possible that you somehow parsed the SQL again afterwards - or maybe it was caused by AUTOTRACE. The second parse may be messing it up - but I'm just guessing.

Try editing the TRC file and deleting the extraneous PARSE - see if the row counts appear then. Or try tracing with AUTOTRACE switched off.

Ross Leishman
Re: masiv Query Blocks on Statement [message #195318 is a reply to message #192843] Wed, 27 September 2006 22:38 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
rleishman wrote on Thu, 14 September 2006 12:12

One of your (range scan) indexes may be fragmented or subtly corrupted, and needs to be rebuilt. I wrote a post on this in the Server Administration forum, but I can't find it now. You could just rebuild the 5 range-scan indexes to eliminate this likelihood.


I know this wasn't the problem, but for anyone interested, you can find fragmented indexes using this.

Ross Leishman
Previous Topic: please help ASAP
Next Topic: Truncate
Goto Forum:
  


Current Time: Tue Dec 06 08:25:21 CST 2016

Total time taken to generate the page: 0.06913 seconds