Скачать презентацию 2 SQL Statement Processing Copyright 2005 Oracle Скачать презентацию 2 SQL Statement Processing Copyright 2005 Oracle

8b9f5e5b5b1de4377f5e7165fc4b6803.ppt

  • Количество слайдов: 29

2 SQL Statement Processing Copyright © 2005, Oracle. All rights reserved. 2 SQL Statement Processing Copyright © 2005, Oracle. All rights reserved.

Objectives After completing this lesson, you should be able to do the following: • Objectives After completing this lesson, you should be able to do the following: • Describe the basic steps involved in processing a SQL statement • Monitor the use of shared SQL areas • Write SQL statements to take advantage of shared SQL areas • Understand how to use the CURSOR_SHARING parameter • Use automatic PGA memory management 2 -2 Copyright © 2005, Oracle. All rights reserved.

Overview • • 2 -3 System Global Area (SGA) Shared SQL areas Program Global Overview • • 2 -3 System Global Area (SGA) Shared SQL areas Program Global Area (PGA) SQL processing phases SQL coding standards Shared cursors Automatic PGA memory management Copyright © 2005, Oracle. All rights reserved.

System Global Area (SGA) Instance SGA Shared pool DB buffer Redo log cache buffer System Global Area (SGA) Instance SGA Shared pool DB buffer Redo log cache buffer Server process PGA User process Program unit 2 -4 Parameter file Password file Data files Control files Redo log files Archived log files Database Copyright © 2005, Oracle. All rights reserved.

Shared Pool Contains: • Data dictionary cache • Library cache – SQL statements – Shared Pool Contains: • Data dictionary cache • Library cache – SQL statements – Parsed or compiled PL/SQL blocks – Java classes 2 -5 Copyright © 2005, Oracle. All rights reserved. Application code

Shared SQL Areas Cursor for SELECT statement 2 Cursor for SELECT statement 1 Shared Shared SQL Areas Cursor for SELECT statement 2 Cursor for SELECT statement 1 Shared SQL SGA User A SELECT statement 1 2 -6 User B SELECT statement 2 Copyright © 2005, Oracle. All rights reserved. User C SELECT statement 1

Program Global Area • PGA is a memory area that contains: – – – Program Global Area • PGA is a memory area that contains: – – – – • • 2 -8 Session information Cursor information SQL execution work areas Sort area Hash join area Bitmap merge area Bitmap create area Work area size influences SQL performance. Work area can be automatically or manually managed. Copyright © 2005, Oracle. All rights reserved.

SQL Statement Processing Phases Open Parse 2 -10 Close Bind Execute Copyright © 2005, SQL Statement Processing Phases Open Parse 2 -10 Close Bind Execute Copyright © 2005, Oracle. All rights reserved. Fetch

SQL Statement Processing Phases: Parse phase: • Searches for the statement in the shared SQL Statement Processing Phases: Parse phase: • Searches for the statement in the shared pool • Checks syntax • Checks semantics and privileges • Merges view definitions and subqueries • Determines execution plan 2 -11 Copyright © 2005, Oracle. All rights reserved.

SQL Statement Processing Phases: Bind phase: • Scans the statement for bind variables • SQL Statement Processing Phases: Bind phase: • Scans the statement for bind variables • Assigns (or reassigns) a value 2 -13 Copyright © 2005, Oracle. All rights reserved.

SQL Statement Processing Phases: Execute and Fetch • Execute phase: – Applies the execution SQL Statement Processing Phases: Execute and Fetch • Execute phase: – Applies the execution plan – Performs necessary I/O and sorts for data manipulation language (DML) statements • Fetch phase: – Retrieves rows for a query – Sorts for queries when needed – Uses an array fetch mechanism 2 -14 Copyright © 2005, Oracle. All rights reserved.

Benefits of Sharing Cursors • • • 2 -16 Reduces parsing Dynamically adjusts memory Benefits of Sharing Cursors • • • 2 -16 Reduces parsing Dynamically adjusts memory Improves memory usage Copyright © 2005, Oracle. All rights reserved.

Shared Cursor: Requirements Cursors can be shared only by SQL statements that have the Shared Cursor: Requirements Cursors can be shared only by SQL statements that have the following identical elements: • Text – Uppercase and lowercase – White space (spaces, tabs, carriage returns) – Comments • • 2 -17 Referenced objects Bind-variable data types Copyright © 2005, Oracle. All rights reserved.

Sharing Cursors • If the case or the amount of white space is different, Sharing Cursors • If the case or the amount of white space is different, then the statements are not identical. select * from sh. customers where cust_id=180; select * from sh. CUSTOMERS where CUST_ID=180; • If the objects belong to different users, then the statements are not identical. select * from customers where cust_last_name ='Taylor'; 2 -18 SH OE Copyright © 2005, Oracle. All rights reserved.

Bind Variables and Shared Cursors Suppose you enter the following two statements: select * Bind Variables and Shared Cursors Suppose you enter the following two statements: select * from sh. customers where cust_id = : c select * from sh. customers where cust_id = : d Both statements are translated into: select * from sh. customers where cust_id = : b 1 2 -19 Copyright © 2005, Oracle. All rights reserved.

Writing SQL to Share Cursors Create generic code using the following: • Stored procedures Writing SQL to Share Cursors Create generic code using the following: • Stored procedures and packages • Database triggers • Referenced Oracle Forms Services procedures • Any other library routines and procedures 2 -20 Copyright © 2005, Oracle. All rights reserved.

