Basic Oracle space monitoring scripts

select tbs.tablespace_name, tot.bytes/1024 total, tot.bytes/1024-sum(nvl(fre.bytes,0))/1024 used, sum(nvl(fre.bytes,0))/1024 free, (1-sum(nvl(fre.bytes,0))/tot.bytes)*100 pct, decode( greatest((1-sum(nvl(fre.bytes,0))/tot.bytes)*100, 90), 90, '', '*' ) pct_warn from dba_free_space fre, (select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) tot, dba_tablespaces tbs where tot.tablespace_name = tbs.tablespace_name and fre.tablespace_name(+) = tbs.tablespace_name group by tbs.tablespace_name, tot.bytes/1024, tot.bytes order by 5, 1 ;

What is Explain Plan in Oracle, & how do we use it?

An Explain Plan is a tool that you can use to have Oracle explain to you how it plans on executing your query. This is useful in tuning queries to the database to get them to perform better. Once you know how Oracle plans on executing your query, you can change your environment to run the query faster.
Before you can use the EXPLAIN PLAN command, you need to have a PLAN_TABLE installed. This can be done by simply running the $ORACLE_HOME/rdbms/admin/utlxplan.sql script in your schema. It creates the table for you. After you have the PLAN_TABLE created, you issue an EXPLAIN PLAN for the query you are interested in tuning. The command is of the form:

EXPLAIN PLAN SET STATEMENT_ID='somevalue' FOR some SQL statement;

You need to use a statement_id and then give your SQL statement. For instance, suppose I have a query to tune. How does it get executed? I issue the following in SQL*Plus:

SQL> explain plan set statement_id = 'q1' for 2 select object_name from test where object_name like 'T%'; Explained.

I used 'q1' for my statement id (short for query 1). But you can use anything you want. My SQL statement is the second line. Now I query the PLAN_TABLE to see how this statement is executed. This is done with the following query:

SQL> SELECT LPAD(' ',2*(level-1)) || operation || ' ' || options || ' ' || 2 object_name || ' ' || DECODE(id,0,'Cost = ' || position) AS "Query Plan",other 3 FROM plan_table 4 START WITH id = 0 5 AND statement_id='q1' 6 CONNECT BY PRIOR ID = PARENT_ID 7* AND statement_id = 'q1' Query Plan OTHER -------------------------------------------------- -------------------------------------------------- SELECT STATEMENT Cost = TABLE ACCESS FULL TEST

This tells me that my SQL statement will perform a FULL table scan on the TEST table (TABLE ACCESS FULL TEST). Now let's add an index on that table!

SQL> create index test_name_idx on test(object_name); Index created. SQL> truncate table plan_table; Table truncated. SQL> explain plan set statement_id = 'q1' for 2 select object_name from test where object_name like 'T%'; Explained. SQL> SELECT LPAD(' ',2*(level-1)) || operation || ' ' || options || ' ' || 2 object_name || ' ' || DECODE(id,0,'Cost = ' || position) AS "Query Plan",other 3 FROM plan_table 4 START WITH id = 0 5 AND statement_id='q1' 6 CONNECT BY PRIOR ID = PARENT_ID 7* AND statement_id = 'q1' Query Plan OTHER -------------------------------------------------- -------------------------------------------------- SELECT STATEMENT Cost = INDEX RANGE SCAN TEST_NAME_IDX

I added an index to the table. Before I issue another EXPLAIN PLAN, I truncate the contents of my PLAN_TABLE to prepare for the new plan. Then I query the PLAN TABLE. Notice that this time I'm using an index (TEST_NAME_IDX) that I created!! Hopefully, this query will run faster now that it has an index to use. But this may not always be the case.

Oracle Database Performance Tuning Steps.

Using Baselines

Comparative Performance Analysis with AWR Baselines
Automatic Workload Repository Baselines
Moving Window Baseline
Baselines in Performance Page Settings & Baseline Templates
AWR Baselines & Creating AWR Baselines
Managing Baselines with PL/SQL & Baseline Views
Performance Monitoring and Baselines & Defining Alert Thresholds Using a Static Baseline

Using AWR Based Tools

Automatic Maintenance Tasks
ADDM Performance Monitoring

Identifying Problem SQL Statements

SQL Statement Processing Phases & Role of the Oracle Optimizer
Identifying Bad SQL, Real Time SQL Monitoring (a 11.1 feature new lesson in NF L-15) & TOP SQL Reports
What Is an Execution Plan? Methods for Viewing Execution Plans & Uses of Execution Plans
DBMS_XPLAN Package: Overview & EXPLAIN PLAN Command
Reading an Execution Plan, Using the V$SQL_PLAN View & Querying the AWR
SQL*Plus AUTOTRACE & SQL Trace Facility
How to Use the SQL Trace Facility
Generate an Optimizer Trace

Influencing the Optimizer

