Tuning

1. Tuning

2. SQL Trace Facility • Sono in grado di fornire informazioni preziose, per ogni statement SQL • chiamato in causa, generando le seguenti statistiche attive: • a livello di sessione (alter session set sql_trace = TRUE) • a livello di intera istanza (parametro di configurazione del file initSID.ora • sql_trace=true) • Numero di parse, execute e fetch • Tempo di CPU e tempo di elapsed (trascorso) • Numero di letture logiche e letture fisiche • Numero di record processati • archiviate, in formato interno, in un file denominato trace file

3. Formatting the Trace Filewith TKPROF $ tkprof tracefile.trc output.txt [options] tracefile.trc output.txt USER_DUMP_DEST

4. Al fine di prospettare le informazione storicizzate nel file di trace sopra • descritto, occorre dare in pasto lo stesso archivio ad un formattatore • denominato TKPROF la cui sintassi di avvio risulta essere: • TKPROF • file_trace_input • file_output • SORT=(option1,option2,……..)  EXECPU , EXEELA, ………. • PRINT=integer • Lists only the first integer sorted SQL statements into the output file. • INSERT=file_scripts_sql_output • Creates a SQL script that stores the trace file statistics in the database. This script creates a table and inserts a row of statistics for each traced SQL statement. • SYS=boolean • Enables and disables the listing of SQL statements issued by the user SYS. • TABLE=schema.table • Specifies the schema and name of the table into which TKPROF temporarily places • execution plans before writing them to the output file. • EXPLAIN=user/password • Determines the execution plan for each SQL statement in the trace file and writes these execution plans to the output file. • RECORD= file_record_output • Creates a SQL script with the specified filename with all of the nonrecursive SQL in • the trace file.

5. Example This example runs TKPROF, accepts a trace file named "dlsun12_jane_fg_svrmgr_007.trc", and writes a formatted output file named "outputa.prf": TKPROF DLSUN12_JANE_FG_SVRMGR_007.TRC OUTPUTA.PRF EXPLAIN=SCOTT/TIGER TABLE=SCOTT.TEMP_PLAN_TABLE_A INSERT=STOREA.SQL SYS=NO SORT=(EXECPU,FCHCPU) • Note the other parameters in this example: • The EXPLAIN value causes TKPROF to connect as the user SCOTT and use the • EXPLAIN PLAN statement to generate the execution plan for each traced SQL • statement. • The TABLE value causes TKPROF to use the table TEMP_PLAN_TABLE_A in • the schema SCOTT as a temporary plan table. • The INSERT value causes TKPROF to generate a SQL script named • STOREA.SQL that stores statistics for all traced SQL statements in the database. • The SYS parameter with the value of NO causes TKPROF to omit recursive SQL • statements from the output file. In this way you can ignore internal Oracle • statements such as temporary table operations. • The SORT value causes TKPROF to sort the SQL statements in order of the sum • of the CPU time spent executing and the CPU time spent fetching rows before • writing them to the output file.

7. Tabular Statistics • TKPROF lists the statistics for a SQL statement returned by the SQL trace facility in • rows and columns. Each row corresponds to one of three steps of SQL statement • processing. • PARSE • This step translates the SQL statement into an execution plan. • This step includes checks for proper security authorization and checks for the • existence of tables, columns, and other referenced objects. • EXECUTE • This step is the actual execution of the statement by Oracle. • For INSERT, UPDATE, and DELETE statements, this step modifies the data. • For SELECT statements, the step identifies the selected rows. • FETCH • This step retrieves rows returned by a query. Fetches are only performed • for SELECT statements.

8. TKPROF Statistics • COUNT Number of times a statement was parsed, executed, or fetched. • CPU Total CPU time in seconds for all parse, execute, or fetch calls for the statement. • ELAPSED Total elapsed time in seconds for all parse, execute, or fetch calls for the statement. • DISK Total number of data blocks physically read from the datafiles on disk for all parse, execute, or fetch calls. • QUERY Total number of buffers retrieved in consistent mode for all parse, execute, or fetch calls. Buffers are retrieved in consistent mode for queries. • CURRENT Total number of buffers retrieved in current mode. Buffers are retrieved in current mode for statements such as INSERT, UPDATE, and DELETE. • ROWS Total number of rows processed by the SQL statement. This total does not include rows processed by subqueries of the SQL statement.

9. Dynamic Performance Views 1) Are maintained by the Oracle server and continuously updated 2) Contain data about disk and memory structures 3) Contain data that is useful for performance tuning 4) Have public synonyms with the prefix V$ Accessing Dynamic Performance Views OPEN Data dictionary MOUNT Dynamic performance views reading data from disk NOMOUNT Dynamic performance views reading from memory SHUTDOWN

