How to generate explain plan for query in Oracle ?

 Generate explain plan for query

SQL> explain plan for select * from test;

Explained.

SQL> explain plan set statement_id ='anurag' for select * from test;

Explained.

SQL>

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

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 1357081020

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |     1 |    40 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| TEST |     1 |    40 |     2   (0)| 00:00:01 |

--------------------------------------------------------------------------

Note

-----

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

   - dynamic statistics used: dynamic sampling (level=2)

12 rows selected.

SQL>

System statistics in oracle database ?

 

In oracle database system statistics provide the optimizer with information about the system hardware and workload information , such cpu speed , i/o performance .

this statistic help the cost based optimizer make better decision when generating the execution plan  .

Why Gather System Statistics?

• To provide the optimizer with accurate information about the system's performance.
• To improve query execution plans by accounting for CPU and I/O costs.
• To adapt the optimizer's behavior to the specific hardware and workload of the database.

Types of System Statistics

NOWORKLOAD Statistics:

Collected when the database is idle or under minimal workload.

Provides baseline hardware characteristics like CPU speed and I/O performance.

Default system statistics provided by Oracle.

WORKLOAD Statistics:

Collected during a representative workload period.

Provides information about the system's performance under actual workload conditions.

Useful for databases with specific workload patterns.

Start gathering statistics:

BEGIN

  DBMS_STATS.GATHER_SYSTEM_STATS('START');

END;

/

Stop gathering statistics:

BEGIN

  DBMS_STATS.GATHER_SYSTEM_STATS('STOP');

END;

/

Best Practices

Choose the Right Type of Statistics:

Use NOWORKLOAD statistics for general-purpose systems.

Use WORKLOAD statistics for systems with specific workload patterns.

Gather Statistics During Representative Workloads:

For WORKLOAD statistics, ensure the workload period reflects typical database usage.

Monitor and Update Regularly:

Gather system statistics periodically or after significant hardware changes (e.g., CPU or storage upgrades).

Backup Existing Statistics:

Before gathering new statistics, back up the current system statistics to restore them if needed.

How to create pluggable database in oracle ?

 create pluggable database in oracle 

SQL> show con_name 

CON_NAME

------------------------------

CDB$ROOT

SQL> 

SQL> show pdbs

    CON_ID CON_NAME   OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED   READ ONLY  NO

3 PDB   READ WRITE NO

SQL> 

SQL> 

SQL> 

SQL> 

SQL> create pluggable database demo1 admin user demo1 identified by demo1

  2  file_name_convert=('/u01/app/oracle/oradata/DB1/pdbseed','/u01/app/oracle/oradata/demo1');   

Pluggable database created.

SQL> show pdbs

    CON_ID CON_NAME   OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED   READ ONLY  NO

3 PDB   READ WRITE NO

4 DEMO1   MOUNTED

SQL> alter pluggable database demo1 open read write;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME   OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED   READ ONLY  NO

3 PDB   READ WRITE NO

4 DEMO1   READ WRITE NO

SQL> 

SQL> 

SQL>       

SQL> alter session set container=demo1;

Session altered.

SQL> show con_name 

CON_NAME

------------------------------

DEMO1

SQL> 

SQL> 

SQL> 

SQL> select name from v$pdbs;

NAME

--------------------

DEMO1

SQL> 

how to drop pluggable database in oracle ?

 Drop pluggable database in oracle

=> connect to the container database 

=> close the pluggable database 

=> unplug the pluggable database (optional)

=> drop the pluggable database 

    .if we are using the command without including datafiles , this only remove pdb not underline datafiles etc .

SQL> alter session set container =cdb$root;

Session altered.

SQL> show pdbs

    CON_ID CON_NAME   OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

 2 PDB$SEED   READ ONLY  NO

 3 PDB   READ WRITE NO

 4 DEMO1   READ WRITE NO

SQL> 

SQL> alter pluggable database demo1 close immediate;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME   OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

 2 PDB$SEED   READ ONLY  NO

 3 PDB   READ WRITE NO

 4 DEMO1   MOUNTED

SQL> drop pluggable database demo1 including datafiles;

Pluggable database dropped.

SQL> show pdbs

    CON_ID CON_NAME   OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

 2 PDB$SEED   READ ONLY  NO

 3 PDB   READ WRITE NO

SQL>