Functions of the Query Optimizer, Selectivity, Cardinality and Cost & Changing Optimizer Behavior
Using Hints, Optimizer Statistics & Extended Statistics
Controlling the Behavior of the Optimizer with Parameters
Enabling Query Optimizer Features & Influencing the Optimizer Approach
Optimizing SQL Statements, Access Paths & Choosing an Access Path
Join & Sort Operations
How the Query Optimizer Chooses Execution Plans for Joins
Reducing the Cost

Using SQL Performance Analyzer

Real Application Testing: Overview & Use Cases
SQL Performance Analyzer: Process & Capturing the SQL Workload
Creating a SQL Performance Analyzer Task & SPA (NF Lesson 9) DBMS_SQLTUNE.CREATE_TUNING_TASK
Optimizer Upgrade Simulation & SQL Performance Analyzer Task Page
Comparison Report & Comparison Report SQL Detail
Tuning Regressing Statements & Preventing Regressions
Parameter Change Analysis & Guided Workflow Analysis
SQL Performance Analyzer: PL/SQL Example & Data Dictionary Views

SQL Performance Management

Maintaining SQL Performance and Optimizer Statistics & Automated Maintenance Tasks
Statistic Gathering Options & Setting Statistic Preferences
Restore Statistics
Deferred Statistics Publishing: Overview & Example
Automatic SQL Tuning: Overview
SQL Tuning Advisor: Overview
Using the SQL Access Advisor
SQL Plan Management: Overview

Tuning PGA and Temporary Space
SQL Memory Usage & Performance Impact
SQL Memory Manager
Configuring Automatic PGA Memory & Setting PGA_AGGREGATE_TARGET Initially
Monitoring & Tuning SQL Memory Usage
PGA Target Advice Statistics & Histograms
Automatic PGA and Enterprise Manager & Automatic PGA and AWR Reports
Temporary Tablespace Management: Overview & Monitoring Temporary Tablespace
Temporary Tablespace Shrink & Tablespace Option for Creating Temporary Table

DBA_TABLESPACES

DBA_TABLESPACES and USER_TABLESPACES views indicates if the tablespace is encrypted or not.

SELECT tablespace_name, encrypted FROM dba_tablespaces;

TABLESPACE_NAME                ENC
------------------------------ ---
SYSTEM                         NO
SYSAUX                         NO
UNDOTBS1                       NO
TEMP                           NO
USERS                          NO

 

Listing Tablespaces and Default Storage Parameters

 

SELECT TABLESPACE_NAME "TABLESPACE",INITIAL_EXTENT, NEXT_EXTENT ,MIN_EXTENTS,

MAX_EXTENTS ,PCT_INCREASEFROM DBA_TABLESPACES;

 

How to check tablespace usage in Oracle

select sysdate, a.owner username, a.tablespace_name, round(b.total_space/1024/1024,2) "Total (MB)", round(sum(a.bytes)/1024/1024,2) "Used (MB)", round(sum(a.bytes/b.total_space)*100,2) "% Used"

from dba_segments a, (select tablespace_name, sum(bytes) total_space

                      from dba_data_files

                      group by tablespace_name) b

where a.tablespace_name not in ('SYSAUX', 'SYSTEM', 'UNDOTBS1', 'UNDOTBS2')

and a.tablespace_name = b.tablespace_name

group by a.tablespace_name, a.owner, b.total_space/1024/1024

order by a.tablespace_name, a.owner;

 

Tablespace Data Dictionary Views

View	Description
V$TABLESPACE	Name and number of all tablespaces from the control file.
V$ENCRYPTED_TABLESPACES	Name and encryption algorithm of all encrypted tablespaces.
DBA_TABLESPACES, USER_TABLESPACES	Descriptions of all (or user accessible) tablespaces.
DBA_TABLESPACE_GROUPS	Displays the tablespace groups and the tablespaces that belong to them.
DBA_SEGMENTS, USER_SEGMENTS	Information about segments within all (or user accessible) tablespaces.
DBA_EXTENTS, USER_EXTENTS	Information about data extents within all (or user accessible) tablespaces.
DBA_FREE_SPACE, USER_FREE_SPACE	Information about free extents within all (or user accessible) tablespaces.
DBA_TEMP_FREE_SPACE	Displays the total allocated and free space in each temporary tablespace.
V$DATAFILE	Information about all datafiles, including tablespace number of owning tablespace.
V$TEMPFILE	Information about all tempfiles, including tablespace number of owning tablespace.
DBA_DATA_FILES	Shows files (datafiles) belonging to tablespaces.
DBA_TEMP_FILES	Shows files (tempfiles) belonging to temporary tablespaces.
V$TEMP_EXTENT_MAP	Information for all extents in all locally managed temporary tablespaces.
V$TEMP_EXTENT_POOL	For locally managed temporary tablespaces: the state of temporary space cached and used for by each instance.
V$TEMP_SPACE_HEADER	Shows space used/free for each tempfile.
DBA_USERS	Default and temporary tablespaces for all users.
DBA_TS_QUOTAS	Lists tablespace quotas for all users.
V$SORT_SEGMENT	Information about every sort segment in a given instance. The view is only updated when the tablespace is of the TEMPORARY type.
V$TEMPSEG_USAGE	Describes temporary (sort) segment usage by user for temporary or permanent tablespaces.

