30 July, 2011

More on COUNT()s -- 2

Continuing with my previous posts "Running a COUNT(Column) versus COUNT(*)" and"More on COUNT()s", I now go on to demonstrate some more "twists" :


SQL> drop table COUNT_ROWS_TBL ;

Table dropped.

SQL> create table COUNT_ROWS_TBL (Column_1 number, Column_2 number, Column_3 varchar2(100)) ;

Table created.

SQL> insert into COUNT_ROWS_TBL
2 select decode(mod(rownum,10),0,NULL,rownum) as Column_1,
3 rownum as Column_2,
4 'Column_3_Values' || dbms_random.string('X',80) as Column_3
5 from dual
6 connect by level < 1001
7 /

1000 rows created.

SQL> -- remember that Column_2 has values 1 to 1000 and has no NULLs
SQL> -- note how count(Column_1) excludes rows with a NULL
SQL> select count(Column_2) from COUNT_ROWS_TBL where Column_2 > 400;

COUNT(COLUMN_2)
---------------
600

SQL> select count(Column_1) from COUNT_ROWS_TBL where Column_2 > 400;

COUNT(COLUMN_1)
---------------
540

SQL> -- this is the count of rows that the query for count(Column_1) excludes
SQL> select count(*) from COUNT_ROWS_TBL where Column_2 > 400 and Column_1 is NULL;

COUNT(*)
----------
60

SQL>
SQL> -- Bitmap Index example : If we have a Bitmap index on Column_1
SQL> create bitmap index COUNT_ROWS_TBL_BMP_1 on COUNT_ROWS_TBL(Column_1);

Index created.

SQL> exec dbms_stats.gather_table_stats('','COUNT_ROWS_TBL',estimate_percent=>100,cascade=>TRUE);

PL/SQL procedure successfully completed.

SQL> set autotrace on
SQL> select count(Column_1) from COUNT_ROWS_TBL;

COUNT(COLUMN_1)
---------------
900


Execution Plan
----------------------------------------------------------
Plan hash value: 1724349832

------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 4 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
| 2 | BITMAP CONVERSION TO ROWIDS | | 1000 | 4000 | 4 (0)| 00:00:01 |
| 3 | BITMAP INDEX FAST FULL SCAN| COUNT_ROWS_TBL_BMP_1 | | | | |
------------------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
429 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> select count(*) from COUNT_ROWS_TBL;

COUNT(*)
----------
1000


Execution Plan
----------------------------------------------------------
Plan hash value: 2301416134

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | BITMAP CONVERSION COUNT | | 1000 | 4 (0)| 00:00:01 |
| 3 | BITMAP INDEX FAST FULL SCAN| COUNT_ROWS_TBL_BMP_1 | | | |
----------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
422 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> -- Now it could use a Bitmap index even if the column contains NULLs !
SQL> -- BUT ! The Count for the column excluded the NULLs !



So, we can have a COUNT of the rows returned from the WHERE clause (or all rows of the table if there is no WHERE clause) executed by :
1. A FULL TABLE SCAN
2. An INDEX [FAST] FULL SCAN
3. A BITMAP Index

In any case, if you specify a Column as your COUNT target and the Column does contain a NULL in one or more rows, the rows with the NULL are excluded.
On the other hand, if you specify a constaint (e.g. a "1") or a * as your COUNT target, no rows are excluded (other than rows that may have been excluded by filters specified in the WHERE clause).

Notice how I have NOT demonstrated a Primary Key index (or a Unique Index) being used for a Count. It is not necessary for an Index to be a Primary Key index for it to be usable by Oracle for this purpose. There are a number of Internet posts that suggest that Oracle can use a Primary Key to execute a count. The truth of the matter is that Oracle can use any Index if the index contains elements that are NOT NULL *OR* it can use a Bitmap Index because a Bitmap Index also captures NULLs.

Would anyone want to extent the test case to an Index Organized Table (an IOT) ?

.
.
.

29 July, 2011

More on COUNT()s

As a followup to my previous post "Running a COUNT(column) versus COUNT(*)", here's a demo of :
a. COUNT(column)
b. COUNT(constant)
c. COUNT(*)
d. COUNT(*) done via an Index

SQL> -- Create a test table with 3 columns
SQL> -- The first column happens to have a NULL value for every 10th row
SQL> -- Column_2 contains numbers, all greater than 0
SQL> -- so a query for "WHERE Column_2 > 0" retrieves ALL the rows
SQL> -- Column_3 contains a string
SQL>
SQL> create table COUNT_ROWS_TBL (Column_1 number, Column_2 number, Column_3 varchar2(100)) ;

