Wednesday, August 27, 2008

V$ADVISOR_PROGRESS

Oracle 11gR1

V$ADVISOR_PROGRESS displays information about the progress of advisor execution.

Column

Datatype

Description

SID

NUMBER

Session ID

SERIAL#

NUMBER

Session serial number

USERNAME

VARCHAR2(30)

Oracle user name

OPNAME

VARCHAR2(64)

Operation name

ADVISOR_NAME

VARCHAR2(64)

Advisor name

TASK_ID

NUMBER

Task ID

TARGET_DESC

VARCHAR2(32)

Description of the target of the advisor

SOFAR

NUMBER

Amount of work done so far

TOTALWORK

NUMBER

Total work to be done

UNITS

VARCHAR2(32)

Units that the work is measured in

BENEFIT_SOFAR

NUMBER

Benefit obtained so far

BENEFIT_MAX

NUMBER

Estimate of maximum benefit that could be obtained

FINDINGS

NUMBER

Number of findings so far

RECOMMENDATIONS

NUMBER

Number of recommendations so far

TIME_REMAINING

NUMBER

Estimate of time remaining for the completion of the task (in seconds)

START_TIME

DATE

Start time of the task

LAST_UPDATE_TIME

DATE

Last time progress was posted

ELAPSED_SECONDS

NUMBER

Elapsed time so far

ADVISOR_METRIC1

NUMBER

Value of the advisor-specific metric

METRIC1_DESC

VARCHAR2(64)

Description of the advisor-specific metric

EXECUTION_TYPE

VARCHAR2(64)

???

Oracle dynamic performance views

DBA_ENABLED_TRACES

Oracle 11gR1

DBA_ENABLED_TRACES displays information about enabled SQL traces.

Column

Datatype

NULL

Description

TRACE_TYPE

VARCHAR2(21)

Type of the trace:

· CLIENT_ID

· SERVICE

· SERVICE_MODULE

· SERVICE_MODULE_ACTION

· DATABASE

PRIMARY_ID

VARCHAR2(64)

Primary qualifier (specific client identifier or service name)

QUALIFIER_ID1

VARCHAR2(48)

Secondary qualifier (specific module name)

QUALIFIER_ID2

VARCHAR2(32)

Additional qualifier (specific action name)

WAITS

VARCHAR2(5)

Indicates whether waits are traced (TRUE) or not (FALSE)

BINDS

VARCHAR2(5)

Indicates whether binds are traced (TRUE) or not (FALSE)

INSTANCE_NAME

VARCHAR2(16)

Instance name for tracing restricted to named instances

Oracle data dictionary views

DBA_AUTO_SEGADV_CTL

Oracle 11gR1

DBA_AUTO_SEGADV_CTL exposes the control information used by the segment advisor. This information gives the DBA an idea of what is happening in the auto advisor.

Column

Datatype

NULL

Description

AUTO_TASKID

NUMBER

Unique task ID generated by the auto advisor

TABLESPACE_NAME

VARCHAR2(30)

Name of the tablespace containing the segment

SEGMENT_OWNER

VARCHAR2(30)

Owner of the segment

SEGMENT_NAME

VARCHAR2(81)

Name of the segment

SEGMENT_TYPE

VARCHAR2(18)

The type of segment (TABLE, INDEX, and so on)

PARTITION_NAME

VARCHAR2(30)

Name of the subsegment (if partitioned)

STATUS

VARCHAR2(40)

Status of the analysis:

· NEW - the segment/tablespace has not been analyzed

· BEING-PROCESSED - the segment/tablespace is being processed

· COMPLETE - the segment/tablespace has been analyzed

· ERROR - problem with the segment/tablespace

REASON

VARCHAR2(40)

Reason why this segment was chosen

REASON_VALUE

NUMBER

A value associated with the reason

CREATION_TIME

TIMESTAMP(6)

Time when this entry was created

PROCESSED_TASKID