10. Example V$ V$PARAMETERV$SGAV$OPTIONV$PROCESSV$SESSION V$VERSIONV$INSTANCE SGA V$THREADV$CONTROLFILE V$DATABASEV$DATAFILEV$DATAFILE_HEADER V$LOGFILE Control file

11. V$SESSTAT V$SYSSTAT V$STATNAME

12. Automated Performance Tuning System • La metodica che si pone alla base della strategia è architettata su quattro step • distinti: • 1 - Individuazione degli aspetti del Data Base che si desidera porre sotto analisi, • definizione delle regole e rintraccio dei valori limite. • 2 - Collecting data. Acquisizione delle informazioni legate a: • database • instance • schema • environment • workload (carico di lavoro). • 3 - Viewing dei collected data. Prospetto, tramite reportistiche semplici e di immediata • comprensione, dei dati precedentemente raccolti. • 4 - Analyzing data / generate recommendations. • Nel caso in cui i valori limite non siano soddisfatti ecco il sistema intervenire • con una serie di utili consigli se non addirittura con un insieme di risoluzioni • automatiche poste in essere. • Sono controlli che rientrano sotto il nome di "Routine Tuning" da considerarsi come • "prevenzione" essendo gli stessi un help per anticipare quelli che potrebbero divenire • reali problemi prima chegli stessi si presentino con la propria complessita' risolutiva.

13. Controllo 1-%Library Cache Misses < 1% select round(sum(reloads)/sum(pins)*100,2) Col1 from v$librarycache; > shared_pool_size Controllo 2 -%Data Dictionary Cache Misses < 10% select round(sum(getmisses)/sum(gets)*100,2) Col1 from v$rowcache; > shared_pool_size

14. Controllo 3 -Ratio Logico/Fisico Critico > 80% select round(((1-(a.value/(b.value+c.value)))*100),2) Col1 from v$sysstat a, v$sysstat b, v$sysstat c where a.name = 'physical reads' -- accessi fisici and b.name = 'db block gets' -- accessi logici and c.name = 'consistent gets'; -- accessi logici > db_block_buffer

15. Controllo 4 -Analisi Aree di Sort Parallelamente all'area condivisa di ram (SGA), esistono un insieme di aree dimensionalmente ridotte, non condivise ed in relazione 1:1 con i processi dedicati al supporto delle connessioni utente (PGA) la cui funzionalita' principale e' rintracciabile nel supporto alle politiche di sort e di grouping. Ogni statement che richiama operazioni quali sort e grouping, sfrutta per il raggiungimento dell'obiettivo l'area PGA. Nel caso in cui la stessa risulti non sufficiente per accomodare l'attività, quest'ultima migra sui segmenti temporanei opportunamente creati su disco, con un degrado significativo dei tempi di esecuzione. Non esiste un limite da utilizzarsi come confronto. Viene demandata alla sensibilità del DBA, la decisione su di una rianalisi dei parametri di initSID.ora: sort_area_retained_size dimensione in bytes allocata nella PGA per potenziali SORT sort_area_size dimensione in bytes allocabile nella PGA per sicuri SORT. Sono individuate due soglie (min & max) per cercare di risolvere il maggior numero di attivita' integralmente in ram.

16. Controllo 4 -Analisi Aree di Sort select name, value from v$sysstat where name in ('sorts (memory)','sorts (disk)');

17. Controllo 5 -Analisi Oggetti con + 25 extents Il controllo prosegue concentrandosi sugli oggetti (segmenti) con un livello di criticità, un livello di frammentazione (numero di extents associati) elevato e potenzialmente colpevolizzabile di un peggioramento delle performances del sistema. select owner, segment_name, segment_type, tablespace_name, extents from dba_segments where extents > 25 and owner not in ('SYS') order by owner,segment_type, extents desc;

18. Auditing Guidelines • Define your purpose of auditing • Suspicious database activity • Gather historical information • Define what you want to audit • Audit users, statements, or objects • By session not by access • Successful or unsuccessful • Manage your audit trail • Monitor the growth of the audit trail • Protect the audit trail from unauthorized access

19. Auditing Categories • Auditing privileged operations • Always audited • Startup, shutdown, and SYSDBA connections • Database auditing • Enabled by DBA • Cannot record column values • Value-based or application auditing • Implemented through code • Can record column values • Used to track changes to tables

20. Execute command Enable database auditing User DBA Parameter file Serverprocess Specify audit options Review audit information Generate audit trail Audit options OS audit trail Audittrail Database Auditing Database

21. Enabling Auditing Options AUDIT user; • Statement auditing • Privilege auditing • Schema object auditing AUDIT select any table BY summit BY ACCESS; AUDIT LOCK ON summit.employeeBY ACCESS WHENEVER SUCCESSFUL;

