Index

Search in book...
Toggle Font Controls
Create new playlist

Name your new playlist

Playlist description (optional)
Sign In

Email address

Password

Forgot Password?

or

Continue with Facebook

Continue with Google
Sign Up

Full Name

Email address

Confirm Email Address

Password

or

Continue with Facebook

Continue with Google

Previous Chapter

CHAPTER 15: Executing SQL in Parallel

Index

A

Active Session History (ASH)

ashrpt.sql script

awrrpt.sql script

background events

blocking sessions

circular buffer

data dictionary

DBA_HIST_ACTIVE_SESS_HISTORY view

SESSION_STATE column

V$ACTIVE_SESSION_HISTORY view

DBA_HIST_ACTIVE_SESS_HISTORY view

enterprise manager

DBA_HIST_EVENT_NAME view

Filter drop-down menu

performance tuning activities

P1/P2/P3 values

real-time/near real-time session information

report section information

SQL command types

ashrpt.sql script

Automated SQL tuning

automatic SQL tuning advisor

begin and end AWR snapshot IDs

creating SQL tuning set object

DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY funcion

DBMS_AUTO_TASK_ADMIN.ENABLE/DISABLE procedure

DBMS_SQLTUNE.CREATE_SQLSET procedure

DBMS_SQLTUNE.CREATE_TUNING_TASK procedure

SQL_ID and AWR snapshot IDs

SQL ID, Cursor Cache

SQL tuning set name

text of SQL statement

diagrammatic representation

maintenance task view descriptions

CAPTURE_CURSOR_CACHE_SQLSET parameter descriptions

DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE procedure

DBMS_SQLTUNE.SELECT_CURSOR_CACHE funcion

modifying maintanence window

Automatic SQL Tuning

statistics gathering

SQL performance problems

SQL tuning advisor

from Enterprise Manager

from SQL Developer

optimizer tuning modes

steps to run manually

SQL tuning sets

deleting statements

displaying contents

transporting to another database

DBMS_AUTO_SQLTUNE.SET_AUTO_TUNING_TASK parameter

e-mailing output

findings section

general information section

REPORT_AUTO_TUNING_TASK function

SCRIPT_TUNING_TASK function

summary section

viewing resource-intensive

with Enterprise Manager

Automatic Database Diagnostic Monitor (ADDM)

DBMS_ADDM Package

performance recommendations

SQL*plus script

Automatic Diagnostic Repository Command Interpreter (ADRCI)

diagnostic tasks

homepath command

in interactive mode

V$DIAG_INFO view

Automatic memory management

caching client result sets

CLIENT_RESULT_CACHE_LAG

CLIENT_RESULT_CACHE_SIZE

OCIStmtExecute()

optional client-side configuration file

caching PL/SQL function

caching SQL query result

read consistency requirements

RESULT_CACHE_MODE initialization parameter

table annotations and query hints

configuring server query cache

DBMS_RESULT_CACHE.FLUSH procedure

initialization parameters

materialized views

PL/SQL collection

RESULT_CACHE_MAX_RESULT

RESULT_CACHE_MAX_SIZE

RESULT_CACHE_REMOTE_EXPIRATION

implementing steps

managing server result cache

DBMS_RESULT_CACHE.STATUS()

shared pool percentage

MEMORY_MAX_TARGET PARAMETER

memory resizing operations

V$MEMORY_RESIZE_OPS

V$MEMORY_TARGET_ADVICE

memory structures

MEMORY_TARGET parameter

optimizing memory usage

Oracle Database Smart Flash Cache

DB_FLASH_CACHE_FILE

DB_FLASH_CACHE_SIZE

PGA memory allocation

PGA_AGGREGATE_TARGET parameter

V$SQL_WORKAREA_HISTOGRAM

V$SYSSTAT and V$SESSTAT

pga_memory_target

redo log buffer tuning

SCOPE parameter

setting minimum values

Automatic Segment Space Management (ASSM)

Automatic SQL Tuning Advisor

Automatic workload repository (AWR)

active session information (see Active Session History (ASH))

baseline statistics

adaptive metrics

awrextr.sql script

awrload.sql script

AWR_REPORT_TEXT function

awrrpt.sql script

CREATE_BASELINE_TEMPLATE procedure

DBA_HIST_BASELINE_TEMPLATE view

DROP_BASELINE procedure

DROP_BASELINE_TEMPLATE procedure

enterprise manager

fixed baselines

moving baselines

performance statistics

RENAME_BASELINE procedure

retention period

historical database performance statistics

interval and retention periods

