| 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
WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'WZZZZZ';
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; |
| |
| 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; |
|