Statistics on Tables, Indexes and Columns

• DBA_TABLES
• DBA_OBJECT_TABLES
• DBA_TAB_STATISTICS
• DBA_TAB_COL_STATISTICS
• DBA_TAB_HISTOGRAMS
• DBA_TAB_COLS
• DBA_COL_GROUP_COLUMNS
• DBA_INDEXES
• DBA_IND_STATISTICS
• DBA_CLUSTERS
• DBA_TAB_PARTITIONS
• DBA_TAB_SUBPARTITIONS
• DBA_IND_PARTITIONS
• DBA_IND_SUBPARTITIONS
• DBA_PART_COL_STATISTICS
• DBA_PART_HISTOGRAMS
• DBA_SUBPART_COL_STATISTICS
• DBA_SUBPART_HISTOGRAMS

Oracle System Tables

System Table	Description
ALL_ARGUMENTS	Arguments in object accessible to the user
ALL_CATALOG	All tables, views, synonyms, sequences accessible to the user
ALL_COL_COMMENTS	Comments on columns of accessible tables and views
ALL_CONSTRAINTS	Constraint definitions on accessible tables
ALL_CONS_COLUMNS	Information about accessible columns in constraint definitions
ALL_DB_LINKS	Database links accessible to the user
ALL_ERRORS	Current errors on stored objects that user is allowed to create
ALL_INDEXES	Descriptions of indexes on tables accessible to the user
ALL_IND_COLUMNS	COLUMNs comprising INDEXes on accessible TABLES
ALL_LOBS	Description of LOBs contained in tables accessible to the user
ALL_OBJECTS	Objects accessible to the user
ALL_OBJECT_TABLES	Description of all object tables accessible to the user
ALL_SEQUENCES	Description of SEQUENCEs accessible to the user
ALL_SNAPSHOTS	Snapshots the user can access
ALL_SOURCE	Current source on stored objects that user is allowed to create
ALL_SYNONYMS	All synonyms accessible to the user
ALL_TABLES	Description of relational tables accessible to the user
ALL_TAB_COLUMNS	Columns of user's tables, views and clusters
ALL_TAB_COL_STATISTICS	Columns of user's tables, views and clusters
ALL_TAB_COMMENTS	Comments on tables and views accessible to the user
ALL_TRIGGERS	Triggers accessible to the current user
ALL_TRIGGER_COLS	Column usage in user's triggers or in triggers on user's tables
ALL_TYPES	Description of types accessible to the user
ALL_UPDATABLE_COLUMNS	Description of all updatable columns
ALL_USERS	Information about all users of the database
ALL_VIEWS	Description of views accessible to the user
DATABASE_COMPATIBLE_LEVEL	Database compatible parameter set via init.ora
DBA_DB_LINKS	All database links in the database
DBA_ERRORS	Current errors on all stored objects in the database
DBA_OBJECTS	All objects in the database
DBA_ROLES	All Roles which exist in the database
DBA_ROLE_PRIVS	Roles granted to users and roles
DBA_SOURCE	Source of all stored objects in the database
DBA_TABLESPACES	Description of all tablespaces
DBA_TAB_PRIVS	All grants on objects in the database
DBA_TRIGGERS	All triggers in the database
DBA_TS_QUOTAS	Tablespace quotas for all users
DBA_USERS	Information about all users of the database
DBA_VIEWS	Description of all views in the database
DICTIONARY	Description of data dictionary tables and views
DICT_COLUMNS	Description of columns in data dictionary tables and views
GLOBAL_NAME	global database name
NLS_DATABASE_PARAMETERS	Permanent NLS parameters of the database
NLS_INSTANCE_PARAMETERS	NLS parameters of the instance
NLS_SESSION_PARAMETERS	NLS parameters of the user session
PRODUCT_COMPONENT_VERSION	version and status information for component products
ROLE_TAB_PRIVS	Table privileges granted to roles
SESSION_PRIVS	Privileges which the user currently has set
SESSION_ROLES	Roles which the user currently has enabled.
SYSTEM_PRIVILEGE_MAP	Description table for privilege type codes. Maps privilege type numbers to type names
TABLE_PRIVILEGES	Grants on objects for which the user is the grantor, grantee, owner, or an enabled role or PUBLIC is the grantee
TABLE_PRIVILEGE_MAP	Description table for privilege (auditing option) type codes. Maps privilege (auditing option) type numbers to type names