interval-based historical statistics

awrrpt.sql script

database instance

data dictionary

enterprise manager

single SQL statement

statistical components

STATISTICS_LEVEL parameter

type of information

UTLBSTAT/UTLESTAT and Statspack

AUTOSTATS_TARGET parameter

B

Baseline statistics

adaptive metrics

awrextr.sql script

awrload.sql script

AWR_REPORT_TEXT function

awrrpt.sql script

CREATE_BASELINE_TEMPLATE procedure

DBA_HIST_BASELINE_TEMPLATE view

DROP_BASELINE procedure

DROP_BASELINE_TEMPLATE procedure

enterprise manager

fixed baselines

moving baselines

RENAME_BASELINE procedure

retention period

Bitmap index, star schema

data warehouse environments

filtering data, fact table

Bitmap join index

database network connectivity troubleshooting

data dictionary

iostat column descriptions

iostat output examination

network-intensive processes detection

diagnosing sources

netstat command

Solaris, prstat utility

virtual memory statistics (vmstat)

output interpretation

processing jobs section

DBMS_SPACE CREATE_INDEX procedure

Index fast full scan

Index range scan

Oracle’s Autotrace utility

technical aspects

C

Center of Expertise (CoE)

Check the Flash Recovery Area (FRA)

COALESCE function

column_name clause

Concatenated index

skip-scan feature

analyzing Oracle wait events

enqueue wait event

exclusive locks

long-term strategy

short-term strategy

transaction locks

buffer busy waits

undo header and undo block

identifying locked object

indentifying SQL statements

latch contention

cache buffer chains

cache buffers LRU chain

CURSOR_SHARING parameter

shared pool and library latches

log file sync wait events

Oracle waiting interface

read by other session wait event

recently locked sessions, database

recent wait events, database

reducing direct path read wait events

simultaneous requests, SGA

time spent waiting, locking

TM lock contention

transaction locks

understanding response time

detailed information, wait event

processing time

time model statistics

understanding wait class events

application wait class

user I/O wait class

V$SESSION_WAIT view

concurrency issues

CONTROL_MANAGEMENT_PACK_ACCESS parameter

Correlated subqueries

Cost-based optimizer (CBO)

CPU, bottlenecks

CPU-consuming processes

cpuspeedNW system statistics

CREATE DATABASE script

automatic UNDO tablespace

default tablespace, USERS

default temporary tablespace, TEMP

online redo logs

passwords, DBA-related users

SYSTEM tablespace

cursor_sharing parameter

D

Database Configuration Assistant (DBCA)

Database network connectivity troubleshooting

DBA/ALL/USER_EXTENTS

DBA/FS/ALL/FS/USER_CONSTRAINTS view

advanced mode option

database creation

in Linux/Unix environments

db file scattered read wait event

db file sequential read wait event

DBMS_AUTO_TASK_ADMIN package

DBMS_MONITOR package

DBMS_WORKLOAD_REPOSITORY package

DBMS_WORKLOAD_REPOSITORY PL/SQL package

Degree of parallelism (DOP)

Direct path loading

maximizing speed of insert statements

Disk space issues

du, sort and head commands

find, Is, sort and head commands

shell script, monitoring

usedSpc variable

E

Estimate_percent parameter

Execution plan, SQL

AUTOTRACE feature

DBMS_SQLPA package

DBMS_XPLAN.DISPLAY function

cost information

DISPLAY function

execution statistics

V$SQL_MONITOR view

identifying resource-consuming

DBA_HIST_SQL_PLAN view

DBA_HIST_SQLSTAT view

DBA_HIST_SQLTEXT view

DBMS_SQLTUNE.REPORT_SQL_MONITOR function

long running query

V$SQL_PLAN_MONITOR view

formulating steps

initialization parameters

out-of-the-box settings

skep-shaped diagram

SQL profiles (see SQL profiles)

stored outlines

DEPT and EMP tables

query processing, steps

resource-consuming SQL statements

V$SQLSTATS to V$SQL, join

V$SQLSTATS view

SQL performance Analyzer

creating analysis task

executing analysis task

REPORT_ANALYSIS_TASK function

reporting analysis task function

F

Foreign key columns

LISTAGG analytical function

many-to-many intersection table

Function-based index

G

GRANULARITY parameter

H

Hybrid columnar compression

I

INCREMENTAL preference

bitmap index, star schema

data warehouse environments

filtering data, fact table

B-tree (see also B-tree indexes)

COMPRESS N clause

concatenated index

skip-scan feature

creating aspects

deciding which columns to index

