Databases Reference
In-Depth Information
Finding Percentage of Updated Rows
To find the number of update rows in an HCC table that have been updated or inserted via conventional inserts,
execute the script in Listing 16-11.
■
The script in listing 16-11 will execute
DBMS_COMPRESSION.GET_COMPRESSION_TYPE
for all rows in your table
and could take a long time to execute and consume a large amount of CPu resources on your machine, so proceed with
caution if you attempt to run against a large table.
Note
Listing 16-11.
lst16-11-allcomptypes.sql
SQL> select comptype,count(*) cnt,100*(count(*)/rowcount) pct
from (
select '&&owner' owner, '&&table_name' tabname, rowid myrowid,
decode(dbms_compression.get_compression_type('&&owner','&&table_name',rowid),
1,'No Compression', 2,'Basic/OLTP', 4,'HCC Query High',
8,'HCC Query Low', 16,'HCC Archive High', 32,'HCC Archive Low',
64,'Block') comptype,
(count(*) over ()) rowcount
from "&&owner"."&&table_name"
) group by comptype,rowcount;
Enter value for owner: D14
Enter value for table_name: MYOBJ_DMLTEST
CompType #Rows %ofTotal
-------------------- ------------ --------
Block 4,862 23.80
HCC Query High 15,560 76.17
No Compression 7 .03
SQL>
Concurrency and Locking
Oracle's documentation states that updates to a single row in an HCC table will lock the entire CU containing the
row. If you are suffering from lock contention and have HCC tables in your database that are updated, you can
validate this by mapping the block containing the first row being updated to the block containing the second row
updated and determining if they belong to the same compression unit. First, we will set up a test casing using the
MYOBJ_DMLTEST table created previously in this recipe, and query a handful of relevant pieces of information:
SQL> select * from (
2 select object_id,rowid,
3 dbms_rowid.rowid_to_absolute_fno(rowid,'D14','MYOBJ_DMLTEST') fno,
4 dbms_rowid.rowid_block_number(rowid,'BIGFILE') blk
5 from d14.myobj_dmltest
6 order by object_id)
7 where rownum < 5;
Search WWH ::
Custom Search