Forum Stats

  • 3,780,468 Users
  • 2,254,398 Discussions
  • 7,879,340 Comments

Discussions

Query takes twice as long in DB with huge difference in 'current' and 'disk' columns

Hello,

Two days I posted a request about a query on a view, where I compared perf between databases on same server... whereas I should've compared perf in a given DB with that of another one hosted on another machine.

To sum up: 3 underlying tables of view have same volume; stats are up-to-date; execution plan is the same for this SELECT sql_id... but here's where it differs a lot, in tkprof report:

Where it's slow we have:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute   4998      0.50       0.50          0          0          0           0
Fetch     4998   1001.63    1233.90    3859604    2827052     194904        4997
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     9997   1002.14    1234.41    3859604    2827052     194904        4997


whereas on DB where it's twice quicker same tkprof block shows:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute   4998      0.29       0.29          0          0          0           0
Fetch     4998    700.13     703.06       1348    2826982          0        4997
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     9997    700.42     703.36       1348    2826982          0        4997

As you can see query/consistent mode reads are exactly the same.

I wonder why that many db block reads (current mode) occur on slow DB (are 'disk' reads a consequence of this ?), because on database where it is slow, we have

SQL> show sga

Total System Global Area 1.6106E+10 bytes
Fixed Size                 15754592 bytes
Variable Size            1.0201E+10 bytes
Database Buffers         5872025600 bytes
Redo Buffers               17797120 bytes

while where query executes well we have:

Total System Global Area 5251266040 bytes
Fixed Size                  8668664 bytes
Variable Size            4261412864 bytes
Database Buffers          973078528 bytes
Redo Buffers                8105984 bytes

only !

Someone has any clue about why first (slow) database needs to execute that many 'current' reads ??

Once again - thanks...

Regards,

Sebino

Tagged:

Answers

  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,638 Red Diamond

    I wonder why that many db block reads (current mode) occur on slow DB (are 'disk' reads a consequence of this ?)

    More block visits could be due to uncommitted transactions on the row data. It's called the "Read Consistency Trap".

    Or are you getting the same metrics repeatedly without transactions on the underlying table?

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,844 Gold Crown

    The earlier thread is at https://community.oracle.com/tech/developers/discussion/comment/16799707#Comment_16799707

    This includes a large volume of direct path writes and reads on the temporary tablespace - which is most of the "disk" activity in the slow query.

    Oracle will have to do some space management on the temp tablespace to allocate and release space and that probably explains the current block gets, and may have some work to do reading segment space management blocks in the temporary segment. I can't tell you exactly what the current block gets are in your output, but it's almost certainly related to temporary space management.

    Regards

    Jonathan Lewis

  • Sebino
    Sebino Member Posts: 62 Red Ribbon
    edited Jun 25, 2021 10:43AM

    Only difference (in that regard) I found is the following:

    "slow" database:

    SQL> select TEMPORARY_TABLESPACE,LOCAL_TEMP_TABLESPACE from dba_users where USERNAME='<Peoplesoft schema>';
    
    TEMPORARY_TABLESPACE           LOCAL_TEMP_TABLESPACE
    ------------------------------ ------------------------------
    PSTEMP                         SYSTEM
    
    
    

    "quick" one:

    SQL> select TEMPORARY_TABLESPACE,LOCAL_TEMP_TABLESPACE from dba_users where USERNAME='<Peoplesoft schema';
    
    TEMPORARY_TABLESPACE           LOCAL_TEMP_TABLESPACE
    ------------------------------ ------------------------------
    PSTEMP                         TEMP
    
    
    

    ... and when trying to ALTER USER <PP> LOCAL TEMPORARY TABLESPACE TEMP on first database, I receive

    ORA-00922: missing or invalid option
    

    (to try this select again).

    We're in 18c, and these are not RAC databases.

    Thanks.

    Sebino

  • saratpvv
    saratpvv Member Posts: 910 Gold Badge

    ORA-00922 With ALTER USER LOCAL TEMPORARY TABLESPACE (Doc ID 2614109.1)

  • EdStevens
    EdStevens Member Posts: 28,593 Gold Crown

    I don't know if it's the root of your performace issue, but it is a serious 'no no' to have the SYSTEM tablespace as a temp TS for any user. SYSTEM tablespace is for the data dictionary and other oracle system uses, not as a temp TS.

  • User_GSQTY
    User_GSQTY Member Posts: 56 Bronze Badge
    edited Jun 25, 2021 7:31PM

    Regarding your error ORA-00922, your database has been hit by bug 30295137 during upgrade to 18c ; this bug flags system tablespace as local temporary by mistake.

    In order to change your user local temporary tablespace, your database will need to be patched with patch 30295137. Once it's patched, you will then be able to execute your alter user statement

  • Sebino
    Sebino Member Posts: 62 Red Ribbon

    That's what I read on MOS indeed; but I don't think this has anything to do with that amount of db blocks buffers accessed in 'current' mode - well I don't know. I should compare with all other Peoplesoft databases we have over here.

    I can't figure out why all of a sudden this query requires that many sorts, and thus writes/reads to temp segments (if J. Lewis' assumption is right), when in the other database where it's running well we have *no such reads* at all. Jonathan writes "it's almost certainly related to temporary space management": what scripts to do a full check-up related to temporary space mgt could I execute to perform a complete comparison of both my databases ?

    Thanks.

    Sebino

  • User_GSQTY
    User_GSQTY Member Posts: 56 Bronze Badge

    You can look into dba_tablespaces .

    SELECT * FROM DBA_TABLESPACES WHERE TABLESPACE_NAME = <Your temp tablespace Name>

  • User_GSQTY
    User_GSQTY Member Posts: 56 Bronze Badge
    edited Jun 26, 2021 2:29PM

    I think you should look into related table / index statiscs from both DB to compare

    SELECT * FROM DBA_TAB_STATISTICS WHERE TABLE_NAME = <your target tables>

    SELECT * FROM DBA_IND_STATISTICS WHERE TABLE_NAME = <your target tables>

    Very likelly they will not be the same because the data is not the same.

    Looks at LAST_ANALYZED, STALE_STATS

    As Jonathan pointed out it must be related to data . If data is the same , then it must be related to statistics.

    You should compare data first.

    If for some reason, stats are not up to date then you will need to gahter stats on that particular table / index if needed.