18 January, 2020

Running the (Segment) Space Advisor - on a Partitioned Table

Here is a quick demo on running the Segment Space Advisor manually

I need to start with the ADVISOR privilege

$sqlplus

SQL*Plus: Release 12.2.0.1.0 Production on Sat Jan 18 22:02:10 2020

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Enter user-name: system
Enter password: 
Last Successful login time: Sat Jan 18 2020 22:00:32 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> grant advisor to hemant;

Grant succeeded.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production


I can then connect with my account to run the Advisor

$sqlplus

SQL*Plus: Release 12.2.0.1.0 Production on Sat Jan 18 22:02:35 2020

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Enter user-name: hemant
Enter password: 
Last Successful login time: Sat Jan 18 2020 21:50:05 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> 
SQL> DECLARE
l_object_id NUMBER;
l_task_name VARCHAR2(32767) := 'Advice on My SALES_DATA Table';

BEGIN
  DBMS_ADVISOR.create_task (
    advisor_name      => 'Segment Advisor',
    task_name         => l_task_name
                            );

  DBMS_ADVISOR.create_object (
    task_name   => l_task_name,
    object_type => 'TABLE',
    attr1       => 'HEMANT',
    attr2       => 'SALES_DATA',
    attr3       => NULL,
    attr4       => NULL,
    attr5       => NULL,
    object_id   => l_object_id 
                             );

  DBMS_ADVISOR.set_task_parameter (
    task_name => l_task_name,
    parameter => 'RECOMMEND_ALL',
    value     => 'TRUE');

  DBMS_ADVISOR.execute_task(task_name => l_task_name);
end;
/
  
    2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27   28   29  
PL/SQL procedure successfully completed.

SQL>


I can then review the advise :

SQL> set serveroutput on
begin
FOR cur_rec IN (SELECT f.impact,
                         o.type,
                         o.attr1,
                         o.attr2,
                         o.attr3,
                         o.attr4,
                         f.message,
                         f.more_info
                  FROM   dba_advisor_findings f, dba_advisor_objects o
                  WHERE  f.object_id = o.object_id 
                  AND f.task_name = o.task_name
                  AND f.task_name = 'Advice on My SALES_DATA Table' 
                  ORDER BY f.impact DESC)
  LOOP
    DBMS_OUTPUT.put_line('..');
    DBMS_OUTPUT.put_line('Type             : ' || cur_rec.type);
    DBMS_OUTPUT.put_line('Schema           : ' || cur_rec.attr1);
    DBMS_OUTPUT.put_line('Table Name       : ' || cur_rec.attr2);
    DBMS_OUTPUT.put_line('Partition Name   : ' || cur_rec.attr3);
    DBMS_OUTPUT.put_line('Tablespace Name  : ' || cur_rec.attr4);
    DBMS_OUTPUT.put_line('Message          : ' || cur_rec.message);
    DBMS_OUTPUT.put_line('More info        : ' || cur_rec.more_info);
  END LOOP;
end;
/
  
  SQL>   2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26  ..
Type   : TABLE PARTITION
Schema   : HEMANT
Table Name  : SALES_DATA
Partition Name  : P_2015
Tablespace Name  : USERS
Message   : The free space in the object is less than 10MB.
More info  : Allocated Space:65536: Used Space:8192: Reclaimable Space :57344:
..
Type   : TABLE PARTITION
Schema   : HEMANT
Table Name  : SALES_DATA
Partition Name  : P_2016
Tablespace Name  : USERS
Message   : The free space in the object is less than 10MB.
More info  : Allocated Space:65536: Used Space:1016: Reclaimable Space :64520:
..
Type   : TABLE PARTITION
Schema   : HEMANT
Table Name  : SALES_DATA
Partition Name  : P_2017
Tablespace Name  : USERS
Message   : The free space in the object is less than 10MB.
More info  : Allocated Space:65536: Used Space:1016: Reclaimable Space :64520:
..
Type   : TABLE PARTITION
Schema   : HEMANT
Table Name  : SALES_DATA
Partition Name  : P_2018
Tablespace Name  : USERS
Message   : The free space in the object is less than 10MB.
More info  : Allocated Space:65536: Used Space:8192: Reclaimable Space :57344:
..
Type   : TABLE PARTITION
Schema   : HEMANT
Table Name  : SALES_DATA
Partition Name  : P_2019
Tablespace Name  : USERS
Message   : The free space in the object is less than 10MB.
More info  : Allocated Space:65536: Used Space:8192: Reclaimable Space :57344:
..
Type   : TABLE PARTITION
Schema   : HEMANT
Table Name  : SALES_DATA
Partition Name  : P_MAXVALUE
Tablespace Name  : USERS
Message   : The free space in the object is less than 10MB.
More info  : Allocated Space:65536: Used Space:8192: Reclaimable Space :57344:

PL/SQL procedure successfully completed.

SQL> 


Thus, it actually reports for each Partition in the table.


Note : Script based on script by Tim Hall  (@oraclebase)  at https://oracle-base.com/dba/script?category=10g&file=segment_advisor.sql


12 November, 2019

Basic Replication -- 11 : Indexes on a Materialized View

A Materialized View is actually also a physical Table (by the same name) that is created and maintained to store the rows that the MV query is supposed to present.

Since it is also a Table, you can build custom Indexes on it.

Here, my Source Table has an Index on OBJECT_ID :

SQL> create table source_table_1
  2  as select object_id, owner, object_name
  3  from dba_objects
  4  where object_id is not null
  5  /

Table created.

SQL> alter table source_table_1
  2  add constraint source_table_1_pk
  3  primary key (object_id)
  4  /

Table altered.

SQL> create materialized view log on source_table_1;

Materialized view log created.

SQL>


I then build Materialized View with  an additional Index on it :

SQL> create materialized view mv_1
  2  refresh fast on demand
  3  as select object_id as obj_id, owner as obj_owner, object_name as obj_name
  4  from source_table_1
  5  /

Materialized view created.

SQL> create index mv_1_ndx_on_owner
  2  on mv_1 (obj_owner)
  3  /

Index created.

SQL>


Let's see if this Index is usable.

SQL> exec  dbms_stats.gather_table_stats('','MV_1');

PL/SQL procedure successfully completed.

SQL> explain plan for
  2  select obj_owner, count(*)
  3  from mv_1
  4  where obj_owner like 'H%'
  5  group by obj_owner
  6  /

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2523122927

------------------------------------------------------------------------------------------
| Id  | Operation            | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                   |     2 |    10 |    15   (0)| 00:00:01 |
|   1 |  SORT GROUP BY NOSORT|                   |     2 |    10 |    15   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN   | MV_1_NDX_ON_OWNER |  5943 | 29715 |    15   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------

   2 - access("OBJ_OWNER" LIKE 'H%')
       filter("OBJ_OWNER" LIKE 'H%')



Note how this Materialized View has a column called "OBJ_OWNER"  (while the Source Table column is called "OWNER") and the Index ("MV_1_NDX_ON_OWNER") on this column is used.


You  would have also noted that you can run DBMS_STATS.GATHER_TABLE_STATS on a Materialized View and it's Indexes.

However, it is NOT a good idea to define your own Unique Indexes (including Primary Key) on a Materialized View.  During the course of a Refresh, the MV may not be consistent and the Unique constraint may be violated.   See Oracle Support Document # 67424.1