Servicii baze de date

Invisible Indexes in Oracle Database

• Data 09/04/2023
• Autor Vali Toncu
• -8
• 1940

Oracle permite ca indecșii să fie marcați ca invizibili. Indecșii invizibili sunt menținuți ca orice alt index, dar sunt ignorați de optimizator, cu excepția cazului în care parametrul OPTIMIZER_USE_INVISIBLE_INDEXES este setat la TRUE la nivel de instanță sau sesiune. Indecșii pot fi creați ca fiind invizibili folosind cuvântul cheie INVISIBLE, iar vizibilitatea lor poate fi comutată folosind comanda ALTER INDEX.

create index index_name on table_name(column_name) invisible;

alter index index_name invisible;
alter index index_name visible;

Indicii invizibili pot fi utili pentru procesele cu nevoi specifice de indexare, unde prezența indicilor poate afecta negativ alte zone funcționale. De asemenea, sunt utile pentru testarea impactului adăugării sau eliminării unui index.

Următorul script creează și populează un tabel, apoi creează un index invizibil pe acesta. create table tab1 as select level as id from dual connect by level <= 10000; create index ind1_id on tab1(id) invisible; exec dbms_stats.gather_table_stats(null, ‘tab1’, cascade=> true);

Starea curentă de vizibilitate a unui index este indicată de coloana VIZIBILITATE a vizualizărilor [DBA|ALL|USER]_INDEXES.

column index_name format a30
column visibility format a10

select index_name, visibility
from   user_indexes;

INDEX_NAME                     VISIBILITY
------------------------------ ----------
IND1_ID                      INVISIBLE

O interogare care utilizează coloana indexată din clauza WHERE ignoră indexul și efectuează o scanare completă a tabelului.

select * from tab1 where id = 9999;

select * from table(dbms_xplan.display_cursor);

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |       |       |     7 (100)|          |
|*  1 |  TABLE ACCESS FULL| TAB1   |     1 |     4 |     7   (0)| 00:00:01 |
----------------------------------------------------------------------------

Setarea parametrului OPTIMIZER_USE_INVISIBLE_INDEXES face ca indexul să fie disponibil pentru optimizator.

alter session set optimizer_use_invisible_indexes=true;

select * from tab1 where id = 9998;

select * from table(dbms_xplan.display_cursor);

------------------------------------------------------------------------------
| Id  | Operation        | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |           |       |       |     1 (100)|          |
|*  1 |  INDEX RANGE SCAN| IND1_ID |     1 |     4 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------

Dacă faceți indexul vizibil, acesta este încă disponibil pentru optimizator atunci când parametrul OPTIMIZER_USE_INVISIBLE_INDEXES este resetat.

alter session set optimizer_use_invisible_indexes=false;
alter index ii_tab_id visible;

select * from TAB1 where id = 9997;

select * from table(dbms_xplan.display_cursor);

------------------------------------------------------------------------------
| Id  | Operation        | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          |       |       |     1 (100)|          |
|*  1 |  INDEX RANGE SCAN| IND1_ID  |     1 |     4 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------

Dacă faceți din nou indexul invizibil, acesta nu mai este disponibil pentru optimizator.

alter index ind1_id invisible;

select * from tab1 where id = 9996;

select * from table(dbms_xplan.display_cursor);

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |       |       |     7 (100)|          |
|*  1 |  TABLE ACCESS FULL| TAB1  |     1 |     4 |     7   (0)| 00:00:01 |
----------------------------------------------------------------------------

Enterprise Manager Database Express in Database 12c Release 1

• Data 07/04/2023
• Autor Vali Toncu
• -3
• 1940

Key things to remember about Enterprise Manager Database Express.

• EM DB Express is not Cloud Control or Grid Control!
• EM DB Express is not a replacement for the DB Control from 11g, as it has significantly less functionality.
• A DBA will not be able to administer the database using just EM DB Express.

If your organisation uses Cloud Control, which it should, you will probably never use EM Database Express. If on the other hand you are playing around with the database and want a pretty interface to perform some tasks, the EM Database Express might be your answer.

Configuration

If you’ve done the sort of database installations described here, you’ve probably already got EM Database Express configured. Unlike the DB Control, it runs from inside the database using the XML DB infrastructure, so there are no additional parts to install or executables to start.

To get up and running, you just need to check the HTTPS port is set for the XML DB.

SQL> SELECT DBMS_XDB_CONFIG.gethttpport FROM dual;

GETHTTPPORT
-----------
          0

SQL> SELECT DBMS_XDB_CONFIG.gethttpsport FROM dual;

GETHTTPSPORT
------------
        5500

SQL> EXEC DBMS_XDB_CONFIG.sethttpsport(5500);

PL/SQL procedure successfully completed.


Once that is done, EM Database Express is accessible using the following type of URL.

https://<hostname>:<port>/em/

Example:

https://ol6-121.localdomain:5500/em/

Enter your database login details and click the “Login” button.

You are presented with the home page for the database.

Multitenant Configuration

For pluggable databases the configuration is a little different.

-- In the root container.
conn sys/SysPassword1@//localhost:1521/cdb1 as sysdba
exec dbms_xdb_config.sethttpsport(0);
exec dbms_xdb_config.setglobalportenabled(TRUE);

-- In each pluggable database.
conn sys/SysPassword1@//localhost:1521/pdb1 as sysdba
exec dbms_xdb_config.sethttpsport(5500);

Usage

The menu structure for DB Express is quite simple.

Configuration
  - Initialization Parameters
  - Memory
  - Database Feature Usage
  - Current Database Properties

Storage
  - Undo Management
  - Redo Log Groups
  - Archive Logs
  - Control Files

Security
  - Users
  - Roles

Performance
  - Performance Hub
  - SQL Tuning Advisor

You will notice, there is very little in the way of administration screens. This is not a tool a DBA will use to administer the database. Most of the screens are self explanatory. The only really interesting screen is the Performance Hub, which actually looks nicer than the Cloud Control equivalent.