Re: Same query with no plan change or volume but runs for hours vs minutes

From: Mladen Gogala <gogala.mladen_at_gmail.com>
Date: Tue, 5 Dec 2023 10:48:30 -0500
Message-ID: <a1249b18-8dfc-470e-8a87-be383c627aad_at_gmail.com>

On 11/28/23 16:17, Chris Taylor wrote:
> Oracles recommendation to us at the time was to turn off auto extend
> on all the full/nearly full data files and ultimately start moving
> objects into a big file table space.

Chris, as a general rule I always turn auto extend off. The slowest thing in the database world is to wait on the database to "make room" itself. That is why Larry has given us DBA_TABLESPACE_USAGE_METRICS. There were some bugs in version 11, but I find this view very useful and reliable:

SQL> select * from dba_tablespace_usage_metrics order by used_percent desc;

TABLESPACE_NAME       USED_SPACE 
TABLESPACE_SIZE                                    USED_PERCENT
__________________ _____________ __________________ 
_______________________________________________

UNDOTBS1                   72192            4194302 1.72119222697841023369323429738726491321 SYSAUX                     65256            4194302 1.55582502166033823983108512453323580419 SYSTEM                     62912            4194302 1.49993968007072452102876712263446933483 USERS                        168            4194302 0.004005434038846034453408457473973023401748

Extending data files dynamically always causes a long execution and people start submitting Jira tickets. I always use the files with the maximum size (32767 GB) and auto-extend turned off. The only exception is my personal Oracle instance running with Docker:

mgogala_at_umajor:~$ docker ps

CONTAINER ID   IMAGE                 COMMAND CREATED        
STATUS         PORTS     NAMES
f9afadaff232   localhost/ora21c:12   "/usr/local/bin/runD…" 22 hours 

ago   Up 5 minutes             ora21c mgogala_at_umajor:~$

Not much room needed for EMP and DEPT tables. Praise Larry.

Regards

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

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

Received on Tue Dec 05 2023 - 16:48:30 CET