Table created.

SQL> insert into COUNT_ROWS_TBL
2 select decode(mod(rownum,10),0,NULL,rownum) as Column_1,
3 rownum as Column_2,
4 'Column_3_Values' || dbms_random.string('X',80) as Column_3
5 from dual
6 connect by level < 1001
7 /

1000 rows created.

SQL> -- the table has 1000 rows, as evidenced by the output above
SQL> -- verify this :
SQL> select count(*) from COUNT_ROWS_TBL;

COUNT(*)
----------
1000

SQL>
SQL> -- First example : COUNT(column) doesn't count all the rows
SQL> -- if the column has a NULL in one or more rows
SQL>
SQL> -- note how count(Column_1) excludes rows with a NULL
SQL> select count(Column_1) from COUNT_ROWS_TBL ;

COUNT(COLUMN_1)
---------------
900

SQL> select count(Column_1) from COUNT_ROWS_TBL where Column_2 > 0;

COUNT(COLUMN_1)
---------------
900

SQL> -- this is the count of rows that the query for count(Column_1) excludes
SQL> select count(*) from COUNT_ROWS_TBL where Column_1 is NULL;

COUNT(*)
----------
100

SQL> select count(Column_2) from COUNT_ROWS_TBL;

COUNT(COLUMN_2)
---------------
1000

SQL> select count(Column_2) from COUNT_ROWS_TBL where Column_2 > 0;

COUNT(COLUMN_2)
---------------
1000

SQL> select count(Column_3) from COUNT_ROWS_TBL;

COUNT(COLUMN_3)
---------------
1000

SQL> select count(Column_3) from COUNT_ROWS_TBL where Column_2 > 0;

COUNT(COLUMN_3)
---------------
1000

SQL>
SQL>
SQL> -- Second example : COUNT(1) does count all the rows
SQL> -- because 1 is a constant, not-NULL, value
SQL> select count(1) from COUNT_ROWS_TBL ;

COUNT(1)
----------
1000

SQL> select count(1) from COUNT_ROWS_TBL where Column_2 > 0;

COUNT(1)
----------
1000

SQL> select count(1) from COUNT_ROWS_TBL;

COUNT(1)
----------
1000

SQL> select count(1) from COUNT_ROWS_TBL where Column_2 > 0;

COUNT(1)
----------
1000

SQL> select count(1) from COUNT_ROWS_TBL;

COUNT(1)
----------
1000

SQL> select count(1) from COUNT_ROWS_TBL where Column_2 > 0;

COUNT(1)
----------
1000

SQL>
SQL>
SQL> -- Third example : COUNT(*) might use an Index
SQL> -- If the index is on a column that is guaranteed to not contain NULLs
SQL>
SQL> set autotrace on
SQL> select count(*) from COUNT_ROWS_TBL;

COUNT(*)
----------
1000


Execution Plan
----------------------------------------------------------
Plan hash value: 3695206450

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| COUNT_ROWS_TBL | 1000 | 7 (0)| 00:00:01 |
-----------------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
23 consistent gets
0 physical reads
0 redo size
422 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> exec dbms_stats.gather_table_stats('','COUNT_ROWS_TBL',estimate_percent=>100,cascade=>TRUE);

PL/SQL procedure successfully completed.

SQL> select count(*) from COUNT_ROWS_TBL;

COUNT(*)
----------
1000


Execution Plan
----------------------------------------------------------
Plan hash value: 3695206450

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| COUNT_ROWS_TBL | 1000 | 7 (0)| 00:00:01 |
-----------------------------------------------------------------------------


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
23 consistent gets
0 physical reads
0 redo size
422 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> alter table COUNT_ROWS_TBL modify (Column_2 NOT NULL);

Table altered.

SQL> create index COUNT_ROWS_TBL_NDX on COUNT_ROWS_TBL(Column_2);

Index created.

SQL> -- NOW ! the Index will be used !
SQL> select count(*) from COUNT_ROWS_TBL;

COUNT(*)
----------
1000


Execution Plan
----------------------------------------------------------
Plan hash value: 1131752359

------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| COUNT_ROWS_TBL_NDX | 1000 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
7 consistent gets
3 physical reads
0 redo size
422 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> -- note how Oracle chose to use the Index now
SQL> -- Remember : It will use the Index IF :
SQL> --- a. The Indexed Column
SQL> (or at least one column in a composite index)
2 is a NOT NULL column
3 --- b. The calculated "Cost" (determined by the Query optimizer)
4 --- is lower for an Index [Fast] Full Scan than for a Full Table Scan
5
SQL>



