TKProf by ASH

Oracle session trace + tkprof is still the best tool for performance diagnosis. But: Need access to database server user_dump_dest directory. Has to be switched on in advance Has a performance impact, especially if tracing multiple sessions or tracing for a long time. And ASH is a good enough alternative much of the time. The […]

Read More TKProf by ASH
October 6, 2016

What to do if a database performance issue is reported

A high level overview. Expectation should be for all OLTP operations to take less than 1 second, and all batch processing jobs and background scheduled report jobs to take less than 1 hour each. This is deliverable on even large databases with moderate hardware, with some effort put in. 1) Is the issue even a […]

Read More What to do if a database performance issue is reported
December 14, 2015

Oracle hint cursor_sharing_exact using sql patch

Bind variable peeking is still a cause of plan instability, even after 11gR1’s introduction of adaptive cursor sharing. In my case adaptive cursor sharing wasn’t helpful for a daily batch job, because the sql was aged out of cache by the time the next scheduled run came around. Making everything worse was having parameter cursor_sharing […]

Read More Oracle hint cursor_sharing_exact using sql patch
November 9, 2015

Oracle Full Text simple example for like % grep wildcard

The equivalent of the grep command in oracle is like: SELECT * FROM dba_source s WHERE LOWER ( s.text ) LIKE ‘%within group%’ ; That works fast enough for querying dba_source, which is a small dataset. But for even medium sized tables, you really want a fast indexed query, which the “LIKE ‘%” syntax ordinarily […]

Read More Oracle Full Text simple example for like % grep wildcard
November 2, 2015

One Comment

Oracle top session info in RAC with tracefile name

See real time top session info in either RAC or standalone database, including tracefile name qualified by instance name, with: — ALTER SESSION SET nls_date_format = ‘Dy DD-Mon-YYYY HH24:MI:SS’ ; SELECT DISTINCT s.inst_id , s.sid , s.serial# , s.username , s.osuser , s.program || ‘ ‘ || s.module AS program , s.status , ROUND ( […]

Read More Oracle top session info in RAC with tracefile name
October 27, 2015

10046 trace a module using logon trigger

1) Update – thanks to Joaquin Gonzalez for pointing out a preferable safer method than logon trigger for 10046 tracing a particular module: EXEC DBMS_MONITOR.serv_mod_act_stat_enable ( service_name => ‘myservice’ , module_name => ‘myModule.exe’ , binds => TRUE ) You can verify service_name is what you expect by checking the column of the same name in […]

Read More 10046 trace a module using logon trigger
October 27, 2015

One Comment

List oracle indexes and their columns with listagg

List indexes on large tables, with listagg to show the indexed columns in a comma separated line, and outer join to include tables with no indexes: SELECT t.owner , t.table_name , ROUND ( t.blocks * 8 / 1024 / 1024 ) AS gb — assuming each block is default 8kb , t.num_rows , i.index_name , […]

Read More List oracle indexes and their columns with listagg
June 17, 2015

Oracle distributed query join over remote database link slow performance leading driving_site hint fix

Oracle Optimizer doesn’t cope so well with distributed queries, joining to tables in different databases. 1) The leading and/or driving_site hints can dramatically improve distributed query performance. SELECT /*+ leading(tab1) driving_site(tab2) */ … FROM table1 AS tab1 JOIN table2@db2 AS tab2 … Driving_site specifies the database you want doing the bulk of the work, likely […]

Read More Oracle distributed query join over remote database link slow performance leading driving_site hint fix
June 16, 2015

Oracle User Group Scotland Conference 2015

Oracle User Group Scotland Conference 2015 1) SQLcl (SQL command line) new enhanced SQL*Plus, easy to output index info, DDL, output/load data in csv, xml. Early adopter (beta) version available for download now, production version expected soon and to be bundled by default into Oracle 12cR2. More at Kris Rice. 2) Relax and Recover Martin […]

Read More Oracle User Group Scotland Conference 2015

Calculate optimal PGA size

Simple script to calculate optimal pga_aggregate_target size (in gb): col avg_tpi form 999.99 col max_tpi form 999.99 select instance_number, avg(value)/1024/1024/1024 avg_tpi , max(value)/1024/1024/1024 max_tpi from dba_hist_pgastat where name = ‘total PGA inuse’ group by instance_number ; dba_hist_pgastat has 1 weeks worth of data by default, snapshots taken at 1 hour intervals. Memory not assigned to […]

Read More Calculate optimal PGA size
July 25, 2013