NUMBER

Auto advisor task that was used to process the segment/tablespace

END_TIME

TIMESTAMP(6)

Time at which the advisor task was completed

Note:

1.

select auto_taskid, tablespace_name, segment_name, segment_type, status, reason, reason_value, end_time

from dba_auto_segadv_ctl

order by end_time desc;

AUTO_

TASKID

TABLESPACE_

NAME

SEGMENT_

NAME

SEGMENT_

TYPE

STATUS

REASON

REASON_

VALUE

END_TIME

694

USERS

F930_UK1

INDEX

COMPLETE

SPACE_USAGE

3379274

2008-08-26 10:31:43.145455 PM

694

BUS_TBS

WSLS_PK

INDEX

COMPLETE

SPACE_ALLOC

134217728

2008-08-26 10:30:21.255841 PM

694

BUS_IDX

BUS_VW_I

INDEX

COMPLETE

SCAN

7

2008-08-26 10:28:44.052631 PM

694

BUS_TBS

BUS_ _FK_I

INDEX

COMPLETE

IO

8822

2008-08-26 10:28:43.658807 PM

694

USERS

BUS_POLY

TABLE

COMPLETE

ADDM

177476597

2008-08-26 10:19:31.552508 PM

694

TBS_TAB

COMPLETE

TBS WARNING ALERT

88.3123224431818

2008-08-26 10:15:01.796381 PM

693

TBS_IDX

BUS_SP

TABLE

COMPLETE

SPACE_ALLOC

199475200

2008-08-25 10:05:07.651942 PM

Oracle data dictionary views

DBA_FEATURE_USAGE_STATISTICS

Thank you for visiting Spatial DBA - Oracle and ArcSDE.

