Hi there, This some queries I am using to check access predicate efficiency. First two small functions. Access predicate: [code language=”sql”] CREATE OR REPLACE FUNCTION EXTRACT_ACCESS_PREDICATES (pIn varchar2, pOwner varchar2,… Read more »
Hi There, This the query I am using to identify good candidate sql statements for result_cache sqlpatch. [code language=”sql”] select s.sql_id,s.executions, round (s.cpu_time/1000000) cpu, round (s.ELAPSED_TIME/1000000) elapse, round ((s.ELAPSED_TIME/1000000)/ decode… Read more »
Hi there Here is a customer case again on Documentum database. Oracle 11.2.0.4 on AIX power 8. [code language=”sql”] create materialized view log on dmi_queue_item_s WITH sequence, ROWID (item_id,name,task_name,task_subject,date_sent,router_id,delete_flag,dependency_type),PRIMARY KEY… Read more »
Hi there Giving an SQL tuning training session, I came to the following point: This query: [code language=”sql”] select count(prs.firstname), count(adr.zip_additional), count(mlg.dispatch_date) from addresses adr join recipients rec… Read more »
Hi there, what’s wrong on the picture bellow? When trying to get sql activity behind there is nothing. [code language=”sql”] select sql_id,wait_class, blocking_session from v$active_session_history where event =’log file… Read more »
Hi there, The idea came to me while running an audit to identify bottlenecks in an Oracle infrastructure composed of two servers with many single instances. Due to lack of… Read more »
Here is an interesting tips in 11.2.0.4 and later (tested also in 12c), Simple example that shows Query rewrite using a materialized view. sqlplus scott/tiger SQL>create materialized view TEST_MV2 enable… Read more »