Transaction Rollback Monitoring

V$FAST_START_TRANSACTIONS displays information about the progress of the transactions that Oracle is recovering.

alter session set nls_date_format='dd/mm/yyyy hh24:mi:ss';

select usn, state, undoblockstotal "Total", undoblocksdone||' ('||round(UNDOBLOCKSDONE/UNDOBLOCKSTOTAL*100,2)||'%)' "Done",
 undoblockstotal-undoblocksdone "ToDo",
         decode(cputime,0,'unknown',sysdate+(((undoblockstotal-undoblocksdone) / (undoblocksdone / cputime)) / 86400))||' ('|| round((undoblockstotal-undoblocksdone)/UNDOBLOCKSDONE*CPUTIME/3600,2)||'h)' "Estimated time to complete"
  from v$fast_start_transactions;

USN	STATE	Total	Done	ToDo	Estimated time to complete
63	RECOVERING	349526	194208 (55,56%)	155318	27/07/2011 16:34:04 (20,25h)
86	RECOVERING	309253	194095 (62,76%)	115158	27/07/2011 11:20:29 (15,02h)

Database Hangs Because SMON is taking 100% CPU doing transaction recovery [ID 414242.1]
How to Disable Parallel Transaction Recovery When Parallel Txn Recovery is Active [ID 238507.1]
Rollback Is Taking A Long Time After Session Killed [ID 1060831.6]
How Long Will My Rollback Take? Is my Transaction Doing Anything? [ID 169138.1]

SQL Tuning Sets

A SQL Tuning Set (STS) is a database object that includes one or more SQL statements along with their execution statistics and execution context, and could include a user priority ranking. The SQL statements can be loaded into a SQL Tuning Set from different SQL sources, such as the Automatic Workload Repository, the cursor cache, or custom SQL provided by the user.

An STS includes:

• A set of SQL statements

• Associated execution context, such as user schema, application module name and action, list of bind values, and the cursor compilation environment

• Associated basic execution statistics, such as elapsed time, CPU time, buffer gets, disk reads, rows processed, cursor fetches, the number of executions, the number of complete executions, optimizer cost, and the command type

• Associated execution plans and row source statistics for each SQL statement (optional)

A SQL Tuning Set can be used as input to the SQL Tuning Advisor, which performs automatic tuning of the SQL statements based on other input parameters specified by the user. SQL Tuning Sets are transportable across databases and can be exported from one system to another, allowing for the transfer of SQL workloads between databases for remote performance diagnostics and tuning. To transport SQL Tuning Sets, use the DBMS_SQLTUNE package procedures.

(Performance Tuning Guide - Ch12.9)

Choosing an Access Path

To choose an access path, the optimizer first determines which access paths are available by examining the conditions in the statement's WHERE clause and its FROM clause. The optimizer then generates a set of possible execution plans using available access paths and estimates the cost of each plan, using the statistics for the index, columns, and tables accessible to the statement. Finally, the optimizer chooses the execution plan with the lowest estimated cost.

When choosing an access path, the optimizer is influenced by the following:

• Optimizer hints: The optimizer's choice among available access paths can be overridden with hints. (Using hints requires changing the code)

• Statistics: For example, if a table has not been analyzed since it was created, and the table statistics show that it is small, then the optimizer uses a full table scan. The LAST_ANALYZED and BLOCKS columns in the ALL_TABLES table reflect the statistics used by the optimizer.

• SQL outlines, SQL baseline plans, and SQL profiles: If these exist they will override the optimizer choices. For more information see the SQL Performance Management lesson.

(Oracle Database 11g: Performance Tuning Ch10.17)

Hierarchical Queries

Come ottenere un result set utilizzando query gerarichiche.

SELECT LPAD(' ', LEVEL*2)||columnX
FROM table
WHERE condition(s)
START WITH condition(s)
CONNECT BY PRIOR column1 = column2
ORDER SIBLINGS BY columnX;

Ci sono due modi di ricevere i dati (il top-down coincide con la visita in ordine anticipato di un albero):

=======
Top down
=======

column1 = Parent Key
column2 = Child Key

=======
Bottom up
=======

column1 = Child Key
column2 = Parent Key

