can occur when a session is waiting/wanting to access a database block in the buffer cache but it can not as the buffer is busy. This can occur if either another session is reading the block in to the buffer or another session holds the buffer in an incompatible mode to our request.
Buffer busy waits are common in IO bound storage subsystems.
These waits indicate read/read, read/write or write/write contention.
The Oracle session is waiting to pin a buffer, a buffer must be pinned before it can be read or modified.
Only one process can pin a buffer at any one time.
It is also often due to several processes repeatedly reading the same block. (hot blocks)
Actions:
- Eliminate HOT blocks from the application. Check for repeatedly scanned/unselective indexes.
- Try rebuilding the object with a higher PCTFREE so that you reduce the number of rows per block.
- Replace with higher IO subsystesm.
Please note, in the recent Oracle Db versions we can Ignore PCTUSED,INITRANS, andMAXTRANS:
PCTUSED: "This parameter is not useful and is ignored for objects with automatic segment-space management."
INITRANS: "In general, you should not change the INITRANS value from its default."
MAXTRANS: "This parameter has been deprecated."
PCTFREE is probably the only parameter worth thinking about, and the default of 10 is likely fine.
2. Free buffer waits:
- Session is waiting for buffer to be available in buffer cache but it's not able to find free buffer because there are too many dirty buffer in the buffer cache.
- The main cause of it is either buffer cache is too small or the DBWR is not able to write dirty buffers to disk fast enough.
- DBWR is unable to keep up to the write requests.
- Checkpoint happening too fast - may be due to high database activity and under-sized online redo log files.
- Large sorts and full table scans are filling the buffer cache with modified block faster than the DBWR is able to write to the disk.
Actions:
- Analyze the buffer cache and SGA/AMM related parameters and increase SGA/AMM appropriately.
- Analyze the DBWR slaves and increase it if required.
3. Cache buffer chain latch:
The cache buffers LRU chain latches protect the lists of buffers in the cache. When adding, moving, or removing a buffer from a list, a latch must be obtained to prevent RAM corruption and ensure that only one process handles the list of buffer addresses.
Possible Causes :
· Processes need to get this latch when they need to move buffers based on the LRU block replacement policy in the buffer cache
· The cache buffer LRU chain latch is acquired in order to introduce a new block into the buffer cache and when writing a buffer back to disk, specifically when trying to scan the LRU (least recently used) chain containing all the dirty blocks in the buffer cache. Competition for the cache buffers LRU chain .
· latch is symptomatic of intense buffer cache activity caused by inefficient SQL statements. Statements that repeatedly scan large unselective indexes or perform full table scans are the prime culprits.
· Heavy contention for this latch is generally due to heavy buffer cache activity which can be caused, for example, by repeatedly scanning large unselective indexes
Actions :
Contention in this latch can be avoided implementing multiple buffer pools or increasing the number of LRU latches with the parameter DB_BLOCK_LRU_LATCHES (The default value is generally sufficient for most systems).
Its possible to reduce contention for the cache buffer LRU chain latch by increasing the size of the buffer cache and thereby reducing the rate at which new blocks are introduced into the buffer cache.
When you perform Oracle client/database installation on the IBM AIX platform, you may encounter below issue post installation, where is upon running any oracle executable, like sqlplus, tnsping, sqlldr, you will encounter error exec(): 0509-036 Cannot load program .
$ sqlplus
exec(): 0509-036 Cannot load program sqlplus because of the following errors:
0509-130 Symbol resolution failed for /app01/Oracle/product/12.2.0/client_1/lib/libons.so because:
0509-136 Symbol CreateIoCompletionPort (number 93) is not exported from
dependent module /unix.
0509-136 Symbol GetQueuedCompletionStatus (number 94) is not exported from
dependent module /unix.
0509-136 Symbol ReadFile (number 95) is not exported from
dependent module /unix.
0509-136 Symbol WriteFile (number 96) is not exported from
dependent module /unix.
0509-192 Examine .loader section symbols with the
'dump -Tv' command.
$ tnsping
exec(): 0509-036 Cannot load program tnsping because of the following errors:
0509-130 Symbol resolution failed for /app01/Oracle/product/12.2.0/client_1/lib/libons.so because:
0509-136 Symbol CreateIoCompletionPort (number 93) is not exported from
dependent module /unix.
0509-136 Symbol GetQueuedCompletionStatus (number 94) is not exported from
dependent module /unix.
0509-136 Symbol ReadFile (number 95) is not exported from
dependent module /unix.
0509-136 Symbol WriteFile (number 96) is not exported from
dependent module /unix.
0509-192 Examine .loader section symbols with the
'dump -Tv' command.
$ lsdev | grep iocp
iocp0 Defined I/O Completion Ports
This is because on AIX, IOCP parameter is not properly set as displayed in above output in red.
Please follow Oracle Support document:
12.2 AIX 7.2: EXEC(): 0509-036 CANNOT LOAD PROGRAM KFOD.BIN DURING GRID INSTALL (Doc ID 2288514.1)
IOCP IBM AIX parameter needs to be changed from “defined” to “available”, this change is required root privilege and it's suggested that this change is implemented by System Admin on Production environment, but for lower environment with less risk you can modify the same with root privilege as mentioned below:
1. Log in as root and run the following command:
# smitty iocp
Select Change / Show Characteristics of I/O Completion Ports.
Change configured state at system restart from Defined to Available.
2. Reboot server.
3. Run the lsdev command to confirm the IOCP status is set to Available:
$ lsdev | grep iocp
iocp0 Available I/O Completion Ports
Post this change and reboot of the server, oracle executables will work without any issue.
Oracle Client Installation is required on Application Server(JBoss, WebLogic, WebShear,etc) to connect to Oracle database. While installing Oracle Client is easy on Windows server, it's little cumbersome to install on Unix/Linux because it's require GUI to be invoke and in order to invoke GUI on Unix/Linux there are certain configuration is required, like Port 6000 opening from and to Server where client installation is required to PC from where SSH terminal is been opened, more over XServer forwarding and SSHD service needs to be started and X Server client/server software like XMing or MobaXTerm, Cygwin/X needs to be installed. Such configuration on production environment in banking, financial domain needs to go through stringent security approval process and it delays overall timing of accomplishing the task.
As you can see installing Oracle Client on Unix/Linux using X Server is not that straight forward, so is there any other easy way forward? Yes. You can perform Oracle client installation using silent method as described below.
Note: Here Oracle Client was installed on AIX OS , but the same steps can be used to install Oracle client in silent mode for any UNIX/Linux platform.
Step#1: Download and Unzipp the Oracle Client Software
Make sure that you download the Oracle Client binaries and unzipped it in appropriate directory. In my case, Oracle software was copied and unziped at below location on the AIX application server.
/tmp/oracle_client_sw/
Step#2: Modify the response file with required parameters.
Sample reponse file available under the Oracle software needs to be modified as per appropriate parameter suitable for your application server environment.
$ pwd
/tmp/oracle_client_sw/client
$ cd response
$ ls
client_install.rsp client_install.rsp.orig netca.rsp
$ vi client_install.rsp
"client_install.rsp" 107 lines, 5614 characters
#-------------------------------------------------------------------------------
# Unix group to be set for the inventory directory.
#----------------------------
UNIX_GROUP_NAME=oinstall
#-------------------------------------------------------------------------------
# Inventory location.
#-------------------------------------------------------------------------------
INVENTORY_LOCATION=/app01/oraInventory
#-------------------------------------------------------------------------------
# Complete path of the Oracle Home
#-------------------------------------------------------------------------------
ORACLE_HOME=/app01/Oracle/product/12.2.0/client_1
#-------------------------------------------------------------------------------
# Complete path of the Oracle Base.
#-------------------------------------------------------------------------------
ORACLE_BASE=/app01/Oracle
#Example : INSTALL_TYPE = Administrator
#------------------------------------------------------------------------------
oracle.install.client.installType=Administrator
SELECTED_LANGUAGES=en,ar
These are the basic parameters that you may need to change, you change other parameters if required per your need.
Step#3: Perform the Silent Oracle client Installation.
$ pwd
/tmp/oracle_client_sw/client
$ ./runInstaller -ignoreSysPrereqs -showProgress -silent -responseFile /tmp/oracle_client_sw/client/response/client_install.rsp
Starting Oracle Universal Installer...
Checking Temp space: must be greater than 231 MB. Actual 49566 MB Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2020-10-27_02-34-11PM. Please wait ...$ [WARNING] [INS-13014] Target environment does not meet some optional requirements.
CAUSE: Some of the optional prerequisites are not met. See logs for details. installActions2020-10-27_02-34-11PM.log
ACTION: Identify the list of failed prerequisite checks from the log: installActions2020-10-27_02-34-11PM.log. Then either from the log file or from installation manual find the appropriate configuration to meet the prerequisites and fix it manually.
You can find the log of this install session at:
/app01/oraInventory/logs/installActions2020-10-27_02-34-11PM.log
Prepare in progress.
.................................................. 6% Done.
Prepare successful.
Copy files in progress.
.................................................. 12% Done.
.................................................. 17% Done.
.................................................. 24% Done.
.................................................. 30% Done.
.................................................. 35% Done.
.................................................. 41% Done.
.................................................. 46% Done.
.................................................. 52% Done.
.................................................. 57% Done.
.................................................. 62% Done.
Copy files successful.
Link binaries in progress.
Link binaries successful.
Setup files in progress.
....................
Setup files successful.
Setup Inventory in progress.
Setup Inventory successful.
Finish Setup in progress.
Finish Setup successful.
The installation of Oracle Client 12c was successful.
Please check '/app01/oraInventory/logs/silentInstall2020-10-27_02-34-11PM.log' for more details.
Prepare for configuration steps in progress.
Prepare for configuration steps successful.
.................................................. 80% Done.
Oracle Client Configuration in progress.
Oracle Client Configuration successful.
.................................................. 96% Done.
As a root user, execute the following script(s):
1. /app01/oraInventory/orainstRoot.sh
.................................................. 100% Done.
Successfully Setup Software.
$
As highlighted above in red, while installing client will require you to run orainstRoot.sh script as root user, so open SSH terminal window with root user and execute the script.
--execute script as root.
--execute script as root.
root@IPYPRAPP03:[/]#/app01/oraInventory/orainstRoot.sh
Changing permissions of /app01/oraInventory.
Adding read,write permissions for group.
Removing read,write,execute permissions for world.
Changing groupname of /app01/oraInventory to oinstall.
The execution of the script is complete.
root@IPYPRAPP03:[/]#
And now our Oracle client installation is complete without need of enabling GUI/X Forwarding option.
In Part-1-Oracle Extent Allocation for non-partitioned table, we learned how Oracle allocates extents for non-partitioned tables and which is most appropriate in most of the cases, however when you have large tables with partitions , sub partitions , composite partitions used for your application, then I would suggest you to take closer look at the way Oracle allocates extents for such tables, most of the cases when all the extents and not densely populated with rows then it will tend to have more storage space wasted and few millions of rows would required 100s of GBs of storage which could have been accommodate in lesser storage.
Recently while investigating data fragmentation with one of our client's database, I was amazed to see that there was huge wasted space for the large partitioned tables having multiple sub-partitions. The database I was investigating with fragmentation was an OLTP database having couple of large partitions/sub-partitions tables that occupies majority of database storage and I was surprised to see more than 70% storage was not properly utilized due extent allocation mystery for partitioned table which I will be revealing in this article.
Now let's see how the extents have been allocated for this table when we created table without specifying storage clause.
Please note that we have truncated some content in below output to make it short and slim.
If you observe the output, the first extent allocated for the extent is 8MB in size, which is 1024 blocks with each block is 8kb block_size.
SQL> set line 500 pagesize 50 echo on
SQL> col owner for a10
SQL> col segment_name for a20
SQL> col partition_name for a25
SQL> col segment_type for a25
SQL> col tablespace_name for a15
SQL> col extent_id for 99
SQL> col file_id for 99
SQL> col block_id for 9999999999
SQL> col bytes for 9999999999
SQL> col blocks for 99999
SQL>
SQL>
SQL> select owner,segment_name,partition_name,segment_type,tablespace_name,extent_id,file_id,block_id,bytes,blocks
2 from dba_extents where owner='TEST' and segment_name='T_OBJ_SUB1';
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS
---------- -------------------- ------------------------- ------------------------- --------------- --------- ------- ----------- ----------- ------
TEST T_OBJ_SUB1 SYS_SUBP137446 TABLE SUBPARTITION IPYPRTBPS1 0 8 266496 8388608 1024
TEST T_OBJ_SUB1 SYS_SUBP137448 TABLE SUBPARTITION IPYPRTBPS1 0 8 270592 8388608 1024
TEST T_OBJ_SUB1 SYS_SUBP137454 TABLE SUBPARTITION IPYPRTBPS1 0 8 275712 8388608 1024
TEST T_OBJ_SUB1 SYS_SUBP137455 TABLE SUBPARTITION IPYPRTBPS1 0 8 272640 8388608 1024
TEST T_OBJ_SUB1 SYS_SUBP137459 TABLE SUBPARTITION IPYPRTBPS1 0 8 277760 8388608 1024
TEST T_OBJ_SUB1 SYS_SUBP137467 TABLE SUBPARTITION IPYPRTBPS1 0 8 286976 8388608 1024
TEST T_OBJ_SUB1 SYS_SUBP137469 TABLE SUBPARTITION IPYPRTBPS1 0 8 280704 8388608 1024
TEST T_OBJ_SUB1 SYS_SUBP137470 TABLE SUBPARTITION IPYPRTBPS1 0 8 284928 8388608 1024
TEST T_OBJ_SUB1 SYS_SUBP137472 TABLE SUBPARTITION IPYPRTBPS1 0 8 283904 8388608 1024
TEST T_OBJ_SUB1 SYS_SUBP137473 TABLE SUBPARTITION IPYPRTBPS1 0 8 282880 8388608 1024
TEST T_OBJ_SUB1 SYS_SUBP137475 TABLE SUBPARTITION IPYPRTBPS1 0 8 290944 8388608 1024
TEST T_OBJ_SUB1 SYS_SUBP137476 TABLE SUBPARTITION IPYPRTBPS1 0 8 291968 8388608 1024
TEST T_OBJ_SUB1 SYS_SUBP137487 TABLE SUBPARTITION IPYPRTBPS1 0 8 300288 8388608 1024
TEST T_OBJ_SUB1 SYS_SUBP137488 TABLE SUBPARTITION IPYPRTBPS1 0 8 296192 8388608 1024
TEST T_OBJ_SUB1 SYS_SUBP137489 TABLE SUBPARTITION IPYPRTBPS1 0 8 295168 8388608 1024
TEST T_OBJ_SUB1 SYS_SUBP137493 TABLE SUBPARTITION IPYPRTBPS1 0 8 305408 8388608 1024
TEST T_OBJ_SUB1 SYS_SUBP137495 TABLE SUBPARTITION IPYPRTBPS1 0 8 309504 8388608 1024
TEST T_OBJ_SUB1 SYS_SUBP137496 TABLE SUBPARTITION IPYPRTBPS1 0 8 311552 8388608 1024
TEST T_OBJ_SUB1 SYS_SUBP137497 TABLE SUBPARTITION IPYPRTBPS1 0 8 302336 8388608 1024
TEST T_OBJ_SUB1 SYS_SUBP137499 TABLE SUBPARTITION IPYPRTBPS1 0 8 306432 8388608 1024
TEST T_OBJ_SUB1 SYS_SUBP137507 TABLE SUBPARTITION IPYPRTBPS1 0 8 314624 8388608 1024
TEST T_OBJ_SUB1 SYS_SUBP137509 TABLE SUBPARTITION IPYPRTBPS1 0 8 313600 8388608 1024
TEST T_OBJ_SUB1 SYS_SUBP137512 TABLE SUBPARTITION IPYPRTBPS1 0 8 315648 8388608 1024
TEST T_OBJ_SUB1 SYS_SUBP137516 TABLE SUBPARTITION IPYPRTBPS1 0 8 317696 8388608 1024
:
:
:
TEST T_OBJ_SUB1 SYS_SUBP137372 TABLE SUBPARTITION IPYPRTBPS1 0 9 2998400 8388608 1024
TEST T_OBJ_SUB1 SYS_SUBP137373 TABLE SUBPARTITION IPYPRTBPS1 0 9 2996352 8388608 1024
TEST T_OBJ_SUB1 SYS_SUBP137374 TABLE SUBPARTITION IPYPRTBPS1 0 9 3000448 8388608 1024
TEST T_OBJ_SUB1 SYS_SUBP137378 TABLE SUBPARTITION IPYPRTBPS1 0 9 3005568 8388608 1024
TEST T_OBJ_SUB1 SYS_SUBP137381 TABLE SUBPARTITION IPYPRTBPS1 0 9 3004544 8388608 1024
TEST T_OBJ_SUB1 SYS_SUBP137382 TABLE SUBPARTITION IPYPRTBPS1 0 9 3006592 8388608 1024
TEST T_OBJ_SUB1 SYS_SUBP137385 TABLE SUBPARTITION IPYPRTBPS1 0 9 3009664 8388608 1024
342 rows selected.
Now let us observe the storage clause picked up by default when this partitioned table is created without specifying storage clause(which will pick default storage clause).
First we need to collect statistics to have the statistics related columns populated in dba_tab_subpartitions and related dictionary views.
Please note that we have truncated some content in below output to make it short and slim.
SQL>
SQL> exec dbms_stats.gather_table_stats(ownname => 'TEST', tabname => 'T_OBJ_SUB1',GRANULARITY=>'ALL', estimate_percent => dbms_stats.auto_sample_size, degree => '4',cascade => TRUE, method_opt=>'FOR ALL COLUMNS SIZE AUTO');
PL/SQL procedure successfully completed.
SQL>
SQL> col table_owner for a10
SQL> col table_name for a15
SQL> col partition_name for a15
SQL> col subpartition_name for a25
SQL> col partition_position for 999
SQL> col subpartition_position for 9999
SQL> col tablespace_name for a15
SQL> col initial_extent for 999999999999
SQL> col next_extent for 999999999999
SQL> col num_rows for 999999999999
SQL> col blocks for 99999999
SQL> col last_analyzed for a25
SQL>
SQL> select table_owner,table_name ,partition_name,subpartition_name,partition_position,subpartition_position,tablespace_name,initial_extent, next_extent ,
2 num_rows, blocks,last_analyzed
3 from dba_tab_subpartitions where table_name='T_OBJ_SUB1';
TABLE_OWNE TABLE_NAME PARTITION_NAME SUBPARTITION_NAME PARTITION_POSITION SUBPARTITION_POSITION TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT NUM_ROWS BLOCKS LAST_ANALYZED
---------- --------------- --------------- ------------------------- ------------------ --------------------- --------------- -------------- ------------- ------------- --------- --------------
TEST T_OBJ_SUB1 P_FIRST P_FIRST_T1 1 1 IPYPRTBPS1 8388608 1048576 8971 1006 23-SEP-20
TEST T_OBJ_SUB1 P_FIRST P_FIRST_T2 1 2 IPYPRTBPS1 8388608 1048576 9069 1006 23-SEP-20
TEST T_OBJ_SUB1 P_FIRST P_FIRST_T3 1 3 IPYPRTBPS1 8388608 1048576 8850 1006 23-SEP-20
TEST T_OBJ_SUB1 P_FIRST P_FIRST_T8 1 8 IPYPRTBPS1 8388608 1048576 9067 1006 23-SEP-20
TEST T_OBJ_SUB1 P_FIRST P_FIRST_T5 1 5 IPYPRTBPS1 8388608 1048576 9088 1006 23-SEP-20
TEST T_OBJ_SUB1 P_FIRST P_FIRST_T6 1 6 IPYPRTBPS1 8388608 1048576 9134 1006 23-SEP-20
TEST T_OBJ_SUB1 P_FIRST P_FIRST_T7 1 7 IPYPRTBPS1 8388608 1048576 9144 1006 23-SEP-20
TEST T_OBJ_SUB1 P_FIRST P_FIRST_T4 1 4 IPYPRTBPS1 8388608 1048576 9033 1006 23-SEP-20
TEST T_OBJ_SUB1 SYS_P137258 SYS_SUBP137250 2 1 IPYPRTBPS1 8388608 1048576 3 1006 23-SEP-20
TEST T_OBJ_SUB1 SYS_P137258 SYS_SUBP137251 2 2 IPYPRTBPS1 8388608 1048576 2 1006 23-SEP-20
TEST T_OBJ_SUB1 SYS_P137258 SYS_SUBP137252 2 3 IPYPRTBPS1 8388608 1048576 4 1006 23-SEP-20
TEST T_OBJ_SUB1 SYS_P137258 SYS_SUBP137257 2 8 IPYPRTBPS1 8388608 1048576 1 1006 23-SEP-20
TEST T_OBJ_SUB1 SYS_P137258 SYS_SUBP137254 2 5 IPYPRTBPS1 8388608 1048576 4 1006 23-SEP-20
TEST T_OBJ_SUB1 SYS_P137258 SYS_SUBP137255 2 6 IPYPRTBPS1 8388608 1048576 2 1006 23-SEP-20
TEST T_OBJ_SUB1 SYS_P137258 SYS_SUBP137256 2 7 IPYPRTBPS1 8388608 1048576 2 1006 23-SEP-20
TEST T_OBJ_SUB1 SYS_P137258 SYS_SUBP137253 2 4 IPYPRTBPS1 8388608 1048576 4 1006 23-SEP-20
TEST T_OBJ_SUB1 SYS_P137339 SYS_SUBP137331 3 1 IPYPRTBPS1 8388608 1048576 4 1006 23-SEP-20
TEST T_OBJ_SUB1 SYS_P137339 SYS_SUBP137332 3 2 IPYPRTBPS1 8388608 1048576 1 1006 23-SEP-20
TEST T_OBJ_SUB1 SYS_P137339 SYS_SUBP137333 3 3 IPYPRTBPS1 8388608 1048576 4 1006 23-SEP-20
TEST T_OBJ_SUB1 SYS_P137339 SYS_SUBP137334 3 4 IPYPRTBPS1 8388608 1048576 2 1006 23-SEP-20
TEST T_OBJ_SUB1 SYS_P137339 SYS_SUBP137335 3 5 IPYPRTBPS1 8388608 1048576 1 1006 23-SEP-20
TEST T_OBJ_SUB1 SYS_P137339 SYS_SUBP137336 3 6 IPYPRTBPS1 8388608 1048576 3 1006 23-SEP-20
TEST T_OBJ_SUB1 SYS_P137339 SYS_SUBP137337 3 7 IPYPRTBPS1 8388608 1048576 4 1006 23-SEP-20
TEST T_OBJ_SUB1 SYS_P137339 SYS_SUBP137338 3 8 IPYPRTBPS1 8388608 1048576 1 1006 23-SEP-20
TEST T_OBJ_SUB1 SYS_P137357 SYS_SUBP137349 4 1 IPYPRTBPS1 8388608 1048576 1 1006 23-SEP-20
TEST T_OBJ_SUB1 SYS_P137357 SYS_SUBP137350 4 2 IPYPRTBPS1 8388608 1048576 3 1006 23-SEP-20
TEST T_OBJ_SUB1 SYS_P137357 SYS_SUBP137351 4 3 IPYPRTBPS1 8388608 1048576 3 1006 23-SEP-20
TEST T_OBJ_SUB1 SYS_P137357 SYS_SUBP137352 4 4 IPYPRTBPS1 8388608
:
:
TEST T_OBJ_SUB1 SYS_P137429 SYS_SUBP137423 57 3 IPYPRTBPS1 8388608 1048576 3 376 23-SEP-20
TEST T_OBJ_SUB1 SYS_P137429 SYS_SUBP137421 57 1 IPYPRTBPS1 8388608 1048576 2 1006 23-SEP-20
TEST T_OBJ_SUB1 SYS_P137429 SYS_SUBP137422 57 2 IPYPRTBPS1 8388608 1048576 1 1006 23-SEP-20
456 rows selected.
If you observe in the above output, the INITIAL_EXTENT is 8MB(8388608) and NEXT_EXTENT size is 1MB(1048576) for the default storage clause.
In below output you can observe that almost all the exten space in each subparitions are wasted and total 2.50GB (2527086KB) storage is being wasted where as actual data is approximately 25MB(26097KB).
Please note that we have truncated some content in below output to make it short and slim.
SQL>
SQL> --Wasted space below HWM
SQL> col owner for a20
SQL> col table_name for a30
SQL> col LAST_ANALYZED for a20
SQL> col EXTENT_MANAGEMENT for a20
SQL> col SEGMENT_SPACE_MANAGEMENT for a20
SQL> col tablespace_name for a20
SQL> col sgment_space_mgmt for a15
SQL> col partition_name for a25
SQL> col subparition_name for a30
SQL>
SQL>
SQL> select * from
2 (
3 select
4 table_owner,table_name,PARTITION_NAME,subpartition_name,last_analyzed,s.tablespace_name,EXTENT_MANAGEMENT EXTENT_MGMT,SEGMENT_SPACE_MANAGEMENT SEGMENT_SPACE_MGMT,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,round((blocks*8),2) "size (kb)" ,
5 round((num_rows*avg_row_len/1024),2) "actual_data (kb)",
6 (round((blocks*8),2) - round((num_rows*avg_row_len/1024),2)) "wasted_space (kb)"
7 from
8 dba_tab_subpartitions T, dba_tablespaces s
9 where
10 (round((blocks*8),2) > round((num_rows*avg_row_len/1024),2))
11 and
12 t.tablespace_name=s.tablespace_name
13 order by (round((blocks*8),2) - round((num_rows*avg_row_len/1024),2)) desc
14 )
15 where table_owner='TEST' AND TABLE_NAME='T_OBJ_SUB1' ;
TABLE_OWNE TABLE_NAME PARTITION_NAME SUBPARTITION_NAME LAST_ANALYZED TABLESPACE_NAME EXTENT_MGM SEGMEN NUM_ROWS BLOCKS EMPTY_BLOCKS size (kb) actual_data (kb) wasted_space (kb)
---------- ------------------------------ ------------------------- ------------------------- -------------------- -------------------- ---------- ------ ------------- --------- ------------ ---------- ---------------- -----------------
TEST T_OBJ_SUB1 SYS_P137690 SYS_SUBP137682 23-SEP-20 IPYPRTBPS1 LOCAL AUTO 1 1006 0 8048 .11 8047.89
TEST T_OBJ_SUB1 SYS_P137708 SYS_SUBP137705 23-SEP-20 IPYPRTBPS1 LOCAL AUTO 1 1006 0 8048 .11 8047.89
TEST T_OBJ_SUB1 SYS_P137708 SYS_SUBP137706 23-SEP-20 IPYPRTBPS1 LOCAL AUTO 1 1006 0 8048 .11 8047.89
TEST T_OBJ_SUB1 SYS_P137717 SYS_SUBP137714 23-SEP-20 IPYPRTBPS1 LOCAL AUTO 1 1006 0 8048 .11 8047.89
TEST T_OBJ_SUB1 SYS_P137753 SYS_SUBP137748 23-SEP-20 IPYPRTBPS1 LOCAL AUTO 1 1006 0 8048 .11 8047.89
TEST T_OBJ_SUB1 SYS_P137357 SYS_SUBP137355 23-SEP-20 IPYPRTBPS1 LOCAL AUTO 1 1006 0 8048 .11 8047.89
TEST T_OBJ_SUB1 SYS_P137483 SYS_SUBP137476 23-SEP-20 IPYPRTBPS1 LOCAL AUTO 1 1006 0 8048 .11 8047.89
TEST T_OBJ_SUB1 SYS_P137510 SYS_SUBP137507 23-SEP-20 IPYPRTBPS1 LOCAL AUTO 1 1006 0 8048 .11 8047.89
TEST T_OBJ_SUB1 SYS_P137339 SYS_SUBP137332 23-SEP-20 IPYPRTBPS1 LOCAL AUTO 1 1006 0 8048 .11 8047.89
TEST T_OBJ_SUB1 SYS_P137339 SYS_SUBP137335 23-SEP-20 IPYPRTBPS1 LOCAL AUTO 1 1006 0 8048 .11 8047.89 :
:
:
TEST T_OBJ_SUB1 SYS_P137690 SYS_SUBP137683 23-SEP-20 IPYPRTBPS1 LOCAL AUTO 1 754 0 6032 .11 6031.89
TEST T_OBJ_SUB1 SYS_P137528 SYS_SUBP137520 23-SEP-20 IPYPRTBPS1 LOCAL AUTO 1 376 0 3008 .14 3007.86
TEST T_OBJ_SUB1 SYS_P137429 SYS_SUBP137423 23-SEP-20 IPYPRTBPS1 LOCAL AUTO 3 376 0 3008 .39 3007.61
TEST T_OBJ_SUB1 SYS_P137294 SYS_SUBP137289 23-SEP-20 IPYPRTBPS1 LOCAL AUTO 803 124 0 992 105.08 886.92
320 rows selected.
SQL>
SQL>
SQL>
SQL> select table_owner,table_name,sum(size_kb),sum(actual_data_kb),sum(wasted_space_kb) from
2 (
3 select
4 table_owner,table_name,PARTITION_NAME,subpartition_name, last_analyzed,s.tablespace_name,EXTENT_MANAGEMENT EXTENT_MGMT,SEGMENT_SPACE_MANAGEMENT SEGMENT_SPACE_MGMT,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,
5 round((blocks*8),2) size_kb ,
6 round((num_rows*avg_row_len/1024),2) actual_data_kb,
7 (round((blocks*8),2) - round((num_rows*avg_row_len/1024),2)) wasted_space_kb
8 from
9 dba_tab_subpartitions T, dba_tablespaces s
10 where
11 (round((blocks*8),2) > round((num_rows*avg_row_len/1024),2))
12 and
13 t.tablespace_name=s.tablespace_name
14 order by (round((blocks*8),2) - round((num_rows*avg_row_len/1024),2)) desc
15 )
16 where table_owner='TEST' AND TABLE_NAME='T_OBJ_SUB1'
17 group by table_owner,table_name;
TABLE_OWNE TABLE_NAME SUM(SIZE_KB) SUM(ACTUAL_DATA_KB) SUM(WASTED_SPACE_KB)
---------- ------------------------------ ------------ ------------------- --------------------
TEST T_OBJ_SUB1 2553184 26097.06 2527086.94 SQL>
SQL>
SQL>
So the important questions is how can we control the wasted spacefor partitioned/sub-partitioned tables.
There are TWO ways you can control the storage clause as mentioned below:
#1: specify 65k or smaller extent_size when you define the paritioned tables.
#2: set hiddent parameter _partition_large_extents to false. (We will discuss this in another article.)
Now let's examine method #1, when we create paritioned/sub-paritioned table T_OBJ_S2 with smaller initial_extent size i.e STORAGE(INITIAL 10K NEXT 20K). Oracle will allocate 64k initial extent_size. Please note that if you create 64k or lesser initial extent size Oracle will allocate at least 64k initial extent and the same algorithm for storage allocation will be applied similar to normal tables that mean first 16 extent size will be 64k and then 17th extent onward size would be 1MB and so on, which is evident from the below example.
With smaller extent size in this case all the data was able to fit in 54MB instead of 2.50GB in previous case with default storage.
Please note that it again depends on how densely you populate each extents, if you populate enough rows in each extents to optimally then it should be fine but if you use default extent allocation scheme for paritioned/sub-partitioned tables and you do not populate enough rows in each extents allocatged for the partitiones/sub-partitions then you may have huge wasted space in your database.
SQL>
SQL> CREATE TABLE "T_OBJ_S2"
2 ( "OWNER" VARCHAR2(128 BYTE) ,
3 "OBJECT_NAME" VARCHAR2(128 BYTE) ,
4 "SUBOBJECT_NAME" VARCHAR2(128 BYTE) ,
5 "OBJECT_ID" NUMBER,
6 "DATA_OBJECT_ID" NUMBER,
7 "OBJECT_TYPE" VARCHAR2(23 BYTE) ,
8 "CREATED" DATE,
9 "LAST_DDL_TIME" DATE,
10 "TIMESTAMP" VARCHAR2(19 BYTE) ,
11 "STATUS" VARCHAR2(7 BYTE) ,
12 "TEMPORARY" VARCHAR2(1 BYTE) ,
13 "GENERATED" VARCHAR2(1 BYTE) ,
14 "SECONDARY" VARCHAR2(1 BYTE) ,
15 "NAMESPACE" NUMBER,
16 "EDITION_NAME" VARCHAR2(128 BYTE) ,
17 "SHARING" VARCHAR2(18 BYTE) ,
18 "EDITIONABLE" VARCHAR2(1 BYTE) ,
19 "ORACLE_MAINTAINED" VARCHAR2(1 BYTE) ,
20 "APPLICATION" VARCHAR2(1 BYTE) ,
21 "DEFAULT_COLLATION" VARCHAR2(100 BYTE) ,
22 "DUPLICATED" VARCHAR2(1 BYTE) ,
23 "SHARDED" VARCHAR2(1 BYTE) ,
24 "CREATED_APPID" NUMBER,
25 "CREATED_VSNID" NUMBER,
26 "MODIFIED_APPID" NUMBER,
27 "MODIFIED_VSNID" NUMBER,
28 ins_dt date
29 ) SEGMENT CREATION IMMEDIATE
30 tablespace IPYPRTBPS1
31 STORAGE(INITIAL 10K NEXT 20K)
32 PARTITION BY RANGE ("INS_DT") INTERVAL (NUMTODSINTERVAL (1, 'DAY'))
33 SUBPARTITION BY HASH ( "OWNER", "OBJECT_NAME", "SUBOBJECT_NAME" )
34 SUBPARTITION TEMPLATE (
35 SUBPARTITION "T1" ,
36 SUBPARTITION "T2" ,
37 SUBPARTITION "T3" ,
38 SUBPARTITION "T4" ,
39 SUBPARTITION "T5" ,
40 SUBPARTITION "T6" ,
41 SUBPARTITION "T7" ,
42 SUBPARTITION "T8" )
43 (PARTITION "P_FIRST" VALUES LESS THAN (TIMESTAMP' 2018-02-12 00:00:00')
44 ( SUBPARTITION "P_FIRST_T1" SEGMENT CREATION DEFERRED
45 NOCOMPRESS ,
46 SUBPARTITION "P_FIRST_T2" SEGMENT CREATION DEFERRED
47 NOCOMPRESS ,
48 SUBPARTITION "P_FIRST_T3" SEGMENT CREATION DEFERRED
49 NOCOMPRESS ,
50 SUBPARTITION "P_FIRST_T4" SEGMENT CREATION DEFERRED
51 NOCOMPRESS ,
52 SUBPARTITION "P_FIRST_T5" SEGMENT CREATION DEFERRED
53 NOCOMPRESS ,
54 SUBPARTITION "P_FIRST_T6" SEGMENT CREATION DEFERRED
55 NOCOMPRESS ,
56 SUBPARTITION "P_FIRST_T7" SEGMENT CREATION DEFERRED
57 NOCOMPRESS ,
58 SUBPARTITION "P_FIRST_T8" SEGMENT CREATION DEFERRED
59 NOCOMPRESS ) ) ENABLE ROW MOVEMENT ;
Table created.
SQL>
SQL>
SQL> insert into t_obj_s2 (select o.*,o.created from dba_objects o);
195070 rows created.
SQL>
SQL>
SQL>
SQL> commit;
Commit complete.
SQL>
SQL>
SQL> BEGIN
2 dbms_stats.gather_table_stats(ownname => 'TEST', tabname => 'T_OBJ_S2',GRANULARITY=>'ALL', estimate_percent => dbms_stats.auto_sample_size, degree => '4',cascade => TRUE, method_opt=>'FOR ALL COLUMNS SIZE AUTO');
3 END;
4 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
SQL> col owner for a10
SQL> col segment_name for a20
SQL> col partition_name for a25
SQL> col segment_type for a25
SQL> col tablespace_name for a15
SQL> col extent_id for 99
SQL> col file_id for 99
SQL> col block_id for 9999999999
SQL> col bytes for 9999999999
SQL> col blocks for 99999
SQL>
SQL> select owner,segment_name,partition_name,segment_type,tablespace_name,extent_id,file_id,block_id,bytes,blocks
2 from dba_extents where owner='TEST' and segment_name='T_OBJ_S2';
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS
---------- -------------------- ------------------------- ------------------------- --------------- --------- ------- ----------- ----------- ------
TEST T_OBJ_S2 SYS_SUBP137941 TABLE SUBPARTITION IPYPRTBPS1 0 9 209144 65536 8
TEST T_OBJ_S2 SYS_SUBP137783 TABLE SUBPARTITION IPYPRTBPS1 0 9 160936 65536 8
TEST T_OBJ_S2 SYS_SUBP137783 TABLE SUBPARTITION IPYPRTBPS1 1 8 68768 65536 8
TEST T_OBJ_S2 SYS_SUBP137783 TABLE SUBPARTITION IPYPRTBPS1 2 11 56488 65536 8
TEST T_OBJ_S2 SYS_SUBP137783 TABLE SUBPARTITION IPYPRTBPS1 3 10 56504 65536 8
TEST T_OBJ_S2 SYS_SUBP137783 TABLE SUBPARTITION IPYPRTBPS1 4 9 282784 65536 8
TEST T_OBJ_S2 SYS_SUBP137788 TABLE SUBPARTITION IPYPRTBPS1 0 9 147672 65536 8
TEST T_OBJ_S2 SYS_SUBP137788 TABLE SUBPARTITION IPYPRTBPS1 1 8 64760 65536 8
TEST T_OBJ_S2 SYS_SUBP137788 TABLE SUBPARTITION IPYPRTBPS1 2 11 56512 65536 8
TEST T_OBJ_S2 SYS_SUBP137788 TABLE SUBPARTITION IPYPRTBPS1 3 10 56528 65536 8
TEST T_OBJ_S2 SYS_SUBP137788 TABLE SUBPARTITION IPYPRTBPS1 4 9 282808 65536 8
TEST T_OBJ_S2 SYS_SUBP137794 TABLE SUBPARTITION IPYPRTBPS1 0 9 160984 65536 8
TEST T_OBJ_S2 SYS_SUBP137794 TABLE SUBPARTITION IPYPRTBPS1 1 8 71920 65536 8
TEST T_OBJ_S2 SYS_SUBP137794 TABLE SUBPARTITION IPYPRTBPS1 2 11 56552 65536 8
TEST T_OBJ_S2 SYS_SUBP137871 TABLE SUBPARTITION IPYPRTBPS1 0 9 198816 65536 8
TEST T_OBJ_S2 SYS_SUBP137871 TABLE SUBPARTITION IPYPRTBPS1 1 8 60640 65536 8
TEST T_OBJ_S2 SYS_SUBP137877 TABLE SUBPARTITION IPYPRTBPS1 0 9 197840 65536 8
TEST T_OBJ_S2 SYS_SUBP137877 TABLE SUBPARTITION IPYPRTBPS1 1 8 60656 65536 8
TEST T_OBJ_S2 SYS_SUBP138053 TABLE SUBPARTITION IPYPRTBPS1 0 9 228568 65536 8
TEST T_OBJ_S2 SYS_SUBP137891 TABLE SUBPARTITION IPYPRTBPS1 0 9 198848 65536 8
:
:
:
TEST T_OBJ_S2 SYS_SUBP138163 TABLE SUBPARTITION IPYPRTBPS1 0 9 281800 65536 8
TEST T_OBJ_S2 SYS_SUBP138164 TABLE SUBPARTITION IPYPRTBPS1 0 9 281784 65536 8
TEST T_OBJ_S2 SYS_SUBP138166 TABLE SUBPARTITION IPYPRTBPS1 0 9 281792 65536 8
TEST T_OBJ_S2 SYS_SUBP138169 TABLE SUBPARTITION IPYPRTBPS1 0 9 281824 65536 8
TEST T_OBJ_S2 SYS_SUBP138170 TABLE SUBPARTITION IPYPRTBPS1 0 9 281816 65536 8
TEST T_OBJ_S2 SYS_SUBP138171 TABLE SUBPARTITION IPYPRTBPS1 0 9 281832 65536 8
TEST T_OBJ_S2 SYS_SUBP138178 TABLE SUBPARTITION IPYPRTBPS1 0 9 282840 65536 8
TEST T_OBJ_S2 SYS_SUBP138179 TABLE SUBPARTITION IPYPRTBPS1 0 9 282856 65536 8
TEST T_OBJ_S2 SYS_SUBP138180 TABLE SUBPARTITION IPYPRTBPS1 0 9 282848 65536 8
TEST T_OBJ_S2 SYS_SUBP138183 TABLE SUBPARTITION IPYPRTBPS1 0 9 282864 65536 8
TEST T_OBJ_S2 SYS_SUBP138186 TABLE SUBPARTITION IPYPRTBPS1 0 9 283800 65536 8
TEST T_OBJ_S2 SYS_SUBP138187 TABLE SUBPARTITION IPYPRTBPS1 0 9 283832 65536 8
TEST T_OBJ_S2 SYS_SUBP138189 TABLE SUBPARTITION IPYPRTBPS1 0 9 283816 65536 8
TEST T_OBJ_S2 SYS_SUBP138190 TABLE SUBPARTITION IPYPRTBPS1 0 9 282872 65536 8
TEST T_OBJ_S2 SYS_SUBP138191 TABLE SUBPARTITION IPYPRTBPS1 0 9 283824 65536 8
TEST T_OBJ_S2 SYS_SUBP138193 TABLE SUBPARTITION IPYPRTBPS1 0 9 283808 65536 8
753 rows selected.
SQL>
SQL> --Wasted space below HWM
SQL> col owner for a20
SQL> col table_name for a30
SQL> col LAST_ANALYZED for a20
SQL> col EXTENT_MANAGEMENT for a20
SQL> col SEGMENT_SPACE_MANAGEMENT for a20
SQL> col tablespace_name for a20
SQL> col sgment_space_mgmt for a15
SQL> col partition_name for a25
SQL> col subparition_name for a30
SQL>
SQL>
SQL> select * from
2 (
3 select
4 table_owner,table_name,PARTITION_NAME,subpartition_name,last_analyzed,s.tablespace_name,EXTENT_MANAGEMENT EXTENT_MGMT,SEGMENT_SPACE_MANAGEMENT SEGMENT_SPACE_MGMT,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,round((blocks*8),2) "size (kb)" ,
5 round((num_rows*avg_row_len/1024),2) "actual_data (kb)",
6 (round((blocks*8),2) - round((num_rows*avg_row_len/1024),2)) "wasted_space (kb)"
7 from
8 dba_tab_subpartitions T, dba_tablespaces s
9 where
10 -- (round((blocks*8),2) > round((num_rows*avg_row_len/1024),2))
11 --and
12 t.tablespace_name=s.tablespace_name
13 order by (round((blocks*8),2) - round((num_rows*avg_row_len/1024),2)) desc
14 )
15 where table_owner='TEST' AND TABLE_NAME='T_OBJ_S2' ;
TABLE_OWNE TABLE_NAME PARTITION_NAME SUBPARTITION_NAME LAST_ANALYZED TABLESPACE_NAME EXTENT_MGM SEGMEN NUM_ROWS BLOCKS EMPTY_BLOCKS size (kb) actual_data (kb) wasted_space (kb)
---------- ------------------------------ ------------------------- ------------------------- -------------------- -------------------- ---------- ------ ------------- --------- ------------ ---------- ---------------- -----------------
TEST T_OBJ_S2 SYS_P137816 SYS_SUBP137813 23-SEP-20 IPYPRTBPS1 LOCAL AUTO 5997 244 0 1952 802.33 1149.67
TEST T_OBJ_S2 SYS_P137816 SYS_SUBP137814 23-SEP-20 IPYPRTBPS1 LOCAL AUTO 5983 244 0 1952 806.3 1145.7
TEST T_OBJ_S2 SYS_P137816 SYS_SUBP137812 23-SEP-20 IPYPRTBPS1 LOCAL AUTO 5998 244 0 1952 808.32 1143.68
TEST T_OBJ_S2 SYS_P137816 SYS_SUBP137809 23-SEP-20 IPYPRTBPS1 LOCAL AUTO 6048 244 0 1952 809.16 1142.84
TEST T_OBJ_S2 SYS_P137816 SYS_SUBP137808 23-SEP-20 IPYPRTBPS1 LOCAL AUTO 6012 244 0 1952 810.21 1141.79
TEST T_OBJ_S2 SYS_P137816 SYS_SUBP137810 23-SEP-20 IPYPRTBPS1 LOCAL AUTO 6043 244 0 1952 814.39 1137.61
TEST T_OBJ_S2 SYS_P137816 SYS_SUBP137811 23-SEP-20 IPYPRTBPS1 LOCAL AUTO 6105 244 0 1952 816.78 1135.22
TEST T_OBJ_S2 SYS_P137816 SYS_SUBP137815 23-SEP-20 IPYPRTBPS1 LOCAL AUTO 6092 244 0 1952 820.99 1131.01
TEST T_OBJ_S2 P_FIRST P_FIRST_T3 23-SEP-20 IPYPRTBPS1 LOCAL AUTO 8850 244 0 1952 1218.6 733.4
TEST T_OBJ_S2 P_FIRST P_FIRST_T1 23-SEP-20 IPYPRTBPS1 LOCAL AUTO 8971 244 0 1952 1235.26 716.74
TEST T_OBJ_S2 P_FIRST P_FIRST_T8 23-SEP-20 IPYPRTBPS1 LOCAL AUTO 9067 244 0 1952 1239.63 712.37
TEST T_OBJ_S2 P_FIRST P_FIRST_T4 23-SEP-20 IPYPRTBPS1 LOCAL AUTO 9033 244 0 1952 1243.8 708.2
:
:
:
TEST T_OBJ_S2 SYS_P137969 SYS_SUBP137965 23-SEP-20 IPYPRTBPS1 LOCAL AUTO 0 0 0 0 0 0
TEST T_OBJ_S2 SYS_P137969 SYS_SUBP137966 23-SEP-20 IPYPRTBPS1 LOCAL AUTO 0 0 0 0 0 0
TEST T_OBJ_S2 SYS_P137969 SYS_SUBP137967 23-SEP-20 IPYPRTBPS1 LOCAL AUTO 0 0 0 0 0 0
TEST T_OBJ_S2 SYS_P137969 SYS_SUBP137968 23-SEP-20 IPYPRTBPS1 LOCAL AUTO 0 0 0 0 0 0
TEST T_OBJ_S2 SYS_P137978 SYS_SUBP137970 23-SEP-20 IPYPRTBPS1 LOCAL AUTO 0 0 0 0 0 0
TEST T_OBJ_S2 SYS_P137978 SYS_SUBP137972 23-SEP-20 IPYPRTBPS1 LOCAL AUTO 0 0 0 0 0 0
456 rows selected.
SQL>
SQL>
SQL> select table_owner,table_name,sum(size_kb),sum(actual_data_kb),sum(wasted_space_kb) from
2 (
3 select
4 table_owner,table_name,PARTITION_NAME,subpartition_name, last_analyzed,s.tablespace_name,EXTENT_MANAGEMENT EXTENT_MGMT,SEGMENT_SPACE_MANAGEMENT SEGMENT_SPACE_MGMT,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,
5 round((blocks*8),2) size_kb ,
6 round((num_rows*avg_row_len/1024),2) actual_data_kb,
7 (round((blocks*8),2) - round((num_rows*avg_row_len/1024),2)) wasted_space_kb
8 from
9 dba_tab_subpartitions T, dba_tablespaces s
10 where
11 (round((blocks*8),2) > round((num_rows*avg_row_len/1024),2))
12 and
13 t.tablespace_name=s.tablespace_name
14 order by (round((blocks*8),2) - round((num_rows*avg_row_len/1024),2)) desc
15 )
16 where table_owner='TEST' AND TABLE_NAME='T_OBJ_S2'
17 group by table_owner,table_name;
TABLE_OWNE TABLE_NAME SUM(SIZE_KB) SUM(ACTUAL_DATA_KB) SUM(WASTED_SPACE_KB)
---------- ------------------------------ ------------ ------------------- --------------------
TEST T_OBJ_S2 54072 26163.87 27908.13
SQL>