So, a COUNT(column) and a COUNT(constant) and a COUNT(*) do not necessarily mean the same thing.


UPDATE :
See the subsequent post
.
.
.

27 July, 2011

25 July, 2011

Running a COUNT(column) versus COUNT(*)

Just a quick post about the recurring myths about COUNT operations in Oracle.
See the discussion in this forums thread.
.
UPDATE : Also see these two subsequent posts :
and
.
.
.

13 July, 2011

ENABLE ROW MOVEMENT with MSSM

As I demonstrated in my previous blog post "ENABLE ROW MOVEMENT" the ALTER TABLE ... ENABLE ROW MOVEMENT is not just for supporting the ALTER TABLE ... SHRINK SPACE.

Furthermore, unlike ALTER TABLE ... SHRINK SPACE which requires that the Table be created in a Tablespace with Segment Space Management AUTO ("ASSM"), ENABLE ROW MOVEMENT can be done for a table in a Segment Space Management MANUAL ("MSSM") Tablespace as well.


SQL> create tablespace MSSM
2 datafile '/addtl/oracle/oradata/orcl/MSSM.dbf' size 100M
3 extent management local segment space management manual
4 /

Tablespace created.

SQL>
SQL>
SQL> -- create a "normal" partitoned table
SQL> -- this time create it in an MSSM tablespace
SQL> drop table my_emp_tbl purge;
drop table my_emp_tbl purge
*
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> create table my_emp_tbl
2 (emp_id number not null primary key,
3 country_id varchar2(2),
4 emp_name varchar2(50),
5 join_date date)
6 partition by list (country_id)
7 (
8 partition my_emp_tbl_in values ('IN'),
9 partition my_emp_tbl_sg values ('SG'),
10 partition my_emp_tbl_us values ('US')
11 )
12 tablespace MSSM
13 /

Table created.

SQL>
SQL> select partition_name, tablespace_name
2 from user_tab_partitions
3 where table_name = 'MY_EMP_TBL'
4 /

PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
MY_EMP_TBL_IN MSSM
MY_EMP_TBL_SG MSSM
MY_EMP_TBL_US MSSM

SQL>
SQL>
SQL> insert into my_emp_tbl
2 select rownum,
3 decode(mod(rownum,3),0,'IN',1,'SG',2,'US'),
4 dbms_random.string('X',40),
5 sysdate-365+rownum
6 from dual connect by level < 100
7 /

99 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> -- collect statistics on row counts
SQL> exec dbms_stats.gather_table_stats('','MY_EMP_TBL',estimate_percent=>100,granularity=>'ALL');

PL/SQL procedure successfully completed.

SQL> select partition_name, num_rows
2 from user_tab_partitions
3 where table_name = 'MY_EMP_TBL'
4 order by partition_position
5 /

PARTITION_NAME NUM_ROWS
------------------------------ ----------
MY_EMP_TBL_IN 33
MY_EMP_TBL_SG 33
MY_EMP_TBL_US 33

SQL>
SQL> -- identify employee 4
SQL> select * from my_emp_tbl where emp_id = 4;

EMP_ID CO EMP_NAME
---------- -- --------------------------------------------------
JOIN_DATE
---------
4 SG WS46VU0RIJDKOIXXAZSEPWUIVG0QBHTL4FUBNYPY
17-JUL-10


SQL> -- change the employees country
SQL> update my_emp_tbl set country_id = 'IN' where emp_id = 4;
update my_emp_tbl set country_id = 'IN' where emp_id = 4
*
ERROR at line 1:
ORA-14402: updating partition key column would cause a partition change


SQL>
SQL> -- enable row movement
SQL> alter table my_emp_tbl enable row movement;

Table altered.

SQL> update my_emp_tbl set country_id = 'IN' where emp_id = 4;

1 row updated.

SQL>



I know for a fact that ALTER TABLE ... ENABLE ROW MOVEMENT appears in the 8i documentation --- predating ASSM Tablespaces.

Thus, ALTER TABLE ... ENABLE ROW MOVEMENT is independent of and significantly predates ALTER TABLE ... SHRINK.

.
.
.

Virtathon Sessions Schedule

The Sessions Schedule for Virtathon has been published.

View it here.

(note : The published session times are US EDT (GMT -4))

