SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR(‘atfwcg8anrykp’)) display nothing. SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR(‘atfwcg8anrykp’)) Oracle 11 G provides new feature of virtual columns which allows user to provide the column virtually where value is specified in Expression itself.Virtual columns acts like a normal columns during indexing and partitioning.Virtual columns belongs to LOBs or collection datatypes. but the last_active_time of the bad one changed as well. Good sql disappear from v$sql after less than 1 hour. The change had an immediate impact and the SQL is executed properly now. Whether 'the application' is supported with Oracle 11 depends on 'the application'. We can see the clue from the exuction planįilter(("URL" LIKE :1 AND INTERNAL_FUNCTION("LOG_DATETIME")>=:2 AND INTERNAL_FUNCTION("LOG_DATETIME")<=:3))īecause we just need the date, not the time, so I tried use the when setting the bind variable. Download the classes12.jar file and put it in the Java CLASSPATH of the application. It triggered me thinking that the bad jdbc driver might pass the value of java timestamp in a way that Oracle has to do some internal transformation, thus the logdate column is omitted.
So it was iterating all the partitions and generated huge amount IO reads. The bad one seems only use the first column (url) of the index and ignore the logdate column.
The good one is 9.0.1 and all others (10.2.0.2, 10.2.0.3) are poor.Įxamed the exeucte plan carefully, I discovered that though both SQLs use same index, the good one use composition index( url and logdate) properly and thus can do partition pruning. With ojdbc14.jar it runs ten times slower. A compsite index contains column (URL, logdate)Ī Java app will fetch some data through JDBC connection.
0 kommentar(er)