foreign key columns

index creation and maintenance guidelines

index creation standards

index with NOSEGMENT clause

primary key constraint

unique key constraint

foreign key columns

B-tree index creation

LISTAGG analytical function

many-to-many intersection table

freeing up unused space

rebuilding the index

shrinking the index

function-based index

Global partitioned

Indexed virtual column

index-organized table

DBA/ALL/USER_TABLES

INCLUDING clause

ORGANIZATION INDEX

invisible indexes

OPTIMIZER_USE_INVISIBLE_INDEXES

local partitioned

maximizing index creation speed

advantage of NOLOGGING

disadvantage of NOLOGGING

increasing degree of parallelism

turning off redo generation

monitoring usage

ALTER INDEX . . . MONITORING USAGE

Oracle Index types

primary key constraint

ALTER TABLE . . . AND CONSTRAINT statement

constraint inline creation

out of line creation

REBUILD NOREVERSE clause

REBUILD REVERSE clause

reverse-key (see also B-tree indexes)

adding constraint

CREATE TABLE statement

vs. function-based indexes

improving performance

Index creation standards

Index Fast Full Scan (IndexFFS)

Index-organized tables (IOTs)

filtering criteria

JOIN . . . ON clause

JOIN . . . USING clause

NATURAL JOIN clause

traditional Oracle SQL

Input/output (I/O) bottlenecks

data dictionary

iostat column descriptions

iostat output examination

ioseektim system statistics

iotfrspeed system statistics

J

full outer join

inner join (see Inner join)

left outer join

outer join (see Outer join)

right outer join

K

KEEP buffer pool

L

Locally managed tablespaces

Low cardinality indexes

M

Memory-consuming processes

METHOD_OPT parameter

MMON background process

DBMS_SQLTUNE.REPORT_SQL_MONITOR function

V$SQL_PLAN_MONITOR view

Multicolumn indexes

Multiple-column subqueries

Multiple-row subqueries

ANY and SOME operators

N

Netstat command

Network-intensive processes detection

diagnosing sources

netstat command

NO_INVALIDATE parameter

maximizing speed of insert statements

Numeric data type

O

Operating system performance analysis

CPU and memory (ps)

database network connectivity troubleshooting

network-intensive processes detection

virtual memory statistics (vmstat)

CPU and memory-consuming processes

database performance problem isolation

decision-making process

disk space issues

du, sort and head commands

find, Is, sort and head commands

shell script, monitoring

usedSpc variable

resource-intensive process

and database process mapping

top server-resource consuming processes

column descriptions of top Output

commands to change top output

Task Manager utility

troubleshooting poor performance

Operating System (OS) watcher

adaptive cursor sharing

BIND_AWARE column

BIND_SHAREABLE column

INDEX FAST FULL SCAN

INDEX RANGE SCAN

IS_BIND_AWARE column

Oracle Database 11g

automatic statistics gathering

dbms_auto_task_admin.disable procedure

dbms_auto_task_admin.enable procedure

DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC procedure

DBMS_STATS.GATHER_DATABASE_STATS procedure

enable procedure

GATHER_DATABASE_STATS procedure

bind peeking behavior

bulk loaded tables

concurrent statistics collection

DBMS_STATS.GATHER_TABLES_STATS procedure

job_queue_processes parameter

monitoring concurrent stats collection jobs

multi-processor environment

parallel execution strategy

SET_GLOBAL_PREFS procedure

exporting statistics

locking statistics

missing statistics

non-use of bind variables

partitioned tables

query optimizer features

related columns

restoring previous versions

statistics on expressions

system statistics

interval parameter

I/O and CPU characteristics

IOTFRSPEED, IOSEEKTIM, and CPUSPEEDNW

mbrc and mreadtim statistics

noworkload statistics

Oracle 11g database

workload statistics

types of statistics

add_sys parameter

AUTOSTATS_TARGET parameter

CASCADE parameter

DEGREE parameter

ESTIMATE_PERCENT parameter

GRANULARITY parameter

INCREMENTAL preference

METHOD_OPT parameter

NO_INVALIDATE parameter

PUBLISH parameter

SET_DATABASE_PREFS

SET_GLOBAL_PREFS

SET_SCHEMA_PREFS

SET_TABLE_PREFS

STALE_PERCENT preference

volatile tables

optimizer_dynamic_sampling initialization parameter

optimizer_features_enable parameter

optimizer_index_cost_adj parameter

optimizer_use_pending_statistics parameter

Oracle Database 11g

Oracle Database 11g R2

Oracle Diagnostics Pack

