SQL Tuning01-Introduction to SQL TuningZhaoyang Wang
?
This document provides an introduction to SQL tuning. It discusses common causes of poor SQL performance such as stale statistics, missing indexes, and suboptimal execution plans. It then describes Oracle tools that can be used for SQL tuning such as the Automatic Database Diagnostic Monitor and SQL Tuning Advisor. Various SQL performance metrics are covered like wait time and how it relates to CPU and I/O times. Common SQL performance problems are also listed like parsing issues, full table scans, and redo log configuration errors. Finally, techniques for SQL tuning are discussed such as query rewrites, materialized views, and cursor sharing.
SQL Tuning02-Intorduction to the CBO OptimizerZhaoyang Wang
?
This document provides an introduction to the Cost-Based Oracle Optimizer (CBO). It describes the main components of the CBO including the estimator, plan generator, and OPTIMIZER_MODE. It also discusses important CBO concepts like selectivity, cardinality, cost and how they are estimated. The document provides examples of how to view CBO statistics and use 10053 tracing to analyze plans.
SQL Tuning04-Interpreting Execution PlansZhaoyang Wang
?
This document discusses various methods for interpreting SQL execution plans in Oracle databases, including using the PLAN_TABLE, views like V$SQL_PLAN, and tools like DBMS_XPLAN and AUTOTRACE. It also covers gathering plan statistics and interpretation from sources like the Automatic Workload Repository (AWR).
The document provides sample questions and answers from Oracle Database certification exam 1Z0-052. It includes 27 multiple choice questions covering topics like database backups, undo tablespaces, Data Pump utilities, and database initialization parameters. For each question, the stem presents a scenario and possible answer choices, and the answer identifies the correct choices. The questions test knowledge of Oracle database concepts, features, and troubleshooting techniques.
SQL Tuning01-Introduction to SQL TuningZhaoyang Wang
?
This document provides an introduction to SQL tuning. It discusses common causes of poor SQL performance such as stale statistics, missing indexes, and suboptimal execution plans. It then describes Oracle tools that can be used for SQL tuning such as the Automatic Database Diagnostic Monitor and SQL Tuning Advisor. Various SQL performance metrics are covered like wait time and how it relates to CPU and I/O times. Common SQL performance problems are also listed like parsing issues, full table scans, and redo log configuration errors. Finally, techniques for SQL tuning are discussed such as query rewrites, materialized views, and cursor sharing.
SQL Tuning02-Intorduction to the CBO OptimizerZhaoyang Wang
?
This document provides an introduction to the Cost-Based Oracle Optimizer (CBO). It describes the main components of the CBO including the estimator, plan generator, and OPTIMIZER_MODE. It also discusses important CBO concepts like selectivity, cardinality, cost and how they are estimated. The document provides examples of how to view CBO statistics and use 10053 tracing to analyze plans.
SQL Tuning04-Interpreting Execution PlansZhaoyang Wang
?
This document discusses various methods for interpreting SQL execution plans in Oracle databases, including using the PLAN_TABLE, views like V$SQL_PLAN, and tools like DBMS_XPLAN and AUTOTRACE. It also covers gathering plan statistics and interpretation from sources like the Automatic Workload Repository (AWR).
The document provides sample questions and answers from Oracle Database certification exam 1Z0-052. It includes 27 multiple choice questions covering topics like database backups, undo tablespaces, Data Pump utilities, and database initialization parameters. For each question, the stem presents a scenario and possible answer choices, and the answer identifies the correct choices. The questions test knowledge of Oracle database concepts, features, and troubleshooting techniques.
Oracle Berkeley DB is Oracle's open source, embeddable database designed for devices, appliances and applications. It provides low latency and high throughput storage with reliability and scalability. Berkeley DB 11gR2 offers the performance and features of a key-value store with the transactional capabilities of SQLite in a small footprint package requiring no administration. Customers across various industries have adopted Berkeley DB for its benefits over flat files such as better performance, reliability and reduced development costs.
This document provides an overview of PL/SQL subprograms, including anonymous blocks, stored PL/SQL units like procedures and functions, and PL/SQL packages. It defines each component and provides examples. Anonymous blocks allow executing PL/SQL code without a name. Stored procedures and functions can be invoked by many users. Packages organize related objects and allow overloading subprograms. The document demonstrates package specifications that declare objects and package bodies that define objects and private components.
Secure Technical Implementation Guide for databases by Martin ObstCarsten Muetzlitz
?
Secure Technical Implementation Guide for databases by Martin Obst
Auditing security of Oracle DB (Karel Miko)DCIT, a.s.
?
The document discusses auditing security of Oracle databases. It divides the audit into four technical phases:
1) Auditing the operating system level, including checking permissions on the Oracle home directory and verifying the OS account used for Oracle has appropriate privileges.
2) Auditing the Oracle RDBMS level, including validating the Oracle version and installed patches.
3) Auditing Oracle database instances, including verifying database options and privileges granted to users and roles.
4) Auditing related processes, such as the Oracle listener and associated configuration files.
Oracle security 08-oracle network securityZhaoyang Wang
?
The document discusses securing Oracle Network services. It provides checklists and procedures for securing clients, the network, and the listener. It recommends configuring clients and browsers with authentication and encryption. It also recommends restricting network access through firewalls and IP address validation. For the listener, it suggests restricting privileges, password protecting administration, and monitoring logs to analyze activity and potential attacks. The goal is to describe how to securely administer the network and listener to restrict access and analyze logs for security.
Oracle security 02-administering user securityZhaoyang Wang
?
This document discusses administering user security in an Oracle database. It covers how to create and manage database user accounts, including authenticating users and assigning privileges. It also covers creating and managing roles to simplify privilege management, and creating profiles to implement password security and control resource usage. Profiles allow enforcing standards for password complexity, aging, locking accounts, and limiting resource consumption. The document stresses applying the principle of least privilege and separating administrative duties for security.
MySQL 5.7 includes several new features that improve performance, replication, and high availability. Key features include performance improvements from the performance schema and optimizer enhancements, replication improvements like multi-source replication and transaction-based parallel replication, and InnoDB improvements such as online operations and general tablespaces.
This document provides an overview of MySQL full-text search capabilities. It discusses the three types of full-text searches supported: natural language, boolean, and query expansion searches. It also covers stopwords, relevance ranking calculations, and techniques for fine-tuning full-text search performance such as configuring minimum/maximum word lengths and optimizing indexes. The document concludes with some restrictions and best practices for MySQL full-text search.
The document summarizes the key data structures used to organize data in InnoDB:
- InnoDB stores data in tablespaces which consist of data files. A tablespace header tracks free/used extents within these files.
- Data files contain fixed-size pages which are organized into extents of 1MB each. Page headers identify page types like interior, leaf, etc.
- File segments allocate ranges of pages to index trees. The root node of each index references two segment headers to allocate leaf/non-leaf pages separately.
MYSQLCLONE is a free and simple tool used to clone MySQL databases from one server to another. It can transfer the entire database including data, schemas, stored procedures, functions and events. The tool connects to the source and destination databases using connection parameters and then transfers the database objects and data in either LOAD or INSERT mode. Quick usage examples are provided to demonstrate transferring the full database, schema objects only, and row data in INSERT mode.