DBAs New Best Friend: Advanced SQL Tuning Features of Oracle
• Manual SQL tuning is painful even for the experts. • Oracle 10g SQL Tuning • Fact: Lots of Qs reference multiple tables forming a network of inter ...
Oracle® Database - SQL Tuning Guide
This manual explains how to tune Oracle SQL. Audience. This document is Reference. 1.5.2.2.2 Real-Time SQL Monitoring and Real-Time Database Operations.
Oracle Database Performance Tuning Guide
SQL for optimal execution. Oracle Database provides. CPU statistics in V$SQL ... Reference for details about the server result cache initialization parameters.
oracle-database-utilities.pdf
Oracle Database SQL Language Reference for information about using the APPEND Oracle Database SQL Tuning Guide. 5.3 Initialization Parameters That Affect Data ...
SQL*Plus Users Guide and Reference
U.S. GOVERNMENT END USERS: Oracle programs including any operating system
SQL*Plus Users Guide and Reference
Oracle Database Advanced Application Developer's Guide. •. Oracle XML DB best use of the Oracle Database SQL optimizer. For information about Tuning ...
database-performance-tuning-guide.pdf
U.S. GOVERNMENT END USERS: Oracle programs (including any operating system integrated software
Oracle Enterprise Manager Database Tuning with the Oracle Tuning
The SQL Hint and SQL Tuning wizard can add hints to existing SQL statements and Because SQL statement analysis may identify references to synonyms Oracle ...
PL/SQL Users Guide and Reference
Records 5 - 37 PL/SQL Oracle's procedural extension of SQL
DBAs New Best Friend: Advanced SQL Tuning Features of Oracle
Challenges of Manual SQL Tuning SQL Tuning Advisor since Oracle Database 10g. Add Missing ... Fact: Lots of Qs reference multiple tables forming a.
sql-tuning-guide.pdf - Oracle® Database
Oracle Database SQL Tuning Guide 12c Release 2 (12.2). E85762-06 and the AMD logo are trademarks or registered trademarks of Advanced Micro Devices.
Oracle 10g Expert SQL Tuning Techniques Understanding Oracle
more efficient forms and the use of advanced techniques such as Oracle SQL tuning is a phenomenally complex subject and entire books have been.
Oracle Database Performance Tuning Guide
Oracle Database PL/SQL Packages and Types Reference for detailed information on the DBMS_ADVISOR DBMS_SQLTUNE
Oracle Database SQL Language Reference
13 Jan 2001 AMD. Opteron
Oracle Database Utilities
Default Locations for Dump Log
Donald K. Burleson Joe Celko John Paul Cook Peter Gulutzan
Oracle Oracle7
Oracle Database SQL Reference
25 Jul 2017 Oracle Database SQL Language Reference 12c Release 1 (12.1) ... you decide to use hints rather than the more advanced tuning tools
Oracle Database Licensing Information
https://www.oracle.com/assets/databaselicensing-070584.pdf. Conventions Oracle Advanced Compression ... into SQL Tuning Set features are supported.
SQL*Plus Users Guide and Reference Release 10.2
SQL*Plus Script Tuning. have familiarity with SQL see the Oracle Database SQL Reference. ... Oracle Database Performance Tuning Guide.
with an action, or terms defined in text or the glossary.italicItalic type indicates book titles, emphasis, or placeholder variables for
which you supply particular values. monospaceMonospace type indicates commands within a paragraph, URLs, code in examples, text that appears on the screen, or text that you enter.DBMS_STATS
DBMS_STATS
DBMS_STATS
AUTO_STAT_EXTENSIONS
ON OFFDBMS_STATS
im_imcu_count im_block_countscanrateDBMS_STATS
PREFERENCE_OVERRIDES_PARAMETER
PREFERENCE_OVERRIDES_PARAMETER
FLUSH_DATABASE_MONITORING_INFO
DBA_TAB_STATISTICS
DBA_IND_STATISTICS
DBA_TAB_MODIFICATIONS
OPTIMIZER_ADAPTIVE_PLANS
OPTIMIZER_ADAPTIVE_STATISTICS
WITHȑOR
CONCATENATION
ORUNION-ALL
LTRIMTO_NUMBER
V$OPTIMIZER_ADAPTIVE_FEATURES
GROUP BY
VECTOR
GROUP BY
SYS_AUTO_SPM_EVOLVE_TASK
DBMS_SPM
SYSAUX
AUTO_SAMPLING
JOB_QUEUE_PROCESSES
DBMS_STATS
DBMS_STATS
DBMS_STATS
CREATE TABLE AS SELECT
INSERT INTO ... SELECT
ALTER TABLE EXCHANGE
INCREMENTAL
trueCUBE JOIN
SIMILAR
CURSOR_SHARING
FORCEDatabase architectureDatabase architecture is not
the domain of administrators alone. As a developer, you want to develop applications in the least amount of time against an Oracle database, which requires exploiting the database architecture and features. For example, not understandingOracle Database concurrency
controls and multiversioning read consistency may make an application corrupt the integrity of the data, run slowly, and decrease scalability.Oracle Database Concepts explains the basic relational data structures, transaction management, storage structures, and instance architecture of Oracle Database.SQL and PL/SQLBecause of the existence ofGUI-based tools, it is possible
to create applications and administer a database without knowing SQL. However, it is impossible to tune applications or a database without knowing SQL.Oracle Database Concepts includes an introduction to OracleSQL and PL/SQL. You must also
have a working knowledge ofOracle Database SQL Language
Reference, Oracle Database
PL/SQL Packages and Types
Reference, and Oracle Database
PL/SQL Packages and Types
Reference.SQL tuning toolsThe database generates
performance statistics, and provides SQL tuning tools that interpret these statistics.Oracle Database 2 Day +Performance Tuning Guide
provides an introduction to the principal SQL tuning tools.ȄUNION
UNION ALLȄ
DBMS_STATS
WHERESELECT
DBMS_SPM
DBMS_XPLAN
DBMS_XPLAN
EXPLAIN PLAN
V$SQL_PLAN
EXPLAIN PLAN
ȑV$SQL_PLAN
AUTOTRACE
AUTOTRACE
V$SQL_MONITOR
V$SQL_PLAN_MONITOR
V$ACTIVE_SESSION_HISTORYȑ
V$SESSIONȑ
V$SESSION_LONGOPSȑ
V$SQLȑ
V$SQL_PLAN
V$DBMS_MONITOR
DBMS_SESSION
ȑTKPROF
trcsess trcsessTKPROF
trcsessDBMS_MONITOR
DBMS_SESSION
SELECT /*+ INDEX (employees emp_department_ix) */ employee_id, department_id FROM employeesWHERE department_id > 50;
DBMS_SQLTUNE
SELECT * FROM employees WHERE last_name LIKE ©KING©; SELECT * FROM employees WHERE last_name LIKE :1;Test #Users SupportedNo Parsing all statements 270 Soft Parsing all statements 150
Hard Parsing all statements 60Re-Connecting for each Transaction 30Generation of
multiple execution plansGeneration of
query planParsing
Optimization
Row Source
Generation
Execution
Hard ParseSoft Parse
Semantic
CheckSyntax
CheckShared Pool
CheckSQL Statement
FROM FORMSQL> SELECT * FORM employees;SELECT * FORM employees *ERROR at line 1:ORA-00923: FROM keyword not found where expected
SQL> SELECT * FROM nonexistent_table;SELECT * FROM nonexistent_table *ERROR at line 1:ORA-00942: table or view does not exist
V$SQL.SQL_ID
UPDATE
Comparison of hash valuesUser
Server
ProcessClient
Process
Private SQL Area
UserUpdate ...
PGASQL Work Areas
Session Memory
3967354608
System Global Area (SGA)
Shared Pool
Private
SQL Area
Shared SQL Area
3667723989
3967354608
2190280494Library Cache
DataDictionary
CacheServer
Result
CacheOther Reserved
Pool CREATE TABLE my_table ( some_col INTEGER );SELECT * FROM my_table;SELECT
my_tableALTER SESSION SET OPTIMIZER_MODE=ALL_ROWS;ALTER SYSTEM FLUSH SHARED_POOL; # optimizer environment 1SELECT * FROM sh.sales;ALTER SESSION SET OPTIMIZER_MODE=FIRST_ROWS; # optimizer environment 2SELECT * FROM sh.sales;ALTER SESSION SET SQL_TRACE=true; # optimizer environment 3SELECT * FROM sh.sales;
SELECT
SELECT
AUTOTRACE
ASELECT e.last_name, j.job_title, d.department_name FROM hr.employees e, hr.departments d, hr.jobs jWHERE e.department_id = d.department_idAND e.job_id = j.job_idAND e.last_name LIKE ©A%©; Execution Plan----------------------------------------------------------Plan hash value: 975837011---------------------------------------------------------------------------| Id| Operation | Name |Rows|Bytes|Cost(%CPU)|Time|---------------------------------------------------------------------------| 0| SELECT STATEMENT | | 3 |189 |7(15)|00:00:01 ||*1| HASH JOIN | | 3 |189 |7(15)|00:00:01 ||*2| HASH JOIN | | 3 |141 |5(20)|00:00:01 || 3| TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 3 | 60 |2 (0)|00:00:01 ||*4| INDEX RANGE SCAN | EMP_NAME_IX | 3 | |1 (0)|00:00:01 || 5| TABLE ACCESS FULL | JOBS |19 |513 |2 (0)|00:00:01 || 6| TABLE ACCESS FULL | DEPARTMENTS |27 |432 |2 (0)|00:00:01 |--------------------------------------------------------------------------- Predicate Information (identified by operation id):---------------------------------------------------
1 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID") 2 - access("E"."JOB_ID"="J"."JOB_ID") 4 - access("E"."LAST_NAME" LIKE ©A%©) filter("E"."LAST_NAME" LIKE ©A%©)
IdOperation
SELECT
TABLE ACCESS
FULL jobs 53TABLE ACCESS
BY INDEX ROWID
employees 4INDEX RANGE
SCAN emp_name_ix 6TABLE ACCESS
FULL departments 1HASH JOIN
2HASH JOIN
Ȅdepartments
jobs emp_name_ix AAtkinson
AAAPzRAAFAAAABSAAe
employeesAAAPzRAAFAAAABSAAe
Atkinson
Atkinson
Stock Clerk
Atkinson
Shipping
emp_name_ix employees jobs jobsCREATETABLE
CREATE TABLE mytable (mycolumn INTEGER);
ȑCOMMIT
CREATE TABLE
COMMIT
ROLLBACK
Parsed Representation
of SQL StatementInput Output
Final Plan with
Lowest Cost
GB HJHJOptimizer
1 0 1 1 0 0 1 0 0
StatisticsGenerates Multiple Plans and Compares Them Plan 1 GB NLNL Plan 2 GB HJHJ Plan 2SELECT
SELECT first_name, last_nameFROM hr.employeesWHERE department_id IN (SELECT department_id FROM hr.departments WHERE location_id = 1800);
FROM QueryTransformer
Estimator
PlanGeneratorParsed Query
(from Parser)Query Plan
(to Row Source Generator)Transformed queryQuery + estimates
DataDictionarystatistics
1Query TransformerThe optimizer determines whether it is helpful
to change the form of the query so that the optimizer can generate a better execution plan." "2EstimatorThe optimizer estimates the cost of each planbased on statistics in the data dictionary.""3Plan GeneratorThe optimizer compares the costs of plans
and chooses the lowest-cost plan, known as the execution plan, to pass to the row source generator."" ORUNION ALL
Query Transformer
SELECT *
FROM sales
WHERE prod_id=136
UNION ALL
SELECT *
FROM sales
WHERE promo_id=33
AND LNNVL(prod_id=136);
SELECT *
FROM sales
WHERE promo_id=33
OR prod_id=136;
0 1WHERE last_name LIKE ©A%©
0 1DBMS_STATS
DISTINCT
GROUP BY
RowsEstimator
1 0 1 0 0
0 0 0 1 1
0 1 1 0 1 Statistics
PlanGB
HJHJTotal Cost
Cardinality
SelectivityCost
---------------------------------------------------------------------------|Id| Operation |Name |Rows|Bytes|Cost %CPU|Time|---------------------------------------------------------------------------| 0| SELECT STATEMENT | |10|250|3 (0)|00:00:01|| 1| NESTED LOOPS | | | | | || 2| NESTED LOOPS | |10|250|3 (0)|00:00:01||*3| TABLE ACCESS FULL |DEPARTMENTS | 1| 7|2 (0)|00:00:01||*4| INDEX RANGE SCAN |EMP_DEPARTMENT_IX|10| |0 (0)|00:00:01|| 5| TABLE ACCESS BY INDEX ROWID|EMPLOYEES |10|180|1 (0)|00:00:01|---------------------------------------------------------------------------
last_name©Smith©
last_name = ©Smith© AND job_id = ©SH_CLERK©OPTIMIZER_DYNAMIC_SAMPLING
last_name = ©Smith© last_name > ©Smith© last_name = ©Smith© n last_name last_nameOPTIMIZER_DYNAMIC_SAMPLING
Rows WHERE hr employees SELECT first_name, last_nameFROM employeesWHERE salary=©10200©; employees salary 582
107/58=1.84
employees departments employees departmentsOptimizer
Hash Join
departments 0, employees 1SELECT e.last_name, d.department_name
FROM hr.employees e, hr.departments d
WHERE e.department_id = d.department_id;
Transformer
Lowest Cost Plan
Join Method
Hash, Nested
Loop, Sort Merge
Access Path
IndexFull Table Scan
Join Order
departments 0 employees 1 employees 0 departments 1GENERAL PLANS***************************************Considering cardinality-based initial join order.Permutations for Starting Table :0Join order[1]: DEPARTMENTS[D]#0 EMPLOYEES[E]#1 ***************Now joining: EMPLOYEES[E]#1***************NL Join Outer table: Card: 27.00 Cost: 2.01 Resp: 2.01 Degree: 1 Bytes: 16Access path analysis for EMPLOYEES. . . Best NL cost: 13.17. . .SM Join SM cost: 6.08 resc: 6.08 resc_io: 4.00 resc_cpu: 2501688 resp: 6.08 resp_io: 4.00 resp_cpu: 2501688. . .SM Join (with index on outer) Access Path: index (FullScan). . .HA Join HA cost: 4.57 resc: 4.57 resc_io: 4.00 resc_cpu: 678154 resp: 4.57 resp_io: 4.00 resp_cpu: 678154Best:: JoinMethod: Hash Cost: 4.57 Degree: 1 Resp: 4.57 Card: 106.00 Bytes: 27. . .***********************Join order[2]: EMPLOYEES[E]#1 DEPARTMENTS[D]#0. . . ***************Now joining: DEPARTMENTS[D]#0***************. . .HA Join HA cost: 4.58 resc: 4.58 resc_io: 4.00 resc_cpu: 690054 resp: 4.58 resp_io: 4.00 resp_cpu: 690054Join order aborted: cost > best plan cost***********************
departments Best:: JoinMethod: Hash Cost: 4.57 Degree: 1 Resp: 4.57 Card: 106.00 Bytes: 27 employeesAdaptive
Statistics
SQL Plan
Directives
Dynamic
Statistics
Automatic
Reoptimization
Bitmap
Index Pruning
JoinMethods
ParallelDistributionMethods
Adaptive
PlansAdaptive QueryOptimization
Dynamic Plan
Subplan Group
Subplan
Subplan
GB HJHJ GB NLNLSubplan Group
Subplan
Subplan
GB HJHJ GB NLNLDynamic Plan
Coordinator
order_items prod_infoSELECT product_name FROM order_items o, prod_info p WHERE o.unit_price = 15 AND quantity > 1 AND p.product_id = o.product_id
SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(FORMAT => ©ADAPTIVE©));SQL_ID 7hj8dwwy6gm7p, child number 0
-------------------------------------SELECT product_name FROM order_items o, prod_info p WHEREo.unit_price = 15 AND quantity > 1 AND p.product_id = o.product_idPlan hash value: 1553478007-----------------------------------------------------------------------------| Id | Operation | Name |Rows|Bytes|Cost (%CPU)|Time|-----------------------------------------------------------------------------| 0| SELECT STATEMENT | | | |7(100)| || * 1| HASH JOIN | |4| 128 | 7 (0)|00:00:01||- 2| NESTED LOOPS | |4| 128 | 7 (0)|00:00:01||- 3| NESTED LOOPS | |4| 128 | 7 (0)|00:00:01||- 4| STATISTICS COLLECTOR | | | | | || * 5| TABLE ACCESS FULL | ORDER_ITEMS |4| 48 | 3 (0)|00:00:01||-* 6| INDEX UNIQUE SCAN | PROD_INFO_PK |1| | 0 (0)| ||- 7| TABLE ACCESS BY INDEX ROWID| PROD_INFO |1| 20 | 1 (0)|00:00:01|| 8| TABLE ACCESS FULL | PROD_INFO |1| 20 | 1 (0)|00:00:01|-----------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - access("P"."PRODUCT_ID"="O"."PRODUCT_ID") 5 - filter(("O"."UNIT_PRICE"=15 AND "QUANTITY">1)) 6 - access("P"."PRODUCT_ID"="O"."PRODUCT_ID")Note----- - this is an adaptive plan (rows marked ©-© are inactive)
prod_info order_items order_itemsNestedLoopsStatisticsCollectorTable scanorder_itemsIndex scanprod_info_pkHashJoinTable scanprod_infoDefault plan is a nested loops joinThe optimizer buffers rows coming from the order_items table up to a point. If the row count is less than the threshold, then use a nested loops join. Otherwise, switch to a hash join. NestedLoopsStatisticsCollectorTable scanorder_itemsIndex scanprod_info_pkHashJoinTable scanprod_infoFinal plan is a hash joinThe optimizer disables the statistics collector after making the decision, and lets the rows pass through. Threshold exceeded, so subplan switches7KHNoteVHFWLRQRIWKHH[HFXWLRQSODQLQGLFDWHVZKHWKHUWKHSODQLVDGDSWLYHDQGZKLFKURZVLQWKHSODQDUHLQDFWLYH6HH$OVR&RQWUROOLQJ$GDSWLYH2SWLPL]DWLRQ5HDGLQJ([HFXWLRQ3ODQV$GYDQFHGIRUDQH[WHQGHGH[DPSOHVKRZLQJDQDGDSWLYHTXHU\SODQ&KDSWHU$ERXW$GDSWLYH4XHU\2SWLPL]DWLRQ
departments employees departments employees P1quotesdbs_dbs6.pdfusesText_12[PDF] advanced oracle sql tutorial
[PDF] advanced php book pdf
[PDF] advanced piano chords pdf
[PDF] advanced piano chords. pdf
[PDF] advanced placement french: preparing for the language examination
[PDF] advanced powerpoint 2010 tutorial pdf
[PDF] advanced powerpoint 2010 tutorial pdf free download
[PDF] advanced powerpoint 2013 tutorial pdf
[PDF] advanced powerpoint animation
[PDF] advanced powerpoint animation free download
[PDF] advanced powerpoint charts
[PDF] advanced powerpoint exercises
[PDF] advanced powerpoint presentation examples
[PDF] advanced powerpoint presentation free download