Oracle Enterprise Manager

$ORACLE_HOME/rdbms/admin directory

Oracle listener

Oracle Process ID (ORAPID)

Oracle’s Autotrace utility

Oracle’s basic compression

hybrid columnar compression

direct path loading

ALTER statemment

COMPRESS clause

CREATE TABLE . . . AS SELECT statement

DBA/ALL/USER_TABLES view

MOVE COMPRES clause

ALTER TABLE statement

COMPRESS FOR OLTP clause

I/O performance

OLTP compression

Oracle Trace Analyzer

installation and running steps

non-default initialization parameters

non-recursive tome and totals

self-time, totals, waits, binds and row source plan

tables and indexes

tacreate.sql script

/trca/install/trcreate.sql script

trca_instructions HTML document

OS Kill command

FULL OUTER JOIN

left outer join

right outer join

traditional Oracle SQL

P

ALTER statement

creating indexes

creating tables

degree of parallelism

ALTER SESSION ENABLE PARALLEL DML

ALTER SESSION FORCE PARALLEL DML

DBMS_PARALLEL_EXECUTE PL/SQL package

INSERT statement

UPDATE, MERGE and DELETE statements

existing object

execution steps

finding bottlenecks

monitoring operations

moving partitions

object creation

rebuilding indexes

sessions, detailed information

splitting partitions

system components

understanding factors

ALTER_SQL_PLAN_BASELINE function

ATTRIBUTE_NAME and ATTRIBUTE_VALUE

DBMS_SPM package

DISPLAY_SQL_PLAN_BASELINE function

EVOLVE_SQL_PLAN_BASELINE function

OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES

PACK STGTAB BASELINE function

for SQL statements

DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE function

LOAD_PLANS CURSOR CACHE function

resource-intensive queries

tuning set object

Primary key constraint

ALTER TABLE . . . AND CONSTRAINT statement

constraint inline creation

out of line creation

Primary key index

Program global area (PGA)

PUBLISH parameter

Q

Query coordinator (QC) controls

caching query results

configuration hierarchy

initialization parameters

NO_RESULT_CACHE hint

result_cache hint

server-side result cache

V$RESULT_CACHE_OBJECTS

changing the access path

analyzing optimizer’s cost

changing the join method

necessary factors

querying multiple tables

sort merge joins

changing the join order

changing the optimizer version

data warehouse performance

directing a distributed query

resource-intensive

direct-path insert technique

APPEND hint works

APPEND_VALUES hint

direct-path load operation

INSERT operations

multiple application process

NO_GATHER_OPTIMIZER_STATISTICS hint

DML performance

enabling query rewrite

materialized view

fast response and overall optimization

GATHER_PLAN_STATISTICS hint

DISPLAY_CURSOR procedure

gather extended statistics

hint categories

star information/fact hint

configuration elements

non-mergeable view

rules, mergeable view

R

Real Application Cluster (RAC) environment

Recovery writer process (RVWR)

RECYCLE buffer pool

Resource-intensive process

and database process mapping

using OS Kill command

using SQL kill session

working principle

Reverse-key index

S

Segment Advisor Advice

display table information

advice and recommendations

ASA_RECOMMENDATIONS

DBMS_SPACE package

Enterprise Manager

retrieving tools

e-mailing advice automatically

freeing unused space

ALTER TABLE . . . SHRINK SPACE statement

enable row movement

generating advice manually

DBMS_ADVISOR.CREATE_TASK procedure, object types

DBMS_ADVISOR package

DBMS_ADVISOR.SET_TASK_PARAMETER procedure

DBMS_SPACE package

rebuild spanned rows

SELECT_BASELINE_DETAILS function

SELECT statement

subqueries (see Subqueries)

Single-row subqueries

Skip-scan feature

avoiding full table scan

avoiding NOT clause

comparison operators

Oracle optimizer

comparing tables

INTERSECT set operator

MINUS set operator

controlling transaction sizes

execution plan (see also Execution plan, SQL)

BILLING_INFO view

PRODUCT_INFO view

SERVICE_INFO view

joining tables, outer join (see Outer join)

in SELECT clause

in WHERE clause

partial column values

TO_CHAR function

plan baselines (see Plan baselines)

re-using SQL statements

execute immediate statement, bind variables

SAVEPOINT command

SELECT statement (see SELECT statement)

UNION/UNION ALL

SQL kill session

SQL performance Analyzer

creating analysis task

executing analysis task

REPORT_ANALYSIS_TASK function

reporting analysis task function

SQL plan management

plan baselines (see Plan baselines)