The direction of the query, whether it is from parent to child or from child to parent, is determined by the CONNECT BY PRIOR column placement. The PRIOR operator refers to the parent row. To find the child rows of a parent row, the Oracle server evaluates the PRIOR expression for the parent row and the other expressions for each row in the table. Rows for which the condition is true are the child rows of the parent. The Oracle server always selects child rows by evaluating the CONNECT BY condition with respect to a current parent row.

(11g SQL Fundamentals II - App F.7)

You have to be careful when trying to order the rows of output in a hierarchical query. By default, if you omit the ORDER BY clause altogether, the query attempts to sort rows in an order that’s reflective of the hierarchy. Display will start first with a LEVEL 1 row. If that row is superior to any LEVEL 2 rows, those rows will display next before another LEVEL 1 row displays. The same approach is taken at LEVEL 2, so that rows will display down to leaf node levels before the next rows show at the higher levels. The result is a display that is meaningful to the hierarchy. But if you try to order these rows with the ORDER BY clause, you’ll create a syntactically correct statement that probably doesn’t help you much. ORDER SIBLINGS sorts rows within each given level, not across levels, thus retaining the hierarchical relationship across rows of output. Note that our earlier use of ORDER BY did not create an error message, just misleading output

(OCA Oracle Database SQL Certified Expert Exam Guide)

Per esempio, il top-down:

SELECT LPAD(' ', LEVEL*2)||last_name
FROM employees
START WITH last_name = 'King'
CONNECT BY PRIOR employee_id = manager_id
ORDER SIBLINGS BY last_name;

Userò il concetto di query gerarchiche in due casi:

1. lock
2. parallel query

Modify dos windows colours

Questi i parametri della finestra dos utilizzati da Power Shell

Layout: Dimensioni buffer dello schermo

Larghezza	120
Altezza	3000

Layout: Dimensioni finestra

Larghezza	120
Altezza	52

Layout: Posizione finestra

Sinistra	201
Alto	18

Colori: Testo normale

Rosso	255
Verde	255
Blu	255

Colori: Sfondo normale

Rosso	1
Verde	36
Blu	86

Colori: Testo popup

Rosso	128
Verde	0
Blu	128

Colori: Sfondo popoup

Rosso	255
Verde	255
Blu	255

How to change language of SQL Developer

Nella direcotry "bin" di SQL Developer, aprire il file "sqldeveloper.conf" ed aggiungere o modificare le seguenti due righe:

AddVMOption -Duser.language=en
AddVMOption -Duser.country=US

Una volta fatto, chiudere e riavviare l'applicazione.

Questo il link originale: How to change the language of SQL Developer's user interface?

Di seguito due immagini utili a mostrare le modifiche da fare.

Create new user and assign grant

[nz@dwhtwf01 ~]$ nzsql
Welcome to nzsql, the Netezza SQL interactive terminal.

Type:  \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

SYSTEM(ADMIN)=> CREATE USER sarusso WITH PASSWORD 'please_change_me';
CREATE USER

SYSTEM(ADMIN)=> ALTER GROUP group_dev ADD USER sarusso;
ALTER GROUP

SYSTEM(ADMIN)=> ALTER GROUP group_analyst ADD USER sarusso;
ALTER GROUP



SYSTEM(ADMIN)=> \c NTZ_DWDMP
You are now connected to database NTZ_DWDMP.

NTZ_DWDMP(ADMIN)=> GRANT LIST ON TABLE TO sarusso;
GRANT

NTZ_DWDMP(ADMIN)=> GRANT SELECT ON TABLE TO sarusso;
GRANT



NTZ_DWDMP(ADMIN)=> \c NTZ_DWDMS
You are now connected to database NTZ_DWDMS.

NTZ_DWDMS(ADMIN)=> GRANT LIST ON TABLE TO sarusso;
GRANT

NTZ_DWDMS(ADMIN)=> GRANT SELECT ON TABLE TO sarusso;
GRANT



NTZ_DWDMS(ADMIN)=> \c NTZ_DWSAS
You are now connected to database NTZ_DWSAS.

NTZ_DWSAS(ADMIN)=> GRANT LIST ON TABLE TO sarusso;
GRANT

NTZ_DWSAS(ADMIN)=>  GRANT SELECT ON TABLE TO sarusso;
GRANT

NTZ_DWSAS(ADMIN)=> \q