.
.
.

12 July, 2011

ENABLE ROW MOVEMENT

Since the ALTER TABLE SHRINK command appeared and "ENABLE ROW MOVEMENT" has been presented as a requirement, some DBAs have been confused about what it means to enable row movement.

This does *NOT* cause Oracle to automatically move a row. However, a row may be moved as a result of action by the DBA (e.g. ALTER TABLE SHRINK) or a User / Application. The latter is the case where a row in a Partitioned Table has to move from one Partition to another because the Partition Key itself in that row has been updated.
Note that updating the Partition Key (such that a row actually moves to another Partition) is frowned upon and is not enabled by default. In the rare case where your design has a flaw that rows have to move between Partitions, you need to ENABLE ROW MOVEMENT.

In the example below, a row has to move from the 'SG' Partition to the 'IN' Partition. This is disallowed by Oracle until and unless the DBA ENABLEs ROW MOVEMENT :

SQL> -- create a "normal" partitoned table
SQL> drop table my_emp_tbl purge;

Table dropped.

SQL> create table my_emp_tbl
2 (emp_id number not null primary key,
3 country_id varchar2(2),
4 emp_name varchar2(50),
5 join_date date)
6 partition by list (country_id)
7 (
8 partition my_emp_tbl_in values ('IN'),
9 partition my_emp_tbl_sg values ('SG'),
10 partition my_emp_tbl_us values ('US')
11 )
12 /

Table created.

SQL>
SQL> insert into my_emp_tbl
2 select rownum,
3 decode(mod(rownum,3),0,'IN',1,'SG',2,'US'),
4 dbms_random.string('X',40),
5 sysdate-365+rownum
6 from dual connect by level < 100
7 /

99 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> -- collect statistics on row counts
SQL> exec dbms_stats.gather_table_stats('','MY_EMP_TBL',estimate_percent=>100,granularity=>'ALL');

PL/SQL procedure successfully completed.

SQL> select partition_name, num_rows
2 from user_tab_partitions
3 where table_name = 'MY_EMP_TBL'
4 order by partition_position
5 /

PARTITION_NAME NUM_ROWS
------------------------------ ----------
MY_EMP_TBL_IN 33
MY_EMP_TBL_SG 33
MY_EMP_TBL_US 33

SQL>
SQL> -- identify employee 4
SQL> select * from my_emp_tbl where emp_id = 4;

EMP_ID CO EMP_NAME
---------- -- --------------------------------------------------
JOIN_DATE
---------
4 SG 9WSXMYH66V6I8B1LKKW7YRTG2VYQGY2OPIT54OFW
16-JUL-10


SQL> -- change the employees country
SQL> update my_emp_tbl set country_id = 'IN' where emp_id = 4;
update my_emp_tbl set country_id = 'IN' where emp_id = 4
*
ERROR at line 1:
ORA-14402: updating partition key column would cause a partition change


SQL>
SQL> -- enable row movement
SQL> alter table my_emp_tbl enable row movement;

Table altered.

SQL> update my_emp_tbl set country_id = 'IN' where emp_id = 4;

1 row updated.

SQL>


So it is not just ALTER TABLE SHRINK that the ENABLE ROW MOVEMENT is necessary for. Yet, it is likely a defect in your design if you have frequent movements of rows between Partitions. Remember that GLOBAL and LOCAL Indexes have to be updated.
.
.
.

Using WGET to download Patches

(more of a "public bookmark" here, because it's been a long time since I downloaded a patch)

Oracle Support's update on using WGET to download patches is in their blog.

.
.
.

09 July, 2011

Reading an AWR - 1

Given this extract from an AWR :
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 99.93 In-memory Sort %: 100.00
Library Hit %: 94.76 Soft Parse %: 96.21
Execute to Parse %: 30.63 Latch Hit %: 100.00
Parse CPU to Parse Elapsd %: 79.13 % Non-Parse CPU: 93.52

Should I be worried about the Parse ratios ?

I then look at the "SQL ordered by Parse calls" section :
-> Total Parse Calls:          24,049
-> Captured SQL account for 71.1% of Total

% Total
Parse Calls Executions Parses SQL Id
------------ ------------ --------- -------------
5,208 5,208 21.66 bsa0wjtftg3uw
select file# from file$ where ts#=:1

3,868 3,868 16.08 cm5vu20fhtnq1
select /*+ connect_by_filtering */ privilege#,level from sysauth$ connect by gra
ntee#=prior privilege# and privilege#>0 start with grantee#=:1 and privilege#>0

861 861 3.58 86708bvah4akq
select name from undo$ where file#=:1 and block#=:2 and ts#=:3 and status$
!= 1

660 660 2.74 0kkhhb2w93cx0
update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize=:9,e
xtpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL, :13),groups=decod
e(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,:
17),scanhint=:18, bitmapranges=:19 where ts#=:1 and file#=:2 and block#=:3