22. Viewing Auditing Options Data Dictionary View ALL_DEF_AUDIT_OPTS DBA_STMT_AUDIT_OPTS DBA_PRIV_AUDIT_OPTS DBA_OBJ_AUDIT_OPTS Description Default audit options Statement auditing options Privilege auditing options Schema object auditing options

23. Viewing Auditing Results Audit Trail View DBA_AUDIT_TRAIL DBA_AUDIT_EXISTS DBA_AUDIT_OBJECT DBA_AUDIT_SESSION DBA_AUDIT_STATEMENT Description All audit trail entries Records for AUDIT EXISTS/NOT EXISTS Records concerning schema objects All connect and disconnect entries Statement auditing records

24. Autonomous Transactions • An independent transaction started by another transaction • Independent of the main transaction; not nested transactions • Do not roll back if the main transaction rolls back. • Changes become visible to other transactions upon a commit. • Only individual routines can be marked autonomous. • You cannot mark a nested PL/SQL block as autonomous.

25. PROCEDURE proc2 IS PRAGMA AUTONOMOUS_TRANSACTION; dept_id NUMBER; BEGIN dept_id := 90; UPDATE ... INSERT ... UPDATE ... COMMIT; END proc2; PRAGMA AUTONOMOUS_TRANSACTION; MT begins MT suspends AT begins MT ends AT ends MT resumes Autonomous Transactions MT = Main Transaction AT = Autonomous Transaction PROCEDURE proc1 IS emp_id NUMBER; BEGIN emp_id := 1234; COMMIT; INSERT ... SELECT ... proc2; DELETE COMMIT; END proc1;

26. PRAGMA AUTONOMOUS_TRANSACTION; Autonomous Transactions Example PROCEDURE bank_trans ... log_card_usage (cardnum, loc); INSERT INTO txn VALUES (9001,1000,...); END bank_trans; PROCEDURE log_card_usage (p_cardno IN NUMBER, p_loc IN NUMBER ) IS PRAGMA AUTONOMOUS_TRANSACTION; BEGIN INSERT INTO usage VALUES (p_cardno, p_loc); COMMIT; END log_card_usage;

27. Oracle Supplied Packages There are more than 70 packages supplied with the Oracle RDBMS that are granted to all users (PUBLIC). They fall into three categories: • Application development support • Server management support • Distributed database packages

28. Interacting with Operating System Files • utl_file Oracle supplied package • Provides text file input/output capabilities • Is available with version 7.3 and later

29. What Is the utl_file Package? • Extends I/O to text files within PL/SQL • Provides security for directories on the server through the init.ora file • Is similar to standard operating system I/O • Open files • Get text • Put text • Close files • Use the exceptions specific to the utl_file package

30. Yes More lines to process? No File Processing Using utl_file Get linesfrom thetext file Open the text file Close the text file Put linesinto thetext file

31. utl_file Procedures and Functions • Function fopen • Function is_open • Procedure get_line • Procedure put_line • Procedure new_line • Procedure fflush • Procedure fclose, fclose_all

33. procedure stampa_testata_file (xpath in varchar2, xfile in varchar2) is fileout UTL_FILE.file_type; begin fileout := UTL_FILE.fopen(xpath,xfile,'w'); UTL_FILE.put_line(fileout, '=====================================>>>> '); UTL_FILE.put_line(fileout, '==== Demone Remoto di Controllo RDBMS ===>>>> '); UTL_FILE.put_line(fileout, '==== ===>>>> '); UTL_FILE.put_line(fileout, '=====================================>>>> '); UTL_FILE.put_line(fileout, '====SysDate ====== '||to_char(sysdate , 'dd/mm/yyyy hh24:mi:ss')||' =============>>>> '); UTL_FILE.put_line(fileout, '=====================================>>>> '); UTL_FILE.fclose (fileout); end; /

34. Java class method C routine External procedure Calling External Routines from PL/SQL With external routines, you make “callouts” and, optionally, “callbacks” through PL/SQL. PL/SQL subprogram

35. Benefits of External Routines • Integrates the strength and capabilities of different languages to give transparent access to these routines from within the database • Extensibility: Provide functionality in the database that is specific to a particular application, company, or technological area • Reusability: Can be shared by all users on a database, as well as moved to other databases or computers, providing standard functionality with limited cost in development, maintenance, and deployment

36. 3 Listenerprocess 2 4 6 extprocprocess 7 5 Aliaslibrary PL/SQL subprogram 1 Userprocess External routine Shared library How PL/SQL Calls a C External Routine BEGIN myproc

37. How an External C Routine Is Called • The user process invokes a PL/SQL program. • The server process executes a PL/SQL subprogram, • which looks up the alias library. • 3. The PL/SQL subprogram passes the request to the listener. • 4. The listener process spawns the extproc process. • The extproc processremains active throughout your • Oracle session until you log off. • 5.The extproc process loads the shared library. • 6.The extproc process executes the external procedure. • 7.The data status is returned to the server.

