List of tables accessed by Tuner

From DB Optimizer
Jump to: navigation, search

Oracle Tables Read by Tuner

  • In order to extract the explain plan we create a table on Oracle named EMBARCADERO_EXPLAIN_PLAN.
  • Outlines management creates a function called EMBT_GET_SIGNATURE that uses sys.outln_edit_pkg.generate_signature package
  • For statistics we create a function called EMBT_DISPLAY_RAW


Component Read Write
SGA V$SQL, SYS.ALL_USERS
Outlines management SYSTEM.OL$HINTS, USER_OUTLINES, DBA_OUTLINES, USER_OUTLINE_HINTS, DBA_OUTLINE_HINTS SYSTEM.OL$HINTS, CREATE OUTLINE, ALTER OUTLINE, DROP OUTLINE
Execution statistics v$session, v$sesstat, v$statname, V$SQL_PLAN_STATISTICS
Index analysis DBA_OBJECTS,, USER_OBJECTS, ALL_IND_COLUMNS, ALL_INDEXES,

all_constraints, all_cons_columns

CREATE INDEX, DROP INDEX
Statistics USER_TAB_MODIFICATIONS, SYS.DBA_TAB_MODIFICATIONS, USER_TABLES, DBA_TABLES, USER_TAB_COLUMNS, SYS.DBA_TAB_COLUMNS, DBA_HISTOGRAMS, USER_HISTOGRAMS


Example of giving grants to user via a role (as SYS):

CREATE ROLE SQLTUNING NOT IDENTIFIED;
GRANT SQLTUNING TO "CONNECT";
GRANT SQLTUNING TO SELECT_CATALOG_ROLE;
GRANT ANALYZE ANY TO SQLTUNING;
GRANT CREATE ANY OUTLINE TO SQLTUNING;
GRANT CREATE ANY PROCEDURE TO SQLTUNING;
GRANT CREATE PROCEDURE TO SQLTUNING;
GRANT CREATE ANY TABLE TO SQLTUNING;
GRANT CREATE ANY TRIGGER TO SQLTUNING;
GRANT CREATE ANY VIEW TO SQLTUNING;
GRANT CREATE SESSION TO SQLTUNING;
GRANT CREATE TRIGGER TO SQLTUNING;
GRANT CREATE VIEW TO SQLTUNING;
GRANT DROP ANY OUTLINE TO SQLTUNING;
GRANT DROP ANY PROCEDURE TO SQLTUNING;
GRANT DROP ANY TRIGGER TO SQLTUNING;
GRANT DROP ANY VIEW TO SQLTUNING;
GRANT SELECT ON SYS.V_$SESSION TO SQLTUNING;
GRANT SELECT ON SYS.V_$SESSTAT TO SQLTUNING;
GRANT SELECT ON SYS.V_$SQL TO SQLTUNING;
GRANT SELECT ON SYS.V_$STATNAME TO SQLTUNING;

Once complete, you can assign the role to users who will be running tuning jobs:

CREATE USER TUNINGUSER IDENTIFIED BY VALUES '05FFD26E95CF4A4B'
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
QUOTA UNLIMITED ON USERS
PROFILE DEFAULT
ACCOUNT UNLOCK;
GRANT SQLTUNING TO TUNINGUSER;
ALTER USER TUNINGUSER DEFAULT ROLE SQLTUNING;


Collecting SQL Execution Statistics:

SELECT (
SELECT
ROUND(S.LAST_ELAPSED_TIME/1000000,2)
FROM
V$SQL_PLAN_STATISTICS S
WHERE
S.ADDRESS=P.ADDRESS and
s.hash_value=p.hash_value and
s.child_number=p.child_number AND
S.OPERATION_ID=P.ID
) "ELAPSED"
FROM V$SQL_PLAN P
where p.sql_id = ?
order by P.CHILD_NUMBER, p.id


select
n.name,
value,
sysdate
from
sys.v_$sesstat s,
sys.v_$statname n
where
s.sid = :sid and
s.statistic#=n.statistic# and
n.name in ('CPU used by this session',
'physical reads',
'consistent gets',
'db block gets',
'session logical reads',
'parse time cpu',
'sorts (memory)',
'sorts (disk)',
'sorts (rows)',
'opened cursors current')


SELECT
p.id,
(SELECT S.STARTS
FROM V$SQL_PLAN_STATISTICS S
WHERE
S.ADDRESS = P.ADDRESS AND
s.hash_value = p.hash_value AND
s.child_number = p.child_number AND
S.OPERATION_ID = P.ID) "STARTS",
(SELECT S.LAST_OUTPUT_ROWS
FROM V$SQL_PLAN_STATISTICS S
WHERE
S.ADDRESS = P.ADDRESS AND
s.hash_value = p.hash_value AND
s.child_number = p.child_number AND
S.OPERATION_ID = P.ID) "ROWS",
(SELECT ROUND (S.LAST_ELAPSED_TIME / 1000000,
2)
FROM V$SQL_PLAN_STATISTICS S
WHERE
S.ADDRESS = P.ADDRESS AND
s.hash_value = p.hash_value AND
s.child_number = p.child_number AND
S.OPERATION_ID = P.ID) "ELAPSED",
(SELECT S.LAST_CR_BUFFER_GETS
FROM V$SQL_PLAN_STATISTICS S
WHERE
S.ADDRESS = P.ADDRESS AND
s.hash_value = p.hash_value AND
s.child_number = p.child_number AND
S.OPERATION_ID = P.ID) "CR_GETS",
(SELECT S.LAST_CU_BUFFER_GETS
FROM V$SQL_PLAN_STATISTICS S
WHERE
S.ADDRESS = P.ADDRESS AND
s.hash_value = p.hash_value AND
s.child_number = p.child_number AND
S.OPERATION_ID = P.ID) "CU_GETS",
(SELECT S.LAST_DISK_READS
FROM V$SQL_PLAN_STATISTICS S
WHERE
S.ADDRESS = P.ADDRESS AND
s.hash_value = p.hash_value AND
s.child_number = p.child_number AND
S.OPERATION_ID = P.ID) "READS",
(SELECT S.LAST_DISK_WRITES
FROM V$SQL_PLAN_STATISTICS S
WHERE
S.ADDRESS = P.ADDRESS AND
s.hash_value = p.hash_value AND
s.child_number = p.child_number AND
S.OPERATION_ID = P.ID) "WRITES",
P.CARDINALITY "Cardinality"
FROM V$SQL_PLAN P
WHERE p.sql_id = ?
ORDER BY
P.CHILD_NUMBER,
p.id
Retrieved from "http://docwiki.embarcadero.com/DBOptimizer/e/index.php?title=List_of_tables_accessed_by_Tuner&oldid=333"