automatic acceptance

vs. database profiles

manage features

transporting database

copy the staging table

DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF procedure

DBMS_SQLTUNE.PACK STGTAB SQLPROF procedure

DBMS_SQLTUNE.PACK_STGTAB_SQLPROF procedure

DBMS_SQLTUNE.UNPACK STGTAB SQLPROF procedure

Tuning Advisor (see SQL Tuning Advisor)

SQL Test Case Builder (TCB)

<ADR Home>/trace subdirectory

archive logs, Data Guard environment

automatic Oracle error traces

background process

correct session

$DIAG_INFO view

diagnostic_dest initialization parameter

event 10046 trace

instance/database

max_dump_file_size parameter

multiple sessions

multiple trace files

optimizer’s execution path

access path analysis for SALES

Oracle event 10053

types of information

Oracle Database 11g

Oracle listener

alter system set events command

Oracle Database 11g

query coordinator

show tracefile-t command

trcsess utility

running session

specific SQL statement

timed_statistics parameter

trace dump file

execution statistics

formatting with TKPROF

Oracle Trace Analyzer (see Oracle Trace Analyzer)

row source operations

SQL Tuning Advisor

DBMS_ADDM Package

Enterprise Manager

SQL*plus script

types of recommendations

create and accept SQL profile

from Enterprise Manager

executing the task

optimizer tuning modes

recommendations

from SQL Developer

steps to run manually

SQL tuning set (STS)

in AWR, high-resource consuming statements

creating object

deleting statements

displaying contents

in Memory, resource-consuming statements

transporting to another database

copy the staging table

create staging table

populate staging table

unpack the staging table

STALE_PERCENT preference

Statement queuing

correlated subqueries

multiple-column subqueries

multiple-row subqueries

single-row subqueries

System global area (SGA)

Systemstate dump

System statistics

interval parameter

I/O and CPU characteristics

IOTFRSPEED, IOSEEKTIM, and CPUSPEEDNW

mbrc and mreadtim statistics

noworkload statistics

Oracle 11g database

workload statistics

T

Table performance

building database

CREATE DATABASE script

default permanent tablespaces

default temporary tablespaces

locally managed tablespaces

compressing data

direct path loading

avoiding extent allocation delays

character data types

DATE/TIMESTAMP data types

numeric data types

performance and sustainability issues

ROWID data type

scalability and maintainability

SEGMENT CREATION DEFERRED

SEGMENT CREATION IMMEDIATE

creating tablespaces

AUTOEXTEND ON clause

storage attributes, tables and indexes

maximizing data loading speeds

direct path loading

Oracle database

removing table data

COMMIT/ROLLBACK

DELETE vs. TRUNCATE statement

high-water mark

TRUNCATE statement

row chaining and migration

CHAINED_ROWS tables

COMPUTE STATISTICS statement

querying V$SYSSTAT

row length calculation

table re-organizing

ROWID pseudo-column

Segment Advisor Advice (see Segment Advisor Advice)

materialized view

unused space detection

Task Manager utility

TIMESTAMP data type

TM lock contention wait events

tracefile_identifier parameter

Troubleshooting Database

awrrpt.sql script

Compare Periods report

DBMS_WORKLOAD_REPOSITORY package

instance efficiency percentages

load profile section

session information

Time Model Statistics

Top 5 Foreground Events

oradebug hanganalyze command

resolving steps

systemstate dump

true database hang

invoke ADRCI (see Automatic Diagnostic Repository Command Interpreter (ADRCI))

optimal undo retention parameter

V$UNDOSTAT view

ORA-01555 error

guaranteed undo retain feature

snapshot too old error

Packaging Incidents, Oracle Support

resolving open cursor errors

running health check

creation process information

DBMS_SQLDIAG package

EXPORT_SQL_TESTCASE procedure

temporary tablespace

identifying the user

monitoring the usage

one-pass/multi-pass operations

unable to extend TEMP segment error

UNDO_TABLESPACE initialization parameter

U

adding constraint

CREATE TABLE statement

UTLBSTAT/UTLESTAT

V

V$SESSION_LONGOPS view

Very large databases (VLDBs)

vs. function-based indexes

improving performance

Virtual memory statistics (vmstat)

W, X, Y, Z

analyzing Oracle wait events

application class

buffer busy waits

undo header and undo block

db file scattered read wait event

db file sequential read wait event

examining session waits

indentifying SQL statements

log file sync waits

read by other session

reducing direct path read

RVWR wait events

V$ACTIVE_SESSION_HISTORY view

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.