38. Development Steps for External C Routines 1. Create and compile the external routine in 3GL. 2. Link external routine into the shared library at theoperating system level. 3. Create an alias library schema object to map to the operating system shared library. 4. Grant execute privileges on the library. 5. Publish the external C routine by creating the PL/SQL subprogram unit specification, which references the alias library. 6. Execute the PL/SQL subprogram that invokes the external routine.

39. Creating an Alias Library • Use the CREATE LIBRARY statement to create an alias library object. • Grant EXECUTE privilege on the alias library. • Publish the external routine. • Call the external C routine through PL/SQL. CREATE OR REPLACE LIBRARY library_name IS|AS 'file_path'; GRANT EXECUTE ON library_name TO user|ROLE|PUBLIC

40. Accessing a Shared Library Through Publishing Publish the external routine in PL/SQL • The body of the subprogram contains the external routine registration. • The external routine runs on the same machine. • Access is controlled through the alias library.

41. Publishing an External C Routine • Identify the external body within a PL/SQL program to publish the external C routine. • The external body contains the external C routine information. CREATE OR REPLACE FUNCTION function_name (parameter_list) RETURN datatype regularbody|externalbody END; IS|AS LANGUAGE C LIBRARY libname [NAME C_function_name] [CALLING STANDARD C | PASCAL] [PARAMETERS (param_1, [param_n]);

42. Oracle Locks • Oracle automatically uses different types of locks to • control concurrent access to data • prevent destructive interaction between users. Oracle locks fall into one of the following general categories: DDL locks (dictionary locks) DDL locks protect the structure of schema objects. For example the definitions of tables and views. DML locks (data locks) DML locks protect data. For example table locks lock entire tables row locks lock selected rows. Internal locks and latches Internal locks and latches protect internal database structures such as datafiles. Internal locks and latches are entirely automatic.

43. Oracle DML Locks DML operations can acquire data locks at two different levels: 1) for specific rows 2) for entire tables. The only DML locks Oracle acquires automatically are row-level locks. There is no limit to the number of row locks held by a statement or transaction. Row locking provides the finest grain locking possible and so provides the best possible concurrency and throughput. A transaction acquires an exclusive DML lock for each individual row modified by one of the following statements: INSERT, UPDATE, DELETE, SELECT with the FOR UPDATE clause. A locked row/table remains locked until you either commit your transaction or roll it back.

44. Lock Table Statement This lock manually overrides automatic locking and permits or denies access to a table or view by other users for the duration of your operation. NOWAIT specifies that Oracle returns control to you immediately if the specified table (or specified partition or subpartition) is already locked by another user. In this case, Oracle returns a message indicating that the table, partition, or subpartition is already locked.

45. Lock Table Statement lockmode is one of the following: ROW SHAREallows concurrent access to the locked table, but prohibits users from locking the entire table for exclusive access. ROW SHARE is synonymous with SHARE UPDATE, which is included for compatibility with earlier versions of Oracle. ROW EXCLUSIVEis the same as ROW SHARE, but also prohibits locking in SHARE mode. Row Exclusive locks are automatically obtained when updating, inserting, or deleting. SHAREallows concurrent queries but prohibits updates to the locked table. SHARE ROW EXCLUSIVEis used to look at a whole table and to allow others to look at rows in the table but to prohibit others from locking the table in SHARE mode or updating rows. EXCLUSIVEallows queries on the locked table but prohibits any other activity on it.

46. Summarizes the information

47. Oracle DDL Locks A DDL lock protects the definition of a schema object (for example, a table). Oracle acquires a dictionary lock automatically on behalf of any DDL transaction requiring it. Users cannot explicitly request DDL locks. Only individual schema objects that are modified or referenced are locked during DDL operations; the whole data dictionary is never locked. Most DDL operations require exclusive DDL locks for a resource to prevent destructive interference with other DDL operations that might modify or reference the same schema object. During the acquisition of an exclusive DDL lock, if another DDL lock is already held on the schema object by another operation, the acquisition waits until the older DDL lock is released and then proceeds.

48. Oracle DDL Locks A share DDL lock is acquired on a schema object for DDL statements that include the following commands: AUDIT, NOAUDIT, COMMENT, CREATE [OR REPLACE] VIEW PROCEDURE PACKAGE PACKAGE BODY FUNCTION TRIGGER CREATE SYNONYM CREATE TABLE

49. Oracle Dead Locks Oracle automatically detects deadlock situations and resolves them by rolling back one of the statements involved in the deadlock, thereby releasing one set of the conflicting row locks. A corresponding message also is returned to the transaction that undergoes statement-level rollback.

50. Oracle Standby Implementation • Fail over Solution • Disaster Recovery Solution (if remote) • Ease of implementation • Minimum impact on Production System • Read Only Standby Database