![76 SQLD360 – how to USE • Download the free tool: https://github.com/mauropagano/sqld360/ • Execution: SQL> sqld360.sql <sql_id> [N|D|T]](https://image.slidesharecdn.com/2018dbabrasil3zaballa-180508142541/75/DBA-Commands-and-Concepts-That-Every-Developer-Should-Know-Part-2-76-2048.jpg)
DBA Commands and Concepts That Every Developer Should Know - Part 2
- 1. DBA Commands andConcepts That Every Developer Should Know Part 2 Alex Zaballa DBA Commands and Concepts That Every Developer Should Know Part 2 Palestrante DBA Commands and Concepts That Every Developer Should Know Part 2 Alex Zaballa
- 2.
- 3.
- 4.
- 5. 5 5 3 MembershipTiers • Oracle ACE Director • Oracle ACE • Oracle ACE Associate bit.ly/OracleACEProgram 500+ Technical Experts Helping Peers Globally Connect: Nominate yourself or someone you know: acenomination.oracle.com @oracleace Facebook.com/oracleaces oracle-ace_ww@oracle.com
- 6.
- 7. 7 Oracle Database MultilingualEngine – Beta 12.2 Initial beta with JavaScript Stored Procedures http://www.oracle.com/technetwork/database/multilingua l-engine/overview/index.html
- 8. 8 Oracle Database MultilingualEngine – Beta 12.2
- 9. 9 Oracle Database MultilingualEngine – Beta 12.2
- 10.
- 11.
- 12.
- 13. 13 Oracle Database 18cis NOT an Autonomous Database! https://oracle-base.com/blog/2018/01/03/oracle-database-18c-is-not-an-autonomous-database/
- 14. 14 Autonomous Database -DW https://juliandontcheff.wordpress.com/2018/03/28/dba-internals-of-the-oracle-autonomous-database/ “You cannot create indexes on columns, you cannot partition tables, you cannot create materialized views, etc. Not even database links. You will get an error message: ORA-00439: feature not enabled: Partitioning or ORA-01031: insufficient privileges.”
- 15.
- 16. 16 count(1) vs count(*) Whatis the difference between count(1) and count(*) ?
- 17.
- 18.
- 19. 19 Should you gatherSystem Statistics? https://blogs.oracle.com/optimizer/should-you-gather-system-statistics
- 20. 20 Is this developerrelated?
- 21. 21 Should you gatherSystem Statistics?
- 22. 22 Should you gatherSystem Statistics?
- 23. 23 Should you gatherSystem Statistics? “There is an exceptional case to consider. Databases supporting a pure data warehouse workload on an Oracle Exadata Database Machine can benefit from system statistics gathered using the EXADATA option.”
- 24. 24 MBRC Should I changeDB_FILE_MULTIBLOCK_READ_COUNT?
- 25. 25 Is this developerrelated?
- 26.
- 27.
- 28. 28 MBRC “Oracle recommends usingthe default unset value, which allows the Optimizer to calculate the cost as a value of 8, but to use the maximum I/O request size (usually 1MB) during Full Scan operations. Starting with Oracle 10gR2, the CBO can automatically determine an optimal value for the environment. Setting MBRC to any value makes CBO to use such value but also limits the number of blocks Oracle can read at once. If the parameter is unset, the optimizer uses maximum I/O request size up to 128 (usually 1MB) during Full Scan operations.”
- 29. 29 MBRC + SYSTEMSTATS + EXADATA EXEC DBMS_STATS.GATHER_SYSTEM_STATS(‘EXADATA’); ***Most of the time the FULL TABLE SCAN is more performant than an INDEX SCAN on Exadata Why? Smartscan
- 30. 30 MBRC + SYSTEMSTATS + EXADATA Exadata + DW: • Do not set MBRC • Collect system stats in Exadata mode Exadata + OLTP: • Do not set MBRC • Do not collect system stats
- 31. 31 MBRC + SYSTEMSTATS Regular Database + DW: • Do not set MBRC • Do not collect system stats Regular Database + OLTP : • Do not set MBRC • Do not collect system stats *****
- 32. 32 List of validvalues for Oracle Database parameters
- 33. 33 List of validvalues for Oracle Database parameters
- 34.
- 35. 35 List of validvalues for Oracle Database parameters How about hidden parameters?
- 36. 36 List of validvalues for Oracle Database parameters
- 37. 37 List of validvalues for Oracle Database parameters
- 38. 38 Parameter values –Another session
- 39. 39 Parameter values –Another session
- 40.
- 41.
- 42. 42 List of validvalues for Oracle Database parameters Not optimizer related?
- 43. 43 List of validvalues for Oracle Database parameters
- 44. 44 List of validvalues for Oracle Database parameters
- 45.
- 46.
- 47.
- 48.
- 49.
- 50.
- 51.
- 52.
- 53.
- 54.
- 55. 55 Cost of anIndex
- 56. 56 Cost of anIndex
- 57. 57 How about ExplainPlan?
- 58.
- 59. 59 Explain Plan It doesnot actually execute the SQL statement. Runtime factors can influence choice of plan: • Dynamic Sampling • Bind Variable Peeking • Adaptive Features
- 60. 60 Now what? • DBMS_XPLAN.DISPLAY_CURSOR •V$SQL_PLAN%
- 61. 61 DBMS_XPLAN.DISPLAY_CURSOR • SQL_ID • CURSOR_CHILD_NO(default 0) • FORMAT TYPICAL = DEFAULT ALL = TYPICAL + QB + PROJECTION + ALIAS + REMOTE ADVANCED = ALL + OUTLINE + BINDS ALLSTATS = IOSTATS + MEMSTATS (all executions) ALLSTATS LAST (last execution) ADAPTIVE (12c)
- 63. DBMS_XPLAN Display plan forthe last SQL executed: select * from table(dbms_xplan.display_cursor());
- 64. Display plan forone specific SQL Get SQL_ID:
- 65.
- 67. SQL MONITOR • Introducedin Oracle Database 11g. • Automatically started when a SQL statement either runs in parallel or has consumed at least 5 seconds of combined CPU and I/O. • Hint /*+ MONITOR */
- 68. • Oracle EnterpriseManager • EM Database Express (12c) • SQL Developer • Command Line **Part of the Oracle Tuning Pack SQL MONITOR
- 69. •select dbms_sqltune.report_sql_monitor( • sql_id=> 'gjabwvvr07w09', • report_level=>'ALL', • type => 'ACTIVE') from dual; SQL MONITOR
- 70.
- 71.
- 72. 72 SQLT (SQLTXPLAIN) DiagnosticTool (Doc ID 215187.1) • Pros: Supported by Oracle • Cons: Requires Installation
- 73. 73 SQLT (SQLTXPLAIN) DiagnosticTool (Doc ID 215187.1)
- 74. 74 SQLD360 • Pros: NoInstallation • Cons: Might miss some information under rare circumstances
- 75.
- 76. 76 SQLD360 – howto USE • Download the free tool: https://github.com/mauropagano/sqld360/ • Execution: SQL> sqld360.sql <sql_id> [N|D|T]
- 77.
- 78.
- 79.
- 80.
- 81. 81 If you wantto know more: https://mauro-pagano.com/category/sqld360/ SQLD360
- 82.
- 83.
- 84.
- 85.
- 86. 86 https://richardfoote.wordpress.com/2008/05/12/index-scan- or-full-table-scan-the-magic-number-magic-dance/ • Clustering Factor How well ordered the rows in the table are in relation to the index. • Selectivity of the query • Number of table blocks • Effective multiblock read count • Relative cost of single vs. multiblock I/Os • Parallelism • Etc Full table scan
- 87.
- 88. 88 • Not alwaysgood • Not always bad Full table scan
- 89.
- 90. 90 Pending Statistics We havethe option of keeping the newly gathered statistics in a pending state for testing purposes, until you choose to publish them. Set table preferences: begin dbms_stats.set_table_prefs ( ownname => 'SCOTT', tabname => 'EMP', pname => 'PUBLISH', pvalue => 'FALSE' ); end; Collect the statistics.
- 91.
- 92.
- 93. 93 If it’s ok: Or: PendingStatistics
- 94. 94 Restore Statistics fromHistory
- 95. 95 Restore Statistics fromHistory Check the retention: Default is 31 days.
- 96. 96 Statistics available forthe table: Restore Statistics from History
- 97.
- 98.
- 99.
- 100.
- 101.
- 102. 102 PARALLEL DML insert /*+append parallel */ into tab1 select /*+ parallel */ * from tab2 nologging; 15 minutes to complete. create table tab1 as select /*+ parallel */ * from tab2 nologging; 2 minutes to complete.
- 103.
- 104.
- 105.
- 106. 106 Oracle Flashback Query Retrieveold versions of procedures:
- 107.
- 108. 108 DBMS_APPLICATION_INFO Allows programs toadd information to the V$SESSION. Use SET_MODULE to set the name for the program that the user is currently executing. Optionally you can also set an action name. Use SET_ACTION for subsequent processing. Use SET_CLIENT_INFO for any additional information.
- 109.
- 110. 110 Schema Management DDL WaitOption SQL> alter table invoice add (code number); alter table invoice add (code number) * ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
- 111. 111 Parameter DDL_LOCK_TIMEOUT (default= 0) It will wait for N seconds. In that N seconds, it continually re-tries the DDL operation until it's successful or this time expires. Schema Management DDL Wait Option
- 112. 112 Adding Columns witha Default Value
- 113. 113 Adding Columns witha Default Value The table SALES is about 400 million rows. 10.2.0.4.0 alter table sales add tax varchar2(2) default ‘XX’ not null; Elapsed: 00:41:00.00 11.2.0.4.0 alter table sales add tax varchar2(2) default ‘XX’ not null; Elapsed: 00:00:00.03
- 114.
- 115. 115 READ Object Privilegeand READ ANY TABLE System Privilege
- 116. 116 READ Object Privilegeand READ ANY TABLE System Privilege What is the difference to SELECT and SELECT ANY TABLE?
- 117. 117 READ Object Privilegeand READ ANY TABLE System Privilege SELECT and SELECT ANY TABLE provides the ability to lock rows: LOCK TABLE table_name IN EXCLUSIVE MODE; SELECT ... FROM table_name FOR UPDATE;
- 118. 118 READ Object Privilegeand READ ANY TABLE System Privilege SQL> grant select on scott.emp to teste; Grant succeeded. SQL> lock table scott.emp in exclusive mode; Table(s) Locked.
- 119. 119 SQL> grant readon scott.emp to teste; Grant succeeded. SQL> lock table scott.emp in exclusive mode; lock table scott.emp in exclusive mode * ERROR at line 1: ORA-01031: insufficient privileges READ Object Privilege and READ ANY TABLE System Privilege
- 120.
- 121. 121 Extended Data Types SQL>create table tabela_teste(campo01 varchar2(4001)); * ERROR at line 1: ORA-00910: specified length too long for its datatype
- 122. 122 Extended Data Types •VARCHAR2 : 32767 bytes • NVARCHAR2 : 32767 bytes • RAW : 32767 bytes
- 123. 123 Extended Data Types SHUTDOWNIMMEDIATE; STARTUP UPGRADE; ALTER SYSTEM SET max_string_size=extended; @?/rdbms/admin/utl32k.sql SHUTDOWN IMMEDIATE; STARTUP; **Once you switch to extended data types you can't switch back
- 124.
- 125.
- 126.
- 127. 127 Temporary Undo ALTER SESSIONSET TEMP_UNDO_ENABLED = TRUE; ALTER SYSTEM SET TEMP_UNDO_ENABLED = TRUE;
- 128.
- 129. 129 Longer Identifier Names Startingwith Oracle Database 12c Release 2 (12.2), the maximum length of identifier names for most types of database objects has been increased to 128 bytes.
- 130.
- 131.
- 132.
- 133.
- 134. 134 PDB LOCKDOWN PROFILES Asecurity mechanism to restrict operations that are available to local users connected to a specified PDB.
- 135.
- 136.
- 137. 137 Thank You Slides Available:http://www.slideshare.net/