Oracle: Unterschied zwischen den Versionen

Aus robopagex.com
Zur Navigation springen Zur Suche springen
 
(242 dazwischenliegende Versionen desselben Benutzers werden nicht angezeigt)
Zeile 1: Zeile 1:
 +
== ORACLE DATABASE HACKBUGZ ==
 +
 +
=== CREATE USER ===
 +
'''$ sqlplus'''
 +
or
 +
'''$ connect /as sysdba'''
 +
'''sp> create user [NAME]identified by [PASSWD];'''
 +
'''sp> grant dba to admin;'''
 +
 +
=== GRANT ALL DICTIONARY TO USER ===
 +
'''sp> grant select any dictionary to [NAME];'''
 +
 +
=== CREATE GLOBAL TEMPORARY TABLE ===
 +
CREATE GLOBAL TEMPORARY TABLE my_temp_table (
 +
  id          NUMBER,
 +
  description  VARCHAR2(20)
 +
)
 +
ON COMMIT PRESERVE ROWS;
 +
 +
-- Insert and commit, then check contents of GTT.
 +
INSERT INTO my_temp_table VALUES (1, 'ONE');
 +
COMMIT;
 +
 +
SELECT COUNT(*) FROM my_temp_table;
 +
 
=== PL/SQL ===
 
=== PL/SQL ===
 
  PL/SQL stands for “Procedural Language extensions to the Structured Query Language”.  
 
  PL/SQL stands for “Procedural Language extensions to the Structured Query Language”.  
 
  SQL is a popular language for both querying and updating data in the relational database management systems (RDBMS).  
 
  SQL is a popular language for both querying and updating data in the relational database management systems (RDBMS).  
−
  PL/SQL adds many procedural constructs to SQL language to overcome some limitations of SQL. Besides, PL/SQL provides a more comprehensive programming language solution for building mission-critical applications on Oracle Databases.
+
  PL/SQL adds many procedural constructs to SQL language to overcome some limitations of SQL.  
 +