625 440 2.60 0v3dvmc22qnam
insert into sys.col_usage$ (obj#, intcol#, equality_preds, equijoin_preds, noneq
uijoin_preds, range_preds, like_preds, null_preds, timestamp) values ( :objn,
:coln, decode(bitand(:flag,1),0,0,1), decode(bitand(:flag,2),0,0,1), decod
e(bitand(:flag,4),0,0,1), decode(bitand(:flag,8),0,0,1), decode(bitand(:flag

Should I be worried about the parse calls on SYSAUTH$ ? What about the lookups on FILE$ that are parsed so many times ?



What do you guys say ?


I'll see what responses I get.
.
.
.
UPDATE : 16-Jul-11 :

There were 3 responses.
The first was that the "Top 5 timed events" must be checked. The third response was that the elapsed time and database time must be checked.

Here they are, with Load Profile included as well :

Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 1230 08-Jul-11 21:53:22 26 1.3
End Snap: 1234 08-Jul-11 23:00:45 32 1.1
Elapsed: 67.38 (mins)
DB Time: 3.45 (mins)


Top 5 Timed Foreground Events
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Avg
wait % DB
Event Waits Time(s) (ms) time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
DB CPU 132 63.7
log buffer space 9 3 349 1.5 Configurat
enq: CR - block range reuse ck 44 3 69 1.5 Other
log file switch (checkpoint in 2 2 1201 1.2 Configurat
log file switch completion 21 1 47 .5 Configurat

Host CPU (CPUs: 1 Cores: 1 Sockets: 1)
~~~~~~~~ Load Average
Begin End %User %System %WIO %Idle
--------- --------- --------- --------- --------- ---------
0.33 1.32 1.3 17.7 1.5 78.4

Load Profile Per Second Per Transaction Per Exec Per Call
~~~~~~~~~~~~ --------------- --------------- ---------- ----------
DB Time(s): 0.1 0.4 0.01 0.06
DB CPU(s): 0.0 0.2 0.00 0.04
Redo size: 341,639.6 2,539,120.1
Logical reads: 1,435.2 10,666.3
Block changes: 1,612.4 11,983.4
Physical reads: 0.9 7.0
Physical writes: 25.6 190.3
User calls: 0.9 6.5
Parses: 6.0 44.2
Hard parses: 0.2 1.7
W/A MB processed: 0.0 0.3
Logons: 0.1 0.5
Executes: 8.6 63.7
Rollbacks: 0.0 0.0
Transactions: 0.1


You can see that the there was very little activity in the database. Less than 4 minutes of database time in 67minutes of elapsed time. There was less than 1 user call per second and 6 parses per second.
So, there was *some* activity occurring (6 parses per second on machine with only 1 CPU/Core). But it didn't consume significant resources. 6 parses per second over 67minutes does account for the more than 24thousand parses.

Why the high activity on FILE$ ? There was a job that was periodically dropping a table, creating a new table, populating it with 400,000 rows, deleting them, doing a rollback and then a count. The job would, in a loop, after a noticeable "sleep" period, run another pass of drop-create-populate-delete-rollback-count.


In this case, the data dictionary queries/updates (FILE$, SYSAUTH$, UNDO$, SEG$, COL_USAGE$) were all because of the pattern of operations. (FILE$ to identify the file for the extent allocation, SEG$ to update the table segment information with each new extent added, SYSAUTH$ for the privileges, UNDO$ for the undo segment and COL_USAGE$ to update column usage on queries.


With respect to the second response about the data dictionary stats and object stats, these were not an issue here.
.
.
.

05 July, 2011

Multiple Channels in RMAN are not always balanced

Occasionally, we come across questions about multiple channels and parallelism in RMAN.
Although RMAN distributes the datafiles across the channels, it doesn't necessarily mean that each channel has the same I/O requirements and needs the same amount of time. One channel may be reading more data and writing a larger backup than another.


For example, in this database with 16 datafiles where data is not equally distributed across all the datafiles :

SQL> select file_id, sum(blocks) from dba_extents group by file_id order by 1;

FILE_ID SUM(BLOCKS)
---------- -----------
1 105632
2 122320
3 259880
4 18864
5 8952
13 152
14 31368
15 2912
16 2336
17 8

10 rows selected.

SQL>


I then run an RMAN backup with two channels :

RMAN> show device type;

RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET;

RMAN>

RMAN> backup as compressed backupset database;

Starting backup at 05_JUL_23_02_29
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=39 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00003 name=/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
input datafile file number=00014 name=/addtl/oracle/oradata/orcl/hemant01.dbf
input datafile file number=00016 name=/addtl/oracle/oradata/orcl/Uniform_64KB.dbf
input datafile file number=00015 name=/addtl/oracle/oradata/orcl/UNDO.dbf
input datafile file number=00017 name=/usr/tmp/X.dbf
input datafile file number=00002 name=/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
input datafile file number=00001 name=/home/oracle/app/oracle/oradata/orcl/system01.dbf
input datafile file number=00004 name=/home/oracle/app/oracle/oradata/orcl/users01.dbf
input datafile file number=00005 name=/home/oracle/app/oracle/oradata/orcl/example01.dbf
channel ORA_DISK_1: starting piece 1 at 05_JUL_23_02_32
channel ORA_DISK_2: starting compressed full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00006 name=/home/oracle/app/oracle/oradata/orcl/FLOW_1046101119510758.dbf
input datafile file number=00007 name=/home/oracle/app/oracle/oradata/orcl/FLOW_1146416395631714.dbf
input datafile file number=00008 name=/home/oracle/app/oracle/oradata/orcl/FLOW_1170420963682633.dbf
input datafile file number=00009 name=/home/oracle/app/oracle/oradata/orcl/FLOW_1194425963955800.dbf
input datafile file number=00010 name=/home/oracle/app/oracle/oradata/orcl/FLOW_1218408858999342.dbf
input datafile file number=00011 name=/home/oracle/app/oracle/oradata/orcl/FLOW_1242310449730067.dbf
input datafile file number=00012 name=/home/oracle/app/oracle/oradata/orcl/FLOW_1266412439758696.dbf
input datafile file number=00013 name=/home/oracle/app/oracle/oradata/orcl/APEX_1295922881855015.dbf
channel ORA_DISK_2: starting piece 1 at 05_JUL_23_02_33
channel ORA_DISK_2: finished piece 1 at 05_JUL_23_02_40
piece handle=/addtl/oracle/flash_recovery_area/ORCL/backupset/2011_07_05/o1_mf_nnndf_TAG20110705T230230_7169w9t3_.bkp tag=TAG20110705T230230 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:07
channel ORA_DISK_1: finished piece 1 at 05_JUL_23_06_40
piece handle=/addtl/oracle/flash_recovery_area/ORCL/backupset/2011_07_05/o1_mf_nnndf_TAG20110705T230230_7169w8s7_.bkp tag=TAG20110705T230230 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:04:08
Finished backup at 05_JUL_23_06_40


So, Channel ORA_DISK_2 did a backup of 8 datafiles in 7seconds while Channel ORA_DISK_1 took 248 seconds to run a backup of 9 datafiles !

The next time you wonder why one RMAN channel takes a longer time than the other ..... ask yourself if the two channels have been given the same amount of "work" to do.

In my case, Channel ORA_DISK_2 had to backup data from 152 blocks only but Channel ORA_DISK_1 had to backup data from 551,104 blocks (*assuming* that there are no "empty" blocks that were formerly part of used extents but are now free extents) :

SQL> select file_id, sum(blocks) from dba_extents
2 where file_id in
3 (select file_id from dba_data_files
4 where (file_name like '%FLOW%' or file_name like '%APEX%')
5 )
6 group by file_id
7 order by file_id
8 /

FILE_ID SUM(BLOCKS)
---------- -----------
13 152
-----------
sum 152

SQL> select file_id, sum(blocks) from dba_extents
2 where file_id not in
3 (select file_id from dba_data_files
4 where (file_name like '%FLOW%' or file_name like '%APEX%')
5 )
6 group by file_id
7 order by file_id
8 /

FILE_ID SUM(BLOCKS)
---------- -----------
1 105632
2 121032
3 259880
4 18864
5 8952
14 31368
15 3032
16 2336
17 8
-----------
sum 551104

9 rows selected.

SQL>
SQL> select sum(space) from dba_recyclebin;

SUM(SPACE)
----------
0

SQL>



Question : What can you do to address this "imbalance" ?

.
.
.