Ideally developers will set module and action to allow for easy debugging and performance monitoring in v$session, v$active_session_history, dba_hist_sqlstat, OEM screens, etc. If not, a logon trigger can be used to set these. Below does this for any tomcat sessions which still have module set to the default generic value. Pattern matching is used for […]
Read More Logon Trigger set ModuleThe broadcast parallel query distribution method can be non-optimal. Use hash distribution instead with:
Read More pq_distribute hash hash Fast Parallel OracleOracle 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 ASHA 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 reportedBind 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 patchThe 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 wildcardSee 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 name1) 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 triggerList 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 listaggOracle 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