Besides, PL/SQL provides a more comprehensive programming language solution for building mission-critical applications on Oracle Databases.
  
 
[https://www.oracletutorial.com/plsql-tutorial/what-is-plsql/ What is PL/SQL]
 
[https://www.oracletutorial.com/plsql-tutorial/what-is-plsql/ What is PL/SQL]
 +
 +
'''PL/SQL is an embedded language. PL/SQL only can execute in an Oracle Database.'''
 +
Platform Independence
 +
 +
[https://www.oracle.com/virtualization/technologies/vm/downloads/virtualbox-downloads.html Oracle VM VirtualBox]
 +
 +
[https://www.oracle.com/database/technologies/databaseappdev-vm.html Database Virtual Box Appliance / Virtual Machine]
 +
 +
- Oracle Forms
 +
 +
'''PL/SQL ENGINE'''
 +
Procedural Statment Executor
 +
 +
Architecture
 +
- Cooperates with SQL Engine
 +
- Enables Subprograms
 +
- Dynamic Queries
 +
- Case Insensitivity
 +
- Oracle Style
 +
- Optimizer
 +
- Enables Object-Orinted Programming(OO Programmierung mit abstrakten Datentypen)
 +
- Web Development
 +
  - PL/SQL gateway
 +
  - PL/SQL Web Toolkit
 +
 +
 +
'''SQL ENGINE'''
 +
SQL Statement Executor
 +
3 Schritte:
 +
Parsing
 +
Fetching
 +
Executing
 +
Bei der Ausführung optimiert der SQL-Optimierer
 +
 +
=== Database-Management-Tools ===
 +
- '''TOAD'''(Tools for Oracle application Development)
 +
- '''Oracle SQL Developer'''
 +
- '''SQLplus'''(command line tool)
 +
 +
=== Oracle SQL Developer ===
 +
'''Oracle SQL DEVELOPER'''
 +
'''ANSICHT/DATA MODELER/BROWSER'''
 +
 +
Fix network adapter connection error:
 +
Check File: '''listener.ora'''
 +
or
 +
Shell:
 +
'''$ lsnrctl status'''
 +
'''$ lsnrctl start'''
 +
'''$ services.msc'''
 +
 +
=== Schema vs Tablespace ===
 +
 +
A schema is a namespace - a logical thing. It is used to organize the names of database objects. It has nothing to do with the way the data is stored.
 +
 +
A tablespace is a physical thing. It's a container for data and has nothing to do with the logical organization of the database objects.
 +
[https://stackoverflow.com/questions/35120219/a-database-schema-vs-a-database-tablespace A database schema vs a database tablespace?]
 +
 +
=== SQL | DDL, DQL, DML, DCL and TCL Commands ===
 +
DDL – Data Definition Language
 +
DQl – Data Query Language
 +
DML – Data Manipulation Language
 +
DCL – Data Control Language
 +
 +
'''DDL (Data Definition Language):'''
 +
- CREATE: This command is used to create the database or its objects (like table, index, function, views, store procedure, and triggers).
 +
    - Table begin with a letter
 +
    - 1-30 character long
 +
    - A-Z,0-9,-,$,#
 +
    - no duplicate names
 +
    - no reserver words(select,from,etc.)
 +
- DROP: This command is used to delete objects from the database.
 +
- ALTER: This is used to alter the structure of the database.
 +
- TRUNCATE: This is used to remove all records from a table, including all spaces allocated for the records are removed.
 +
- COMMENT: This is used to add comments to the data dictionary.
 +
- RENAME: This is used to rename an object existing in the database.
 +
 +
'''DQL (Data Query Language):'''
 +
- SELECT: It is used to retrieve data from the database.
 +
 +
'''DML(Data Manipulation Language): '''
 +
- INSERT : It is used to insert data into a table.
 +
- UPDATE: It is used to update existing data within a table.
 +
- DELETE : It is used to delete records from a database table.
 +
- LOCK: Table control concurrency.
 +
- CALL: Call a PL/SQL or JAVA subprogram.
 +
- EXPLAIN PLAN: It describes the access path to data.
 +
 +
'''SELECT'''
 +
'''FROM'''
 +
'''JOIN'''
 +
'''WHERE'''
 +
'''GROUP BY'''
 +
'''HAVING'''
 +
'''ORDER BY'''
 +
 +
'''DCL (Data Control Language): '''
 +
- GRANT: This command gives users access privileges to the database.
 +
- REVOKE: This command withdraws the user’s access privileges given by using the GRANT command.
 +
 +
'''TCL – Transaction Control Language '''
 +
- COMMIT: Commits a Transaction.
 +
- ROLLBACK: Rollbacks a transaction in case of any error occurs.
 +
- SAVEPOINT:Sets a savepoint within a transaction.
 +
- SET TRANSACTION: Specify characteristics for the transaction.
 +
 +
==== Character functions ====
 +
lower      | converts
 +
upper      | converts
 +
length      | returns
 +
substring  | select characters from string to display
 +
 +
concat    | join 2 strings with ||
 +
initcap  | first letter to uppercase
 +
trim      | trims text from specified x
 +
ltrim    | trims text from left
 +
rtrim    | trims text from right
 +
replace  | replace and remove strings
 +
translate | replace set of characters
 +
 +
==== convert functions ====
 +
to_char  = numeric and date to string
 +
to_date  = numeric & string to date
 +
to_number = value to numeric
 +
 +
==== date functions ====
 +
add_months    | add and return
 +
months_between | return between x and y
 +
next_day      | next day of day
 +
last_day      | days left in month from date
 +
 +
==== numeric functions ====
 +
round    |
 +
trunc    |
 +
floor    |
 +
ceil      |
 +
mod      |
 +
 +
=== Oracle data constrains ===
 +
NULL        | not null
 +
UNIQUE      | each row unique
 +
PRIMARY KEY  | references pk in other table
 +
FOREIGN KEY  | references fk in other table
 +
CHECK        | meets condition
 +
DEFAULT      |
 +
 +
=== ALIAS ===
 +
shorter query
 +
more than one table
 +
table or column name too long or not reader friendly
 +
two or more columns combined together
 +
when functions used in query
 +
 +
=== Limiting and sorting data ===
 +
retrieve a only a part of a big data and sort the result
 +
use select clause and operators
 +
 +
=== sub query ===
 +
 +
=== from clause ===
 +
 +
=== where ===
 +
 +
=== order by ===
 +
filter or sort data
 +
required by some analytic functions
 +
'''function () over ( order by column_name)'''
 +
 +
=== group by having ===
 +
 +
=== like & wildcards ===
 +
 +
=== JOIN ===
 +
equi join
 +
non equi join
 +
catesian join
 +
outer join
 +
self join
 +
 +
join
 +
ON
 +
USING(mergeinto a single col)
 +
 +
=== aggregate function ===
 +
avg
 +
count
 +
max
 +
min
 +
sum
 +
 +
=== set_operator ===
 +
'''union all'''
 +
  returns result from both queries after eliminating doubles
 +
'''intersect set'''
 +
  rows that are common to both queries
 +
'''minnus set'''
 +
  rows in the first query that are not in the second query
 +
 +
'''set operators summary'''
 +
  use order by only at the end of statement
 +
 +
=== analytic functions ===
 +
 +
=== SQL logical operators ===
 +
- and
 +
- or
 +
- in
 +
- between
 +
- like
 +
- not
 +
- all
 +
- any
 +
- is null
 +
 +
=== VIEW and DUAL TABLE ===
 +
'''VIEW'''
 +
SQL SELECT QUERY, stored in db, treated like a table but no data stored in.
 +
 +
'''DUAL TABLE'''
 +
A special dummy one-row, one-column table, present by default in all oracle db installations
 +
varchar2(1), column value is x
 +
 +
=== Describe ===
 +
Description relating to the db obj like tables and view
 +
 +
=== Synonyms ===
 +
alias for an obj in the db
 +
can be used to hide the identity and location
 +
 +
'''PRIVATE SYNONYM'''
 +
 +
'''PUBLIC SYNONYM'''
 +
 +
=== SQL INDEXES ===
 +
Used to speed up the performance of sql-querys or statements
 +
Pointer to data
 +
 +
==== Type of Indexes ====
 +
- B-Tree
 +
- Bitmap
 +
  Can be simple or composite
 +
- Function-Based
 +
  If we use a function on a column which is indexed, then such an index will not be used
 +
  Create an index on functionor expression
 +
- Index - Organized Tables
 +
  Für kleine Datenmengen
 +
  B-Tree-Format
 +
- Cluster Index
 +
  group of multi Tables, stores data in the same block
 +
- Composite Index
 +
 +
B-Tree
 +
- Branch Block
 +
- Leaf Block
 +
 +
Fragementation of Index
 +
Rebuild the Indexes
 +
 +
Create index [NAME] on [TABLE](COL)
 +
Create index [NAME] on [TABLE](COL1,COL2)
 +
 +
=== ERD - Entity Relationship Diagram ===
 +
 +
Cardinality
 +
 +
Crow's foot notation
 +
 +
=== Pluggable Database ===
 +
Pluggable Database = Multitenant Architecture
 +
 +
sys-user rolle SYSDBA or SYSOPER
 +
 +
Methoden wie "Bulk Insert"
 +
 +
[https://docs.oracle.com/database/121/TDDDG/tdddg_dml.htm#TDDDG99941 About DML Statements and Transactions]
 +
 +
[https://www.tutorialspoint.com/plsql/plsql_arrays.htm# PL/SQL - Arrays]
 +
 +
=== BLOCKS ===
 +
 +
DECLARE    | optional
 +
BEGIN      | mandatory
 +
EXCEPTION  | optional
 +
END;        | mandatory
 +
 +
3 TYPES OF BLOCKS
 +
- Anonymous Blocks
 +
- Procedures
 +
- Functions
 +
 +
=== PGA | PROGRAM GLOBAL AREA ===
 +
 +
=== PL/SQL Outputs ===
 +
Not an output language
 +
'''SET SERVEROUTPUT ON'''
 +
'''SET SERVEROUTPUT ON SIZE UNLIMITED'''
 +
'''SET SERVEROUTPUT ON SIZE 5000'''
 +
'''DBMS_OUTPUT''' | Package
 +
[https://docs.oracle.com/en/database/oracle/oracle-database/21/arpls/DBMS_OUTPUT.html#GUID-3C2FDFF2-4A8A-4555-988C-7AA3D0FF7971 DBMS_OUTPUT ]
 +
[https://docs.oracle.com/database/121/ARPLS/d_output.htm#ARPLS036 106 DBMS_OUTPUT]
 +
 +
Ein Paket ist ein PL/SQL Objekt das Prozeduren oder Funktionen enthält um Operationen auszuführen.
 +
 +
'''SET SERVEROUTPUT ON; '''
 +
'''begin '''
 +
'''dbms_output.put_line ('Hello'); '''
 +
'''end; '''
 +
 +
''' SET SERVEROUTPUT ON; '''
 +
''' begin '''
 +
''' dbms_output.put_line ('Anonymous Block'); '''
 +
'''    begin '''
 +
'''        dbms_output.put_line ('Nested Block'); '''
 +
'''    end; '''
 +
''' end; '''
 +
 +
=== PL/SQL VARIABLE ===
 +
TYPES:
 +
 +
'''SCALAR'''
 +
  CHAR (max_length) | 32767 bytes
 +
  VARCHAR2(max_length)
 +
NUMBER[precision,sale]
 +
BINARY_INTEGER = PLSINTEGER | (LOWER MEMORY, FASTER FOR ARITHMETIC)
 +
BINARY_FLOAT
 +
BINARY_DOUBLE
 +
BOOLEAN | TRUE, FALSE, NULL (DEFAULT NULL)
 +
DATE
 +
TIMESTAMP
 +
TIMESTAMP(p) WITH TIME ZONE
 +
TIMESTAMP(p) WITH LOCAL TIME ZONE
 +
INTERVAL(p) YEAR TO MONTH
 +
INTERVAL(p) DAY TO SECOND(p)
 +
 +
- '''LARGE OBJECTS'''
 +
- '''REFERENCE'''
 +
- '''COMPOSITE'''
 +
 +
Variable naming rules
 +
start with letter
 +
can contain special chars
 +
max 30 chars
 +
no oracle reserved names
 +
 +
name conventions:
 +
v_variable
 +
cur_cursor
 +
e_exception
 +
p_procedure
 +
p_function
 +
b_bind
 +
 +
'''variable_name [CONSTANT] datatype [NOT NULL] [:= | DEFAULT initial_value] '''
 +
 +
sales number(10, 2);
 +
pi CONSTANT double precision := 3.1415;
 +
name varchar2(25);
 +
address varchar2(100);
 +
 +
DECLARE
 +
    V_TEXT_1 VARCHAR2(50) := 'STRING';
 +
    V_TEXT_2 VARCHAR2(50) NOT NULL DEFAULT 'DEFAULT';
 +
    V_TEXT_3 VARCHAR2(50) := 'STRING';   
 +
    V_TEXT_4 VARCHAR2(50) := 'STRING';
 +
   
 +
    V_NUMBER_1 NUMBER NOT NULL := 50;
 +
    V_NUMBER_2 NUMBER NOT NULL := 100.02;
 +
    V_NUMBER_3 NUMBER(4) NOT NULL := 111.22;
 +
    V_NUMBER_4 NUMBER(5,2) NOT NULL := 111.22;
 +
    V_NUMBER_5 NUMBER(5,1) NOT NULL := 111.22;
 +
   
 +
    V_PLS_INTEGER_1 PLS_INTEGER := 111.22;
 +
    V_BINARY_INTEGER_1 BINARY_INTEGER := 111.22;
 +
    V_BINARY_FLOAT_1 PLS_INTEGER := 111.22f;
 +
       
 +
    V_DATE_1 DATE NOT NULL := SYSDATE;
 +
    V_DATE_2 DATE NOT NULL := '6 JUN-22 08:01:22';
 +
   
 +
    V_TIMESTAMP_1 TIMESTAMP NOT NULL := SYSTIMESTAMP;
 +
    V_TIMESTAMP_2 TIMESTAMP(3) WITH TIME ZONE NOT NULL := SYSTIMESTAMP;
 +
 +
=== PL/SQL DELIMETER | COMMENTS ===
 +
-- CODE --
 +
/* CODE */
 +
 +
=== PL/SQL VARIABLE ===
 +
- Declaring
 +
- Initializing
 +
- Attribute
 +
- Scope
 +
- Bind
 +
 +
==== PL/SQL BIND VARIABLE ====
 +
SQL-Bindings können helfen, die Performance von dynamischem SQL ganz erheblich zu verbessern.
 +
 +
Die meisten Datenbankanbieter verfügen heutzutage über einen Ausführungsplan-Cache (Oracle nennt ihn Cursor-Cache),
 +
in dem zuvor analysierte SQL-Anweisungen gespeichert sind und deren Ausführungsplan (und -pläne) zur Wiederverwendung zwischengespeichert werden.
 +
Dies ist der Hauptgrund, warum Bind-Variablen so wichtig sind
 +
 +
=== PL/SQL CONTROL STRUCTURES ===
 +
- IF
 +
- CASE EXPRESSIONS
 +
- LOOPS
 +
  - Basic LOOP
 +
  - WHILE LOOP
 +
  - FOR LOOP
 +
  - NESTED LOOP
 +
  - LOOP LABELING
 +
- CONTINUE STATEMENT
 +
- GOTO
 +
 +
==== IF | IF-ELSE | IF-ELSE IF ====
 +
The IF statement allows you to either execute or skip a sequence of statements, depending on a condition.
 +
The IF statement has the three forms:
 +
 +
– IF THEN
 +
– IF THEN ELSE
 +
– IF THEN ELSIF
 +
 +
'''IF condition THEN'''
 +
'''statements;'''
 +
'''END IF;'''
 +
 +
==== LOOP ====
 +
The CASE statement chooses one sequence of statements to execute out of many possible sequences.
 +
The CASE statement has two types: simple CASE statement and searched CASE statement.
 +
Both types of the CASE statements support an optional ELSE clause.
 +
 +
'''Simple CASE statement'''
 +
'''Searched CASE statement'''
 +
 +
This structure is the most basic of all the loop constructs including FOR LOOP and WHILE LOOP.
 +
This basic LOOP statement consists of a LOOP keyword, a body of executable code, and the END LOOP keywords.
 +
 +
The LOOP statement executes the statements in its body and returns control to the top of the loop.
 +
Typically, the body of the loop contains at least one EXIT or EXIT WHEN statement for terminating the loop. Otherwise, the loop becomes an infinite loop.
 +
 +
The LOOP statement can have an optional label that appears at the beginning and the end of the statement.
 +
 +
'''<<label>> LOOP '''
 +
''' statements; '''
 +
'''END LOOP loop_label; '''
 +
 +
''' LOOP '''
 +
'''  EXIT; '''
 +
''' END LOOP;  '''
 +
 +
''' LOOP '''
 +
'''  IF condition THEN '''
 +
'''  EXIT; '''
 +
'''  END IF; '''
 +
''' END LOOP; '''
 +
 +
'''EXIT WHEN statement'''
 +
The EXIT WHEN statement has the following syntax:
 +
'''EXIT WHEN condition;'''
 +
 +
'''Nested loops'''
 +
It is possible to nest a LOOP statement within another LOOP statement as shown in the following example:
 +
 +
=== USING SQL INSIDE PL/SQL ===
 +
'''SELECT''' columns|expressions
 +
'''INTO''' variables|records
 +
'''FROM''' table|tables
 +
['''WHERE''' condition];
 +
 +
Gleiche Reihenfolge|Typen der Variablen im STATEMENT
 +
 +
[https://docs.oracle.com/en/database/oracle/oracle-database/21/lnpls/static-sql.html#GUID-A2E4086F-94DC-4CC7-9E4B-30285BEC3313 PL/SQL Static SQL ]
 +
 +
==== DML OPERATIONS ====
 +
A Transaction start's with a DML and end with a commit,rollback or system error
 +
 +
- INSERT
 +
- UPDATE
 +
- DELETE
 +
- MERGE
 +
 +
'''CREATE TABLE testcopy as'''
 +
'''      select * from test;'''
 +
 +
There is no create or replace table in Oracle
 +
 +
'''DROP TABLE test;'''
 +
'''CREATE TABLE test ...;'''
 +
 +
==== SEQUENCES ====
 +
 +
Use of '''CURRVAL''' and '''NEXTVAL'''
 +
A sequence is a schema object that can generate unique sequential values.
 +
These values are often used for primary and unique keys.
 +
You can refer to sequence values in SQL statements with these pseudocolumns:
 +
 +
'''CURRVAL: Returns the current value of a sequence'''
 +
'''NEXTVAL: Increments the sequence and returns the next value'''
 +
In same statement increments only 1 time
 +
select 1.NEXTVAL 1.NEXTVAL 1.NEXTVAL ....
 +
 +
You must qualify CURRVAL and NEXTVAL with the name of the sequence:
 +
'''SELECT sequence_name.nextval|currval INTO variable|column'''
 +
'''FROM table_name|dual;'''
 +
 +
'''Variable|column :=sequence_name.nextval|currval'''
 +
 +
[https://stackoverflow.com/questions/51470/how-do-i-reset-a-sequence-in-oracle How do I reset a sequence in Oracle?]
 +
[https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1119633817597 How to reset sequences?]
 +
[https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:951269671592 Dynamic SQL to reset sequence value]
 +
 +
==== COMPOSITE DATA TYPES ====
 +
 +
===== Records =====
 +
Ein Datensatz mit Einem oder mehrere Werte
 +
Gleiche oder unterschiedliche Typen
 +
Datensatz innerhalb eines Datensatzes
 +
NULL by Default
 +
 +
1. Weg
 +
'''r_name TABLE_NAME%rowtype;'''
 +
 +
Vorteil:
 +
- Dynamische Typen-Zuweisung aus Tabelle
 +
- Durchführung einfacher DML Operationen
 +
 +
2. Weg
 +
- Einzelne Spalten Auswahl
 +
- Eigene Typen definition
 +
 +
'''type NAME is record ( NAME TYPE,'''
 +
'''                      NAME TYPE, '''
 +
'''                      []);'''
 +
 +
Alle typen ausser ref-cursors
 +
 +
Einen Datensatz
 +
 +
===== Collections =====
 +
List of the same type
 +
Vorsicht vor Speicherfehler
 +
 +
3 Types
 +
 +
- '''Nested tables'''
 +
    Keys only Numbers, begin fom 1
 +
    No Max Values
 +
 +
- '''VARRAYs'''
 +
    Begrenzung der Grösse
 +
    Genau Angabe der Grösse
 +
    Objekt für Prozedur oder Funktion
 +
    Seltene Verwendung
 +
Max 2 GB
 +
Varray-Typ in der Datenbank
 +
Can't go back
 +
Bounded
 +
Index start from 1
 +
one dimensional array
 +
null by default
 +
 +
- '''Associative Arrays'''
 +
    Key can be strings
 +
 +
==== CURSORS ====
 +
Bei normaler SQL-Abfrage, wird nur ein Teil der Ergebnismenge, Algorithemn, Ausführungspläne, etc. im KOntextbereich gespeichert.
 +
 +
Cursors are pointers to the Data, in Memory or Disc
 +
 +
''2 Types of Cursors:''
 +
 +
'''Implicit Cursor'''(Create and administration by Database)
 +
  Bei jedem SELECT wird ein Impliziter Cursor erstellt
 +
 +
'''Explicit Cursor'''
 +
We can control cursors
 +
Explicit cursors are created by the programmers
 +
Collection vs cursor
 +
You cannot go back in cursors
 +
Abruf- und Speicheralgorithmus
 +
Gleichzeitiges laden von Ergebnismengen in den Speicher
 +
Operationen sind so viel schneller
 +
 +
Anordnung vom Server anhand der Ergebnismenge und der Speichersituation.
 +
Ein Select aus dem Speicher ist schneller als aus der Datenbank
 +
 +
Usage:
 +
- Declare
 +
- Open
 +
- Fetch
 +
- Check
 +
- Close
 +
 +
loop
 +
'''exit when NAME%notfound;'''
 +
 +
Cursor Parameters
 +
 +
Cursor Attributes
 +
- %FOUND
 +
- %NOTFOUND
 +
- %ISOPEN
 +
- %ROWCOUNT
 +
 +
FOR UPDATE CLAUSE
 +
- for update(locks the selected rows)
 +
- nowait
 +
- wait(default)
 +
- for update of(locks only selected tables)
 +
 +
==== EXCEPTIONS ====
 +
- ORA CODE
 +
  ORA-01403: no data found
 +
 +
Over 10.000 nonamed exceptions
 +
Cause
 +
Action
 +
 +
A block has 3 sections
 +
- Declare
 +
- Begin-End
 +
- Exception
 +
 +
Implicit
 +
Explicit
 +
 +
- Predefined Oracle Server Error
 +
- Nonpredefined Oracle Server Error
 +
- User-defined Errors
 +
 +
Select "Step Into" on first run, with anonymous blocks
 +
Handle by 3 ways:
 +
1. Trap
 +
2. Propagate
 +
3. Trap & Propagate
 +
 +
'''SQLCODE & SQLERRM'''
 +
 +
==== FUNCTIONS & PROCEDURES ====
 +
Stored in the Database with names
 +
Compiled only once
 +
Can be called by another block or application
 +
Can return values
 +
Can take parameters
 +
 +
[https://stackoverflow.com/questions/25419629/functions-vs-procedures-in-oracle Functions vs procedures in Oracle]
 +
[https://stackoverflow.com/questions/771949/what-is-the-difference-between-function-and-procedure-in-pl-sql What is the difference between function and procedure in PL/SQL?]
 +
[https://oracle-base.com/articles/8i/function-based-indexes Function-Based Indexes]
 +
[https://docs.oracle.com/cd/A97630_01/server.920/a96533/autotrac.htm Using Autotrace in SQL*Plus]
 +
[https://docs.oracle.com/cd/E18283_01/server.112/e16604/ch_eight.htm Tuning SQL*Plus]
 +
 +
==== Packages ====
 +
Advantages of using packages
 +
- Modularity
 +
- Easy Maintenance
 +
- Encapsulation & Security
 +
- Performance
 +
- Functionality
 +
- Overloading
 +
 +
==== Triggers ====
 +
- Security
 +
- Auditing
 +
- Data Integrity
 +
- Table Logging
 +
- Event Logging
 +
- Derived Data
 +
 +
3 types of Triggers
 +
- DML Triggers
 +
- Compound Triggers
 +
- Non-DML Triggers
 +
 +
==== Debugging ====
 +
dbms_debug_package
 +
JDWP Java Debug Wire Protocol
 +
 +
Never debug on production
 +
 +
[https://www.oracle.com/webfolder/technetwork/tutorials/obe/db/apex/r51/testing_and_debugging_sqldeveloper/testing_and_debugging_sqldeveloper.html Testing and Debugging Procedures with SQL Developer]
 +
 +
'''CARE !! Execute the code on debug'''
 +
Can't debug package
 +
 +
Connect with SYS or SYSTEM and GRAND SESSION and ANY PROCEDURE
 +
 +
On remote get port range from DBA
 +
 +
Breakpoints
 +
- Run until Breakpointoccurs
 +
- Step over
 +
- Step into
 +
 +
==== Dynamic SQL & PL/SQL IN PL/SQL ====
 +
Static SQL
 +
- Written in advance
 +
- parse at compile time
 +
 +
SQL EXECUTION STAGES
 +
- Parse
 +
- Bind
 +
- Execute
 +
- Fetch
 +
 +
Advantages of Dynamic SQL
 +
- Constructed as string at runtime
 +
- Bind variables can be used
 +
- Allows us to use DDL, DCL or Session-Control statements in PL/SQL blocks
 +
 +
How to generate Dynamic SQL(2 Ways)
 +
- Native Dynamic SQL Statements(more efficient and better performance as DBMS_SQL)
 +
- DBMS_SQL Package
 +
 +
Can also generate dynamic PL/SQL blocks
 +
 +
==== SQL Injection ====
 +
 +
==== Oracle-Supplied Packages ====
 +
- StandardPackage
 +
- DBMS_OUTPUT Package(debug subprograms, read-write text data)
 +
- UTL_FILE Package(performing file operations)
 +
- UTL_MAIL Package(performs mailing operations)
 +
 +
=== PL/SQL CODE ===
 +
- Name conventions
 +
 +
== SQL & PERFORMANCE TUNING ==
 +
- What is SQL Tuning and why we need it?
 +
- Oracle Database Architecture
 +
- SQL Tuning Basics
 +
- Execution Plans in Details
 +
- Join Operations
 +
- Basic Tuning Techniques
 +
- Advanced Indexing Techniques
 +
- Hints
 +
- Subjects
 +
 +
Change SQL-Structure or Database-Structure
 +
Need to know of the Database Architecture in Basic
 +
 +
Tools
 +
- SQL plus
 +
- SQL Developer
 +
- SQL Tuning Advisor
 +
is internal diagnostic software that identifies problematic SQL statements and recommends how to improve statement performance.
 +
- Automatic SQL Tuning Advisor
 +
- SQL Access Advisor
 +
is internal diagnostic software that recommends which materialized views, indexes, and  materialized view logs to create, drop, or retain.
 +
- Automatic Database Diagnostic Monitor (ADDM)
 +
ADDM is self-diagnostic software built into Oracle Database.
 +
- TOAD
 +
- AWR
 +
- TKPROF
 +
 +
- Determine the bottlenecks in low-performance Oracle databases
 +
- Use Oracle database performance tuning tools, like: AWR, ASH, V$ views, SQL tracing, real-time monitoring, and EM Express
 +
- Propose solutions to database performance issues
 +
- Implement Oracle methodology in performance tuning
 +
 +
SQL Tuning is a continuous process
 +
You need to tune your queris
 +
- While creating
 +
- After the creating
 +
 +
When to decide tuning?
 +
- By checking the top consuming queries frequently
 +
- After any complaints of bad performance
 +
 +
The reasons of performance loss
 +
- Structural changes
 +
- Changes on the data volume
 +
- Application changes
 +
- Aged statistics
 +
- Database upgrades
 +
- Database parameter changes
 +
- Operating system changes
 +
- hardware changes
 +
 +
=== Blocks ===
 +
All data stored in blocks
 +
smallest unit of database storage
 +
2KB-32KB(default 8KB)
 +
stores row data or index data
 +
 +
Eigenschaften:
 +
- Block Header
 +
  Block Type Information
 +
  Table Information
 +
  Row Directory
 +
    ROWID(Like pointer?)
 +
- Rows
 +
Blocks in Blocks
 +
Jeder Block wird in eine bestimmte Stelle geschrieben
 +
 +
PCTFREE or PCTUSE parameters for the space size in blocks
 +
 +
Wenn nicht genug Platz, wird bei jedem update wahrscheinlich die Position geändert
 +
- Verringerte Leistung
 +
- IO-OPS werden langsamer
 +
- Schlecht für das Tuning
 +
- Verlangsamt die Indexes
 +
 +
Das lesen einer Zeile, von einer Tabelle aus einem Block, ist schneller als aus mehreren Blöcken
 +
 +
1. Row Header
 +
2. Column Data
 +
 +
- Row Overhead
 +
- Number of Columns
 +
- Cluster Key ID(if clustered)
 +
- ROWID of chained Row Pieces(if any)
 +
- Column Length
 +
- Column Value
 +
 +
Wenn die größe der Blocks in den Abfragen verringert wird, steigert man die Leistung
 +
 +
=== PGA | PROGRAM/PROCESS/PRIVATE GLOBAL AREA ===
 +
Private for each user
 +
 +
''4 Hauptspeicher Bereiche:''
 +
'''PGA'''
 +
Session AREA
 +
    - Session info for each user
 +
    - Session Variables, login info, session status, etc.
 +
Unnötige verbindungen vermeiden
 +
 +
'''PSA(Private SQL Area)'''
 +
PERSISTENT AREA
 +
  - Jede Abfrage wird zu einem Cursor
 +
  - Verwendete Bindvariablen der Cursor werden hier gespeichert
 +
RUNTIME AREA
 +
  - Execution state info
 +
CURSOR AREA
 +
- Information of cursors
 +
 +
'''SQL WORK AREA'''
 +
SORT AREA
 +
HASH JOIN AREA
 +
BITMAP MERGE AREA
 +
BITMAP CREATE AREA
 +
 +
Weniger Speicher verringert die LEistung
 +
 +
Speichergröße festlegen
 +
- User
 +
- Oracle selber
 +
 +
=== SGA | SHARED/SYSTEM GLOBAL AREA ===
 +
Database buffer cache
 +
Redo log buffer
 +
Shared pool
 +
  - Data dictionary cache
 +
  - Result Cache
 +
  - other
 +
Large pool
 +
- I/O Buffer
 +
- Free memory
 +
- Response queue
 +
- Request queue
 +
JAVA pool
 +
Streams pool
 +
 +
Context switching between pl/sql engine and sql engine
 +
 +
=== SHARED POOL ===
 +
'''DATA DICTIONARY CACHE'''
 +
- Stores the definitions of the database object and permissions
 +
  - check if Data from query exists
 +
  - check your privileges
 +
 +
'''RESULT CACHE'''
 +
- stores result of common used queries
 +
- stores result of functin
 +
  - Bei wiederholter Abfrage wird aus dem RESULT CACHE gelesen
 +
  - Speicher die Abfrageergebnisse
 +
  - Speicher die Funktionsergebnisse
 +
 +
Erhöht die Leistung
 +
Wird von der Datenbank durchgeführt
 +
User kann anweisen Result Cache anweisen daten zu speichern
 +
 +
'''Library Cache'''
 +
- Stores the execution plans
 +
- Stores procedures, packages control structures
 +
  Oracle erstellt Ausführungspläne
 +
  Wie die Daten aus der Disc oder dem Puffer gelesen werden
 +
  Die Erstellung eines Ausführungsplans ist ein kostspieliger Vorgang
 +
  Die Verwendung eines Ausführungsplans für ähnliche Abfragen kann sehr effizient sein
 +
 +
'''OTHER AREAS'''
 +
 +
=== BUFFER CACHE ===
 +
'''Largest memory area of sga'''
 +
Stores the copies of the blocks read from the disc
 +
Available for all the users
 +
First check Buffer than Disc, for the data
 +
Check Disc only if blocks are missing
 +
Much faster than disc
 +
Size of the memory is endless
 +
Maintained with a complex algorithm
 +
Can't save all data in buffer cache
 +
 +
'''IN-MEMORY-DATABASES'''
 +
Stores the most recently used & most toched ones
 +
'''Database writer process''' handles the write operations to the disc
 +
Writes not one by one, writes all changed blocks in one step
 +
'''dirty blocks'''
 +
Stores Tabledata and Indexdata
 +
 +
Performance for same
 +
- queries
 +
- tables
 +
- indexes
 +
 +
=== REDO LOG BUFFER ===
 +
'''-> Log Writer Process(LGWR)'''
 +
'''-> Redo Log Entries'''
 +
'''-> Redo Log Files'''
 +
'''-> Server Process'''
 +
 +
Redo-Log-Files
 +
Oracle Guarantees Not to Lose Data
 +
- A Redo Log Entry is created when insert, update, delete, create, alter, drop occurs
 +
Create Redo Log entries for all changes
 +
Redo Log Entries has the changes made to the database
 +
They are used for recovery operatins
 +
You need privileges as a developer
 +
Redo Log Entries are Stored in the redo log buffer
 +
Starts every 3 sec and write to disc
 +
'''Redo Log buffer is a circular buffer'''
 +
'''Rollback is not done with redo log data'''
 +
Redo do recovery on rollback redo log files are deleted
 +
 +
=== UNDO ===
 +
What is rollback and recovery
 +
The original data stored into the memory(undo tablespace) is called as undo data
 +
buffer cache for the modifications
 +
undo data is not modified because
 +
  - Used for rollback operations
 +
  - Used for providing read consistency
 +
  - Used for providing flashback feature
 +
 +
'''BLOCKS > EXTENTS > SEGMENTS > TABLESPACE'''
 +
 +
=== DML PROCESS AND COMMIT ===
 +
Optimize Queries and DML's
 +
- Checks the Shared SQL AREA for similar statements to use
 +
- checks the data dictionary cache and checks if query is valid
 +
- Checks buffer cache & undo segements for the related data
 +
- Locks the related blocks
 +
- Makes the changes to the blocks in the buffer cache
 +
- the changes are applied to the redo log buffer before the buffer cache
 +
- the server returns the feedback for the changes
 +
 +
When the user commits:
 +
- System change number write to redo log files
 +
- the server creates a commitrecord with scn
 +
- the lgwr process write redo log entries in the redo log buffer to the redo log files
 +
- the dbwn writes the dirty blocks to the disc & unlocks the blocks
 +
- The server returns a feedback about the transaction completion
 +
 +
=== Automatic memory management===
 +
The size of each memory ara is important for the execution performance of your queries
 +
Wenn des Speicherbereich nicht ausreicht verringert das die performance
 +
It can manage both SGA & PGA memory
 +
In früheren versionen von oracle musste PGA manuell angegeben werden
 +
'''it is recommended to leave automatic memory management enabled to increase the performance'''
 +
will prefent out of memory errors
 +
 +
=== Oracle database storage architecture===
 +
- '''Storage''' = Discs
 +
- '''Control files''' storage the pyhsical structure information of the database(without no access to data)
 +
- '''Data Files''' - Stores data(Tables, procedures, application, data
 +
- '''Online Redo log files''': stores redo log entries
 +
- '''archived redo log files''' - online redo log files are constantly mover ehre
 +
- '''backup files''' - Stores the exact copy of the data files for disaster recovery
 +
- '''Parameter files''' - Stores the configuration data of the database instance
 +
- '''password file''' - Stores the passwords of the amin user(sysdba,sysoper,sysasm)
 +
- alert log & trace files - stores log message and errors occured in the database
 +
 +
Some Informations are for the dev's and some for the oracle support service
 +
 +
=== Logical and Physical Database structure ===
 +
- Blocks | smallest units of storage(2kb-32kb)
 +
- Extents | combination of several consecutive data blocks.
 +
  used for stroing secific type of info
 +
- segments | combination of several extents
 +
  used for storing some big data(tables,indexes, etc)
 +
  - data segments
 +
  - index segments
 +
  - undo segments
 +
  - temporary segments
 +
- tablespaces | combination of many segments. used for grouping the related data in one container
 +
  - Temporary Tabelspace
 +
    Stores teh temporary data of a session
 +
  - Permanent Tablespace
 +
    Stores the persistent schema objects
 +
 +
'''DB must have min 2 Tablespaces'''
 +
- System Tablespace
 +
- SYSAUX Tablespace
 +
DBA can create more Tablespaces
 +
 +
Check for Performance
 +
Set Tablespaces Online and Offline Status
 +
Can do backup or recovery of a give tablespace
 +
Import or export Tablespaces
 +
Create a transportable tablespace and move too other db
 +
 +
=== Schema ===
 +
Ein Schema ist eine Sammlung von Datenbankobjekten die einem Datenbankbenutzer gehören
 +
 +
=== PERFORMANCE TUNING BASICS ===
 +
SQL Tuning is a continuous process
 +
You nedd to tune your queries
 +
  - on creating
 +
  - After the creation
 +
  - On new Indexes created
 +
  - Change of data volume
 +
 +
When to decide tuning?
 +
  - checking the top consuming queries frequently
 +
  - after any complaints of bad performance
 +
 +
two goals in tuning:
 +
- reduce user response time, decreasing time between statement and response
 +
- Improve throughput
 +
 +
Knowledge and skills:
 +
- know your db architecture
 +
- sql
 +
- sql tuning tools
 +
 +
Check oracle database statistics
 +
 +
Betriebssystem
 +
- Software
 +
- Services
 +
- Updates
 +
- Datenbank, Oracle Datenbank Architektur
 +
 +
Häufige Ursache für Performance Verlust, bei Änderung von:
 +
- Tabellen, DB Struktur
 +
- Datenvolumen
 +
- Applikationen
 +
- Datenbank-Update
 +
- Datenbank-Parameter
 +
- Betriebssystem
 +
- Betriebssystem-Update
 +
- Betriebssystem-Konfiguration
 +
- Hardware
 +
 +
Welche Oracle Packages für:
 +
- Modularity
 +
- Easy Maintenance
 +
- Encapsulation & Security
 +
- Performance
 +
- Functionality
 +
- Overloading
 +
 +
Tuning-Maßnahmen | SQL & PERFORMANCE TUNING
 +
- SQL Tuning Basics
 +
- Execution Plans in Details
 +
- Join Operations
 +
- Basic Tuning Techniques
 +
- Advanced Indexing Techniques
 +
- Hints
 +
- Subjects
 +
 +
=== BAD SQL ===
 +
Unnecessary:
 +
- parse time
 +
- I/O operations
 +
- CPU Time
 +
- waits
 +
 +
'''TIME on Wait(CPU) + Time on Execution = DB TIME'''
 +
The reason of a bad sql
 +
- bad design
 +
- poor coding
 +
- inefficient execution plan
 +
 +
=== Effective Schema Design ===
 +
- Assign data types as much as needed
 +
- datatypes with variable-length
 +
- check data-consistency problems
 +
- select exactly the same data type between parent-child-keys
 +
- don't use varchar2 for the most of the datatypes/strings/values
 +
 +
Enforce data integrity
 +
- correct PK and FK
 +
- use normalization well
 +
- smaller tables, faster join operation
 +
- select right table type
 +
  - heap-organized table
 +
  - Index Clustered tables
 +
  - Hash Clustered tables
 +
  - Index-Organized tables
 +
  - External tables
 +
  - and more
 +
- Create Clusters
 +
- use indexes often and select index type carefully
 +
- create good indexes methods
 +
- Create index-organized tables(IOT)
 +
 +
=== table partitioning ===
 +
 +
=== SQL STATEMENT PROCESSED ===
 +
-> '''Syntax check'''
 +
-> '''Semantic check'''
 +
-> '''Privilege check'''
 +
-> '''Allocate private SQL AREA'''
 +
Existing shared SQL AREA
 +
NO-> '''HARDPARSE'''(Library cache miss)
 +
  -> Allocate shared sql area
 +
  -> optimization
 +
  -> row source generation
 +
YES-> '''SOFTPARSE'''
 +
-> EXECUTE STATEMENT
 +
 +
Softparse is faster, use for tuning
 +
- Hashwert in Libcache
 +
  - Key for the executionplan in shared sql area
 +
- executionplan is stored in shared library
 +
 +
Optimierer ist Software die Abfrage als Eingabe erhält und den besten ExecutionPlan erstellt
 +
 +
Full-table scan
 +
Index scan
 +
 +
'''1. Optimization'''
 +
'''2. Execution plans'''
 +
'''3. Row Source Generation'''
 +
 +
Result-Cache
 +
 +
Select * from... STEPS
 +
-> Check Schema Information
 +
-> Find possible access paths
 +
  -> Use Index
 +
  -> Read whole table
 +
-> check statistics
 +
 +
selective query
 +
 +
'''Don't use Index if Result is bigger than 25% of the whole table?'''
 +
 +
==== Access Methods====
 +
- Table Access Path
 +
  - Full-Table scan
 +
  - Table Access by ROWID
 +
- B-Tree Indexx Access Path
 +
  - Index unique scan
 +
  - Index range scan
 +
  - Index skip scan
 +
  - Full Index scan
 +
- Bitmap index Access Path
 +
- Table Cluster Access Path
 +
 +
In-List- Iterator
 +
 +
=== Optimizer overview===
 +
- Optimizer
 +
- RBO | RuleBasedO(not in use anymore since 10g)
 +
- CBO | CostBaseO
 +
  Plan is dynamic
 +
-> Query Transformer(Transform Query)
 +
-> Estimator(Query+estimates)
 +
^-> Plan Generator(Query plan to row source generation)
 +
 +
===Query Transformer===
 +
Query transforms the query into a better performing one but semantically equivalent of it
 +
If the transform isn't better, it use the original one
 +
Time restriction and old statistics may lead a wrong plan creation
 +
Or Expansion
 +
  Using or in the where clause will prevent index usage
 +
 
 +
'''SELECT * FROM ID WHERE ID = 1 OR ID = 2;'''
 +
Faster:
 +
'''SELECT * FROM ID WHERE ID = 1;'''
 +
'''UNION ALL'''
 +
'''SELECT * FROM ID WHERE ID = 2;'''
 +
 +
Subquery Unnesting
 +
Verschachtelte Abfrage in Join-Anweisung
 +
'''SELECT * FROM ID WHERE ID IN'''
 +
    '''(SELECT ID FROM CLIENTS)'''
 +
The otimizer transfomrs a nested query into a join statement
 +
SELECT ID,*
 +
FROM ID, CLIENTS
 +
WHERE ID.ID = CLIENTS.ID;
 +
 +
Complex VIEW
 +
 +
=== Selectivity & Cardinality ===
 +
Selectivity = NUMBER OF ROWS FROM QUERY / TOTAL NUMBERS OF THE ROWS
 +
Cardinality = Total numbers of rows x Selectivity
 +
 +
Why selectivity and cardinality is important?
 +
- SEL affects the estimates in I/O cost
 +
- SEL affects the sort cost
 +
- CAR is used to determine join, sort and filter costs
 +
- Incorrect SEL and CAR = incorrect plan cost estimation
 +
 +
=== COST IN DETAIL ===
 +
- Cost is the optimizer's best estimate of the numbers of I/O to execute statement
 +
- To estimate the cost, the estimator uses:
 +
  - Disk I/O
 +
  - CPU usage
 +
  - Memory usage
 +
 +
'''COST = SINGLE-BLOCK I/O COST + MULTI-BLOCK I/O COST + CPU COST / SINGLE-BLOCK READ TIME'''
 +
- SINGEL BLOCK READ TIME
 +
- MULTI BLOCK READ TIME
 +
- CPU CYCLES / CPU SPEED
 +
 +
=== PLAN GENERATOR ===
 +
Basierend auf ausprobieren von verschiedenen Zugriffspfaden,JOIN-Methoden und JOIN-Reihenfolgen
 +
 +
=== ROW SOURCE GENERATOR===
 +
once the plan generator generates the optimum plan, it handles that to the row source generator
 +
generates an interative execution plan usabel for the database
 +
is an area that we get the row set(Table,view,result of join or groups)
 +
row source tree show the following information:
 +
- execution order
 +
- access methods
 +
- join methods
 +
- data operatons(filter,sort,...)
 +
 +
=== SQL TUNING PRINICPLES AND STRATEGIES===
 +
SQL TUNING PRINCIPALS
 +
- IDENTIFYING the issue
 +
- carify teh details of that issue
 +
- collection data
 +
- analyze the data
 +
- choose an appropriate tuning strategy
 +
 +
SQL TUNING STRATEGIES
 +
- Parse time reduction
 +
- plan comparison strategy
 +
- quick solution strategy
 +
- finding & implementing a good plan
 +
- '''Query analysis strategy'''
 +
 +
SQL TUNING ADVISOR(braucht zugriffsrechte)
 +
otimierungsmodus ändern
 +
index-caching
 +
etc..
 +
 +
Use dynamic stats to create better plans
 +
 +
=== QUERY ANALYSIS STRATEGIE===
 +
- quick tuning strategies did not work, and we have time to work on this problem
 +
- query can be modified
 +
- determine the underlying cause
 +
 +
'''What to do on this strategy'''
 +
- statistics and parameters
 +
- query structure
 +
- access paths
 +
- join orders & join methods(changes?)
 +
- others
 +
 +
'''Collecting Data'''
 +
- Execution plan
 +
- information of the objects in the query
 +
- collecting statistics
 +
  - object stats
 +
  - systems stats
 +
- histograms
 +
'''The available tools are'''
 +
- sqlt
 +
- dbms_stats
 +
- tkprof
 +
- awr report
 +
- etc..
 +
 +
'''Systemstatistiken und Histogramme'''
 +
 +
'''PRE-ANALYZE of the Query'''
 +
- check the
 +
  - volumes of resulting data
 +
  - predicates
 +
  - problematic constructs
 +
    - OUTER JOIN
 +
    - VIEWS
 +
    - SUBQUERIES
 +
    - IN or OR list
 +
    - Hierarchical queries
 +
 +
'''Analyzing the execution plan'''
 +
'''Tools to get the execution plan'''
 +
  - SQL TRACE
 +
  - TKPROF
 +
  - V$_SQL_PLAN
 +
  - DBMS_MONITOR
 +
  - AWRSQRPT.SQL
 +
  - etc..
 +
 +
'''How to read the execution plan:'''
 +
- check the access paths
 +
- check the join order and the join type
 +
- compare actual & estimated number of rows
 +
- find the steps where cost and logical reads are different
 +
 +
'''Analyzing by considering the query tuning techniques'''
 +
 +
'''Find a possible solution'''
 +
- updating statitics
 +
- using dynamic stats
 +
- creating or re-create an index
 +
- creating index-organized tables(IOT)
 +
- using hints
 +
- others
 +
 +
=== EXECUTION PLAN AND EXPLAIN IN DETAIL===
 +
An execution plan is a list of steps how to execute an sql statement
 +
 +
- Operations
 +
- Object_name
 +
- Cardinality
 +
- Cost
 +
- Partition_Start
 +
- Partition_Stop
 +
 +
Stat types
 +
- system stats
 +
- optimizer stats
 +
System stats
 +
  - used by the optimizer to estimate I/O and CPU
 +
  - costs
 +
  - should be generated regularly
 +
- shoud be gathered during a normal workload
 +
 +
regenaration/recreate on every hardware change the system stats
 +
 +
Access Predicates
 +
Filter Preddiactes
 +
 +
USE OF:
 +
'''EXEC dbms_stats.gather_system_stats('Start');'''
 +
Prozedur
 +
collect_system_stats()
 +
 +
Berechtigung auf Tabelle '''aux_stats$'''
 +
 +
Optimizer Statitics
 +
- can be gathered manually or autmatically
 +
 +
Dynamic stats?
 +
 +
Job für Automatische Erfassung der Statistiken
 +
 +
ANALYZE TABLE [TABLE_NAME] COMPUTE STATISTICS;
 +
Sollte nicht mehr genutzt werden
 +
 +
DBMS_STATS package
 +
PROCEDURES:
 +
GATHER_DATABASE_STATS
 +
GATHER_DICTIONARY_STATS
 +
GATHER_SCHEMA_STATS
 +
GATHER_TABLE_STATS | very fast
 +
GATHER_INDEX_STATS | faster for singel indexes
 +
 +
How can see the optimizer statistics
 +
- DBA_TABLES
 +
- DBA_TAB_STATISTICS
 +
- DBA_COL_STATISTICS
 +
- DBA_INDEXES
 +
- DBA_CLUSTERS
 +
- DBA_TAB_PARTITIONS
 +
- DBA_IND_PARTITIONS
 +
- DBA_PART_COL_STATISTICS
 +
 +
=== Generating Execution plan ===
 +
To analyze an execution plan
 +
  - explain plan
 +
  - autotrace
 +
  - V$SQL_PLAN
 +
EXPLAIN PLAN
 +
'''EXPLAN PLAN FOR [QUERY];'''
 +
generates the explain plan and save into plan_table
 +
 +
select * from plan_table;
 +
 +
=== AUTOTRACE ===
 +
SET AUTOTRACE ON;
 +
SET AUTOTRACE ON [EXPLAIN|STATISTICS];
 +
SET AUTOTRACE TRACE[ONLY] ON [EXPLAIN|STATISTICS];
 +
SET AUTOTRACE OFF;
 +
 +
Autotrace uses plan table like the explain plan
 +
Bindvariablen mit implizierter typenkonvertierung, klappt manchmal nicht
 +
 +
SQLPLUS>>
 +
SQL> set linesize 200;
 +
SQL> set autotrace traceonly explain;
 +
SQL> select * from sales s, customers c where s.cust_id = c.cust_id and s.cust_id = 987;
 +
SQL> set autotrace traceonly statistics;
 +
SQL> set autotrace traceonly statistics;
 +
SQL> set autotrace traceonly;
 +
SQL> set autotrace on;
 +
SQL> show autotrace;
 +
 +
AUTOTRACE WITH SQL DEVELOPER
 +
 +
Kann man den Code nicht durch generieren des explain-plans optimieren, nutzt man autotrace-methoden
 +
Bei DML-OPS zuerst den EXPLAIN-PLAN
 +
 +
=== V$SQL_PLAN VIEW ===
 +
There are a lot of performance views tha can be used for tuning
 +
- V$SQLAREA
 +
- V$SQL_WORKAREA
 +
- V$SQL
 +
- V$SQL_PLAN
 +
- V$SQL_PLAN_STATISTICS
 +
- V$SQL_PLAN_STATISTICS_ALL
 +
 +
V$SQL_PLAN
 +
- Actaul execution plans are stored here
 +
- It is very similar to plan_table
 +
Wenn es eine Unterschied zwischen beiden gibt, gilt der Wert von V$SQL_PLAN als wahr
 +
- V$SQL view
 +
Sie können beide die Tabellenfunktin und das dbms_xplan Paket aufrufen für eine gute Ausgabe
 +
 +
'''SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('XXXXX'));'''
 +
 +
=== READING THE EXECUTION PLANS ===
 +
Zugriffsprädikate werden für die Zeilenquellen verwendet
 +
Bitmap-KOnvertierung in ROWIDS die ROWIDS aus der eingehenden Quelle
 +
Tabellen Zugrif durch lokale Index-ROWID
 +
Oft werden 0 Costs angezeigt, welche aber nicht 0 sind sondern einfach nur zu gering
 +
 +
2 Arten von Optimierung
 +
- kostenbasierte | '''COST-BASED-OPTIMIZATION''' (Empfehlung von Oracle)
 +
- regelbasierte  | '''RULE-BASED-OPTIMIZATION''' (Alte Art, nicht so genau)
 +
 +
ALL_ROWS
 +
ANALYZED
 +
 +
first_rows & .... und später den rest, schnellere Reaktionszeit
 +
 +
Parameter type String
 +
Syntax OPTIMIZER_MODE =
 +
 +
{ first_rows_[1 | 10 | 100 | 1000] | first_rows | all_rows }
 +
Default value all_rows
 +
Modifiable ALTER SESSION, ALTER SYSTEM
 +
 +
[https://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams145.htm#REFRN10145 OPTIMIZER_MODE]
 +
 +
Detailierte Tuning Strategie vs schnelle Tuning Strategie
 +
 +
Where to look?
 +
- Cost
 +
- Access Methods
 +
- Cardinality
 +
- JOIN Methods & JOIN TYPES
 +
- Partition Pruning
 +
- Others
 +
##??
 +
- Parse count
 +
- Number of fetches
 +
- etc..
 +
 +
=== Optimizer hints ===
 +
- To command the optimizer, we use optimizer hints
 +
- optimizer hints force the optimizer to pick a specific action
 +
- The optimizer may not follow your hints
 +
- If hint is not reasonable, the optimizer will ignore it
 +
- Hints can be operating on a single hash, multi-tables, a query block, a specific statement
 +
 +
Categories of the hints:
 +
- Hints for optimization approaches
 +
- Access paths hints
 +
- Query Transformation Hints
 +
- Join order Hints
 +
- Parallel Execution hint
 +
- Others..
 +
 +
SELECT /*+ hint_name(para1 para2 para3) */ id from db;
 +
- hints can used after a select, update or delete keywords
 +
- you can use the table name or its alias as the hint parameter. but if there is an alias, you cannot use the table name
 +
- There can be only one hint area
 +
- be careful on the hints you selected, especially if you are using multiple hints
 +
  you may lead the optimizer to a bad execution plan
 +
 +
=== Gathering stats ===
 +
optimizer statistics are crucialto sql tuning
 +
 +
=== Search for * in execution plan ===
 +
- cardinality
 +
- Access method
 +
- Join method
 +
- Join type
 +
- join order
 +
- Partition pruning
 +
- parallel execution

Aktuelle Version vom 20. Juni 2022, 10:16 Uhr

Inhaltsverzeichnis

ORACLE DATABASE HACKBUGZ

CREATE USER

$ sqlplus
or
$ connect /as sysdba
sp> create user [NAME]identified by [PASSWD];
sp> grant dba to admin;

GRANT ALL DICTIONARY TO USER

sp> grant select any dictionary to [NAME];

CREATE GLOBAL TEMPORARY TABLE

CREATE GLOBAL TEMPORARY TABLE my_temp_table (
 id           NUMBER,
 description  VARCHAR2(20)
)
ON COMMIT PRESERVE ROWS;

-- Insert and commit, then check contents of GTT.
INSERT INTO my_temp_table VALUES (1, 'ONE');
COMMIT;

SELECT COUNT(*) FROM my_temp_table;

PL/SQL

PL/SQL stands for “Procedural Language extensions to the Structured Query Language”. 
SQL is a popular language for both querying and updating data in the relational database management systems (RDBMS). 
PL/SQL adds many procedural constructs to SQL language to overcome some limitations of SQL. 
Besides, PL/SQL provides a more comprehensive programming language solution for building mission-critical applications on Oracle Databases.

What is PL/SQL 

PL/SQL is an embedded language. PL/SQL only can execute in an Oracle Database.
Platform Independence

Oracle VM VirtualBox

Database Virtual Box Appliance / Virtual Machine 

- Oracle Forms

PL/SQL ENGINE
Procedural Statment Executor

Architecture
- Cooperates with SQL Engine
- Enables Subprograms
- Dynamic Queries
- Case Insensitivity
- Oracle Style
- Optimizer
- Enables Object-Orinted Programming(OO Programmierung mit abstrakten Datentypen)
- Web Development
  - PL/SQL gateway
  - PL/SQL Web Toolkit


SQL ENGINE
SQL Statement Executor
3 Schritte:
Parsing
Fetching
Executing
Bei der Ausführung optimiert der SQL-Optimierer

Database-Management-Tools

- TOAD(Tools for Oracle application Development)
- Oracle SQL Developer
- SQLplus(command line tool)

Oracle SQL Developer

Oracle SQL DEVELOPER
ANSICHT/DATA MODELER/BROWSER

Fix network adapter connection error:
Check File: listener.ora
or
Shell:
$ lsnrctl status
$ lsnrctl start
$ services.msc

Schema vs Tablespace

A schema is a namespace - a logical thing. It is used to organize the names of database objects. It has nothing to do with the way the data is stored.

A tablespace is a physical thing. It's a container for data and has nothing to do with the logical organization of the database objects. 
A database schema vs a database tablespace?

SQL | DDL, DQL, DML, DCL and TCL Commands

DDL – Data Definition Language
DQl – Data Query Language
DML – Data Manipulation Language
DCL – Data Control Language

DDL (Data Definition Language): 
- CREATE: This command is used to create the database or its objects (like table, index, function, views, store procedure, and triggers).
   - Table begin with a letter
   - 1-30 character long
   - A-Z,0-9,-,$,#
   - no duplicate names
   - no reserver words(select,from,etc.)
- DROP: This command is used to delete objects from the database.
- ALTER: This is used to alter the structure of the database.
- TRUNCATE: This is used to remove all records from a table, including all spaces allocated for the records are removed.
- COMMENT: This is used to add comments to the data dictionary.
- RENAME: This is used to rename an object existing in the database.

DQL (Data Query Language):
- SELECT: It is used to retrieve data from the database.

DML(Data Manipulation Language): 
- INSERT : It is used to insert data into a table.
- UPDATE: It is used to update existing data within a table.
- DELETE : It is used to delete records from a database table.
- LOCK: Table control concurrency.
- CALL: Call a PL/SQL or JAVA subprogram.
- EXPLAIN PLAN: It describes the access path to data.

SELECT
FROM
JOIN
WHERE
GROUP BY
HAVING
ORDER BY

DCL (Data Control Language): 
- GRANT: This command gives users access privileges to the database.
- REVOKE: This command withdraws the user’s access privileges given by using the GRANT command.

TCL – Transaction Control Language 
- COMMIT: Commits a Transaction.
- ROLLBACK: Rollbacks a transaction in case of any error occurs.
- SAVEPOINT:Sets a savepoint within a transaction.
- SET TRANSACTION: Specify characteristics for the transaction.

Character functions

lower       | converts
upper       | converts
length      | returns
substring   | select characters from string to display

concat    | join 2 strings with ||
initcap   | first letter to uppercase
trim      | trims text from specified x
ltrim     | trims text from left
rtrim     | trims text from right
replace   | replace and remove strings
translate | replace set of characters

convert functions

to_char   = numeric and date to string
to_date   = numeric & string to date
to_number = value to numeric

date functions

add_months     | add and return
months_between | return between x and y
next_day       | next day of day
last_day       | days left in month from date

numeric functions

round     | 
trunc     | 
floor     | 
ceil      | 
mod       |

Oracle data constrains

NULL         | not null
UNIQUE       | each row unique
PRIMARY KEY  | references pk in other table
FOREIGN KEY  | references fk in other table
CHECK        | meets condition
DEFAULT      |

ALIAS

shorter query
more than one table
table or column name too long or not reader friendly
two or more columns combined together
when functions used in query

Limiting and sorting data

retrieve a only a part of a big data and sort the result
use select clause and operators

sub query

from clause

where

order by

filter or sort data
required by some analytic functions
function () over ( order by column_name)

group by having

like & wildcards

JOIN

equi join
non equi join
catesian join
outer join
self join

join
ON
USING(mergeinto a single col)

aggregate function

avg
count
max
min
sum

set_operator

union all
  returns result from both queries after eliminating doubles
intersect set
 rows that are common to both queries
minnus set
 rows in the first query that are not in the second query

set operators summary
  use order by only at the end of statement

analytic functions

SQL logical operators

- and
- or
- in
- between
- like
- not
- all
- any
- is null

VIEW and DUAL TABLE

VIEW
SQL SELECT QUERY, stored in db, treated like a table but no data stored in.

DUAL TABLE
A special dummy one-row, one-column table, present by default in all oracle db installations
varchar2(1), column value is x

Describe

Description relating to the db obj like tables and view

Synonyms

alias for an obj in the db
can be used to hide the identity and location

PRIVATE SYNONYM

PUBLIC SYNONYM

SQL INDEXES

Used to speed up the performance of sql-querys or statements
Pointer to data

Type of Indexes

- B-Tree
- Bitmap
  Can be simple or composite
- Function-Based
  If we use a function on a column which is indexed, then such an index will not be used
  Create an index on functionor expression
- Index - Organized Tables
  Für kleine Datenmengen
  B-Tree-Format
- Cluster Index
  group of multi Tables, stores data in the same block
- Composite Index

B-Tree
- Branch Block
- Leaf Block

Fragementation of Index
Rebuild the Indexes

Create index [NAME] on [TABLE](COL)
Create index [NAME] on [TABLE](COL1,COL2)

ERD - Entity Relationship Diagram

Cardinality

Crow's foot notation

Pluggable Database

Pluggable Database = Multitenant Architecture

sys-user rolle SYSDBA or SYSOPER

Methoden wie "Bulk Insert"

About DML Statements and Transactions

PL/SQL - Arrays

BLOCKS

DECLARE     | optional
BEGIN       | mandatory
EXCEPTION   | optional
END;        | mandatory

3 TYPES OF BLOCKS
- Anonymous Blocks
- Procedures
- Functions

PGA | PROGRAM GLOBAL AREA

PL/SQL Outputs

Not an output language
SET SERVEROUTPUT ON
SET SERVEROUTPUT ON SIZE UNLIMITED
SET SERVEROUTPUT ON SIZE 5000
DBMS_OUTPUT | Package
DBMS_OUTPUT 
106 DBMS_OUTPUT

Ein Paket ist ein PL/SQL Objekt das Prozeduren oder Funktionen enthält um Operationen auszuführen.

SET SERVEROUTPUT ON; 
begin 
dbms_output.put_line ('Hello'); 
end; 

 SET SERVEROUTPUT ON; 
 begin 
 dbms_output.put_line ('Anonymous Block'); 
    begin 
        dbms_output.put_line ('Nested Block'); 
    end; 
 end;

PL/SQL VARIABLE

TYPES:

SCALAR
 CHAR (max_length) | 32767 bytes
 VARCHAR2(max_length)
NUMBER[precision,sale]
BINARY_INTEGER = PLSINTEGER | (LOWER MEMORY, FASTER FOR ARITHMETIC)
BINARY_FLOAT
BINARY_DOUBLE
BOOLEAN | TRUE, FALSE, NULL (DEFAULT NULL)
DATE
TIMESTAMP 
TIMESTAMP(p) WITH TIME ZONE
TIMESTAMP(p) WITH LOCAL TIME ZONE
INTERVAL(p) YEAR TO MONTH
INTERVAL(p) DAY TO SECOND(p)

- LARGE OBJECTS
- REFERENCE
- COMPOSITE

Variable naming rules 

start with letter
can contain special chars
max 30 chars
no oracle reserved names

name conventions:
v_variable
cur_cursor
e_exception
p_procedure
p_function
b_bind

variable_name [CONSTANT] datatype [NOT NULL] [:= | DEFAULT initial_value] 

sales number(10, 2); 
pi CONSTANT double precision := 3.1415; 
name varchar2(25); 
address varchar2(100);

DECLARE
   V_TEXT_1 VARCHAR2(50) := 'STRING';
   V_TEXT_2 VARCHAR2(50) NOT NULL DEFAULT 'DEFAULT';
   V_TEXT_3 VARCHAR2(50) := 'STRING';    
   V_TEXT_4 VARCHAR2(50) := 'STRING';
   
   V_NUMBER_1 NUMBER NOT NULL := 50;
   V_NUMBER_2 NUMBER NOT NULL := 100.02;
   V_NUMBER_3 NUMBER(4) NOT NULL := 111.22;
   V_NUMBER_4 NUMBER(5,2) NOT NULL := 111.22;
   V_NUMBER_5 NUMBER(5,1) NOT NULL := 111.22;
   
   V_PLS_INTEGER_1 PLS_INTEGER := 111.22;
   V_BINARY_INTEGER_1 BINARY_INTEGER := 111.22;
   V_BINARY_FLOAT_1 PLS_INTEGER := 111.22f;
       
   V_DATE_1 DATE NOT NULL := SYSDATE;
   V_DATE_2 DATE NOT NULL := '6 JUN-22 08:01:22';
   
   V_TIMESTAMP_1 TIMESTAMP NOT NULL := SYSTIMESTAMP;
   V_TIMESTAMP_2 TIMESTAMP(3) WITH TIME ZONE NOT NULL := SYSTIMESTAMP;

PL/SQL DELIMETER | COMMENTS

-- CODE --
/* CODE */

PL/SQL VARIABLE

- Declaring
- Initializing
- Attribute
- Scope
- Bind

PL/SQL BIND VARIABLE

SQL-Bindings können helfen, die Performance von dynamischem SQL ganz erheblich zu verbessern.

Die meisten Datenbankanbieter verfügen heutzutage über einen Ausführungsplan-Cache (Oracle nennt ihn Cursor-Cache), 
in dem zuvor analysierte SQL-Anweisungen gespeichert sind und deren Ausführungsplan (und -pläne) zur Wiederverwendung zwischengespeichert werden. 
Dies ist der Hauptgrund, warum Bind-Variablen so wichtig sind

PL/SQL CONTROL STRUCTURES

- IF
- CASE EXPRESSIONS
- LOOPS
  - Basic LOOP
  - WHILE LOOP
  - FOR LOOP
  - NESTED LOOP
  - LOOP LABELING
- CONTINUE STATEMENT
- GOTO

IF | IF-ELSE | IF-ELSE IF

The IF statement allows you to either execute or skip a sequence of statements, depending on a condition. 
The IF statement has the three forms:

– IF THEN
– IF THEN ELSE
– IF THEN ELSIF

IF condition THEN
statements;
END IF;

LOOP

The CASE statement chooses one sequence of statements to execute out of many possible sequences.
The CASE statement has two types: simple CASE statement and searched CASE statement. 
Both types of the CASE statements support an optional ELSE clause.

Simple CASE statement
Searched CASE statement

This structure is the most basic of all the loop constructs including FOR LOOP and WHILE LOOP. 
This basic LOOP statement consists of a LOOP keyword, a body of executable code, and the END LOOP keywords.

The LOOP statement executes the statements in its body and returns control to the top of the loop. 
Typically, the body of the loop contains at least one EXIT or EXIT WHEN statement for terminating the loop. Otherwise, the loop becomes an infinite loop.

The LOOP statement can have an optional label that appears at the beginning and the end of the statement.

<<label>> LOOP 
 statements; 
END LOOP loop_label; 

 LOOP 
  EXIT; 
 END LOOP;  

 LOOP 
  IF condition THEN 
   EXIT; 
  END IF; 
 END LOOP; 

EXIT WHEN statement
The EXIT WHEN statement has the following syntax:
EXIT WHEN condition;

Nested loops
It is possible to nest a LOOP statement within another LOOP statement as shown in the following example:

USING SQL INSIDE PL/SQL

SELECT columns|expressions
INTO variables|records
FROM table|tables
[WHERE condition];

Gleiche Reihenfolge|Typen der Variablen im STATEMENT

PL/SQL Static SQL

DML OPERATIONS

A Transaction start's with a DML and end with a commit,rollback or system error

- INSERT
- UPDATE
- DELETE
- MERGE

CREATE TABLE testcopy as
       select * from test;

There is no create or replace table in Oracle

DROP TABLE test;
CREATE TABLE test ...;

SEQUENCES

Use of CURRVAL and NEXTVAL
A sequence is a schema object that can generate unique sequential values. 
These values are often used for primary and unique keys. 
You can refer to sequence values in SQL statements with these pseudocolumns:

CURRVAL: Returns the current value of a sequence
NEXTVAL: Increments the sequence and returns the next value
In same statement increments only 1 time
select 1.NEXTVAL 1.NEXTVAL 1.NEXTVAL ....

You must qualify CURRVAL and NEXTVAL with the name of the sequence:
SELECT sequence_name.nextval|currval INTO variable|column
FROM table_name|dual;

Variable|column :=sequence_name.nextval|currval

How do I reset a sequence in Oracle?
How to reset sequences?
Dynamic SQL to reset sequence value

COMPOSITE DATA TYPES

Records
Ein Datensatz mit Einem oder mehrere Werte
Gleiche oder unterschiedliche Typen
Datensatz innerhalb eines Datensatzes
NULL by Default

1. Weg
r_name TABLE_NAME%rowtype;

Vorteil:
- Dynamische Typen-Zuweisung aus Tabelle
- Durchführung einfacher DML Operationen

2. Weg
- Einzelne Spalten Auswahl
- Eigene Typen definition

type NAME is record ( NAME TYPE,
                      NAME TYPE, 
                      []);

Alle typen ausser ref-cursors

Einen Datensatz
Collections
List of the same type
Vorsicht vor Speicherfehler 

3 Types

- Nested tables
    Keys only Numbers, begin fom 1
    No Max Values

- VARRAYs
    Begrenzung der Grösse
    Genau Angabe der Grösse
    Objekt für Prozedur oder Funktion
    Seltene Verwendung
Max 2 GB
Varray-Typ in der Datenbank
Can't go back
Bounded
Index start from 1
one dimensional array
null by default

- Associative Arrays
    Key can be strings

CURSORS

Bei normaler SQL-Abfrage, wird nur ein Teil der Ergebnismenge, Algorithemn, Ausführungspläne, etc. im KOntextbereich gespeichert.

Cursors are pointers to the Data, in Memory or Disc

2 Types of Cursors:

Implicit Cursor(Create and administration by Database)
  Bei jedem SELECT wird ein Impliziter Cursor erstellt

Explicit Cursor
We can control cursors
Explicit cursors are created by the programmers
Collection vs cursor
You cannot go back in cursors
Abruf- und Speicheralgorithmus
Gleichzeitiges laden von Ergebnismengen in den Speicher
Operationen sind so viel schneller

Anordnung vom Server anhand der Ergebnismenge und der Speichersituation.
Ein Select aus dem Speicher ist schneller als aus der Datenbank

Usage:
- Declare
- Open
- Fetch
- Check
- Close

loop
exit when NAME%notfound;

Cursor Parameters

Cursor Attributes
- %FOUND
- %NOTFOUND
- %ISOPEN
- %ROWCOUNT

FOR UPDATE CLAUSE
- for update(locks the selected rows)
- nowait
- wait(default)
- for update of(locks only selected tables)

EXCEPTIONS

- ORA CODE
 ORA-01403: no data found

Over 10.000 nonamed exceptions
Cause 
Action

A block has 3 sections
- Declare
- Begin-End
- Exception

Implicit
Explicit

- Predefined Oracle Server Error
- Nonpredefined Oracle Server Error
- User-defined Errors

Select "Step Into" on first run, with anonymous blocks
Handle by 3 ways:
1. Trap
2. Propagate
3. Trap & Propagate

SQLCODE & SQLERRM

FUNCTIONS & PROCEDURES

Stored in the Database with names
Compiled only once
Can be called by another block or application
Can return values
Can take parameters

Functions vs procedures in Oracle
What is the difference between function and procedure in PL/SQL?
Function-Based Indexes
Using Autotrace in SQL*Plus
Tuning SQL*Plus

Packages

Advantages of using packages
- Modularity
- Easy Maintenance
- Encapsulation & Security
- Performance
- Functionality
- Overloading

Triggers

- Security
- Auditing
- Data Integrity
- Table Logging
- Event Logging
- Derived Data

3 types of Triggers
- DML Triggers
- Compound Triggers
- Non-DML Triggers

Debugging

dbms_debug_package
JDWP Java Debug Wire Protocol

Never debug on production

Testing and Debugging Procedures with SQL Developer

CARE !! Execute the code on debug
Can't debug package

Connect with SYS or SYSTEM and GRAND SESSION and ANY PROCEDURE

On remote get port range from DBA

Breakpoints
- Run until Breakpointoccurs
- Step over
- Step into

Dynamic SQL & PL/SQL IN PL/SQL

Static SQL
- Written in advance
- parse at compile time

SQL EXECUTION STAGES
- Parse
- Bind
- Execute
- Fetch

Advantages of Dynamic SQL
- Constructed as string at runtime
- Bind variables can be used
- Allows us to use DDL, DCL or Session-Control statements in PL/SQL blocks

How to generate Dynamic SQL(2 Ways)
- Native Dynamic SQL Statements(more efficient and better performance as DBMS_SQL)
- DBMS_SQL Package

Can also generate dynamic PL/SQL blocks

SQL Injection

Oracle-Supplied Packages

- StandardPackage
- DBMS_OUTPUT Package(debug subprograms, read-write text data)
- UTL_FILE Package(performing file operations)
- UTL_MAIL Package(performs mailing operations)

PL/SQL CODE

- Name conventions

SQL & PERFORMANCE TUNING

- What is SQL Tuning and why we need it?
- Oracle Database Architecture
- SQL Tuning Basics
- Execution Plans in Details
- Join Operations
- Basic Tuning Techniques
- Advanced Indexing Techniques
- Hints
- Subjects

Change SQL-Structure or Database-Structure
Need to know of the Database Architecture in Basic

Tools
- SQL plus
- SQL Developer
- SQL Tuning Advisor
is internal diagnostic software that identifies problematic SQL statements and recommends how to improve statement performance.
- Automatic SQL Tuning Advisor
- SQL Access Advisor 
is internal diagnostic software that recommends which materialized views, indexes, and  materialized view logs to create, drop, or retain.
- Automatic Database Diagnostic Monitor (ADDM)
ADDM is self-diagnostic software built into Oracle Database.
- TOAD
- AWR
- TKPROF

- Determine the bottlenecks in low-performance Oracle databases
- Use Oracle database performance tuning tools, like: AWR, ASH, V$ views, SQL tracing, real-time monitoring, and EM Express
- Propose solutions to database performance issues
- Implement Oracle methodology in performance tuning

SQL Tuning is a continuous process
You need to tune your queris
- While creating
- After the creating

When to decide tuning?
- By checking the top consuming queries frequently
- After any complaints of bad performance

The reasons of performance loss
- Structural changes
- Changes on the data volume
- Application changes
- Aged statistics
- Database upgrades
- Database parameter changes
- Operating system changes
- hardware changes

Blocks

All data stored in blocks
smallest unit of database storage
2KB-32KB(default 8KB)
stores row data or index data

Eigenschaften:
- Block Header
  Block Type Information
  Table Information
  Row Directory
    ROWID(Like pointer?)
- Rows
Blocks in Blocks
Jeder Block wird in eine bestimmte Stelle geschrieben

PCTFREE or PCTUSE parameters for the space size in blocks

Wenn nicht genug Platz, wird bei jedem update wahrscheinlich die Position geändert
- Verringerte Leistung 
- IO-OPS werden langsamer
- Schlecht für das Tuning
- Verlangsamt die Indexes

Das lesen einer Zeile, von einer Tabelle aus einem Block, ist schneller als aus mehreren Blöcken

1. Row Header
2. Column Data

- Row Overhead
- Number of Columns
- Cluster Key ID(if clustered)
- ROWID of chained Row Pieces(if any)
- Column Length
- Column Value

Wenn die größe der Blocks in den Abfragen verringert wird, steigert man die Leistung

PGA | PROGRAM/PROCESS/PRIVATE GLOBAL AREA

Private for each user

4 Hauptspeicher Bereiche:
PGA
Session AREA
   - Session info for each user
   - Session Variables, login info, session status, etc.
Unnötige verbindungen vermeiden

PSA(Private SQL Area)
PERSISTENT AREA
  - Jede Abfrage wird zu einem Cursor
  - Verwendete Bindvariablen der Cursor werden hier gespeichert
RUNTIME AREA
  - Execution state info
CURSOR AREA
- Information of cursors

SQL WORK AREA
SORT AREA
HASH JOIN AREA
BITMAP MERGE AREA
BITMAP CREATE AREA

Weniger Speicher verringert die LEistung

Speichergröße festlegen
- User
- Oracle selber

SGA | SHARED/SYSTEM GLOBAL AREA

Database buffer cache
Redo log buffer
Shared pool
 - Data dictionary cache
 - Result Cache
 - other
Large pool
- I/O Buffer
- Free memory
- Response queue
- Request queue
JAVA pool
Streams pool

Context switching between pl/sql engine and sql engine

SHARED POOL

DATA DICTIONARY CACHE
- Stores the definitions of the database object and permissions
  - check if Data from query exists
  - check your privileges

RESULT CACHE
- stores result of common used queries
- stores result of functin
  - Bei wiederholter Abfrage wird aus dem RESULT CACHE gelesen
  - Speicher die Abfrageergebnisse
  - Speicher die Funktionsergebnisse

Erhöht die Leistung
Wird von der Datenbank durchgeführt
User kann anweisen Result Cache anweisen daten zu speichern

Library Cache
- Stores the execution plans
- Stores procedures, packages control structures
  Oracle erstellt Ausführungspläne
  Wie die Daten aus der Disc oder dem Puffer gelesen werden
  Die Erstellung eines Ausführungsplans ist ein kostspieliger Vorgang
  Die Verwendung eines Ausführungsplans für ähnliche Abfragen kann sehr effizient sein

OTHER AREAS

BUFFER CACHE

Largest memory area of sga
Stores the copies of the blocks read from the disc
Available for all the users
First check Buffer than Disc, for the data
Check Disc only if blocks are missing
Much faster than disc
Size of the memory is endless
Maintained with a complex algorithm
Can't save all data in buffer cache

IN-MEMORY-DATABASES
Stores the most recently used & most toched ones
Database writer process handles the write operations to the disc
Writes not one by one, writes all changed blocks in one step
dirty blocks
Stores Tabledata and Indexdata

Performance for same
- queries
- tables
- indexes

REDO LOG BUFFER

-> Log Writer Process(LGWR)
-> Redo Log Entries
-> Redo Log Files
-> Server Process 

Redo-Log-Files
Oracle Guarantees Not to Lose Data
- A Redo Log Entry is created when insert, update, delete, create, alter, drop occurs
Create Redo Log entries for all changes
Redo Log Entries has the changes made to the database
They are used for recovery operatins
You need privileges as a developer
Redo Log Entries are Stored in the redo log buffer
Starts every 3 sec and write to disc
Redo Log buffer is a circular buffer
Rollback is not done with redo log data
Redo do recovery on rollback redo log files are deleted

UNDO

What is rollback and recovery
The original data stored into the memory(undo tablespace) is called as undo data
buffer cache for the modifications
undo data is not modified because
  - Used for rollback operations
  - Used for providing read consistency
  - Used for providing flashback feature

BLOCKS > EXTENTS > SEGMENTS > TABLESPACE

DML PROCESS AND COMMIT

Optimize Queries and DML's
- Checks the Shared SQL AREA for similar statements to use
- checks the data dictionary cache and checks if query is valid
- Checks buffer cache & undo segements for the related data
- Locks the related blocks
- Makes the changes to the blocks in the buffer cache
- the changes are applied to the redo log buffer before the buffer cache
- the server returns the feedback for the changes

When the user commits:
- System change number write to redo log files
- the server creates a commitrecord with scn
- the lgwr process write redo log entries in the redo log buffer to the redo log files
- the dbwn writes the dirty blocks to the disc & unlocks the blocks
- The server returns a feedback about the transaction completion

Automatic memory management

The size of each memory ara is important for the execution performance of your queries
Wenn des Speicherbereich nicht ausreicht verringert das die performance
It can manage both SGA & PGA memory 
In früheren versionen von oracle musste PGA manuell angegeben werden
it is recommended to leave automatic memory management enabled to increase the performance
will prefent out of memory errors

Oracle database storage architecture

- Storage = Discs
- Control files storage the pyhsical structure information of the database(without no access to data)
- Data Files - Stores data(Tables, procedures, application, data
- Online Redo log files: stores redo log entries
- archived redo log files - online redo log files are constantly mover ehre
- backup files - Stores the exact copy of the data files for disaster recovery
- Parameter files - Stores the configuration data of the database instance
- password file - Stores the passwords of the amin user(sysdba,sysoper,sysasm)
- alert log & trace files - stores log message and errors occured in the database

Some Informations are for the dev's and some for the oracle support service

Logical and Physical Database structure

- Blocks | smallest units of storage(2kb-32kb)
- Extents | combination of several consecutive data blocks.
  used for stroing secific type of info
- segments | combination of several extents
 used for storing some big data(tables,indexes, etc)
  - data segments
  - index segments
  - undo segments
  - temporary segments
- tablespaces | combination of many segments. used for grouping the related data in one container
 - Temporary Tabelspace
   Stores teh temporary data of a session
 - Permanent Tablespace
   Stores the persistent schema objects

DB must have min 2 Tablespaces
- System Tablespace
- SYSAUX Tablespace
DBA can create more Tablespaces

Check for Performance
Set Tablespaces Online and Offline Status
Can do backup or recovery of a give tablespace
Import or export Tablespaces
Create a transportable tablespace and move too other db

Schema

Ein Schema ist eine Sammlung von Datenbankobjekten die einem Datenbankbenutzer gehören

PERFORMANCE TUNING BASICS

SQL Tuning is a continuous process
You nedd to tune your queries
 - on creating
 - After the creation
 - On new Indexes created
 - Change of data volume

When to decide tuning?
 - checking the top consuming queries frequently
 - after any complaints of bad performance

two goals in tuning:
- reduce user response time, decreasing time between statement and response
- Improve throughput

Knowledge and skills:
- know your db architecture
- sql
- sql tuning tools

Check oracle database statistics

Betriebssystem
- Software
- Services
- Updates
- Datenbank, Oracle Datenbank Architektur

Häufige Ursache für Performance Verlust, bei Änderung von:
- Tabellen, DB Struktur
- Datenvolumen
- Applikationen
- Datenbank-Update
- Datenbank-Parameter
- Betriebssystem
- Betriebssystem-Update
- Betriebssystem-Konfiguration
- Hardware

Welche Oracle Packages für:
- Modularity
- Easy Maintenance
- Encapsulation & Security
- Performance
- Functionality
- Overloading

Tuning-Maßnahmen | SQL & PERFORMANCE TUNING
- SQL Tuning Basics
- Execution Plans in Details
- Join Operations
- Basic Tuning Techniques
- Advanced Indexing Techniques
- Hints
- Subjects

BAD SQL

Unnecessary:
- parse time
- I/O operations
- CPU Time
- waits

TIME on Wait(CPU) + Time on Execution = DB TIME
The reason of a bad sql
- bad design
- poor coding
- inefficient execution plan

Effective Schema Design

- Assign data types as much as needed
- datatypes with variable-length
- check data-consistency problems
- select exactly the same data type between parent-child-keys
- don't use varchar2 for the most of the datatypes/strings/values

Enforce data integrity
- correct PK and FK
- use normalization well
- smaller tables, faster join operation
- select right table type
  - heap-organized table
  - Index Clustered tables
  - Hash Clustered tables
  - Index-Organized tables
  - External tables
  - and more
- Create Clusters
- use indexes often and select index type carefully
- create good indexes methods
- Create index-organized tables(IOT)

table partitioning

SQL STATEMENT PROCESSED

-> Syntax check
-> Semantic check
-> Privilege check
-> Allocate private SQL AREA
Existing shared SQL AREA
NO-> HARDPARSE(Library cache miss)
 -> Allocate shared sql area
 -> optimization
 -> row source generation
YES-> SOFTPARSE
-> EXECUTE STATEMENT

Softparse is faster, use for tuning
- Hashwert in Libcache
 - Key for the executionplan in shared sql area
- executionplan is stored in shared library

Optimierer ist Software die Abfrage als Eingabe erhält und den besten ExecutionPlan erstellt

Full-table scan
Index scan

1. Optimization
2. Execution plans
3. Row Source Generation

Result-Cache

Select * from... STEPS
-> Check Schema Information
-> Find possible access paths
  -> Use Index
  -> Read whole table
-> check statistics

selective query

Don't use Index if Result is bigger than 25% of the whole table?

Access Methods

- Table Access Path
  - Full-Table scan
  - Table Access by ROWID
- B-Tree Indexx Access Path
  - Index unique scan
  - Index range scan
  - Index skip scan
  - Full Index scan
- Bitmap index Access Path
- Table Cluster Access Path

In-List- Iterator

Optimizer overview

- Optimizer
- RBO | RuleBasedO(not in use anymore since 10g)
- CBO | CostBaseO
  Plan is dynamic
-> Query Transformer(Transform Query)
-> Estimator(Query+estimates)
^-> Plan Generator(Query plan to row source generation)

Query Transformer

Query transforms the query into a better performing one but semantically equivalent of it
If the transform isn't better, it use the original one
Time restriction and old statistics may lead a wrong plan creation
Or Expansion
 Using or in the where clause will prevent index usage
 
SELECT * FROM ID WHERE ID = 1 OR ID = 2;
Faster:
SELECT * FROM ID WHERE ID = 1;
UNION ALL
SELECT * FROM ID WHERE ID = 2;

Subquery Unnesting
Verschachtelte Abfrage in Join-Anweisung
SELECT * FROM ID WHERE ID IN
   (SELECT ID FROM CLIENTS)
The otimizer transfomrs a nested query into a join statement
SELECT ID,*
FROM ID, CLIENTS
WHERE ID.ID = CLIENTS.ID;

Complex VIEW

Selectivity & Cardinality

Selectivity = NUMBER OF ROWS FROM QUERY / TOTAL NUMBERS OF THE ROWS
Cardinality = Total numbers of rows x Selectivity

Why selectivity and cardinality is important?
- SEL affects the estimates in I/O cost
- SEL affects the sort cost
- CAR is used to determine join, sort and filter costs
- Incorrect SEL and CAR = incorrect plan cost estimation

COST IN DETAIL

- Cost is the optimizer's best estimate of the numbers of I/O to execute statement
- To estimate the cost, the estimator uses:
 - Disk I/O
 - CPU usage
 - Memory usage

COST = SINGLE-BLOCK I/O COST + MULTI-BLOCK I/O COST + CPU COST / SINGLE-BLOCK READ TIME
- SINGEL BLOCK READ TIME
- MULTI BLOCK READ TIME
- CPU CYCLES / CPU SPEED

PLAN GENERATOR

Basierend auf ausprobieren von verschiedenen Zugriffspfaden,JOIN-Methoden und JOIN-Reihenfolgen

ROW SOURCE GENERATOR

once the plan generator generates the optimum plan, it handles that to the row source generator
generates an interative execution plan usabel for the database
is an area that we get the row set(Table,view,result of join or groups)
row source tree show the following information:
- execution order
- access methods
- join methods
- data operatons(filter,sort,...)

SQL TUNING PRINICPLES AND STRATEGIES

SQL TUNING PRINCIPALS
- IDENTIFYING the issue
- carify teh details of that issue
- collection data
- analyze the data
- choose an appropriate tuning strategy

SQL TUNING STRATEGIES
- Parse time reduction
- plan comparison strategy
- quick solution strategy
- finding & implementing a good plan
- Query analysis strategy

SQL TUNING ADVISOR(braucht zugriffsrechte)
otimierungsmodus ändern
index-caching
etc..

Use dynamic stats to create better plans

QUERY ANALYSIS STRATEGIE

- quick tuning strategies did not work, and we have time to work on this problem
- query can be modified
- determine the underlying cause

What to do on this strategy
- statistics and parameters
- query structure
- access paths
- join orders & join methods(changes?)
- others

Collecting Data
- Execution plan
- information of the objects in the query
- collecting statistics
  - object stats
  - systems stats
- histograms
The available tools are
- sqlt
- dbms_stats
- tkprof
- awr report
- etc..

Systemstatistiken und Histogramme

PRE-ANALYZE of the Query
- check the
  - volumes of resulting data
  - predicates
  - problematic constructs
    - OUTER JOIN
    - VIEWS
    - SUBQUERIES
    - IN or OR list
    - Hierarchical queries

Analyzing the execution plan
Tools to get the execution plan
  - SQL TRACE
  - TKPROF
  - V$_SQL_PLAN
  - DBMS_MONITOR
  - AWRSQRPT.SQL
  - etc..

How to read the execution plan:
- check the access paths
- check the join order and the join type
- compare actual & estimated number of rows
- find the steps where cost and logical reads are different

Analyzing by considering the query tuning techniques

Find a possible solution
- updating statitics
- using dynamic stats
- creating or re-create an index
- creating index-organized tables(IOT)
- using hints
- others

EXECUTION PLAN AND EXPLAIN IN DETAIL

An execution plan is a list of steps how to execute an sql statement

- Operations
- Object_name
- Cardinality
- Cost
- Partition_Start
- Partition_Stop

Stat types
- system stats
- optimizer stats
System stats
 - used by the optimizer to estimate I/O and CPU
 - costs
 - should be generated regularly
- shoud be gathered during a normal workload

regenaration/recreate on every hardware change the system stats

Access Predicates
Filter Preddiactes

USE OF:
EXEC dbms_stats.gather_system_stats('Start');
Prozedur
collect_system_stats()

Berechtigung auf Tabelle aux_stats$

Optimizer Statitics
- can be gathered manually or autmatically

Dynamic stats?

Job für Automatische Erfassung der Statistiken

ANALYZE TABLE [TABLE_NAME] COMPUTE STATISTICS;
Sollte nicht mehr genutzt werden

DBMS_STATS package
PROCEDURES:
GATHER_DATABASE_STATS 
GATHER_DICTIONARY_STATS
GATHER_SCHEMA_STATS
GATHER_TABLE_STATS | very fast
GATHER_INDEX_STATS | faster for singel indexes

How can see the optimizer statistics
- DBA_TABLES
- DBA_TAB_STATISTICS
- DBA_COL_STATISTICS
- DBA_INDEXES
- DBA_CLUSTERS
- DBA_TAB_PARTITIONS
- DBA_IND_PARTITIONS
- DBA_PART_COL_STATISTICS

Generating Execution plan

To analyze an execution plan
  - explain plan
  - autotrace
  - V$SQL_PLAN
EXPLAIN PLAN
EXPLAN PLAN FOR [QUERY];
generates the explain plan and save into plan_table

select * from plan_table;

AUTOTRACE

SET AUTOTRACE ON;
SET AUTOTRACE ON [EXPLAIN|STATISTICS];
SET AUTOTRACE TRACE[ONLY] ON [EXPLAIN|STATISTICS];
SET AUTOTRACE OFF;

Autotrace uses plan table like the explain plan
Bindvariablen mit implizierter typenkonvertierung, klappt manchmal nicht

SQLPLUS>> 
SQL> set linesize 200;
SQL> set autotrace traceonly explain;
SQL> select * from sales s, customers c where s.cust_id = c.cust_id and s.cust_id = 987;
SQL> set autotrace traceonly statistics;
SQL> set autotrace traceonly statistics;
SQL> set autotrace traceonly;
SQL> set autotrace on;
SQL> show autotrace;

AUTOTRACE WITH SQL DEVELOPER

Kann man den Code nicht durch generieren des explain-plans optimieren, nutzt man autotrace-methoden
Bei DML-OPS zuerst den EXPLAIN-PLAN

V$SQL_PLAN VIEW

There are a lot of performance views tha can be used for tuning
- V$SQLAREA
- V$SQL_WORKAREA
- V$SQL
- V$SQL_PLAN
- V$SQL_PLAN_STATISTICS
- V$SQL_PLAN_STATISTICS_ALL

V$SQL_PLAN
- Actaul execution plans are stored here
- It is very similar to plan_table
Wenn es eine Unterschied zwischen beiden gibt, gilt der Wert von V$SQL_PLAN als wahr
- V$SQL view
Sie können beide die Tabellenfunktin und das dbms_xplan Paket aufrufen für eine gute Ausgabe

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('XXXXX'));

READING THE EXECUTION PLANS

Zugriffsprädikate werden für die Zeilenquellen verwendet
Bitmap-KOnvertierung in ROWIDS die ROWIDS aus der eingehenden Quelle
Tabellen Zugrif durch lokale Index-ROWID
Oft werden 0 Costs angezeigt, welche aber nicht 0 sind sondern einfach nur zu gering

2 Arten von Optimierung
- kostenbasierte | COST-BASED-OPTIMIZATION (Empfehlung von Oracle)
- regelbasierte  | RULE-BASED-OPTIMIZATION (Alte Art, nicht so genau)

ALL_ROWS
ANALYZED

first_rows & .... und später den rest, schnellere Reaktionszeit

Parameter type 	String
Syntax 	OPTIMIZER_MODE =

{ first_rows_[1 | 10 | 100 | 1000] | first_rows | all_rows }
Default value 	all_rows
Modifiable 	ALTER SESSION, ALTER SYSTEM

OPTIMIZER_MODE

Detailierte Tuning Strategie vs schnelle Tuning Strategie

Where to look?
- Cost
- Access Methods
- Cardinality
- JOIN Methods & JOIN TYPES
- Partition Pruning
- Others
##??
- Parse count
- Number of fetches
- etc..

Optimizer hints

- To command the optimizer, we use optimizer hints
- optimizer hints force the optimizer to pick a specific action
- The optimizer may not follow your hints
- If hint is not reasonable, the optimizer will ignore it
- Hints can be operating on a single hash, multi-tables, a query block, a specific statement

Categories of the hints:
- Hints for optimization approaches
- Access paths hints
- Query Transformation Hints
- Join order Hints
- Parallel Execution hint
- Others..

SELECT /*+ hint_name(para1 para2 para3) */ id from db;
- hints can used after a select, update or delete keywords
- you can use the table name or its alias as the hint parameter. but if there is an alias, you cannot use the table name
- There can be only one hint area
- be careful on the hints you selected, especially if you are using multiple hints
  you may lead the optimizer to a bad execution plan

Gathering stats

optimizer statistics are crucialto sql tuning

Search for * in execution plan

- cardinality
- Access method
- Join method
- Join type
- join order
- Partition pruning
- parallel execution
Abgerufen von „http://robopagex.com/index.php?title=Oracle&oldid=1039“