Standardizing SQL for Cursor Sharing Follow SQL coding standards: • Case • White space Standardizing SQL for Cursor Sharing Follow SQL coding standards: • Case • White space • Comments • Object references • Bind variables 2 -21 Copyright © 2005, Oracle. All rights reserved.

Controlling Shared Cursors The CURSOR_SHARING initialization parameter can be set to: • EXACT (default) Controlling Shared Cursors The CURSOR_SHARING initialization parameter can be set to: • EXACT (default) • SIMILAR • FORCE 2 -22 Copyright © 2005, Oracle. All rights reserved.

Monitoring Shared Cursors • • V$LIBRARYCACHE provides general information about the library cache. Information Monitoring Shared Cursors • • V$LIBRARYCACHE provides general information about the library cache. Information about individual SQL statements is contained in the following views: – – 2 -24 V$SQLTEXT_WITH_NEWLINES V$SQL_BIND_DATA V$SQL and V$SQLAREA Copyright © 2005, Oracle. All rights reserved.

V$LIBRARYCACHE Columns NAMESPACE GETS Total number of requests (lookups) GETHITS Number of times that V$LIBRARYCACHE Columns NAMESPACE GETS Total number of requests (lookups) GETHITS Number of times that an object's handle was found in memory GETHITRATIO Ratio of GETHITS to GETS PINS Number of objects in the library cache PINHITS Number of times that all the pieces of the object were found in memory PINHITRATIO Ratio of PINHITS to PINS RELOADS 2 -25 Name of the library cache area Number of library cache misses Copyright © 2005, Oracle. All rights reserved.

V$SQLAREA Columns SQL_TEXT VERSION_COUNT Number of versions of this cursor LOADS Number of times V$SQLAREA Columns SQL_TEXT VERSION_COUNT Number of versions of this cursor LOADS Number of times the cursor has been loaded INVALIDATIONS Number of times the contents have been invalidated PARSE_CALLS Number of times a user has called this cursor SORTS Number of sorts performed by the statement COMMAND_TYPE Command type PARSING_USER_ID 2 -27 Text of the SQL statement Parsing user ID (SYS = 0) Copyright © 2005, Oracle. All rights reserved.

Monitoring the Use of Shared Cursors • • • 2 -28 One load per Monitoring the Use of Shared Cursors • • • 2 -28 One load per statement is ideal. One load per version/invalidation is optimal. More than one load per version indicates a potential benefit from increasing the shared pool size. Copyright © 2005, Oracle. All rights reserved.

Monitoring the Use of Shared Cursors SELECT sql_text, version_count, loads, invalidations, parse_calls, sorts FROM Monitoring the Use of Shared Cursors SELECT sql_text, version_count, loads, invalidations, parse_calls, sorts FROM v$sqlarea WHERE parsing_user_id > 0 AND command_type = 3 ORDER BY sql_text; version invali parse sql_text count loads dations calls sorts -------- ----select * from 2 1 0 3 0 customers where CUST_ID = 180 select * from customers where cust_id = 180 2 -29 1 2 Copyright © 2005, Oracle. All rights reserved. 1 1 0

Automated SQL Execution Memory Management • • 2 -30 Allocation and tuning of PGA Automated SQL Execution Memory Management • • 2 -30 Allocation and tuning of PGA memory is simplified and improved. DBAs can use parameters to specify the policy for PGA sizing. Copyright © 2005, Oracle. All rights reserved.

Dynamic Performance Views Several dynamic performance views provide information to help you tune the Dynamic Performance Views Several dynamic performance views provide information to help you tune the value of the PGA_AGGREGATE_TARGET parameter: • V$SYSSTAT and V$SESSTAT • V$PGASTAT • V$PROCESS • V$SQL_WORKAREA_ACTIVE • V$SQL_WORKAREA • V$PGA_TARGET_ADVICE 2 -31 Copyright © 2005, Oracle. All rights reserved.

V$SYSSTAT and V$SESSTAT Find the total number and percentage of times that work areas V$SYSSTAT and V$SESSTAT Find the total number and percentage of times that work areas were executed in the optimal, one-pass, and multipass modes. SELECT name profile, cnt, decode(total, 0, 0, round(cnt*100/total)) percentage FROM (SELECT name, value cnt, (sum(value) over ()) total FROM V$SYSSTAT WHERE name like 'workarea exec%'); PROFILE CNT PERCENTAGE ------------------workarea executions - optimal 5395 95 workarea executions - onepass 284 5 workarea executions - multipass 0 0 2 -32 Copyright © 2005, Oracle. All rights reserved.

Summary In this lesson, you should have learned that the four most important processing Summary In this lesson, you should have learned that the four most important processing phases are: • Parse • Bind • Execute • Fetch 2 -34 Copyright © 2005, Oracle. All rights reserved.

Summary To optimize the use of shared SQL areas: • Write generic code. • Summary To optimize the use of shared SQL areas: • Write generic code. • Follow coding standards. • Use bind variables. • Set the CURSOR_SHARING parameter. • 2 -35 Use automatic PGA memory management. Copyright © 2005, Oracle. All rights reserved.

Practice 2: Overview This practice covers the following topics: • SQL statement processing • Practice 2: Overview This practice covers the following topics: • SQL statement processing • Using the V$LIBRARYCACHE view to analyze library cache performance • Using the V$SQLAREA view to see information about all shared cursors in the cache 2 -36 Copyright © 2005, Oracle. All rights reserved.