| General Information |
How Can I Get Help Tuning A SQL Statement?
If you were referred to this page from an OTN or other forum most likely the reason is one of the following:
- You did not include your version number (SELECT * FROM v$version). 10g or 11g are not version numbers. They are marketing labels with no real meaning.
- You did not include DDL for tables and indexes. One can not tune in the absence of column data types and index information.
- You did not include an Explain Plan report generated using DBMS_XPLAN.DISPLAY. Toad and other product explain plan reports are worthless
- You provided no information on RAC vs. stand-alone
- You did not read the FAQ and post your listing in a formatted and readable fashion
Try again and perhaps someone will be able to help you. |
Question:
How does the latch process in the shared pool work?
Answer:
- Compute the hash value of the statement
- Use the hash value to determine the part of the shared pool to be latched (locked)
- Find the statement (if already in the Shared Pool)
- If not present hard-parse the statement (syntax and lexical check, privilege check, optimize)
- If it is present skip the syntax and lexical check. Perform the privilege check and optimize
- Release the latch
|
| |
| Caching |
One of the things you want to avoid, in any situation, is rereading data. If you have it in memory ...
you want to be able to perform multiple operations on it right then and there.
This demo is based on code I received from Tim Hill. |
CREATE OR REPLACE TYPE t_airplanes_row AS OBJECT (
program_id VARCHAR2(3),
line_number NUMBER(10),
customer_id VARCHAR2(4),
order_date DATE,
delivered_date DATE);
/
CREATE OR REPLACE TYPE t_airplanes_tab AS TABLE OF t_airplanes_row;
/
CREATE OR REPLACE PACKAGE cache_demo AS
l_tab t_airplanes_tab;
PROCEDURE populate_cache;
FUNCTION get_airplanes RETURN t_airplanes_tab;
END cache_demo;
/
CREATE OR REPLACE PACKAGE BODY cache_demo AS
PROCEDURE populate_cache AS
BEGIN
SELECT t_airplanes_row(program_id, line_number,
customer_id, order_date, delivered_date)
BULK COLLECT INTO l_tab
FROM airplanes
WHERE rownum < 50001;
END;
------------------------------------------------
FUNCTION get_airplanes RETURN t_airplanes_tab AS
BEGIN
RETURN l_tab;
END;
BEGIN
populate_cache;
END cache_demo;
/
SELECT e.program_id, SUM(e.line_number)
FROM TABLE(cache_demo.get_airplanes) e
GROUP BY e.program_id;
SELECT e.program_id, SUM(e.line_number)
FROM TABLE(cache_demo.get_airplanes) e
WHERE order_date BETWEEN TO_DATE('01-NOV-2009') AND TO_DATE('31-DEC-2009')
GROUP BY e.program_id; |
| |
| Control File |
| Control File Waits |
SELECT inst_id, total_waits, total_timeouts, time_waited
FROM gv$system_event
WHERE event = 'control file sequential read'; |
| |
| Disk I/O |
| A high ratio is indicative of full table scans |
-- phyrds is the number of physical reads
-- phyblkrd is the number of physical blocks read during the physical reads.
SELECT d.tablespace_name, f.file#, round(f.phyblkrd / f.phyrds, 3) RATIO
FROM gv$filestat f, dba_data_files d
WHERE f.file# = d.file_id; |
| |
| Hinting |
| Full Hinting Demo |
CREATE TABLE t1 AS
SELECT * FROM all_objects
WHERE ROWNUM = 1;
ALTER TABLE t1
ADD CONSTRAINT pk_t1
PRIMARY KEY(object_id)
USING INDEX;
CREATE TABLE t2 AS
SELECT * FROM all_objects;
ALTER TABLE t2
ADD CONSTRAINT pk_t2
PRIMARY KEY(object_id)
USING INDEX;
exec dbms_stats.gather_table_stats('UWCLASS', 'T1', CASCADE=>TRUE);
exec dbms_stats.gather_table_stats('UWCLASS', 'T2', CASCADE => TRUE);
ALTER SESSION SET tracefile_identifier='base plan';
ALTER SESSION SET EVENTS '10053 trace name context forever,level 1';
ALTER SESSION SET EVENTS '10046 trace name context forever,level 1';
SELECT COUNT(*)
FROM t2, t1
WHERE t1.object_id = t2.object_id
AND t2.owner='UWCLASS';
ALTER SESSION SET tracefile_identifier='hinted plan';
SELECT /*+ use_nl(hint2 hint1) */ COUNT(*)
FROM t2, t1
WHERE t1.object_id = t2.object_id
AND t2.owner='UWCLASS';
ALTER SESSION SET tracefile_identifier='fully hinted plan';
SELECT /*+ ordered use_nl(hint2 hint1) */ COUNT(*)
FROM t2, t1
WHERE t1.object_id = t2.object_id
AND t2.owner='UWCLASS';
ALTER SESSION SET EVENTS '10053 trace name context off'; |
| |
| Log Buffer and Files |
If you see substantial waits for log buffer space consider enlarging the memory based log buffer.
If substantial waits for log file sync consider examine I/O performance of the online log buffers. |
-- waits for space in the log file
SELECT name, value
FROM v$sysstat
WHERE name = 'redo log space requests';
SELECT name, block_size, resize_State, current_size, target_size
FROM gv$buffer_pool;
SELECT name, value
FROM gv$parameter
WHERE name LIKE '%log%buf%';
ALTER SYSTEM SET log_buffer = 10240000 SCOPE=spfile; |
| |
| Memory Optimization |
Memory over time
Posted by Steve Howard at c.d.o.server 4-Dec-2007 |
SELECT time, instance_number,
MAX(DECODE(name, 'free memory',shared_pool_bytes,NULL)) free_memory,
MAX(DECODE(name,'library cache',shared_pool_bytes,NULL)) library_cache,
MAX(DECODE(name,'sql area',shared_pool_bytes,NULL)) sql_area
FROM (
SELECT TO_CHAR(begin_interval_time,'YYYY_MM_DD HH24:MI') time,
dhs.instance_number, name, bytes - LAG(bytes, 1, NULL)
OVER (ORDER BY dhss.instance_number,name,dhss.snap_id) AS
shared_pool_bytes
FROM dba_hist_sgastat dhss, dba_hist_snapshot dhs
WHERE name IN ('free memory', 'library cache', 'sql area')
AND pool = 'shared pool'
AND dhss.snap_id = dhs.snap_id
AND dhss.instance_number = dhs.instance_number
ORDER BY dhs.snap_id,name)
GROUP BY time, instance_number; |
| |
| NULL and the CBO |
| The CBO makes different decisions based on whether it is possible for a column to contains NULLs |
CREATE TABLE t (
rid NUMBER(12),
col1 VARCHAR2(30),
col2 VARCHAR2(300));
ALTER TABLE t
ADD CONSTRAINT pk_t
PRIMARY KEY(rid)
USING INDEX
PCTFREE 0;
CREATE INDEX ix_t_col1
ON t(col1)
PCTFREE 0;
CREATE SEQUENCE seq_t_rid;
INSERT INTO t
SELECT seq_t_rid.NEXTVAL, dbms_crypto.randombytes(15), dbms_crypto.randombytes(150)
FROM dual
CONNECT BY LEVEL<=100000;
COMMIT;
SELECT *
FROM t
WHERE rownum < 11;
exec dbms_stats.gather_table_stats(USER, 'T', CASCADE=>TRUE);
EXPLAIN PLAN FOR
SELECT DISTINCT rid FROM t;
set linesize 121
SELECT * FROM TABLE(dbms_xplan.display);
EXPLAIN PLAN FOR
SELECT DISTINCT col1 FROM t;
SELECT * FROM TABLE(dbms_xplan.display);
ALTER TABLE t
MODIFY col1 NOT NULL;
EXPLAIN PLAN FOR
SELECT DISTINCT col1 FROM t;
SELECT * FROM TABLE(dbms_xplan.display); |
| |
| NULL Pruning |
| NULL Pruning |
CREATE TABLE parent (
parent_id NUMBER(10),
first_name VARCHAR2(20),
last_name VARCHAR2(20));
CREATE TABLE child (
child_id NUMBER(10),
parent_id NUMBER(10),
birth_date DATE);
BEGIN
FOR i IN 1..500000 LOOP
INSERT INTO parent VALUES (i, 'Daniel', 'Morgan');
INSERT INTO child VALUES (i*2, i, SYSDATE);
INSERT INTO child VALUES (i*3, i, SYSDATE);
INSERT INTO child VALUES (i*4, i, SYSDATE);
END LOOP;
COMMIT;
END;
/
UPDATE child
SET birth_date = NULL
WHERE TO_CHAR(child_id) LIKE '%2';
UPDATE child
SET birth_date = NULL
WHERE TO_CHAR(child_id) LIKE '%6';
COMMIT;
SELECT 'Is Not Null', COUNT(*)
FROM child
WHERE birth_date IS NOT NULL
UNION
SELECT 'Is Null', COUNT(*)
FROM child
WHERE birth_date IS NULL;
SELECT birth_date, COUNT(*)
FROM child
GROUP BY birth_date;
CREATE INDEX ix_child_dob
ON child(birth_date)
PCTFREE 0;
exec dbms_stats.gather_table_stats('UWCLASS', 'PARENT');
exec dbms_stats.gather_table_stats('UWCLASS', 'CHILD');
set timing on
SELECT COUNT(*)
FROM parent p, child c
WHERE p.parent_id = c.parent_id;
SELECT COUNT(*)
FROM parent p, child c
WHERE p.parent_id = c.parent_id
AND birth_date is NOT NULL; |
| |
| Operating System |
| Operating System Statistics |
desc v$osstat
col stat_name format a25
col comments format a63
SELECT stat_name, value, comments, cumulative
FROM v$osstat
ORDER BY 1;
SELECT dho.instance_number, TRUNC(dhs.begin_interval_time, 'HH24') DATETIME, dho.stat_name, AVG(dho.value) AVG_VAL
FROM dba_hist_osstat dho, dba_hist_snapshot dhs
WHERE dho.snap_id = dhs.snap_id
AND dhs.begin_interval_time BETWEEN TRUNC(SYSDATE) AND SYSDATE
GROUP BY dho.instance_number, TRUNC(dhs.begin_interval_time, 'HH24'), dho.stat_name; |
| |
| Parsing |
| Parsing Efficiency |
CREATE TABLE t (
mycol NUMBER(5));
set timing on
BEGIN
FOR i IN 1 .. 10000
LOOP
EXECUTE IMMEDIATE 'INSERT INTO t VALUES (:x)'
USING i;
END LOOP;
END;
/
DECLARE
cur PLS_INTEGER := dbms_sql.open_cursor;
str VARCHAR2(200);
retval NUMBER;
BEGIN
FOR i IN 10001 .. 20000
LOOP
str := 'INSERT INTO t VALUES (' || TO_CHAR(i) || ')';
dbms_sql.parse(cur, str, dbms_sql.native);
RetVal := dbms_sql.execute(cur);
END LOOP;
dbms_sql.close_cursor(cur);
END;
/
DECLARE
cur PLS_INTEGER := dbms_sql.open_cursor;
str VARCHAR2(200);
retval NUMBER;
BEGIN
str := 'INSERT INTO t VALUES (:x)';
dbms_sql.parse(cur, str, dbms_sql.native);
FOR i IN 20001 .. 30000
LOOP
dbms_sql.bind_variable(cur,':x', i);
RetVal := dbms_sql.execute(cur);
END LOOP;
dbms_sql.close_cursor(cur);
END;
/
BEGIN
FOR i IN 30001..40000
LOOP
INSERT INTO t VALUES (i);
END LOOP;
END;
/
-- 0.35 seconds
set timing off |
| |
| Session Statistics |
| Collect session statistics |
conn uwclass/uwclass
SELECT DISTINCT sid FROM v$mystat;
SELECT *
FROM v$sesstat s, v$statname sn
WHERE s.statistic# = sn.statistic#
AND s.value > 0
AND s.sid = 145;
-- do some work then compare the change in values
SELECT *
FROM v$sesstat s, v$statname sn
WHERE s.statistic# = sn.statistic#
AND s.value > 0
AND s.sid = 145; |
| |
| Setting CBO Statistics |
| Some joins are better than others |
conn scott/tiger
exec dbms_stats.gather_schema_stats(USER, cascade=>TRUE);
set autotrace on
SELECT DISTINCT d.deptno, d.dname
FROM dept d, emp e
WHERE e.deptno = d.deptno
ORDER BY 1;
SELECT d.deptno, d.dname
FROM dept d
WHERE EXISTS (
SELECT NULL
FROM emp e
WHERE e.deptno = d.deptno)
ORDER BY 1;
CREATE INDEX ix_emp_deptno
ON emp(deptno);
exec dbms_stats.set_table_stats(USER, 'EMP', numrows=>1000000, numblks=>10000, avgrlen=>74);
exec dbms_stats.set_index_stats(USER, 'ix_emp_deptno', numrows=>1000000, numlblks=>1000, numdist=>10000, clstfct=>1);
exec dbms_stats.set_column_stats(USER, 'emp', 'deptno', distcnt=>10000);
exec dbms_stats.set_table_stats(USER, 'dept', numrows=>100, numblks=>100);
-- repeat queries
exec dbms_stats.set_table_stats(USER, 'dept', numrows=>100000, numblks=>10000);
-- again repeat queries |
| |
| SQL Statements |
| Access Objects |
set linesize 131
col object format a20
col object_type format a11
col owner format a20
col username format a10
col osuser format a25
SELECT a.object, a.type OBJECT_TYPE , a.owner, s.username, s.osuser, s.status, s.type USER_TYPE
FROM gv$access a, gv$session s
WHERE a.sid = s.sid
ORDER BY 2,1; |
| Resources |
desc gv$resource_limit
set linesize 121
col event format a30
SELECT *
FROM gv$resource_limit
ORDER BY 2, 1; |
| Session Environment |
desc gv$ses_optimizer_env
set linesize 121
col event format a30
SELECT s.inst_id, oe.sid, id, name, isdefault, value
FROM gv$ses_optimizer_env oe, gv$session s
WHERE oe.sid = s.sid
AND s.service_name <> 'SYS$BACKGROUND'; |
| Wait Times |
SELECT sid, schemaname
FROM gv$session
ORDER BY 2;
set linesize 121
col event format a30
SELECT inst_id, seq#, event, p1, p2, p3, wait_time
FROM gv$session_wait_history
WHERE sid = 158; |
| Wait Time Trend Analysis |
col interval format a20
SELECT TO_CHAR(begin_interval_time,'YYYY_MM_DD HH24:MI') AS interval,
dhse.instance_number, time_waited_micro - LAG(time_waited_micro, 1, 0) OVER (ORDER BY dhse.instance_number, dhse.snap_id) AS time_waited,
total_waits - LAG(total_waits, 1, 0) OVER (ORDER BY dhse.instance_number, dhse.snap_id) AS total_waits
FROM dba_hist_snapshot dhs, dba_hist_system_event dhse
WHERE dhs.snap_id = dhse.snap_id
AND dhs.instance_number = dhse.instance_number
AND event_name = 'db file sequential read'
ORDER BY 1,2; |
| What happened during the execution of a SQL statement |
set linesize 121
col username format a8
col name format a60
SELECT s.sid, s.serial#, s.username, sn.name, ms.value
FROM gv$statname sn, gv$mystat ms, gv$session s
WHERE MS.SID = s.sid
AND ms.statistic# = sn.statistic#
ORDER BY 4;
-- run your SQL statement here
SELECT s.sid, s.serial#, s.username, sn.name, ms.value
FROM gv$statname sn, gv$mystat ms, gv$session s
WHERE MS.SID = s.sid
AND ms.statistic# = sn.statistic#
ORDER BY 4; |
| |
| Startup Parameters |
| Examine some of your init parameters and modify them to see if they have a positive affect. |
SELECT name, value
FROM gv$parameter
WHERE name IN (
'optimizer_features_enabled',
'optimizer_index_caching',
'optimizer_index_cost_adj',
'optmimizer_mode',
'optimizer_secure_view_merging',
'plsql_optimize_level'); |
| |
| Setting OPTIMIZER_INDEX_COST_ADJ |
| These queries provides a guideline, a starting point, tuning is the next step |
SELECT ROUND((s.time_waited/e.time_waited)*100, 0)
optimizer_index_cost_adj
FROM v$system_event s, v$system_event e
WHERE s.event = 'db file sequential read'
AND e.event = 'db file scattered read';
SELECT ROUND(AVG(singleblkrdtim)/AVG(readtim-singleblkrdtim)*100,0)
optimizer_index_cost_adj
FROM v$filestat; |
| |
| Time Modeling |
| System Time Model |
col value format 999999999999
SELECT stat_name, value
FROM v$sys_time_model
ORDER BY 2; |
| Session Time Model |
col value format 999999999999
SELECT stat_name, value
FROM v$sess_time_model
WHERE sid = (SELECT DISTINCT sid FROM v$mystat)
ORDER BY 2;
-- follow up by working with dba_hist_active_sess_history |
| |
| Windows |
Kernel Tuning
The following was contained in a private note from Oakie Charles Hooper in Oct. 2008 |
By default, Windows servers are set up to provide fast performance for file sharing, which sets aside large portions
of the RAM for file caching. You need to change this so that it is set to be optimized for network applications. In the Windows Control Panel, find the
network settings (if a network card is listed, right-click it and select Properties), and then inside the network settings display the settings for
"File and Printer Settings". Change the setting to be optimized for network applications. There is a chance that client computers may be
disconnected briefly when this change is made.
There was a Microsoft document floating around which suggested changing a Windows registry setting (TcpAckFrequency) from the default to 13 in order
to improve performance. The result of doing so is that a file which should transfer across a gigabit network from a Linux box to
the Windows box in 3 seconds, actually requires 45 minutes to complete. |
|
