Gathering Data on the SQL Identified
====================================
If you are most concerned with CPU, then examine the top SQL statements that
performed the most BUFFER_GETS during that interval. Otherwise, start with the SQL
statement that performed the most DISK_READS.
Information to Gather During Tuning
-----------------------------------
The tuning process begins by determining the structure of the underlying tables and
indexes. The information gathered includes the following:
1. Complete SQL text from V$SQLTEXT
2. Structure of the tables referenced in the SQL statement, usually by describing the
table in SQL*Plus
3. Definitions of any indexes (columns, column orderings), and whether the indexes
are unique or non-unique
4. Optimizer statistics for the segments (including the number of rows each table,
selectivity of the index columns), including the date when the segments were last
analyzed
5. Definitions of any views referred to in the SQL statement
6. Repeat steps two, three, and four for any tables referenced in the view definitions found in step five
7. Optimizer plan for the SQL statement (either from EXPLAIN PLAN, V$SQL_PLAN,
or the TKPROF output)
8. Any previous optimizer plans for that SQL statement.
Halim is a Sr. Database Engineer/Data Architect (in Atlanta, USA) who is an Oracle certified (OCP) DBA, (OCP) Developer, Certified Cloud Architect Professional as well as OCI Autonomous DB specialist with extensive expertise in Database design, configuration, tuning, capacity planning, RAC, DG, Scripting, Python, PL/SQL etc. He achieved 16th position in worldwide first-ever PL/SQL Challenge cup playoff- http://plsql-challenge.blogspot.com/2010/07/winners-of-first-plsql-challenge.html
Friday, October 9, 2009
Subscribe to:
Post Comments (Atom)
My Blog List
-
-
Index Usage – 21 week ago
-
-
-
-
-
-
-
-
Oracle Cloud & Third party tools3 years ago
-
-
-
Moving Sideways7 years ago
-
Upcoming Events...10 years ago
-
No comments:
Post a Comment