Re: How does Oracle determine how much to extend autoextend datafiles?

I've been burned by SMCO (currently disabled for us) in the past because we were periodically (and annoyingly during busy periods in an OLTP env) getting high contention, where the blockers were the SMCO worker slaves. If you have DIAG pack and are armed with Tanel's d/ashtop it's pretty easy to check for their presence.

SQL> _at_dashtop
instance_number,program,sql_id,event,blocking_session,blocking_inst_id "program like '%(W%'" "to_date('2018-01-30 07:30','YYYY-MM-DD hh24:mi')" "to_date('2018-01-30 08:30','YYYY-MM-DD hh24:mi')"

   TotalSeconds             AAS %This  INSTANCE_NUMBER PROGRAM
                                            SQL_ID        EVENT
                             BLOCKING_SESSION BLOCKING_INST_ID FIRST_SEEN
        LAST_SEEN
--------------- --------------- ------ ---------------
----------------------------------------------------------------
------------- ---------------------------------------------
---------------- ---------------- ------------------- -------------------
           1370              .4   25%                1 oracle_at_eagnmnmep1f30
(W01N)                                                    row cache lock
                                                              2018-01-30
07:59:15 2018-01-30 08:22:04
           1370              .4   25%                3 oracle_at_eagnmnmep1f32
(W00M)                                                    row cache lock
                                                              2018-01-30
07:59:15 2018-01-30 08:22:03
           1320              .4   24%                2 oracle_at_eagnmnmep1f31
(W00K)                                                    row cache lock
                                                              2018-01-30
08:00:03 2018-01-30 08:22:01
           1320              .4   24%                2 oracle_at_eagnmnmep1f31
(W03Y)                                                    row cache lock
                                                              2018-01-30
08:00:03 2018-01-30 08:22:01
             10               0    0%                1 oracle_at_eagnmnmep1f30
(W03E)                                                    row cache lock
                                                              2018-01-30
08:03:47 2018-01-30 08:03:47
             10               0    0%                1 oracle_at_eagnmnmep1f30
(W03G)                                                    gc buffer busy
acquire                                    2361                1 2018-01-30
08:22:14 2018-01-30 08:22:14
             10               0    0%                3 oracle_at_eagnmnmep1f32
(W00M)                                      1p36ta7p3d7fu
                                                               2018-01-30

7 rows selected.

Andy K

On Mon, Mar 18, 2019 at 9:26 AM Rich J <rjoralist3_at_society.servebeer.com> wrote:

> On 2019/03/14 17:59, Tanel Poder wrote:
>
> Oracle tries to be proactive with tablespace extension (and even some
> segments' extention - securefiles for example). That's why the Space
> Management COordinator (SMCO) and its worker slaves (Wnnn) exist. So
> assuming that there was no user activity (like creating and later
> dropping/purging a large table), I'd say the SMCO process got a little too
> proactive with extending datafiles. Maybe you'll find some traces or
> warnings in alert log or the SMCO tracefile.
>
> There's a _enable_space_preallocation parameter that controls this and
> also plenty of MOS notes:
>
> - SMCO (Space Management Coordinator) For Autoextend On Datafiles And
> How To Disable/Enable (Doc ID 743773.1)
> - AUTOEXTEND Grows To Full Size Without Reason (Doc ID 1459097.1)
> - Master Note: Overview of Oracle Segment Storage (Doc ID 1491960.1) - *search
> for SMCO*
>
>
> There's no smco tracefile, other than an unrelated incident more than a
> week before my recent upgrade. And nothing in the alert log, other than
> the notification of the datafile extends.
>
> Looks like I have some homework to do. But at least now I have some idea
> of where to start looking. :)
>
> Thanks much, Tanel!
> Rich
>

--
http://www.freelists.org/webpage/oracle-l