| General |
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
|
| |
| 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; |
| |
| 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 |
| |
| Setting Stats |
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. |
|
