Debugging Info

AskTom Thread on debugging VPD

Helpful Role

This roll will help developers that need to investigate VPD issues without having to give them 'Keys to the Castle' DBA Role:

CREATE ROLE VPD_SUPPORT_ROLE;

GRANT SELECT_CATALOG_ROLE TO VPD_SUPPORT_ROLE;

GRANT SELECT ON DBA_AUDIT_POLICIES TO VPD_SUPPORT_ROLE;
GRANT SELECT ON DBA_AUDIT_POLICY_COLUMNS TO VPD_SUPPORT_ROLE;
GRANT SELECT ON DBA_POLICIES TO VPD_SUPPORT_ROLE;
GRANT SELECT ON D Read more »

...well not yet...

An Infoworld article was released today describing an Oracle vulnerability. The SCN space can be exhausted via bugs or attackers exploiting bugs. Metalink doc: 1376995.1 [Bulletin : Information on the System Change Number (SCN) and how it is used in the Oracle Database] covers a technical description for what SCNs are used for.

You can use this query to find out how close you are to the SCN limit. Read more »

Optimizer Decisions

Great article from Oracle Optimizer team covering Subquery Unnesting including mechanics and limitations of Optimizer Subquery Unnesting: Part 1 | Part 2

Installation

Initial Installation

cd ~/sqlt/install
sqlplus "/ as sysdba" @sqcreate.sql

This script will create the SQLTXPLAIN user and associated objects. You will have to answer some questions about paths and tablespaces.

Silent Installation

cd ~/sqlt/install
sqlplus "/ as sysdba"

START sqdrop.sql
START sqcsilent2.sql '' xplain USERS TEMP 'SCHEMA' T
REM To speed up execution, run these commands:
EXEC sqltxplain.sqlt$a.set_param('test_case_builder', 'N');
 Read more »

I normally get a lot of mileage out of Application Express interactive reports. Unfortunately there are some restrictions on the SELECT statements that can be used with them. Here is a demonstration of a technique I use when I want to add some interactivity to a plain old Apex report. I will create a Select List dropdown that submits and refreshes the report data automagically. The value in the select list will be applied as a filter to the report when it refreshes.

1) Create the Select List

• Items -> New Item -> Select List • Choose "Select List with Submit" • Next

Had an odd one come across my desk today. I have seen Bind variables used in the WHERE clause thousands of times. I have seen Binds in the SELECT lists hundreds of times. I have seen it used in the HAVING clause zero times... until today when it turns out it was causing a performance issue. With the Bind variable in the HAVING clause we were seeing an execution plan with lots of Full Table Scans and 12+ second runtimes. When editing the query and using a literal in the HAVING clause we saw 100ms or less performance and very efficient Execution Plans.

I am a Software Developer and Oracle DBA by trade, but have worked with many RDBMS systems ranging from small DBaseIV / FoxPro PC databases, medium sized MySQL databases, and large Sybase, Oracle, and MS SQL Server databases. I hope you can find some useful information here. I have been collecting posts and notes since July 2007. The information presented in Blog Posts and Notebook Pages is a wide ranging collection of hints, tips, tricks, and caveats. If you are not a regular reader then hopefully you land here from a Google search and take something away that helps you solve a problem.

Note: This note contains some ideas for getting customers and tuners on the same page. Oracle is a complex piece of software and doesn't always do the 'right thing'.

• SQL Selects can have multiple logically equivalent forms.
  5 people could write a SQL Query to solve the same problem but they may arrive at the same correct result set using different combinations of SQL constructs and SQL features.
• Logically equivalent forms can be analyzed differently by the CBO

Seems like Golden Gate is following me around. I used their tools in 2005 to replicate from OLTP databases to a central reporting database for my previous employer. Now that Oracle bought them the buzz in my current company is to leave Oracle's multiple and complicated incarnations of replication technologies in the dust and try moving forward with GG. Fine with me, I like the product, not a fan of the licensing as I think Oracle has been on the lagging side of vendor supplied replication tools, but hey what do I know, I just arrange zeroes and ones for a living.

I have started a project to collect useful SQL Developer User Defined Reports. More info to follow after I complete a few check-ins.

https://github.com/dmann99/SQLDevUDRepPack

-Dave

Display Most Recent Plan from PLAN_TABLE

WITH CurrPlan AS (SELECT * FROM PLAN_TABLE WHERE TIMESTAMP=(SELECT MAX(TIMESTAMP) FROM PLAN_TABLE)) 
select 
  ID, 
  lpad(' ', level-1) || 'ParID='||PARENT_ID || ':'||operation || ' (' || options || ')' "Operation", 
  object_name "Object",
  CurrPlan.*
from 
  CurrPlan
 start with id = 0 
connect by prior id=parent_id;

Display from Cursor Cache

Add a unique comment hint (like QUERYCOMMENT001 below) so we can locate the SQL easily in v$sql.

WITH targetsql AS
 Read more »

Setting up a new project

griffon create-app myApp

# Visual
griffon install-plugin abeilleform-builder
griffon install-plugin miglayout

# Database
# griffon install-plugin activejdbc

# Eclipse integration
griffon integrate-with --eclipse
griffon install-plugin eclipse-support
griffon eclipse-update

eclipse --> import existing project