Please visit Oracle DBA Tips (http://www.oracledbatips.com) for more Oracle DBA Tips.

=================================================================


Oracle 11gR1
DBA_FEATURE_USAGE_STATISTICS displays information about database feature usage statistics.

Column
Datatype
NULL
Description
DBID
NUMBER
NOT NULL
Database identifier of the database being tracked
NAME
VARCHAR2(64)
NOT NULL
Name of the feature (see table below)
VERSION
VARCHAR2(17)
NOT NULL
Database version in which the feature was tracked
DETECTED_USAGES
NUMBER
NOT NULL
Number of times the system has detected usage for the feature
TOTAL_SAMPLES
NUMBER
NOT NULL
Number of times the system has woken up and checked for feature usage
CURRENTLY_USED
VARCHAR2(5)
Indicates whether usage was detected the last time the system checked (TRUE) or not (FALSE)
FIRST_USAGE_DATE
DATE
First sample time the system detected usage of the feature
LAST_USAGE_DATE
DATE
Last sample time the system detected usage of the feature
AUX_COUNT
NUMBER
This column stores feature-specific usage data in number format.
FEATURE_INFO
CLOB
This column stores feature-specific usage data in character format.
LAST_SAMPLE_DATE
DATE
Amount of time (in seconds) between the last two usage sample times
LAST_SAMPLE_PERIOD
NUMBER
Amount of time (in hours) between the last two usage sample times
SAMPLE_INTERVAL
NUMBER
Sample interval
DESCRIPTION
VARCHAR2(128)
Description of the feature and usage detection logic (see table below)
DBA_FEATURE_USAGE_STATISTICS Statistics
Name
Description
Undo Advisor Undo Advisor has been used.
SQL Tuning Advisor SQL Tuning Advisor has been used.
Segment Advisor Segment Advisor has been used.
SQL Workload Manager SQL Workload Manager has been used.
Tune MView Tune MView has been used.
SQL Performance Impact SQL Performance Impact has been used.
SQL Repair Advisor SQL Repair Advisor has been used.
SQL Tuning Set (user) A SQL Tuning Set has been created in the database in a user schema.
SQL Tuning Set (system) A SQL Tuning Set has been created in the database in the SYS schema.
Automatic SQL Tuning Advisor Automatic SQL Tuning Advisor has been used.
SQL Profile SQL profiles have been used.
Database Replay: Workload Capture Database Replay: Workload was ever captured.
Database Replay: Workload Replay Database Replay: Workload was ever replayed.
Streams (system) Oracle Streams processes have been configured
Streams (user) Users have configured Oracle Streams AQ
Transparent Gateway Heterogeneous Connectivity, access to a non-Oracle system, has been configured.
Virtual Private Database (VPD) Virtual Private Database (VPD) policies are being used.
Workspace Manager There is at least one version enabled table.
XDB XDB feature is being used.
Application Express Application Express feature is being used.
LOB Persistent LOBs are being used.
Object Object feature is being used.
Extensibility Extensibility feature is being used.
SQL Plan Management SQL Plan Management has been used.
Rules Manager Rules Manager and Expression Filter
Oracle Utility Datapump (Export) Oracle Utility Datapump (Export) has been used.
Oracle Utility Datapump (Import) Oracle Utility Datapump (Import) has been used.
Result Cache The Result Cache feature has been used.
Oracle Utility SQL Loader (Direct Path Load) Oracle Utility SQL Loader (Direct Path Load) has been used.
Oracle Utility Metadata API Oracle Utility (Metadata API) has been used.
Transparent Data Encryption Transparent Database Encryption is being used. There is atleast one column or tablespace that is encrypted.
interMedia interMedia has been used
interMedia DICOM interMedia DICOM has been used
Materialized Views (User) User Materialized Views exist in the database
Change Data Capture Change Data Capture exit in the database
Services Oracle Services.
Semantics/RDF A semantic network has been created indicating usage of the Oracle Semantics Feature.
SecureFiles SecureFiles are being used
Automatic Segment Advisor Automatic Segment Advisor has been used.
Segment Shrink Segment Shrink has been used.
Advanced Replication Advanced Replication has been enabled.
Advanced Security External Global users are configured.
Audit Options Audit options in use.
Automatic Maintenance - Optimizer Statistics Gathering Automatic initiation of Optimizer Statistics Collection
Automatic Maintenance - Space Advisor Automatic initiation of Space Advisor
Automatic Maintenance - SQL Tuning Advisor Automatic initiation of SQL Tuning Advisor
Automatic Segment Space Management (system) Extents of locally managed tablespaces are managed automatically by Oracle.
Automatic Segment Space Management (user) Extents of locally managed user tablespaces are managed automatically by Oracle.
Automatic SQL Execution Memory Sizing of work areas for all dedicated sessions (PGA) is automatic.
Automatic Storage Manager Automatic Storage Management has been enabled
Automatic Undo Management Oracle automatically manages undo data using an UNDO tablespace.
Automatic Workload Repository A manual Automatic Workload Repository (AWR) snapshot was taken in the last sample period.
AWR Baseline At least one AWR Baseline has been created by the user
AWR Baseline Template At least one AWR Baseline Template has been created by the user
Backup Encryption Encrypted backups are being used.
Baseline Adaptive Thresholds Adaptive Thresholds have been configured.
Baseline Static Computations Static baseline statistics have been computed.
Change-Aware Incremental Backup Track blocks that have changed in the database.
Client Identifier Application User Proxy Authentication: Client Identifier is used at this specific time.
Clusterwide Global Transactions Clusterwide Global Transactions is being used.
CSSCAN Oracle Database has been scanned at least once for character set:CSSCAN has been run at least once.
Character Semantics Character length semantics is used in Oracle Database
Character Set Character set is used in Oracle Database
Data Guard Data Guard, a set of services, is being used to create, maintain, manage, and monitor one or more standby databases.
Data Mining There exist Oracle Data Mining models in the database.
Dynamic SGA The Oracle SGA has been dynamically resized through an ALTER SYSTEM SET statement.
EM Database Control EM Database Control Home Page has been visited at least once.
EM Grid Control EM Grid Control Database Home Page has been visited at least once.
File Mapping File Mapping, the mechanism that shows a complete mapping of a file to logical volumes and physical devices, is being used.
Flashback Database Flashback Database, a rewind button for the database, is enabled
Flashback Data Archive Flashback Data Archive, a historical repository of changes to data contained in a table, is used
Internode Parallel Execution Internode Parallel Execution is being used.
Label Security Oracle Label Security, that enables label-based access control Oracle applications, is being used.
Locally Managed Tablespaces (system) There exists tablespaces that are locally managed in the database.
Locally Managed Tablespaces (user) There exists user tablespaces that are locally managed in the database.
Messaging Gateway Messaging Gateway, that enables communication between non-Oracle messaging systems and Advanced Queuing (AQ), link configured.
Very Large Memory Very Large Memory is enabled.
Automatic Memory Tuning Automatic Memory Tuning is enabled.
Automatic SGA Tuning Automatic SGA Tuning is enabled.
Encrypted Tablespaces Encrypted Tablespaces is enabled.
MTTR Advisor Mean Time to Recover Advisor is enabled.
Multiple Block Sizes Multiple Block Sizes are being used with this database.
OLAP - Analytic Workspaces OLAP - the analytic workspaces stored in the database.
OLAP - Cubes OLAP - number of cubes in the OLAP catalog that are fully mapped and accessible by the OLAP API.
Oracle Managed Files Database files are being managed by Oracle.
Oracle Secure Backup Oracle Secure Backup is used for backups to tertiary storage.
Parallel SQL DDL Execution Parallel SQL DDL Execution is being used.
Parallel SQL DML Execution Parallel SQL DML Execution is being used.
Parallel SQL Query Execution Parallel SQL Query Execution is being used.
Partitioning (system) Oracle Partitioning option is being used - there is at least one partitioned object created.
Partitioning (user) Oracle Partitioning option is being used - there is at least one user partitioned object created.
Oracle Text Oracle Text is being used - there is at least one oracle text index
PL/SQL Native Compilation PL/SQL Native Compilation is being used - there is at least one natively compiled PL/SQL library unit in the database.
Real Application Clusters (RAC) Real Application Clusters (RAC) is configured.
Recovery Area The recovery area is configured.
Recovery Manager (RMAN) Recovery Manager (RMAN) is being used to backup the database.
RMAN - Disk Backup Recovery Manager (RMAN) is being used to backup the database to disk.
RMAN - Tape Backup Recovery Manager (RMAN) is being used to backup the database to tape.
Block Media Recovery Block Media Recovery is being used to repair the database.
Restore Point Restore Points are being used as targets for Flashback
Logfile Multiplexing Multiple members are used in a single log file group
Bigfile Tablespace Bigfile tablespace is being used
Transportable Tablespace Transportable tablespace is being used
Read Only Tablespace Read only tablespace is being used
Deferred Open Read Only Deferred open read only feature is enabled
Physical Standby with Real-Time Query Physical standby database is in real-time query mode
Backup Rollforward Backup Rollforward strategy is being used to backup the database.
Data Recovery Advisor Data Recovery Advisor (DRA) is being used to repair the database.
Resource Manager Oracle Database Resource Manager is being used to manage database resources.
Server Parameter File The server parameter file (SPFILE) was used to startup the database.
Shared Server The database is configured as Shared Server, where one server process can service multiple client programs.
Spatial There is at least one usage of the Oracle Spatial index metadata table.
ADDM ADDM has been used.
SQL Access Advisor SQL Access Advisor has been used.
Note:
1.
select name, detected_usages, total_samples, currently_used, first_usage_date, last_usage_date, last_sample_date
from dba_feature_usage_statistics
where version = '10.2.0.2.0';
NAME
DETECTED_
USAGES
TOTAL_
SAMPLES
CURRENTLY_
USED
FIRST_
USAGE_
DATE
LAST_
USAGE_
DATE
LAST_
SAMPLE_
DATE
Advanced Replication
0
115
FALSE
2008-08-25 11:45:14 PM
Advanced Security
36
115
TRUE
2007-12-24 10:34:24 PM
2008-08-25 11:45:14 PM
2008-08-25 11:45:14 PM
Audit Options
115
115
TRUE
2006-06-20 1:31:41 AM
2008-08-25 11:45:14 PM
2008-08-25 11:45:14 PM
Automatic Database Diagnostic Monitor
9
115
FALSE
2006-06-26 11:33:35 PM
2008-02-11 10:41:20 PM
2008-08-25 11:45:14 PM
Automatic Segment Space Management (system)
115
115
TRUE
2006-06-20 1:31:41 AM
2008-08-25 11:45:14 PM
2008-08-25 11:45:14 PM
Automatic Segment Space Management (user)
115
115
TRUE
2006-06-20 1:31:41 AM
2008-08-25 11:45:14 PM
2008-08-25 11:45:14 PM
Automatic SQL Execution Memory
115
115
TRUE
2006-06-20 1:31:41 AM
2008-08-25 11:45:14 PM
2008-08-25 11:45:14 PM
Automatic Storage Manager
0
115
FALSE
2008-08-25 11:45:14 PM
Automatic Undo Management
115
115
TRUE
2006-06-20 1:31:41 AM
2008-08-25 11:45:14 PM
2008-08-25 11:45:14 PM
Automatic Workload Repository
0
115
FALSE
2008-08-25 11:45:14 PM
Change-Aware Incremental Backup
0
115
FALSE
2008-08-25 11:45:14 PM
Client Identifier
0
115
FALSE
2008-08-25 11:45:14 PM
CSSCAN
0
115
FALSE
2008-08-25 11:45:14 PM
Character Semantics
0
115
FALSE
2008-08-25 11:45:14 PM
Character Set
115
115
TRUE
2006-06-20 1:31:41 AM
2008-08-25 11:45:14 PM
2008-08-25 11:45:14 PM
Data Guard
0
115
FALSE
2008-08-25 11:45:14 PM
Data Guard Broker
0
115
FALSE
2008-08-25 11:45:14 PM
Data Mining
0
115
FALSE
2008-08-25 11:45:14 PM
Dynamic SGA
22
115
TRUE
2008-03-31 11:34:32 PM
2008-08-25 11:45:14 PM
2008-08-25 11:45:14 PM
File Mapping
0
115
FALSE
2008-08-25 11:45:14 PM
Flashback Database
0
115
FALSE
2008-08-25 11:45:14 PM
Internode Parallel Execution
0
115
FALSE
2008-08-25 11:45:14 PM
Label Security
0
115
FALSE
2008-08-25 11:45:14 PM
Locally Managed Tablespaces (system)
115
115
TRUE
2006-06-20 1:31:41 AM
2008-08-25 11:45:14 PM
2008-08-25 11:45:14 PM
Locally Managed Tablespaces (user)
115
115
TRUE
2006-06-20 1:31:41 AM
2008-08-25 11:45:14 PM
2008-08-25 11:45:14 PM
Messaging Gateway
0
115
FALSE
2008-08-25 11:45:14 PM
MTTR Advisor
83
115
FALSE
2006-06-20 1:31:41 AM
2008-07-21 11:38:42 PM
2008-08-25 11:45:14 PM
Multiple Block Sizes
0
115
FALSE
2008-08-25 11:45:14 PM
OLAP - Analytic Workspaces
0
115
FALSE
2008-08-25 11:45:14 PM
OLAP - Cubes
0
115
FALSE
2008-08-25 11:45:14 PM
Oracle Managed Files
0
115
FALSE
2008-08-25 11:45:14 PM
Parallel SQL DDL Execution
41
115
FALSE
2006-06-20 1:31:41 AM
2008-05-19 11:31:48 PM
2008-08-25 11:45:14 PM
Parallel SQL DML Execution
0
115
FALSE
2008-08-25 11:45:14 PM
Parallel SQL Query Execution
4
115
FALSE
2008-04-28 11:25:06 PM
2008-05-19 11:31:48 PM
2008-08-25 11:45:14 PM
Partitioning (system)
115
115
TRUE
2006-06-20 1:31:41 AM
2008-08-25 11:45:14 PM
2008-08-25 11:45:14 PM
Partitioning (user)
0
115
FALSE
2008-08-25 11:45:14 PM
PL/SQL Native Compilation
0
115
FALSE
2008-08-25 11:45:14 PM
Protection Mode - Maximum Availability
0
115
FALSE
2008-08-25 11:45:14 PM
Protection Mode - Maximum Performance
115
115
TRUE
2006-06-20 1:31:41 AM
2008-08-25 11:45:14 PM
2008-08-25 11:45:14 PM
Protection Mode - Maximum Protection
0
115
FALSE
2008-08-25 11:45:14 PM
Protection Mode - Unprotected
0
115
FALSE
2008-08-25 11:45:14 PM
Real Application Clusters (RAC)
0
115
FALSE
2008-08-25 11:45:14 PM
Recovery Area
0
115
FALSE
2008-08-25 11:45:14 PM
Recovery Manager (RMAN)
115
115
TRUE
2006-06-20 1:31:41 AM
2008-08-25 11:45:14 PM
2008-08-25 11:45:14 PM
RMAN - Disk Backup
93
115
TRUE
2006-06-20 1:31:41 AM
2008-08-25 11:45:14 PM
2008-08-25 11:45:14 PM
RMAN - Tape Backup
42
115
FALSE
2006-06-20 1:31:41 AM
2007-04-03 12:10:45 AM
2008-08-25 11:45:14 PM
Resource Manager
0
115
FALSE
2008-08-25 11:45:14 PM
Segment Advisor
114
115
TRUE
2006-06-26 11:33:35 PM
2008-08-25 11:45:14 PM
2008-08-25 11:45:14 PM
Server Parameter File
45
115
TRUE
2007-10-22 11:25:41 PM
2008-08-25 11:45:14 PM
2008-08-25 11:45:14 PM
Shared Server
0
115
FALSE
2008-08-25 11:45:14 PM
Spatial
114
115
TRUE
2006-06-20 1:31:41 AM
2008-08-25 11:45:14 PM
2008-08-25 11:45:14 PM
SQL Access Advisor
0
115
FALSE
2008-08-25 11:45:14 PM
SQL Tuning Advisor
0
115
FALSE
2008-08-25 11:45:14 PM
SQL Tuning Set
0
115
FALSE
2008-08-25 11:45:14 PM
Standby Archival - LGWR
0
115
FALSE
2008-08-25 11:45:14 PM
Standby Archival - ARCH
0
115
FALSE
2008-08-25 11:45:14 PM
Standby Transmission
0
115
FALSE
2008-08-25 11:45:14 PM
Streams (system)
115
115
TRUE
2006-06-20 1:31:41 AM
2008-08-25 11:45:14 PM
2008-08-25 11:45:14 PM
Streams (user)
115
115
TRUE
2006-06-20 1:31:41 AM
2008-08-25 11:45:14 PM
2008-08-25 11:45:14 PM
Transparent Gateway
0
115
FALSE
2008-08-25 11:45:14 PM
Undo Advisor
1
115
FALSE
2008-04-21 11:23:22 PM
2008-04-21 11:23:22 PM
2008-08-25 11:45:14 PM
Virtual Private Database (VPD)
115
115
TRUE
2006-06-20 1:31:41 AM
2008-08-25 11:45:14 PM
2008-08-25 11:45:14 PM

Oracle data dictionary views